Nearest Neighbour - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nearest Neighbour

Author

Topic

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2012-10-30 : 21:30:29

Hi...

The same old nearest neighbour question as everyone else seems to have.

I have a database of about 3500 places in a table named "sites". There is a latitude and longitude column with coordinates in decimal format.

eg.
SITES (name, latitude, longitude)
-------------------------------
This place, 43.44, -73.33
Toronto CN Tower, 43.33, -80.22
My house, 42.333, -81.432

Currently I have a search option that allows a person to search for places within a proximity of xxx kilometres. The search uses an algorithim to find places within those xxx kilometres.

First I obtain a latitude and longitude of the city they are searching from, which is the centre point if you look at it as a circle.

Then I loop through ALL of the 3500 locations, selecting the latitude and longitude with a SELECT statement and find the distance using a function.

Lat1 and Long1 are the centre point, Lat2 and Long2 is each of the 3500 rows in the database. Unit is "K" for kilometres.

function GetDistance(Lat1, Long1, Lat2, Long2)
dim x
' do the nasty calcs
x = (sin(DegToRads(Lat1)) * sin(DegToRads(Lat2)) + cos(DegToRads(Lat1)) * _
cos(DegToRads(Lat2)) * cos(abs((DegToRads(long2))-(DegToRads(long1)))))
' Get Acos(x)
x = atn((sqr(1-x^2))/x)

' Get distance in kilometers
GetDistance = 1.852 * 60.0 * ((x/pi)*180)

end function

'::: This function converts decimal degrees (e.g. 79.1928376) to radians :::

function DegToRads(Deg)
DegToRads = cdbl(Deg * 3.14159265358979323846 / 180)
end function

Anyways, it is crude and the more entries that are created in the table over time, the longer the query will take. I know that instead of querying 3500 rows, a SQL statement can be made with the mathematical equation in it instead.

I've seen examples of this but they use stored procedures which I'm not familiar with, and have limited knowledge of SQL.

Is there a SQL command that will work instead of 3500 individual records being checked?

This failed to work, it's in mysql but it came close... 43.44 and -80.33 were the centre point and 25 is the distance (radius).

SELECT latitude, longitude, SQRT(
POWER(69.1 * (latitude - 43.44), 2) +
POWER(69.1 * (-80.33 - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM sites where distance < 25 ORDER BY distance;

I get 'invalid column name distance' even though it uses the "AS Distance" command.

I'm using SQL 2008.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-30 : 21:57:23

Take a look at the geography data type introduced in SQL Server 2008. There are methods for calculating distance, plus it can be indexed for these kinds of operations.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources