From b4cb22fbbcb622a8f93010a5ff19e71ffc6a2e95 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Tue, 22 Jun 2010 14:19:34 +0200 Subject: [PATCH] Splits filling of profile_addresses and geocoding of the addresses. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- upgrade/newdirectory-0.0.1/README | 2 +- upgrade/newdirectory-0.0.1/addresses.php | 50 ------------------------ upgrade/newdirectory-0.0.1/geocoding.php | 67 ++++++++++++++++++++++++++++++++ 3 files changed, 68 insertions(+), 51 deletions(-) create mode 100644 upgrade/newdirectory-0.0.1/geocoding.php diff --git a/upgrade/newdirectory-0.0.1/README b/upgrade/newdirectory-0.0.1/README index 291c826..241e6aa 100644 --- a/upgrade/newdirectory-0.0.1/README +++ b/upgrade/newdirectory-0.0.1/README @@ -3,4 +3,4 @@ Il faudra déplacer le yourself qui est mis dans profile_display dans le script Le 12_secteurs.sql est à faire avant le alternate_subsubsectors.php et pour que ce dernier fonctionne, il faut que le fichier arbo-UTF8.xml soit dans le même dossier. -Le script addresses.php *doit* être lancé dans un screen - il prend plusieurs jours à tourner. +Le script geocding.php *doit* être lancé dans un screen - il prend plusieurs jours à tourner. diff --git a/upgrade/newdirectory-0.0.1/addresses.php b/upgrade/newdirectory-0.0.1/addresses.php index ad5a4ee..12c0497 100755 --- a/upgrade/newdirectory-0.0.1/addresses.php +++ b/upgrade/newdirectory-0.0.1/addresses.php @@ -44,56 +44,6 @@ for ($pid = $minPid; $pid < $maxPid + 1; ++$pid) { } } -echo "Filling the 'text' filles is over. Geocoding will start now and will take a few days\n"; - -// Tries to geocode all the addresses. -for ($pid = $minPid; $pid < $maxPid + 1; ++$pid) { - $res = XDB::iterator('SELECT * - FROM profile_addresses - WHERE pid = {?}', - $pid); - - while ($address = $res->next()) { - $updateTime = $address['updateTime']; - $gmapsGeocoder = new GMapsGeocoder(); - $address = $gmapsGeocoder->getGeocodedAddress($address); - - if (!isset($address['geoloc'])) { - // TODO: use address and phone classes to update profile_job_enum and profile_phones once they are done. - - XDB::execute('DELETE FROM profile_addresses - WHERE pid = {?} AND id = {?} AND type = {?}', - $address['pid'], $address['id'], $address['type']); - - Geocoder::getAreaId($address, 'administrativeArea'); - Geocoder::getAreaId($address, 'subAdministrativeArea'); - Geocoder::getAreaId($address, 'locality'); - XDB::execute('INSERT INTO profile_addresses (pid, type, id, flags, accuracy, - text, postalText, postalCode, localityId, - subAdministrativeAreaId, administrativeAreaId, - countryId, latitude, longitude, updateTime, pub, comment, - north, south, east, west) - VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, - {?}, {?}, FROM_UNIXTIME({?}), {?}, {?}, {?}, {?}, {?}, {?})', - $address['pid'], $address['type'], $address['id'], $flags, $address['accuracy'], - $address['text'], $address['postalText'], $address['postalCode'], $address['localityId'], - $address['subAdministrativeAreaId'], $address['administrativeAreaId'], - $address['countryId'], $address['latitude'], $address['longitude'], - $updateTime, $address['pub'], $address['comment'], - $address['north'], $address['south'], $address['east'], $address['west']); - } else { - XDB::execute('UPDATE profile_addresses - SET postalText = {?} - WHERE pid = {?} AND id = {?} AND type = {?}', - $address['postalText'], $address['pid'], $address['id'], $address['type']); - } - - sleep(60); // So we don't get blacklisted by Google. - } -} - -echo "Geocoding is over.\n"; - function get_address_text($adr) { $t = ''; diff --git a/upgrade/newdirectory-0.0.1/geocoding.php b/upgrade/newdirectory-0.0.1/geocoding.php new file mode 100644 index 0000000..53bc287 --- /dev/null +++ b/upgrade/newdirectory-0.0.1/geocoding.php @@ -0,0 +1,67 @@ +#!/usr/bin/php5 +debug = 0; // Do not store backtraces. + +$res = XDB::query('SELECT MIN(pid), MAX(pid) + FROM profiles'); + +$pids = $res->fetchOneRow(); + +$minPid = $pids[0]; +$maxPid = $pids[1]; + +echo "Filling the 'text' fied is over. Geocoding will start now and will take a few days.\n"; + +// Tries to geocode all the addresses. +for ($pid = $minPid; $pid < $maxPid + 1; ++$pid) { + $res = XDB::iterator('SELECT * + FROM profile_addresses + WHERE pid = {?}', + $pid); + + while ($address = $res->next()) { + $updateTime = $address['updateTime']; + $gmapsGeocoder = new GMapsGeocoder(); + $address = $gmapsGeocoder->getGeocodedAddress($address); + + if (!isset($address['geoloc'])) { + // TODO: use address and phone classes to update profile_job_enum and profile_phones once they are done. + + XDB::execute('DELETE FROM profile_addresses + WHERE pid = {?} AND id = {?} AND type = {?}', + $address['pid'], $address['id'], $address['type']); + + Geocoder::getAreaId($address, 'administrativeArea'); + Geocoder::getAreaId($address, 'subAdministrativeArea'); + Geocoder::getAreaId($address, 'locality'); + XDB::execute('INSERT INTO profile_addresses (pid, type, id, flags, accuracy, + text, postalText, postalCode, localityId, + subAdministrativeAreaId, administrativeAreaId, + countryId, latitude, longitude, updateTime, pub, comment, + north, south, east, west) + VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, + {?}, {?}, FROM_UNIXTIME({?}), {?}, {?}, {?}, {?}, {?}, {?})', + $address['pid'], $address['type'], $address['id'], $flags, $address['accuracy'], + $address['text'], $address['postalText'], $address['postalCode'], $address['localityId'], + $address['subAdministrativeAreaId'], $address['administrativeAreaId'], + $address['countryId'], $address['latitude'], $address['longitude'], + $updateTime, $address['pub'], $address['comment'], + $address['north'], $address['south'], $address['east'], $address['west']); + } else { + XDB::execute('UPDATE profile_addresses + SET postalText = {?} + WHERE pid = {?} AND id = {?} AND type = {?}', + $address['postalText'], $address['pid'], $address['id'], $address['type']); + } + + sleep(60); // So we don't get blacklisted by Google. + } +} + +echo "Geocoding is over.\n"; + +/* vim:set et sw=4 sts=4 ts=4: */ +?> -- 2.1.4