utils/setup.php

   1 #!/usr/bin/php -Cq
   2 <?php
   3
   4         require_once(dirname(dirname(__FILE__)).'/settings/settings.php');
   5         require_once(CONST_BasePath.'/lib/init-cmd.php');
   6         ini_set('memory_limit', '800M');
   7
   8         $aCMDOptions = array(
   9                 "Create and setup nominatim search system",
  10                 array('help', 'h', 0, 1, 0, 0, false, 'Show Help'),
  11                 array('quiet', 'q', 0, 1, 0, 0, 'bool', 'Quiet output'),
  12                 array('verbose', 'v', 0, 1, 0, 0, 'bool', 'Verbose output'),
  13
  14                 array('osm-file', '', 0, 1, 1, 1, 'realpath', 'File to import'),
  15                 array('threads', '', 0, 1, 1, 1, 'int', 'Number of threads (where possible)'),
  16
  17                 array('all', '', 0, 1, 0, 0, 'bool', 'Do the complete process'),
  18
  19                 array('create-db', '', 0, 1, 0, 0, 'bool', 'Create nominatim db'),
  20                 array('setup-db', '', 0, 1, 0, 0, 'bool', 'Build a blank nominatim db'),
  21                 array('import-data', '', 0, 1, 0, 0, 'bool', 'Import a osm file'),
  22                 array('osm2pgsql-cache', '', 0, 1, 1, 1, 'int', 'Cache size used by osm2pgsql'),
  23                 array('create-functions', '', 0, 1, 0, 0, 'bool', 'Create functions'),
  24                 array('enable-diff-updates', '', 0, 1, 0, 0, 'bool', 'Turn on the code required to make diff updates work'),
  25                 array('enable-debug-statements', '', 0, 1, 0, 0, 'bool', 'Include debug warning statements in pgsql commands'),
  26                 array('ignore-errors', '', 0, 1, 0, 0, 'bool', 'Continue import even when errors in SQL are present (EXPERT)'),
  27                 array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
  28                 array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'),
  29                 array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'),
  30                 array('no-partitions', '', 0, 1, 0, 0, 'bool', "Do not partition search indices (speeds up import of single country extracts)"),
  31                 array('import-wikipedia-articles', '', 0, 1, 0, 0, 'bool', 'Import wikipedia article dump'),
  32                 array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'),
  33                 array('disable-token-precalc', '', 0, 1, 0, 0, 'bool', 'Disable name precalculation (EXPERT)'),
  34                 array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'),
  35                 array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
  36                 array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'),
  37                 array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'),
  38                 array('index-noanalyse', '', 0, 1, 0, 0, 'bool', 'Do not perform analyse operations during index (EXPERT)'),
  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                 array('drop', '', 0, 1, 0, 0, 'bool', 'Drop tables needed for updates, making the database readonly (EXPERIMENTAL)'),
  42         );
  43         getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true);
  44
  45         $bDidSomething = false;
  46
  47         // Check if osm-file is set and points to a valid file if --all or --import-data is given
  48         if ($aCMDResult['import-data'] || $aCMDResult['all'])
  49         {
  50                 if (!isset($aCMDResult['osm-file']))
  51                 {
  52                         fail('missing --osm-file for data import');
  53                 }
  54
  55                 if (!file_exists($aCMDResult['osm-file']))
  56                 {
  57                         fail('the path supplied to --osm-file does not exist');
  58                 }
  59
  60                 if (!is_readable($aCMDResult['osm-file']))
  61                 {
  62                         fail('osm-file "'.$aCMDResult['osm-file'].'" not readable');
  63                 }
  64         }
  65
  66
  67         // This is a pretty hard core default - the number of processors in the box - 1
  68         $iInstances = isset($aCMDResult['threads'])?$aCMDResult['threads']:(getProcessorCount()-1);
  69         if ($iInstances < 1)
  70         {
  71                 $iInstances = 1;
  72                 echo "WARNING: resetting threads to $iInstances\n";
  73         }
  74         if ($iInstances > getProcessorCount())
  75         {
  76                 $iInstances = getProcessorCount();
  77                 echo "WARNING: resetting threads to $iInstances\n";
  78         }
  79
  80         // Assume we can steal all the cache memory in the box (unless told otherwise)
  81         if (isset($aCMDResult['osm2pgsql-cache']))
  82         {
  83                 $iCacheMemory = $aCMDResult['osm2pgsql-cache'];
  84         }
  85         else
  86         {
  87                 $iCacheMemory = getCacheMemoryMB();
  88         }
  89
  90         $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
  91         if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
  92
  93         if ($aCMDResult['create-db'] || $aCMDResult['all'])
  94         {
  95                 echo "Create DB\n";
  96                 $bDidSomething = true;
  97                 $oDB =& DB::connect(CONST_Database_DSN, false);
  98                 if (!PEAR::isError($oDB))
  99                 {
 100                         fail('database already exists ('.CONST_Database_DSN.')');
 101                 }
 102                 passthruCheckReturn('createdb -E UTF-8 -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
 103         }
 104
 105         if ($aCMDResult['setup-db'] || $aCMDResult['all'])
 106         {
 107                 echo "Setup DB\n";
 108                 $bDidSomething = true;
 109                 // TODO: path detection, detection memory, etc.
 110
 111                 $oDB =& getDB();
 112
 113                 $fPostgresVersion = getPostgresVersion($oDB);
 114                 echo 'Postgres version found: '.$fPostgresVersion."\n";
 115
 116                 if ($fPostgresVersion < 9.1)
 117                 {
 118                         fail("Minimum supported version of Postgresql is 9.1.");
 119                 }
 120
 121                 pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS hstore');
 122                 pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS postgis');
 123
 124                 // For extratags and namedetails the hstore_to_json converter is
 125                 // needed which is only available from Postgresql 9.3+. For older
 126                 // versions add a dummy function that returns nothing.
 127                 $iNumFunc = $oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'");
 128                 if (PEAR::isError($iNumFunc))
 129                 {
 130                         fail("Cannot query stored procedures.", $iNumFunc);
 131                 }
 132                 if ($iNumFunc == 0)
 133                 {
 134                         pgsqlRunScript("create function hstore_to_json(dummy hstore) returns text AS 'select null::text' language sql immutable");
 135                         echo "WARNING: Postgresql is too old. extratags and namedetails API not available.";
 136                 }
 137
 138                 $fPostgisVersion = getPostgisVersion($oDB);
 139                 echo 'Postgis version found: '.$fPostgisVersion."\n";
 140
 141                 if ($fPostgisVersion < 2.1)
 142                 {
 143                         // Function was renamed in 2.1 and throws an annoying deprecation warning
 144                         pgsqlRunScript('ALTER FUNCTION st_line_interpolate_point(geometry, double precision) RENAME TO ST_LineInterpolatePoint');
 145                 }
 146
 147                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
 148                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql');
 149                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql');
 150                 pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode_table.sql');
 151                 if (file_exists(CONST_BasePath.'/data/gb_postcode_data.sql.gz'))
 152                 {
 153                         pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode_data.sql.gz');
 154                 }
 155                 else
 156                 {
 157                         echo "WARNING: external UK postcode table not found.\n";
 158                 }
 159                 if (CONST_Use_Extra_US_Postcodes)
 160                 {
 161                         pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
 162                 }
 163
 164                 if ($aCMDResult['no-partitions'])
 165                 {
 166                         pgsqlRunScript('update country_name set partition = 0');
 167                 }
 168
 169                 // the following will be needed by create_functions later but
 170                 // is only defined in the subsequently called create_tables.
 171                 // Create dummies here that will be overwritten by the proper
 172                 // versions in create-tables.
 173                 pgsqlRunScript('CREATE TABLE place_boundingbox ()');
 174                 pgsqlRunScript('create type wikipedia_article_match as ()');
 175         }
 176
 177         if ($aCMDResult['import-data'] || $aCMDResult['all'])
 178         {
 179                 echo "Import\n";
 180                 $bDidSomething = true;
 181
 182                 $osm2pgsql = CONST_Osm2pgsql_Binary;
 183                 if (!file_exists($osm2pgsql))
 184                 {
 185                         echo "Please download and build osm2pgsql.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 186                         fail("osm2pgsql not found in '$osm2pgsql'");
 187                 }
 188
 189                 if (!is_null(CONST_Osm2pgsql_Flatnode_File))
 190                 {
 191                         $osm2pgsql .= ' --flat-nodes '.CONST_Osm2pgsql_Flatnode_File;
 192                 }
 193                 if (CONST_Tablespace_Osm2pgsql_Data)
 194                         $osm2pgsql .= ' --tablespace-slim-data '.CONST_Tablespace_Osm2pgsql_Data;
 195                 if (CONST_Tablespace_Osm2pgsql_Index)
 196                         $osm2pgsql .= ' --tablespace-slim-index '.CONST_Tablespace_Osm2pgsql_Index;
 197                 if (CONST_Tablespace_Place_Data)
 198                         $osm2pgsql .= ' --tablespace-main-data '.CONST_Tablespace_Place_Data;
 199                 if (CONST_Tablespace_Place_Index)
 200                         $osm2pgsql .= ' --tablespace-main-index '.CONST_Tablespace_Place_Index;
 201                 $osm2pgsql .= ' -lsc -O gazetteer --hstore --number-processes 1';
 202                 $osm2pgsql .= ' -C '.$iCacheMemory;
 203                 $osm2pgsql .= ' -P '.$aDSNInfo['port'];
 204                 $osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
 205                 passthruCheckReturn($osm2pgsql);
 206
 207                 $oDB =& getDB();
 208                 $x = $oDB->getRow('select * from place limit 1');
 209                 if (PEAR::isError($x)) {
 210                         fail($x->getMessage());
 211                 }
 212                 if (!$x) fail('No Data');
 213         }
 214
 215         if ($aCMDResult['create-functions'] || $aCMDResult['all'])
 216         {
 217                 echo "Functions\n";
 218                 $bDidSomething = true;
 219                 if (!file_exists(CONST_InstallPath.'/module/nominatim.so')) fail("nominatim module not built");
 220                 create_sql_functions($aCMDResult);
 221         }
 222
 223         if ($aCMDResult['create-tables'] || $aCMDResult['all'])
 224         {
 225                 $bDidSomething = true;
 226
 227                 echo "Tables\n";
 228                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tables.sql');
 229                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
 230                 $sTemplate = replace_tablespace('{ts:address-data}',
 231                                                 CONST_Tablespace_Address_Data, $sTemplate);
 232                 $sTemplate = replace_tablespace('{ts:address-index}',
 233                                                 CONST_Tablespace_Address_Index, $sTemplate);
 234                 $sTemplate = replace_tablespace('{ts:search-data}',
 235                                                 CONST_Tablespace_Search_Data, $sTemplate);
 236                 $sTemplate = replace_tablespace('{ts:search-index}',
 237                                                 CONST_Tablespace_Search_Index, $sTemplate);
 238                 $sTemplate = replace_tablespace('{ts:aux-data}',
 239                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 240                 $sTemplate = replace_tablespace('{ts:aux-index}',
 241                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 242                 pgsqlRunScript($sTemplate, false);
 243
 244                 // re-run the functions
 245                 echo "Functions\n";
 246                 create_sql_functions($aCMDResult);
 247         }
 248
 249         if ($aCMDResult['create-partition-tables'] || $aCMDResult['all'])
 250         {
 251                 echo "Partition Tables\n";
 252                 $bDidSomething = true;
 253                 $oDB =& getDB();
 254                 $sSQL = 'select distinct partition from country_name';
 255                 $aPartitions = $oDB->getCol($sSQL);
 256                 if (PEAR::isError($aPartitions))
 257                 {
 258                         fail($aPartitions->getMessage());
 259                 }
 260                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 261
 262                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
 263                 $sTemplate = replace_tablespace('{ts:address-data}',
 264                                                 CONST_Tablespace_Address_Data, $sTemplate);
 265                 $sTemplate = replace_tablespace('{ts:address-index}',
 266                                                 CONST_Tablespace_Address_Index, $sTemplate);
 267                 $sTemplate = replace_tablespace('{ts:search-data}',
 268                                                 CONST_Tablespace_Search_Data, $sTemplate);
 269                 $sTemplate = replace_tablespace('{ts:search-index}',
 270                                                 CONST_Tablespace_Search_Index, $sTemplate);
 271                 $sTemplate = replace_tablespace('{ts:aux-data}',
 272                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 273                 $sTemplate = replace_tablespace('{ts:aux-index}',
 274                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 275                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 276                 foreach($aMatches as $aMatch)
 277                 {
 278                         $sResult = '';
 279                         foreach($aPartitions as $sPartitionName)
 280                         {
 281                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 282                         }
 283                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 284                 }
 285
 286                 pgsqlRunScript($sTemplate);
 287         }
 288
 289
 290         if ($aCMDResult['create-partition-functions'] || $aCMDResult['all'])
 291         {
 292                 echo "Partition Functions\n";
 293                 $bDidSomething = true;
 294                 $oDB =& getDB();
 295                 $sSQL = 'select distinct partition from country_name';
 296                 $aPartitions = $oDB->getCol($sSQL);
 297                 if (PEAR::isError($aPartitions))
 298                 {
 299                         fail($aPartitions->getMessage());
 300                 }
 301                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 302
 303                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
 304                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 305                 foreach($aMatches as $aMatch)
 306                 {
 307                         $sResult = '';
 308                         foreach($aPartitions as $sPartitionName)
 309                         {
 310                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 311                         }
 312                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 313                 }
 314
 315                 pgsqlRunScript($sTemplate);
 316         }
 317
 318         if ($aCMDResult['import-wikipedia-articles'] || $aCMDResult['all'])
 319         {
 320                 $bDidSomething = true;
 321                 $sWikiArticlesFile = CONST_BasePath.'/data/wikipedia_article.sql.bin';
 322                 $sWikiRedirectsFile = CONST_BasePath.'/data/wikipedia_redirect.sql.bin';
 323                 if (file_exists($sWikiArticlesFile))
 324                 {
 325                         echo "Importing wikipedia articles...";
 326                         pgsqlRunDropAndRestore($sWikiArticlesFile);
 327                         echo "...done\n";
 328                 }
 329                 else
 330                 {
 331                         echo "WARNING: wikipedia article dump file not found - places will have default importance\n";
 332                 }
 333                 if (file_exists($sWikiRedirectsFile))
 334                 {
 335                         echo "Importing wikipedia redirects...";
 336                         pgsqlRunDropAndRestore($sWikiRedirectsFile);
 337                         echo "...done\n";
 338                 }
 339                 else
 340                 {
 341                         echo "WARNING: wikipedia redirect dump file not found - some place importance values may be missing\n";
 342                 }
 343         }
 344
 345
 346         if ($aCMDResult['load-data'] || $aCMDResult['all'])
 347         {
 348                 echo "Drop old Data\n";
 349                 $bDidSomething = true;
 350
 351                 $oDB =& getDB();
 352                 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
 353                 echo '.';
 354                 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
 355                 echo '.';
 356                 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
 357                 echo '.';
 358                 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
 359                 echo '.';
 360                 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
 361                 echo '.';
 362                 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
 363                 echo '.';
 364                 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
 365                 echo '.';
 366                 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
 367                 echo '.';
 368                 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
 369                 echo '.';
 370
 371                 $sSQL = 'select distinct partition from country_name';
 372                 $aPartitions = $oDB->getCol($sSQL);
 373                 if (PEAR::isError($aPartitions))
 374                 {
 375                         fail($aPartitions->getMessage());
 376                 }
 377                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 378                 foreach($aPartitions as $sPartition)
 379                 {
 380                         if (!pg_query($oDB->connection, 'TRUNCATE location_road_'.$sPartition)) fail(pg_last_error($oDB->connection));
 381                         echo '.';
 382                 }
 383
 384                 // used by getorcreate_word_id to ignore frequent partial words
 385                 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));
 386                 echo ".\n";
 387
 388                 // pre-create the word list
 389                 if (!$aCMDResult['disable-token-precalc'])
 390                 {
 391                         echo "Loading word list\n";
 392                         pgsqlRunScriptFile(CONST_BasePath.'/data/words.sql');
 393                 }
 394
 395                 echo "Load Data\n";
 396                 $aDBInstances = array();
 397                 for($i = 0; $i < $iInstances; $i++)
 398                 {
 399                         $aDBInstances[$i] =& getDB(true);
 400                         $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
 401                         $sSQL .= 'housenumber, street, addr_place, isin, postcode, country_code, extratags, ';
 402                         $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
 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                 echo "Reanalysing database...\n";
 419                 pgsqlRunScript('ANALYSE');
 420         }
 421
 422         if ($aCMDResult['import-tiger-data'])
 423         {
 424                 $bDidSomething = true;
 425
 426                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_start.sql');
 427                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
 428                 $sTemplate = replace_tablespace('{ts:aux-data}',
 429                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 430                 $sTemplate = replace_tablespace('{ts:aux-index}',
 431                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 432                 pgsqlRunScript($sTemplate, false);
 433
 434                 $aDBInstances = array();
 435                 for($i = 0; $i < $iInstances; $i++)
 436                 {
 437                         $aDBInstances[$i] =& getDB(true);
 438                 }
 439
 440                 foreach(glob(CONST_Tiger_Data_Path.'/*.sql') as $sFile)
 441                 {
 442                         echo $sFile.': ';
 443                         $hFile = fopen($sFile, "r");
 444                         $sSQL = fgets($hFile, 100000);
 445                         $iLines = 0;
 446
 447                         while(true)
 448                         {
 449                                 for($i = 0; $i < $iInstances; $i++)
 450                                 {
 451                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
 452                                         {
 453                                                 while(pg_get_result($aDBInstances[$i]->connection));
 454                                                 $sSQL = fgets($hFile, 100000);
 455                                                 if (!$sSQL) break 2;
 456                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 457                                                 $iLines++;
 458                                                 if ($iLines == 1000)
 459                                                 {
 460                                                         echo ".";
 461                                                         $iLines = 0;
 462                                                 }
 463                                         }
 464                                 }
 465                                 usleep(10);
 466                         }
 467
 468                         fclose($hFile);
 469
 470                         $bAnyBusy = true;
 471                         while($bAnyBusy)
 472                         {
 473                                 $bAnyBusy = false;
 474                                 for($i = 0; $i < $iInstances; $i++)
 475                                 {
 476                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 477                                 }
 478                                 usleep(10);
 479                         }
 480                         echo "\n";
 481                 }
 482
 483                 echo "Creating indexes\n";
 484                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_finish.sql');
 485                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
 486                 $sTemplate = replace_tablespace('{ts:aux-data}',
 487                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 488                 $sTemplate = replace_tablespace('{ts:aux-index}',
 489                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 490                 pgsqlRunScript($sTemplate, false);
 491         }
 492
 493         if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
 494         {
 495                 $bDidSomething = true;
 496                 $oDB =& getDB();
 497                 if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
 498                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 499                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
 500                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
 501                 $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
 502                 $sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x";
 503                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 504
 505                 if (CONST_Use_Extra_US_Postcodes)
 506                 {
 507                         $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 508                         $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
 509                         $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
 510                         if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 511                 }
 512         }
 513
 514         if ($aCMDResult['osmosis-init'] || ($aCMDResult['all'] && !$aCMDResult['drop'])) // no use doing osmosis-init when dropping update tables
 515         {
 516                 $bDidSomething = true;
 517                 $oDB =& getDB();
 518
 519                 if (!file_exists(CONST_Osmosis_Binary))
 520                 {
 521                         echo "Please download osmosis.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 522                         if (!$aCMDResult['all'])
 523                         {
 524                                 fail("osmosis not found in '".CONST_Osmosis_Binary."'");
 525                         }
 526                 }
 527                 else
 528                 {
 529                         if (file_exists(CONST_InstallPath.'/settings/configuration.txt'))
 530                         {
 531                                 echo "settings/configuration.txt already exists\n";
 532                         }
 533                         else
 534                         {
 535                                 passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_InstallPath.'/settings');
 536                                 // update osmosis configuration.txt with our settings
 537                                 passthru("sed -i 's!baseUrl=.*!baseUrl=".CONST_Replication_Url."!' ".CONST_InstallPath.'/settings/configuration.txt');
 538                                 passthru("sed -i 's:maxInterval = .*:maxInterval = ".CONST_Replication_MaxInterval.":' ".CONST_InstallPath.'/settings/configuration.txt');
 539                         }
 540
 541                         // Find the last node in the DB
 542                         $iLastOSMID = $oDB->getOne("select max(osm_id) from place where osm_type = 'N'");
 543
 544                         // Lookup the timestamp that node was created (less 3 hours for margin for changsets to be closed)
 545                         $sLastNodeURL = 'http://www.openstreetmap.org/api/0.6/node/'.$iLastOSMID."/1";
 546                         $sLastNodeXML = file_get_contents($sLastNodeURL);
 547                         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);
 548                         $iLastNodeTimestamp = strtotime($aLastNodeDate[1]) - (3*60*60);
 549
 550                         // Search for the correct state file - uses file timestamps so need to sort by date descending
 551                         $sRepURL = CONST_Replication_Url."/";
 552                         $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 553                         // download.geofabrik.de:    <a href="000/">000/</a></td><td align="right">26-Feb-2013 11:53  </td>
 554                         // planet.openstreetmap.org: <a href="273/">273/</a>                    2013-03-11 07:41    -
 555                         preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
 556                         if ($aRepMatches)
 557                         {
 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];
 567                                 $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 568                                 preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
 569                                 $aPrevRepMatch = false;
 570                                 foreach($aRepMatches as $aRepMatch)
 571                                 {
 572                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 573                                         $aPrevRepMatch = $aRepMatch;
 574                                 }
 575                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 576
 577                                 $sRepURL .= $aRepMatch[1];
 578                                 $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 579                                 preg_match_all('#<a href="[0-9]{3}.state.txt">([0-9]{3}).state.txt</a>\s*([-0-9a-zA-Z]+ [0-9]{2}:[0-9]{2})#', $sRep, $aRepMatches, PREG_SET_ORDER);
 580                                 $aPrevRepMatch = false;
 581                                 foreach($aRepMatches as $aRepMatch)
 582                                 {
 583                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 584                                         $aPrevRepMatch = $aRepMatch;
 585                                 }
 586                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 587
 588                                 $sRepURL .= $aRepMatch[1].'.state.txt';
 589                                 echo "Getting state file: $sRepURL\n";
 590                                 $sStateFile = file_get_contents($sRepURL);
 591                                 if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
 592                                 file_put_contents(CONST_InstallPath.'/settings/state.txt', $sStateFile);
 593                                 echo "Updating DB status\n";
 594                                 pg_query($oDB->connection, 'TRUNCATE import_status');
 595                                 $sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
 596                                 pg_query($oDB->connection, $sSQL);
 597                         }
 598                         else
 599                         {
 600                                 if (!$aCMDResult['all'])
 601                                 {
 602                                         fail("Cannot read state file directory.");
 603                                 }
 604                         }
 605                 }
 606         }
 607
 608         if ($aCMDResult['index'] || $aCMDResult['all'])
 609         {
 610                 $bDidSomething = true;
 611                 $sOutputFile = '';
 612                 $sBaseCmd = CONST_InstallPath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
 613                 passthruCheckReturn($sBaseCmd.' -R 4');
 614                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 615                 passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
 616                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 617                 passthruCheckReturn($sBaseCmd.' -r 26');
 618         }
 619
 620         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
 621         {
 622                 echo "Search indices\n";
 623                 $bDidSomething = true;
 624                 $oDB =& getDB();
 625                 $sSQL = 'select distinct partition from country_name';
 626                 $aPartitions = $oDB->getCol($sSQL);
 627                 if (PEAR::isError($aPartitions))
 628                 {
 629                         fail($aPartitions->getMessage());
 630                 }
 631                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 632
 633                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
 634                 $sTemplate = replace_tablespace('{ts:address-index}',
 635                                                 CONST_Tablespace_Address_Index, $sTemplate);
 636                 $sTemplate = replace_tablespace('{ts:search-index}',
 637                                                 CONST_Tablespace_Search_Index, $sTemplate);
 638                 $sTemplate = replace_tablespace('{ts:aux-index}',
 639                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 640                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 641                 foreach($aMatches as $aMatch)
 642                 {
 643                         $sResult = '';
 644                         foreach($aPartitions as $sPartitionName)
 645                         {
 646                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 647                         }
 648                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 649                 }
 650
 651                 pgsqlRunScript($sTemplate);
 652         }
 653
 654         if (isset($aCMDResult['create-website']))
 655         {
 656                 $bDidSomething = true;
 657                 $sTargetDir = $aCMDResult['create-website'];
 658                 if (!is_dir($sTargetDir))
 659                 {
 660                         echo "You must create the website directory before calling this function.\n";
 661                         fail("Target directory does not exist.");
 662                 }
 663
 664                 @symlink(CONST_InstallPath.'/website/details.php', $sTargetDir.'/details.php');
 665                 @symlink(CONST_InstallPath.'/website/reverse.php', $sTargetDir.'/reverse.php');
 666                 @symlink(CONST_InstallPath.'/website/search.php', $sTargetDir.'/search.php');
 667                 @symlink(CONST_InstallPath.'/website/search.php', $sTargetDir.'/index.php');
 668                 @symlink(CONST_InstallPath.'/website/lookup.php', $sTargetDir.'/lookup.php');
 669                 @symlink(CONST_InstallPath.'/website/deletable.php', $sTargetDir.'/deletable.php');
 670                 @symlink(CONST_InstallPath.'/website/polygons.php', $sTargetDir.'/polygons.php');
 671                 @symlink(CONST_InstallPath.'/website/status.php', $sTargetDir.'/status.php');
 672                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
 673                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
 674                 @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
 675                 echo "Symlinks created\n";
 676
 677                 $sTestFile = @file_get_contents(CONST_Website_BaseURL.'js/tiles.js');
 678                 if (!$sTestFile)
 679                 {
 680                         echo "\nWARNING: Unable to access the website at ".CONST_Website_BaseURL."\n";
 681                         echo "You may want to update settings/local.php with @define('CONST_Website_BaseURL', 'http://[HOST]/[PATH]/');\n";
 682                 }
 683         }
 684
 685         if ($aCMDResult['drop'])
 686         {
 687                 // The implementation is potentially a bit dangerous because it uses
 688                 // a positive selection of tables to keep, and deletes everything else.
 689                 // Including any tables that the unsuspecting user might have manually
 690                 // created. USE AT YOUR OWN PERIL.
 691                 $bDidSomething = true;
 692
 693                 // tables we want to keep. everything else goes.
 694                 $aKeepTables = array(
 695                    "*columns",
 696                    "import_polygon_*",
 697                    "import_status",
 698                    "place_addressline",
 699                    "location_property*",
 700                    "placex",
 701                    "search_name",
 702                    "seq_*",
 703                    "word",
 704                    "query_log",
 705                    "new_query_log",
 706                    "gb_postcode",
 707                    "spatial_ref_sys",
 708                    "country_name",
 709                    "place_classtype_*"
 710                 );
 711
 712                 $oDB =& getDB();
 713                 $aDropTables = array();
 714                 $aHaveTables = $oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'");
 715                 if (PEAR::isError($aHaveTables))
 716                 {
 717                         fail($aPartitions->getMessage());
 718                 }
 719                 foreach($aHaveTables as $sTable)
 720                 {
 721                         $bFound = false;
 722                         foreach ($aKeepTables as $sKeep)
 723                         {
 724                                 if (fnmatch($sKeep, $sTable))
 725                                 {
 726                                         $bFound = true;
 727                                         break;
 728                                 }
 729                         }
 730                         if (!$bFound) array_push($aDropTables, $sTable);
 731                 }
 732
 733                 foreach ($aDropTables as $sDrop)
 734                 {
 735                         if ($aCMDResult['verbose']) echo "dropping table $sDrop\n";
 736                         @pg_query($oDB->connection, "DROP TABLE $sDrop CASCADE");
 737                         // ignore warnings/errors as they might be caused by a table having
 738                         // been deleted already by CASCADE
 739                 }
 740
 741                 if (!is_null(CONST_Osm2pgsql_Flatnode_File))
 742                 {
 743                         if ($aCMDResult['verbose']) echo "deleting ".CONST_Osm2pgsql_Flatnode_File."\n";
 744                         unlink(CONST_Osm2pgsql_Flatnode_File);
 745                 }
 746         }
 747
 748         if (!$bDidSomething)
 749         {
 750                 showUsage($aCMDOptions, true);
 751         }
 752         else
 753         {
 754                 echo "Setup finished.\n";
 755         }
 756
 757         function pgsqlRunScriptFile($sFilename)
 758         {
 759                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
 760
 761                 // Convert database DSN to psql parameters
 762                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 763                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 764                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 765
 766                 $ahGzipPipes = null;
 767                 if (preg_match('/\\.gz$/', $sFilename))
 768                 {
 769                         $aDescriptors = array(
 770                                 0 => array('pipe', 'r'),
 771                                 1 => array('pipe', 'w'),
 772                                 2 => array('file', '/dev/null', 'a')
 773                         );
 774                         $hGzipProcess = proc_open('zcat '.$sFilename, $aDescriptors, $ahGzipPipes);
 775                         if (!is_resource($hGzipProcess)) fail('unable to start zcat');
 776                         $aReadPipe = $ahGzipPipes[1];
 777                         fclose($ahGzipPipes[0]);
 778                 }
 779                 else
 780                 {
 781                         $sCMD .= ' -f '.$sFilename;
 782                         $aReadPipe = array('pipe', 'r');
 783                 }
 784
 785                 $aDescriptors = array(
 786                         0 => $aReadPipe,
 787                         1 => array('pipe', 'w'),
 788                         2 => array('file', '/dev/null', 'a')
 789                 );
 790                 $ahPipes = null;
 791                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 792                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 793
 794
 795                 // TODO: error checking
 796                 while(!feof($ahPipes[1]))
 797                 {
 798                         echo fread($ahPipes[1], 4096);
 799                 }
 800                 fclose($ahPipes[1]);
 801
 802                 $iReturn = proc_close($hProcess);
 803                 if ($iReturn > 0)
 804                 {
 805                         fail("pgsql returned with error code ($iReturn)");
 806                 }
 807                 if ($ahGzipPipes)
 808                 {
 809                         fclose($ahGzipPipes[1]);
 810                         proc_close($hGzipProcess);
 811                 }
 812
 813         }
 814
 815         function pgsqlRunScript($sScript, $bfatal = true)
 816         {
 817                 global $aCMDResult;
 818                 // Convert database DSN to psql parameters
 819                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 820                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 821                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 822                 if ($bfatal && !$aCMDResult['ignore-errors'])
 823                         $sCMD .= ' -v ON_ERROR_STOP=1';
 824                 $aDescriptors = array(
 825                         0 => array('pipe', 'r'),
 826                         1 => STDOUT,
 827                         2 => STDERR
 828                 );
 829                 $ahPipes = null;
 830                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
 831                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 832
 833                 while(strlen($sScript))
 834                 {
 835                         $written = fwrite($ahPipes[0], $sScript);
 836                         if ($written <= 0) break;
 837                         $sScript = substr($sScript, $written);
 838                 }
 839                 fclose($ahPipes[0]);
 840                 $iReturn = proc_close($hProcess);
 841                 if ($bfatal && $iReturn > 0)
 842                 {
 843                         fail("pgsql returned with error code ($iReturn)");
 844                 }
 845         }
 846
 847         function pgsqlRunRestoreData($sDumpFile)
 848         {
 849                 // Convert database DSN to psql parameters
 850                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 851                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 852                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
 853
 854                 $aDescriptors = array(
 855                         0 => array('pipe', 'r'),
 856                         1 => array('pipe', 'w'),
 857                         2 => array('file', '/dev/null', 'a')
 858                 );
 859                 $ahPipes = null;
 860                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 861                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 862
 863                 fclose($ahPipes[0]);
 864
 865                 // TODO: error checking
 866                 while(!feof($ahPipes[1]))
 867                 {
 868                         echo fread($ahPipes[1], 4096);
 869                 }
 870                 fclose($ahPipes[1]);
 871
 872                 $iReturn = proc_close($hProcess);
 873         }
 874
 875         function pgsqlRunDropAndRestore($sDumpFile)
 876         {
 877                 // Convert database DSN to psql parameters
 878                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 879                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 880                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
 881
 882                 $aDescriptors = array(
 883                         0 => array('pipe', 'r'),
 884                         1 => array('pipe', 'w'),
 885                         2 => array('file', '/dev/null', 'a')
 886                 );
 887                 $ahPipes = null;
 888                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 889                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 890
 891                 fclose($ahPipes[0]);
 892
 893                 // TODO: error checking
 894                 while(!feof($ahPipes[1]))
 895                 {
 896                         echo fread($ahPipes[1], 4096);
 897                 }
 898                 fclose($ahPipes[1]);
 899
 900                 $iReturn = proc_close($hProcess);
 901         }
 902
 903         function passthruCheckReturn($cmd)
 904         {
 905                 $result = -1;
 906                 passthru($cmd, $result);
 907                 if ($result != 0) fail('Error executing external command: '.$cmd);
 908         }
 909
 910         function replace_tablespace($sTemplate, $sTablespace, $sSql)
 911         {
 912                 if ($sTablespace)
 913                         $sSql = str_replace($sTemplate, 'TABLESPACE "'.$sTablespace.'"',
 914                                             $sSql);
 915                 else
 916                         $sSql = str_replace($sTemplate, '', $sSql);
 917
 918                 return $sSql;
 919         }
 920
 921         function create_sql_functions($aCMDResult)
 922         {
 923                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 924                 $sTemplate = str_replace('{modulepath}', CONST_InstallPath.'/module', $sTemplate);
 925                 if ($aCMDResult['enable-diff-updates'])
 926                 {
 927                         $sTemplate = str_replace('RETURN NEW; -- %DIFFUPDATES%', '--', $sTemplate);
 928                 }
 929                 if ($aCMDResult['enable-debug-statements'])
 930                 {
 931                         $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
 932                 }
 933                 if (CONST_Limit_Reindexing)
 934                 {
 935                         $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
 936                 }
 937                 if (!CONST_Use_US_Tiger_Data)
 938                 {
 939                         $sTemplate = str_replace('-- %NOTIGERDATA% ', '', $sTemplate);
 940                 }
 941                 if (!CONST_Use_Aux_Location_data)
 942                 {
 943                         $sTemplate = str_replace('-- %NOAUXDATA% ', '', $sTemplate);
 944                 }
 945                 pgsqlRunScript($sTemplate);
 946
 947         }
 948