2 /*******************************************************************************
3 * ZIP Code and Distance Claculation Class
4 *******************************************************************************
5 * Author: Micah Carrick
6 * Email: email@micahcarrick.com
7 * Website: http://www.micahcarrick.com
9 * File: zipcode.class.php
11 * Copyright: (c) 2005 - Micah Carrick
12 * You are free to use, distribute, and modify this software
13 * under the terms of the GNU General Public License. See the
14 * included license.txt file.
16 *******************************************************************************
18 * v1.2.0 [Oct 22, 2006] - Using a completely new database based on user
19 contributions which resolves many data bugs.
20 - Added sorting to get_zips_in_range()
21 - Added ability to include/exclude the base zip
22 from get_zips_in_range()
24 * v1.1.0 [Apr 30, 2005] - Added Jeff Bearer's code to make it MUCH faster!
26 * v1.0.1 [Apr 22, 2005] - Fixed a typo :)
28 * v1.0.0 [Apr 12, 2005] - Initial Version
30 *******************************************************************************
33 * A PHP Class and MySQL table to find the distance between zip codes and
34 * find all zip codes within a given mileage or kilometer range.
36 *******************************************************************************
38 * Note: The following 2 database definitions are needed to import zipcode data from
39 * the new style P.O. data which is a great departure.
40 * One will either have to dynamically specify the data source or
41 * create zipcode_import.php and zipcode_new.php models containing below information
44 *class ZipcodeNew extends AppModel {
45 var $name = 'ZipcodeNew';
46 var $useTable = 'zip_code_new';
47 var $primaryKey = 'id';
48 var $useDbConfig = 'avengerShared';
51 class ZipcodeImport extends AppModel {
52 var $name = 'ZipcodeImport';
53 var $useTable = 'free_zipcode_database';
54 var $primaryKey = 'RecordNumber';
55 var $useDbConfig = 'avengerShared';
65 // constants for setting the $units data member
66 define('_UNIT_MILES', 'm');
67 define('_UNIT_KILOMETERS', 'k');
69 // constants for passing $sort to get_zips_in_range()
70 define('_ZIPS_SORT_BY_DISTANCE_ASC', 1);
71 define('_ZIPS_SORT_BY_DISTANCE_DESC', 2);
72 define('_ZIPS_SORT_BY_ZIP_ASC', 3);
73 define('_ZIPS_SORT_BY_ZIP_DESC', 4);
75 // constant for miles to kilometers conversion
76 define('_M2KM_FACTOR', 1.609344);
79 class Zipcode extends AppModel {
80 var $name = 'Zipcode';
81 var $useTable = 'zip_code';
82 var $primaryKey = 'zip_code';
83 var $useDbConfig = 'avengerShared';
84 var $zipInRangeData = array();
85 var $zipPageConditions = array();
88 function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null) {
89 //$conditions[] ="1 = 1 GROUP BY week, away_team_id, home_team_id";
91 //$fields = array('week', 'away_team_id', 'home_team_id');
92 $this->get_zips_in_range($this->zipPageConditions['zip'], $this->zipPageConditions['range'], $this->zipPageConditions['sort']);
94 //loop through page data
97 return $this->zipInRangeData;
100 function paginateCount($conditions = null, $recursive = 0) {
101 return count($this->zipInRangeData);
107 * TAKEN FROM MICCAH CODE
110 var $last_error = ""; // last error message set by this class
111 var $units = _UNIT_MILES; // miles or kilometers
112 var $decimals = 2; // decimal places for returned distance
114 function get_distance($zip1, $zip2) {
116 // returns the distance between to zip codes. If there is an error, the
117 // function will return false and set the $last_error variable.
120 return 0; // same zip code means 0 miles between. :)
123 // get details from database about each zip and exit if there is an error
125 $details1 = $this->get_zip_point($zip1);
126 $details2 = $this->get_zip_point($zip2);
127 if ($details1 == false) {
128 $this->last_error = "No details found for zip code: $zip1";
131 if ($details2 == false) {
132 $this->last_error = "No details found for zip code: $zip2";
137 // calculate the distance between the two points based on the lattitude
138 // and longitude pulled out of the database.
140 $miles = $this->calculate_mileage($details1['lat'], $details2['lat'], $details1['lon'], $details2['lon']);
144 if ($this->units == _UNIT_KILOMETERS) return round($miles * _M2KM_FACTOR, $this->decimals);
145 else return round($miles, $this->decimals); // must be miles
152 * Lookups up a zip code and returns single array or multi-array based on sending multiple zip codes in array or just a string
155 function lookup($zip, $options = array()) {
158 'bare' => false, //set to true to strip outer Zipcode dimension
160 $options = am($default_options, $options);
164 $data = $this->find('all', array('conditions'=>array('Zipcode.zip_code'=>$zip), 'recursive' => -1));
167 $data = $this->find('first', array('conditions'=>array('Zipcode.zip_code'=>$zip), 'recursive' => -1));
170 if($options['bare']) {
171 $data = $data['Zipcode'];
178 * This returns all zip codes (even if one sent)
181 function get_zip_details($zip) {
182 if (!$data = $this->find('all',array('conditions'=>array('zip_code'=>$zip)))){
189 function get_details_by_city($cityname) {
191 if (!$data = $this->find('all', array('conditions'=>array('city LIKE'=>$cityname),'recursive'=>-1))) {
198 function get_details_by_lat_lon($geolocation) {
199 if (!$data = $this->find('all', array('conditions'=>array('lat'=>$geolocation['latitude'],'lon'=>$geolocation['longitude']),'recursive'=>-1))) {
205 function get_details_by_location($locationtext) {
207 if (!$data = $this->find('all', array('conditions'=>array('locationtext LIKE'=>$locationtext),'recursive'=>-1))) {
214 function get_details_by_county($county, $state_id){
215 if (!$data = $this->find('all', array('conditions'=>array('county LIKE'=>$county, 'state_prefix LIKE'=>$state_id),'recursive'=>-1))) {
224 function get_locations_by_state($stateprefix) {
225 if (!$data = $this->find('all', array('conditions'=>array('state_prefix LIKE'=>$stateprefix),'recursive'=>-1,'order'=>array('zip_code'=>'asc')))){
232 function get_locations_by_county($county) {
234 if (!$data = $this->find('all', array('conditions'=>array('county LIKE'=>$county),'recursive'=>-1))){
242 function get_counties_by_state($stateprefix) {
244 if (!$data = $this->find('all', array('fields'=>array('DISTINCT county'),'conditions'=>array('state_prefix LIKE'=>$stateprefix),'recursive'=>-1,'order'=>array('county'=>'asc')))){
251 function get_zip_point($zip) {
253 // This function pulls just the lattitude and longitude from the
254 // database for a given zip code.
255 //$sql = "SELECT lat, lon from {$this->tablePrefix}zip_code WHERE zip_code='$zip'";
256 //$r = mysql_query($sql);
258 if (!$data = $this->find(array('zip_code'=>$zip), array('lat', 'lon'))) {
259 //$this->last_error = mysql_error();
262 //$row = mysql_fetch_array($r);
263 //mysql_free_result($r);
264 //var_dump($data['Zipcode']);
266 return $data['Zipcode'];
271 function calculate_mileage($lat1, $lat2, $lon1, $lon2) {
273 // used internally, this function actually performs that calculation to
274 // determine the mileage between 2 points defined by lattitude and
275 // longitude coordinates. This calculation is based on the code found
276 // at http://www.cryptnet.net/fsp/zipdy/
278 // Convert lattitude/longitude (degrees) to radians for calculations
279 $lat1 = deg2rad($lat1);
280 $lon1 = deg2rad($lon1);
281 $lat2 = deg2rad($lat2);
282 $lon2 = deg2rad($lon2);
285 $delta_lat = $lat2 - $lat1;
286 $delta_lon = $lon2 - $lon1;
288 // Find the Great Circle distance
289 $temp = pow(sin($delta_lat/2.0),2) + cos($lat1) * cos($lat2) * pow(sin($delta_lon/2.0),2);
290 $distance = 3956 * 2 * atan2(sqrt($temp),sqrt(1-$temp));
295 function get_zips_in_range($zip, $range, $sort=1, $include_base = true, $options = array()) {
296 $default_options = array('datasource' => null, 'conditions' => array());
297 $options = Set::merge($default_options, $options);
300 // returns an array of the zip codes within $range of $zip. Returns
301 // an array with keys as zip codes and values as the distance from
302 // the zipcode defined in $zip.
303 //var_dump($zip,$range);
306 //passing an array with zip code detail(s) known, don't need to lookup
307 if(isset($zip['latitude'])) {
308 $zip['lat'] = $zip['latitude'];
309 unset($zip['latitude']);
311 if(isset($zip['longitude'])) {
312 $zip['lon'] = $zip['longitude'];
313 unset($zip['longitude']);
317 $details = $this->get_zip_point($zip); // base zip details
320 if ($details == false) return false;
322 // This portion of the routine calculates the minimum and maximum lat and
323 // long within a given range. This portion of the code was written
324 // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases
325 // the time it takes to execute a query. My demo took 3.2 seconds in
326 // v1.0.0 and now executes in 0.4 seconds! Greate job Jeff!
328 // Find Max - Min Lat / Long for Radius and zero point and query
329 // only zips in that range.
332 $lat_range = $range/69.172;
333 $lon_range = abs($range/(cos($details['lat']) * 69.172));
334 $min_lat = number_format($details['lat'] - $lat_range, "4", ".", "");
335 $max_lat = number_format($details['lat'] + $lat_range, "4", ".", "");
336 $min_lon = number_format($details['lon'] - $lon_range, "4", ".", "");
337 $max_lon = number_format($details['lon'] + $lon_range, "4", ".", "");
339 $return = array(); // declared here for scope
341 /* $sql = "SELECT zip_code, lat, lon FROM zip_code ";
342 if (!$include_base) $sql .= "WHERE zip_code <> '$zip' AND ";
343 else $sql .= "WHERE ";
344 $sql .= "lat BETWEEN '$min_lat' AND '$max_lat'
345 AND lon BETWEEN '$min_lon' AND '$max_lon'"; */
347 $conditions = array();
348 if($range === 'all') {
349 // don't want to have a range
350 // $conditions = array()
353 $conditions['zip_code'] = "<> $zip";
355 $conditions['lat BETWEEN ? AND ?'] = array($min_lat, $max_lat);
356 $conditions['lon BETWEEN ? AND ?'] = array($min_lon, $max_lon);
360 if($options['conditions']) {
361 $conditions = Set::merge($options['conditions'], $conditions);
364 if($options['datasource']) {
365 $datasource =& ClassRegistry::init($options['datasource'], 'model');
366 $data = $datasource->find('all', array('conditions' =>$conditions, 'recursive' => -1));
368 list($pluginName, $modelName) = pluginSplit($options['datasource']);
371 $data = $this->find('all', array('conditions' =>$conditions));
373 $modelName = 'Zipcode';
376 if (!$data) { // sql error
378 //$this->last_error = mysql_error();
383 /*while ($row = mysql_fetch_row($r)) {
385 // loop through all 40 some thousand zip codes and determine whether
386 // or not it's within the specified range.
388 $dist = $this->calculate_mileage($details[0],$row[1],$details[1],$row[2]);
389 if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR;
390 if ($dist <= $range) {
391 $return[str_pad($row[0], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals);
394 mysql_free_result($r);*/
395 $zip1VarName = 'zip_code';
397 if(isset($details['zip'])) {
398 $zip1 = $details['zip'];
399 $zip1VarName = 'zip';
400 } else if (isset($details['zipcode'])) {
401 $zip1 = $details['zipcode'];
402 $zip1VarName = 'zipcode';
403 } else if (isset($details['zip_code'])) {
404 $zip1 = $details['zip_code'];
405 $zip1VarName = 'zip_code';
408 $zip2VarName = 'zip_code';
410 $dataByZip = array();
411 foreach($data as $row) {
412 if(isset($row[$modelName]['lat']) and isset($row[$modelName]['lon'])) {
413 $dist = $this->calculate_mileage($details['lat'],$row[$modelName]['lat'],$details['lon'],$row[$modelName]['lon']);
416 if(isset($row[$modelName]['zip'])) {
417 $zip2 = $row[$modelName]['zip'];
418 $zip2VarName = 'zip';
419 } else if (isset($row[$modelName]['zipcode'])) {
420 $zip2 = $row[$modelName]['zipcode'];
421 $zip2VarName = 'zipcode';
422 } else if (isset($row[$modelName]['zip_code'])) {
423 $zip2 = $row[$modelName]['zip_code'];
424 $zip2VarName = 'zip_code';
427 $dist = $this->get_distance($zip1, $zip2);
430 if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR;
431 if ($range === 'all' or $dist <= $range) {
432 $return[str_pad($row[$modelName][$zip2VarName], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals);
433 $dataByZip[str_pad($row[$modelName][$zip2VarName], 5, "0", STR_PAD_LEFT)] = $row;
441 case _ZIPS_SORT_BY_DISTANCE_ASC:
445 case _ZIPS_SORT_BY_DISTANCE_DESC:
449 case _ZIPS_SORT_BY_ZIP_ASC:
453 case _ZIPS_SORT_BY_ZIP_DESC:
458 $this->zipInRangeData = $return;
460 if($options['datasource']) {
461 //merge database results back into return (with distance calculated)
462 $newReturn = array();
463 foreach($return as $zip => $distance) {
464 if(isset($dataByZip[$zip])) {
465 $tmp = $dataByZip[$zip];
466 $tmp['distance'] = $distance;
471 $return = $newReturn;
474 if (empty($return)) return false;
478 function get_detailed_zips_in_range($zip, $range, $sort=1, $include_base = true) {
479 // returns an array of the zip codes within $range of $zip with their details
480 //var_dump($zip,$range);
482 $details = $this->get_zip_point($zip); // base zip details
484 if ($details == false) return false;
486 // This portion of the routine calculates the minimum and maximum lat and
487 // long within a given range. This portion of the code was written
488 // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases
489 // the time it takes to execute a query. My demo took 3.2 seconds in
490 // v1.0.0 and now executes in 0.4 seconds! Greate job Jeff!
492 // Find Max - Min Lat / Long for Radius and zero point and query
493 // only zips in that range.
494 $lat_range = $range/69.172;
495 $lon_range = abs($range/(cos($details['lat']) * 69.172));
496 $min_lat = number_format($details['lat'] - $lat_range, "4", ".", "");
497 $max_lat = number_format($details['lat'] + $lat_range, "4", ".", "");
498 $min_lon = number_format($details['lon'] - $lon_range, "4", ".", "");
499 $max_lon = number_format($details['lon'] + $lon_range, "4", ".", "");
501 $return = array(); // declared here for scope
503 $conditions = array();
504 $conditions['z_primary'] = 'PRIMARY';
506 $conditions['zip_code'] = "<> $zip";
508 $conditions['lat BETWEEN ? AND ?'] = array($min_lat, $max_lat);
509 $conditions['lon BETWEEN ? AND ?'] = array($min_lon, $max_lon);
512 if (!$data = $this->find('all', array('conditions' =>$conditions))) { // sql error
517 foreach($data as $row) {
519 $dist = $this->calculate_mileage($details['lat'],$row['Zipcode']['lat'],$details['lon'],$row['Zipcode']['lon']);
520 //$return['locationdetails'] = $this->find('all',array('conditions'=>array('zip_code'=>$zip)));
521 if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR;
522 if ($dist <= $range) {
523 $row['Zipcode']['distance'] = round($dist, $this->decimals);
530 function dist_sort($a,$b) {
531 //var_dump($a['Zipcode']['distance']);
532 if($a['Zipcode']['distance'] > $b['Zipcode']['distance'])
533 return 1;//here,if you return -1,return 1 below,the result will be descending
534 if($a['Zipcode']['distance'] < $b['Zipcode']['distance'])
536 if($a['Zipcode']['distance'] == $b['Zipcode']['distance'])
540 case '_ZIPS_SORT_BY_DISTANCE_ASC':
543 uasort($return, 'dist_sort');
547 case '_ZIPS_SORT_BY_DISTANCE_DESC':
552 $this->zipInRangeData = $return;
554 if (empty($return)) return false;
560 * Returns a flat array (e.g. 04072,03801, etc.) for SQL IN statements
564 function getZipsInRangeFlat() {
565 return array_keys($this->zipInRangeData);
569 function zipRangeOrderCase($zips = array(), $column_name = 'zip') {
571 /*Order by (CASE City
573 WHEN 'Chicago' THEN 2
575 WHEN 'New York' THEN 4
576 WHEN 'Berkeley' THEN 5
580 $zip_count = count($zips);
581 for($x=0; $x < $zip_count; $x++) {
582 $order_by .= " WHEN {$zips[$x]} THEN ". ($x + 1);
585 if(!empty($order_by)) {
586 $order_by = "(CASE $column_name". $order_by ." ELSE 100 END) ASC";
596 * Takes a string [from a form]
597 * @param $search string
598 * @param $options array
599 * return false if no zip found; or text if a single zip code or an array if multiple zip codes
601 function findZipFromText($search, $options = array()) {
602 App::import('Sanitize');
607 'stateList' => array(), //supply list of states, array('SN' => 'State Name');, to use instead of full geography helper list... will increase performance if you supply a small list
608 'defaultState' => Configure::read('avorders.avengerDefaultState'), //array('ME'), //an array of state abbreviations to use by default when a state isn't found
609 'firstMatch' => true, //will return first zip code that matches if distance is 0 (otherwise, returns all zipcodes)
610 'primary' => true, // set to false to return all zip codes, not just primary zip codes
612 $options = am($default_options, $options);
614 if(is_numeric($search)) {
615 //assume it's a zip code
616 $zip = trim($search);
617 $zip = substr($zip, 0, 5); //make sure it's only 5 numbers
620 // searching by a string
621 // 1. clean/sanitize data
622 // 2a. see if preg_match finds a zip code in user-entered text, if so use it
623 // 2b. otherwise, explode on spaces, b/c each word needs can be matched, then find unique zip codes
624 // 3. create conditions, add in distance condition and zips in range if specified
625 // 4. append to join conditions
627 $location_array = array();
628 $location = Sanitize::paranoid($search, array(' ', ',', '.'));
629 $location = trim($location);
631 if(preg_match("/([0-9]{5})(-[0-9]{4})?/i", $location, $match)) {
632 //zip code entered with text, use it
635 App::import('Helper', 'Geography');
636 $geography = new GeographyHelper();
641 // zip code not in text, so try to find zipcode using city, state if known
642 // check to see if there is a comma, if so explode on that: portsmouth, new hampshire
643 if(strpos($location, ',') !== false) {
644 $location_array = explode(',', $location);
646 //look for state names (not abbreviations) contained within search term
647 $stateAbbr = $geography->isAState($location, array('search' => true, 'list' => $options['stateList'])); // looking to see if a state is buried in this query
649 // state found, set to state_prefix and remove from search terms (for down-the-line processing)
651 $terms['Zipcode.state_prefix'] = $stateAbbr['state'];
652 $location = str_ireplace($stateAbbr['term'], '', $location);
653 $location = trim($location);
656 // explode remaining search terms on spaces
657 $location_array = explode(' ', $location);
660 $cityTerms = array();
661 foreach($location_array as $term) {
664 $stateAbbr = $geography->isAState($term);
666 //we know this is a state, and the 2 letter abbreviation
667 $terms['Zipcode.state_prefix'] = $stateAbbr;
670 //not a state, must be a city, eliminated everything else
671 $cityTerm = $term .'%';
672 if(!empty($cityTerms)) {
673 $cityTerm = '%'. $cityTerm; // if it's not the first word, need to allow double-sided wild card
675 $cityTerms[] = $cityTerm;
678 //state already found, these terms must be a city
679 $cityTerm = $term .'%';
680 if(!empty($cityTerms)) {
681 $cityTerm = '%'. $cityTerm; // if it's not the first word, need to allow double-sided wild card
683 $cityTerms[] = $cityTerm;
687 if(!empty($cityTerms)) {
688 if(count($cityTerms) == 1) {
689 $terms['Zipcode.city LIKE'] = bootArrayFirstValue($cityTerms);
691 $terms['AND'] = array();
692 foreach($cityTerms as $term) {
693 $terms['AND'][] = array('Zipcode.city LIKE' => $term);
698 //default state to ME, b/c this is originally for MyMaineTherapist
699 if(!isset($terms['Zipcode.state_prefix'])) {
700 $terms['Zipcode.state_prefix'] = $options['defaultState'];
703 // see if we are checking for primary-only zip codes
704 if($options['primary']) {
705 $terms['Zipcode.z_primary'] = 'PRIMARY';
708 $zips = $this->find('all', array('conditions' => $terms, 'fields' => 'Zipcode.city, Zipcode.state_prefix, Zipcode.zip_code, Zipcode.z_primary', 'group' => 'zip_code', 'recursive' => -1));
710 if($options['firstMatch'] or !empty($options['distance'])) {
711 // only want the first zip code (if distance is specified, firstMatch is implied)
712 $zip = bootArrayFirstValue($zips);
713 $zip = isset($zip['Zipcode']['zip_code']) ? $zip['Zipcode']['zip_code'] : false;
715 // for multiple zip codes, you should show a list of available zip codes and allow user to pick zip code in calling function
716 $zip = Set::extract('/Zipcode/zip_code', $zips);
722 // ADD IN DISTANCE-BASED SEARCHING
723 if(!empty($options['distance']) and isset($zip) and $zip) {
725 if($options['distance'] == 'all') {
728 $distance = (int) $options['distance'];
729 $distance = ($distance > 100) ? 100 : $distance; //cap it at 100, that's a lot of zipcodes
732 $zipsInRange = $this->get_zips_in_range($zip, $distance);
734 $zip = array_keys($zipsInRange);
740 if(!isset($zip) or !$zip) {
741 // basically, turning zipcode off, so no results will be found...
751 * Performs a Web Service call to FreeGeoIP.net to get IP Whois Information
752 * @param string $ipaddress
753 * @return array $location zip, lat, long, etc. of ip address
755 function fetchFreeGeoIpLocation($ipaddress) {
756 //http://freegeoip.net/{format}/{ip_or_hostname}
757 // create curl resource
761 $ipurl = 'http://freegeoip.net/json/'.$ipaddress;
765 curl_setopt( $ch, CURLOPT_TIMEOUT_MS, 1500 );
766 curl_setopt( $ch, CURLOPT_CONNECTTIMEOUT_MS, 1500 );
768 curl_setopt($ch, CURLOPT_URL, $ipurl);
769 //return the transfer as a string
770 curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
771 // $output contains the output string
772 $output = curl_exec($ch);
773 // close curl resource to free up system resources
775 if(is_string($output) and !empty($output)){
776 $location = json_decode($output, true);
778 if(!isset($location['zipcode']) or empty($location['zipcode'])){
779 //need to find zip code based on geocoded lat/long
780 if(isset($location['latitude']) and !empty($location['latitude']) and isset($location['longitude']) and !empty($location['longitude'])) {
781 $zipcode = $this->get_zips_in_range($location, 10); //no zip code supplied, grab the one that is closest within 10 miles
782 if(is_array($zipcode) and !empty($zipcode)){
783 $location['zipcode'] = bootArrayFirstValue(array_keys($zipcode));
785 $location['zipcode'] = '10001';
790 if(is_array($location) and !empty($location)) {
791 // change to match format from ZipCode table column names
792 $location['zip_code'] = $location['zipcode'];
793 unset($location['zipcode']);
795 $location['lat'] = $location['latitude'];
796 unset($location['latitude']);
798 $location['lon'] = $location['longitude'];
799 unset($location['longitude']);
801 $location['state_prefix'] = $location['region_code'];
802 unset($location['region_code']);
804 $location['country'] = $location['country_code'];
805 unset($location['country_code']);
807 $location['locationtext'] = $location['city'].', '.$location['state_prefix'];
819 function getClosestLocation($location, $options = array()) {
820 $default_options = array('range' => 50);
821 $options = Set::merge($default_options, $options);
823 if($this->hasField('active')) {
824 $params['conditions'] = array('AvordersStore.active'=>1);
826 return bootArrayFirstValue($this->get_zips_in_range($location, $options['range'], 1, true, null));