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?
 
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
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
 
glopezzAuthor Commented:
I have tried the scripts and it works fine! Thanks a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.