This question already has an answer here:

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

marked as duplicate by Ian Turton Sep 14 '18 at 8:30

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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. – ThingumaBob Sep 14 '18 at 9:02

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

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.