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