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