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.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Kyle K
  • 289
  • 2
  • 8
  • 1
    Why are creating a geography and then casting it to a geometry? ST_GeogFromText('POINT(-122.206111 47.983056)')::GEOMETRY What SRID is your data in? See also https://gis.stackexchange.com/questions/6681/what-are-the-pros-and-cons-of-postgis-geography-and-geometry-types – raphael Dec 03 '15 at 03:18

2 Answers2

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;
raphael
  • 3,407
  • 23
  • 61
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;
alphabetasoup
  • 8,718
  • 4
  • 38
  • 78
DamnBack
  • 223
  • 2
  • 7