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