# 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!!
Asked:
###### Who is Participating?

Commented:
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);
}
``````
0

Commented:
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

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

Thanks!
0

Commented:
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>
``````
0

Author 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

Author 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.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.