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

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
Commented Jun 29, 2015 at 14:23

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
Commented Jun 30, 2015 at 22:45
• I've answered you in answer body. Commented Jul 1, 2015 at 6:41