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

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.

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

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 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"];
}
?>

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="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
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

Experts Exchange Solution brought to you by

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

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