radius search by latitude / longitude

database, geolocation, sql-server, sql-server-2008

Solution

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;

Problem

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 ); ```

Original source