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