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