Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

radius search by latitude / longitude

Tags:

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 );
like image 510
Scott Selby Avatar asked Mar 26 '13 03:03

Scott Selby


People also ask

How do you find the radius using latitude and longitude?

A circle of latitude at latitude lat=1.3963 rad has the radius Rs = R · cos(lat) = 1106 km, so d=1000 km now corresponds to an angular radius of rs = d/Rs = d/(R · cos(lat)) = 0.9039. Hence, covering d=1000 km on a circle of latitude gets you to longitude lonS = lon ± d/(R · cos(lat)) = -0.6981 rad ± 0.9039 rad.

How do you find the distance between two latitude longitude points?

from math import cos, asin, sqrt, pi def distance(lat1, lon1, lat2, lon2): p = pi/180 a = 0.5 - cos((lat2-lat1)*p)/2 + cos(lat1*p) * cos(lat2*p) * (1-cos((lon2-lon1)*p))/2 return 12742 * asin(sqrt(a)) #2*R*asin... And for the sake of completeness: Haversine on Wikipedia.

How can I find the distance between two points in MySQL?

Calculating Distance in MySQL To get the distance between two points, you call the function with the two points as the arguments: -- Returns distance in meters.


1 Answers

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;
like image 70
Ben Thul Avatar answered Dec 29 '22 01:12

Ben Thul