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