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('create-functions', '', 0, 1, 0, 0, 'bool', 'Create functions'),
  22                 array('create-minimal-tables', '', 0, 1, 0, 0, 'bool', 'Create minimal main tables'),
  23                 array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
  24                 array('create-partitions', '', 0, 1, 0, 0, 'bool', 'Create required partition tables and triggers'),
  25                 array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'),
  26                 array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'),
  27                 array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
  28                 array('create-roads', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
  29                 array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'),
  30                 array('osmosis-init-date', '', 0, 1, 1, 1, 'string', 'Generate default osmosis configuration'),
  31                 array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'),
  32                 array('index-output', '', 0, 1, 1, 1, 'string', 'File to dump index information to'),
  33                 array('create-search-indices', '', 0, 1, 0, 0, 'bool', 'Create additional indices required for search and update'),
  34                 array('create-website', '', 0, 1, 1, 1, 'realpath', 'Create symlinks to setup web directory'),
  35         );
  36         getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true);
  37
  38         $bDidSomething = false;
  39
  40         // This is a pretty hard core defult - the number of processors in the box - 1
  41         $iInstances = isset($aCMDResult['threads'])?$aCMDResult['threads']:(getProcessorCount()-1);
  42         if ($iInstances < 1)
  43         {
  44                 $iInstances = 1;
  45                 echo "WARNING: resetting threads to $iInstances\n";
  46         }
  47         if ($iInstances > getProcessorCount())
  48         {
  49                 $iInstances = getProcessorCount();
  50                 echo "WARNING: resetting threads to $iInstances\n";
  51         }
  52         if (isset($aCMDResult['osm-file']) && !isset($aCMDResult['osmosis-init-date']))
  53         {
  54                 $sBaseFile = basename($aCMDResult['osm-file']);
  55                 if (preg_match('#^planet-([0-9]{2})([0-9]{2})([0-9]{2})[.]#', $sBaseFile, $aMatch))
  56                 {
  57                         $iTime = mktime(0, 0, 0, $aMatch[2], $aMatch[3], '20'.$aMatch[1]);
  58                         $iTime -= (60*60*24);
  59                         $aCMDResult['osmosis-init-date'] = date('Y-m-d', $iTime).'T22:00:00Z';
  60                 }
  61         }
  62         $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
  63         if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
  64
  65         if ($aCMDResult['create-db'] || $aCMDResult['all'])
  66         {
  67                 echo "Create DB\n";
  68                 $bDidSomething = true;
  69                 $oDB =& DB::connect(CONST_Database_DSN, false);
  70                 if (!PEAR::isError($oDB))
  71                 {
  72                         fail('database already exists ('.CONST_Database_DSN.')');
  73                 }
  74                 passthru('createdb '.$aDSNInfo['database']);
  75         }
  76
  77         if ($aCMDResult['create-db'] || $aCMDResult['all'])
  78         {
  79                 echo "Create DB (2)\n";
  80                 $bDidSomething = true;
  81                 // TODO: path detection, detection memory, etc.
  82
  83                 $oDB =& getDB();
  84                 passthru('createlang plpgsql '.$aDSNInfo['database']);
  85         $pgver = (float) CONST_Postgresql_Version;
  86                 if ($pgver < 9.1) {
  87                         pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/hstore.sql');
  88                 } else {
  89                         pgsqlRunScript('CREATE EXTENSION hstore');
  90                 }
  91                 pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
  92                 pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
  93                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
  94                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql');
  95                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql');
  96                 pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode.sql');
  97                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_statecounty.sql');
  98                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_state.sql');
  99                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
 100                 pgsqlRunScriptFile(CONST_BasePath.'/data/worldboundaries.sql');
 101         }
 102
 103         if ($aCMDResult['import-data'] || $aCMDResult['all'])
 104         {
 105                 echo "Import\n";
 106                 $bDidSomething = true;
 107
 108                 $osm2pgsql = CONST_Osm2pgsql_Binary;
 109                 if (!file_exists($osm2pgsql)) fail("please download and build osm2pgsql");
 110                 passthru($osm2pgsql.' --tablespace-slim-index data --tablespace-main-index ssd --tablespace-main-data data --tablespace-slim-data data -lsc -O gazetteer -C 12000 --hstore -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file']);
 111
 112                 $oDB =& getDB();
 113                 $x = $oDB->getRow('select * from place limit 1');
 114                 if (!$x || PEAR::isError($x)) fail('No Data');
 115         }
 116
 117         if ($aCMDResult['create-functions'] || $aCMDResult['all'])
 118         {
 119                 echo "Functions\n";
 120                 $bDidSomething = true;
 121                 if (!file_exists(CONST_BasePath.'/module/nominatim.so')) fail("nominatim module not built");
 122                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 123                 $sTemplate = str_replace('{modulepath}',CONST_BasePath.'/module', $sTemplate);
 124                 pgsqlRunScript($sTemplate);
 125         }
 126
 127         if ($aCMDResult['create-minimal-tables'])
 128         {
 129                 echo "Minimal Tables\n";
 130                 $bDidSomething = true;
 131                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables-minimal.sql');
 132
 133                 $sScript = '';
 134
 135                 // Backstop the import process - easliest possible import id
 136                 $sScript .= "insert into import_npi_log values (18022);\n";
 137
 138                 $hFile = @fopen(CONST_BasePath.'/settings/partitionedtags.def', "r");
 139                 if (!$hFile) fail('unable to open list of partitions: '.CONST_BasePath.'/settings/partitionedtags.def');
 140
 141                 while (($sLine = fgets($hFile, 4096)) !== false && $sLine && substr($sLine,0,1) !='#')
 142                 {
 143                         list($sClass, $sType) = explode(' ', trim($sLine));
 144                         $sScript .= "create table place_classtype_".$sClass."_".$sType." as ";
 145                         $sScript .= "select place_id as place_id,geometry as centroid from placex limit 0;\n";
 146
 147                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_centroid ";
 148                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING GIST (centroid);\n";
 149
 150                         $sScript .= "CREATE INDEX idx_place_classtype_".$sClass."_".$sType."_place_id ";
 151                         $sScript .= "ON place_classtype_".$sClass."_".$sType." USING btree(place_id);\n";
 152                 }
 153                 fclose($hFile);
 154                 pgsqlRunScript($sScript);
 155         }
 156
 157         if ($aCMDResult['create-tables'] || $aCMDResult['all'])
 158         {
 159                 echo "Tables\n";
 160                 $bDidSomething = true;
 161                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables.sql');
 162
 163                 // re-run the functions
 164                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
 165                 $sTemplate = str_replace('{modulepath}',CONST_BasePath.'/module', $sTemplate);
 166                 pgsqlRunScript($sTemplate);
 167         }
 168
 169         if ($aCMDResult['create-partitions'] || $aCMDResult['all'])
 170         {
 171                 echo "Partitions\n";
 172                 $bDidSomething = true;
 173                 $oDB =& getDB();
 174                 $sSQL = 'select partition from country_name order by country_code';
 175                 $aPartitions = $oDB->getCol($sSQL);
 176                 if (PEAR::isError($aPartitions))
 177                 {
 178                         fail($aPartitions->getMessage());
 179                 }
 180                 $aPartitions[] = 0;
 181
 182                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partitions.src.sql');
 183                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 184                 foreach($aMatches as $aMatch)
 185                 {
 186                         $sResult = '';
 187                         foreach($aPartitions as $sPartitionName)
 188                         {
 189                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 190                         }
 191                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 192                 }
 193
 194                 pgsqlRunScript($sTemplate);
 195         }
 196
 197         if ($aCMDResult['load-data'] || $aCMDResult['all'])
 198         {
 199                 echo "Load Data\n";
 200                 $bDidSomething = true;
 201
 202                 $oDB =& getDB();
 203                 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
 204                 echo '.';
 205                 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
 206                 echo '.';
 207                 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
 208                 echo '.';
 209                 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
 210                 echo '.';
 211                 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
 212                 echo '.';
 213                 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
 214                 echo '.';
 215                 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
 216                 echo '.';
 217                 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
 218                 echo '.';
 219                 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
 220                 echo '.';
 221
 222                 $aDBInstances = array();
 223                 for($i = 0; $i < $iInstances; $i++)
 224                 {
 225                         $aDBInstances[$i] =& getDB(true);
 226                         $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
 227                         $sSQL .= 'housenumber, street, isin, postcode, country_code, extratags, ';
 228                         $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
 229                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 230                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 231                 }
 232                 $bAnyBusy = true;
 233                 while($bAnyBusy)
 234                 {
 235                         $bAnyBusy = false;
 236                         for($i = 0; $i < $iInstances; $i++)
 237                         {
 238                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 239                         }
 240                         sleep(1);
 241                         echo '.';
 242                 }
 243                 echo "\n";
 244                 echo "Reanalysing database...\n";
 245                 pgsqlRunScript('ANALYSE');
 246         }
 247
 248         if ($aCMDResult['create-roads'])
 249         {
 250                 $bDidSomething = true;
 251
 252                 $oDB =& getDB();
 253                 $aDBInstances = array();
 254                 for($i = 0; $i < $iInstances; $i++)
 255                 {
 256                         $aDBInstances[$i] =& getDB(true);
 257                         if (!pg_query($aDBInstances[$i]->connection, 'set enable_bitmapscan = off')) fail(pg_last_error($oDB->connection));
 258                         $sSQL = 'select count(*) from (select insertLocationRoad(partition, place_id, country_code, geometry) from ';
 259                         $sSQL .= 'placex where osm_id % '.$iInstances.' = '.$i.' and rank_search between 26 and 27 and class = \'highway\') as x ';
 260                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 261                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 262                 }
 263                 $bAnyBusy = true;
 264                 while($bAnyBusy)
 265                 {
 266                         $bAnyBusy = false;
 267                         for($i = 0; $i < $iInstances; $i++)
 268                         {
 269                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 270                         }
 271                         sleep(1);
 272                         echo '.';
 273                 }
 274                 echo "\n";
 275         }
 276
 277         if ($aCMDResult['import-tiger-data'])
 278         {
 279                 $bDidSomething = true;
 280
 281                 $aDBInstances = array();
 282                 for($i = 0; $i < $iInstances; $i++)
 283                 {
 284                         $aDBInstances[$i] =& getDB(true);
 285                 }
 286
 287                 foreach(glob(CONST_BasePath.'/data/tiger2011/*.sql') as $sFile)
 288                 {
 289                         echo $sFile.': ';
 290                         $hFile = fopen($sFile, "r");
 291                         $sSQL = fgets($hFile, 100000);
 292                         $iLines = 0;
 293
 294                         while(true)
 295                         {
 296                                 for($i = 0; $i < $iInstances; $i++)
 297                                 {
 298                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
 299                                         {
 300                                                 while(pg_get_result($aDBInstances[$i]->connection));
 301                                                 $sSQL = fgets($hFile, 100000);
 302                                                 if (!$sSQL) break 2;
 303                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 304                                                 $iLines++;
 305                                                 if ($iLines == 1000)
 306                                                 {
 307                                                         echo ".";
 308                                                         $iLines = 0;
 309                                                 }
 310                                         }
 311                                 }
 312                                 usleep(10);
 313                         }
 314
 315                         fclose($hFile);
 316
 317                         $bAnyBusy = true;
 318                         while($bAnyBusy)
 319                         {
 320                                 $bAnyBusy = false;
 321                                 for($i = 0; $i < $iInstances; $i++)
 322                                 {
 323                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 324                                 }
 325                                 usleep(10);
 326                         }
 327                         echo "\n";
 328                 }
 329         }
 330
 331         if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
 332         {
 333                 $bDidSomething = true;
 334                 $oDB =& getDB();
 335                 if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
 336                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,country_code,geometry) ";
 337                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,country_code,";
 338                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select country_code,postcode,";
 339                 $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
 340                 $sSQL .= "from placex where postcode is not null group by country_code,postcode) as x";
 341                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 342
 343                 $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,country_code,geometry) ";
 344                 $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
 345                 $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
 346                 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 347         }
 348
 349         if ($aCMDResult['osmosis-init'] && isset($aCMDResult['osmosis-init-date']))
 350         {
 351                 $bDidSomething = true;
 352
 353                 if (!file_exists(CONST_Osmosis_Binary)) fail("please download osmosis");
 354                 if (file_exists(CONST_BasePath.'/settings/configuration.txt')) echo "settings/configuration.txt already exists\n";
 355                 else passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_BasePath.'/settings');
 356
 357                 $sDate = $aCMDResult['osmosis-init-date'];
 358                 $sURL = 'http://toolserver.org/~mazder/replicate-sequences/?'.$sDate;
 359                 echo "Getting state file: $sURL\n";
 360                 $sStateFile = file_get_contents($sURL);
 361                 if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
 362                 file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
 363         }
 364
 365         if ($aCMDResult['index'] || $aCMDResult['all'])
 366         {
 367                 $bDidSomething = true;
 368                 $sOutputFile = '';
 369                 if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output'];
 370                 $sBaseCmd = CONST_BasePath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -t '.$iInstances.$sOutputFile;
 371                 passthru($sBaseCmd.' -R 4');
 372                 pgsqlRunScript('ANALYSE');
 373                 passthru($sBaseCmd.' -r 5 -R 25');
 374                 pgsqlRunScript('ANALYSE');
 375                 passthru($sBaseCmd.' -r 26');
 376         }
 377
 378         if ($aCMDResult['create-search-indices'] || $aCMDResult['all'])
 379         {
 380                 echo "Search indices\n";
 381                 $bDidSomething = true;
 382                 $oDB =& getDB();
 383                 $sSQL = 'select partition from country_name order by country_code';
 384                 $aPartitions = $oDB->getCol($sSQL);
 385                 if (PEAR::isError($aPartitions))
 386                 {
 387                         fail($aPartitions->getMessage());
 388                 }
 389                 $aPartitions[] = 0;
 390
 391                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
 392                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 393                 foreach($aMatches as $aMatch)
 394                 {
 395                         $sResult = '';
 396                         foreach($aPartitions as $sPartitionName)
 397                         {
 398                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 399                         }
 400                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 401                 }
 402
 403                 pgsqlRunScript($sTemplate);
 404         }
 405
 406         if (isset($aCMDResult['create-website']))
 407         {
 408                 $bDidSomething = true;
 409                 $sTargetDir = $aCMDResult['create-website'];
 410                 if (!is_dir($sTargetDir)) fail('please specify a directory to setup');
 411                 @symlink(CONST_BasePath.'/website/details.php', $sTargetDir.'/details.php');
 412                 @symlink(CONST_BasePath.'/website/reverse.php', $sTargetDir.'/reverse.php');
 413                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/search.php');
 414                 @symlink(CONST_BasePath.'/website/search.php', $sTargetDir.'/index.php');
 415                 @symlink(CONST_BasePath.'/website/images', $sTargetDir.'/images');
 416                 @symlink(CONST_BasePath.'/website/js', $sTargetDir.'/js');
 417                 echo "Symlinks created\n";
 418         }
 419
 420         if (!$bDidSomething)
 421         {
 422                 showUsage($aCMDOptions, true);
 423         }
 424
 425         function pgsqlRunScriptFile($sFilename)
 426         {
 427                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
 428
 429                 // Convert database DSN to psql paramaters
 430                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 431                 $sCMD = 'psql -f '.$sFilename.' '.$aDSNInfo['database'];
 432
 433                 $aDescriptors = array(
 434                         0 => array('pipe', 'r'),
 435                         1 => array('pipe', 'w'),
 436                         2 => array('file', '/dev/null', 'a')
 437                 );
 438                 $ahPipes = null;
 439                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 440                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 441
 442                 fclose($ahPipes[0]);
 443
 444                 // TODO: error checking
 445                 while(!feof($ahPipes[1]))
 446                 {
 447                         echo fread($ahPipes[1], 4096);
 448                 }
 449                 fclose($ahPipes[1]);
 450
 451                 proc_close($hProcess);
 452         }
 453
 454         function pgsqlRunScript($sScript)
 455         {
 456                 // Convert database DSN to psql paramaters
 457                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 458                 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 459                 $sCMD = 'psql -p '.$aDSNInfo['port'].' '.$aDSNInfo['database'];
 460                 $aDescriptors = array(
 461                         0 => array('pipe', 'r'),
 462                         1 => STDOUT,
 463                         2 => STDERR
 464                 );
 465                 $ahPipes = null;
 466                 $hProcess = @proc_open($sCMD, $aDescriptors, $ahPipes);
 467                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 468
 469                 while(strlen($sScript))
 470                 {
 471                         $written = fwrite($ahPipes[0], $sScript);
 472                         $sScript = substr($sScript, $written);
 473                 }
 474                 fclose($ahPipes[0]);
 475                 proc_close($hProcess);
 476         }