PostGIS Proximity – Finding Closest Segment to a Point Using PostGIS

postgisproximity

I am trying to find the closest road segment to a point that I have and return an attribute from the closest segment to the given points.

I know in PostGIS I need to use ST_ClosestPoint, St_distance and min functions, but I keep having syntax errors.

So far my code looks like

WITH closest_segment As
)
 SELECT
  ST_ClosestPoint(ST_GeogFromText('POINT(-122.206111 47.983056)')::GEOMETRY,   
    shape::GEOMETRY)AS segment_location  --shape is the geography WKB    

 FROM
  public.road_segments
)
 RETURNING closest_segment
WITH new_segment AS
)
 SELECT 
  MiN(ST_Distance(closest_segment::GEOM,ST_GeogFromText('POINT(-122.206111 
   47.983056)')::GEOMETRY) 
 FROM 
  closest_segment
)
 RETURNING closest_segment

My knowledge of PostGIS and coding in it is very limited.

Best Answer

Assuming that you are searching for the closest line edge to your point, you can do a K-Nearest Neighbour (KNN, or 1NN in this case), based on this answer. However you want to use the <#> operator instead, since that operates on the edges of the bounding boxes, rather than the centroids.

SELECT *
FROM public.road_segments r
ORDER BY r.geom <-> ST_GeomFromText('POINT(-122.206111 47.983056)')
LIMIT 1;
Related Question