sql-server


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.

Related Links

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?

Categories

HOME
paypal
sass
project-management
amazon-swf
sed
aem
docker-swarm
parse-server
codeeffects
appmaker
webdav
laravel-4
rebol
algorithmic-trading
iis-7.5
simpy
keystore
cartodb
uiview
rebol2
datagrip
rapidjson
opera-mini
factor-analysis
chocolatey
google-api-java-client
ip-camera
koa
backpropagation
restfb
novnc
osmdroid
cep
ivy
freebase
shapes
phpspreadsheet
windows-10-iot-core
color-scheme
sql-like
visualstudio.testtools
ddms
stdclass
nsexception
knockout-3.0
drawingarea
greenhills
castle-dynamicproxy
sqlexception
verbose
software-product-lines
try-finally
contour
mongo-c-driver
freefem++
concur
sun-codemodel
portfolio
acm
document-oriented-db
app.xaml
kendo-menu
base32
debian-based
shift-jis
mdm-zinc
ticket-system
tcpreplay
ember-components
author
system.web
emma
aquafold
neoload
dbsetup
anjuta
openkinect
winrt-httpclient
fogbugz-api
zend-lucene
reporting-tools
pys60
attachevent
pstree
legacy-code
law-of-demeter
objectbrowser
throttling
dashcode
ruby-debug
libavformat
getresource
msn
querypath
unreachable-code
external-assemblies
integer-promotion
rd

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App