How to get the nearest points from pgrouting linestring result?

Question

I'm trying to get the points or landmarks nearest to the result of pgrouting linestring (the shortest path). I'm using OSM map entities and working with planet_osm_point. Here's my code:

SELECT st_asgeojson( ST_Collect( nearRank.nearGeom ) ) As geojson 
 FROM ( SELECT ST_Transform( marks.way, 4326 )  as nearGeom, name, 
              ST_Distance( road, marks.way )/1000.0 AS distance_km 
    FROM planet_osm_point AS marks, 
         st_setsrid( ST_GeomFromText('LINESTRING(121.1527761
                     13.9413653,121.154058 13.9413113,121.1550161 
                     13.9412372,121.1559079 13.9411891)') , 900913 ) as road 
    WHERE marks.amenity <> '' AND marks.name <> ''
    ORDER BY  ST_Line_Locate_Point(road, marks.way), 
    marks.way <-> road LIMIT 15 ) As nearRank 

The above sql script is not giving the right nearest nodes along the linestring. The linestring above is the result of pgrouting functions. Is there anyone that can tell me what I have missed? The output should be points representing the landmarks near the shortest path.

Answer 1

Try analyzing your sub-statements to find out why it isn't working. I had a compound select statement such as yours which worked on one set of data but didn't on another due to the statement referring to the record id rather than a target or source id. Doing so, you may discover that you are referencing/comparing the wrong geometry srid.

From your example, try this part and validate the result:

SELECT st_setsrid( ST_GeomFromText('LINESTRING(121.1527761
                     13.9413653,121.154058 13.9413113,121.1550161 
                     13.9412372,121.1559079 13.9411891)') , 900913 );

You may have to modify some of them slightly to get a result... try this and validate the result:

SELECT ST_Transform( marks.way, 4326 )  as nearGeom, name
    FROM planet_osm_point AS marks;

and so on.

Answer 2

Check your osm2po config file to ensure you create the desired routing table.

See this post

Answer 3

Or perhaps it is because you are transforming your ways to srid=4326 (WGS 84) to compare against your linestring with an srid=900913 (Deprecated Mercator)?

ST_Transform( marks.way, 4326 )

st_setsrid( ST_GeomFromText('LINESTRING(121.1527761
                     13.9413653,121.154058 13.9413113,121.1550161 
                     13.9412372,121.1559079 13.9411891)') , 900913 )