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 .= ' --tablespace-slim-index ssd --tablespace-main-index ssd --tablespace-main-data ssd --tablespace-slim-data data';
 186                 $osm2pgsql .= ' -lsc -O gazetteer --hstore';
 187                 $osm2pgsql .= ' -C 18000';
 188                 $osm2pgsql .= ' -P '.$aDSNInfo['port'];
 189                 $osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
 190                 passthruCheckReturn($osm2pgsql);
 191
 192                 $oDB =& getDB();
 193                 $x = $oDB->getRow('select * from place limit 1');
 194                 if (PEAR::isError($x)) {
 195                         fail($x->getMessage());
 196                 }
 197                 if (!$x) fail('No Data');
 198         }
 199
 200         if ($aCMDResult['create-functions'] || $aCMDResult['all'])
 201         {
 202                 echo "Functions\n";
 203                 $bDidSomething = true;
 204                 if (!file_exists(CONST_BasePath.'/module/nominatim.so')) fail("nominatim module not built");
 205                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 206                 $sTemplate = str_replace('{modulepath}', CONST_BasePath.'/module', $sTemplate);
 207                 if ($aCMDResult['enable-diff-updates']) $sTemplate = str_replace('RETURN NEW; -- @DIFFUPDATES@', '--', $sTemplate);
 208                 if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
 209                 if (CONST_Limit_Reindexing) $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
 210                 pgsqlRunScript($sTemplate);
 211         }
 212
 213         if ($aCMDResult['create-minimal-tables'])
 214         {
 215                 echo "Minimal Tables\n";
 216                 $bDidSomething = true;
 217                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables-minimal.sql');
 218
 219                 $sScript = '';
 220
 221                 // Backstop the import process - easliest possible import id
 222                 $sScript .= "insert into import_npi_log values (18022);\n";
 223
 224                 $hFile = @fopen(CONST_BasePath.'/settings/partitionedtags.def', "r");
 225                 if (!$hFile) fail('unable to open list of partitions: '.CONST_BasePath.'/settings/partitionedtags.def');
 226
 227                 while (($sLine = fgets($hFile, 4096)) !== false && $sLine && substr($sLine,0,1) !='#')
 228                 {
 229                         list($sClass, $sType) = explode(' ', trim($sLine));
 230                         $sScript .= "create table place_classtype_".$sClass."_".$sType." as ";
 231                         $sScript .= "select place_id as place_id,geometry as centroid from placex limit 0;\n";
 232
 233                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_centroid ";
 234                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING GIST (centroid);\n";
 235
 236                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_place_id ";
 237                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING btree(place_id);\n";
 238                 }
 239                 fclose($hFile);
 240                 pgsqlRunScript($sScript);
 241         }
 242
 243         if ($aCMDResult['create-tables'] || $aCMDResult['all'])
 244         {
 245                 echo "Tables\n";
 246                 $bDidSomething = true;
 247                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables.sql');
 248
 249                 // re-run the functions
 250                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 251                 $sTemplate = str_replace('{modulepath}',CONST_BasePath.'/module', $sTemplate);
 252                 pgsqlRunScript($sTemplate);
 253         }
 254
 255         if ($aCMDResult['create-partition-tables'] || $aCMDResult['all'])
 256         {
 257                 echo "Partition Tables\n";
 258                 $bDidSomething = true;
 259                 $oDB =& getDB();
 260                 $sSQL = 'select distinct partition from country_name';
 261                 $aPartitions = $oDB->getCol($sSQL);
 262                 if (PEAR::isError($aPartitions))
 263                 {
 264                         fail($aPartitions->getMessage());
 265                 }
 266                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 267
 268                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
 269                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 270                 foreach($aMatches as $aMatch)
 271                 {
 272                         $sResult = '';
 273                         foreach($aPartitions as $sPartitionName)
 274                         {
 275                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 276                         }
 277                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 278                 }
 279
 280                 pgsqlRunScript($sTemplate);
 281         }
 282
 283
 284         if ($aCMDResult['create-partition-functions'] || $aCMDResult['all'])
 285         {
 286                 echo "Partition Functions\n";
 287                 $bDidSomething = true;
 288                 $oDB =& getDB();
 289                 $sSQL = 'select distinct partition from country_name';
 290                 $aPartitions = $oDB->getCol($sSQL);
 291                 if (PEAR::isError($aPartitions))
 292                 {
 293                         fail($aPartitions->getMessage());
 294                 }
 295                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 296
 297                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
 298                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 299                 foreach($aMatches as $aMatch)
 300                 {
 301                         $sResult = '';
 302                         foreach($aPartitions as $sPartitionName)
 303                         {
 304                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 305                         }
 306                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 307                 }
 308
 309                 pgsqlRunScript($sTemplate);
 310         }
 311
 312         if ($aCMDResult['import-wikipedia-articles'] || $aCMDResult['all'])
 313         {
 314                 $bDidSomething = true;
 315                 $sWikiArticlesFile = CONST_BasePath.'/data/wikipedia_article.sql.bin';
 316                 $sWikiRedirectsFile = CONST_BasePath.'/data/wikipedia_redirect.sql.bin';
 317                 if (file_exists($sWikiArticlesFile))
 318                 {
 319                         echo "Importing wikipedia articles...";
 320                         pgsqlRunDropAndRestore($sWikiArticlesFile);
 321                         echo "...done\n";
 322                 }
 323                 else
 324                 {
 325                         echo "WARNING: wikipedia article dump file not found - places will have default importance\n";
 326                 }
 327                 if (file_exists($sWikiRedirectsFile))
 328                 {
 329                         echo "Importing wikipedia redirects...";
 330                         pgsqlRunDropAndRestore($sWikiRedirectsFile);
 331                         echo "...done\n";
 332                 }
 333                 else
 334                 {
 335                         echo "WARNING: wikipedia redirect dump file not found - some place importance values may be missing\n";
 336                 }
 337         }
 338
 339
 340         if ($aCMDResult['load-data'] || $aCMDResult['all'])
 341         {
 342                 echo "Drop old Data\n";
 343                 $bDidSomething = true;
 344
 345                 $oDB =& getDB();
 346                 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
 347                 echo '.';
 348                 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
 349                 echo '.';
 350                 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
 351                 echo '.';
 352                 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
 353                 echo '.';
 354                 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
 355                 echo '.';
 356                 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
 357                 echo '.';
 358                 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
 359                 echo '.';
 360                 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
 361                 echo '.';
 362                 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
 363                 echo '.';
 364
 365                 $sSQL = 'select distinct partition from country_name';
 366                 $aPartitions = $oDB->getCol($sSQL);
 367                 if (PEAR::isError($aPartitions))
 368                 {
 369                         fail($aPartitions->getMessage());
 370                 }
 371                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 372                 foreach($aPartitions as $sPartition)
 373                 {
 374                         if (!pg_query($oDB->connection, 'TRUNCATE location_road_'.$sPartition)) fail(pg_last_error($oDB->connection));
 375                         echo '.';
 376                 }
 377
 378                 // used by getorcreate_word_id to ignore frequent partial words
 379                 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));
 380                 echo ".\n";
 381
 382                 // pre-create the word list
 383                 if (!$aCMDResult['disable-token-precalc'])
 384                 {
 385                         echo "Loading word list\n";
 386                         pgsqlRunScriptFile(CONST_BasePath.'/data/words.sql');
 387                 }
 388
 389                 echo "Load Data\n";
 390                 $aDBInstances = array();
 391                 for($i = 0; $i < $iInstances; $i++)
 392                 {
 393                         $aDBInstances[$i] =& getDB(true);
 394                         $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
 395                         $sSQL .= 'housenumber, street, addr_place, isin, postcode, country_code, extratags, ';
 396                         $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
 397                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 398                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 399                 }
 400                 $bAnyBusy = true;
 401                 while($bAnyBusy)
 402                 {
 403                         $bAnyBusy = false;
 404                         for($i = 0; $i < $iInstances; $i++)
 405                         {
 406                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 407                         }
 408                         sleep(1);
 409                         echo '.';
 410                 }
 411                 echo "\n";
 412                 echo "Reanalysing database...\n";
 413                 pgsqlRunScript('ANALYSE');
 414         }
 415
 416         if ($aCMDResult['create-roads'])
 417         {
 418                 $bDidSomething = true;
 419
 420                 $oDB =& getDB();
 421                 $aDBInstances = array();
 422                 for($i = 0; $i < $iInstances; $i++)
 423                 {
 424                         $aDBInstances[$i] =& getDB(true);
 425                         if (!pg_query($aDBInstances[$i]->connection, 'set enable_bitmapscan = off')) fail(pg_last_error($oDB->connection));
 426                         $sSQL = 'select count(*) from (select insertLocationRoad(partition, place_id, calculated_country_code, geometry) from ';
 427                         $sSQL .= 'placex where osm_id % '.$iInstances.' = '.$i.' and rank_search between 26 and 27 and class = \'highway\') as x ';
 428                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 429                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 430                 }
 431                 $bAnyBusy = true;
 432                 while($bAnyBusy)
 433                 {
 434                         $bAnyBusy = false;
 435                         for($i = 0; $i < $iInstances; $i++)
 436                         {
 437                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 438                         }
 439                         sleep(1);
 440                         echo '.';
 441                 }
 442                 echo "\n";
 443         }
 444
 445         if ($aCMDResult['import-tiger-data'])
 446         {
 447                 $bDidSomething = true;
 448
 449                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_start.sql');
 450
 451                 $aDBInstances = array();
 452                 for($i = 0; $i < $iInstances; $i++)
 453                 {
 454                         $aDBInstances[$i] =& getDB(true);
 455                 }
 456
 457                 foreach(glob(CONST_BasePath.'/data/tiger2011/*.sql') as $sFile)
 458                 {
 459                         echo $sFile.': ';
 460                         $hFile = fopen($sFile, "r");
 461                         $sSQL = fgets($hFile, 100000);
 462                         $iLines = 0;
 463
 464                         while(true)
 465                         {
 466                                 for($i = 0; $i < $iInstances; $i++)
 467                                 {
 468                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
 469                                         {
 470                                                 while(pg_get_result($aDBInstances[$i]->connection));
 471                                                 $sSQL = fgets($hFile, 100000);
 472                                                 if (!$sSQL) break 2;
 473                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 474                                                 $iLines++;
 475                                                 if ($iLines == 1000)
 476                                                 {
 477                                                         echo ".";
 478                                                         $iLines = 0;
 479                                                 }
 480                                         }
 481                                 }
 482                                 usleep(10);
 483                         }
 484
 485                         fclose($hFile);
 486
 487                         $bAnyBusy = true;
 488                         while($bAnyBusy)
 489                         {
 490                                 $bAnyBusy = false;
 491                                 for($i = 0; $i < $iInstances; $i++)
 492                                 {
 493                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 494                                 }
 495                                 usleep(10);
 496                         }
 497                         echo "\n";
 498                 }
 499
 500                 echo "Creating indexes\n";
 501                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_finish.sql');
 502         }
 503
 504         if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
 505         {
 506                 $bDidSomething = true;
 507                 $oDB =& getDB();
 508                 if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
 509                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 510                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
 511                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
 512                 $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
 513                 $sSQL .= "from placex where postcode is not null and calculated_country_code not in ('ie') group by calculated_country_code,postcode) as x";
 514                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 515
 516                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 517                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
 518                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
 519                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 520         }
 521
 522         if ($aCMDResult['osmosis-init'] || $aCMDResult['all'])
 523         {
 524                 $bDidSomething = true;
 525                 $oDB =& getDB();
 526
 527                 if (!file_exists(CONST_Osmosis_Binary))
 528                 {
 529                         echo "Please download osmosis.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 530                         if (!$aCMDResult['all'])
 531                         {
 532                                 fail("osmosis not found in '".CONST_Osmosis_Binary."'");
 533                         }
 534                 }
 535                 else
 536                 {
 537                         if (file_exists(CONST_BasePath.'/settings/configuration.txt'))
 538                         {
 539                                 echo "settings/configuration.txt already exists\n";
 540                         }
 541                         else
 542                         {
 543                                 passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_BasePath.'/settings');
 544                                 // update osmosis configuration.txt with our settings
 545                                 passthru("sed -i 's!baseUrl=.*!baseUrl=".CONST_Replication_Url."!' ".CONST_BasePath.'/settings/configuration.txt');
 546                                 passthru("sed -i 's:maxInterval = .*:maxInterval = ".CONST_Replication_MaxInterval.":' ".CONST_BasePath.'/settings/configuration.txt');
 547                         }
 548
 549                         // Find the last node in the DB
 550                         $iLastOSMID = $oDB->getOne("select max(id) from planet_osm_nodes");
 551
 552                         // Lookup the timestamp that node was created (less 3 hours for margin for changsets to be closed)
 553                         $sLastNodeURL = 'http://www.openstreetmap.org/api/0.6/node/'.$iLastOSMID."/1";
 554                         $sLastNodeXML = file_get_contents($sLastNodeURL);
 555                         preg_match('#timestamp="(([0-9]{4})-([0-9]{2})-([0-9]{2})T([0-9]{2}):([0-9]{2}):([0-9]{2})Z)"#', $sLastNodeXML, $aLastNodeDate);
 556                         $iLastNodeTimestamp = strtotime($aLastNodeDate[1]) - (3*60*60);
 557
 558                         // Search for the correct state file - uses file timestamps so need to sort by date descending
 559                         $sRepURL = CONST_Replication_Url."/";
 560                         $sRep = file_get_contents($sRepURL."?C=M;O=D");
 561                         // download.geofabrik.de:    <a href="000/">000/</a></td><td align="right">26-Feb-2013 11:53  </td>
 562                         // planet.openstreetmap.org: <a href="273/">273/</a>                    22-Mar-2013 07:41    -
 563                         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);
 564                         $aPrevRepMatch = false;
 565                         foreach($aRepMatches as $aRepMatch)
 566                         {
 567                                 if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 568                                 $aPrevRepMatch = $aRepMatch;
 569                         }
 570                         if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 571
 572                         $sRepURL .= $aRepMatch[1];
 573                         $sRep = file_get_contents($sRepURL."?C=M;O=D");
 574                         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);
 575                         $aPrevRepMatch = false;
 576                         foreach($aRepMatches as $aRepMatch)
 577                         {
 578                                 if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 579                                 $aPrevRepMatch = $aRepMatch;
 580                         }
 581                         if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 582
 583                         $sRepURL .= $aRepMatch[1];
 584                         $sRep = file_get_contents($sRepURL."?C=M;O=D");
 585                         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);
 586                         $aPrevRepMatch = false;
 587                         foreach($aRepMatches as $aRepMatch)
 588                         {
 589                                 if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 590                                 $aPrevRepMatch = $aRepMatch;
 591                         }
 592                         if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 593
 594                         $sRepURL .= $aRepMatch[1].'.state.txt';
 595                         echo "Getting state file: $sRepURL\n";
 596                         $sStateFile = file_get_contents($sRepURL);
 597                         if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
 598                         file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
 599                         echo "Updating DB status\n";
 600                         pg_query($oDB->connection, 'TRUNCATE import_status');
 601                         $sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
 602                         pg_query($oDB->connection, $sSQL);
 603                 }
 604         }
 605
 606         if ($aCMDResult['index'] || $aCMDResult['all'])
 607         {
 608                 $bDidSomething = true;
 609                 $sOutputFile = '';
 610                 if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output'];
 611                 $sBaseCmd = CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
 612                 passthruCheckReturn($sBaseCmd.' -R 4');
 613                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 614                 passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
 615                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 616                 passthruCheckReturn($sBaseCmd.' -r 26');
 617         }
 618
 619         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
 620         {
 621                 echo "Search indices\n";
 622                 $bDidSomething = true;
 623                 $oDB =& getDB();
 624                 $sSQL = 'select distinct partition from country_name';
 625                 $aPartitions = $oDB->getCol($sSQL);
 626                 if (PEAR::isError($aPartitions))
 627                 {
 628                         fail($aPartitions->getMessage());
 629                 }
 630                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 631
 632                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
 633                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 634                 foreach($aMatches as $aMatch)
 635                 {
 636                         $sResult = '';
 637                         foreach($aPartitions as $sPartitionName)
 638                         {
 639                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 640                         }
 641                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 642                 }
 643
 644                 pgsqlRunScript($sTemplate);
 645         }
 646
 647         if (isset($aCMDResult['create-website']))
 648         {
 649                 $bDidSomething = true;
 650                 $sTargetDir = $aCMDResult['create-website'];
 651                 if (!is_dir($sTargetDir))
 652                 {
 653                         echo "You must create the website directory before calling this function.\n";
 654                         fail("Target directory does not exist.");
 655                 }
 656
 657                 @symlink(CONST_BasePath.'/website/details.php', $sTargetDir.'/details.php');
 658                 @symlink(CONST_BasePath.'/website/reverse.php', $sTargetDir.'/reverse.php');
 659                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/search.php');
 660                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/index.php');
 661                 @symlink(CONST_BasePath.'/website/deletable.php', $sTargetDir.'/deletable.php');
 662                 @symlink(CONST_BasePath.'/website/polygons.php', $sTargetDir.'/polygons.php');
 663                 @symlink(CONST_BasePath.'/website/status.php', $sTargetDir.'/status.php');
 664                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
 665                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
 666                 @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
 667                 echo "Symlinks created\n";
 668
 669                 $sTestFile = @file_get_contents(CONST_Website_BaseURL.'js/tiles.js');
 670                 if (!$sTestFile)
 671                 {
 672                         echo "\nWARNING: Unable to access the website at ".CONST_Website_BaseURL."\n";
 673                         echo "You may want to update settings/local.php with @define('CONST_Website_BaseURL', 'http://[HOST]/[PATH]/');\n";
 674                 }
 675         }
 676
 677         if (!$bDidSomething)
 678         {
 679                 showUsage($aCMDOptions, true);
 680         }
 681
 682         function pgsqlRunScriptFile($sFilename)
 683         {
 684                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
 685
 686                 // Convert database DSN to psql parameters
 687                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 688                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 689                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 690
 691                 $ahGzipPipes = null;
 692                 if (preg_match('/\\.gz$/', $sFilename))
 693                 {
 694                         $aDescriptors = array(
 695                                 0 => array('pipe', 'r'),
 696                                 1 => array('pipe', 'w'),
 697                                 2 => array('file', '/dev/null', 'a')
 698                         );
 699                         $hGzipProcess = proc_open('zcat '.$sFilename, $aDescriptors, $ahGzipPipes);
 700                         if (!is_resource($hGzipProcess)) fail('unable to start zcat');
 701                         $aReadPipe = $ahGzipPipes[1];
 702                         fclose($ahGzipPipes[0]);
 703                 }
 704                 else
 705                 {
 706                         $sCMD .= ' -f '.$sFilename;
 707                         $aReadPipe = array('pipe', 'r');
 708                 }
 709
 710                 $aDescriptors = array(
 711                         0 => $aReadPipe,
 712                         1 => array('pipe', 'w'),
 713                         2 => array('file', '/dev/null', 'a')
 714                 );
 715                 $ahPipes = null;
 716                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 717                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 718
 719
 720                 // TODO: error checking
 721                 while(!feof($ahPipes[1]))
 722                 {
 723                         echo fread($ahPipes[1], 4096);
 724                 }
 725                 fclose($ahPipes[1]);
 726
 727                 proc_close($hProcess);
 728                 if ($ahGzipPipes)
 729                 {
 730                         fclose($ahGzipPipes[1]);
 731                         proc_close($hGzipProcess);
 732                 }
 733
 734         }
 735
 736         function pgsqlRunScript($sScript)
 737         {
 738                 // Convert database DSN to psql parameters
 739                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 740                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 741                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 742                 $aDescriptors = array(
 743                         0 => array('pipe', 'r'),
 744                         1 => STDOUT,
 745                         2 => STDERR
 746                 );
 747                 $ahPipes = null;
 748                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
 749                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 750
 751                 while(strlen($sScript))
 752                 {
 753                         $written = fwrite($ahPipes[0], $sScript);
 754                         $sScript = substr($sScript, $written);
 755                 }
 756                 fclose($ahPipes[0]);
 757                 proc_close($hProcess);
 758         }
 759
 760         function pgsqlRunRestoreData($sDumpFile)
 761         {
 762                 // Convert database DSN to psql parameters
 763                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 764                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 765                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
 766
 767                 $aDescriptors = array(
 768                         0 => array('pipe', 'r'),
 769                         1 => array('pipe', 'w'),
 770                         2 => array('file', '/dev/null', 'a')
 771                 );
 772                 $ahPipes = null;
 773                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 774                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 775
 776                 fclose($ahPipes[0]);
 777
 778                 // TODO: error checking
 779                 while(!feof($ahPipes[1]))
 780                 {
 781                         echo fread($ahPipes[1], 4096);
 782                 }
 783                 fclose($ahPipes[1]);
 784
 785                 proc_close($hProcess);
 786         }
 787
 788         function pgsqlRunDropAndRestore($sDumpFile)
 789         {
 790                 // Convert database DSN to psql parameters
 791                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 792                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 793                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
 794
 795                 $aDescriptors = array(
 796                         0 => array('pipe', 'r'),
 797                         1 => array('pipe', 'w'),
 798                         2 => array('file', '/dev/null', 'a')
 799                 );
 800                 $ahPipes = null;
 801                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 802                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 803
 804                 fclose($ahPipes[0]);
 805
 806                 // TODO: error checking
 807                 while(!feof($ahPipes[1]))
 808                 {
 809                         echo fread($ahPipes[1], 4096);
 810                 }
 811                 fclose($ahPipes[1]);
 812
 813                 proc_close($hProcess);
 814         }
 815
 816         function passthruCheckReturn($cmd)
 817         {
 818                 $result = -1;
 819                 passthru($cmd, $result);
 820                 if ($result != 0) fail('Error executing external command: '.$cmd);
 821         }