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