Find the nearest zip code location in PHP / MYSQL

Question

The code below I have written to do a radius search within 50 miles of the searched zip code. How can I change this code to find only the nearest clinic location, instead of a list of clinic locations? So if I typed in a zip code for Denver, Colorado and my locations are mainly in New York, PA, and Ohio, how can I get the nearest location located in Ohio?

 /* RADIUS SEARCH - 50 MILES FROM THE ZIP CODE VARIABLE */
 $zipcode=trim($_REQUEST['zipcode']); 


 //Find Out The Longitude & Latitude of Zipcode in existing database
 $zcquery = "SELECT * FROM wp_nava5jxxyp_zipcodes WHERE zipcode = $zipcode";
    $result = mysqli_query($dbc, $zcquery);
    $row = mysqli_fetch_array($result);

     $lat = $row['latitude'];
     $long = $row['longitude'];   


 //Setting the Default Distance 50 Miles 
 // (Equation 1 / 69 = 0.0144927536231884 * 50 miles equals result below)
 $miles = 0.7246376811594203; 

   /* Query To Search for all of the Zip Codes within the range */
  $query = 'SELECT zipcode from wp_nava5jxxyp_zipcodes
  WHERE latitude between ' . $lat . ' - ' . $miles . ' and ' . $lat . ' + ' . $miles . '
   and longitude between ' . $long . ' - ' . $miles . ' and ' . $long . ' + ' . $miles;
   $result = mysqli_query($dbc, $query);  


  //Put those zip codes into a variable array
  $variabele = array();
 while($row = mysqli_fetch_array($result))
 {
 if($row['zipcode'] != '')
    $variabele[] = $row['zipcode'];
 }
 $zipcodelist =  implode(', ', $variabele); 


 // Close Connection
 mysqli_close($dbc); 

 //Query Database For Any Clinics that are included in zip code list
 $args = array(
'posts_per_page'   => 10,   
'orderby'          => 'post_title',
'order'            => 'DESC', 
'post_type' => 'clinics',
   'meta_query' => array (
        array (
          'key' => 'cliniczipcode',
          'value' => $zipcodelist,
          'compare' => 'IN'
        )
      ) );

 // the query
 $the_query = new WP_Query( $args );

Use haversine formula, https://developers.google.com/maps/articles/phpsqlsearch_v3. See `Finding Locations with MySQL`. — chris85, Jan 24 '17 at 04:56
If I LIMIT the Sql Statement to only 1, will it give me the nearest location? — user2593040, Jan 24 '17 at 05:03
Instead of a radius search, I want to find the nearest location. So the nearest location might be 1000 miles away. Would I just put in the query the circumference of the earth instead of 10 miles? — user2593040, Jan 24 '17 at 05:10
Well that'd be one approach, I think it'd be better to just take off the `HAVING distance < 25`, then there is no max distance. — chris85, Jan 24 '17 at 05:11
Note also that zip codes shouldnt be treated as integers and you aren open to SQL injections. A zip code for most massachusetts locations would fail/be misrepresented as an integer because of the leading zeros. — chris85, Jan 24 '17 at 05:22

score 3 · Accepted Answer · edited May 23 '17 at 11:52

See this Fastest Way to Find Distance Between Two Lat/Long Points

Basically you compute the distance to your base latlon, sort by shortest distance, and get the item on top. You can also do this in code if you don't want to bother having complex queries.

Keywords are "great circle distance" and "haversine formula"

EDIT

Let's say I have a database of vet clinics in LA

Limehouse Veterinary Clinic   34.1534500122    -118.3630599976
LA Pet Clinic                 34.0838623047    -118.3284454346
Bestfriends Animal Hospital   34.1692466736    -118.3970489502

And my base coordinates is in hollywood lat 34.092324, lon -118.337122

haversine formula needs coords in radians instead of degrees.

There are 2 pi radians in 360 degrees so y radians = 6.28319 * x degrees / 360 (solving for y)

The following is the haversine formula where r in 2 r arcsin is the earth's radius: 6,371,000 meters

-- haversine
--                           latdif                                  londif
-- 2 r arcsin [ sqrt[  sin^2(------) + cos(lat1) * cos(lat2) * sin^2(------)   ] ]
--                            2                                       2

Therefore to get the nearest clinics near my location, I can use this query as a translation of the above formula

select 
name,
2 * 6371000 * ASIN(
    sqrt(
        SIN(6.28319 * (34.092324 - lat) / 360 / 2) * SIN(6.28319 * (34.092324 - lat) / 360 / 2)
        +
        COS( 6.28319 * 34.092324 / 360  ) * COS( 6.28319 * lat / 360  )
        *
        SIN(6.28319 * (-118.337122 - lon) / 360 / 2) * SIN(6.28319 * (-118.337122 - lon) / 360 / 2)
    )
)
as dist_in_meters
from locations ORDER BY dist_in_meters;

Giving me

name                              dist_in_meters
LA Pet Clinic                   1234.3897096551932
Limehouse Veterinary Clinic     7204.075434291492
Bestfriends Animal Hospital    10177.689847331932

How can I figure the second location? I know the long/lat for Denver, but for the list of zip codes in my database, how could I know which one to use as the second point? Figure distance from Denver to "?" — user2593040, Jan 24 '17 at 05:07
You need the actual coordinates of the clinics. You can find these manually using google maps. Then you will compare your base zip code coordinate against these. Ideally the zip code coordinate is in the center of the coverage area. BUT In a real application, you will have to use your user's actual location (using mobile's gps or browser's geolocation feature) to compare against the clinics' locations — Kyle Domingo, Jan 24 '17 at 05:10
Ok I have those fields in my locations database, I am just confused on how I can change my code. I may not even be able to use any of my existing code. — user2593040, Jan 24 '17 at 05:15
I see, Ok so would it be better to query all of my clinics first (since there are only 90 locations), then turn their coordinates into radians? — user2593040, Jan 24 '17 at 06:16
Not really. You can include the radian conversion in the query like i did above `degrees x 2 x 3.14159 / 360`. The coordinates stored in the database are in degrees — Kyle Domingo, Jan 24 '17 at 06:24
Ok! I get it! Thank you! I was confused inside the query but now i see you used your coordinates to Hollywood! I will try this and let you know if it works! Thank you!!! — user2593040, Jan 24 '17 at 06:30

Find the nearest zip code location in PHP / MYSQL

1 Answers1