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