« Get remote ip address in PHP even when the user is behind a proxy
af83 @ Google Developer Day 2007 in Paris »
25 05 2007

Retrouver par code postal tous les lieux qui sont du même dépratement, de la même région ou de la même ville (mysql, php)

Posted by: Ori Pekelman in php, sql

EN: Imagine you have a google maps application (or anything else that deals with places) with data in France, now often you would want to get places that are in the same area as one chosen by the user. Here is a bit of PHP code to help you with that...
It will help you select all the places from your database that have the same postal code (easy) are in the same city, are in the same department or are in the same region (a bit harder). All of this is extremely French and of no use anywhere else... Don't forget to download and importfrench_cities_zipcodes.zip into your MySQL database...

CAVEAT: This code has not been tested yet... There may be some typos in here...

FR: Vous voulez faire une petite application Google Maps ou n'importe quel code PHP où vous avez besoin de trouver pour un lieu les autres lieux qui sont de la même région, du même département ou de la même ville... Voilà un bout de code qui pourra vous être utile.
N'oubliez pas de télécharger et d'importer la table french_cities_zipcodes.zipdans votre base MySQL

CAVEAT: TCe code n'a pas encore été testé du tout... il peut y avoir de fautes de frappe...

PLAIN TEXT
PHP:
  1. /**
  2. * get Departments For Region By Postal Code
  3. * EN: This function will return all the French deprtments that are in the same region for a given
  4. * Postal code or the name of the region if $retournerNomRegion=True is passed
  5. * This code is VERY French specific.
  6. * FR: Cette fonction retourne tous les départements Français qui sont dans la même region qu'un code postal donné
  7. * Ou le nom de la région si la valeur $retournerNomRegion=true lui est passée
  8. * <b>USAGE</b>:
  9. * <code>
  10. * //Exemple:
  11. *   getDepartementsForRegionByPostalCode('75005');
  12. *  result:'75','77','78','91','92','93','94','95'
  13. *
  14. *  getDepartementsForRegionByPostalCode('75005');
  15. *  result: 'Ile de France'
  16. * </code>
  17. * @param   $zipcode string (we need to keep leading zeros) zipcode to search for
  18. * @return  always returns true
  19. * @author  ori@af83.com
  20. * @since   Wed March 19 2007 18:09:09 GMT+0200
  21. * @version v 0.01 Wed May 22 2007 18:09:09 GMT+0200
  22. * For an up to date version go to http://dev.af83.com
  23. */
  24.  
  25. function getDepartementsForRegionByPostalCode($zipCode, $retournerNomRegion=false)
  26.  
  27. {
  28. $liste_regions = array (
  29.     "Alsace" => array("67","68"),
  30.     "Aquitaine" => array("24","33","40","47","64"),
  31.     "Auvergne" => array ("03","15","43","63"),
  32.     "Basse-Normandie" => array ("14","50","61"),
  33.     "Bourgogne" => array ("21","58","71","89"),
  34.     "Bretagne" => array ("22","29","35","56"),
  35.     "Centre" => array ("18","28","36","37","41","45"),
  36.     "Champagne-Ardenne" => array ("08","10","51","52"),
  37.     "Corse" => array("20"),
  38.     "DOM-TOM" => array("97"),
  39.     "Franche-Comté" => array ("25","39","70","90"),
  40.     "Haute-Normandie" => array ("27","76"),
  41.     "Ile de France" => array("75","77","78","91","92","93","94","95"),
  42.     "Languedoc-Roussillon" => array("11","30","34","48","66"),
  43.     "Limousin" => array("19","23","87"),
  44.     "Lorraine" => array ("54","55","57","88"),
  45.     "Midi-Pyrénées" => array("09","12","31","32","46","65","81","82"),
  46.     "Nord / Pas-de-Calais" => array("59","62"),
  47.     "Pays de la Loire" => array ("44","49","53","72","85"),
  48.     "Picardie" => array ("02","60","80"),
  49.     "Poitou-Charentes" => array ("16","17","79","86"),
  50.     "PACA" => array("04","05","06","13","83","84"),
  51.     "Rhône-Alpes" => array ("01","07","26","38","42","69","73","74")
  52. );
  53.         $departement = substr($zipCode,0,2);
  54.        
  55.         foreach($liste_regions as $region => $liste_dep)
  56.         {
  57.             if (in_array($departement, $liste_dep))
  58.             {
  59.                 return $retournerNomRegion ? $region: implode (',',$liste_dep);
  60.             }
  61.         }      
  62. }
  63.  
  64. /**
  65. * get SQL For Search By zipcode
  66. * EN: This function will return an sql statement to search for either all places that are in the same postal code,
  67. * All places that are in the same town, all places that are in the same deparment or all places that are from the same region
  68. * We assume here you have a table "places" that has a column for the french zipcode. Change to meet your needs...
  69. * This code is VERY French specific.
  70. * To use this you will need to import the table (mysql format) joined to this blog post (french_cities_zipcodes) it contains the
  71. * correspondance between cities and zipcodes
  72. * FR: Cette fonction retourne une requête SQL pour retrouver par code postal tous les lieux qui sont du même code postal, de la même
  73. * Ville du même Déprtement ou de la même Région
  74. * Le code prend comme postulat l'existence d'une table "places" qui a une colonne "zipcode" pour le code postal.
  75. * Pour l'utiliser vous devez importer la table french_cities_zipcodes jointe à ce billet de blog qui contient la correspondance entre les code postaux et les villes.
  76. *
  77. * <b>USAGE</b>:
  78. * <code>
  79. * //Exemple:
  80. *   getSQLForSearchByzipcode('75005', 'zipcode');
  81. * </code>
  82. * @param   $zipcode string  zipcode to search for (we need to keep leading zeros)
  83. * @return  String Sql Statement
  84. * @author  ori@af83.com
  85. * @since   Wed March 19 2007 18:09:09 GMT+0200
  86. * @version v 0.01 Wed May 22 2007 18:09:09 GMT+0200
  87. * For an up to date version go to http://dev.af83.com
  88. */
  89.  
  90. function $getSQLForSearchByzipcode($zipcode,$resultFrom){
  91.         $sqlSelect=" SELECT * from places as p";
  92.         if($zipcode!='' && is_numeric($zipcode)){
  93.             switch ($resultFrom)
  94.             {   case 'zipcode': //places from the same zipcode
  95.                     $sqlWhere=" WHERE zipcode LIKE '".$zipcode."'  ";
  96.                 break;
  97.                 case 'region':  //places from the same region
  98.                     $zipSearch= getDepartementsForRegionByPostalCode ($zipcode);
  99.                     $sqlWhere=" WHERE LEFT(p.zipcode,2) in (".$zipSearch.")  ";
  100.                 break;
  101.                 case 'departement': // in France all zipcodes from the same department start with the same two numbers which is the deprtment identifier
  102.                     $zipSearch=substr($zipcode,0,2);
  103.                     $sqlWhere=" WHERE LEFT(p.zipcode,2) = $zipSearch  ";
  104.                 break;
  105.                 case 'city': // for this you will need a table containing the correspondance between cities and postal codes in your database
  106.                     $sqlSelect=" SELECT * from places as p left join  `french_cities_zipcodes` as fcz on p.zipcode =fcz.`zipcode` left join `french_cities_zipcodes` as fcz2 on fcz.city=fcz2.city and fcz.departement=fcz2.departement ";
  107.                     $sqlWhere=" WHERE fcz2.zipcode='$zipcode'  ";
  108.                 break;
  109.             }
  110.         }
  111.         return $sqlSelect.$sqlWhere;
  112. }

This entry was posted on Friday, May 25th, 2007 at 4:55 pm and is filed under php, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses to “Retrouver par code postal tous les lieux qui sont du même dépratement, de la même région ou de la même ville (mysql, php)”

  1. Dev Blog AF83 » Blog Archive » Google Developer Day presentation Says:
    June 1st, 2007 at 1:17 pm

    […] Retrouver par code postal tous les lieux qui sont du même dépratement, de la même région ou de l… […]

  2. Vincent Caron Says:
    June 6th, 2007 at 7:15 pm

    I can spoil this one !

    You’ll have some issues with Corsican people, Corsica is a region which is split into 2A and 2B departments. And you don’t want to mess with Corsican people !

    The DOM and TOM are also very different issues and they won’t appreciate being mixed into the same pseudo-department.

    And lust but not least, Bearstech has developped a complete and very bureaucratic conformant French geocoding database for a real eastate PHP site which does automatic demand+offer matching. It has a complete region, department and city list (a hefty 35,000 records if I remember) and redundant data for very efficient ane complete lookups.

    It’s bound to be released as GPL, anyone to help for the packaging effort ?

    For authorized personnel: http://teddy.bearstech.com/viewcvs/demandimmo/trunk/demandimmo.fr/include/geo.inc.php?rev=157&view=markup

  3. Ori Pekelman Says:
    June 8th, 2007 at 6:37 pm

    Will study the Corsican, DOM/TOM questions to get better definition there.. but If you have already some Bearcode I will be happy to help package this for release…

  4. Vincent Caron Says:
    June 12th, 2007 at 1:24 pm

    I finally took the time to extract the PHP code and release a proper SQL dump. It’s all GPL’ed and I’m handling it to Ori right now.

  5. Dev Blog AF83 » Blog Archive » Geo-Data (france), bis repetita the Bears are here! Says:
    June 21st, 2007 at 2:30 pm

    […] Retrouver par code postal tous les lieux qui sont du même dépratement, de la même région ou de l… […]

Leave a Reply

Creative Commons License
This work is licensed under a Creative Commons Attribution 2.0 License.