SQL Server 2008: Spatial Query - Return 5 closest sites

Question

I'm trying to create an origin/destination matrix selection with SQL Server 2008. I want to find the closest 5 sites to any given site.

The matrix should include the origin ID, Destination ID and the distance between the two. So far I have managed to get something working for one site, but I want to loop through every row in my table. I've hit a wall in working out how to do this, could anybody help? I only want to return a destination if they are within 2.5km of the origin site.

The working code for my one origin site is below (I want the same output, but including all rows as an origin):

SP_Geometry is my geography column (MapInfo names this column by default when using EasyLoader)

DECLARE @Point1 AS Geography
DECLARE @Point1ID AS Nvarchar (255)
SELECT @Point1=SP_Geometry FROM SitesTable WHERE Label = 'ID1'
SELECT @Point1ID = Label FROM SitesTable WHERE Label = 'ID1'

SELECT TOP 5
@Point1ID AS Origin
,@Point1 AS Origin_SP_Geometry
,@Point1.STDistance(SP_Geometry) AS Distance
,@Label AS Destination
,SP_Geometry AS Destination_SP_Geometry

FROM SiteTable
WHERE @Point1.STDistance(SP_Geometry) <2500
ORDER BY @Point1.STDistance(SP_Geometry)

Running the above results in the following selection:

+--------+---------------------+-------------+-------------+----------------------------+
| Origin | Origin_SP_GEOMETRY  |  Distance   | Destination |  Destination_SP_GEOMETRY   |
+--------+---------------------+-------------+-------------+----------------------------+
| ID1    | 0xE6100000010CDD(…) | 0           | ID1         | 0xE6100000010CDD772D9D(…)  |
| ID1    | 0xE6100000010CDD(…) | 395.7739586 | ID867       | 0xE6100000010C2466CDFA5(…) |
| ID1    | 0xE6100000010CDD(…) | 407.6394398 | ID2500      | 0xE6100000010C6FBC54(…)    |
| ID1    | 0xE6100000010CDD(…) | 1033.827269 | ID91        | 0xE6100000010C3981C0353(…) |
| ID1    | 0xE6100000010CDD(…) | 1082.667065 | ID1540      | 0xE6100000010CD03BFCD2(…)  |
+--------+---------------------+-------------+-------------+----------------------------+

Ideally this is exactly what I want, but am having trouble establishing any kind of loop (that would union origin ID2, ID3 etc.)

Any help would be much appreciated!

score 0 · Accepted Answer · edited Jan 02 '15 at 09:22

Try using the RANK function. I can't test since I don't have sample data so this might need a tweak but should be close...

;WITH cteDistances AS (
    SELECT 
         origin.Label AS OriginId
        ,dest.Label AS DestinationId
        ,origin.SP_Geometry.STDistance(dest.SP_Geometry) AS Distance
        ,RANK() OVER (PARTITION BY origin.Label ORDER BY origin.SP_Geometry.STDistance(dest.SP_Geometry)) AS DistanceRank
    FROM 
        SiteTable origin
        INNER JOIN SiteTable dest ON (dest.Label <> origin.Label)
    WHERE 
        origin.SP_Geometry.STDistance(dest.SP_Geometry) < 2500
)       

SELECT 
    OriginId, DestinationId, Distance
FROM 
    cteDistances 
WHERE 
    DistanceRank <= 5

SQL Server 2008: Spatial Query - Return 5 closest sites

1 Answers1