3

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.

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.

0

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

See this post

0

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 )
  • 1
    Are the 3 answers independent from each other? Or would it be interesting putting all the content (through edits) in just one post? – Andre Silva May 24 '16 at 18:12
  • 1
    I posted three answers because any one of them could solve the issue. I admit that I am not sure if they should have been consolidated in the first place. Although a future reader may find that one of the answers helps them rather than the OP. – kttii May 24 '16 at 18:14

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.