CODEWITHSUNDEEP
phpmysqlsearch
user2593040
user2593040

Reputation: 199

Find the nearest zip code location in PHP / MYSQL

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 );

Upvotes: 0

Views: 3033

Answers (1)

Kyle Domingo
Kyle Domingo

Reputation: 531

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

Upvotes: 3

Related Questions