SQL 2005 – Finding nearest value from a column

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

 

[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

 

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

 

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

 

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

 

[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

 

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

 

[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

 

[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

 

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