SQL Server Geography datatype nearest point on line

You can store your objects in a GEOGRAPHY column and create a SPATIAL INDEX over this column.

Unfortunately, SQL Server implements spatial indexes by tiling the surface and storing the tile identifiers in a plain B-Tree index, so plain ORDER BY STDistance won't work (well, it will work but won't use the index).

Instead, you'll have to make a query similar to this:

DECLARE @mypoint GEOGRAPHYSET @mypoint = geography::STGeomFromText('POINT(@mylat, @mylon)', 4326);WITH    num (distance) AS        (        SELECT  1000        UNION ALL        SELECT  distance + 1000        FROM    num        WHERE   distance <= 50000        )SELECT  TOP 1 m.*FROM    numCROSS APPLY        (        SELECT  TOP 1 *        FROM    mytable        WHERE   myroad.STDistance(@mypoint) <= distance        ORDER BY                STDistance(@mypoint)        ) m

This way, SQL Server will first search roads within 1 kilometer from your point, then within 2 kilometers, etc., each time using the index.

Update:

If you have multiple points in a table and want to find the closest point for each of them:

WITH    num (distance) AS        (        SELECT  1000        UNION ALL        SELECT  distance + 1000        FROM    num        WHERE   distance <= 50000        )SELECT  mp.mypoint, m.*FROM    @mypoints mpCROSS APPLY        (        SELECT  TOP 1 m.*        FROM    num        CROSS APPLY                (                SELECT  TOP 1 *                FROM    mytable                WHERE   myroad.STDistance(@mypoint) <= distance                ORDER BY                        STDistance(@mypoint)                ) m        ) m

How are your road segments stored? lat and long? if so, you can convert them to radians, and do the math:

• http://www.zipcodeworld.com/samples/distance.mssql.html
• http://www.petefreitag.com/item/622.cfm
• http://johndyer.name/post/2005/07/11/LatitudeLongitude-Distance-Calculation-in-SQL-Server.aspx