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