SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 help with zipcode search
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sonny king
Starting Member

4 Posts

Posted - 08/27/2007 :  20:00:42  Show Profile  Visit sonny king's Homepage  Reply with Quote
I have this script that will find all zipcodes in a given radius. the script works fine at finding all the zipcodes, but I am having trouble getting it to output users in my database who have these matching zipcodes. in short. Im tryingto output members of my site who are "closest to me" by say..50 miles.

I have 2 tables in the same database, one is called zips which holds all the us zipcodes and it has the columns zip,lng,lat,city,state

and the table which holds my user data jamroom_band_info, with the columns zipcode,state,city,band_name,band_id.

here is the script I have so far.. I have no clue what Im doing at this point! but I know I need to somehow perform a join to output my members that reacht he search critia. (there is also a php file zipcode.class.php which does all the calculations which I have not posted here)


echo '<h3>A sample getting all the zip codes withing a range: 2 miles from 97214</h3>';


$zips = $z->get_zips_in_range('97214', 2, _ZIPS_SORT_BY_DISTANCE_ASC, true); 


if ($zips === false) echo 'Error: '.$z->last_error;
else {
   
   foreach ($zips as $key => $value) {
      echo "Zip code <b>$key</b> is <b>$value</b> miles away from <b>97214</b>.<br />";


   }


Koji Matsumura
Posting Yak Master

141 Posts

Posted - 08/27/2007 :  23:50:54  Show Profile  Reply with Quote
Where is the script?
Go to Top of Page

sonny king
Starting Member

4 Posts

Posted - 08/28/2007 :  00:10:32  Show Profile  Visit sonny king's Homepage  Reply with Quote
Im sorry.. here is the accual script.




// constants for setting the $units data member
define('_UNIT_MILES', 'm');
define('_UNIT_KILOMETERS', 'k');

// constants for passing $sort to get_zips_in_range()
define('_ZIPS_SORT_BY_DISTANCE_ASC', 1);
define('_ZIPS_SORT_BY_DISTANCE_DESC', 2);
define('_ZIPS_SORT_BY_ZIP_ASC', 3);
define('_ZIPS_SORT_BY_ZIP_DESC', 4);

// constant for miles to kilometers conversion
define('_M2KM_FACTOR', 1.609344);

class zipcode_class {

   var $last_error = "";            // last error message set by this class
   var $last_time = 0;              // last function execution time (debug info)
   var $units = _UNIT_MILES;        // miles or kilometers
   var $decimals = 2;               // decimal places for returned distance

   function get_distance($zip1, $zip2) {

      // returns the distance between to zip codes.  If there is an error, the 
      // function will return false and set the $last_error variable.
      
      $this->chronometer();         // start the clock
      
      if ($zip1 == $zip2) return 0; // same zip code means 0 miles between. :)
   
   
      // get details from database about each zip and exit if there is an error
      
      $details1 = $this->get_zip_point($zip1);
      $details2 = $this->get_zip_point($zip2);
      if ($details1 == false) {
         $this->last_error = "No details found for zip code: $zip1";
         return false;
      }
      if ($details2 == false) {
         $this->last_error = "No details found for zip code: $zip2";
         return false;
      }     


      // calculate the distance between the two points based on the lattitude
      // and longitude pulled out of the database.
      
      $miles = $this->calculate_mileage($details1[0], $details2[0], $details1[1], $details2[1]);
      
      $this->last_time = $this->chronometer();
 
      if ($this->units == _UNIT_KILOMETERS) return round($miles * _M2KM_FACTOR, $this->decimals);
      else return round($miles, $this->decimals);       // must be miles
      
   }   

   function get_zip_details($zip) {
      
      // This function pulls the details from the database for a 
      // given zip code.
 
      $sql = "SELECT lat AS lattitude, lon AS longitude, city, county, state_prefix, 
              state_name, area_code, time_zone
              FROM zip_code 
              WHERE zip_code='$zip'";
              
      $r = mysql_query($sql);
      if (!$r) {
         $this->last_error = mysql_error();
         return false;
      } else {
         $row = mysql_fetch_array($r, MYSQL_ASSOC);
         mysql_free_result($r);
         return $row;       
      }
   }

   function get_zip_point($zip) {
   
      // This function pulls just the lattitude and longitude from the
      // database for a given zip code.
      
      $sql = "SELECT lat, lon from zip_code WHERE zip_code='$zip'";
      $r = mysql_query($sql);
      if (!$r) {
         $this->last_error = mysql_error();
         return false;
      } else {
         $row = mysql_fetch_array($r);
         mysql_free_result($r);
         return $row;       
      }      
   }

   function calculate_mileage($lat1, $lat2, $lon1, $lon2) {
 
      // used internally, this function actually performs that calculation to
      // determine the mileage between 2 points defined by lattitude and
      // longitude coordinates.  This calculation is based on the code found
      // at http://www.cryptnet.net/fsp/zipdy/
       
      // Convert lattitude/longitude (degrees) to radians for calculations
      $lat1 = deg2rad($lat1);
      $lon1 = deg2rad($lon1);
      $lat2 = deg2rad($lat2);
      $lon2 = deg2rad($lon2);
      
      // Find the deltas
      $delta_lat = $lat2 - $lat1;
      $delta_lon = $lon2 - $lon1;
	
      // Find the Great Circle distance 
      $temp = pow(sin($delta_lat/2.0),2) + cos($lat1) * cos($lat2) * pow(sin($delta_lon/2.0),2);
      $distance = 3956 * 2 * atan2(sqrt($temp),sqrt(1-$temp));

      return $distance;
   }
   
   function get_zips_in_range($zip, $range, $sort=1, $include_base) {
       
      // returns an array of the zip codes within $range of $zip. Returns
      // an array with keys as zip codes and values as the distance from 
      // the zipcode defined in $zip.
      
      $this->chronometer();                     // start the clock
      
      $details = $this->get_zip_point($zip);  // base zip details
      if ($details == false) return false;
      
      // This portion of the routine  calculates the minimum and maximum lat and
      // long within a given range.  This portion of the code was written
      // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases
      // the time it takes to execute a query.  My demo took 3.2 seconds in 
      // v1.0.0 and now executes in 0.4 seconds!  Greate job Jeff!
      
      // Find Max - Min Lat / Long for Radius and zero point and query
      // only zips in that range.
      $lat_range = $range/69.172;
      $lon_range = abs($range/(cos($details[0]) * 69.172));
      $min_lat = number_format($details[0] - $lat_range, "4", ".", "");
      $max_lat = number_format($details[0] + $lat_range, "4", ".", "");
      $min_lon = number_format($details[1] - $lon_range, "4", ".", "");
      $max_lon = number_format($details[1] + $lon_range, "4", ".", "");

      $return = array();    // declared here for scope

      $sql = "SELECT zip_code, lat, lon FROM zip_code ";
      if (!$include_base) $sql .= "WHERE zip_code <> '$zip' AND ";
      else $sql .= "WHERE "; 
      $sql .= "lat BETWEEN '$min_lat' AND '$max_lat' 
               AND lon BETWEEN '$min_lon' AND '$max_lon'";
             
      $r = mysql_query($sql);
      
      if (!$r) {    // sql error

         $this->last_error = mysql_error();
         return false;
         
      } else {
          
         while ($row = mysql_fetch_row($r)) {
   
            // loop through all 40 some thousand zip codes and determine whether
            // or not it's within the specified range.
            
            $dist = $this->calculate_mileage($details[0],$row[1],$details[1],$row[2]);
            if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR;
            if ($dist <= $range) {
               $return[str_pad($row[0], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals);
            }
         }
         mysql_free_result($r);
      }
      
      // sort array
      switch($sort)
      {
         case _ZIPS_SORT_BY_DISTANCE_ASC:
            asort($return);
            break;
            
         case _ZIPS_SORT_BY_DISTANCE_DESC:
            arsort($return);
            break;
            
         case _ZIPS_SORT_BY_ZIP_ASC:
            ksort($return);
            break;
            
         case _ZIPS_SORT_BY_ZIP_DESC:
            krsort($return);
            break; 
      }
      
      $this->last_time = $this->chronometer();
      
      if (empty($return)) return false;
      return $return;
   }

   function chronometer()  {
 
   // chronometer function taken from the php manual.  This is used primarily
   // for debugging and anlyzing the functions while developing this class.  
  
   $now = microtime(TRUE);  // float, in _seconds_
   $now = $now + time();
   $malt = 1;
   $round = 7;
  
   if ($this->last_time > 0) {
       /* Stop the chronometer : return the amount of time since it was started,
       in ms with a precision of 3 decimal places, and reset the start time.
       We could factor the multiplication by 1000 (which converts seconds
       into milliseconds) to save memory, but considering that floats can
       reach e+308 but only carry 14 decimals, this is certainly more precise */
      
       $retElapsed = round($now * $malt - $this->last_time * $malt, $round);
      
       $this->last_time = $now;
      
       return $retElapsed;
   } else {
       // Start the chronometer : save the starting time
    
       $this->last_time = $now;
      
       return 0;
   }
}

}


and here is a demo.php file for running the script


<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
   <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
   <title>Zip Code Range and Distance Calculation Demo</title>
   <style type="text/css" lang="en">
   BODY, P { font-family: sans-serif; font-size: 9pt; }
   H3 { font-family: sans-serif; font-size: 14pt; }
   </style>
</head>
<body>

<?php

/*  
   DEMO for using the zipcode PHP class. By: Micah Carrick 
   Questions?  Comments?  Suggestions?  email@micahcarrick.com
*/

require_once('zipcode.class.php');      // zip code class


// Open up a connection to the database.  The sql required to create the MySQL
// tables and populate them with the data is in the /sql subfolder.  You can
// upload those sql files using phpMyAdmin or a MySQL prompt.  You will have to
// modify the below information to your database information.  
mysql_connect('localhost','php_user','php_user') or die(mysql_error()); 
mysql_select_db('test') or die(mysql_error()); 



// Below is an example of how to calculate the distance between two zip codes.

echo '<h3>A sample calculating the distance between 2 zip codes: 93001 and 60618</h3>';

$z = new zipcode_class;
$miles = $z->get_distance(97214, 98501);

if ($miles === false) echo 'Error: '.$z->last_error;
else echo "Zip code <b>97214</b> is <b>$miles</b> miles away from <b>98501</b>.<br />";



// Below is an example of how to return an array with all the zip codes withing
// a range of a given zip code along with how far away they are.  The array's
// keys are assigned to the zip code and their value is the distance from the
// given zip code.  

echo '<h3>A sample getting all the zip codes withing a range: 2 miles from 97214</h3>';

$zips = $z->get_zips_in_range('97214', 2, _ZIPS_SORT_BY_DISTANCE_ASC, true); 


if ($zips === false) echo 'Error: '.$z->last_error;
else {
   
   foreach ($zips as $key => $value) {
      echo "Zip code <b>$key</b> is <b>$value</b> miles away from <b>97214</b>.<br />";
   }
   
   // One thing you may want to do with this is create SQL from it. For example, 
   // iterate through the array to create SQL that is something like:
   // WHERE zip_code IN ('93001 93002 93004')
   // and then use that condition in your query to find all pizza joints or 
   // whatever you're using it for. Make sense? Hope so.
   
   echo "<br /><i>get_zips_in_range() executed in <b>".$z->last_time."</b> seconds.</i><br />";
}

// And one more example of using the class to simply get the information about
// a zip code.  You can then do whatever you want with it.  The array returned
// from the function has the database field names as the keys.  I just do a 
// couple string converstions to make them more readable.

echo '<h3>A sample getting details about a zip code: 97214</h3>';

$details = $z->get_zip_details('97214');

if ($details === false) echo 'Error: '.$z->last_error;
else {
   foreach ($details as $key => $value) {
      $key = str_replace('_',' ',$key);
      $key = ucwords($key);
      echo "$key: $value<br />";
   } 
}
?>



from the looks of it, it gives you various things you can do, Im only interested in finding zipcodes (or my members with zipcodes) that are within a given radius of a given zipcode.


Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 08/28/2007 :  01:17:49  Show Profile  Reply with Quote
Please narrow down you problem to SQL Server.
Go to Top of Page

sonny king
Starting Member

4 Posts

Posted - 08/28/2007 :  01:35:10  Show Profile  Visit sonny king's Homepage  Reply with Quote
Im trying to output members of my site that have zipcodes that fall within a given radius. I gave the table and column names above. all I need to do is get the code to join my member table with the zipcode database and output the results.
Go to Top of Page

sonny king
Starting Member

4 Posts

Posted - 08/28/2007 :  01:36:42  Show Profile  Visit sonny king's Homepage  Reply with Quote
maybe this is the wrong place for me to ask this? could you possibly lead me in the right direction? thanks
Go to Top of Page

codezilla94
Starting Member

6 Posts

Posted - 11/12/2007 :  20:07:09  Show Profile  Visit codezilla94's Homepage  Reply with Quote
(spam removed --graz)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 11/12/2007 :  21:46:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It cost $20 and you can get it for free here!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000