How to find Nearest point on line from a point cordinate in SQL server

Question

I want to find out the nearest point on my road network which is a multiline string from a point with lat lon value.

The below code I have tried for finding buffer distance line from a point.

DECLARE @a geometry;
SET @a = geometry::Point(73.0127941495,19.26388944035,4326);
    SELECT top 1 * FROM Routes
    WHERE geom.STIntersects(@a.STBuffer(0.002)) = 1 order by @a.STDistance(geom);

I want a solution for finding Nearest point on line from a point cordinate

Peter Hill · Answer

This question is a couple of months old, but in SQL Server 2012, you can try and use the ShortestLineTo method.
Example (not tested):
DECLARE @a geometry;
SET @a = geometry::STPointFromText('POINT(73.0127941495 19.26388944035)',4326);

SELECT TOP (1) *, geom.ShortestLineTo(@a).STStartPoint() as closest_point_on_line
FROM Routes
WHERE geom.STDistance(@a) IS NOT NULL 
ORDER BY geom.STDistance(@a);

This should return the closest record from the table Routes based on  geom, along with the tangent point on geom to @a as long as they have the same SRID.

How to find Nearest point on line from a point cordinate in SQL server

One Answer

Add your own answers!

Ask a Question