1

I have a Postgres custom function that uses pgr_dijkstra to generate a route working. It works be passing the lat/lon of the start and destination points. I then find the closest segment vertice to that lat/lon point and use that in the pgr_dijkstra call.

snippit: EXECUTE 'SELECT id::integer FROM trail_split_segs_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' || x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec; source := rec.id;

The problem is even though I may be moused over a specific segment, the closest vertex might belong to a different segment, since its doing the lookup based on distance.

the red point is the one I want, but I get the purple one since its closer
(the red point is the one I want, but I get the purple one since its closer)

I tried creating a different function to instead find the closest edge to the users lat/lon points rather than the closest vertex. But I have to choose if i'm going to use that edge's source or target.

snippit SELECT id, source, target, geom, ST_Distance( geom, ST_GeometryFromText( 'POINT(-123.036073 49.357152)', 4326 ) ) AS dist FROM trail_split_segs WHERE geom && ST_SetSRID( 'BOX3D(-123.036083 49.357142, -123.036063 49.357162)'::box3d, 4326 ) ORDER BY dist LIMIT 1 The box created by this is very small, so pretty much will always select the correct edge, because I have the users mouse snap to the edges in the web interface.

What I want to do is select the edge source or target that is closest to the lat/lon point where the id of the edge has to be the same.

UPDATE Zia's response below helped set me on the right track, I modified his query.

WITH point AS (SELECT st_setsrid(st_makepoint(-123.0568528175354, 49.36230206100319), 4326) AS point), line AS (SELECT geom AS line_geom FROM trail_split_segs, point WHERE st_dwithin(geom, point, 0.00012) ORDER BY geom <-> point LIMIT 1) SELECT id, ST_AsText(the_geom) AS point_geom FROM trail_split_segs_vertices_pgr, line, point WHERE st_dwithin(the_geom, line_geom, 0.00001) ORDER BY the_geom <-> point LIMIT 1;

Improve this question
2
  • How is the vertex pointed to by the red arrow supposed to be identified. Is there another intersection there, your image doesn't show? Commented Dec 3, 2014 at 15:29
  • It is the "source" id of that edge. Each edge/segment has a source & target ID/node. Commented Dec 4, 2014 at 0:07

1 Answer 1

Reset to default
2

I think you don't need to set the BOX3D, instead use the following approach. But keep in mind that the 0.00012 is a bit crucial, you have to select it carefully. Don't insert 0 instead as I have noticed some abnormal behavior in the past. Plus, if your CRS is in ESPG:3857 then you have to defined this buffer value not in degrees but meters.

with line as (select geom as line_geom from trail_split_segs order by geom <-> st_setsrid(st_makepoint(-123.036073 49.357152), 4326) limit 1) select id, the_geom as point_geom from trail_split_segs_vertices_pgr, line where st_dwithin(the_geom, line_geom, 0.00012) order by the_geom <-> st_setsrid(st_makepoint(-123.036073 49.357152), 4326) limit 1;
Improve this answer
2
  • this still gives the same result as above, it doesn't restrict to the edge, so the purple point is selected. BUT this helped set me on the right path, thank you! Commented Dec 4, 2014 at 0:04
  • It's strange why the result is still the same. Anyways, your welcome. Commented Dec 4, 2014 at 9:56

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.