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