CODEWITHSUNDEEP
sql-serversql-server-2012
csterling
csterling

Reputation: 714

Calculate Nearest Neighbor in SQL Server

I have 2 datasets, Fire Hydrants (3381 records) and Street Centerlines (6636 records). I would like to identify the Street nearest to each Hydrant record.

Following some SQL Server resources and tutorials I put together a script, but it has been running for over an hour now. I understand nearest neighbor may take awhile to run but it seems like something might be wrong in the logic. 

select 

h.OBJECTID,
st.FULL_ST_NAME

from WATER_HYDRANTS as h LEFT OUTER JOIN
STREET_CENTERLINES as st

on st.Shape.STDistance(h.SHAPE) is NOT NULL
ORDER BY st.Shape.STDistance(h.SHAPE) ASC

The reason I think something is wrong in the logic is because when I add a WHERE clause to select only one record with an ID, the query returns a list of the entire dataset. In the ObjectID column it is all the same value (e.g., 13992) and in the FULL_ST_NAME column is (I assume) a list of every street in ordered by proximity to the feature. 

select      

h.OBJECTID,
st.FULL_ST_NAME

from WATER_HYDRANTS as h LEFT OUTER JOIN
STREET_CENTERLINES as st
on st.Shape.STDistance(h.SHAPE) is NOT NULL

where h.OBJECTID = '13992'

ORDER BY st.Shape.STDistance(h.SHAPE) ASC

Ideally, each record in the objectID column will be unique and the FULL_ST_NAME column will have the street that is closest to each hydrant.

Please let me know if I can provide any other information. I tried to be thorough in my explanation and made an attempt at due diligence and research before coming to SO.

Thanks

Upvotes: 0

Views: 5075

Answers (1)

Edward
Edward

Reputation: 8596

Instead of LEFT OUTER JOIN to StreetCenterLines you need CROSS APPLY. The TOP 1 with ORDER BY STDistance inside the CROSS APPLY gives you the nearest street for each Hydrant. (Your original query was giving ALL the streets for each hydrant.)

Easier to show than to explain; it's like this:

select 
h.OBJECTID,
st2.FULL_ST_NAME
from WATER_HYDRANTS as h 
CROSS APPLY (SELECT TOP 1 st.FULL_ST_NAME 
  FROM STREET_CENTERLINES as st
  WHERE st.Shape.STDistance(h.SHAPE) IS NOT NULL
  ORDER BY st.Shape.STDistance(h.SHAPE) ASC) as st2

It might take a long time to run though, since for every hydrant it has to calculate the distance to every street and then find the shortest one.

Upvotes: 3

Related Questions