SQL 2005 – Finding nearest value from a column

[wxman]

Finally got the Lat / Lon bearing working this morning in SQL (well MS SQL 2005)

For those who are intrested here it is - result in whole degrees....

DECLARE @lat1 float
DECLARE @lon1 float
DECLARE @lat2 float
DECLARE @lon2 float

SET @lat1 = 37.286771
SET @lon1 = -97.892715
SET @lat2 = 37.22495
SET @lon2 = -97.95307

DECLARE @y float
DECLARE @x float
DECLARE @dLon float
DECLARE @brng DECIMAL(20,10)
DECLARE @result VARCHAR

SET @lat1 = RADIANS(@lat1)
SET @lat2 = RADIANS(@lat2)

SET @dLon = RADIANS(@lon2 - @lon1)
SET @y = SIN(@dLon) * COS(@lat2)
SET @x = (COS(@lat1) * SIN(@lat2)) - (SIN(@lat1) * COS(@lat2) * COS(@dLon))
SET @brng = (ATN2(@y, @x) * 180) / 3.14159265358979323846
SET @Brng = (@Brng+360) % 360;

SELECT CAST(@brng as INT)

 

[chuckylefrek]

I am seeking a list of UK towns with their associated longitude and latitude. I am hoping someone on this thread can suggest a source for such a list.

Ideally I would like to have the following:

town | county | longitude | latitude

Something similar to what is returned by http://freepages.genealogy.rootsweb.ancestry.com/~agene/locations/index.html

I don't mind paying for such a list but I just cannot find one anywhere.

 

[Spacecadet]

xyz maps might have something for you:
http://xyzmaps.com/

as well as traditional paper maps, they also supply electronic map data

 

[Bob Dalek]

Use Oracle, instead.

 

[jmo21]

i haven't read it, but it looks good

http://msdn.microsoft.com/en-us/library/aa964138(SQL.90).aspx

took longer to write this post than find it using google