2

I have a table of roads and a table of points. I'd like for each point to have the ID of the closest road, as well as the distance to that road.

The line data has both the path type and the geometry type for its coordinates. The points have XY coordinates. What would be the best way to go about finding the closest road using PostGIS?

2

You can pass in the point XY to ST_DWithin to return the ID of the closest road, as well as the distance to that road:

SELECT DISTINCT ON (p.id) p.id, r.id, ST_Distance(ST_SetSRID(ST_Makepoint(p.x, p.y),4326), r.geom)
FROM points p
    LEFT JOIN roads r ON ST_DWithin(ST_SetSRID(ST_Makepoint(p.x, p.y),4326), r.geom, 30.48)
ORDER BY p.id, ST_Distance(ST_SetSRID(ST_Makepoint(p.x, p.y),4326), r.geom);

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.