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.