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)
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;
