CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008geolocation
Scott Selby
Scott Selby

Reputation: 9570

radius search by latitude / longitude

I have found a bunch of answers for this question using mysql , but I wasn't able to convert anything into a query ms sql 2008 can use. I have a longitude and latitude column for each row in the database. I am going to have a latitude and longitude for where the user is. I want to be able to find all rows that are within x miles from the user's latitude/longitude. Also when trying to use other queries I found on SO I keep getting the error - 'pow' is not a recognized built-in function name. which is weird , because I'm pretty sure that I have used pow before in sql 2008. Any help with this would be greatly appreciated. So far this is the closest could come up with.

select * from tbl_MyTable
WHERE (
POW( ( 69.1 * ( Longitude - @longitude ) * cos( @latitude / 57.3 ) ) , 2 ) + POW( ( 69.1 * ( Latitude - @latitude ) ) , 2 )
) < ( 5 *5 );

Upvotes: 23

Views: 31054

Answers (3)

Ben Thul
Ben Thul

Reputation: 32687

Since you're on SQL 2008, consider using the native geospatial capabilities. You can do fancy things like:

  • Create a persisted computed column of geography type that represents your point.
  • Create a spatial index on the computed column. This will make things like yourPoint.STDistance(@otherPoint) <= @distance efficient

Like so:

alter table [yourTable] add [p] as geography::Point(Latitude, Longitude, 4326) persisted;
create spatial index [yourSpatialIndex] on [yourTable] ([p])

declare @Latitude float = <somevalue>, @Longitude float = <somevalue>;
declare @point geography = geography::Point(@Latitude, @Longitude, 4326);
declare @distance int = <distance in meters>;

select * from [yourTable] where @point.STDistance([p]) <= @distance;

Upvotes: 47

Vignesh Raja
Vignesh Raja

Reputation: 610

DECLARE @CurrentLocation geography; 
SET @CurrentLocation  = geography::Point(12.822222, 80.222222, 4326)

SELECT * , Round (GeoLocation.STDistance(@CurrentLocation ),0) AS Distance FROM [Landmark]
WHERE GeoLocation.STDistance(@CurrentLocation )<= 2000 -- 2 Km

Wonderful Tutorial

http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx

Upvotes: 7

Derek Tomes
Derek Tomes

Reputation: 4007

I think you want POWER not POW

http://msdn.microsoft.com/en-us/library/ms174276.aspx

Upvotes: 5

Related Questions