Find cities within radius based on latitude longitude

I have the zip code data saved in the database for all areas in the US (some 80,000 records). Once a user specifies a city (or zip code) our site needs to show 5-10 other cities within 20 mile radius. The sample data is attached in mySQL format. I need help with a SQL query and/or PHP code to retrieve additional cities based on given city or zip code. default-zipdist.sql
greenerpasturesAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
While not an exact answer, this article tells the design pattern:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html

Sidebar note: There are not 80,000 useful ZIP codes.  And most free, online ZIP code data bases are full of errors.  The actual number of useful ZIP codes is more like 37,000.  More than a thousand of the 37K were wrong, even after applying a filter to raise the signal-to-noise ratio toward unity.  You cannot know where any APO ZIP code is located.  It took me over two weeks to "purify" the free ZIP codes by using the Google and Yahoo geocoder algorithms.  You get what you pay for!
0
 
Kevin CrossChief Technology OfficerCommented:
This may come in handy for you: http://www.experts-exchange.com/Q_27288006.html
It shows a distance function using latitude and longitude values. So finding the original zipcode, you can search for distances whose absolute value is less-than-or-equal to twenty.
0
 
Kevin CrossChief Technology OfficerCommented:
After you create the GET_DISTANCE() function, you can do a query similar to the attached.

SELECT DISTINCT dest.*
FROM `default_zipdist` AS dest
JOIN (
SELECT zipdist_latitude AS lat
     , zipdist_longitude AS lon
FROM `default_zipdist`
WHERE zipdist_zipcode = '02801'
) AS src 
  ON GET_DISTANCE(src.lat, src.lon, dest.zipdist_latitude, dest.zipdist_longitude) BETWEEN -20 AND 20
;

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
greenerpasturesAuthor Commented:
Thanks guys for pointing in the right direction. I am still trying to get my code to work.
0
 
greenerpasturesAuthor Commented:
Ray,
I i used your code to adopt my zipcode database (see default-zipdist.sql above , but for some reason the distance formula does not work correctly. The result does not show cities nearby, but records in some other order. I know there is something wrong with my file (see below). When I loaded your sample data with your example code it works. Any ideas why mine would not work?


<?php // RAY_EE_proximity_calculator.php
error_reporting(E_ALL);


// DEMONSTRATE HOW TO FIND THE GEOGRAPHICALLY CLOSEST ZIP CODES


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "--------";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "----";
$db_word = "---";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// DATA DEFINITIONS - ADJUST THESE AS NEEDED - MAY BE APPLICATION DEPENDENT
define('LAT_OFFSET', 0.3);
define('LON_OFFSET', 0.3);


// A SCRIPT TIMER
$alpha_time = microtime(TRUE);

// WHAT UNIT OF DISTANCE SHOULD WE USE?
$what_unit = (isset($_GET["u"])) ? $_GET["u"] : 'MILES';
if     (strtoupper(substr($what_unit,0,1)) == 'K') $what_unit = 'KILOMETERS';
elseif (strtoupper(substr($what_unit,0,1)) == 'M') $what_unit = 'MILES';
else $what_unit = 'MILES';

// WHAT MAXIMUM DISTANCE LIMIT IS ACCEPTABLE?
$how_far = (isset($_GET["d"])) ? $_GET["d"] : 10.0;
if ($how_far < 1)  $how_far = 1.0;
if ($how_far > 20) $how_far = 20.0;

// HOW MANY RESULTS SHALL WE PRODUCE
$how_many = (isset($_GET["n"])) ? $_GET["n"] : 10;
if ($how_many < 10)  $how_many = 10;
if ($how_many > 100) $how_many = 100;

// WHAT ZIP CODE SHOULD WE START WITH?
$zip = '00000';
if (!empty($_GET["z"]))
{
    // CLEAN DATA
    $zip = substr(trim($_GET["z"]),0,5);
    $zip = preg_replace('/[^0-9]/', '', $zip);
    $zip = mysql_real_escape_string($zip);

    // GET THE GEOCODE (LAT,LON) FOR THIS ZIP CODE
    $sql = "SELECT zipdist_zipcode, zipdist_latitude, zipdist_longitude FROM default_zipdist WHERE zipdist_zipcode ='$zip' LIMIT 1";
    $res = mysql_query($sql);
    if (!$res)
    {
        $errmsg = mysql_errno() . ' ' . mysql_error();
        echo "<br/>QUERY FAIL: ";
        echo "<br/>$sql <br/>";
        die($errmsg);
    }

    // IF NOTHING FOUND, HANDLE ERROR
    $num = mysql_num_rows($res);
    if ($num == 0)
    {
        die
        ( "ZIP CODE $zip NOT FOUND. "
        . '<br/>PLEASE <a href="'
        . $_SERVER["PHP_SELF"]
        . '">TRY AGAIN</a>'
        );
    }

    // SAVE THE GOOD ZIP VALUE
    $what_zip = $zip;

    // EXTRACT THE SINGLETON ROW FROM THE QUERY
    $row = mysql_fetch_assoc($res);
    extract($row, EXTR_PREFIX_ALL, 'my');

    // CREATE A SPACE AROUND OUR POINT OF ORIGIN
    $my_lo_lat = $my_lat - LAT_OFFSET;
    $my_hi_lat = $my_lat + LAT_OFFSET;
    $my_lo_lon = $my_lon - LON_OFFSET;
    $my_hi_lon = $my_lon + LON_OFFSET;

    // CREATE A TEMPORARY TABLE OF NEARBY ZIP CODES BASED ON LAT/LON PROXIMITIES
//zipdist_zipcode, zipdist_latitude, zipdist_longitude FROM default_zipdist
    $sql
    = "CREATE TEMPORARY TABLE nearby ( distance DECIMAL(6,1) ) ENGINE=MEMORY
    SELECT zipdist_zipcode, zipdist_latitude, zipdist_longitude, zipdist_cityname, zipdist_statename FROM default_zipdist
    WHERE
    ( zipdist_latitude BETWEEN $my_lo_lat AND $my_hi_lat )
    AND
    ( zipdist_longitude BETWEEN $my_lo_lon AND $my_hi_lon )
    AND  zipdist_zipcode <> '$zip'"
    ;
    $res = mysql_query($sql) or die( mysql_error() );

    // COMPUTE THE DISTANCES FROM OUR ZIP CODE TO THE LOCATIONS OF THE OTHER ZIP CODES
    $sql = "SELECT zipdist_zipcode, zipdist_latitude, zipdist_longitude FROM nearby";
    $res = mysql_query($sql) or die( mysql_error() );
    while ($row = mysql_fetch_assoc($res))
    {
        $distance = compute_distance($my_lat, $my_lon, $row["zipdist_latitude"], $row["zipdist_longitude"], $what_unit);
        $uql = "UPDATE nearby SET distance = '$distance' WHERE zipdist_zipcode = '{$row["zipdist_zipcode"]}' LIMIT 1";
        if (!$u = mysql_query($uql)) die( mysql_error() );
    }

    // SHOW THE CLOSEST DISTANCES
    $sql = "SELECT zipdist_zipcode, zipdist_latitude, zipdist_longitude, zipdist_cityname, zipdist_statename, distance FROM nearby WHERE distance <= $how_far ORDER BY distance LIMIT $how_many";
    $res = mysql_query($sql) or die( mysql_error() );
    $num = mysql_num_rows($res);
    echo PHP_EOL . "<br/>HERE ARE THE CLOSEST $num ZIP CODES WITHIN ABOUT $how_far $what_unit OF $zip";
    while ($row = mysql_fetch_assoc($res))
    {
        echo PHP_EOL
        . '<br/>'
        . "ZIP CODE {$row["zipdist_zipcode"]} IS {$row["distance"]} "
        . $what_unit
        . " AWAY IN {$row["zipdist_cityname"]}, {$row["zipdist_statename"]}"
        ;
    }

    // A SCRIPT TIMER
	$omega_time = microtime(TRUE);
    $lapse_time = $omega_time - $alpha_time;
    $lapse_msec = $lapse_time * 1000.0;
    $lapse_echo = number_format($lapse_msec, 1);
    echo "<br/>SCRIPT TIME: $lapse_echo MILLISECONDS <br/>";
}


// LOWER THE ERROR REPORTING THRESHHOLD SO WE CAN USE UNDEFINED VARIABLES AS BLANK FIELDS IN THE FORM
error_reporting(E_ALL ^ E_NOTICE);

// CREATE A FORM FOR INPUT
$form = <<<ENDFORM
<form>
SHOW ME
<input type="text"   name="n" value="$how_many" /> RESULTS WITHIN
<input type="text"   name="d" value="$how_far"  />
<input type="radio"  name="u" value="M" checked /> MILES, OR
<input type="radio"  name="u" value="K"         /> KILOS
 OF ZIP:
<input type="text"   name="z" value="$what_zip" />
<input type="submit"          value="Go!" />
</form>
ENDFORM;

echo $form;

// RESET ERROR REPORTING
error_reporting(E_ALL);

// CREATE A FEW TEST CASES
echo '<br/>' . '<a href="' . $_SERVER["PHP_SELF"] . '?z=94111">94111</a> SAN FRANCISCO';
echo '<br/>' . '<a href="' . $_SERVER["PHP_SELF"] . '?z=94087">94087</a> SOUTH BAY';
echo '<br/>' . '<a href="' . $_SERVER["PHP_SELF"] . '?z=96155">96155</a> TAHOE';
echo '<br/>' . '<a href="' . $_SERVER["PHP_SELF"] . '?z=95241">95241</a> LODI';


// MAN PAGE: http://en.wikipedia.org/wiki/Haversine_formula
function compute_distance($from_lat, $from_lon, $to_lat, $to_lon, $units='KM')
{
    // CHOOSE A UNIT OF MEASURE BY THE FIRST CHARACTER
    $units = strtoupper(substr(trim($units),0,1));

    // ENSURE THAT ALL COORDINATES 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 THE DISTANCE IS ZERO AND HAVERSINE IS NOT NEEDED
    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 OTHER MEASURES IF NEEDED
    $miles = (float) $distance * 69.0;
    $km    = (float) $miles * 1.61;

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

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

    // UNITS NOT UNDERSTOOD
    return('INVALID FIFTH ARGUMENT - USE MILES OR KM');
}

Open in new window

0
 
greenerpasturesAuthor Commented:
OK, got this resolved. I had a mistake in $my_lat and $my_lon:

 // CREATE A SPACE AROUND OUR POINT OF ORIGIN
    $my_lo_lat = $my_zipdist_latitude - LAT_OFFSET;
    $my_hi_lat = $my_zipdist_latitude + LAT_OFFSET;
    $my_lo_lon = $my_zipdist_longitude - LON_OFFSET;
    $my_hi_lon = $my_zipdist_longitude + LON_OFFSET;
0
 
greenerpasturesAuthor Commented:
Thanks for the great article
0
 
Ray PaseurCommented:
Thanks very much for the points! ~Ray
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.