utils/setup.php

   1 #!/usr/bin/php -Cq
   2 <?php
   3
   4         require_once(dirname(dirname(__FILE__)).'/lib/init-cmd.php');
   5         ini_set('memory_limit', '800M');
   6
   7         $aCMDOptions = array(
   8                 "Create and setup nominatim search system",
   9                 array('help', 'h', 0, 1, 0, 0, false, 'Show Help'),
  10                 array('quiet', 'q', 0, 1, 0, 0, 'bool', 'Quiet output'),
  11                 array('verbose', 'v', 0, 1, 0, 0, 'bool', 'Verbose output'),
  12
  13                 array('osm-file', '', 0, 1, 1, 1, 'realpath', 'File to import'),
  14                 array('threads', '', 0, 1, 1, 1, 'int', 'Number of threads (where possible)'),
  15
  16                 array('all', '', 0, 1, 0, 0, 'bool', 'Do the complete process'),
  17
  18                 array('create-db', '', 0, 1, 0, 0, 'bool', 'Create nominatim db'),
  19                 array('setup-db', '', 0, 1, 0, 0, 'bool', 'Build a blank nominatim db'),
  20                 array('import-data', '', 0, 1, 0, 0, 'bool', 'Import a osm file'),
  21                 array('osm2pgsql-cache', '', 0, 1, 1, 1, 'int', 'Cache size used by osm2pgsql'),
  22                 array('create-functions', '', 0, 1, 0, 0, 'bool', 'Create functions'),
  23                 array('enable-diff-updates', '', 0, 1, 0, 0, 'bool', 'Turn on the code required to make diff updates work'),
  24                 array('enable-debug-statements', '', 0, 1, 0, 0, 'bool', 'Include debug warning statements in pgsql commands'),
  25                 array('create-minimal-tables', '', 0, 1, 0, 0, 'bool', 'Create minimal main tables'),
  26                 array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
  27                 array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'),
  28                 array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'),
  29                 array('no-partitions', '', 0, 1, 0, 0, 'bool', "Do not partition search indices (speeds up import of single country extracts)"),
  30                 array('import-wikipedia-articles', '', 0, 1, 0, 0, 'bool', 'Import wikipedia article dump'),
  31                 array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'),
  32                 array('disable-token-precalc', '', 0, 1, 0, 0, 'bool', 'Disable name precalculation (EXPERT)'),
  33                 array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'),
  34                 array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
  35                 array('create-roads', '', 0, 1, 0, 0, 'bool', ''),
  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('index-output', '', 0, 1, 1, 1, 'string', 'File to dump index information to'),
  40                 array('create-search-indices', '', 0, 1, 0, 0, 'bool', 'Create additional indices required for search and update'),
  41                 array('create-website', '', 0, 1, 1, 1, 'realpath', 'Create symlinks to setup web directory'),
  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         $iCacheMemory = (isset($aCMDResult['osm2pgsql-cache'])?$aCMDResult['osm2pgsql-cache']:getCacheMemoryMB());
  82         if ($iCacheMemory > getTotalMemoryMB())
  83         {
  84                 $iCacheMemory = getCacheMemoryMB();
  85                 echo "WARNING: resetting cache memory to $iCacheMemory\n";
  86         }
  87
  88         $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
  89         if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
  90
  91         if ($aCMDResult['create-db'] || $aCMDResult['all'])
  92         {
  93                 echo "Create DB\n";
  94                 $bDidSomething = true;
  95                 $oDB =& DB::connect(CONST_Database_DSN, false);
  96                 if (!PEAR::isError($oDB))
  97                 {
  98                         fail('database already exists ('.CONST_Database_DSN.')');
  99                 }
 100                 passthruCheckReturn('createdb -E UTF-8 -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
 101         }
 102
 103         if ($aCMDResult['setup-db'] || $aCMDResult['all'])
 104         {
 105                 echo "Setup DB\n";
 106                 $bDidSomething = true;
 107                 // TODO: path detection, detection memory, etc.
 108
 109                 $oDB =& getDB();
 110
 111                 $sVersionString = $oDB->getOne('select version()');
 112                 preg_match('#PostgreSQL ([0-9]+)[.]([0-9]+)[.]([0-9]+) #', $sVersionString, $aMatches);
 113                 if (CONST_Postgresql_Version != $aMatches[1].'.'.$aMatches[2])
 114                 {
 115                         echo "ERROR: PostgreSQL version is not correct.  Expected ".CONST_Postgresql_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n";
 116                         exit;
 117                 }
 118
 119                 passthru('createlang plpgsql -p '.$aDSNInfo['port'].' '.$aDSNInfo['database']);
 120                 $pgver = (float) CONST_Postgresql_Version;
 121                 if ($pgver < 9.1) {
 122                         pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/hstore.sql');
 123                         pgsqlRunScriptFile(CONST_BasePath.'/sql/hstore_compatability_9_0.sql');
 124                 } else {
 125                         pgsqlRunScript('CREATE EXTENSION hstore');
 126                 }
 127
 128                 $fPostgisVersion = (float) CONST_Postgis_Version;
 129                 if ($fPostgisVersion < 2.0) {
 130                         pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
 131                         pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
 132                 } else {
 133                         pgsqlRunScript('CREATE EXTENSION postgis');
 134                 }
 135                 if ($fPostgisVersion < 2.1) {
 136                         // Function was renamed in 2.1 and throws an annoying deprecation warning
 137                         pgsqlRunScript('ALTER FUNCTION st_line_interpolate_point(geometry, double precision) RENAME TO ST_LineInterpolatePoint');
 138                 }
 139                 $sVersionString = $oDB->getOne('select postgis_full_version()');
 140                 preg_match('#POSTGIS="([0-9]+)[.]([0-9]+)[.]([0-9]+)( r([0-9]+))?"#', $sVersionString, $aMatches);
 141                 if (CONST_Postgis_Version != $aMatches[1].'.'.$aMatches[2])
 142                 {
 143                         echo "ERROR: PostGIS version is not correct.  Expected ".CONST_Postgis_Version." found ".$aMatches[1].'.'.$aMatches[2]."\n";
 144                         exit;
 145                 }
 146
 147                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
 148                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql');
 149                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql');
 150                 pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode_table.sql');
 151                 if (file_exists(CONST_BasePath.'/data/gb_postcode_data.sql.gz'))
 152                 {
 153                         pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode_data.sql.gz');
 154                 }
 155                 else
 156                 {
 157                         echo "WARNING: external UK postcode table not found.\n";
 158                 }
 159                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_statecounty.sql');
 160                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_state.sql');
 161                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
 162
 163                 if ($aCMDResult['no-partitions'])
 164                 {
 165                         pgsqlRunScript('update country_name set partition = 0');
 166                 }
 167         }
 168
 169         if ($aCMDResult['import-data'] || $aCMDResult['all'])
 170         {
 171                 echo "Import\n";
 172                 $bDidSomething = true;
 173
 174                 $osm2pgsql = CONST_Osm2pgsql_Binary;
 175                 if (!file_exists($osm2pgsql))
 176                 {
 177                         echo "Please download and build osm2pgsql.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 178                         fail("osm2pgsql not found in '$osm2pgsql'");
 179                 }
 180
 181                 if (!is_null(CONST_Osm2pgsql_Flatnode_File))
 182                 {
 183                         $osm2pgsql .= ' --flat-nodes '.CONST_Osm2pgsql_Flatnode_File;
 184                 }
 185                 $osm2pgsql .= ' -lsc -O gazetteer --hstore';
 186                 $osm2pgsql .= ' -C '.$iCacheMemory;
 187                 $osm2pgsql .= ' -P '.$aDSNInfo['port'];
 188                 $osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
 189                 passthruCheckReturn($osm2pgsql);
 190
 191                 $oDB =& getDB();
 192                 $x = $oDB->getRow('select * from place limit 1');
 193                 if (PEAR::isError($x)) {
 194                         fail($x->getMessage());
 195                 }
 196                 if (!$x) fail('No Data');
 197         }
 198
 199         if ($aCMDResult['create-functions'] || $aCMDResult['all'])
 200         {
 201                 echo "Functions\n";
 202                 $bDidSomething = true;
 203                 if (!file_exists(CONST_BasePath.'/module/nominatim.so')) fail("nominatim module not built");
 204                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 205                 $sTemplate = str_replace('{modulepath}', CONST_BasePath.'/module', $sTemplate);
 206                 if ($aCMDResult['enable-diff-updates']) $sTemplate = str_replace('RETURN NEW; -- @DIFFUPDATES@', '--', $sTemplate);
 207                 if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
 208                 if (CONST_Limit_Reindexing) $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
 209                 pgsqlRunScript($sTemplate);
 210         }
 211
 212         if ($aCMDResult['create-minimal-tables'])
 213         {
 214                 echo "Minimal Tables\n";
 215                 $bDidSomething = true;
 216                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables-minimal.sql');
 217
 218                 $sScript = '';
 219
 220                 // Backstop the import process - easliest possible import id
 221                 $sScript .= "insert into import_npi_log values (18022);\n";
 222
 223                 $hFile = @fopen(CONST_BasePath.'/settings/partitionedtags.def', "r");
 224                 if (!$hFile) fail('unable to open list of partitions: '.CONST_BasePath.'/settings/partitionedtags.def');
 225
 226                 while (($sLine = fgets($hFile, 4096)) !== false && $sLine && substr($sLine,0,1) !='#')
 227                 {
 228                         list($sClass, $sType) = explode(' ', trim($sLine));
 229                         $sScript .= "create table place_classtype_".$sClass."_".$sType." as ";
 230                         $sScript .= "select place_id as place_id,geometry as centroid from placex limit 0;\n";
 231
 232                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_centroid ";
 233                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING GIST (centroid);\n";
 234
 235                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_place_id ";
 236                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING btree(place_id);\n";
 237                 }
 238                 fclose($hFile);
 239                 pgsqlRunScript($sScript);
 240         }
 241
 242         if ($aCMDResult['create-tables'] || $aCMDResult['all'])
 243         {
 244                 echo "Tables\n";
 245                 $bDidSomething = true;
 246                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables.sql');
 247
 248                 // re-run the functions
 249                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 250                 $sTemplate = str_replace('{modulepath}',CONST_BasePath.'/module', $sTemplate);
 251                 pgsqlRunScript($sTemplate);
 252         }
 253
 254         if ($aCMDResult['create-partition-tables'] || $aCMDResult['all'])
 255         {
 256                 echo "Partition Tables\n";
 257                 $bDidSomething = true;
 258                 $oDB =& getDB();
 259                 $sSQL = 'select distinct partition from country_name';
 260                 $aPartitions = $oDB->getCol($sSQL);
 261                 if (PEAR::isError($aPartitions))
 262                 {
 263                         fail($aPartitions->getMessage());
 264                 }
 265                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 266
 267                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
 268                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 269                 foreach($aMatches as $aMatch)
 270                 {
 271                         $sResult = '';
 272                         foreach($aPartitions as $sPartitionName)
 273                         {
 274                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 275                         }
 276                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 277                 }
 278
 279                 pgsqlRunScript($sTemplate);
 280         }
 281
 282
 283         if ($aCMDResult['create-partition-functions'] || $aCMDResult['all'])
 284         {
 285                 echo "Partition Functions\n";
 286                 $bDidSomething = true;
 287                 $oDB =& getDB();
 288                 $sSQL = 'select distinct partition from country_name';
 289                 $aPartitions = $oDB->getCol($sSQL);
 290                 if (PEAR::isError($aPartitions))
 291                 {
 292                         fail($aPartitions->getMessage());
 293                 }
 294                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 295
 296                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
 297                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 298                 foreach($aMatches as $aMatch)
 299                 {
 300                         $sResult = '';
 301                         foreach($aPartitions as $sPartitionName)
 302                         {
 303                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 304                         }
 305                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 306                 }
 307
 308                 pgsqlRunScript($sTemplate);
 309         }
 310
 311         if ($aCMDResult['import-wikipedia-articles'] || $aCMDResult['all'])
 312         {
 313                 $bDidSomething = true;
 314                 $sWikiArticlesFile = CONST_BasePath.'/data/wikipedia_article.sql.bin';
 315                 $sWikiRedirectsFile = CONST_BasePath.'/data/wikipedia_redirect.sql.bin';
 316                 if (file_exists($sWikiArticlesFile))
 317                 {
 318                         echo "Importing wikipedia articles...";
 319                         pgsqlRunDropAndRestore($sWikiArticlesFile);
 320                         echo "...done\n";
 321                 }
 322                 else
 323                 {
 324                         echo "WARNING: wikipedia article dump file not found - places will have default importance\n";
 325                 }
 326                 if (file_exists($sWikiRedirectsFile))
 327                 {
 328                         echo "Importing wikipedia redirects...";
 329                         pgsqlRunDropAndRestore($sWikiRedirectsFile);
 330                         echo "...done\n";
 331                 }
 332                 else
 333                 {
 334                         echo "WARNING: wikipedia redirect dump file not found - some place importance values may be missing\n";
 335                 }
 336         }
 337
 338
 339         if ($aCMDResult['load-data'] || $aCMDResult['all'])
 340         {
 341                 echo "Drop old Data\n";
 342                 $bDidSomething = true;
 343
 344                 $oDB =& getDB();
 345                 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
 346                 echo '.';
 347                 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
 348                 echo '.';
 349                 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
 350                 echo '.';
 351                 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
 352                 echo '.';
 353                 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
 354                 echo '.';
 355                 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
 356                 echo '.';
 357                 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
 358                 echo '.';
 359                 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
 360                 echo '.';
 361                 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
 362                 echo '.';
 363
 364                 $sSQL = 'select distinct partition from country_name';
 365                 $aPartitions = $oDB->getCol($sSQL);
 366                 if (PEAR::isError($aPartitions))
 367                 {
 368                         fail($aPartitions->getMessage());
 369                 }
 370                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 371                 foreach($aPartitions as $sPartition)
 372                 {
 373                         if (!pg_query($oDB->connection, 'TRUNCATE location_road_'.$sPartition)) fail(pg_last_error($oDB->connection));
 374                         echo '.';
 375                 }
 376
 377                 // used by getorcreate_word_id to ignore frequent partial words
 378                 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));
 379                 echo ".\n";
 380
 381                 // pre-create the word list
 382                 if (!$aCMDResult['disable-token-precalc'])
 383                 {
 384                         echo "Loading word list\n";
 385                         pgsqlRunScriptFile(CONST_BasePath.'/data/words.sql');
 386                 }
 387
 388                 echo "Load Data\n";
 389                 $aDBInstances = array();
 390                 for($i = 0; $i < $iInstances; $i++)
 391                 {
 392                         $aDBInstances[$i] =& getDB(true);
 393                         $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
 394                         $sSQL .= 'housenumber, street, addr_place, isin, postcode, country_code, extratags, ';
 395                         $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
 396                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 397                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 398                 }
 399                 $bAnyBusy = true;
 400                 while($bAnyBusy)
 401                 {
 402                         $bAnyBusy = false;
 403                         for($i = 0; $i < $iInstances; $i++)
 404                         {
 405                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 406                         }
 407                         sleep(1);
 408                         echo '.';
 409                 }
 410                 echo "\n";
 411                 echo "Reanalysing database...\n";
 412                 pgsqlRunScript('ANALYSE');
 413         }
 414
 415         if ($aCMDResult['create-roads'])
 416         {
 417                 $bDidSomething = true;
 418
 419                 $oDB =& getDB();
 420                 $aDBInstances = array();
 421                 for($i = 0; $i < $iInstances; $i++)
 422                 {
 423                         $aDBInstances[$i] =& getDB(true);
 424                         if (!pg_query($aDBInstances[$i]->connection, 'set enable_bitmapscan = off')) fail(pg_last_error($oDB->connection));
 425                         $sSQL = 'select count(*) from (select insertLocationRoad(partition, place_id, calculated_country_code, geometry) from ';
 426                         $sSQL .= 'placex where osm_id % '.$iInstances.' = '.$i.' and rank_search between 26 and 27 and class = \'highway\') as x ';
 427                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 428                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 429                 }
 430                 $bAnyBusy = true;
 431                 while($bAnyBusy)
 432                 {
 433                         $bAnyBusy = false;
 434                         for($i = 0; $i < $iInstances; $i++)
 435                         {
 436                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 437                         }
 438                         sleep(1);
 439                         echo '.';
 440                 }
 441                 echo "\n";
 442         }
 443
 444         if ($aCMDResult['import-tiger-data'])
 445         {
 446                 $bDidSomething = true;
 447
 448                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_start.sql');
 449
 450                 $aDBInstances = array();
 451                 for($i = 0; $i < $iInstances; $i++)
 452                 {
 453                         $aDBInstances[$i] =& getDB(true);
 454                 }
 455
 456                 foreach(glob(CONST_BasePath.'/data/tiger2011/*.sql') as $sFile)
 457                 {
 458                         echo $sFile.': ';
 459                         $hFile = fopen($sFile, "r");
 460                         $sSQL = fgets($hFile, 100000);
 461                         $iLines = 0;
 462
 463                         while(true)
 464                         {
 465                                 for($i = 0; $i < $iInstances; $i++)
 466                                 {
 467                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
 468                                         {
 469                                                 while(pg_get_result($aDBInstances[$i]->connection));
 470                                                 $sSQL = fgets($hFile, 100000);
 471                                                 if (!$sSQL) break 2;
 472                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 473                                                 $iLines++;
 474                                                 if ($iLines == 1000)
 475                                                 {
 476                                                         echo ".";
 477                                                         $iLines = 0;
 478                                                 }
 479                                         }
 480                                 }
 481                                 usleep(10);
 482                         }
 483
 484                         fclose($hFile);
 485
 486                         $bAnyBusy = true;
 487                         while($bAnyBusy)
 488                         {
 489                                 $bAnyBusy = false;
 490                                 for($i = 0; $i < $iInstances; $i++)
 491                                 {
 492                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 493                                 }
 494                                 usleep(10);
 495                         }
 496                         echo "\n";
 497                 }
 498
 499                 echo "Creating indexes\n";
 500                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_finish.sql');
 501         }
 502
 503         if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
 504         {
 505                 $bDidSomething = true;
 506                 $oDB =& getDB();
 507                 if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
 508                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 509                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
 510                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
 511                 $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
 512                 $sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x";
 513                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 514
 515                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 516                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
 517                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
 518                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 519         }
 520
 521         if ($aCMDResult['osmosis-init'] || $aCMDResult['all'])
 522         {
 523                 $bDidSomething = true;
 524                 $oDB =& getDB();
 525
 526                 if (!file_exists(CONST_Osmosis_Binary))
 527                 {
 528                         echo "Please download osmosis.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 529                         if (!$aCMDResult['all'])
 530                         {
 531                                 fail("osmosis not found in '".CONST_Osmosis_Binary."'");
 532                         }
 533                 }
 534                 else
 535                 {
 536                         if (file_exists(CONST_BasePath.'/settings/configuration.txt'))
 537                         {
 538                                 echo "settings/configuration.txt already exists\n";
 539                         }
 540                         else
 541                         {
 542                                 passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_BasePath.'/settings');
 543                                 // update osmosis configuration.txt with our settings
 544                                 passthru("sed -i 's!baseUrl=.*!baseUrl=".CONST_Replication_Url."!' ".CONST_BasePath.'/settings/configuration.txt');
 545                                 passthru("sed -i 's:maxInterval = .*:maxInterval = ".CONST_Replication_MaxInterval.":' ".CONST_BasePath.'/settings/configuration.txt');
 546                         }
 547
 548                         // Find the last node in the DB
 549                         $iLastOSMID = $oDB->getOne("select max(id) from planet_osm_nodes");
 550
 551                         // Lookup the timestamp that node was created (less 3 hours for margin for changsets to be closed)
 552                         $sLastNodeURL = 'http://www.openstreetmap.org/api/0.6/node/'.$iLastOSMID."/1";
 553                         $sLastNodeXML = file_get_contents($sLastNodeURL);
 554                         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);
 555                         $iLastNodeTimestamp = strtotime($aLastNodeDate[1]) - (3*60*60);
 556
 557                         // Search for the correct state file - uses file timestamps so need to sort by date descending
 558                         $sRepURL = CONST_Replication_Url."/";
 559                         $sRep = file_get_contents($sRepURL."?C=M;O=D");
 560                         // download.geofabrik.de:    <a href="000/">000/</a></td><td align="right">26-Feb-2013 11:53  </td>
 561                         // planet.openstreetmap.org: <a href="273/">273/</a>                    22-Mar-2013 07:41    -
 562                         preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>.*(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
 563                         $aPrevRepMatch = false;
 564                         foreach($aRepMatches as $aRepMatch)
 565                         {
 566                                 if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 567                                 $aPrevRepMatch = $aRepMatch;
 568                         }
 569                         if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 570
 571                         $sRepURL .= $aRepMatch[1];
 572                         $sRep = file_get_contents($sRepURL."?C=M;O=D");
 573                         preg_match_all('#<a href="[0-9]{3}/">([0-9]{3}/)</a>.*(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([0-9]{2}):([0-9]{2}))#', $sRep, $aRepMatches, PREG_SET_ORDER);
 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");
 584                         preg_match_all('#<a href="[0-9]{3}.state.txt">([0-9]{3}).state.txt</a>.*(([0-9]{2})-([A-z]{3})-([0-9]{4}) ([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].'.state.txt';
 594                         echo "Getting state file: $sRepURL\n";
 595                         $sStateFile = file_get_contents($sRepURL);
 596                         if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
 597                         file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
 598                         echo "Updating DB status\n";
 599                         pg_query($oDB->connection, 'TRUNCATE import_status');
 600                         $sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
 601                         pg_query($oDB->connection, $sSQL);
 602                 }
 603         }
 604
 605         if ($aCMDResult['index'] || $aCMDResult['all'])
 606         {
 607                 $bDidSomething = true;
 608                 $sOutputFile = '';
 609                 if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output'];
 610                 $sBaseCmd = CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
 611                 passthruCheckReturn($sBaseCmd.' -R 4');
 612                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 613                 passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
 614                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 615                 passthruCheckReturn($sBaseCmd.' -r 26');
 616         }
 617
 618         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
 619         {
 620                 echo "Search indices\n";
 621                 $bDidSomething = true;
 622                 $oDB =& getDB();
 623                 $sSQL = 'select distinct partition from country_name';
 624                 $aPartitions = $oDB->getCol($sSQL);
 625                 if (PEAR::isError($aPartitions))
 626                 {
 627                         fail($aPartitions->getMessage());
 628                 }
 629                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 630
 631                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
 632                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 633                 foreach($aMatches as $aMatch)
 634                 {
 635                         $sResult = '';
 636                         foreach($aPartitions as $sPartitionName)
 637                         {
 638                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 639                         }
 640                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 641                 }
 642
 643                 pgsqlRunScript($sTemplate);
 644         }
 645
 646         if (isset($aCMDResult['create-website']))
 647         {
 648                 $bDidSomething = true;
 649                 $sTargetDir = $aCMDResult['create-website'];
 650                 if (!is_dir($sTargetDir))
 651                 {
 652                         echo "You must create the website directory before calling this function.\n";
 653                         fail("Target directory does not exist.");
 654                 }
 655
 656                 @symlink(CONST_BasePath.'/website/details.php', $sTargetDir.'/details.php');
 657                 @symlink(CONST_BasePath.'/website/reverse.php', $sTargetDir.'/reverse.php');
 658                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/search.php');
 659                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/index.php');
 660                 @symlink(CONST_BasePath.'/website/deletable.php', $sTargetDir.'/deletable.php');
 661                 @symlink(CONST_BasePath.'/website/polygons.php', $sTargetDir.'/polygons.php');
 662                 @symlink(CONST_BasePath.'/website/status.php', $sTargetDir.'/status.php');
 663                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
 664                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
 665                 @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
 666                 echo "Symlinks created\n";
 667
 668                 $sTestFile = @file_get_contents(CONST_Website_BaseURL.'js/tiles.js');
 669                 if (!$sTestFile)
 670                 {
 671                         echo "\nWARNING: Unable to access the website at ".CONST_Website_BaseURL."\n";
 672                         echo "You may want to update settings/local.php with @define('CONST_Website_BaseURL', 'http://[HOST]/[PATH]/');\n";
 673                 }
 674         }
 675
 676         if (!$bDidSomething)
 677         {
 678                 showUsage($aCMDOptions, true);
 679         }
 680
 681         function pgsqlRunScriptFile($sFilename)
 682         {
 683                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
 684
 685                 // Convert database DSN to psql parameters
 686                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 687                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 688                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 689
 690                 $ahGzipPipes = null;
 691                 if (preg_match('/\\.gz$/', $sFilename))
 692                 {
 693                         $aDescriptors = array(
 694                                 0 => array('pipe', 'r'),
 695                                 1 => array('pipe', 'w'),
 696                                 2 => array('file', '/dev/null', 'a')
 697                         );
 698                         $hGzipProcess = proc_open('zcat '.$sFilename, $aDescriptors, $ahGzipPipes);
 699                         if (!is_resource($hGzipProcess)) fail('unable to start zcat');
 700                         $aReadPipe = $ahGzipPipes[1];
 701                         fclose($ahGzipPipes[0]);
 702                 }
 703                 else
 704                 {
 705                         $sCMD .= ' -f '.$sFilename;
 706                         $aReadPipe = array('pipe', 'r');
 707                 }
 708
 709                 $aDescriptors = array(
 710                         0 => $aReadPipe,
 711                         1 => array('pipe', 'w'),
 712                         2 => array('file', '/dev/null', 'a')
 713                 );
 714                 $ahPipes = null;
 715                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 716                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 717
 718
 719                 // TODO: error checking
 720                 while(!feof($ahPipes[1]))
 721                 {
 722                         echo fread($ahPipes[1], 4096);
 723                 }
 724                 fclose($ahPipes[1]);
 725
 726                 proc_close($hProcess);
 727                 if ($ahGzipPipes)
 728                 {
 729                         fclose($ahGzipPipes[1]);
 730                         proc_close($hGzipProcess);
 731                 }
 732
 733         }
 734
 735         function pgsqlRunScript($sScript)
 736         {
 737                 // Convert database DSN to psql parameters
 738                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 739                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 740                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 741                 $aDescriptors = array(
 742                         0 => array('pipe', 'r'),
 743                         1 => STDOUT,
 744                         2 => STDERR
 745                 );
 746                 $ahPipes = null;
 747                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
 748                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 749
 750                 while(strlen($sScript))
 751                 {
 752                         $written = fwrite($ahPipes[0], $sScript);
 753                         $sScript = substr($sScript, $written);
 754                 }
 755                 fclose($ahPipes[0]);
 756                 proc_close($hProcess);
 757         }
 758
 759         function pgsqlRunRestoreData($sDumpFile)
 760         {
 761                 // Convert database DSN to psql parameters
 762                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 763                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 764                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
 765
 766                 $aDescriptors = array(
 767                         0 => array('pipe', 'r'),
 768                         1 => array('pipe', 'w'),
 769                         2 => array('file', '/dev/null', 'a')
 770                 );
 771                 $ahPipes = null;
 772                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 773                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 774
 775                 fclose($ahPipes[0]);
 776
 777                 // TODO: error checking
 778                 while(!feof($ahPipes[1]))
 779                 {
 780                         echo fread($ahPipes[1], 4096);
 781                 }
 782                 fclose($ahPipes[1]);
 783
 784                 proc_close($hProcess);
 785         }
 786
 787         function pgsqlRunDropAndRestore($sDumpFile)
 788         {
 789                 // Convert database DSN to psql parameters
 790                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 791                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 792                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
 793
 794                 $aDescriptors = array(
 795                         0 => array('pipe', 'r'),
 796                         1 => array('pipe', 'w'),
 797                         2 => array('file', '/dev/null', 'a')
 798                 );
 799                 $ahPipes = null;
 800                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 801                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 802
 803                 fclose($ahPipes[0]);
 804
 805                 // TODO: error checking
 806                 while(!feof($ahPipes[1]))
 807                 {
 808                         echo fread($ahPipes[1], 4096);
 809                 }
 810                 fclose($ahPipes[1]);
 811
 812                 proc_close($hProcess);
 813         }
 814
 815         function passthruCheckReturn($cmd)
 816         {
 817                 $result = -1;
 818                 passthru($cmd, $result);
 819                 if ($result != 0) fail('Error executing external command: '.$cmd);
 820         }