Zipcode Calculation in Mysql Query

[patrickstrijdonck]

hi all,

i want to create a mysql query wichs looks to a zipcode

the query must search for a zipcode example: 1506 and when the zipcode is 80 higher or lower it must give those records wich are within 1426 and 1586, if the zipcode is: 1033 it must find all zipcodes within 1000 (because thats the lowest zipcode) and 1113

Then the zipcode is always 1506XW so can a query also seperate the 1506 from XW so he can calculate??

i hope you know what i mean, can someone tell me how to make that WHERE condition???

Ty in advance

 

[spamjim]

There is a problem with your logic.

Visit

http://maps.huge.info/zip.htm

and type in 40162

The adjacent areas vary much more than 80.

What you need to do is have a table of all zip codes and their latitude and longitude. Then, with a formula, you can find the proximity of other zip codes based on their relation in latitude and longitude.

There are a few places on the net to get such a table. Here's one...

http://databases.about.com/od/access/a/zipcodedatabase.htm

 

[spamjim]

Silly me, I just noticed you are not using US zipcodes. Maybe the locale that you are dealing with is not as screwed up as US zipcodes.

 

[patrickstrijdonck]

lol, yeh we have better organised

but i only want to know how to search records / update records with have zipcode 1506 or 80 above or lower then.

and the zipcode is 1506XW and i need to separate that 1506 from XW to make that calculation.

but thanks for the quick response

 

[spamjim]

There are more concise ways to do this but here it is spelled out in baby steps...

// assuming we are getting form data posted
// get the first 4 characters ([URL unfurl="true"]http://us.php.net/substr)[/URL]
$zip = substr($zip, 0, 4);

// find the maximum and minimum values
$zipceiling = $zip + 80;
$zipfloor = $zip - 80;

// connect to your DB, then query...
// [your DB info here]
SELECT * FROM table WHERE `zip` > '$zipfloor' AND `zip` < '$zipceiling'

 

[patrickstrijdonck]

Ok i understand almost everything,

but $zip = substr($zip, 0, 4);

What is substr???

 

[spamjim]

http://us.php.net/substr

 

[spamjim]

You may want to pick up a good book on PHP to learn basic string manipulation functions and other basics about PHP. Substr is one of those elementary functions that you learn in your first week of PHP. Here's a good book that I've used:

http://www.dmcinsights.com/phpmysql2/

You should also revisit the concept of what you are trying to do. Postal codes are not arranged in a concentric circle to make it easy to locate nearby codes. Calculating geographic area by latitude and longitude is still ideal.

 

[patrickstrijdonck]

Ty for the link to the book,

im new to PHP although i learned alot since i began...

this problem i could not fix on my own, so i needed some help or a push in right direction....


But thanks for the tip BTW, your piece of code worked for me,

In the netherlandse zipcodes (postalcodes) are good organisez, between those 80 codes, is around 50 KM thats what i needed

Thanks

 

[jpadie]

if you wanted to use regex you might like this

preg_match('/\b(\d{1,4})([a-z]{1,2})\b/i', $text, $match);
list ($complete, $numbers, $letters) = $match;

this will match any combination of 1-4 numbers followed by 1-2 letters. the results are in $complete, $numbers and $letters.

in general it is better to avoid regex in favour of non-regex solutions (at least until php6). however postcodes quite often get too complex for simple find/replace functions to work.

 

[ingresman]

You US guys are lucky to have your zipcodes in the public domain. The UK post office has copyright on the post office address file (PAF) and you have to pay to use it. So a group of guys set up an group called

http://www.freethepostcode.org/

where you can contribute your GPS co-ordniates and the post code where you are. As long as you don't use the PAF directly to get the postcode you can use it so you wish , for example, use a restaurant menu, ask some behind the counter etc.
But that's not what I cam to say!, When you get around to working out how near somewhere is to somewhere else, have a look at

http://www.easypeasy.com/guides/article.php?article=64

which exaplins how to do it. It does use the UK postcodes but underneath it uses the latitude and longditude (which is in the access database refereced) based on a point off the cornish coast. It then uses Pythagoras to work out the as-the-crow-flies distance between two points (ie the root zip and where you want to get to). So having this raw data you should be able to do anything you need - hope this helps !

 

[spamjim]

The +/- 80 method is still not the greatest idea. Seeing this map (

http://www.surfusuf.nl/images/postcodekaart2.gif

), it appears that your postal codes are no different than with what I am familiar in the USA.

See in the center of the map the adjacent zones of 37__ and 67__. These adjacent areas could not be found with a range of +/- __80. Your PHP application can miss a location two blocks away between this 37__ and 67__ area.

You'd be better off with a geo-coordinate database like this:

http://www.geopostcodes.com/sample_netherlands_full.htm

Source:

http://www.geopostcodes.com/netherlands.htm

 

[spamjim]

...and there's two 37__ regions separated by a 38__ and 39__ region. Oy, you Dutch have my sympathy. You're just as messed up as we are.

Here's a lead to a PHP class to locate nearby zipcodes:

http://www.micahcarrick.com/04-19-2005/php-zip-code-range-and-distance-calculation.html

 

[patrickstrijdonck]

Yeh your right, around 15, is also 19, and thats not far away :S

well thanks anyway for showing that picture to me, although i thought i was finished :S

Gonna try the Geo-coordinates will post soon if it works

 

[patrickstrijdonck]

ive found this piece of code, and works perfect, only i need to put this in a WHERE condition, so that PHP will check every postcode of every record in the database, i will explane what i want to accomplace;

i have more then 100 personal with adresses and Postcodes
when i create a (lets call it) "schoolday", i can setup a city with postcode, what PHP needs to do, is update the records, with a postcode that is closer then 50KM from the City postcode from schoolday,

all those records with personel has also a postcode, if the postcode is closer to the city postcode it must update the record, if not leave it alone!!

Hope this is clear to you?

<?php 
/** 
 * GETCOORD 
 * Uses Google Maps to resolve the coordinates of a postal code 
 */ 

function getCoord($postcode) 
{ 
    // Je google api key 
    $apikey = 'abcdefg'; 

    $xml = simplexml_load_file('[URL unfurl="true"]http://maps.google.com/maps/geo?q='[/URL] . $postcode . '%20Netherlands&output=xml&key=' . $apikey); 
// Of voor 'n nederlandse vertaling van je plaats namen 
// $xml = simplexml_load_file('[URL unfurl="true"]http://maps.google.nl/maps/geo?q='[/URL] . $postcode . '%20Nederland&output=xml&key=' . $apikey); 
    if ($xml && $xml->Response->Status->code == '200') { 
        $adres = $xml->Response->Placemark->address; 
        list($lng, $lat) = explode(',', (string) $xml->Response->Placemark->Point->coordinates); 
        // print_r($xml); 
        return array('Lat' => (float) $lat, 'Lng' => (float) $lng, 'Adres' => (string) $adres);  
    } 
    return false; 
} 

/** 
 * CALCDISTANCE 
 * Calculates the distance between to postal codes 
 */ 
function calcDistance($postal1, $postal2) 
{ 
    $dst1 = getCoord($postal1); 
    // evt 1 fixed eigen coordinaat 
    // Bijv Paleis Soestdijk 3744AA Baarn 
    // $dst1 = array('Lat' => 52.198269, 'Lng' => 5.278128, 'Adres' =>  'Baarn'); 
    $dst2 = getCoord($postal2); 

    if (!$dst1 or !$dst2) 
        return false; // Invalid postal codes 

    $km = rad2deg(acos(sin(deg2rad($dst1['Lat'])) * sin(deg2rad($dst2['Lat'])) +   
        cos(deg2rad($dst1['Lat'])) * cos(deg2rad($dst2['Lat'])) *  
        cos(deg2rad($dst1['Lng'] - $dst2['Lng'])))) * 60 * 1.1515 * 1.609344;  

    return array($dst1['Adres'], $dst2['Adres'], number_format($km, 2, '.', '')); 
} 

print_r(calcDistance('5644PB', '3744AA')); 
?>