?
Solved

Find cities within radius based on latitude longitude

Posted on 2011-10-25
8
Medium Priority
?
1,445 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
0
Comment
Question by:greenerpastures
  • 4
  • 2
  • 2
8 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37028583
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37028647
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
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 37028894
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:greenerpastures
ID: 37050592
Thanks guys for pointing in the right direction. I am still trying to get my code to work.
0
 

Author Comment

by:greenerpastures
ID: 37054423
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
 

Author Comment

by:greenerpastures
ID: 37054639
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
 

Author Closing Comment

by:greenerpastures
ID: 37054796
Thanks for the great article
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37054808
Thanks very much for the points! ~Ray
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month13 days, 8 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question