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