How to obtain points within radius with latitude and longitude?

Hi Experts!!

I have a database with certain locations, and their corresponding latitude and longitude fields. These are expressed in decimal format.

I want to create a PHP program or SQL query, in order to know which of these locations are within an X radius from a source point.

I will tell the program my current latitude and longitude (decimal) and a radius like "1 kilometer". I want to know which locations in the DB are inside that radius.

Thanks a lot!!
glopezzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

karoldvlCommented:
How much precision do you need? And is this always like "1 kilometer" or can it be 1500 km?

Normally you would need some calculations to get the distances: http://en.wikipedia.org/wiki/Haversine_formula

You can read more elaborate explanation here:
http://stackoverflow.com/questions/2096385/formulas-to-calculate-geo-proximity

This can prove helpful too:
http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

0
glopezzAuthor Commented:
Precision must be within 1 km. That's the kind of range needed for my app.

Thanks!
0
Ray PaseurCommented:
This will compute the distance between two LAT/LON pairs.  The design pattern you might want to follow would go something like this...

Get your current LAT/LON geocode data.
Add some amounts to it, perhaps + / - 0.1 to create a "buffer zone" that is bigger than 1Km in any direction.
Select the values from the data base that are inside the buffer zone - create a temporary table in memory with this selection.
Run through the table, computing the distance from your geocode to each row's geocode - store the computed distance in the temporary table.
Select from the temporary table, order by the computed distance.

HTH, ~Ray
<?php // RAY_compute_distance.php
error_reporting(E_ALL);
echo "<pre>\n";

// COMPUTE THE DISTANCE BETWEEN TWO LAT/LON PAIRS

// MAN PAGE: http://en.wikipedia.org/wiki/Haversine_formula
function compute_distance($from_lat, $from_lon, $to_lat, $to_lon, $units='KM')
{
    $units = strtoupper(substr(trim($units),0,1));
    // ENSURE THAT ALL ARE FLOATING POINT VALUES
    $from_lat = floatval($from_lat);
    $from_lon = floatval($from_lon);
    $to_lat   = floatval($to_lat);
    $to_lon   = floatval($to_lon);

    // IF THE SAME POINT
    if ( ($from_lat == $to_lat) && ($from_lon == $to_lon) )
    {
        return 0.0;
    }

    // COMPUTE THE DISTANCE WITH THE HAVERSINE FORMULA
    $distance = acos( sin(deg2rad($from_lat))
              * sin(deg2rad($to_lat))
              + cos(deg2rad($from_lat))
              * cos(deg2rad($to_lat))
              * cos(deg2rad($from_lon - $to_lon)) );

    $distance = rad2deg($distance);

    // DISTANCE IN MILES AND KM - ADD OTHERS IF NEEDED
    $miles = (float) $distance * 69.0;
    $km    = (float) $miles * 1.61;

    // RETURN MILES
    if ($units == 'M') return round($miles,1);

    // RETURN KILOMETERS = MILES * 1.61
    if ($units == 'K') return round($km,2);
}

if (!empty($_GET))
{
    $distance = compute_distance($_GET["a_lat"], $_GET["a_lon"], $_GET["b_lat"], $_GET["b_lon"], $_GET["units"]);
    echo $distance . ' ' . $_GET["units"];
}
?>


<a target="_blank" href="http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=camp+david+geolocation&sll=39.648333,-77.465&sspn=0.025972,0.057807&ie=UTF8&z=15&iwloc=lyrftr:m,10141899780758213082,39.648361,-77.465029">Camp David</a>
<a target="_blanl" href="http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=White+House+Washington,+DC&sll=39.806296,-77.097044&sspn=0.414606,0.924911&ie=UTF8&hq=White+House&hnear=White+House,+Washington,+DC&ll=38.898047,-77.036562&spn=0.025417,0.057807&z=15&iwloc=A">White House</a>
TEST IT HERE:
<form>
POINT A LAT <input name="a_lat" value="38.898047"> LON <input name="a_lon" value="-77.036562" />
POINT B LAT <input name="b_lat" value="39.737554"> LON <input name="b_lon" value="-77.464943" />
<input type="radio" name="units" value="miles" checked="checked" />Miles
<input type="radio" name="units" value="km" />Kilometers
<input type="submit" />
</form>

Open in new window

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

glopezzAuthor Commented:
Hi Ray, thank you very much for your input. The solution sounds very good, I am testing the script right now. I will just need a little more help on the database selection inside the buffer zone.

Let's suppose that my current LAT/LON geocode data is: 19.0,-99.0

I add and substract  .00900 to each, since I've more less seen this is equivalent to 1 KM (I don't know if this relation applies to all  Earth, however it does apply to my city). The final result would give me a "square", consisting of:

Upper Left corner: 19.009,-99.009
Upper Right corner: 19.009,-98.991
Lower Left corner: 18.991,-99.009
Lower Right corner: 18.991,-98.991

What would your recommended query be?

Thanks a lot!
0
Ray PaseurCommented:
Here is some code extracted from one of my proximity match algorithms.  It has some local functions, and I would not generally recommend the use of extract(), but hopefully it will show you what the logic is for this app.  Best regards, ~Ray
// COMPUTE THE MIN AND MAX LAT/LON PAIRS
$minlat = $p_latitude  - LATITUDE_OFFSET;
$maxlat = $p_latitude  + LATITUDE_OFFSET;
$minlon = $p_longitude - LONGITUDE_OFFSET;
$maxlon = $p_longitude + LONGITUDE_OFFSET;


// FIND NEARBY
$tsql = "CREATE TEMPORARY TABLE nearby ( ";
$tsql .= "distance    decimal(6,1) ";
$tsql .= " ) ENGINE=MEMORY SELECT * FROM locations WHERE ";
$tsql .= "( p_latitude BETWEEN $minlat AND $maxlat ) AND ( p_longitude BETWEEN $minlon AND $maxlon ) ";
if (!$t = mysql_query("$tsql", $db_connection)) { fatal_query_error($tsql); }

// WAS ANYTHING FOUND NEARBY
if (!$t = mysql_query('SELECT COUNT(*) FROM nearby', $db_connection)) { fatal_query_error($tsql); }
$trow = mysql_fetch_array($t, MYSQL_NUM);

// NOTHING NEAR
if ($trow[0] == 0) 
{
    echo "<h3>No good matches</h3>\n";
    return;
}


// COMPUTE THE DISTANCE FOR EACH NEARBY
$dsql = "SELECT * FROM nearby";
if (!$d = mysql_query("$dsql", $db_connection)) { fatal_query_error($dsql); }
while ($drow = mysql_fetch_assoc($d)) 
{
    extract ($drow);
    $dist = number_format(compute_distance( $my_lat, $my_lon, $p_latitude, $p_longitude ),1);
    $usql = "UPDATE nearby SET distance = $dist WHERE _key = $_key LIMIT 1";
    if (!$u = mysql_query("$usql", $db_connection)) { fatal_query_error($usql); }
}


// ITERATE OVER RESULTS SET
$rsql = "SELECT * FROM nearby ORDER BY distance ASC";
if (!$r = mysql_query("$rsql", $db_connection)) { fatal_query_error($rsql); }

// THIS SHOULD NOT OCCUR
if (!mysql_num_rows($r)) 
{ 
    echo "<h3>No good matches</h3>\n";
    warning_RAY("DB ERROR $my_p_pass in _camper_Ridematch", "FOO");
    return;
}

// DISPLAY THE CLOSEST NEARBY
while ($rrow = mysql_fetch_assoc($r))
{
    var_dump($rrow);
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
glopezzAuthor Commented:
I have tried the scripts and it works fine! Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.