utils/setup.php

   1 #!/usr/bin/php -Cq
   2 <?php
   3
   4         require_once(dirname(dirname(__FILE__)).'/lib/init-cmd.php');
   5         ini_set('memory_limit', '800M');
   6
   7         $aCMDOptions = array(
   8                 "Create and setup nominatim search system",
   9                 array('help', 'h', 0, 1, 0, 0, false, 'Show Help'),
  10                 array('quiet', 'q', 0, 1, 0, 0, 'bool', 'Quiet output'),
  11                 array('verbose', 'v', 0, 1, 0, 0, 'bool', 'Verbose output'),
  12
  13                 array('osm-file', '', 0, 1, 1, 1, 'realpath', 'File to import'),
  14                 array('threads', '', 0, 1, 1, 1, 'int', 'Number of threads (where possible)'),
  15
  16                 array('all', '', 0, 1, 0, 0, 'bool', 'Do the complete process'),
  17
  18                 array('create-db', '', 0, 1, 0, 0, 'bool', 'Create nominatim db'),
  19                 array('setup-db', '', 0, 1, 0, 0, 'bool', 'Build a blank nominatim db'),
  20                 array('import-data', '', 0, 1, 0, 0, 'bool', 'Import a osm file'),
  21                 array('osm2pgsql-cache', '', 0, 1, 1, 1, 'int', 'Cache size used by osm2pgsql'),
  22                 array('create-functions', '', 0, 1, 0, 0, 'bool', 'Create functions'),
  23                 array('enable-diff-updates', '', 0, 1, 0, 0, 'bool', 'Turn on the code required to make diff updates work'),
  24                 array('enable-debug-statements', '', 0, 1, 0, 0, 'bool', 'Include debug warning statements in pgsql commands'),
  25                 array('ignore-errors', '', 0, 1, 0, 0, 'bool', 'Continue import even when errors in SQL are present (EXPERT)'),
  26                 array('create-minimal-tables', '', 0, 1, 0, 0, 'bool', 'Create minimal main tables'),
  27                 array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
  28                 array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'),
  29                 array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'),
  30                 array('no-partitions', '', 0, 1, 0, 0, 'bool', "Do not partition search indices (speeds up import of single country extracts)"),
  31                 array('import-wikipedia-articles', '', 0, 1, 0, 0, 'bool', 'Import wikipedia article dump'),
  32                 array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'),
  33                 array('disable-token-precalc', '', 0, 1, 0, 0, 'bool', 'Disable name precalculation (EXPERT)'),
  34                 array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'),
  35                 array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
  36                 array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'),
  37                 array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'),
  38                 array('index-noanalyse', '', 0, 1, 0, 0, 'bool', 'Do not perform analyse operations during index (EXPERT)'),
  39                 array('index-output', '', 0, 1, 1, 1, 'string', 'File to dump index information to'),
  40                 array('create-search-indices', '', 0, 1, 0, 0, 'bool', 'Create additional indices required for search and update'),
  41                 array('create-website', '', 0, 1, 1, 1, 'realpath', 'Create symlinks to setup web directory'),
  42         );
  43         getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true);
  44
  45         $bDidSomething = false;
  46
  47         // Check if osm-file is set and points to a valid file if --all or --import-data is given
  48         if ($aCMDResult['import-data'] || $aCMDResult['all'])
  49         {
  50                 if (!isset($aCMDResult['osm-file']))
  51                 {
  52                         fail('missing --osm-file for data import');
  53                 }
  54
  55                 if (!file_exists($aCMDResult['osm-file']))
  56                 {
  57                         fail('the path supplied to --osm-file does not exist');
  58                 }
  59
  60                 if (!is_readable($aCMDResult['osm-file']))
  61                 {
  62                         fail('osm-file "'.$aCMDResult['osm-file'].'" not readable');
  63                 }
  64         }
  65
  66
  67         // This is a pretty hard core default - the number of processors in the box - 1
  68         $iInstances = isset($aCMDResult['threads'])?$aCMDResult['threads']:(getProcessorCount()-1);
  69         if ($iInstances < 1)
  70         {
  71                 $iInstances = 1;
  72                 echo "WARNING: resetting threads to $iInstances\n";
  73         }
  74         if ($iInstances > getProcessorCount())
  75         {
  76                 $iInstances = getProcessorCount();
  77                 echo "WARNING: resetting threads to $iInstances\n";
  78         }
  79
  80         // Assume we can steal all the cache memory in the box (unless told otherwise)
  81         if (isset($aCMDResult['osm2pgsql-cache']))
  82         {
  83                 $iCacheMemory = $aCMDResult['osm2pgsql-cache'];
  84         }
  85         else
  86         {
  87                 $iCacheMemory = getCacheMemoryMB();
  88         }
  89
  90         $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
  91         if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
  92
  93         $fPostgisVersion = (float) CONST_Postgis_Version;
  94
  95         if ($aCMDResult['create-db'] || $aCMDResult['all'])
  96         {
  97                 echo "Create DB\n";
  98                 $bDidSomething = true;
  99                 $oDB =& DB::connect(CONST_Database_DSN, false);
 100                 if (!PEAR::isError($oDB))
 101                 {
 102                         fail('database already exists ('.CONST_Database_DSN.')');
 103                 }
 104                 passthruCheckReturn('createdb -E UTF-8 -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
 105         }
 106
 107         if ($aCMDResult['setup-db'] || $aCMDResult['all'])
 108         {
 109                 echo "Setup DB\n";
 110                 $bDidSomething = true;
 111                 // TODO: path detection, detection memory, etc.
 112
 113                 $oDB =& getDB();
 114
 115                 $sVersionString = $oDB->getOne('select version()');
 116                 preg_match('#PostgreSQL ([0-9]+)[.]([0-9]+)[^0-9]#', $sVersionString, $aMatches);
 117                 if (CONST_Postgresql_Version != $aMatches[1].'.'.$aMatches[2])
 118                 {
 119                         echo "ERROR: PostgreSQL version is not correct.  Expected ".CONST_Postgresql_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n";
 120                         exit;
 121                 }
 122
 123                 passthru('createlang plpgsql -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
 124                 $pgver = (float) CONST_Postgresql_Version;
 125                 if ($pgver < 9.1) {
 126                         pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/hstore.sql');
 127                         pgsqlRunScriptFile(CONST_BasePath.'/sql/hstore_compatability_9_0.sql');
 128                 } else {
 129                         pgsqlRunScript('CREATE EXTENSION hstore');
 130                 }
 131
 132                 if ($fPostgisVersion < 2.0) {
 133                         pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
 134                         pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
 135                 } else {
 136                         pgsqlRunScript('CREATE EXTENSION postgis');
 137                 }
 138                 if ($fPostgisVersion < 2.1) {
 139                         // Function was renamed in 2.1 and throws an annoying deprecation warning
 140                         pgsqlRunScript('ALTER FUNCTION st_line_interpolate_point(geometry, double precision) RENAME TO ST_LineInterpolatePoint');
 141                 }
 142                 $sVersionString = $oDB->getOne('select postgis_full_version()');
 143                 preg_match('#POSTGIS="([0-9]+)[.]([0-9]+)[.]([0-9]+)( r([0-9]+))?"#', $sVersionString, $aMatches);
 144                 if (CONST_Postgis_Version != $aMatches[1].'.'.$aMatches[2])
 145                 {
 146                         echo "ERROR: PostGIS version is not correct.  Expected ".CONST_Postgis_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n";
 147                         exit;
 148                 }
 149
 150                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
 151                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql');
 152                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql');
 153                 pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode_table.sql');
 154                 if (file_exists(CONST_BasePath.'/data/gb_postcode_data.sql.gz'))
 155                 {
 156                         pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode_data.sql.gz');
 157                 }
 158                 else
 159                 {
 160                         echo "WARNING: external UK postcode table not found.\n";
 161                 }
 162                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_statecounty.sql');
 163                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_state.sql');
 164                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
 165
 166                 if ($aCMDResult['no-partitions'])
 167                 {
 168                         pgsqlRunScript('update country_name set partition = 0');
 169                 }
 170
 171                 // the following will be needed by create_functions later but
 172                 // is only defined in the subsequently called create_tables.
 173                 // Create dummies here that will be overwritten by the proper
 174                 // versions in create-tables.
 175                 pgsqlRunScript('CREATE TABLE place_boundingbox ()');
 176                 pgsqlRunScript('create type wikipedia_article_match as ()');
 177         }
 178
 179         if ($aCMDResult['import-data'] || $aCMDResult['all'])
 180         {
 181                 echo "Import\n";
 182                 $bDidSomething = true;
 183
 184                 $osm2pgsql = CONST_Osm2pgsql_Binary;
 185                 if (!file_exists($osm2pgsql))
 186                 {
 187                         echo "Please download and build osm2pgsql.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 188                         fail("osm2pgsql not found in '$osm2pgsql'");
 189                 }
 190
 191                 if (!is_null(CONST_Osm2pgsql_Flatnode_File))
 192                 {
 193                         $osm2pgsql .= ' --flat-nodes '.CONST_Osm2pgsql_Flatnode_File;
 194                 }
 195                 if (CONST_Tablespace_Osm2pgsql_Data)
 196                         $osm2pgsql .= ' --tablespace-slim-data '.CONST_Tablespace_Osm2pgsql_Data;
 197                 if (CONST_Tablespace_Osm2pgsql_Index)
 198                         $osm2pgsql .= ' --tablespace-slim-index '.CONST_Tablespace_Osm2pgsql_Index;
 199                 if (CONST_Tablespace_Place_Data)
 200                         $osm2pgsql .= ' --tablespace-main-data '.CONST_Tablespace_Place_Data;
 201                 if (CONST_Tablespace_Place_Index)
 202                         $osm2pgsql .= ' --tablespace-main-index '.CONST_Tablespace_Place_Index;
 203                 $osm2pgsql .= ' -lsc -O gazetteer --hstore';
 204                 $osm2pgsql .= ' -C 25000';
 205                 $osm2pgsql .= ' -P '.$aDSNInfo['port'];
 206                 $osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
 207                 passthruCheckReturn($osm2pgsql);
 208
 209                 $oDB =& getDB();
 210                 $x = $oDB->getRow('select * from place limit 1');
 211                 if (PEAR::isError($x)) {
 212                         fail($x->getMessage());
 213                 }
 214                 if (!$x) fail('No Data');
 215         }
 216
 217         if ($aCMDResult['create-functions'] || $aCMDResult['all'])
 218         {
 219                 echo "Functions\n";
 220                 $bDidSomething = true;
 221                 if (!file_exists(CONST_BasePath.'/module/nominatim.so')) fail("nominatim module not built");
 222                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 223                 $sTemplate = str_replace('{modulepath}', CONST_BasePath.'/module', $sTemplate);
 224                 if ($aCMDResult['enable-diff-updates']) $sTemplate = str_replace('RETURN NEW; -- @DIFFUPDATES@', '--', $sTemplate);
 225                 if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
 226                 if (CONST_Limit_Reindexing) $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
 227                 pgsqlRunScript($sTemplate);
 228
 229                 if ($fPostgisVersion < 2.0) {
 230                         echo "Helper functions for postgis < 2.0\n";
 231                         $sTemplate = file_get_contents(CONST_BasePath.'/sql/postgis_15_aux.sql');
 232                 } else {
 233                         echo "Helper functions for postgis >= 2.0\n";
 234                         $sTemplate = file_get_contents(CONST_BasePath.'/sql/postgis_20_aux.sql');
 235                 }
 236                 pgsqlRunScript($sTemplate);
 237         }
 238
 239         if ($aCMDResult['create-minimal-tables'])
 240         {
 241                 echo "Minimal Tables\n";
 242                 $bDidSomething = true;
 243                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables-minimal.sql');
 244
 245                 $sScript = '';
 246
 247                 // Backstop the import process - easliest possible import id
 248                 $sScript .= "insert into import_npi_log values (18022);\n";
 249
 250                 $hFile = @fopen(CONST_BasePath.'/settings/partitionedtags.def', "r");
 251                 if (!$hFile) fail('unable to open list of partitions: '.CONST_BasePath.'/settings/partitionedtags.def');
 252
 253                 while (($sLine = fgets($hFile, 4096)) !== false && $sLine && substr($sLine,0,1) !='#')
 254                 {
 255                         list($sClass, $sType) = explode(' ', trim($sLine));
 256                         $sScript .= "create table place_classtype_".$sClass."_".$sType." as ";
 257                         $sScript .= "select place_id as place_id,geometry as centroid from placex limit 0;\n";
 258
 259                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_centroid ";
 260                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING GIST (centroid);\n";
 261
 262                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_place_id ";
 263                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING btree(place_id);\n";
 264                 }
 265                 fclose($hFile);
 266                 pgsqlRunScript($sScript);
 267         }
 268
 269         if ($aCMDResult['create-tables'] || $aCMDResult['all'])
 270         {
 271                 $bDidSomething = true;
 272
 273                 echo "Tables\n";
 274                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tables.sql');
 275                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
 276                 $sTemplate = replace_tablespace('{ts:address-data}',
 277                                                 CONST_Tablespace_Address_Data, $sTemplate);
 278                 $sTemplate = replace_tablespace('{ts:address-index}',
 279                                                 CONST_Tablespace_Address_Index, $sTemplate);
 280                 $sTemplate = replace_tablespace('{ts:search-data}',
 281                                                 CONST_Tablespace_Search_Data, $sTemplate);
 282                 $sTemplate = replace_tablespace('{ts:search-index}',
 283                                                 CONST_Tablespace_Search_Index, $sTemplate);
 284                 $sTemplate = replace_tablespace('{ts:aux-data}',
 285                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 286                 $sTemplate = replace_tablespace('{ts:aux-index}',
 287                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 288                 pgsqlRunScript($sTemplate, false);
 289
 290                 // re-run the functions
 291                 echo "Functions\n";
 292                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 293                 $sTemplate = str_replace('{modulepath}',
 294                                              CONST_BasePath.'/module', $sTemplate);
 295                 pgsqlRunScript($sTemplate);
 296         }
 297
 298         if ($aCMDResult['create-partition-tables'] || $aCMDResult['all'])
 299         {
 300                 echo "Partition Tables\n";
 301                 $bDidSomething = true;
 302                 $oDB =& getDB();
 303                 $sSQL = 'select distinct partition from country_name';
 304                 $aPartitions = $oDB->getCol($sSQL);
 305                 if (PEAR::isError($aPartitions))
 306                 {
 307                         fail($aPartitions->getMessage());
 308                 }
 309                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 310
 311                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
 312                 $sTemplate = replace_tablespace('{ts:address-data}',
 313                                                 CONST_Tablespace_Address_Data, $sTemplate);
 314                 $sTemplate = replace_tablespace('{ts:address-index}',
 315                                                 CONST_Tablespace_Address_Index, $sTemplate);
 316                 $sTemplate = replace_tablespace('{ts:search-data}',
 317                                                 CONST_Tablespace_Search_Data, $sTemplate);
 318                 $sTemplate = replace_tablespace('{ts:search-index}',
 319                                                 CONST_Tablespace_Search_Index, $sTemplate);
 320                 $sTemplate = replace_tablespace('{ts:aux-data}',
 321                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 322                 $sTemplate = replace_tablespace('{ts:aux-index}',
 323                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 324                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 325                 foreach($aMatches as $aMatch)
 326                 {
 327                         $sResult = '';
 328                         foreach($aPartitions as $sPartitionName)
 329                         {
 330                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 331                         }
 332                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 333                 }
 334
 335                 pgsqlRunScript($sTemplate);
 336         }
 337
 338
 339         if ($aCMDResult['create-partition-functions'] || $aCMDResult['all'])
 340         {
 341                 echo "Partition Functions\n";
 342                 $bDidSomething = true;
 343                 $oDB =& getDB();
 344                 $sSQL = 'select distinct partition from country_name';
 345                 $aPartitions = $oDB->getCol($sSQL);
 346                 if (PEAR::isError($aPartitions))
 347                 {
 348                         fail($aPartitions->getMessage());
 349                 }
 350                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 351
 352                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
 353                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 354                 foreach($aMatches as $aMatch)
 355                 {
 356                         $sResult = '';
 357                         foreach($aPartitions as $sPartitionName)
 358                         {
 359                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 360                         }
 361                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 362                 }
 363
 364                 pgsqlRunScript($sTemplate);
 365         }
 366
 367         if ($aCMDResult['import-wikipedia-articles'] || $aCMDResult['all'])
 368         {
 369                 $bDidSomething = true;
 370                 $sWikiArticlesFile = CONST_BasePath.'/data/wikipedia_article.sql.bin';
 371                 $sWikiRedirectsFile = CONST_BasePath.'/data/wikipedia_redirect.sql.bin';
 372                 if (file_exists($sWikiArticlesFile))
 373                 {
 374                         echo "Importing wikipedia articles...";
 375                         pgsqlRunDropAndRestore($sWikiArticlesFile);
 376                         echo "...done\n";
 377                 }
 378                 else
 379                 {
 380                         echo "WARNING: wikipedia article dump file not found - places will have default importance\n";
 381                 }
 382                 if (file_exists($sWikiRedirectsFile))
 383                 {
 384                         echo "Importing wikipedia redirects...";
 385                         pgsqlRunDropAndRestore($sWikiRedirectsFile);
 386                         echo "...done\n";
 387                 }
 388                 else
 389                 {
 390                         echo "WARNING: wikipedia redirect dump file not found - some place importance values may be missing\n";
 391                 }
 392         }
 393
 394
 395         if ($aCMDResult['load-data'] || $aCMDResult['all'])
 396         {
 397                 echo "Drop old Data\n";
 398                 $bDidSomething = true;
 399
 400                 $oDB =& getDB();
 401                 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
 402                 echo '.';
 403                 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
 404                 echo '.';
 405                 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
 406                 echo '.';
 407                 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
 408                 echo '.';
 409                 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
 410                 echo '.';
 411                 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
 412                 echo '.';
 413                 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
 414                 echo '.';
 415                 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
 416                 echo '.';
 417                 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
 418                 echo '.';
 419
 420                 $sSQL = 'select distinct partition from country_name';
 421                 $aPartitions = $oDB->getCol($sSQL);
 422                 if (PEAR::isError($aPartitions))
 423                 {
 424                         fail($aPartitions->getMessage());
 425                 }
 426                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 427                 foreach($aPartitions as $sPartition)
 428                 {
 429                         if (!pg_query($oDB->connection, 'TRUNCATE location_road_'.$sPartition)) fail(pg_last_error($oDB->connection));
 430                         echo '.';
 431                 }
 432
 433                 // used by getorcreate_word_id to ignore frequent partial words
 434                 if (!pg_query($oDB->connection, 'CREATE OR REPLACE FUNCTION get_maxwordfreq() RETURNS integer AS $$ SELECT '.CONST_Max_Word_Frequency.' as maxwordfreq; $$ LANGUAGE SQL IMMUTABLE')) fail(pg_last_error($oDB->connection));
 435                 echo ".\n";
 436
 437                 // pre-create the word list
 438                 if (!$aCMDResult['disable-token-precalc'])
 439                 {
 440                         echo "Loading word list\n";
 441                         pgsqlRunScriptFile(CONST_BasePath.'/data/words.sql');
 442                 }
 443
 444                 echo "Load Data\n";
 445                 $aDBInstances = array();
 446                 for($i = 0; $i < $iInstances; $i++)
 447                 {
 448                         $aDBInstances[$i] =& getDB(true);
 449                         $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
 450                         $sSQL .= 'housenumber, street, addr_place, isin, postcode, country_code, extratags, ';
 451                         $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
 452                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 453                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 454                 }
 455                 $bAnyBusy = true;
 456                 while($bAnyBusy)
 457                 {
 458                         $bAnyBusy = false;
 459                         for($i = 0; $i < $iInstances; $i++)
 460                         {
 461                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 462                         }
 463                         sleep(1);
 464                         echo '.';
 465                 }
 466                 echo "\n";
 467                 echo "Reanalysing database...\n";
 468                 pgsqlRunScript('ANALYSE');
 469         }
 470
 471         if ($aCMDResult['import-tiger-data'])
 472         {
 473                 $bDidSomething = true;
 474
 475                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_start.sql');
 476                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
 477                 $sTemplate = replace_tablespace('{ts:aux-data}',
 478                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 479                 $sTemplate = replace_tablespace('{ts:aux-index}',
 480                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 481                 pgsqlRunScript($sTemplate, false);
 482
 483                 $aDBInstances = array();
 484                 for($i = 0; $i < $iInstances; $i++)
 485                 {
 486                         $aDBInstances[$i] =& getDB(true);
 487                 }
 488
 489                 foreach(glob(CONST_Tiger_Data_Path.'/*.sql') as $sFile)
 490                 {
 491                         echo $sFile.': ';
 492                         $hFile = fopen($sFile, "r");
 493                         $sSQL = fgets($hFile, 100000);
 494                         $iLines = 0;
 495
 496                         while(true)
 497                         {
 498                                 for($i = 0; $i < $iInstances; $i++)
 499                                 {
 500                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
 501                                         {
 502                                                 while(pg_get_result($aDBInstances[$i]->connection));
 503                                                 $sSQL = fgets($hFile, 100000);
 504                                                 if (!$sSQL) break 2;
 505                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 506                                                 $iLines++;
 507                                                 if ($iLines == 1000)
 508                                                 {
 509                                                         echo ".";
 510                                                         $iLines = 0;
 511                                                 }
 512                                         }
 513                                 }
 514                                 usleep(10);
 515                         }
 516
 517                         fclose($hFile);
 518
 519                         $bAnyBusy = true;
 520                         while($bAnyBusy)
 521                         {
 522                                 $bAnyBusy = false;
 523                                 for($i = 0; $i < $iInstances; $i++)
 524                                 {
 525                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 526                                 }
 527                                 usleep(10);
 528                         }
 529                         echo "\n";
 530                 }
 531
 532                 echo "Creating indexes\n";
 533                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_finish.sql');
 534                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
 535                 $sTemplate = replace_tablespace('{ts:aux-data}',
 536                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 537                 $sTemplate = replace_tablespace('{ts:aux-index}',
 538                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 539                 pgsqlRunScript($sTemplate, false);
 540         }
 541
 542         if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
 543         {
 544                 $bDidSomething = true;
 545                 $oDB =& getDB();
 546                 if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
 547                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 548                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
 549                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
 550                 $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
 551                 $sSQL .= "from placex where postcode is not null and calculated_country_code not in ('ie') group by calculated_country_code,postcode) as x";
 552                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 553
 554                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 555                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
 556                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
 557                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 558         }
 559
 560         if ($aCMDResult['osmosis-init'] || $aCMDResult['all'])
 561         {
 562                 $bDidSomething = true;
 563                 $oDB =& getDB();
 564
 565                 if (!file_exists(CONST_Osmosis_Binary))
 566                 {
 567                         echo "Please download osmosis.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 568                         if (!$aCMDResult['all'])
 569                         {
 570                                 fail("osmosis not found in '".CONST_Osmosis_Binary."'");
 571                         }
 572                 }
 573                 else
 574                 {
 575                         if (file_exists(CONST_BasePath.'/settings/configuration.txt'))
 576                         {
 577                                 echo "settings/configuration.txt already exists\n";
 578                         }
 579                         else
 580                         {
 581                                 passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_BasePath.'/settings');
 582                                 // update osmosis configuration.txt with our settings
 583                                 passthru("sed -i 's!baseUrl=.*!baseUrl=".CONST_Replication_Url."!' ".CONST_BasePath.'/settings/configuration.txt');
 584                                 passthru("sed -i 's:maxInterval = .*:maxInterval = ".CONST_Replication_MaxInterval.":' ".CONST_BasePath.'/settings/configuration.txt');
 585                         }
 586
 587                         // Find the last node in the DB
 588                         $iLastOSMID = $oDB->getOne("select max(osm_id) from place where osm_type = 'N'");
 589
 590                         // Lookup the timestamp that node was created (less 3 hours for margin for changsets to be closed)
 591                         $sLastNodeURL = 'http://www.openstreetmap.org/api/0.6/node/'.$iLastOSMID."/1";
 592                         $sLastNodeXML = file_get_contents($sLastNodeURL);
 593                         preg_match('#timestamp="(([0-9]{4})-([0-9]{2})-([0-9]{2})T([0-9]{2}):([0-9]{2}):([0-9]{2})Z)"#', $sLastNodeXML, $aLastNodeDate);
 594                         $iLastNodeTimestamp = strtotime($aLastNodeDate[1]) - (3*60*60);
 595
 596                         // Search for the correct state file - uses file timestamps so need to sort by date descending
 597                         $sRepURL = CONST_Replication_Url."/";
 598                         $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 599                         // download.geofabrik.de:    <a href="000/">000/</a></td><td align="right">26-Feb-2013 11:53  </td>
 600                         // planet.openstreetmap.org: <a href="273/">273/</a>                    2013-03-11 07:41    -
 601                         preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
 602                         if ($aRepMatches)
 603                         {
 604                                 $aPrevRepMatch = false;
 605                                 foreach($aRepMatches as $aRepMatch)
 606                                 {
 607                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 608                                         $aPrevRepMatch = $aRepMatch;
 609                                 }
 610                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 611
 612                                 $sRepURL .= $aRepMatch[1];
 613                                 $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 614                                 preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
 615                                 $aPrevRepMatch = false;
 616                                 foreach($aRepMatches as $aRepMatch)
 617                                 {
 618                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 619                                         $aPrevRepMatch = $aRepMatch;
 620                                 }
 621                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 622
 623                                 $sRepURL .= $aRepMatch[1];
 624                                 $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 625                                 preg_match_all('#<a href="[0-9]{3}.state.txt">([0-9]{3}).state.txt</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
 626                                 $aPrevRepMatch = false;
 627                                 foreach($aRepMatches as $aRepMatch)
 628                                 {
 629                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 630                                         $aPrevRepMatch = $aRepMatch;
 631                                 }
 632                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 633
 634                                 $sRepURL .= $aRepMatch[1].'.state.txt';
 635                                 echo "Getting state file: $sRepURL\n";
 636                                 $sStateFile = file_get_contents($sRepURL);
 637                                 if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
 638                                 file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
 639                                 echo "Updating DB status\n";
 640                                 pg_query($oDB->connection, 'TRUNCATE import_status');
 641                                 $sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
 642                                 pg_query($oDB->connection, $sSQL);
 643                         }
 644                         else
 645                         {
 646                                 if (!$aCMDResult['all'])
 647                                 {
 648                                         fail("Cannot read state file directory.");
 649                                 }
 650                         }
 651                 }
 652         }
 653
 654         if ($aCMDResult['index'] || $aCMDResult['all'])
 655         {
 656                 $bDidSomething = true;
 657                 $sOutputFile = '';
 658                 if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output'];
 659                 $sBaseCmd = CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
 660                 passthruCheckReturn($sBaseCmd.' -R 4');
 661                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 662                 passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
 663                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 664                 passthruCheckReturn($sBaseCmd.' -r 26');
 665         }
 666
 667         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
 668         {
 669                 echo "Search indices\n";
 670                 $bDidSomething = true;
 671                 $oDB =& getDB();
 672                 $sSQL = 'select distinct partition from country_name';
 673                 $aPartitions = $oDB->getCol($sSQL);
 674                 if (PEAR::isError($aPartitions))
 675                 {
 676                         fail($aPartitions->getMessage());
 677                 }
 678                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 679
 680                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
 681                 $sTemplate = replace_tablespace('{ts:address-index}',
 682                                                 CONST_Tablespace_Address_Index, $sTemplate);
 683                 $sTemplate = replace_tablespace('{ts:search-index}',
 684                                                 CONST_Tablespace_Search_Index, $sTemplate);
 685                 $sTemplate = replace_tablespace('{ts:aux-index}',
 686                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 687                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 688                 foreach($aMatches as $aMatch)
 689                 {
 690                         $sResult = '';
 691                         foreach($aPartitions as $sPartitionName)
 692                         {
 693                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 694                         }
 695                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 696                 }
 697
 698                 pgsqlRunScript($sTemplate);
 699         }
 700
 701         if (isset($aCMDResult['create-website']))
 702         {
 703                 $bDidSomething = true;
 704                 $sTargetDir = $aCMDResult['create-website'];
 705                 if (!is_dir($sTargetDir))
 706                 {
 707                         echo "You must create the website directory before calling this function.\n";
 708                         fail("Target directory does not exist.");
 709                 }
 710
 711                 @symlink(CONST_BasePath.'/website/details.php', $sTargetDir.'/details.php');
 712                 @symlink(CONST_BasePath.'/website/reverse.php', $sTargetDir.'/reverse.php');
 713                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/search.php');
 714                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/index.php');
 715                 @symlink(CONST_BasePath.'/website/lookup.php', $sTargetDir.'/lookup.php');
 716                 @symlink(CONST_BasePath.'/website/deletable.php', $sTargetDir.'/deletable.php');
 717                 @symlink(CONST_BasePath.'/website/polygons.php', $sTargetDir.'/polygons.php');
 718                 @symlink(CONST_BasePath.'/website/status.php', $sTargetDir.'/status.php');
 719                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
 720                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
 721                 @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
 722                 echo "Symlinks created\n";
 723
 724                 $sTestFile = @file_get_contents(CONST_Website_BaseURL.'js/tiles.js');
 725                 if (!$sTestFile)
 726                 {
 727                         echo "\nWARNING: Unable to access the website at ".CONST_Website_BaseURL."\n";
 728                         echo "You may want to update settings/local.php with @define('CONST_Website_BaseURL', 'http://[HOST]/[PATH]/');\n";
 729                 }
 730         }
 731
 732         if (!$bDidSomething)
 733         {
 734                 showUsage($aCMDOptions, true);
 735         }
 736         else
 737         {
 738                 echo "Setup finished.\n";
 739         }
 740
 741         function pgsqlRunScriptFile($sFilename)
 742         {
 743                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
 744
 745                 // Convert database DSN to psql parameters
 746                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 747                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 748                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 749
 750                 $ahGzipPipes = null;
 751                 if (preg_match('/\\.gz$/', $sFilename))
 752                 {
 753                         $aDescriptors = array(
 754                                 0 => array('pipe', 'r'),
 755                                 1 => array('pipe', 'w'),
 756                                 2 => array('file', '/dev/null', 'a')
 757                         );
 758                         $hGzipProcess = proc_open('zcat '.$sFilename, $aDescriptors, $ahGzipPipes);
 759                         if (!is_resource($hGzipProcess)) fail('unable to start zcat');
 760                         $aReadPipe = $ahGzipPipes[1];
 761                         fclose($ahGzipPipes[0]);
 762                 }
 763                 else
 764                 {
 765                         $sCMD .= ' -f '.$sFilename;
 766                         $aReadPipe = array('pipe', 'r');
 767                 }
 768
 769                 $aDescriptors = array(
 770                         0 => $aReadPipe,
 771                         1 => array('pipe', 'w'),
 772                         2 => array('file', '/dev/null', 'a')
 773                 );
 774                 $ahPipes = null;
 775                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 776                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 777
 778
 779                 // TODO: error checking
 780                 while(!feof($ahPipes[1]))
 781                 {
 782                         echo fread($ahPipes[1], 4096);
 783                 }
 784                 fclose($ahPipes[1]);
 785
 786                 $iReturn = proc_close($hProcess);
 787                 if ($iReturn > 0)
 788                 {
 789                         fail("pgsql returned with error code ($iReturn)");
 790                 }
 791                 if ($ahGzipPipes)
 792                 {
 793                         fclose($ahGzipPipes[1]);
 794                         proc_close($hGzipProcess);
 795                 }
 796
 797         }
 798
 799         function pgsqlRunScript($sScript, $bfatal = true)
 800         {
 801                 global $aCMDResult;
 802                 // Convert database DSN to psql parameters
 803                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 804                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 805                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 806                 if ($bfatal && !$aCMDResult['ignore-errors'])
 807                         $sCMD .= ' -v ON_ERROR_STOP=1';
 808                 $aDescriptors = array(
 809                         0 => array('pipe', 'r'),
 810                         1 => STDOUT,
 811                         2 => STDERR
 812                 );
 813                 $ahPipes = null;
 814                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
 815                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 816
 817                 while(strlen($sScript))
 818                 {
 819                         $written = fwrite($ahPipes[0], $sScript);
 820                         if ($written <= 0) break;
 821                         $sScript = substr($sScript, $written);
 822                 }
 823                 fclose($ahPipes[0]);
 824                 $iReturn = proc_close($hProcess);
 825                 if ($bfatal && $iReturn > 0)
 826                 {
 827                         fail("pgsql returned with error code ($iReturn)");
 828                 }
 829         }
 830
 831         function pgsqlRunRestoreData($sDumpFile)
 832         {
 833                 // Convert database DSN to psql parameters
 834                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 835                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 836                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
 837
 838                 $aDescriptors = array(
 839                         0 => array('pipe', 'r'),
 840                         1 => array('pipe', 'w'),
 841                         2 => array('file', '/dev/null', 'a')
 842                 );
 843                 $ahPipes = null;
 844                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 845                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 846
 847                 fclose($ahPipes[0]);
 848
 849                 // TODO: error checking
 850                 while(!feof($ahPipes[1]))
 851                 {
 852                         echo fread($ahPipes[1], 4096);
 853                 }
 854                 fclose($ahPipes[1]);
 855
 856                 $iReturn = proc_close($hProcess);
 857         }
 858
 859         function pgsqlRunDropAndRestore($sDumpFile)
 860         {
 861                 // Convert database DSN to psql parameters
 862                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 863                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 864                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
 865
 866                 $aDescriptors = array(
 867                         0 => array('pipe', 'r'),
 868                         1 => array('pipe', 'w'),
 869                         2 => array('file', '/dev/null', 'a')
 870                 );
 871                 $ahPipes = null;
 872                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 873                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 874
 875                 fclose($ahPipes[0]);
 876
 877                 // TODO: error checking
 878                 while(!feof($ahPipes[1]))
 879                 {
 880                         echo fread($ahPipes[1], 4096);
 881                 }
 882                 fclose($ahPipes[1]);
 883
 884                 $iReturn = proc_close($hProcess);
 885         }
 886
 887         function passthruCheckReturn($cmd)
 888         {
 889                 $result = -1;
 890                 passthru($cmd, $result);
 891                 if ($result != 0) fail('Error executing external command: '.$cmd);
 892         }
 893
 894         function replace_tablespace($sTemplate, $sTablespace, $sSql)
 895         {
 896                 if ($sTablespace)
 897                         $sSql = str_replace($sTemplate, 'TABLESPACE "'.$sTablespace.'"',
 898                                             $sSql);
 899                 else
 900                         $sSql = str_replace($sTemplate, '', $sSql);
 901
 902                 return $sSql;
 903         }
 904