3 require_once 'connect.db.inc.php';
4 require_once 'geocoding.inc.php';
6 $globals->debug
= 0; // Do not store backtraces.
8 $res = XDB
::query('SELECT MIN(user_id), MAX(user_id)
11 $pids = $res->fetchOneRow();
16 echo "This will take a few minutes.\n".
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
32 INNER JOIN geoloc_pays AS gp ON(gp.a2 = a.country)
37 while ($address = $res->next()) {
38 $text = get_address_text($address);
39 XDB
::iterator('UPDATE profile_addresses
41 WHERE pid = {?} AND type = {?} AND id = {?}',
42 $text, $pid, $address['pro'] ?
'job' : 'home', $address['id']);
46 echo "Filling the 'text' filles is over. Geocoding will start now and will take a few days\n";
48 // Tries to geocode all the addresses.
49 for ($pid = $minPid; $pid < $maxPid +
1; ++
$pid) {
50 $res = XDB
::iterator('SELECT *
51 FROM profile_addresses
55 while ($address = $res->next()) {
56 $updateTime = $address['updateTime'];
57 $gmapsGeocoder = new GMapsGeocoder();
58 $address = $gmapsGeocoder->getGeocodedAddress($address);
60 if (!isset($address['geoloc'])) {
61 // TODO: use address and phone classes to update profile_job_enum and profile_phones once they are done.
63 XDB
::execute('DELETE FROM profile_addresses
64 WHERE pid = {?} AND id = {?} AND type = {?}',
65 $address['pid'], $address['id'], $address['type']);
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']);
84 XDB
::execute('UPDATE profile_addresses
86 WHERE pid = {?} AND id = {?} AND type = {?}',
87 $address['postalText'], $address['pid'], $address['id'], $address['type']);
90 sleep(60); // So we don't get blacklisted by Google.
94 echo "Geocoding is over.\n";
96 function get_address_text($adr)
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'];
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];
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'];
118 if (isset($adr['postcode']) && $adr['postcode']) $l .= $adr['postcode']." ";
119 if (isset($adr['city']) && $adr['city']) $l .= $adr['city'];
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
127 $adr['countrytxt'] = $res->fetchOneCell();
129 if (isset($adr['countrytxt']) && $adr['countrytxt']) {
130 $t .= "\n".$adr['countrytxt'];
135 /* vim:set et sw=4 sts=4 ts=4: */