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)) fail("please download osmosis");
 499                 if (file_exists(CONST_BasePath.'/settings/configuration.txt'))
 500                 {
 501                         echo "settings/configuration.txt already exists\n";
 502                 }
 503                 else
 504                 {
 505                         passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_BasePath.'/settings');
 506                         // server layout changed afer license change, fix path to minutely diffs
 507                         passthru("sed -i 's:minute-replicate:replication/minute:' ".CONST_BasePath.'/settings/configuration.txt');
 508                 }
 509
 510                 // Find the last node in the DB
 511                 $iLastOSMID = $oDB->getOne("select max(osm_id) as osm_id from place where osm_type = 'N'");
 512
 513                 // Lookup the timestamp that node was created (less 3 hours for margin for changsets to be closed)
 514                 $sLastNodeURL = 'http://www.openstreetmap.org/api/0.6/node/'.$iLastOSMID;
 515                 $sLastNodeXML = file_get_contents($sLastNodeURL);
 516                 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);
 517                 $iLastNodeTimestamp = strtotime($aLastNodeDate[1]) - (3*60*60);
 518
 519
 520                 // Search for the correct state file - uses file timestamps
 521                 $sRepURL = 'http://planet.openstreetmap.org/replication/minute/';
 522                 $sRep = file_get_contents($sRepURL);
 523                 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);
 524                 $aPrevRepMatch = false;
 525                 foreach($aRepMatches as $aRepMatch)
 526                 {
 527                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 528                         $aPrevRepMatch = $aRepMatch;
 529                 }
 530                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 531
 532                 $sRepURL .= $aRepMatch[1];
 533                 $sRep = file_get_contents($sRepURL);
 534                 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);
 535                 $aPrevRepMatch = false;
 536                 foreach($aRepMatches as $aRepMatch)
 537                 {
 538                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 539                         $aPrevRepMatch = $aRepMatch;
 540                 }
 541                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 542
 543                 $sRepURL .= $aRepMatch[1];
 544                 $sRep = file_get_contents($sRepURL);
 545                 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);
 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].'.state.txt';
 555                 echo "Getting state file: $sRepURL\n";
 556                 $sStateFile = file_get_contents($sRepURL);
 557                 if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
 558                 file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
 559                 echo "Updating DB status\n";
 560                 pg_query($oDB->connection, 'TRUNCATE import_status');
 561                 $sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
 562                 pg_query($oDB->connection, $sSQL);
 563         }
 564
 565         if ($aCMDResult['index'] || $aCMDResult['all'])
 566         {
 567                 $bDidSomething = true;
 568                 $sOutputFile = '';
 569                 if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output'];
 570                 $sBaseCmd = CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
 571                 passthruCheckReturn($sBaseCmd.' -R 4');
 572                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 573                 passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
 574                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 575                 passthruCheckReturn($sBaseCmd.' -r 26');
 576         }
 577
 578         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
 579         {
 580                 echo "Search indices\n";
 581                 $bDidSomething = true;
 582                 $oDB =& getDB();
 583                 $sSQL = 'select partition from country_name order by country_code';
 584                 $aPartitions = $oDB->getCol($sSQL);
 585                 if (PEAR::isError($aPartitions))
 586                 {
 587                         fail($aPartitions->getMessage());
 588                 }
 589                 $aPartitions[] = 0;
 590
 591                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
 592                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 593                 foreach($aMatches as $aMatch)
 594                 {
 595                         $sResult = '';
 596                         foreach($aPartitions as $sPartitionName)
 597                         {
 598                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 599                         }
 600                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 601                 }
 602
 603                 pgsqlRunScript($sTemplate);
 604         }
 605
 606         if (isset($aCMDResult['create-website']))
 607         {
 608                 $bDidSomething = true;
 609                 $sTargetDir = $aCMDResult['create-website'];
 610                 if (!is_dir($sTargetDir))
 611                 {
 612                         echo "You must create the website directory before calling this function.\n";
 613                         fail("Target directory does not exist.");
 614                 }
 615
 616                 @symlink(CONST_BasePath.'/website/details.php', $sTargetDir.'/details.php');
 617                 @symlink(CONST_BasePath.'/website/reverse.php', $sTargetDir.'/reverse.php');
 618                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/search.php');
 619                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/index.php');
 620                 @symlink(CONST_BasePath.'/website/deletable.php', $sTargetDir.'/deletable.php');
 621                 @symlink(CONST_BasePath.'/website/polygons.php', $sTargetDir.'/polygons.php');
 622                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
 623                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
 624                 @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
 625                 echo "Symlinks created\n";
 626         }
 627
 628         if (!$bDidSomething)
 629         {
 630                 showUsage($aCMDOptions, true);
 631         }
 632
 633         function pgsqlRunScriptFile($sFilename)
 634         {
 635                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
 636
 637                 // Convert database DSN to psql parameters
 638                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 639                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 640                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -f '.$sFilename;
 641
 642                 $aDescriptors = array(
 643                         0 => array('pipe', 'r'),
 644                         1 => array('pipe', 'w'),
 645                         2 => array('file', '/dev/null', 'a')
 646                 );
 647                 $ahPipes = null;
 648                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 649                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 650
 651                 fclose($ahPipes[0]);
 652
 653                 // TODO: error checking
 654                 while(!feof($ahPipes[1]))
 655                 {
 656                         echo fread($ahPipes[1], 4096);
 657                 }
 658                 fclose($ahPipes[1]);
 659
 660                 proc_close($hProcess);
 661         }
 662
 663         function pgsqlRunScript($sScript)
 664         {
 665                 // Convert database DSN to psql parameters
 666                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 667                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 668                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 669                 $aDescriptors = array(
 670                         0 => array('pipe', 'r'),
 671                         1 => STDOUT,
 672                         2 => STDERR
 673                 );
 674                 $ahPipes = null;
 675                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
 676                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 677
 678                 while(strlen($sScript))
 679                 {
 680                         $written = fwrite($ahPipes[0], $sScript);
 681                         $sScript = substr($sScript, $written);
 682                 }
 683                 fclose($ahPipes[0]);
 684                 proc_close($hProcess);
 685         }
 686
 687         function pgsqlRunRestoreData($sDumpFile)
 688         {
 689                 // Convert database DSN to psql parameters
 690                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 691                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 692                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
 693
 694                 $aDescriptors = array(
 695                         0 => array('pipe', 'r'),
 696                         1 => array('pipe', 'w'),
 697                         2 => array('file', '/dev/null', 'a')
 698                 );
 699                 $ahPipes = null;
 700                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 701                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 702
 703                 fclose($ahPipes[0]);
 704
 705                 // TODO: error checking
 706                 while(!feof($ahPipes[1]))
 707                 {
 708                         echo fread($ahPipes[1], 4096);
 709                 }
 710                 fclose($ahPipes[1]);
 711
 712                 proc_close($hProcess);
 713         }
 714
 715         function pgsqlRunDropAndRestore($sDumpFile)
 716         {
 717                 // Convert database DSN to psql parameters
 718                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 719                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 720                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
 721
 722                 $aDescriptors = array(
 723                         0 => array('pipe', 'r'),
 724                         1 => array('pipe', 'w'),
 725                         2 => array('file', '/dev/null', 'a')
 726                 );
 727                 $ahPipes = null;
 728                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 729                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 730
 731                 fclose($ahPipes[0]);
 732
 733                 // TODO: error checking
 734                 while(!feof($ahPipes[1]))
 735                 {
 736                         echo fread($ahPipes[1], 4096);
 737                 }
 738                 fclose($ahPipes[1]);
 739
 740                 proc_close($hProcess);
 741         }
 742
 743         function passthruCheckReturn($cmd)
 744         {
 745                 $result = -1;
 746                 passthru($cmd, $result);
 747                 if ($result != 0) fail('Error executing external command: '.$cmd);
 748         }