find closest location with lat and long based on db data

Question

I want to find nearest location based on requested lat and lon from my database data. I am confused my code result that it's distance is right or wrong?

$lat= mysql_real_escape_string($_REQUEST['lat']);

$lng= mysql_real_escape_string($_REQUEST['lon']);
$multiplier = 112.12; 
$distance = 20;   

$sql=" SELECT beach.id,county.title,municipality.title,beach.beach_name,beach.description,beach.latitude,beach.longitude,beach.rainfall,beach.temperature,beach.status_id,beach.status_details,beach.notice, (SQRT(POW((beach.latitude - $lat), 2) + POW((beach.longitude - $lng), 2)) * $multiplier) AS distance 
from beach as beach,county as county,municipality as municipality where beach.county_id=county.id and beach.municipality_id=municipality.id  and POW((beach.latitude - $lat), 2) + POW((beach.longitude - $lng), 2) < POW(($distance / $multiplier), 2) ORDER BY distance ASC LIMIT 0 , 3";

How do you define your variable `$multiplier`? – Floris Dec 26 '13 at 12:49 — Floris, Dec 26 '13 at 12:49
$multiplier = 112.12; $distance = 20; – user3130599 Dec 26 '13 at 12:56 — user3130599, Dec 26 '13 at 12:56
Do you want distance in miles or kilometers? – Floris Dec 26 '13 at 13:03 — Floris, Dec 26 '13 at 13:03

Floris · Answer 1 · 2013-12-26T13:34:33.693

The correct function for (great circle) distance between two arbitrary points is a much more complicated formulae than you are using.

Assuming "closest beach" means

A) not near the north or South Pole

B) not very far

C) if you had two very similar distances you don't care if you get the "wrong" one

Then you can use an equation similar to the one you were using, but with a couple of fixes.

Specifically, your formula needs two factors. One factor (your multiplier converts from degrees to kilometers (or miles). The other factor accounts for the fact that the distance between two points of longitude is a function of latitude. In the code below I call these two factors $dmult (for distance) and $lmult (for latitude correction).

Simple example:

<?php
$lat = 45;
$lon = 72;
$lat2 = 46;
$lon2 = 73;
$lmult = cos($lat * pi() / 180.0);
$dmult = 40000 / 360; // circumference of earth in km divided by 360 degrees
$dist = $dmult * sqrt(pow($lmult *($lon-$lon2),2) + pow($lat-$lat2,2));
echo "The distance between these points is approximately".$dist."\n";
?>

See example at http://phpfiddle.org/lite/code/9j6-iet . It gives an approximate distance of 136 km. If you wanted the distance in miles, you would define

$dmult = 24900 / 360;  // circumference in miles divided by 360 degrees

Comparing the result (136 km) with an "accurate" online calculator: http://boulter.com/gps/distance/?from=N+45+0.0+W+72+0.0&to=N+46+0.0++W+73+0.0&units=k This gives the value 135.9 km - close enough for beach work…

Note - as the distances get bigger, the result of the above calculation will get more inaccurate, as it doesn't fully account for the curvature of the earth.

Let me know if you need help putting this into your query - it should be pretty straightforward.

EDIT it does appear that you are not concatenating the values of the variables into your query string - in other words, you look like you have things like "$multiplier" where you should have "112.12" . You want to check your query string looks right before executing it. Assign it to a variable (as you do), then print it out.

find closest location with lat and long based on db data

1 Answers1