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