2

I need to create a series of street networks for a neighbourhood in a city. I have used PGRouting before using a whole table to create a topology but I have not been able to do so with only a subset, as you'll see here.

I'm using the following query.

WITH vkk AS (SELECT name, way
            FROM planet_osm_polygon
            WHERE admin_level = '10'
            AND name = 'Vallcarca i els Penitents'),
    roads as (SELECT r.osm_id, r.way
             FROM planet_osm_line r
              JOIN vkk v
              ON ST_Contains(v.way, r.way)
             WHERE highway IS NOT NULL)
SELECT pgr_createTopology('roads', 5, 'way', 'osm_id',
 'source', 'target', rows_where := 'true')

This returns the following error

NOTICE:  PROCESSING:
NOTICE:  pgr_createTopology('roads', 5, 'way', 'osm_id', 'source', 'target', rows_where := 'true', clean := f)
NOTICE:  Performing checks, please wait .....
NOTICE:  ----> PGR ERROR in pgr_createTopology: Table roads not found
HINT:    ----> Check your table name
NOTICE:  Unexpected error raise_exception

Successfully run. Total query runtime: 47 msec.
1 rows affected.

Is there any solution besides creating a table for each neighbourhood I need to do this and then delete it? (I'd loop through the queries using sqlalchemy or something of the sort)

1
  • 2
    That function needs a proper relation to pull data from. You can prepare TEMP tables, or properly utilize the rows_where filter. If not already, it may be a good idea to extract the greater AOI from the OSM data, and create a seamless network topology. Then run further analysis and routing on particular neighborhoods dynamically.
    – geozelot
    Jun 25, 2021 at 10:32

1 Answer 1

3

AS @geozelot writes in a comment, the pgr_createTopology function refers to a table name as the first argument, but in your query this table does not exist.

Instead of creating a table for each neighborhood you could create a view for each neighborhood. I made a quick test with the workshop data and PostgreSQL 13 and pgr_createTopology is able to populate the topology information also in a view, which eventually will not work in older versions of PostgreSQL.

Or you can use the rows_where argument, which allows to apply a filter query.

SELECT  pgr_createTopology('roads', 0.00001, rows_where:='id < 10');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.