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                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_start.sql');
 504
 505                 $aDBInstances = array();
 506                 for($i = 0; $i < $iInstances; $i++)
 507                 {
 508                         $aDBInstances[$i] =& getDB(true);
 509                 }
 510
 511                 foreach(glob(CONST_BasePath.'/data/tiger2011/*.sql') as $sFile)
 512                 {
 513                         echo $sFile.': ';
 514                         $hFile = fopen($sFile, "r");
 515                         $sSQL = fgets($hFile, 100000);
 516                         $iLines = 0;
 517
 518                         while(true)
 519                         {
 520                                 for($i = 0; $i < $iInstances; $i++)
 521                                 {
 522                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
 523                                         {
 524                                                 while(pg_get_result($aDBInstances[$i]->connection));
 525                                                 $sSQL = fgets($hFile, 100000);
 526                                                 if (!$sSQL) break 2;
 527                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 528                                                 $iLines++;
 529                                                 if ($iLines == 1000)
 530                                                 {
 531                                                         echo ".";
 532                                                         $iLines = 0;
 533                                                 }
 534                                         }
 535                                 }
 536                                 usleep(10);
 537                         }
 538
 539                         fclose($hFile);
 540
 541                         $bAnyBusy = true;
 542                         while($bAnyBusy)
 543                         {
 544                                 $bAnyBusy = false;
 545                                 for($i = 0; $i < $iInstances; $i++)
 546                                 {
 547                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 548                                 }
 549                                 usleep(10);
 550                         }
 551                         echo "\n";
 552                 }
 553
 554                 echo "Creating indexes\n";
 555                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_finish.sql');
 556         }
 557
 558         if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
 559         {
 560                 $bDidSomething = true;
 561                 $oDB =& getDB();
 562                 if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
 563                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 564                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,";
 565                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,";
 566                 $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
 567                 $sSQL .= "from placex where postcode is not null and calculated_country_code not in ('ie') group by calculated_country_code,postcode) as x";
 568                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 569
 570                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
 571                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
 572                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
 573                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 574         }
 575
 576         if ($aCMDResult['osmosis-init'] || $aCMDResult['all'])
 577         {
 578                 $bDidSomething = true;
 579                 $oDB =& getDB();
 580
 581                 if (!file_exists(CONST_Osmosis_Binary))
 582                 {
 583                         echo "Please download osmosis.\nIf it is already installed, check the path in your local settings (settings/local.php) file.\n";
 584                         if (!$aCMDResult['all'])
 585                         {
 586                                 fail("osmosis not found in '".CONST_Osmosis_Binary."'");
 587                         }
 588                 }
 589                 else
 590                 {
 591                         if (file_exists(CONST_BasePath.'/settings/configuration.txt'))
 592                         {
 593                                 echo "settings/configuration.txt already exists\n";
 594                         }
 595                         else
 596                         {
 597                                 passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_BasePath.'/settings');
 598                                 // update osmosis configuration.txt with our settings
 599                                 passthru("sed -i 's!baseUrl=.*!baseUrl=".CONST_Replication_Url."!' ".CONST_BasePath.'/settings/configuration.txt');
 600                                 passthru("sed -i 's:maxInterval = .*:maxInterval = ".CONST_Replication_MaxInterval.":' ".CONST_BasePath.'/settings/configuration.txt');
 601                         }
 602
 603                         // Find the last node in the DB
 604                         $iLastOSMID = $oDB->getOne("select max(id) from planet_osm_nodes");
 605
 606                         // Lookup the timestamp that node was created (less 3 hours for margin for changsets to be closed)
 607                         $sLastNodeURL = 'http://www.openstreetmap.org/api/0.6/node/'.$iLastOSMID."/1";
 608                         $sLastNodeXML = file_get_contents($sLastNodeURL);
 609                         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);
 610                         $iLastNodeTimestamp = strtotime($aLastNodeDate[1]) - (3*60*60);
 611
 612                         // Search for the correct state file - uses file timestamps so need to sort by date descending
 613                         $sRepURL = CONST_Replication_Url."/";
 614                         $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 615                         // download.geofabrik.de:    <a href="000/">000/</a></td><td align="right">26-Feb-2013 11:53  </td>
 616                         // planet.openstreetmap.org: <a href="273/">273/</a>                    2013-03-11 07:41    -
 617                         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);
 618                         if ($aRepMatches)
 619                         {
 620                                 $aPrevRepMatch = false;
 621                                 foreach($aRepMatches as $aRepMatch)
 622                                 {
 623                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 624                                         $aPrevRepMatch = $aRepMatch;
 625                                 }
 626                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 627
 628                                 $sRepURL .= $aRepMatch[1];
 629                                 $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 630                                 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);
 631                                 $aPrevRepMatch = false;
 632                                 foreach($aRepMatches as $aRepMatch)
 633                                 {
 634                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 635                                         $aPrevRepMatch = $aRepMatch;
 636                                 }
 637                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 638
 639                                 $sRepURL .= $aRepMatch[1];
 640                                 $sRep = file_get_contents($sRepURL."?C=M;O=D;F=1");
 641                                 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);
 642                                 $aPrevRepMatch = false;
 643                                 foreach($aRepMatches as $aRepMatch)
 644                                 {
 645                                         if (strtotime($aRepMatch[2]) < $iLastNodeTimestamp) break;
 646                                         $aPrevRepMatch = $aRepMatch;
 647                                 }
 648                                 if ($aPrevRepMatch) $aRepMatch = $aPrevRepMatch;
 649
 650                                 $sRepURL .= $aRepMatch[1].'.state.txt';
 651                                 echo "Getting state file: $sRepURL\n";
 652                                 $sStateFile = file_get_contents($sRepURL);
 653                                 if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
 654                                 file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
 655                                 echo "Updating DB status\n";
 656                                 pg_query($oDB->connection, 'TRUNCATE import_status');
 657                                 $sSQL = "INSERT INTO import_status VALUES('".$aRepMatch[2]."')";
 658                                 pg_query($oDB->connection, $sSQL);
 659                         }
 660                         else
 661                         {
 662                                 if (!$aCMDResult['all'])
 663                                 {
 664                                         fail("Cannot read state file directory.");
 665                                 }
 666                         }
 667                 }
 668         }
 669
 670         if ($aCMDResult['index'] || $aCMDResult['all'])
 671         {
 672                 $bDidSomething = true;
 673                 $sOutputFile = '';
 674                 if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output'];
 675                 $sBaseCmd = CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
 676                 passthruCheckReturn($sBaseCmd.' -R 4');
 677                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 678                 passthruCheckReturn($sBaseCmd.' -r 5 -R 25');
 679                 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript('ANALYSE');
 680                 passthruCheckReturn($sBaseCmd.' -r 26');
 681         }
 682
 683         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
 684         {
 685                 echo "Search indices\n";
 686                 $bDidSomething = true;
 687                 $oDB =& getDB();
 688                 $sSQL = 'select distinct partition from country_name';
 689                 $aPartitions = $oDB->getCol($sSQL);
 690                 if (PEAR::isError($aPartitions))
 691                 {
 692                         fail($aPartitions->getMessage());
 693                 }
 694                 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
 695
 696                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
 697                 $sTemplate = replace_tablespace('{ts:address-index}',
 698                                                 CONST_Tablespace_Address_Index, $sTemplate);
 699                 $sTemplate = replace_tablespace('{ts:search-index}',
 700                                                 CONST_Tablespace_Search_Index, $sTemplate);
 701                 $sTemplate = replace_tablespace('{ts:aux-index}',
 702                                                 CONST_Tablespace_Aux_Index, $sTemplate);
 703                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 704                 foreach($aMatches as $aMatch)
 705                 {
 706                         $sResult = '';
 707                         foreach($aPartitions as $sPartitionName)
 708                         {
 709                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 710                         }
 711                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 712                 }
 713
 714                 pgsqlRunScript($sTemplate);
 715         }
 716
 717         if (isset($aCMDResult['create-website']))
 718         {
 719                 $bDidSomething = true;
 720                 $sTargetDir = $aCMDResult['create-website'];
 721                 if (!is_dir($sTargetDir))
 722                 {
 723                         echo "You must create the website directory before calling this function.\n";
 724                         fail("Target directory does not exist.");
 725                 }
 726
 727                 @symlink(CONST_BasePath.'/website/details.php', $sTargetDir.'/details.php');
 728                 @symlink(CONST_BasePath.'/website/reverse.php', $sTargetDir.'/reverse.php');
 729                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/search.php');
 730                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/index.php');
 731                 @symlink(CONST_BasePath.'/website/deletable.php', $sTargetDir.'/deletable.php');
 732                 @symlink(CONST_BasePath.'/website/polygons.php', $sTargetDir.'/polygons.php');
 733                 @symlink(CONST_BasePath.'/website/status.php', $sTargetDir.'/status.php');
 734                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
 735                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
 736                 @symlink(CONST_BasePath.'/website/css', $sTargetDir.'/css');
 737                 echo "Symlinks created\n";
 738
 739                 $sTestFile = @file_get_contents(CONST_Website_BaseURL.'js/tiles.js');
 740                 if (!$sTestFile)
 741                 {
 742                         echo "\nWARNING: Unable to access the website at ".CONST_Website_BaseURL."\n";
 743                         echo "You may want to update settings/local.php with @define('CONST_Website_BaseURL', 'http://[HOST]/[PATH]/');\n";
 744                 }
 745         }
 746
 747         if (!$bDidSomething)
 748         {
 749                 showUsage($aCMDOptions, true);
 750         }
 751         else
 752         {
 753                 echo "Setup finished.\n";
 754         }
 755
 756         function pgsqlRunScriptFile($sFilename)
 757         {
 758                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
 759
 760                 // Convert database DSN to psql parameters
 761                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 762                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 763                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 764
 765                 $ahGzipPipes = null;
 766                 if (preg_match('/\\.gz$/', $sFilename))
 767                 {
 768                         $aDescriptors = array(
 769                                 0 => array('pipe', 'r'),
 770                                 1 => array('pipe', 'w'),
 771                                 2 => array('file', '/dev/null', 'a')
 772                         );
 773                         $hGzipProcess = proc_open('zcat '.$sFilename, $aDescriptors, $ahGzipPipes);
 774                         if (!is_resource($hGzipProcess)) fail('unable to start zcat');
 775                         $aReadPipe = $ahGzipPipes[1];
 776                         fclose($ahGzipPipes[0]);
 777                 }
 778                 else
 779                 {
 780                         $sCMD .= ' -f '.$sFilename;
 781                         $aReadPipe = array('pipe', 'r');
 782                 }
 783
 784                 $aDescriptors = array(
 785                         0 => $aReadPipe,
 786                         1 => array('pipe', 'w'),
 787                         2 => array('file', '/dev/null', 'a')
 788                 );
 789                 $ahPipes = null;
 790                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 791                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 792
 793
 794                 // TODO: error checking
 795                 while(!feof($ahPipes[1]))
 796                 {
 797                         echo fread($ahPipes[1], 4096);
 798                 }
 799                 fclose($ahPipes[1]);
 800
 801                 $iReturn = proc_close($hProcess);
 802                 if ($iReturn > 0)
 803                 {
 804                         fail("pgsql returned with error code ($iReturn)");
 805                 }
 806                 if ($ahGzipPipes)
 807                 {
 808                         fclose($ahGzipPipes[1]);
 809                         proc_close($hGzipProcess);
 810                 }
 811
 812         }
 813
 814         function pgsqlRunScript($sScript, $bfatal = true)
 815         {
 816                 global $aCMDResult;
 817                 // Convert database DSN to psql parameters
 818                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 819                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 820                 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
 821                 if ($bfatal && !$aCMDResult['ignore-errors'])
 822                         $sCMD .= ' -v ON_ERROR_STOP=1';
 823                 $aDescriptors = array(
 824                         0 => array('pipe', 'r'),
 825                         1 => STDOUT,
 826                         2 => STDERR
 827                 );
 828                 $ahPipes = null;
 829                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
 830                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 831
 832                 while(strlen($sScript))
 833                 {
 834                         $written = fwrite($ahPipes[0], $sScript);
 835                         if ($written <= 0) break;
 836                         $sScript = substr($sScript, $written);
 837                 }
 838                 fclose($ahPipes[0]);
 839                 $iReturn = proc_close($hProcess);
 840                 if ($bfatal && $iReturn > 0)
 841                 {
 842                         fail("pgsql returned with error code ($iReturn)");
 843                 }
 844         }
 845
 846         function pgsqlRunRestoreData($sDumpFile)
 847         {
 848                 // Convert database DSN to psql parameters
 849                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 850                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 851                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
 852
 853                 $aDescriptors = array(
 854                         0 => array('pipe', 'r'),
 855                         1 => array('pipe', 'w'),
 856                         2 => array('file', '/dev/null', 'a')
 857                 );
 858                 $ahPipes = null;
 859                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 860                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 861
 862                 fclose($ahPipes[0]);
 863
 864                 // TODO: error checking
 865                 while(!feof($ahPipes[1]))
 866                 {
 867                         echo fread($ahPipes[1], 4096);
 868                 }
 869                 fclose($ahPipes[1]);
 870
 871                 $iReturn = proc_close($hProcess);
 872         }
 873
 874         function pgsqlRunDropAndRestore($sDumpFile)
 875         {
 876                 // Convert database DSN to psql parameters
 877                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 878                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 879                 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
 880
 881                 $aDescriptors = array(
 882                         0 => array('pipe', 'r'),
 883                         1 => array('pipe', 'w'),
 884                         2 => array('file', '/dev/null', 'a')
 885                 );
 886                 $ahPipes = null;
 887                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 888                 if (!is_resource($hProcess)) fail('unable to start pg_restore');
 889
 890                 fclose($ahPipes[0]);
 891
 892                 // TODO: error checking
 893                 while(!feof($ahPipes[1]))
 894                 {
 895                         echo fread($ahPipes[1], 4096);
 896                 }
 897                 fclose($ahPipes[1]);
 898
 899                 $iReturn = proc_close($hProcess);
 900         }
 901
 902         function passthruCheckReturn($cmd)
 903         {
 904                 $result = -1;
 905                 passthru($cmd, $result);
 906                 if ($result != 0) fail('Error executing external command: '.$cmd);
 907         }
 908
 909         function replace_tablespace($sTemplate, $sTablespace, $sSql)
 910         {
 911                 if ($sTablespace)
 912                         $sSql = str_replace($sTemplate, 'TABLESPACE "'.$sTablespace.'"',
 913                                             $sSql);
 914                 else
 915                         $sSql = str_replace($sTemplate, '', $sSql);
 916
 917                 return $sSql;
 918         }
 919