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