1

I'm new to the GIS system and I'm following the pgRouting workshop.

I've imported the locations of nodes (points, highway=street_lamp) using osm2pgsql.

now I'm calculating a few paths between two points using pgr_ksp (K-Shortest Path) to compute K shortest paths.

How can I count how many street lamps there are for each path?

There's a function that given a path returns all the nodes in that path?

  • Depending on what you are trying to achieve you should also consider looking at the lit tag. – scai Jun 29 '15 at 14:23
2

You have two issues mixed.

First: pgRoute, uses precreated topology based on LineString geometry. Such geometry doesn't have direct links on osm Nodes. You could get geospatial points for built route, not osm Nodes.

To get Nodes, you could build an index among osm Nodes geometries and perform distance lookup. In other words, select all nodes which are not far then treshold around built route's nodes.

Anyway, you have to use this approach be cause of second issue.

Second: highway=street_lamp are not always the part of highways. It's possible, then highway=primary go along a center of a street, and highway=street_lamp marks sideroad lamps mounted on the poles.

So you need to

  1. Calculate route
  2. Create buffer of a some distance around route geometry see: ST_Buffer
  3. Join street lamps via query like this:

    select * from planet_osm_point AS sl where ST_Intersects( ST_Buffer( (SELECT the_geom as route_geom FROM pgr_dijkstra( 'SELECT gid as id, source::int, target::int, length::float AS cost FROM ways', 157543, 236829, false, false), ways WHERE id2 = gid ORDER BY seq), 100), sl.way)

    As I've understood you, route_geom returns you not a single row, but a set of rows. In that case, you could use ST_Collect with group by (if you need) to form a MultiLineString geometry for your subquery.

    Something like this:

    ... ST_Buffer(ST_Multi(ST_Collect(route.route_geom)) from ((SELECT the_geom as route_geom FROM pgr_dijkstra( 'SELECT gid as id, source::int, target::int, length::float AS cost FROM ways', 157543, 236829, false, false), ways WHERE id2 = gid ORDER BY seq)) as route, 100) ...

    I'm not sure about ST_Buffer(your_route.geometry, 100) because I don't know projection of your data, and can't say will it be 100 meters or 100 degrees. Refer to ST_Buffer documentation, or try to open buffered route geometry in some GIS application, for clarification.

  • Thank you! it helped me better understand it! I've still a problem. The query below gives me "ERROR: more than one row returned by a subquery used as an expression" because ST_Intersects needs only one row. I'm trying to understand how to do some kind of for loop. select * from planet_osm_point AS sl where ST_Intersects(ST_Buffer((SELECT the_geom FROM pgr_dijkstra('SELECT gid as id, source::int, target::int, length::float AS cost FROM ways',157543, 236829, false, false), ways WHERE id2 = gid ORDER BY seq), 100), sl.way) – Luca Jun 30 '15 at 22:45
  • I've answered you in answer body. – dmitry.v.kiselev Jul 1 '15 at 6:41

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.