• Wednesday, August 15, 2012 5:52 AM

it has a longitude and latitude column they are varchar (not sure if should be float)

i need to be able to query the table and order by nearest to my current position

i dont have a clue

we run sql2008 r2, coldfusion

### All Replies

• Wednesday, August 15, 2012 6:30 AM

This type of query is called a "nearest neighbour" query. I've written several articles about nearest neighbour queries in SQL Server on my blog, and you'll also find it discussed here several times.

However, one thing to be aware of is that if your directoryaddresses table considers each address to be a single location, and you only ever want to work out the distance between two points, you don't really need to use spatial queries at all. SQL Server spatial functionality is designed for more complex spatial queries involving Polygons, LineStrings, tests of intersection, calculating proximity and area of topological relationships etc. To calculate the approximate distance between two points expressed using lat/long coordinates you can use the Haversine formula, as shown here: http://www.movable-type.co.uk/scripts/latlong.html which will be easier and probably quicker for you.

• Wednesday, August 15, 2012 6:48 AM

any idea on how i would put that into a select statement, or would i have to loop through the query and set distance using the formula

Ben Holland Maxmoment

• Wednesday, August 15, 2012 7:08 AM

First create a function that accepts a pair of lat/long coordinates and calculates the distance between them. Something like this (gives the result in miles - to use other unit of measurement adjust the constant 3959 to the radius of the earth in whatever units you want instead):

```CREATE FUNCTION dbo.CalculateDistanceBetweenCoords (
@Longitude1 float,
@Latitude1 float,
@Longitude2 float,
@Latitude2 float
) RETURNS float
AS
BEGIN
DECLARE @result float;

SET @result = sin(@Latitude1/57.2957795130823) * sin(@Latitude2/57.2957795130823) + cos(@Latitude1/57.2957795130823) * cos(@Latitude2/57.2957795130823) * cos(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823);

--Check value lies within range for acos
IF @result > 1
SET @result = 1;
ELSE IF @result < -1
SET @result = -1;

RETURN (3959 * acos(@result) );

END;```

Then use this to sort the results of your SELECT query and select the closest record:

```SELECT TOP 1
FROM
ORDER BY

Pro Spatial with SQL Server 2012

• Marked As Answer by Wednesday, August 15, 2012 8:27 AM
•
• Wednesday, August 15, 2012 8:27 AM

you are a god !!!!

that worked brilliant,

thank you so much

Ben Holland Maxmoment