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('create-minimal-tables', '', 0, 1, 0, 0, 'bool', 'Create minimal main tables'),
  26                 array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
  27                 array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'),
  28                 array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'),
  29                 array('import-wikipedia-articles', '', 0, 1, 0, 0, 'bool', 'Import wikipedia article dump'),
  30                 array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'),
  31                 array('disable-token-precalc', '', 0, 1, 0, 0, 'bool', 'Disable name precalculation (EXPERT)'),
  32                 array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'),
  33                 array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
  34                 array('create-roads', '', 0, 1, 0, 0, 'bool', ''),
  35                 array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'),
  36                 array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'),
  37                 array('index-noanalyse', '', 0, 1, 0, 0, 'bool', 'Do not perform analyse operations during index (EXPERT)'),
  38                 array('index-output', '', 0, 1, 1, 1, 'string', 'File to dump index information to'),
  39                 array('create-search-indices', '', 0, 1, 0, 0, 'bool', 'Create additional indices required for search and update'),
  40                 array('create-website', '', 0, 1, 1, 1, 'realpath', 'Create symlinks to setup web directory'),
  41         );
  42         getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true);
  43
  44         $bDidSomething = false;
  45
  46         // Check if osm-file is set and points to a valid file if --all or --import-data is given
  47         if ($aCMDResult['import-data'] || $aCMDResult['all'])
  48         {
  49                 if (!isset($aCMDResult['osm-file']))
  50                 {
  51                         fail('missing --osm-file for data import');
  52                 }
  53
  54                 if (!file_exists($aCMDResult['osm-file']))
  55                 {
  56                         fail('the path supplied to --osm-file does not exist');
  57                 }
  58
  59                 if (!is_readable($aCMDResult['osm-file']))
  60                 {
  61                         fail('osm-file "'.$aCMDResult['osm-file'].'" not readable');
  62                 }
  63         }
  64
  65
  66         // This is a pretty hard core default - the number of processors in the box - 1
  67         $iInstances = isset($aCMDResult['threads'])?$aCMDResult['threads']:(getProcessorCount()-1);
  68         if ($iInstances < 1)
  69         {
  70                 $iInstances = 1;
  71                 echo "WARNING: resetting threads to $iInstances\n";
  72         }
  73         if ($iInstances > getProcessorCount())
  74         {
  75                 $iInstances = getProcessorCount();
  76                 echo "WARNING: resetting threads to $iInstances\n";
  77         }
  78
  79         // Assume we can steal all the cache memory in the box (unless told otherwise)
  80         $iCacheMemory = (isset($aCMDResult['osm2pgsql-cache'])?$aCMDResult['osm2pgsql-cache']:getCacheMemoryMB());
  81         if ($iCacheMemory > getTotalMemoryMB())
  82         {
  83                 $iCacheMemory = getCacheMemoryMB();
  84                 echo "WARNING: resetting cache memory to $iCacheMemory\n";
  85         }
  86
  87         $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
  88         if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
  89
  90         if ($aCMDResult['create-db'] || $aCMDResult['all'])
  91         {
  92                 echo "Create DB\n";
  93                 $bDidSomething = true;
  94                 $oDB =& DB::connect(CONST_Database_DSN, false);
  95                 if (!PEAR::isError($oDB))
  96                 {
  97                         fail('database already exists ('.CONST_Database_DSN.')');
  98                 }
  99                 passthruCheckReturn('createdb -E UTF-8 -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
 100         }
 101
 102         if ($aCMDResult['setup-db'] || $aCMDResult['all'])
 103         {
 104                 echo "Setup DB\n";
 105                 $bDidSomething = true;
 106                 // TODO: path detection, detection memory, etc.
 107
 108                 $oDB =& getDB();
 109
 110                 $sVersionString = $oDB->getOne('select version()');
 111                 preg_match('#PostgreSQL ([0-9]+)[.]([0-9]+)[.]([0-9]+) #', $sVersionString, $aMatches);
 112                 if (CONST_Postgresql_Version != $aMatches[1].'.'.$aMatches[2])
 113                 {
 114                         echo "ERROR: PostgreSQL version is not correct.  Expected ".CONST_Postgresql_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n";
 115                         exit;
 116                 }
 117
 118                 passthru('createlang plpgsql -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
 119                 $pgver = (float) CONST_Postgresql_Version;
 120                 if ($pgver < 9.1) {
 121                         pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/hstore.sql');
 122                         pgsqlRunScriptFile(CONST_BasePath.'/sql/hstore_compatability_9_0.sql');
 123                 } else {
 124                         pgsqlRunScript('CREATE EXTENSION hstore');
 125                 }
 126
 127                 pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
 128                 $sVersionString = $oDB->getOne('select postgis_full_version()');
 129                 preg_match('#POSTGIS="([0-9]+)[.]([0-9]+)[.]([0-9]+)( r([0-9]+))?"#', $sVersionString, $aMatches);
 130                 if (CONST_Postgis_Version != $aMatches[1].'.'.$aMatches[2])
 131                 {
 132                         echo "ERROR: PostGIS version is not correct.  Expected ".CONST_Postgis_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n";
 133                         exit;
 134                 }
 135
 136                 pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
 137                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
 138                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql');
 139                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql');
 140                 pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode.sql');
 141                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_statecounty.sql');
 142                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_state.sql');
 143                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
 144                 pgsqlRunScriptFile(CONST_BasePath.'/data/worldboundaries.sql');
 145         }
 146
 147         if ($aCMDResult['import-data'] || $aCMDResult['all'])
 148         {
 149                 echo "Import\n";
 150                 $bDidSomething = true;
 151
 152                 $osm2pgsql = CONST_Osm2pgsql_Binary;
 153                 if (!file_exists($osm2pgsql))
 154                 {
 155                         echo "Please download and build osm2pgsql.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 156                         fail("osm2pgsql not found in '$osm2pgsql'");
 157                 }
 158                 $osm2pgsql .= ' -lsc -O gazetteer --hstore';
 159                 $osm2pgsql .= ' -C '.$iCacheMemory;
 160                 $osm2pgsql .= ' -P '.$aDSNInfo['port'];
 161                 $osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
 162                 passthruCheckReturn($osm2pgsql);
 163
 164                 $oDB =& getDB();
 165                 $x = $oDB->getRow('select * from place limit 1');
 166                 if (PEAR::isError($x)) {
 167                         fail($x->getMessage());
 168                 }
 169                 if (!$x) fail('No Data');
 170         }
 171
 172         if ($aCMDResult['create-functions'] || $aCMDResult['all'])
 173         {
 174                 echo "Functions\n";
 175                 $bDidSomething = true;
 176                 if (!file_exists(CONST_BasePath.'/module/nominatim.so')) fail("nominatim module not built");
 177                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 178                 $sTemplate = str_replace('{modulepath}', CONST_BasePath.'/module', $sTemplate);
 179                 if ($aCMDResult['enable-diff-updates']) $sTemplate = str_replace('RETURN NEW; -- @DIFFUPDATES@', '--', $sTemplate);
 180                 if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
 181                 pgsqlRunScript($sTemplate);
 182         }
 183
 184         if ($aCMDResult['create-minimal-tables'])
 185         {
 186                 echo "Minimal Tables\n";
 187                 $bDidSomething = true;
 188                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables-minimal.sql');
 189
 190                 $sScript = '';
 191
 192                 // Backstop the import process - easliest possible import id
 193                 $sScript .= "insert into import_npi_log values (18022);\n";
 194
 195                 $hFile = @fopen(CONST_BasePath.'/settings/partitionedtags.def', "r");
 196                 if (!$hFile) fail('unable to open list of partitions: '.CONST_BasePath.'/settings/partitionedtags.def');
 197
 198                 while (($sLine = fgets($hFile, 4096)) !== false && $sLine && substr($sLine,0,1) !='#')
 199                 {
 200                         list($sClass, $sType) = explode(' ', trim($sLine));
 201                         $sScript .= "create table place_classtype_".$sClass."_".$sType." as ";
 202                         $sScript .= "select place_id as place_id,geometry as centroid from placex limit 0;\n";
 203
 204                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_centroid ";
 205                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING GIST (centroid);\n";
 206
 207                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_place_id ";
 208                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING btree(place_id);\n";
 209                 }
 210                 fclose($hFile);
 211                 pgsqlRunScript($sScript);
 212         }
 213
 214         if ($aCMDResult['create-tables'] || $aCMDResult['all'])
 215         {
 216                 echo "Tables\n";
 217                 $bDidSomething = true;
 218                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables.sql');
 219
 220                 // re-run the functions
 221                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 222                 $sTemplate = str_replace('{modulepath}',CONST_BasePath.'/module', $sTemplate);
 223                 pgsqlRunScript($sTemplate);
 224         }
 225
 226         if ($aCMDResult['create-partition-tables'] || $aCMDResult['all'])
 227         {
 228                 echo "Partition Tables\n";
 229                 $bDidSomething = true;
 230                 $oDB =& getDB();
 231                 $sSQL = 'select partition from country_name order by country_code';
 232                 $aPartitions = $oDB->getCol($sSQL);
 233                 if (PEAR::isError($aPartitions))
 234                 {
 235                         fail($aPartitions->getMessage());
 236                 }
 237                 $aPartitions[] = 0;
 238
 239                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
 240                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 241                 foreach($aMatches as $aMatch)
 242                 {
 243                         $sResult = '';
 244                         foreach($aPartitions as $sPartitionName)
 245                         {
 246                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 247                         }
 248                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 249                 }
 250
 251                 pgsqlRunScript($sTemplate);
 252         }
 253
 254
 255         if ($aCMDResult['create-partition-functions'] || $aCMDResult['all'])
 256         {
 257                 echo "Partition Functions\n";
 258                 $bDidSomething = true;
 259                 $oDB =& getDB();
 260                 $sSQL = 'select partition from country_name order by country_code';
 261                 $aPartitions = $oDB->getCol($sSQL);
 262                 if (PEAR::isError($aPartitions))
 263                 {
 264                         fail($aPartitions->getMessage());
 265                 }
 266                 $aPartitions[] = 0;
 267
 268                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
 269                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 270                 foreach($aMatches as $aMatch)
 271                 {
 272                         $sResult = '';
 273                         foreach($aPartitions as $sPartitionName)
 274                         {
 275                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 276                         }
 277                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 278                 }
 279
 280                 pgsqlRunScript($sTemplate);
 281         }
 282
 283         if ($aCMDResult['import-wikipedia-articles'] || $aCMDResult['all'])
 284         {
 285                 $bDidSomething = true;
 286                 $sWikiArticlesFile = CONST_BasePath.'/data/wikipedia_article.sql.bin';
 287                 $sWikiRedirectsFile = CONST_BasePath.'/data/wikipedia_redirect.sql.bin';
 288                 if (file_exists($sWikiArticlesFile))
 289                 {
 290                         echo "Importing wikipedia articles...";
 291                         pgsqlRunDropAndRestore($sWikiArticlesFile);
 292                         echo "...done\n";
 293                 }
 294                 else
 295                 {
 296                         echo "WARNING: wikipedia article dump file not found - places will have default importance\n";
 297                 }
 298                 if (file_exists($sWikiRedirectsFile))
 299                 {
 300                         echo "Importing wikipedia redirects...";
 301                         pgsqlRunDropAndRestore($sWikiRedirectsFile);
 302                         echo "...done\n";
 303                 }
 304                 else
 305                 {
 306                         echo "WARNING: wikipedia redirect dump file not found - some place importance values may be missing\n";
 307                 }
 308         }
 309
 310
 311         if ($aCMDResult['load-data'] || $aCMDResult['all'])
 312         {
 313                 echo "Drop old Data\n";
 314                 $bDidSomething = true;
 315
 316                 $oDB =& getDB();
 317                 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
 318                 echo '.';
 319                 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
 320                 echo '.';
 321                 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
 322                 echo '.';
 323                 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
 324                 echo '.';
 325                 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
 326                 echo '.';
 327                 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
 328                 echo '.';
 329                 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
 330                 echo '.';
 331                 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
 332                 echo '.';
 333                 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
 334                 echo '.';
 335
 336                 $sSQL = 'select partition from country_name order by country_code';
 337                 $aPartitions = $oDB->getCol($sSQL);
 338                 if (PEAR::isError($aPartitions))
 339                 {
 340                         fail($aPartitions->getMessage());
 341                 }
 342                 $aPartitions[] = 0;
 343                 foreach($aPartitions as $sPartition)
 344                 {
 345                         if (!pg_query($oDB->connection, 'TRUNCATE location_road_'.$sPartition)) fail(pg_last_error($oDB->connection));
 346                         echo '.';
 347                 }
 348
 349                 // used by getorcreate_word_id to ignore frequent partial words
 350                 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));
 351                 echo ".\n";
 352
 353                 // pre-create the word list
 354                 if (!$aCMDResult['disable-token-precalc'])
 355                 {
 356                         echo "Loading word list\n";
 357                         pgsqlRunScriptFile(CONST_BasePath.'/data/words.sql');
 358                 }
 359
 360                 echo "Load Data\n";
 361                 $aDBInstances = array();
 362                 for($i = 0; $i < $iInstances; $i++)
 363                 {
 364                         $aDBInstances[$i] =& getDB(true);
 365                         $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
 366                         $sSQL .= 'housenumber, street, isin, postcode, country_code, extratags, ';
 367                         $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
 368                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 369                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 370                 }
 371                 $bAnyBusy = true;
 372                 while($bAnyBusy)
 373                 {
 374                         $bAnyBusy = false;
 375                         for($i = 0; $i < $iInstances; $i++)
 376                         {
 377                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 378                         }
 379                         sleep(1);
 380                         echo '.';
 381                 }
 382                 echo "\n";
 383                 echo "Reanalysing database...\n";
 384                 pgsqlRunScript('ANALYSE');
 385         }
 386
 387         if ($aCMDResult['create-roads'])
 388         {
 389                 $bDidSomething = true;
 390
 391                 $oDB =& getDB();
 392                 $aDBInstances = array();
 393                 for($i = 0; $i < $iInstances; $i++)
 394                 {
 395                         $aDBInstances[$i] =& getDB(true);
 396                         if (!pg_query($aDBInstances[$i]->connection, 'set enable_bitmapscan = off')) fail(pg_last_error($oDB->connection));
 397                         $sSQL = 'select count(*) from (select insertLocationRoad(partition, place_id, calculated_country_code, geometry) from ';
 398                         $sSQL .= 'placex where osm_id % '.$iInstances.' = '.$i.' and rank_search between 26 and 27 and class = \'highway\') as x ';
 399                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 400                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 401                 }
 402                 $bAnyBusy = true;
 403                 while($bAnyBusy)
 404                 {
 405                         $bAnyBusy = false;
 406                         for($i = 0; $i < $iInstances; $i++)
 407                         {
 408                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 409                         }
 410                         sleep(1);
 411                         echo '.';
 412                 }
 413                 echo "\n";
 414         }
 415
 416         if ($aCMDResult['import-tiger-data'])
 417         {
 418                 $bDidSomething = true;
 419
 420                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_start.sql');
 421
 422                 $aDBInstances = array();
 423                 for($i = 0; $i < $iInstances; $i++)
 424                 {
 425                         $aDBInstances[$i] =& getDB(true);
 426                 }
 427
 428                 foreach(glob(CONST_BasePath.'/data/tiger2011/*.sql') as $sFile)
 429                 {
 430                         echo $sFile.': ';
 431                         $hFile = fopen($sFile, "r");
 432                         $sSQL = fgets($hFile, 100000);
 433                         $iLines = 0;
 434
 435                         while(true)
 436                         {
 437                                 for($i = 0; $i < $iInstances; $i++)
 438                                 {
 439                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
 440                                         {
 441                                                 while(pg_get_result($aDBInstances[$i]->connection));
 442                                                 $sSQL = fgets($hFile, 100000);
 443                                                 if (!$sSQL) break 2;
 444                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 445                                                 $iLines++;
 446                                                 if ($iLines == 1000)
 447                                                 {
 448                                                         echo ".";
 449                                                         $iLines = 0;
 450                                                 }
 451                                         }
 452                                 }
 453                                 usleep(10);
 454                         }
 455
 456                         fclose($hFile);
 457
 458                         $bAnyBusy = true;
 459                         while($bAnyBusy)
 460                         {
 461                                 $bAnyBusy = false;
 462                                 for($i = 0; $i < $iInstances; $i++)
 463                                 {
 464                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 465                                 }
 466                                 usleep(10);
 467                         }
 468                         echo "\n";
 469                 }
 470
 471                 echo "Creating indexes\n";
 472                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_finish.sql');
 473         }
 474
 475         if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
 476         {
 477                 $bDidSomething = true;
 478                 $oDB =& getDB();
 479                 if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
 480                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 481                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
 482                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
 483                 $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
 484                 $sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x";
 485                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 486
 487                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 488                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
 489                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
 490                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 491         }
 492
 493         if ($aCMDResult['osmosis-init'] || $aCMDResult['all'])
 494         {
 495                 $bDidSomething = true;
 496                 $oDB =& getDB();
 497
 498                 if (!file_exists(CONST_Osmosis_Binary))
 499                 {
 500                         echo "Please download osmosis.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 501                         fail("osmosis not found in '".CONST_Osmosis_Binary."'");
 502                 }
 503                 if (file_exists(CONST_BasePath.'/settings/configuration.txt'))
 504                 {
 505                         echo "settings/configuration.txt already exists\n";
 506                 }
 507                 else
 508                 {
 509                         passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_BasePath.'/settings');
 510                         // update osmosis configuration.txt with our settings
 511                         passthru("sed -i 's!baseUrl=.*!baseUrl=".CONST_Replication_Url."!' ".CONST_BasePath.'/settings/configuration.txt');
 512                         passthru("sed -i 's:maxInterval = .*:maxInterval = ".CONST_Replication_MaxInterval.":' ".CONST_BasePath.'/settings/configuration.txt');
 513                 }
 514
 515                 // Find the last node in the DB
 516                 $iLastOSMID = $oDB->getOne("select max(osm_id) as osm_id from place where osm_type = 'N'");
 517
 518                 // Lookup the timestamp that node was created (less 3 hours for margin for changsets to be closed)
 519                 $sLastNodeURL = 'http://www.openstreetmap.org/api/0.6/node/'.$iLastOSMID;
 520                 $sLastNodeXML = file_get_contents($sLastNodeURL);
 521                 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);
 522                 $iLastNodeTimestamp = strtotime($aLastNodeDate[1]) - (3*60*60);
 523
 524
 525                 // Search for the correct state file - uses file timestamps so need to sort by date descending
 526                 $sRepURL = CONST_Replication_Url."/";
 527                 $sRep = file_get_contents($sRepURL."?C=M;O=D");
 528                 // download.geofabrik.de:    <a href="000/">000/</a></td><td align="right">26-Feb-2013 11:53  </td>
 529                 // planet.openstreetmap.org: <a href="273/">273/</a>                    22-Mar-2013 07:41    -
 530                 preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>.*(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
 531                 $aPrevRepMatch = false;
 532                 foreach($aRepMatches as $aRepMatch)
 533                 {
 534                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 535                         $aPrevRepMatch = $aRepMatch;
 536                 }
 537                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 538
 539                 $sRepURL .= $aRepMatch[1];
 540                 $sRep = file_get_contents($sRepURL."?C=M;O=D");
 541                 preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>.*(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
 542                 $aPrevRepMatch = false;
 543                 foreach($aRepMatches as $aRepMatch)
 544                 {
 545                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 546                         $aPrevRepMatch = $aRepMatch;
 547                 }
 548                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 549
 550                 $sRepURL .= $aRepMatch[1];
 551                 $sRep = file_get_contents($sRepURL."?C=M;O=D");
 552                 preg_match_all('#<a href="[0-9]{3}.state.txt">([0-9]{3}).state.txt</a>.*(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
 553                 $aPrevRepMatch = false;
 554                 foreach($aRepMatches as $aRepMatch)
 555                 {
 556                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 557                         $aPrevRepMatch = $aRepMatch;
 558                 }
 559                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 560
 561                 $sRepURL .= $aRepMatch[1].'.state.txt';
 562                 echo "Getting state file: $sRepURL\n";
 563                 $sStateFile = file_get_contents($sRepURL);
 564                 if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
 565                 file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
 566                 echo "Updating DB status\n";
 567                 pg_query($oDB->connection, 'TRUNCATE import_status');
 568                 $sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
 569                 pg_query($oDB->connection, $sSQL);
 570         }
 571
 572         if ($aCMDResult['index'] || $aCMDResult['all'])
 573         {
 574                 $bDidSomething = true;
 575                 $sOutputFile = '';
 576                 if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output'];
 577                 $sBaseCmd = CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
 578                 passthruCheckReturn($sBaseCmd.' -R 4');
 579                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 580                 passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
 581                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 582                 passthruCheckReturn($sBaseCmd.' -r 26');
 583         }
 584
 585         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
 586         {
 587                 echo "Search indices\n";
 588                 $bDidSomething = true;
 589                 $oDB =& getDB();
 590                 $sSQL = 'select partition from country_name order by country_code';
 591                 $aPartitions = $oDB->getCol($sSQL);
 592                 if (PEAR::isError($aPartitions))
 593                 {
 594                         fail($aPartitions->getMessage());
 595                 }
 596                 $aPartitions[] = 0;
 597
 598                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
 599                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 600                 foreach($aMatches as $aMatch)
 601                 {
 602                         $sResult = '';
 603                         foreach($aPartitions as $sPartitionName)
 604                         {
 605                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 606                         }
 607                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 608                 }
 609
 610                 pgsqlRunScript($sTemplate);
 611         }
 612
 613         if (isset($aCMDResult['create-website']))
 614         {
 615                 $bDidSomething = true;
 616                 $sTargetDir = $aCMDResult['create-website'];
 617                 if (!is_dir($sTargetDir))
 618                 {
 619                         echo "You must create the website directory before calling this function.\n";
 620                         fail("Target directory does not exist.");
 621                 }
 622
 623                 @symlink(CONST_BasePath.'/website/details.php', $sTargetDir.'/details.php');
 624                 @symlink(CONST_BasePath.'/website/reverse.php', $sTargetDir.'/reverse.php');
 625                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/search.php');
 626                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/index.php');
 627                 @symlink(CONST_BasePath.'/website/deletable.php', $sTargetDir.'/deletable.php');
 628                 @symlink(CONST_BasePath.'/website/polygons.php', $sTargetDir.'/polygons.php');
 629                 @symlink(CONST_BasePath.'/website/status.php', $sTargetDir.'/status.php');
 630                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
 631                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
 632                 @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
 633                 echo "Symlinks created\n";
 634         }
 635
 636         if (!$bDidSomething)
 637         {
 638                 showUsage($aCMDOptions, true);
 639         }
 640
 641         function pgsqlRunScriptFile($sFilename)
 642         {
 643                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
 644
 645                 // Convert database DSN to psql parameters
 646                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 647                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 648                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -f '.$sFilename;
 649
 650                 $aDescriptors = array(
 651                         0 => array('pipe', 'r'),
 652                         1 => array('pipe', 'w'),
 653                         2 => array('file', '/dev/null', 'a')
 654                 );
 655                 $ahPipes = null;
 656                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 657                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 658
 659                 fclose($ahPipes[0]);
 660
 661                 // TODO: error checking
 662                 while(!feof($ahPipes[1]))
 663                 {
 664                         echo fread($ahPipes[1], 4096);
 665                 }
 666                 fclose($ahPipes[1]);
 667
 668                 proc_close($hProcess);
 669         }
 670
 671         function pgsqlRunScript($sScript)
 672         {
 673                 // Convert database DSN to psql parameters
 674                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 675                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 676                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 677                 $aDescriptors = array(
 678                         0 => array('pipe', 'r'),
 679                         1 => STDOUT,
 680                         2 => STDERR
 681                 );
 682                 $ahPipes = null;
 683                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
 684                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 685
 686                 while(strlen($sScript))
 687                 {
 688                         $written = fwrite($ahPipes[0], $sScript);
 689                         $sScript = substr($sScript, $written);
 690                 }
 691                 fclose($ahPipes[0]);
 692                 proc_close($hProcess);
 693         }
 694
 695         function pgsqlRunRestoreData($sDumpFile)
 696         {
 697                 // Convert database DSN to psql parameters
 698                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 699                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 700                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
 701
 702                 $aDescriptors = array(
 703                         0 => array('pipe', 'r'),
 704                         1 => array('pipe', 'w'),
 705                         2 => array('file', '/dev/null', 'a')
 706                 );
 707                 $ahPipes = null;
 708                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 709                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 710
 711                 fclose($ahPipes[0]);
 712
 713                 // TODO: error checking
 714                 while(!feof($ahPipes[1]))
 715                 {
 716                         echo fread($ahPipes[1], 4096);
 717                 }
 718                 fclose($ahPipes[1]);
 719
 720                 proc_close($hProcess);
 721         }
 722
 723         function pgsqlRunDropAndRestore($sDumpFile)
 724         {
 725                 // Convert database DSN to psql parameters
 726                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 727                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 728                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
 729
 730                 $aDescriptors = array(
 731                         0 => array('pipe', 'r'),
 732                         1 => array('pipe', 'w'),
 733                         2 => array('file', '/dev/null', 'a')
 734                 );
 735                 $ahPipes = null;
 736                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 737                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 738
 739                 fclose($ahPipes[0]);
 740
 741                 // TODO: error checking
 742                 while(!feof($ahPipes[1]))
 743                 {
 744                         echo fread($ahPipes[1], 4096);
 745                 }
 746                 fclose($ahPipes[1]);
 747
 748                 proc_close($hProcess);
 749         }
 750
 751         function passthruCheckReturn($cmd)
 752         {
 753                 $result = -1;
 754                 passthru($cmd, $result);
 755                 if ($result != 0) fail('Error executing external command: '.$cmd);
 756         }