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-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
  23                 array('create-partitions', '', 0, 1, 0, 0, 'bool', 'Create required partition tables and triggers'),
  24                 array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'),
  25                 array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data'),
  26         );
  27         getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true);
  28
  29         $bDidSomething = false;
  30
  31         // This is a pretty hard core defult - the number of processors in the box - 1
  32         $iInstances = isset($aCMDResult['threads'])?$aCMDResult['threads']:(getProcessorCount()-1);
  33         if ($iInstances < 1)
  34         {
  35                 $iInstances = 1;
  36                 echo "WARNING: resetting threads to $iInstances\n";
  37         }
  38         if ($iInstances > getProcessorCount())
  39         {
  40                 $iInstances = getProcessorCount();
  41                 echo "WARNING: resetting threads to $iInstances\n";
  42         }
  43
  44         if ($aCMDResult['create-db'] || $aCMDResult['all'])
  45         {
  46                 $bDidSomething = true;
  47                 $oDB =& DB::connect(CONST_Database_DSN, false);
  48                 if (!PEAR::isError($oDB))
  49                 {
  50                         fail('database already exists');
  51                 }
  52                 passthru('createdb nominatim');
  53         }
  54
  55         if ($aCMDResult['create-db'] || $aCMDResult['all'])
  56         {
  57                 $bDidSomething = true;
  58                 // TODO: path detection, detection memory, etc.
  59
  60                 $oDB =& getDB();
  61                 passthru('createlang plpgsql nominatim');
  62                 pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/_int.sql');
  63                 pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/hstore.sql');
  64                 pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
  65                 pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
  66                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
  67                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturaleathdata.sql');
  68                 pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql');
  69                 pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode.sql');
  70                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_statecounty.sql');
  71                 pgsqlRunScriptFile(CONST_BasePath.'/data/us_state.sql');
  72                 pgsqlRunScriptFile(CONST_BasePath.'/data/worldboundaries.sql');
  73         }
  74
  75         if ($aCMDResult['import-data'] || $aCMDResult['all'])
  76         {
  77                 $bDidSomething = true;
  78                 passthru(CONST_BasePath.'/osm2pgsql/osm2pgsql -lsc -O gazetteer -C 10000 --hstore -d nominatim '.$aCMDResult['osm-file']);
  79         }
  80
  81         if ($aCMDResult['create-functions'] || $aCMDResult['all'])
  82         {
  83                 $bDidSomething = true;
  84                 if (!file_exists(CONST_BasePath.'/module/nominatim.so')) fail("nominatim module not built");
  85                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
  86                 $sTemplate = str_replace('{modulepath}',CONST_BasePath.'/module', $sTemplate);
  87                 pgsqlRunScript($sTemplate);
  88         }
  89
  90         if ($aCMDResult['create-tables'] || $aCMDResult['all'])
  91         {
  92                 $bDidSomething = true;
  93                 pgsqlRunScriptFile(CONST_BasePath.'/sql/tables.sql');
  94
  95                 // re-run the functions
  96                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
  97                 $sTemplate = str_replace('{modulepath}',CONST_BasePath.'/module', $sTemplate);
  98                 pgsqlRunScript($sTemplate);
  99         }
 100
 101         if ($aCMDResult['create-partitions'] || $aCMDResult['all'])
 102         {
 103                 $bDidSomething = true;
 104                 $oDB =& getDB();
 105                 $sSQL = 'select partition from country_name order by country_code';
 106                 $aPartitions = $oDB->getCol($sSQL);
 107                 if (PEAR::isError($aPartitions))
 108                 {
 109                         fail($aPartitions->getMessage());
 110                 }
 111                 $aPartitions[] = 0;
 112
 113                 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partitions.src.sql');
 114                 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
 115                 foreach($aMatches as $aMatch)
 116                 {
 117                         $sResult = '';
 118                         foreach($aPartitions as $sPartitionName)
 119                         {
 120                                 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
 121                         }
 122                         $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
 123                 }
 124                 pgsqlRunScript($sTemplate);
 125         }
 126
 127         if ($aCMDResult['load-data'] || $aCMDResult['all'])
 128         {
 129                 $bDidSomething = true;
 130
 131                 $oDB =& getDB();
 132                 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
 133                 echo '.';
 134                 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
 135                 echo '.';
 136                 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
 137                 echo '.';
 138                 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
 139                 echo '.';
 140                 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
 141                 echo '.';
 142                 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
 143                 echo '.';
 144                 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
 145                 echo '.';
 146                 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
 147                 echo '.';
 148                 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
 149                 echo '.';
 150
 151                 $aDBInstances = array();
 152                 for($i = 0; $i < $iInstances; $i++)
 153                 {
 154                         $aDBInstances[$i] =& getDB(true);
 155                         $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
 156                         $sSQL .= 'housenumber, street, isin, postcode, country_code, extratags, ';
 157                         $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
 158                         if ($aCMDResult['verbose']) echo "$sSQL\n";
 159                         if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 160                 }
 161                 $bAnyBusy = true;
 162                 while($bAnyBusy)
 163                 {
 164                         $bAnyBusy = false;
 165                         for($i = 0; $i < $iInstances; $i++)
 166                         {
 167                                 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 168                         }
 169                         sleep(1);
 170                         echo '.';
 171                 }
 172                 echo "\n";
 173         }
 174
 175         if ($aCMDResult['import-tiger-data'] || $aCMDResult['all'])
 176         {
 177                 $bDidSomething = true;
 178
 179                 $aDBInstances = array();
 180                 for($i = 0; $i < $iInstances; $i++)
 181                 {
 182                         $aDBInstances[$i] =& getDB(true);
 183                 }
 184
 185                 foreach(glob(CONST_BasePath.'/data/tiger2009/*.sql') as $sFile)
 186                 {
 187                         echo $sFile.': ';
 188                         $hFile = fopen($sFile, "r");
 189                         $sSQL = fgets($hFile, 100000);
 190                         $iLines = 0;
 191
 192                         while(true)
 193                         {
 194                                 for($i = 0; $i < $iInstances; $i++)
 195                                 {
 196                                         if (!pg_connection_busy($aDBInstances[$i]->connection))
 197                                         {
 198                                                 while(pg_get_result($aDBInstances[$i]->connection));
 199                                                 $sSQL = fgets($hFile, 100000);
 200                                                 if (!$sSQL) break 2;
 201                                                 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
 202                                                 $iLines++;
 203                                                 if ($iLines == 1000)
 204                                                 {
 205                                                         echo ".";
 206                                                         $iLines = 0;
 207                                                 }
 208                                         }
 209                                 }
 210                                 usleep(10);
 211                         }
 212
 213                         fclose($hFile);
 214
 215                         $bAnyBusy = true;
 216                         while($bAnyBusy)
 217                         {
 218                                 $bAnyBusy = false;
 219                                 for($i = 0; $i < $iInstances; $i++)
 220                                 {
 221                                         if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
 222                                 }
 223                                 usleep(10);
 224                         }
 225                         echo "\n";
 226                 }
 227         }
 228
 229         if (!$bDidSomething)
 230         {
 231                 showUsage($aCMDOptions, true);
 232         }
 233
 234         function pgsqlRunScriptFile($sFilename)
 235         {
 236                 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
 237
 238                 // Convert database DSN to psql paramaters
 239                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 240                 $sCMD = 'psql -f '.$sFilename.' '.$aDSNInfo['database'];
 241
 242                 $aDescriptors = array(
 243                         0 => array('pipe', 'r'),
 244                         1 => array('pipe', 'w'),
 245                         2 => array('file', '/dev/null', 'a')
 246                 );
 247                 $ahPipes = null;
 248                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 249                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 250
 251                 fclose($ahPipes[0]);
 252
 253                 // TODO: error checking
 254                 while(!feof($ahPipes[1]))
 255                 {
 256                         echo fread($ahPipes[1], 4096);
 257                 }
 258                 fclose($ahPipes[1]);
 259
 260                 proc_close($hProcess);
 261         }
 262
 263         function pgsqlRunScript($sScript)
 264         {
 265                 // Convert database DSN to psql paramaters
 266                 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
 267                 $sCMD = 'psql '.$aDSNInfo['database'];
 268
 269                 $aDescriptors = array(
 270                         0 => array('pipe', 'r'),
 271                         1 => array('pipe', 'w'),
 272                         2 => array('file', '/dev/null', 'a')
 273                 );
 274                 $ahPipes = null;
 275                 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
 276                 if (!is_resource($hProcess)) fail('unable to start pgsql');
 277
 278                 fwrite($ahPipes[0], $sScript);
 279                 fclose($ahPipes[0]);
 280
 281                 // TODO: error checking
 282                 while(!feof($ahPipes[1]))
 283                 {
 284                         echo fread($ahPipes[1], 4096);
 285                 }
 286                 fclose($ahPipes[1]);
 287
 288                 proc_close($hProcess);
 289         }