WebSVN – eFlore/Applications.coel – Diff – /trunk/scripts/modules/201310-bulk-coord-update.php


<?php
/*
  cat >> /etc/php.d/php.ini // /etc/php5/cli/conf.d/ZZ-mycustom.ini <<EOF
pdo.dsn.testcoel = "mysql:dbname=tb_coel_test;host=localhost"
mysql.default_user = telabotap
mysql.default_password =
EOF
*/
if(PHP_SAPI !== 'cli') exit(1);
 
define('DOUBLE_CHECK', FALSE);
 
date_default_timezone_set('Europe/Paris');
$DIR = dirname(__FILE__); // XXX: PHP-5.3
define("_GEO_STORE_CACHE", $DIR . "/nominatim-coords.cache.ser");
 
require_once($DIR . '/../../jrest/services/Coel.php');
$db = new PDO('testcoel',
			  @$argv[1] ? $argv[1] : ini_get('mysql.default_user'),
			  @$argv[2] ? $argv[2] : ini_get('mysql.default_password'),
			  array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'")) or die('no db');
$cache = @unserialize(file_get_contents(_GEO_STORE_CACHE));
 
if(function_exists('pcntl_signal')) {
	// SIGUSR1 handler to save the result during a run
	function savecache($signal = FALSE) {
		global $cache;
		if($cache) {
			ksort($cache, SORT_NUMERIC);
			file_put_contents(_GEO_STORE_CACHE, serialize($cache));
		}
		echo "saved " . count($cache) . " results\n";
		if($signal == SIGINT) exit;
	}
	declare(ticks = 1);
	pcntl_signal(SIGUSR1, 'savecache');
	pcntl_signal(SIGINT, 'savecache');
	echo '$ kill -USR1 ' . posix_getpid() . ' # to flush results to disk' . "\n";
}
 
printf("cache already contains %d entries, will start in 4 seconds:\n", count($cache));
sleep(4);
 
 
 
 
 
 
 
// smooth:
$q = "SELECT * FROM coel_structure".
	" WHERE cs_latitude IS NULL OR cs_latitude = '' OR cs_longitude IS NULL".
	" OR cs_longitude = ''";
foreach($db->query($q)->fetchAll(PDO::FETCH_ASSOC) AS $params) {
	$params_override = array('countrycodes' => 'fr,de,ae');
	$id = $params['cs_id_structure'];
	if(array_key_exists($id, $cache)) {
		$db->query(sprintf("UPDATE coel_structure SET cs_latitude = %.9f, cs_longitude = %.9f WHERE cs_id_structure = %d",
						   $cache[$id]['lat'], $cache[$id]['lon'], $id));
		continue;
	}
	if(array_key_exists($id, @$cache['failed'])) {
		if(!DOUBLE_CHECK) continue;
 
		// mode texte-libre (plus efficace pour les cas tordus)
		$params_override['force-q'] = TRUE;
		// cf (très) mauvaise gestion des CP par Nominatim
		if($params['cs_ville']) {
			unset($params['cs_code_postal']);
		}
		if($params['cs_adresse_01']) {
			$params['cs_adresse_01'] = preg_replace(
				'/.*(?:impasse|chemin|route|rue|avenue|boulevard|place|ville)(?:\s(?:du|des|de)\s)?(la\s)?/i',
				'',
				$params['cs_adresse_01']);
		}
	}
 
	$lonlat = array();
	if(Coel::coordGuess(Coel::addrReStruct($params), $lonlat, $params_override)) {
		unset($cache['failed'][$id]);
		$lat = $lonlat['lat'];
		$lon = $lonlat['lon'];
		$cache[$id] = $lonlat;
	}
	else {
		$cache['failed'][$id] = FALSE;
	}
 
	echo "sleep\n";
	sleep(1.5);
}
if($cache) {
	ksort($cache, SORT_NUMERIC);
	file_put_contents(_GEO_STORE_CACHE, serialize($cache));
	echo "saved " . count($cache) . " results\n";
}
 
 
 
/* raw:
   $q = "SELECT cs_id_structure AS id, cs_adresse_01 AS addr, cs_code_postal AS cp, cs_ville AS city".
   " FROM coel_structure".
   " WHERE cs_latitude IS NULL OR cs_latitude = '' OR cs_longitude IS NULL OR cs_longitude = ''";
   $query = http_build_query(array('q' => implode(',',$q),'accept_language' => 'fr', 'format' => 'json', 'limit' => 1));
   var_dump( json_decode(file_get_contents('http://nominatim.openstreetmap.org/search.php?' . $query)) );
*/

Subversion Repositories eFlore/Applications.coel

(root)/trunk/scripts/modules/201310-bulk-coord-update.php – Rev 1696 → 1713

Rev 1696		Rev 1713
1	`<?php`	1	`<?php`
2	`/*`	2	`/*`
3	`cat >> /etc/php.d/php.ini // /etc/php5/cli/conf.d/ZZ-mycustom.ini <<EOF`	3	`cat >> /etc/php.d/php.ini // /etc/php5/cli/conf.d/ZZ-mycustom.ini <<EOF`
4	`pdo.dsn.testcoel = "mysql:dbname=tb_coel_test;host=localhost"`	4	`pdo.dsn.testcoel = "mysql:dbname=tb_coel_test;host=localhost"`
5	`mysql.default_user = telabotap`	5	`mysql.default_user = telabotap`
6	`mysql.default_password =`	6	`mysql.default_password =`
7	`EOF`	7	`EOF`
8	`*/`	8	`*/`
9	`if(PHP_SAPI !== 'cli') exit(1);`	9	`if(PHP_SAPI !== 'cli') exit(1);`
10		10
11	`define('DOUBLE_CHECK', FALSE);`	11	`define('DOUBLE_CHECK', FALSE);`
12		12
13	`date_default_timezone_set('Europe/Paris');`	13	`date_default_timezone_set('Europe/Paris');`
14	`$DIR = dirname(__FILE__); // XXX: PHP-5.3`	14	`$DIR = dirname(__FILE__); // XXX: PHP-5.3`
15	`define("_GEO_STORE_CACHE", $DIR . "/nominatim-coords.cache.ser");`	15	`define("_GEO_STORE_CACHE", $DIR . "/nominatim-coords.cache.ser");`
16		16
17	`require_once($DIR . '/../../jrest/services/Coel.php');`	17	`require_once($DIR . '/../../jrest/services/Coel.php');`
18	`$db = new PDO('testcoel',`	18	`$db = new PDO('testcoel',`
19	`@$argv[1] ? $argv[1] : ini_get('mysql.default_user'),`	19	`@$argv[1] ? $argv[1] : ini_get('mysql.default_user'),`
20	`@$argv[2] ? $argv[2] : ini_get('mysql.default_password'),`	20	`@$argv[2] ? $argv[2] : ini_get('mysql.default_password'),`
21	`array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'")) or die('no db');`	21	`array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'")) or die('no db');`
22	`$cache = @unserialize(file_get_contents(_GEO_STORE_CACHE));`	22	`$cache = @unserialize(file_get_contents(_GEO_STORE_CACHE));`
23		23
24	`if(function_exists('pcntl_signal')) {`	24	`if(function_exists('pcntl_signal')) {`
25	`// SIGUSR1 handler to save the result during a run`	25	`// SIGUSR1 handler to save the result during a run`
26	`function savecache($signal = FALSE) {`	26	`function savecache($signal = FALSE) {`
27	`global $cache;`	27	`global $cache;`
28	`if($cache) {`	28	`if($cache) {`
29	`ksort($cache, SORT_NUMERIC);`	29	`ksort($cache, SORT_NUMERIC);`
30	`file_put_contents(_GEO_STORE_CACHE, serialize($cache));`	30	`file_put_contents(_GEO_STORE_CACHE, serialize($cache));`
31	`}`	31	`}`
32	`echo "saved " . count($cache) . " results\n";`	32	`echo "saved " . count($cache) . " results\n";`
33	`if($signal == SIGINT) exit;`	33	`if($signal == SIGINT) exit;`
34	`}`	34	`}`
35	`declare(ticks = 1);`	35	`declare(ticks = 1);`
36	`pcntl_signal(SIGUSR1, 'savecache');`	36	`pcntl_signal(SIGUSR1, 'savecache');`
37	`pcntl_signal(SIGINT, 'savecache');`	37	`pcntl_signal(SIGINT, 'savecache');`
38	`echo '$ kill -USR1 ' . posix_getpid() . ' # to flush results to disk' . "\n";`	38	`echo '$ kill -USR1 ' . posix_getpid() . ' # to flush results to disk' . "\n";`
39	`}`	39	`}`
40		40
41	`printf("cache already contains %d entries, will start in 4 seconds:\n", count($cache));`	41	`printf("cache already contains %d entries, will start in 4 seconds:\n", count($cache));`
42	`sleep(4);`	42	`sleep(4);`
43		-
44	`// correction`	-
45	`$db->query('UPDATE coel_structure SET cs_longitude = REPLACE(cs_longitude, ",", "."), cs_latitude = REPLACE(cs_latitude, ",", ".")');`	-
46	`$db->query('ALTER TABLE coel_structure MODIFY cs_longitude DOUBLE(12,9) NULL DEFAULT NULL');`	-
47	`$db->query('ALTER TABLE coel_structure MODIFY cs_latitude DOUBLE(12,9) NULL DEFAULT NULL');`	-
48	`$db->query('UPDATE coel_structure SET cs_latitude = NULL, cs_longitude = NULL WHERE cs_latitude = "0" AND cs_longitude = "0"');`	-
49	`$db->query('UPDATE coel_structure SET cs_nbre_personne = NULL WHERE cs_nbre_personne = 0');`	43
50	`// smooth:`	44	`// smooth:`
51	`$q = "SELECT * FROM coel_structure".`	45	`$q = "SELECT * FROM coel_structure".`
52	`" WHERE cs_latitude IS NULL OR cs_latitude = '' OR cs_longitude IS NULL".`	46	`" WHERE cs_latitude IS NULL OR cs_latitude = '' OR cs_longitude IS NULL".`
53	`" OR cs_longitude = ''";`	47	`" OR cs_longitude = ''";`
54	`foreach($db->query($q)->fetchAll(PDO::FETCH_ASSOC) AS $params) {`	48	`foreach($db->query($q)->fetchAll(PDO::FETCH_ASSOC) AS $params) {`
55	`$params_override = array('countrycodes' => 'fr,de,ae');`	49	`$params_override = array('countrycodes' => 'fr,de,ae');`
56	`$id = $params['cs_id_structure'];`	50	`$id = $params['cs_id_structure'];`
57	`if(array_key_exists($id, $cache)) {`	51	`if(array_key_exists($id, $cache)) {`
58	`$db->query(sprintf("UPDATE coel_structure SET cs_latitude = %.9f, cs_longitude = %.9f WHERE cs_id_structure = %d",`	52	`$db->query(sprintf("UPDATE coel_structure SET cs_latitude = %.9f, cs_longitude = %.9f WHERE cs_id_structure = %d",`
59	`$cache[$id]['lat'], $cache[$id]['lon'], $id));`	53	`$cache[$id]['lat'], $cache[$id]['lon'], $id));`
60	`continue;`	54	`continue;`
61	`}`	55	`}`
62	`if(array_key_exists($id, @$cache['failed'])) {`	56	`if(array_key_exists($id, @$cache['failed'])) {`
63	`if(!DOUBLE_CHECK) continue;`	57	`if(!DOUBLE_CHECK) continue;`
64		58
65	`// mode texte-libre (plus efficace pour les cas tordus)`	59	`// mode texte-libre (plus efficace pour les cas tordus)`
66	`$params_override['force-q'] = TRUE;`	60	`$params_override['force-q'] = TRUE;`
67	`// cf (très) mauvaise gestion des CP par Nominatim`	61	`// cf (très) mauvaise gestion des CP par Nominatim`
68	`if($params['cs_ville']) {`	62	`if($params['cs_ville']) {`
69	`unset($params['cs_code_postal']);`	63	`unset($params['cs_code_postal']);`
70	`}`	64	`}`
71	`if($params['cs_adresse_01']) {`	65	`if($params['cs_adresse_01']) {`
72	`$params['cs_adresse_01'] = preg_replace(`	66	`$params['cs_adresse_01'] = preg_replace(`
73	`'/.*(?:impasse\|chemin\|route\|rue\|avenue\|boulevard\|place\|ville)(?:\s(?:du\|des\|de)\s)?(la\s)?/i',`	67	`'/.*(?:impasse\|chemin\|route\|rue\|avenue\|boulevard\|place\|ville)(?:\s(?:du\|des\|de)\s)?(la\s)?/i',`
74	`'',`	68	`'',`
75	`$params['cs_adresse_01']);`	69	`$params['cs_adresse_01']);`
76	`}`	70	`}`
77	`}`	71	`}`
78		72
79	`$lonlat = array();`	73	`$lonlat = array();`
80	`if(Coel::coordGuess(Coel::addrReStruct($params), $lonlat, $params_override)) {`	74	`if(Coel::coordGuess(Coel::addrReStruct($params), $lonlat, $params_override)) {`
81	`unset($cache['failed'][$id]);`	75	`unset($cache['failed'][$id]);`
82	`$lat = $lonlat['lat'];`	76	`$lat = $lonlat['lat'];`
83	`$lon = $lonlat['lon'];`	77	`$lon = $lonlat['lon'];`
84	`$cache[$id] = $lonlat;`	78	`$cache[$id] = $lonlat;`
85	`}`	79	`}`
86	`else {`	80	`else {`
87	`$cache['failed'][$id] = FALSE;`	81	`$cache['failed'][$id] = FALSE;`
88	`}`	82	`}`
89		83
90	`echo "sleep\n";`	84	`echo "sleep\n";`
91	`sleep(1.5);`	85	`sleep(1.5);`
92	`}`	86	`}`
93	`if($cache) {`	87	`if($cache) {`
94	`ksort($cache, SORT_NUMERIC);`	88	`ksort($cache, SORT_NUMERIC);`
95	`file_put_contents(_GEO_STORE_CACHE, serialize($cache));`	89	`file_put_contents(_GEO_STORE_CACHE, serialize($cache));`
96	`echo "saved " . count($cache) . " results\n";`	90	`echo "saved " . count($cache) . " results\n";`
97	`}`	91	`}`
98		92
99		93
100		94
101	`/* raw:`	95	`/* raw:`
102	`$q = "SELECT cs_id_structure AS id, cs_adresse_01 AS addr, cs_code_postal AS cp, cs_ville AS city".`	96	`$q = "SELECT cs_id_structure AS id, cs_adresse_01 AS addr, cs_code_postal AS cp, cs_ville AS city".`
103	`" FROM coel_structure".`	97	`" FROM coel_structure".`
104	`" WHERE cs_latitude IS NULL OR cs_latitude = '' OR cs_longitude IS NULL OR cs_longitude = ''";`	98	`" WHERE cs_latitude IS NULL OR cs_latitude = '' OR cs_longitude IS NULL OR cs_longitude = ''";`
105	`$query = http_build_query(array('q' => implode(',',$q),'accept_language' => 'fr', 'format' => 'json', 'limit' => 1));`	99	`$query = http_build_query(array('q' => implode(',',$q),'accept_language' => 'fr', 'format' => 'json', 'limit' => 1));`
106	`var_dump( json_decode(file_get_contents('http://nominatim.openstreetmap.org/search.php?' . $query)) );`	100	`var_dump( json_decode(file_get_contents('http://nominatim.openstreetmap.org/search.php?' . $query)) );`
107	`*/`	101	`*/`