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