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
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.
Error with Visual Studio, connect server
MDX SSAS 2008 Last year calculation - Leap year issue
saving a VB timespan value in SQL database
test if an action it was an update in triggers
sql server clustered index on non unique keys
How to use MSSQL DSN as URL in DataSource.groovy
Value cannot be null, Parameter name: viewInfo
How to make recipient address dynamic ,so i can avoid making changes in SSIS package?
SQL Server 2005 - Check/Validate null values and add to results
Parse XML data in SQL Server 2012
Primary Key on existing data
Can't get SQL Server to start or connect to my local database engine through SSMS
Retrieve a uniform data sample from a table
Reading multiple records related to every record in the result set at a time
Schema Changes to Integrate Facebook and Google Login
file stream vs local save in sql server?