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