Return to Revisions

Use your query structure to get the closest edge to P and project a closest point P' from P on the edge with ST_ClosestPoint:

SELECT edge.id,
       ST_ClosestPoint(edge.geom, <P>) AS "P'"
FROM (
    SELECT <edges_id> AS id,
           <edges_geom> AS geom
    FROM <edges_table>
    ORDER BY ST_Distance(<P>, <edges_geom>)
    LIMIT 1
) AS edge

EDIT:

ST_Distance in conjunction with LatLon (e.g. EPSG:4326) is a tricky business. While it might work here, with sufficiently small distances, do at least not use it to derive metric distances (this does equally apply to the KNN operators, e.g. <->). For a more robust and precise solution, consider casting to geography prior to the calculations (e.g. ST_Distance(<P>::geography, <edges_geom>::geography))