I need to write a query that allows me to find all the locations within the range (Miles) from the provided location.
The table looks something like this:
id | name | lat | lng
So, I am doing research and found: this is my sql presentation
I tested it on a table with 100 rows and get a lot more! - Must be scalable.
I first tried something simpler:
//just some test data this would be required by user input
set @orig_lat=55.857807; set @orig_lng=-4.242511; set @dist=10;
SELECT *, 3956 * 2 * ASIN(
SQRT( POWER(SIN((orig.lat - abs(dest.lat)) * pi()/180 / 2), 2)
+ COS(orig.lat * pi()/180 ) * COS(abs(dest.lat) * pi()/180)
* POWER(SIN((orig.lng - dest.lng) * pi()/180 / 2), 2) ))
AS distance
FROM locations dest, locations orig
WHERE orig.id = '1'
HAVING distance < 1
ORDER BY distance;
This returns lines around 50 ms , which is very good! However, this will significantly slow down the growth of lines.
EXPLAIN shows this only using the obvious PRIMARY key.
Then after reading the related article above . I tried something like this:
// defining variables - this when made into a stored procedure will call
// the values with a SELECT query.
set @mylon = -4.242511;
set @mylat = 55.857807;
set @dist = 0.5;
set @lon1 = @mylon-@dist/abs(cos(radians(@mylat))*69);
set @lon2 = @mylon+@dist/abs(cos(radians(@mylat))*69);
set @lat1 = @mylat-(@dist/69);
set @lat2 = @mylat+(@dist/69);
SELECT *, 3956 * 2 * ASIN(
SQRT( POWER(SIN((@mylat - abs(dest.lat)) * pi()/180 / 2) ,2)
+ COS(@mylat * pi()/180 ) * COS(abs(dest.lat) * pi()/180)
* POWER(SIN((@mylon - dest.lng) * pi()/180 / 2), 2) ))
AS distance
FROM locations dest
WHERE dest.lng BETWEEN @lon1 AND @lon2
AND dest.lat BETWEEN @lat1 AND @lat2
HAVING distance < @dist
ORDER BY distance;
240 , , . , . EXPLAIN lat, lng PRIMARY PRIMARY.
???
, lat lng POINT(); , , ?
!
!
-Stefan
UPDATE:
, , :
abs() lat. id WHERE , . , - .
EXPLAIN , lng 180 , .