How to check if a node is in route computed with pgRouting?

Question

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?

Answer 1

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.