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                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_statecounty.sql');
 160                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_state.sql');
 161                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
 162
 163                 if ($aCMDResult['no-partitions'])
 164                 {
 165                         pgsqlRunScript('update country_name set partition = 0');
 166                 }
 167
 168                 // the following will be needed by create_functions later but
 169                 // is only defined in the subsequently called create_tables.
 170                 // Create dummies here that will be overwritten by the proper
 171                 // versions in create-tables.
 172                 pgsqlRunScript('CREATE TABLE place_boundingbox ()');
 173                 pgsqlRunScript('create type wikipedia_article_match as ()');
 174         }
 175
 176         if ($aCMDResult['import-data'] || $aCMDResult['all'])
 177         {
 178                 echo "Import\n";
 179                 $bDidSomething = true;
 180
 181                 $osm2pgsql = CONST_Osm2pgsql_Binary;
 182                 if (!file_exists($osm2pgsql))
 183                 {
 184                         echo "Please download and build osm2pgsql.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 185                         fail("osm2pgsql not found in '$osm2pgsql'");
 186                 }
 187
 188                 if (!is_null(CONST_Osm2pgsql_Flatnode_File))
 189                 {
 190                         $osm2pgsql .= ' --flat-nodes '.CONST_Osm2pgsql_Flatnode_File;
 191                 }
 192                 if (CONST_Tablespace_Osm2pgsql_Data)
 193                         $osm2pgsql .= ' --tablespace-slim-data '.CONST_Tablespace_Osm2pgsql_Data;
 194                 if (CONST_Tablespace_Osm2pgsql_Index)
 195                         $osm2pgsql .= ' --tablespace-slim-index '.CONST_Tablespace_Osm2pgsql_Index;
 196                 if (CONST_Tablespace_Place_Data)
 197                         $osm2pgsql .= ' --tablespace-main-data '.CONST_Tablespace_Place_Data;
 198                 if (CONST_Tablespace_Place_Index)
 199                         $osm2pgsql .= ' --tablespace-main-index '.CONST_Tablespace_Place_Index;
 200                 $osm2pgsql .= ' -lsc -O gazetteer --hstore --number-processes 1';
 201                 $osm2pgsql .= ' -C '.$iCacheMemory;
 202                 $osm2pgsql .= ' -P '.$aDSNInfo['port'];
 203                 $osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
 204                 passthruCheckReturn($osm2pgsql);
 205
 206                 $oDB =& getDB();
 207                 $x = $oDB->getRow('select * from place limit 1');
 208                 if (PEAR::isError($x)) {
 209                         fail($x->getMessage());
 210                 }
 211                 if (!$x) fail('No Data');
 212         }
 213
 214         if ($aCMDResult['create-functions'] || $aCMDResult['all'])
 215         {
 216                 echo "Functions\n";
 217                 $bDidSomething = true;
 218                 if (!file_exists(CONST_InstallPath.'/module/nominatim.so')) fail("nominatim module not built");
 219                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 220                 $sTemplate = str_replace('{modulepath}', CONST_InstallPath.'/module', $sTemplate);
 221                 if ($aCMDResult['enable-diff-updates']) $sTemplate = str_replace('RETURN NEW; -- @DIFFUPDATES@', '--', $sTemplate);
 222                 if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
 223                 if (CONST_Limit_Reindexing) $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
 224                 pgsqlRunScript($sTemplate);
 225         }
 226
 227         if ($aCMDResult['create-tables'] || $aCMDResult['all'])
 228         {
 229                 $bDidSomething = true;
 230
 231                 echo "Tables\n";
 232                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tables.sql');
 233                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
 234                 $sTemplate = replace_tablespace('{ts:address-data}',
 235                                                 CONST_Tablespace_Address_Data, $sTemplate);
 236                 $sTemplate = replace_tablespace('{ts:address-index}',
 237                                                 CONST_Tablespace_Address_Index, $sTemplate);
 238                 $sTemplate = replace_tablespace('{ts:search-data}',
 239                                                 CONST_Tablespace_Search_Data, $sTemplate);
 240                 $sTemplate = replace_tablespace('{ts:search-index}',
 241                                                 CONST_Tablespace_Search_Index, $sTemplate);
 242                 $sTemplate = replace_tablespace('{ts:aux-data}',
 243                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 244                 $sTemplate = replace_tablespace('{ts:aux-index}',
 245                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 246                 pgsqlRunScript($sTemplate, false);
 247
 248                 // re-run the functions
 249                 echo "Functions\n";
 250                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 251                 $sTemplate = str_replace('{modulepath}',
 252                                              CONST_InstallPath.'/module', $sTemplate);
 253                 pgsqlRunScript($sTemplate);
 254         }
 255
 256         if ($aCMDResult['create-partition-tables'] || $aCMDResult['all'])
 257         {
 258                 echo "Partition Tables\n";
 259                 $bDidSomething = true;
 260                 $oDB =& getDB();
 261                 $sSQL = 'select distinct partition from country_name';
 262                 $aPartitions = $oDB->getCol($sSQL);
 263                 if (PEAR::isError($aPartitions))
 264                 {
 265                         fail($aPartitions->getMessage());
 266                 }
 267                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 268
 269                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
 270                 $sTemplate = replace_tablespace('{ts:address-data}',
 271                                                 CONST_Tablespace_Address_Data, $sTemplate);
 272                 $sTemplate = replace_tablespace('{ts:address-index}',
 273                                                 CONST_Tablespace_Address_Index, $sTemplate);
 274                 $sTemplate = replace_tablespace('{ts:search-data}',
 275                                                 CONST_Tablespace_Search_Data, $sTemplate);
 276                 $sTemplate = replace_tablespace('{ts:search-index}',
 277                                                 CONST_Tablespace_Search_Index, $sTemplate);
 278                 $sTemplate = replace_tablespace('{ts:aux-data}',
 279                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 280                 $sTemplate = replace_tablespace('{ts:aux-index}',
 281                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 282                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 283                 foreach($aMatches as $aMatch)
 284                 {
 285                         $sResult = '';
 286                         foreach($aPartitions as $sPartitionName)
 287                         {
 288                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 289                         }
 290                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 291                 }
 292
 293                 pgsqlRunScript($sTemplate);
 294         }
 295
 296
 297         if ($aCMDResult['create-partition-functions'] || $aCMDResult['all'])
 298         {
 299                 echo "Partition Functions\n";
 300                 $bDidSomething = true;
 301                 $oDB =& getDB();
 302                 $sSQL = 'select distinct partition from country_name';
 303                 $aPartitions = $oDB->getCol($sSQL);
 304                 if (PEAR::isError($aPartitions))
 305                 {
 306                         fail($aPartitions->getMessage());
 307                 }
 308                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 309
 310                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
 311                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 312                 foreach($aMatches as $aMatch)
 313                 {
 314                         $sResult = '';
 315                         foreach($aPartitions as $sPartitionName)
 316                         {
 317                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 318                         }
 319                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 320                 }
 321
 322                 pgsqlRunScript($sTemplate);
 323         }
 324
 325         if ($aCMDResult['import-wikipedia-articles'] || $aCMDResult['all'])
 326         {
 327                 $bDidSomething = true;
 328                 $sWikiArticlesFile = CONST_BasePath.'/data/wikipedia_article.sql.bin';
 329                 $sWikiRedirectsFile = CONST_BasePath.'/data/wikipedia_redirect.sql.bin';
 330                 if (file_exists($sWikiArticlesFile))
 331                 {
 332                         echo "Importing wikipedia articles...";
 333                         pgsqlRunDropAndRestore($sWikiArticlesFile);
 334                         echo "...done\n";
 335                 }
 336                 else
 337                 {
 338                         echo "WARNING: wikipedia article dump file not found - places will have default importance\n";
 339                 }
 340                 if (file_exists($sWikiRedirectsFile))
 341                 {
 342                         echo "Importing wikipedia redirects...";
 343                         pgsqlRunDropAndRestore($sWikiRedirectsFile);
 344                         echo "...done\n";
 345                 }
 346                 else
 347                 {
 348                         echo "WARNING: wikipedia redirect dump file not found - some place importance values may be missing\n";
 349                 }
 350         }
 351
 352
 353         if ($aCMDResult['load-data'] || $aCMDResult['all'])
 354         {
 355                 echo "Drop old Data\n";
 356                 $bDidSomething = true;
 357
 358                 $oDB =& getDB();
 359                 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
 360                 echo '.';
 361                 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
 362                 echo '.';
 363                 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
 364                 echo '.';
 365                 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
 366                 echo '.';
 367                 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
 368                 echo '.';
 369                 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
 370                 echo '.';
 371                 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
 372                 echo '.';
 373                 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
 374                 echo '.';
 375                 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
 376                 echo '.';
 377
 378                 $sSQL = 'select distinct partition from country_name';
 379                 $aPartitions = $oDB->getCol($sSQL);
 380                 if (PEAR::isError($aPartitions))
 381                 {
 382                         fail($aPartitions->getMessage());
 383                 }
 384                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 385                 foreach($aPartitions as $sPartition)
 386                 {
 387                         if (!pg_query($oDB->connection, 'TRUNCATE location_road_'.$sPartition)) fail(pg_last_error($oDB->connection));
 388                         echo '.';
 389                 }
 390
 391                 // used by getorcreate_word_id to ignore frequent partial words
 392                 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));
 393                 echo ".\n";
 394
 395                 // pre-create the word list
 396                 if (!$aCMDResult['disable-token-precalc'])
 397                 {
 398                         echo "Loading word list\n";
 399                         pgsqlRunScriptFile(CONST_BasePath.'/data/words.sql');
 400                 }
 401
 402                 echo "Load Data\n";
 403                 $aDBInstances = array();
 404                 for($i = 0; $i < $iInstances; $i++)
 405                 {
 406                         $aDBInstances[$i] =& getDB(true);
 407                         $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
 408                         $sSQL .= 'housenumber, street, addr_place, isin, postcode, country_code, extratags, ';
 409                         $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
 410                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 411                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 412                 }
 413                 $bAnyBusy = true;
 414                 while($bAnyBusy)
 415                 {
 416                         $bAnyBusy = false;
 417                         for($i = 0; $i < $iInstances; $i++)
 418                         {
 419                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 420                         }
 421                         sleep(1);
 422                         echo '.';
 423                 }
 424                 echo "\n";
 425                 echo "Reanalysing database...\n";
 426                 pgsqlRunScript('ANALYSE');
 427         }
 428
 429         if ($aCMDResult['import-tiger-data'])
 430         {
 431                 $bDidSomething = true;
 432
 433                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_start.sql');
 434                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
 435                 $sTemplate = replace_tablespace('{ts:aux-data}',
 436                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 437                 $sTemplate = replace_tablespace('{ts:aux-index}',
 438                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 439                 pgsqlRunScript($sTemplate, false);
 440
 441                 $aDBInstances = array();
 442                 for($i = 0; $i < $iInstances; $i++)
 443                 {
 444                         $aDBInstances[$i] =& getDB(true);
 445                 }
 446
 447                 foreach(glob(CONST_Tiger_Data_Path.'/*.sql') as $sFile)
 448                 {
 449                         echo $sFile.': ';
 450                         $hFile = fopen($sFile, "r");
 451                         $sSQL = fgets($hFile, 100000);
 452                         $iLines = 0;
 453
 454                         while(true)
 455                         {
 456                                 for($i = 0; $i < $iInstances; $i++)
 457                                 {
 458                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
 459                                         {
 460                                                 while(pg_get_result($aDBInstances[$i]->connection));
 461                                                 $sSQL = fgets($hFile, 100000);
 462                                                 if (!$sSQL) break 2;
 463                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 464                                                 $iLines++;
 465                                                 if ($iLines == 1000)
 466                                                 {
 467                                                         echo ".";
 468                                                         $iLines = 0;
 469                                                 }
 470                                         }
 471                                 }
 472                                 usleep(10);
 473                         }
 474
 475                         fclose($hFile);
 476
 477                         $bAnyBusy = true;
 478                         while($bAnyBusy)
 479                         {
 480                                 $bAnyBusy = false;
 481                                 for($i = 0; $i < $iInstances; $i++)
 482                                 {
 483                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 484                                 }
 485                                 usleep(10);
 486                         }
 487                         echo "\n";
 488                 }
 489
 490                 echo "Creating indexes\n";
 491                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_finish.sql');
 492                 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
 493                 $sTemplate = replace_tablespace('{ts:aux-data}',
 494                                                 CONST_Tablespace_Aux_Data, $sTemplate);
 495                 $sTemplate = replace_tablespace('{ts:aux-index}',
 496                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 497                 pgsqlRunScript($sTemplate, false);
 498         }
 499
 500         if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
 501         {
 502                 $bDidSomething = true;
 503                 $oDB =& getDB();
 504                 if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
 505                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 506                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
 507                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
 508                 $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
 509                 $sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x";
 510                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 511
 512                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 513                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
 514                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
 515                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 516         }
 517
 518         if ($aCMDResult['osmosis-init'] || ($aCMDResult['all'] && !$aCMDResult['drop'])) // no use doing osmosis-init when dropping update tables
 519         {
 520                 $bDidSomething = true;
 521                 $oDB =& getDB();
 522
 523                 if (!file_exists(CONST_Osmosis_Binary))
 524                 {
 525                         echo "Please download osmosis.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 526                         if (!$aCMDResult['all'])
 527                         {
 528                                 fail("osmosis not found in '".CONST_Osmosis_Binary."'");
 529                         }
 530                 }
 531                 else
 532                 {
 533                         if (file_exists(CONST_InstallPath.'/settings/configuration.txt'))
 534                         {
 535                                 echo "settings/configuration.txt already exists\n";
 536                         }
 537                         else
 538                         {
 539                                 passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_InstallPath.'/settings');
 540                                 // update osmosis configuration.txt with our settings
 541                                 passthru("sed -i 's!baseUrl=.*!baseUrl=".CONST_Replication_Url."!' ".CONST_InstallPath.'/settings/configuration.txt');
 542                                 passthru("sed -i 's:maxInterval = .*:maxInterval = ".CONST_Replication_MaxInterval.":' ".CONST_InstallPath.'/settings/configuration.txt');
 543                         }
 544
 545                         // Find the last node in the DB
 546                         $iLastOSMID = $oDB->getOne("select max(osm_id) from place where osm_type = 'N'");
 547
 548                         // Lookup the timestamp that node was created (less 3 hours for margin for changsets to be closed)
 549                         $sLastNodeURL = 'http://www.openstreetmap.org/api/0.6/node/'.$iLastOSMID."/1";
 550                         $sLastNodeXML = file_get_contents($sLastNodeURL);
 551                         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);
 552                         $iLastNodeTimestamp = strtotime($aLastNodeDate[1]) - (3*60*60);
 553
 554                         // Search for the correct state file - uses file timestamps so need to sort by date descending
 555                         $sRepURL = CONST_Replication_Url."/";
 556                         $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 557                         // download.geofabrik.de:    <a href="000/">000/</a></td><td align="right">26-Feb-2013 11:53  </td>
 558                         // planet.openstreetmap.org: <a href="273/">273/</a>                    2013-03-11 07:41    -
 559                         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);
 560                         if ($aRepMatches)
 561                         {
 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];
 571                                 $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 572                                 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);
 573                                 $aPrevRepMatch = false;
 574                                 foreach($aRepMatches as $aRepMatch)
 575                                 {
 576                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 577                                         $aPrevRepMatch = $aRepMatch;
 578                                 }
 579                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 580
 581                                 $sRepURL .= $aRepMatch[1];
 582                                 $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 583                                 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);
 584                                 $aPrevRepMatch = false;
 585                                 foreach($aRepMatches as $aRepMatch)
 586                                 {
 587                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 588                                         $aPrevRepMatch = $aRepMatch;
 589                                 }
 590                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 591
 592                                 $sRepURL .= $aRepMatch[1].'.state.txt';
 593                                 echo "Getting state file: $sRepURL\n";
 594                                 $sStateFile = file_get_contents($sRepURL);
 595                                 if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
 596                                 file_put_contents(CONST_InstallPath.'/settings/state.txt', $sStateFile);
 597                                 echo "Updating DB status\n";
 598                                 pg_query($oDB->connection, 'TRUNCATE import_status');
 599                                 $sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
 600                                 pg_query($oDB->connection, $sSQL);
 601                         }
 602                         else
 603                         {
 604                                 if (!$aCMDResult['all'])
 605                                 {
 606                                         fail("Cannot read state file directory.");
 607                                 }
 608                         }
 609                 }
 610         }
 611
 612         if ($aCMDResult['index'] || $aCMDResult['all'])
 613         {
 614                 $bDidSomething = true;
 615                 $sOutputFile = '';
 616                 $sBaseCmd = CONST_InstallPath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
 617                 passthruCheckReturn($sBaseCmd.' -R 4');
 618                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 619                 passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
 620                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 621                 passthruCheckReturn($sBaseCmd.' -r 26');
 622         }
 623
 624         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
 625         {
 626                 echo "Search indices\n";
 627                 $bDidSomething = true;
 628                 $oDB =& getDB();
 629                 $sSQL = 'select distinct partition from country_name';
 630                 $aPartitions = $oDB->getCol($sSQL);
 631                 if (PEAR::isError($aPartitions))
 632                 {
 633                         fail($aPartitions->getMessage());
 634                 }
 635                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 636
 637                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
 638                 $sTemplate = replace_tablespace('{ts:address-index}',
 639                                                 CONST_Tablespace_Address_Index, $sTemplate);
 640                 $sTemplate = replace_tablespace('{ts:search-index}',
 641                                                 CONST_Tablespace_Search_Index, $sTemplate);
 642                 $sTemplate = replace_tablespace('{ts:aux-index}',
 643                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 644                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 645                 foreach($aMatches as $aMatch)
 646                 {
 647                         $sResult = '';
 648                         foreach($aPartitions as $sPartitionName)
 649                         {
 650                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 651                         }
 652                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 653                 }
 654
 655                 pgsqlRunScript($sTemplate);
 656         }
 657
 658         if (isset($aCMDResult['create-website']))
 659         {
 660                 $bDidSomething = true;
 661                 $sTargetDir = $aCMDResult['create-website'];
 662                 if (!is_dir($sTargetDir))
 663                 {
 664                         echo "You must create the website directory before calling this function.\n";
 665                         fail("Target directory does not exist.");
 666                 }
 667
 668                 @symlink(CONST_InstallPath.'/website/details.php', $sTargetDir.'/details.php');
 669                 @symlink(CONST_InstallPath.'/website/reverse.php', $sTargetDir.'/reverse.php');
 670                 @symlink(CONST_InstallPath.'/website/search.php', $sTargetDir.'/search.php');
 671                 @symlink(CONST_InstallPath.'/website/search.php', $sTargetDir.'/index.php');
 672                 @symlink(CONST_InstallPath.'/website/lookup.php', $sTargetDir.'/lookup.php');
 673                 @symlink(CONST_InstallPath.'/website/deletable.php', $sTargetDir.'/deletable.php');
 674                 @symlink(CONST_InstallPath.'/website/polygons.php', $sTargetDir.'/polygons.php');
 675                 @symlink(CONST_InstallPath.'/website/status.php', $sTargetDir.'/status.php');
 676                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
 677                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
 678                 @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
 679                 echo "Symlinks created\n";
 680
 681                 $sTestFile = @file_get_contents(CONST_Website_BaseURL.'js/tiles.js');
 682                 if (!$sTestFile)
 683                 {
 684                         echo "\nWARNING: Unable to access the website at ".CONST_Website_BaseURL."\n";
 685                         echo "You may want to update settings/local.php with @define('CONST_Website_BaseURL', 'http://[HOST]/[PATH]/');\n";
 686                 }
 687         }
 688
 689         if ($aCMDResult['drop'])
 690         {
 691                 // The implementation is potentially a bit dangerous because it uses
 692                 // a positive selection of tables to keep, and deletes everything else.
 693                 // Including any tables that the unsuspecting user might have manually
 694                 // created. USE AT YOUR OWN PERIL.
 695                 $bDidSomething = true;
 696
 697                 // tables we want to keep. everything else goes.
 698                 $aKeepTables = array(
 699                    "*columns",
 700                    "import_polygon_*",
 701                    "import_status",
 702                    "place_addressline",
 703                    "location_property*",
 704                    "placex",
 705                    "search_name",
 706                    "seq_*",
 707                    "word",
 708                    "query_log",
 709                    "new_query_log",
 710                    "gb_postcode",
 711                    "spatial_ref_sys",
 712                    "country_name",
 713                    "place_classtype_*"
 714                 );
 715
 716                 $oDB =& getDB();
 717                 $aDropTables = array();
 718                 $aHaveTables = $oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'");
 719                 if (PEAR::isError($aHaveTables))
 720                 {
 721                         fail($aPartitions->getMessage());
 722                 }
 723                 foreach($aHaveTables as $sTable)
 724                 {
 725                         $bFound = false;
 726                         foreach ($aKeepTables as $sKeep)
 727                         {
 728                                 if (fnmatch($sKeep, $sTable))
 729                                 {
 730                                         $bFound = true;
 731                                         break;
 732                                 }
 733                         }
 734                         if (!$bFound) array_push($aDropTables, $sTable);
 735                 }
 736
 737                 foreach ($aDropTables as $sDrop)
 738                 {
 739                         if ($aCMDResult['verbose']) echo "dropping table $sDrop\n";
 740                         @pg_query($oDB->connection, "DROP TABLE $sDrop CASCADE");
 741                         // ignore warnings/errors as they might be caused by a table having
 742                         // been deleted already by CASCADE
 743                 }
 744
 745                 if (!is_null(CONST_Osm2pgsql_Flatnode_File))
 746                 {
 747                         if ($aCMDResult['verbose']) echo "deleting ".CONST_Osm2pgsql_Flatnode_File."\n";
 748                         unlink(CONST_Osm2pgsql_Flatnode_File);
 749                 }
 750         }
 751
 752         if (!$bDidSomething)
 753         {
 754                 showUsage($aCMDOptions, true);
 755         }
 756         else
 757         {
 758                 echo "Setup finished.\n";
 759         }
 760
 761         function pgsqlRunScriptFile($sFilename)
 762         {
 763                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
 764
 765                 // Convert database DSN to psql parameters
 766                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 767                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 768                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 769
 770                 $ahGzipPipes = null;
 771                 if (preg_match('/\\.gz$/', $sFilename))
 772                 {
 773                         $aDescriptors = array(
 774                                 0 => array('pipe', 'r'),
 775                                 1 => array('pipe', 'w'),
 776                                 2 => array('file', '/dev/null', 'a')
 777                         );
 778                         $hGzipProcess = proc_open('zcat '.$sFilename, $aDescriptors, $ahGzipPipes);
 779                         if (!is_resource($hGzipProcess)) fail('unable to start zcat');
 780                         $aReadPipe = $ahGzipPipes[1];
 781                         fclose($ahGzipPipes[0]);
 782                 }
 783                 else
 784                 {
 785                         $sCMD .= ' -f '.$sFilename;
 786                         $aReadPipe = array('pipe', 'r');
 787                 }
 788
 789                 $aDescriptors = array(
 790                         0 => $aReadPipe,
 791                         1 => array('pipe', 'w'),
 792                         2 => array('file', '/dev/null', 'a')
 793                 );
 794                 $ahPipes = null;
 795                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 796                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 797
 798
 799                 // TODO: error checking
 800                 while(!feof($ahPipes[1]))
 801                 {
 802                         echo fread($ahPipes[1], 4096);
 803                 }
 804                 fclose($ahPipes[1]);
 805
 806                 $iReturn = proc_close($hProcess);
 807                 if ($iReturn > 0)
 808                 {
 809                         fail("pgsql returned with error code ($iReturn)");
 810                 }
 811                 if ($ahGzipPipes)
 812                 {
 813                         fclose($ahGzipPipes[1]);
 814                         proc_close($hGzipProcess);
 815                 }
 816
 817         }
 818
 819         function pgsqlRunScript($sScript, $bfatal = true)
 820         {
 821                 global $aCMDResult;
 822                 // Convert database DSN to psql parameters
 823                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 824                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 825                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 826                 if ($bfatal && !$aCMDResult['ignore-errors'])
 827                         $sCMD .= ' -v ON_ERROR_STOP=1';
 828                 $aDescriptors = array(
 829                         0 => array('pipe', 'r'),
 830                         1 => STDOUT,
 831                         2 => STDERR
 832                 );
 833                 $ahPipes = null;
 834                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
 835                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 836
 837                 while(strlen($sScript))
 838                 {
 839                         $written = fwrite($ahPipes[0], $sScript);
 840                         if ($written <= 0) break;
 841                         $sScript = substr($sScript, $written);
 842                 }
 843                 fclose($ahPipes[0]);
 844                 $iReturn = proc_close($hProcess);
 845                 if ($bfatal && $iReturn > 0)
 846                 {
 847                         fail("pgsql returned with error code ($iReturn)");
 848                 }
 849         }
 850
 851         function pgsqlRunRestoreData($sDumpFile)
 852         {
 853                 // Convert database DSN to psql parameters
 854                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 855                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 856                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
 857
 858                 $aDescriptors = array(
 859                         0 => array('pipe', 'r'),
 860                         1 => array('pipe', 'w'),
 861                         2 => array('file', '/dev/null', 'a')
 862                 );
 863                 $ahPipes = null;
 864                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 865                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 866
 867                 fclose($ahPipes[0]);
 868
 869                 // TODO: error checking
 870                 while(!feof($ahPipes[1]))
 871                 {
 872                         echo fread($ahPipes[1], 4096);
 873                 }
 874                 fclose($ahPipes[1]);
 875
 876                 $iReturn = proc_close($hProcess);
 877         }
 878
 879         function pgsqlRunDropAndRestore($sDumpFile)
 880         {
 881                 // Convert database DSN to psql parameters
 882                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 883                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 884                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
 885
 886                 $aDescriptors = array(
 887                         0 => array('pipe', 'r'),
 888                         1 => array('pipe', 'w'),
 889                         2 => array('file', '/dev/null', 'a')
 890                 );
 891                 $ahPipes = null;
 892                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 893                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 894
 895                 fclose($ahPipes[0]);
 896
 897                 // TODO: error checking
 898                 while(!feof($ahPipes[1]))
 899                 {
 900                         echo fread($ahPipes[1], 4096);
 901                 }
 902                 fclose($ahPipes[1]);
 903
 904                 $iReturn = proc_close($hProcess);
 905         }
 906
 907         function passthruCheckReturn($cmd)
 908         {
 909                 $result = -1;
 910                 passthru($cmd, $result);
 911                 if ($result != 0) fail('Error executing external command: '.$cmd);
 912         }
 913
 914         function replace_tablespace($sTemplate, $sTablespace, $sSql)
 915         {
 916                 if ($sTablespace)
 917                         $sSql = str_replace($sTemplate, 'TABLESPACE "'.$sTablespace.'"',
 918                                             $sSql);
 919                 else
 920                         $sSql = str_replace($sTemplate, '', $sSql);
 921
 922                 return $sSql;
 923         }
 924