| 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 | // TODO: use address and phone classes to update profile_job_enum and profile_phones once they are done. |
| 62 | |
| 63 | XDB::execute('DELETE FROM profile_addresses |
| 64 | WHERE pid = {?} AND id = {?} AND type = {?}', |
| 65 | $address['pid'], $address['id'], $address['type']); |
| 66 | |
| 67 | Geocoder::getAreaId($address, 'administrativeArea'); |
| 68 | Geocoder::getAreaId($address, 'subAdministrativeArea'); |
| 69 | Geocoder::getAreaId($address, 'locality'); |
| 70 | XDB::execute('INSERT INTO profile_addresses (pid, type, id, flags, accuracy, |
| 71 | text, postalText, postalCode, localityId, |
| 72 | subAdministrativeAreaId, administrativeAreaId, |
| 73 | countryId, latitude, longitude, updateTime, pub, comment, |
| 74 | north, south, east, west) |
| 75 | VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, |
| 76 | {?}, {?}, FROM_UNIXTIME({?}), {?}, {?}, {?}, {?}, {?}, {?})', |
| 77 | $address['pid'], $address['type'], $address['id'], $flags, $address['accuracy'], |
| 78 | $address['text'], $address['postalText'], $address['postalCode'], $address['localityId'], |
| 79 | $address['subAdministrativeAreaId'], $address['administrativeAreaId'], |
| 80 | $address['countryId'], $address['latitude'], $address['longitude'], |
| 81 | $updateTime, $address['pub'], $address['comment'], |
| 82 | $address['north'], $address['south'], $address['east'], $address['west']); |
| 83 | } else { |
| 84 | XDB::execute('UPDATE profile_addresses |
| 85 | SET postalText = {?} |
| 86 | WHERE pid = {?} AND id = {?} AND type = {?}', |
| 87 | $address['postalText'], $address['pid'], $address['id'], $address['type']); |
| 88 | } |
| 89 | |
| 90 | sleep(60); // So we don't get blacklisted by Google. |
| 91 | } |
| 92 | } |
| 93 | |
| 94 | echo "Geocoding is over.\n"; |
| 95 | |
| 96 | function get_address_text($adr) |
| 97 | { |
| 98 | $t = ""; |
| 99 | if (isset($adr['adr1']) && $adr['adr1']) $t.= $adr['adr1']; |
| 100 | if (isset($adr['adr2']) && $adr['adr2']) $t.= "\n".$adr['adr2']; |
| 101 | if (isset($adr['adr3']) && $adr['adr3']) $t.= "\n".$adr['adr3']; |
| 102 | $l = ""; |
| 103 | if (isset($adr['display']) && $adr['display']) { |
| 104 | $keys = explode(' ', $adr['display']); |
| 105 | foreach ($keys as $key) { |
| 106 | if (isset($adr[$key])) { |
| 107 | $l .= " ".$adr[$key]; |
| 108 | } else { |
| 109 | $l .= " ".$key; |
| 110 | } |
| 111 | } |
| 112 | if ($l) substr($l, 1); |
| 113 | } elseif ($adr['country'] == 'US' || $adr['country'] == 'CA' || $adr['country'] == 'GB') { |
| 114 | if ($adr['city']) $l .= $adr['city'].",\n"; |
| 115 | if ($adr['region']) $l .= $adr['region']." "; |
| 116 | if ($adr['postcode']) $l .= $adr['postcode']; |
| 117 | } else { |
| 118 | if (isset($adr['postcode']) && $adr['postcode']) $l .= $adr['postcode']." "; |
| 119 | if (isset($adr['city']) && $adr['city']) $l .= $adr['city']; |
| 120 | } |
| 121 | if ($l) $t .= "\n".trim($l); |
| 122 | if ($adr['country'] != '00' && (!$adr['countrytxt'] || $adr['countrytxt'] == strtoupper($adr['countrytxt']))) { |
| 123 | $res = XDB::query('SELECT pays |
| 124 | FROM geoloc_pays |
| 125 | WHERE a2 = {?}', |
| 126 | $adr['country']); |
| 127 | $adr['countrytxt'] = $res->fetchOneCell(); |
| 128 | } |
| 129 | if (isset($adr['countrytxt']) && $adr['countrytxt']) { |
| 130 | $t .= "\n".$adr['countrytxt']; |
| 131 | } |
| 132 | return trim($t); |
| 133 | } |
| 134 | |
| 135 | /* vim:set et sw=4 sts=4 ts=4: */ |
| 136 | ?> |