We help IT Professionals succeed at work.

Find cities within radius based on latitude longitude

1,809 Views
Last Modified: 2012-06-21
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
Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
This may come in handy for you: https://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.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

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

Most Valuable Expert 2011
Author of the Year 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks guys for pointing in the right direction. I am still trying to get my code to work.

Author

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

Author

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;

Author

Commented:
Thanks for the great article
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Thanks very much for the points! ~Ray
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.