Issues with pgrouting createtopology() and rows_where condition

Question

I am attempting to run createtopology on a database where tags match certain conditions with a jsonb datacolumn but the command fails on the rows_where condition and I don't know why.

The Database is setup under the schema 'berlin' and has tables 'nodes', 'ways', and 'routes' and I'm trying to run it on the ways table obviously.

berlin.ways has the following columns and datatypes:

way_id - int
tags - jsonb
geom - linestring
dist - real
source - int
target - int

and I am trying to run:

SELECT pgr_createTopology('berlin.ways', 10, id:='way_id', the_geom:='geom', rows_where:=(tags @> '{"subway":"yes"}'::jsonb));

This returns the error: column "tags" does not exist. I've switched it to berlin.ways.tags and get the error: missing FROM-clause entry for table "ways"

I am very new to postgres/sql in general

Hmmm, it should work this way after reading the docs. What happens if you do something simple like using rows_where:='way_id < X' ? Does it work? Can you also instead turn the rows_where into a select query? — Timothy Dalton, Feb 22 '23 at 03:02

score 0 · Accepted Answer · answered Feb 22 '23 at 13:58

pgr_createTopology can receive the option rows_where, which is the condition that will be applied on the targeted table. The type of rows_where is therefore a text.

By using an expression instead of a text, you are building the rows_where clause, which can't work because there is not even a from clause (it would have worked - but it is not the intent - with something like select pgr_createTopology(... , rows_where=my_where_clause_column) from my_where_clause_table where id=123)

So the solution is to quote the rows_where clause, possibly with dollar-quotes to avoid having to escape the single quotes:

SELECT pgr_createTopology('berlin.ways', 10, id:='way_id', the_geom:='geom', rows_where:=$$(tags @> '{"subway":"yes"}'::jsonb)$$);

Issues with pgrouting createtopology() and rows_where condition

1 Answers1