SQL 2005 – Finding nearest value from a column

[jmo21]

i haven't read it, but it looks good

http://msdn.microsoft.com/en-us/libr...8(SQL.90).aspx

took longer to write this post than find it using google

[Bob Dalek]

Use Oracle, instead.

[Spacecadet]

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

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

[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....ons/index.html

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

[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)

[scooterscot]

(not work related!) I am working on a GPS mapping application for use with my hobby.

The GPS produces output in longitude and Latitude – I would then like to run this against a place name table in order to find out the nearest town (I will then go on the work out distance and bearing to this town)

However I am having trouble finding the nearest value in my PlaceName table

[PlaceNames]. [Latitudes]
19.065925
19.107649
19.204109
19.445961
19.449831

If my GPS Latitude is 19.120000 I want to select the second row as the nearest value but if the GPS Latitude is 19.180000 then I want to select the third row.

Any suggestion on the select statement ??

Simple in excel...

vlookup(19.12,array,2<place name>,true<nearest value>)

[wxman]

To add to the above.. I need to find the bearing between to points - while i have the JavaScript to do this I am having difficulties converting this to T-SQL - google does not help me here

http://www.movable-type.co.uk/scripts/latlong.html

[wxman]

I am still working on this ....

Right now I have solved the LAT/LON distance issue on SQL 2005 what I do is first "box" select lat/lons by +-0.5 to each lat/lon to give me a sub set I then run the following function on each lat/lon and order by distance - this all seems to work VERy quickly


CREATE FUNCTION [dbo].[LatLonDistance]
(
@lat1Degrees decimal(15,12),
@lon1Degrees decimal(15,12),
@lat2Degrees decimal(15,12),
@lon2Degrees decimal(15,12)
)
RETURNS decimal(9,4)
AS
BEGIN

DECLARE @earthSphereRadiusNauticalMiles as decimal(10,6)
DECLARE @nauticalMileConversionToMilesFactor as decimal(7,6)
SELECT @earthSphereRadiusNauticalMiles = 6366.707019
SELECT @nauticalMileConversionToMilesFactor = .621371

-- convert degrees to radians
DECLARE @lat1Radians decimal(15,12)
DECLARE @lon1Radians decimal(15,12)
DECLARE @lat2Radians decimal(15,12)
DECLARE @lon2Radians decimal(15,12)
SELECT @lat1Radians = (@lat1Degrees / 180) * PI()
SELECT @lon1Radians = (@lon1Degrees / 180) * PI()
SELECT @lat2Radians = (@lat2Degrees / 180) * PI()
SELECT @lon2Radians = (@lon2Degrees / 180) * PI()

-- formula for distance from [lat1,lon1] to [lat2,lon2]
RETURN ROUND(2 * ASIN(SQRT(POWER(SIN((@lat1Radians - @lat2Radians) / 2) ,2)
+ COS(@lat1Radians) * COS(@lat2Radians) * POWER(SIN((@lon1Radians - @lon2Radians) / 2), 2)))
* (@earthSphereRadiusNauticalMiles * @nauticalMileConversionToMilesFactor), 4)

END

[TimberWolf]

Although it uses Mysql, this link is good (look at pages 8-13). The sql involved should be very similar

GeoDistance Search

Here's another interesting piece (on a mysql forum)

Mysql Spatial Forum

WHS, use the Haversine formula rather than the law of cosines (the latter assumes a spherical earth and small errors become big ones), though strictly speaking you only need to calculate and compare squared distances and also need not multiply each by the constant for each row retrieved after the BETWEEN clause has weeded out obvious non-candidate rows. Not that calculating square roots unnecessarily is such a big deal on modern processors, it just bugs me.

[MrMark]

Although it uses Mysql, this link is good (look at pages 8-13). The sql involved should be very similar

GeoDistance Search

Here's another interesting piece (on a mysql forum)

Mysql Spatial Forum

[EvilWeevil]

Any suggestion on the select statement ??

1. You need to consider longitude as well as latitude, otherwise Bristol and London, for example, would be the same.

2. So you need to calculate the distance, using pythagoras by approximating to a flat surface.

3. So once you've calculated the distance from your location, sort on it.

Select top 1 * from places order by [distance calculation]

4. And then you need to think about how to cope with odd shapes, so you don't conclude that Southend is the closest town to Margate, for example.

[Spacecadet]

try this

select * 
into #placenames
from (
select 'place1' as place, 19.065925 as latitude union all
select 'place2' as place, 19.107649 as latitude union all
select 'place3' as place, 19.204109 as latitude union all
select 'place4' as place, 19.445961 as latitude union all
select 'place5' as place, 19.449831 as latitude ) T1

declare @latitude float

--set @latitude = 19.120000
set @latitude = 19.180000 

select place, latitude, abs(latitude-@latitude) as diff
from #placenames
where abs(latitude-@latitude) = (select min(abs(latitude-@latitude)) from #placenames)

drop table #placenames

I demand credits for any TV shows you use this in!!

[Weltchy]

Surely the row that you want to return would be the row where the (@variablelatitude - row latitude) = min((@variablelatitude - row latitude)), excluding sign. Or something like that.

You can use the ABS function to negate sign

So, something like

SELECT * FROM Table HAVING ABS(@latitude - Table.Latitude) = MIN(ABS(@latitude - Table.Latitude))

Or something like that.

PS - SELECT * FROM Table..... Just for you DP!!!!!

[themistry]

SQL Server may not be the best for performing spatial database queries.

Have a google, there are other options that specialise in what you want to do.

As per the SQL statement, someone more qualified may be better to answer.

TM

[wxman]

(not work related!) I am working on a GPS mapping application for use with my hobby.

The GPS produces output in longitude and Latitude – I would then like to run this against a place name table in order to find out the nearest town (I will then go on the work out distance and bearing to this town)

However I am having trouble finding the nearest value in my PlaceName table

[PlaceNames]. [Latitudes]
19.065925
19.107649
19.204109
19.445961
19.449831

If my GPS Latitude is 19.120000 I want to select the second row as the nearest value but if the GPS Latitude is 19.180000 then I want to select the third row.

Any suggestion on the select statement ??