0

I have a road lines data in a table with field roads(id SERIAL ,the_line geometry(LineString,4326))

suppose an accident occurs in a middle or at any location in that waterline i want to stop the entire waterline so, given a location or coordinates(POINT(350205.805 148722.56)), I need to find a roadline which is closest to that point. and return its start point and endpoint

 "SELECT id, ST_AsText(ST_StartPoint(the_line )) AS startpoint,ST_AsText(ST_EndPoint(the_line )) AS endpoint from roadlines where (some condition to get the closest roadline) close to 'POINT(350205.805 148722.56)'"

POINT(350205.805 148722.56) WILL BE THE INPUT

geozelot
  • 30,050
  • 4
  • 32
  • 56
Pramit Sawant
  • 101
  • 2
  • while this is quite similar to other KNN related questions and indeed a duplicate topic of the post stated as duplicate, I strongly recommend to use the query structure below instead. – geozelot Sep 14 '18 at 09:02

1 Answers1

0

A Nearest Neighbor search with a single, constant comparator is fairly simple; order the table by distance and get the first row:

SELECT a.id,
       ST_StartPoint(rl.the_line) AS sp,
       ST_EndPoint(rl.the_line) AS ep
FROM roadlines AS rl
ORDER BY rl.the_line::geography <-> ST_Transform(ST_GeomFromText('POINT(350205.805 148722.56)', <your_srid>), 4326)::geography
LIMIT 1;

The tricky part is how to handle different projections and their units; transform your projected point (fill in <your_srid>) to WGS84 to match the lines CRS, then cast both to geography to get a precise distance measurement based on meter (on a sphere).

The <-> KNN operator makes efficient use of the spatial index on the roadlines geometry column.

If you intend to run a (K) Nearest Neighbor search between two tables (i.e. finding the closest line to each point in a second table, or vice versa), a JOIN LATERAL is the way to go. See this answer for the basic structure.

geozelot
  • 30,050
  • 4
  • 32
  • 56