upgrade/newdirectory-0.0.1/addresses.php

   1 #!/usr/bin/php5
   2 <?php
   3 require_once 'connect.db.inc.php';
   4 require_once 'geocoding.inc.php';
   5
   6 $globals->debug = 0; // Do not store backtraces.
   7
   8 $res = XDB::query('SELECT  MIN(user_id), MAX(user_id)
   9                      FROM  auth_user_md5');
  10
  11 $pids = $res->fetchOneRow();
  12
  13 $minPid = $pids[0];
  14 $maxPid = $pids[1];
  15
  16 echo "This will take a few minutes.\n".
  17
  18 // Fills the 'text' field in profile_addresses.
  19 for ($pid = $minPid; $pid < $maxPid + 1; ++$pid) {
  20     $res  = XDB::iterator("SELECT  a.adrid AS id, a.adr1, a.adr2, a.adr3,
  21                                    UNIX_TIMESTAMP(a.datemaj) AS datemaj,
  22                                    a.postcode, a.city, a.cityid, a.region, a.regiontxt,
  23                                    a.pub, a.country, gp.pays AS countrytxt, gp.display,
  24                                    FIND_IN_SET('coord-checked', a.statut) AS checked,
  25                                    FIND_IN_SET('res-secondaire', a.statut) AS secondaire,
  26                                    FIND_IN_SET('courrier', a.statut) AS mail,
  27                                    FIND_IN_SET('temporaire', a.statut) AS temporary,
  28                                    FIND_IN_SET('active', a.statut) AS current,
  29                                    FIND_IN_SET('pro', a.statut) AS pro,
  30                                    a.glat AS precise_lat, a.glng AS precise_lon
  31                              FROM  adresses AS a
  32                        INNER JOIN  geoloc_pays AS gp ON(gp.a2 = a.country)
  33                             WHERE  uid = {?}
  34                          ORDER BY  adrid",
  35                            $pid);
  36
  37     while ($address = $res->next()) {
  38         $text = get_address_text($address);
  39         XDB::iterator('UPDATE  profile_addresses
  40                           SET  text = {?}
  41                         WHERE  pid = {?} AND type = {?} AND id = {?}',
  42                       $text, $pid, $address['pro'] ? 'job' : 'home', $address['id']);
  43     }
  44 }
  45
  46 echo "Filling the 'text' filles is over. Geocoding will start now and will take a few days\n";
  47
  48 // Tries to geocode all the addresses.
  49 for ($pid = $minPid; $pid < $maxPid + 1; ++$pid) {
  50     $res = XDB::iterator('SELECT  *
  51                             FROM  profile_addresses
  52                            WHERE  pid = {?}',
  53                          $pid);
  54
  55     while ($address = $res->next()) {
  56         $updateTime = $address['updateTime'];
  57         $gmapsGeocoder = new GMapsGeocoder();
  58         $address = $gmapsGeocoder->getGeocodedAddress($address);
  59
  60         if (!isset($address['geoloc'])) {
  61             XDB::execute('DELETE FROM  profile_addresses
  62                                 WHERE  pid = {?} AND id = {?} AND type = {?}',
  63                          $address['pid'], $address['id'], $address['type']);
  64
  65             Geocoder::getAreaId($address, 'administrativeArea');
  66             Geocoder::getAreaId($address, 'subAdministrativeArea');
  67             Geocoder::getAreaId($address, 'locality');
  68             XDB::execute('INSERT INTO  profile_addresses (pid, type, id, flags, accuracy,
  69                                                           text, postalText, postalCode, localityId,
  70                                                           subAdministrativeAreaId, administrativeAreaId,
  71                                                           countryId, latitude, longitude, updateTime, pub, comment,
  72                                                           north, south, east, west)
  73                                VALUES  ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?},
  74                                         {?}, {?}, FROM_UNIXTIME({?}), {?}, {?}, {?}, {?}, {?}, {?})',
  75                          $address['pid'], $address['type'], $address['id'], $flags, $address['accuracy'],
  76                          $address['text'], $address['postalText'], $address['postalCode'], $address['localityId'],
  77                          $address['subAdministrativeAreaId'], $address['administrativeAreaId'],
  78                          $address['countryId'], $address['latitude'], $address['longitude'],
  79                          $updateTime, $address['pub'], $address['comment'],
  80                          $address['north'], $address['south'], $address['east'], $address['west']);
  81         } else {
  82             XDB::execute('UPDATE  profile_addresses
  83                              SET  postalText = {?}
  84                            WHERE  pid = {?} AND id = {?} AND type = {?}',
  85                          $address['postalText'], $address['pid'], $address['id'], $address['type']);
  86         }
  87
  88         sleep(60); // So we don't get blacklisted by Google.
  89     }
  90 }
  91
  92 echo "Geocoding is over.\n";
  93
  94 function get_address_text($adr)
  95 {
  96     $t = "";
  97     if (isset($adr['adr1']) && $adr['adr1']) $t.= $adr['adr1'];
  98     if (isset($adr['adr2']) && $adr['adr2']) $t.= "\n".$adr['adr2'];
  99     if (isset($adr['adr3']) && $adr['adr3']) $t.= "\n".$adr['adr3'];
 100     $l = "";
 101     if (isset($adr['display']) && $adr['display']) {
 102         $keys = explode(' ', $adr['display']);
 103         foreach ($keys as $key) {
 104             if (isset($adr[$key])) {
 105                 $l .= " ".$adr[$key];
 106             } else {
 107                 $l .= " ".$key;
 108             }
 109         }
 110         if ($l) substr($l, 1);
 111     } elseif ($adr['country'] == 'US' || $adr['country'] == 'CA' || $adr['country'] == 'GB') {
 112         if ($adr['city']) $l .= $adr['city'].",\n";
 113         if ($adr['region']) $l .= $adr['region']." ";
 114         if ($adr['postcode']) $l .= $adr['postcode'];
 115     } else {
 116         if (isset($adr['postcode']) && $adr['postcode']) $l .= $adr['postcode']." ";
 117         if (isset($adr['city']) && $adr['city']) $l .= $adr['city'];
 118     }
 119     if ($l) $t .= "\n".trim($l);
 120     if ($adr['country'] != '00' && (!$adr['countrytxt'] || $adr['countrytxt'] == strtoupper($adr['countrytxt']))) {
 121         $res = XDB::query('SELECT  pays
 122                              FROM  geoloc_pays
 123                             WHERE  a2 = {?}',
 124                           $adr['country']);
 125         $adr['countrytxt'] = $res->fetchOneCell();
 126     }
 127     if (isset($adr['countrytxt']) && $adr['countrytxt']) {
 128         $t .= "\n".$adr['countrytxt'];
 129     }
 130     return trim($t);
 131 }
 132
 133 /* vim:set et sw=4 sts=4 ts=4: */
 134 ?>