From: Sarah Hoffmann <lonvia@denofr.de>
Date: Sun, 13 Oct 2013 17:49:08 +0000 (+0200)
Subject: Merge remote-tracking branch 'upstream/master'
X-Git-Tag: deploy~571
X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/09bde0baf96af07d5873e952f604fe4e2409e455?hp=-c

Merge remote-tracking branch 'upstream/master'

Conflicts:
	utils/update.php
---

09bde0baf96af07d5873e952f604fe4e2409e455
diff --combined sql/tables.sql
index 244f2036,038a373e..32ff5e87
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@@ -23,6 -23,19 +23,6 @@@ CREATE TABLE import_npi_log 
    event text
    );
  
 ---drop table IF EXISTS query_log;
 -CREATE TABLE query_log (
 -  starttime timestamp,
 -  query text,
 -  ipaddress text,
 -  endtime timestamp,
 -  results integer
 -  );
 -CREATE INDEX idx_query_log ON query_log USING BTREE (starttime);
 -GRANT SELECT ON query_log TO "www-data" ;
 -GRANT INSERT ON query_log TO "www-data" ;
 -GRANT UPDATE ON query_log TO "www-data" ;
 -
  CREATE TABLE new_query_log (
    type text,
    starttime timestamp,
@@@ -30,7 -43,6 +30,7 @@@
    useragent text,
    language text,
    query text,
 +  searchterm text,
    endtime timestamp,
    results integer,
    format text,
@@@ -41,6 -53,9 +41,6 @@@ GRANT INSERT ON new_query_log TO "www-d
  GRANT UPDATE ON new_query_log TO "www-data" ;
  GRANT SELECT ON new_query_log TO "www-data" ;
  
 -create view vw_search_query_log as SELECT substr(query, 1, 50) AS query, starttime, endtime - starttime AS duration, substr(useragent, 1, 20) as 
 -useragent, language, results, ipaddress FROM new_query_log WHERE type = 'search' ORDER BY starttime DESC;
 -
  --drop table IF EXISTS report_log;
  CREATE TABLE report_log (
    starttime timestamp,
@@@ -61,8 -76,8 +61,8 @@@ CREATE TABLE word 
    country_code varchar(2),
    search_name_count INTEGER,
    operator TEXT
 -  );
 -CREATE INDEX idx_word_word_token on word USING BTREE (word_token);
 +  ) TABLESPACE ssd;
 +CREATE INDEX idx_word_word_token on word USING BTREE (word_token) TABLESPACE ssd;
  GRANT SELECT ON word TO "www-data" ;
  DROP SEQUENCE seq_word;
  CREATE SEQUENCE seq_word start 1;
@@@ -117,7 -132,7 +117,7 @@@ CREATE TABLE search_name 
    nameaddress_vector integer[]
    );
  SELECT AddGeometryColumn('search_name', 'centroid', 4326, 'GEOMETRY', 2);
 -CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id);
 +CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id) TABLESPACE ssd;
  
  drop table IF EXISTS place_addressline;
  CREATE TABLE place_addressline (
@@@ -127,8 -142,8 +127,8 @@@
    isaddress boolean,
    distance float,
    cached_rank_address integer
 -  );
 -CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id);
 +  ) TABLESPACE data;
 +CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) TABLESPACE ssd;
  
  drop table IF EXISTS place_boundingbox CASCADE;
  CREATE TABLE place_boundingbox (
@@@ -181,14 -196,14 +181,14 @@@ CREATE TABLE placex 
    wikipedia TEXT, -- calculated wikipedia article name (language:title)
    geometry_sector INTEGER,
    calculated_country_code varchar(2)
 -  );
 +  ) TABLESPACE ssd;
  SELECT AddGeometryColumn('placex', 'centroid', 4326, 'GEOMETRY', 2);
 -CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id);
 -CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id);
 -CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id);
 -CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector);
 -CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry);
 -CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) WHERE osm_type='N' and rank_search < 26;
 +CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) TABLESPACE ssd;
 +CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) TABLESPACE ssd;
 +CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) TABLESPACE ssd;
 +CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) TABLESPACE ssd;
 +CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) TABLESPACE ssd;
 +CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) TABLESPACE ssd WHERE osm_type='N' and rank_search < 26;
  
  --CREATE INDEX idx_placex_indexed ON placex USING BTREE (indexed);
  
@@@ -197,16 -212,9 +197,9 @@@
  DROP SEQUENCE seq_place;
  CREATE SEQUENCE seq_place start 1;
  GRANT SELECT on placex to "www-data" ;
- GRANT UPDATE ON placex to "www-data" ;
  GRANT SELECT ON search_name to "www-data" ;
- GRANT DELETE on search_name to "www-data" ;
- GRANT INSERT on search_name to "www-data" ;
  GRANT SELECT on place_addressline to "www-data" ;
- GRANT INSERT ON place_addressline to "www-data" ;
- GRANT DELETE on place_addressline to "www-data" ;
  GRANT SELECT ON seq_word to "www-data" ;
- GRANT UPDATE ON seq_word to "www-data" ;
- GRANT INSERT ON word to "www-data" ;
  GRANT SELECT ON planet_osm_ways to "www-data" ;
  GRANT SELECT ON planet_osm_rels to "www-data" ;
  GRANT SELECT on location_area to "www-data" ;
@@@ -229,7 -237,7 +222,7 @@@ CREATE TRIGGER place_before_insert BEFO
      FOR EACH ROW EXECUTE PROCEDURE place_insert();
  
  drop index idx_placex_sector;
 -CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);
 +CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id) TABLESPACE ssd;
  
  DROP SEQUENCE seq_postcodes;
  CREATE SEQUENCE seq_postcodes start 1;
@@@ -247,7 -255,7 +240,7 @@@ CREATE TABLE import_polygon_error 
    );
  SELECT AddGeometryColumn('import_polygon_error', 'prevgeometry', 4326, 'GEOMETRY', 2);
  SELECT AddGeometryColumn('import_polygon_error', 'newgeometry', 4326, 'GEOMETRY', 2);
 -CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id);
 +CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id) TABLESPACE ssd;
  GRANT SELECT ON import_polygon_error TO "www-data";
  
  drop table import_polygon_delete;
@@@ -257,7 -265,7 +250,7 @@@ CREATE TABLE import_polygon_delete 
    class TEXT NOT NULL,
    type TEXT NOT NULL
    );
 -CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id);
 +CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id) TABLESPACE ssd;
  GRANT SELECT ON import_polygon_delete TO "www-data";
  
  drop sequence file;
diff --combined utils/setup.php
index d5f6047e,fb16254d..270c1a49
--- a/utils/setup.php
+++ b/utils/setup.php
@@@ -125,7 -125,13 +125,13 @@@
  			pgsqlRunScript('CREATE EXTENSION hstore');
  		}
  
- 		pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
+ 		$fPostgisVersion = (float) CONST_Postgis_Version;
+ 		if ($fPostgisVersion < 2.0) {
+ 			pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
+ 			pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
+ 		} else {
+ 			pgsqlRunScript('CREATE EXTENSION postgis');
+ 		}
  		$sVersionString = $oDB->getOne('select postgis_full_version()');
  		preg_match('#POSTGIS="([0-9]+)[.]([0-9]+)[.]([0-9]+)( r([0-9]+))?"#', $sVersionString, $aMatches);
  		if (CONST_Postgis_Version != $aMatches[1].'.'.$aMatches[2])
@@@ -134,7 -140,6 +140,6 @@@
  			exit;
  		}
  
- 		pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
  		pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
  		pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql');
  		pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql');
@@@ -165,9 -170,8 +170,9 @@@
  		{
  			$osm2pgsql .= ' --flat-nodes '.CONST_Osm2pgsql_Flatnode_File;
  		}
 +		$osm2pgsql .= ' --tablespace-slim-index ssd --tablespace-main-index ssd --tablespace-main-data ssd --tablespace-slim-data data';
  		$osm2pgsql .= ' -lsc -O gazetteer --hstore';
 -		$osm2pgsql .= ' -C '.$iCacheMemory;
 +		$osm2pgsql .= ' -C 18000';
  		$osm2pgsql .= ' -P '.$aDSNInfo['port'];
  		$osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
  		passthruCheckReturn($osm2pgsql);
@@@ -492,7 -496,7 +497,7 @@@
  		$sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
  		$sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
  		$sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
 -		$sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x";
 +		$sSQL .= "from placex where postcode is not null and calculated_country_code not in ('ie') group by calculated_country_code,postcode) as x";
  		if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
  
  		$sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
diff --combined utils/update.php
index 9a509fdf,42a7b59e..7448348f
--- a/utils/update.php
+++ b/utils/update.php
@@@ -47,6 -47,7 +47,6 @@@
  		showUsage($aCMDOptions, true, 'Select either import of hourly or daily');
  	}
  
 -	if (!isset($aResult['index-instances'])) $aResult['index-instances'] = 1;
  	if (!isset($aResult['index-rank'])) $aResult['index-rank'] = 0;
  
  /*
@@@ -359,7 -360,6 +359,7 @@@
  
  	if ($aResult['index'])
  	{
 +		if (!isset($aResult['index-instances'])) $aResult['index-instances'] = 1;
  		passthru(CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -t '.$aResult['index-instances'].' -r '.$aResult['index-rank']);
  	}
  
@@@ -377,7 -377,7 +377,7 @@@
  		$sCMDDownload = $sOsmosisCMD.' --read-replication-interval workingDirectory='.$sOsmosisConfigDirectory.' --simplify-change --write-xml-change '.$sImportFile;
  		$sCMDCheckReplicationLag = $sOsmosisCMD.' -q --read-replication-lag workingDirectory='.$sOsmosisConfigDirectory;
  		$sCMDImport = $sOsm2pgsqlCmd.' '.$sImportFile;
 -		$sCMDIndex = $sBasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -t '.$aResult['index-instances'];
 +		$sCMDIndex = $sBasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'];
  		if (!$aResult['no-npi']) {
  			$sCMDIndex .= '-F ';
  		}
@@@ -417,8 -417,9 +417,9 @@@
  							exec($sCMDCheckReplicationLag, $aReplicationLag, $iErrorLevel); 
  						}
  						// There are new replication files - use osmosis to download the file
- 						echo "\nReplication Delay is ".$aReplicationLag[0]."\n";
+ 						echo "\n".date('Y-m-d H:i:s')." Replication Delay is ".$aReplicationLag[0]."\n";
  					}
+ 					$fStartTime = time();
  					$fCMDStartTime = time();
  					echo $sCMDDownload."\n";
  					exec($sCMDDownload, $sJunk, $iErrorLevel);
@@@ -431,9 -432,10 +432,10 @@@
  					}
  					$iFileSize = filesize($sImportFile);
  					$sBatchEnd = getosmosistimestamp($sOsmosisConfigDirectory);
- 					echo "Completed for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
  					$sSQL = "INSERT INTO import_osmosis_log values ('$sBatchEnd',$iFileSize,'".date('Y-m-d H:i:s',$fCMDStartTime)."','".date('Y-m-d H:i:s')."','osmosis')";
+ 					var_Dump($sSQL);
  					$oDB->query($sSQL);
+ 					echo date('Y-m-d H:i:s')." Completed osmosis step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
  				}
  
  				$iFileSize = filesize($sImportFile);
@@@ -448,10 -450,10 +450,10 @@@
  					echo "Error: $iErrorLevel\n";
  					exit($iErrorLevel);
  				}
- 				echo "Completed for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
  				$sSQL = "INSERT INTO import_osmosis_log values ('$sBatchEnd',$iFileSize,'".date('Y-m-d H:i:s',$fCMDStartTime)."','".date('Y-m-d H:i:s')."','osm2pgsql')";
  				var_Dump($sSQL);
  				$oDB->query($sSQL);
+ 				echo date('Y-m-d H:i:s')." Completed osm2pgsql step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
  
  				// Archive for debug?
  				unlink($sImportFile);
@@@ -460,20 -462,11 +462,20 @@@
  			$sBatchEnd = getosmosistimestamp($sOsmosisConfigDirectory);
  
  			// Index file
 -			$sThisIndexCmd = $sCMDIndex;
 +			if (!isset($aResult['index-instances']))
 +			{
 +				if (getLoadAverage() < 15)
 +					$iIndexInstances = 2;
 +				else
 +					$iIndexInstances = 1;
 +			} else
 +				$iIndexInstances = $aResult['index-instances'];
 +
 +			$sThisIndexCmd = $sCMDIndex.' -t '.$iIndexInstances;
+ 			$fCMDStartTime = time();
  
  			if (!$aResult['no-npi'])
  			{
- 				$fCMDStartTime = time();
  				$iFileID = $oDB->getOne('select nextval(\'file\')');
  				if (PEAR::isError($iFileID))
  				{
@@@ -527,15 -520,16 +529,16 @@@
  				}
  			}
  
- 			echo "Completed for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
  			$sSQL = "INSERT INTO import_osmosis_log values ('$sBatchEnd',$iFileSize,'".date('Y-m-d H:i:s',$fCMDStartTime)."','".date('Y-m-d H:i:s')."','index')";
+ 			var_Dump($sSQL);
  			$oDB->query($sSQL);
+ 			echo date('Y-m-d H:i:s')." Completed index step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
  
  			$sSQL = "update import_status set lastimportdate = '$sBatchEnd'";
  			$oDB->query($sSQL);
  
  			$fDuration = time() - $fStartTime;
- 			echo "Completed for $sBatchEnd in ".round($fDuration/60,2)."\n";
+ 			echo date('Y-m-d H:i:s')." Completed all for $sBatchEnd in ".round($fDuration/60,2)." minutes\n";
  			if (!$aResult['import-osmosis-all']) exit;
  
  			if ( CONST_Replication_Update_Interval > 60 )
@@@ -546,7 -540,7 +549,7 @@@
  			{
  				$iSleep = max(0,CONST_Replication_Update_Interval-$fDuration);
  			}
- 			echo "Sleeping $iSleep seconds\n";
+ 			echo date('Y-m-d H:i:s')." Sleeping $iSleep seconds\n";
  			sleep($iSleep);
  		}