3

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.

3

Assuming that road_segments table have geographies written in geog column you can try this query:

SELECT
    geog,
    dist
FROM
    (SELECT
        rs.geog,
        ST_Distance(rs.geog, ST_GeogFromText('POINT(-122.206111 47.983056)'), false) AS dist
    FROM
        public.road_segments AS rs) AS roads_with_distances
ORDER BY
    dist
LIMIT 1;
5

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;

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.