2

I am working through the book 'pgRouting: A Practical Guide' and have come across a function that has been deprecated. The function is pgr_pointToEdgeNode. Is there a similar function I can use to convert a point to a vertex_id based on closest edge?

This is a link to the function from version 2.1.0.

geozelot
  • 30,050
  • 4
  • 32
  • 56
jbalk
  • 7,505
  • 1
  • 17
  • 39

1 Answers1

3

In case not (have you checked the docs?), use a simple (K)NN search:

SELECT CASE WHEN ST_LineLocatePoint(geom, <point>) < 0.5
         THEN source
         ELSE target
       END as node
FROM   edges
ORDER BY
       geom <-> <point>
LIMIT   1
;

ST_LineLocatePoint in the CASE finds the fraction of line length of the projected point from <point> on the closest edge.geom, and selects either source or target.

A spatial index on edges is mandatory.

You can outsource the <point> creation into a CTE if you want.

You could also wrap this up in a function, but note that for a batch solution on a table of points (or more than one point) you'd better CROSS JOIN LATERAL the points table/expression to a (K)NN subquery. See e.g.

geozelot
  • 30,050
  • 4
  • 32
  • 56
  • Yes I checked the docs for pgr. I read through most of the manual and couldn't find any information about the pgr_pointToEdgeNode or a replacement for it. I know KNN search and this function was basically a wrapper for that search. I was hoping there was something similar in the latest version of pgr. I suppose I will create my own function using something similar to your answer. Another method using the pgr vertices table is SELECT id FROM ospr.ways_vertices_pgr ORDER BY the_geom <-> ST_SetSRID( ST_Point(-77.009003, 38.889931), 4326) limit 1 – jbalk May 18 '20 at 20:17
  • I guess that function predates some major improvements to the <-> operator that aren't easily implemented in an extension. But that's only a guess. Writing a function for one point is fine, better use the inline SQL solution for batch searches. Note that you may get different results with your alternative, as a vertex may be closer to point even if the point is right next to an edge. – geozelot May 18 '20 at 20:24
  • That makes sense. You are correct, your example should provide the best results. What makes me curious is why this function was deprecated. I thought features were usually deprecated because their functionality was replaced by a different function. I assumed this was the case with this function but I could not find any similar functions in pgr. Maybe I'm missing something. – jbalk May 18 '20 at 21:23
  • The function was deprecated to reduce the number of functions that need to be maintained and documented. A few functions made it into pgRouting over the years that didn't bring much value, so it was decided to focus on the core functionality. – dkastl May 18 '20 at 23:56