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