SQL Server Geography datatype nearest point on line

gis, sql, sql-server, sql-server-2008, t-sql

Solution

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 GEOGRAPHY
SET @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    num
CROSS 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 mp
CROSS 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

Problem

I am trying to build a query, but I am having some difficulty. I have a SQL Server 2008 database with a table that includes, among other fields, a geography field that describes road segments. (This data has been imported from TIGER/Line data from the U.S. Census.) I have another fixed point describing a user's location. I want to find the closest road segment in the database to that point, but I can't seem to figure out how to accomplish this. Furthermore, I want to find the closest point on that segment to the user location point. This is what I want to select and return back in my query. Does anyone have any experience with the geography/geometry functionality that can help me? Thanks!

Original source