Closest line to a point using PostGIS?

Question

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?

Answer 1

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