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