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