i am terrible at spatial sql please help - order by distance from my position

Question

I have a table of addresses called directoryaddresses

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

Thank you in advance

Answer 1

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.

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

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

Answer 3

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
  Address
FROM
  DirectoryAddresses
ORDER BY
  DistanceBetweenCoords(addresslong, addresslat, currentlong, currentlat) ASC;

Pro Spatial with SQL Server 2012

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 4

you are a god !!!!

that worked brilliant,

thank you so much

---

Ben Holland Maxmoment