Find closest latitude & longitude match

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Find closest latitude & longitude match

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2011-07-06 : 11:26:08

Hi,

Sounds like a simple query this. We have a table of latitudes & longitudes that relate to various other values in our DB:


CREATE TABLE [GeoSpatial](
	[intRowId] [bigint] NOT NULL,
	[fltLatitude] [float] NOT NULL,
	[fltLongitude] [float] NOT NULL,

I have incoming data for a user that includes their latitude and longitude, and I need to find the row in the above table that is the closest geographical match.

It isn't critical that this is 100% accurate. So it's permissable to find the closest match in latitude and longitude values rather than trying to convert that to an actual geographic location.

Cheers,
Matt

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-06 : 11:40:23

select top 1 *
from tbl
order by abs(fltLatitude-@fltLatitude) + abs(fltLongitude-@fltLongitude) desc

or a bit better
select top 1 *
from tbl
order by power(fltLatitude-@fltLatitude,2) + power(fltLongitude-@fltLongitude,2) desc

Neither will be very quick though.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mattt
Posting Yak Master

194 Posts

Posted - 2011-07-06 : 12:02:33

Thanks very much for the suggestion - your time is much appreciated - but that doesn't seem to work terribly well. I think the fact that some of the values are negative is screwing it up. For example, this:


  declare @fltLatitude float
  set @fltLatitude =43.5
  
  declare @fltLongitude float
  set @fltLongitude = 5.5
  
  select top 1 *
from [GeoSpatial]
order by power(fltLatitude-@fltLatitude,2) + power(fltLongitude-@fltLongitude,2) desc

Returns the values 54.9884, -7.29968 even though there is the value pair 43.4212, 5.28414 in the table which is clearly a better match.

mattt
Posting Yak Master

194 Posts

Posted - 2011-07-06 : 12:14:19

Ah, it was just ordered upside down ... needs an asc at the end!

Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-06 : 12:36:24

oops

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources