3 namespace Nominatim\Setup;
5 require_once(CONST_LibDir.'/Shell.php');
13 protected $sIgnoreErrors;
14 protected $bEnableDiffUpdates;
15 protected $bEnableDebugStatements;
17 protected $oDB = null;
18 protected $oNominatimCmd;
20 public function __construct(array $aCMDResult)
22 // by default, use all but one processor, but never more than 15.
23 $this->iInstances = isset($aCMDResult['threads'])
24 ? $aCMDResult['threads']
25 : (min(16, getProcessorCount()) - 1);
27 if ($this->iInstances < 1) {
28 $this->iInstances = 1;
29 warn('resetting threads to '.$this->iInstances);
32 // parse database string
33 $this->aDSNInfo = \Nominatim\DB::parseDSN(getSetting('DATABASE_DSN'));
34 if (!isset($this->aDSNInfo['port'])) {
35 $this->aDSNInfo['port'] = 5432;
38 // setting member variables based on command line options stored in $aCMDResult
39 $this->bQuiet = isset($aCMDResult['quiet']) && $aCMDResult['quiet'];
40 $this->bVerbose = $aCMDResult['verbose'];
42 //setting default values which are not set by the update.php array
43 if (isset($aCMDResult['ignore-errors'])) {
44 $this->sIgnoreErrors = $aCMDResult['ignore-errors'];
46 $this->sIgnoreErrors = false;
48 if (isset($aCMDResult['enable-debug-statements'])) {
49 $this->bEnableDebugStatements = $aCMDResult['enable-debug-statements'];
51 $this->bEnableDebugStatements = false;
53 if (isset($aCMDResult['enable-diff-updates'])) {
54 $this->bEnableDiffUpdates = $aCMDResult['enable-diff-updates'];
56 $this->bEnableDiffUpdates = false;
59 $this->bDrop = isset($aCMDResult['drop']) && $aCMDResult['drop'];
61 $this->oNominatimCmd = new \Nominatim\Shell(getSetting('NOMINATIM_TOOL'));
63 $this->oNominatimCmd->addParams('--quiet');
65 if ($this->bVerbose) {
66 $this->oNominatimCmd->addParams('--verbose');
70 public function importTigerData($sTigerPath)
72 info('Import Tiger data');
74 $aFilenames = glob($sTigerPath.'/*.sql');
75 info('Found '.count($aFilenames).' SQL files in path '.$sTigerPath);
76 if (empty($aFilenames)) {
77 warn('Tiger data import selected but no files found in path '.$sTigerPath);
80 $sTemplate = file_get_contents(CONST_SqlDir.'/tiger_import_start.sql');
81 $sTemplate = $this->replaceSqlPatterns($sTemplate);
83 $this->pgsqlRunScript($sTemplate, false);
85 $aDBInstances = array();
86 for ($i = 0; $i < $this->iInstances; $i++) {
87 // https://secure.php.net/manual/en/function.pg-connect.php
88 $DSN = getSetting('DATABASE_DSN');
89 $DSN = preg_replace('/^pgsql:/', '', $DSN);
90 $DSN = preg_replace('/;/', ' ', $DSN);
91 $aDBInstances[$i] = pg_connect($DSN, PGSQL_CONNECT_FORCE_NEW | PGSQL_CONNECT_ASYNC);
92 pg_ping($aDBInstances[$i]);
95 foreach ($aFilenames as $sFile) {
97 $hFile = fopen($sFile, 'r');
98 $sSQL = fgets($hFile, 100000);
101 for ($i = 0; $i < $this->iInstances; $i++) {
102 if (!pg_connection_busy($aDBInstances[$i])) {
103 while (pg_get_result($aDBInstances[$i]));
104 $sSQL = fgets($hFile, 100000);
106 if (!pg_send_query($aDBInstances[$i], $sSQL)) fail(pg_last_error($aDBInstances[$i]));
108 if ($iLines == 1000) {
121 for ($i = 0; $i < $this->iInstances; $i++) {
122 if (pg_connection_busy($aDBInstances[$i])) $bAnyBusy = true;
129 for ($i = 0; $i < $this->iInstances; $i++) {
130 pg_close($aDBInstances[$i]);
133 info('Creating indexes on Tiger data');
134 $sTemplate = file_get_contents(CONST_SqlDir.'/tiger_import_finish.sql');
135 $sTemplate = $this->replaceSqlPatterns($sTemplate);
137 $this->pgsqlRunScript($sTemplate, false);
140 public function calculatePostcodes($bCMDResultAll)
142 info('Calculate Postcodes');
143 $this->pgsqlRunScriptFile(CONST_SqlDir.'/postcode_tables.sql');
145 $sPostcodeFilename = CONST_InstallDir.'/gb_postcode_data.sql.gz';
146 if (file_exists($sPostcodeFilename)) {
147 $this->pgsqlRunScriptFile($sPostcodeFilename);
149 warn('optional external GB postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
152 $sPostcodeFilename = CONST_InstallDir.'/us_postcode_data.sql.gz';
153 if (file_exists($sPostcodeFilename)) {
154 $this->pgsqlRunScriptFile($sPostcodeFilename);
156 warn('optional external US postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
160 $this->db()->exec('TRUNCATE location_postcode');
162 $sSQL = 'INSERT INTO location_postcode';
163 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
164 $sSQL .= "SELECT nextval('seq_place'), 1, country_code,";
165 $sSQL .= " upper(trim (both ' ' from address->'postcode')) as pc,";
166 $sSQL .= ' ST_Centroid(ST_Collect(ST_Centroid(geometry)))';
167 $sSQL .= ' FROM placex';
168 $sSQL .= " WHERE address ? 'postcode' AND address->'postcode' NOT SIMILAR TO '%(,|;)%'";
169 $sSQL .= ' AND geometry IS NOT null';
170 $sSQL .= ' GROUP BY country_code, pc';
171 $this->db()->exec($sSQL);
173 // only add postcodes that are not yet available in OSM
174 $sSQL = 'INSERT INTO location_postcode';
175 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
176 $sSQL .= "SELECT nextval('seq_place'), 1, 'us', postcode,";
177 $sSQL .= ' ST_SetSRID(ST_Point(x,y),4326)';
178 $sSQL .= ' FROM us_postcode WHERE postcode NOT IN';
179 $sSQL .= ' (SELECT postcode FROM location_postcode';
180 $sSQL .= " WHERE country_code = 'us')";
181 $this->db()->exec($sSQL);
183 // add missing postcodes for GB (if available)
184 $sSQL = 'INSERT INTO location_postcode';
185 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
186 $sSQL .= "SELECT nextval('seq_place'), 1, 'gb', postcode, geometry";
187 $sSQL .= ' FROM gb_postcode WHERE postcode NOT IN';
188 $sSQL .= ' (SELECT postcode FROM location_postcode';
189 $sSQL .= " WHERE country_code = 'gb')";
190 $this->db()->exec($sSQL);
192 if (!$bCMDResultAll) {
193 $sSQL = "DELETE FROM word WHERE class='place' and type='postcode'";
194 $sSQL .= 'and word NOT IN (SELECT postcode FROM location_postcode)';
195 $this->db()->exec($sSQL);
198 $sSQL = 'SELECT count(getorcreate_postcode_id(v)) FROM ';
199 $sSQL .= '(SELECT distinct(postcode) as v FROM location_postcode) p';
200 $this->db()->exec($sSQL);
203 public function createCountryNames()
205 info('Create search index for default country names');
207 $this->pgsqlRunScript("select getorcreate_country(make_standard_name('uk'), 'gb')");
208 $this->pgsqlRunScript("select getorcreate_country(make_standard_name('united states'), 'us')");
209 $this->pgsqlRunScript('select count(*) from (select getorcreate_country(make_standard_name(country_code), country_code) from country_name where country_code is not null) as x');
210 $this->pgsqlRunScript("select count(*) from (select getorcreate_country(make_standard_name(name->'name'), country_code) from country_name where name ? 'name') as x");
211 $sSQL = 'select count(*) from (select getorcreate_country(make_standard_name(v),'
212 .'country_code) from (select country_code, skeys(name) as k, svals(name) as v from country_name) x where k ';
213 $sLanguages = getSetting('LANGUAGES');
217 foreach (explode(',', $sLanguages) as $sLang) {
218 $sSQL .= $sDelim."'name:$sLang'";
223 // all include all simple name tags
224 $sSQL .= "like 'name:%'";
227 $this->pgsqlRunScript($sSQL);
231 * Return the connection to the database.
233 * @return Database object.
235 * Creates a new connection if none exists yet. Otherwise reuses the
236 * already established connection.
238 private function db()
240 if (is_null($this->oDB)) {
241 $this->oDB = new \Nominatim\DB();
242 $this->oDB->connect();
248 private function pgsqlRunScript($sScript, $bfatal = true)
258 public function createSqlFunctions()
260 $oCmd = (clone($this->oNominatimCmd))
261 ->addParams('refresh', '--functions');
263 if (!$this->bEnableDiffUpdates) {
264 $oCmd->addParams('--no-diff-updates');
267 if ($this->bEnableDebugStatements) {
268 $oCmd->addParams('--enable-debug-statements');
271 $oCmd->run(!$this->sIgnoreErrors);
274 private function pgsqlRunScriptFile($sFilename)
276 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
278 $oCmd = (new \Nominatim\Shell('psql'))
279 ->addParams('--port', $this->aDSNInfo['port'])
280 ->addParams('--dbname', $this->aDSNInfo['database']);
282 if (!$this->bVerbose) {
283 $oCmd->addParams('--quiet');
285 if (isset($this->aDSNInfo['hostspec'])) {
286 $oCmd->addParams('--host', $this->aDSNInfo['hostspec']);
288 if (isset($this->aDSNInfo['username'])) {
289 $oCmd->addParams('--username', $this->aDSNInfo['username']);
291 if (isset($this->aDSNInfo['password'])) {
292 $oCmd->addEnvPair('PGPASSWORD', $this->aDSNInfo['password']);
295 if (preg_match('/\\.gz$/', $sFilename)) {
296 $aDescriptors = array(
297 0 => array('pipe', 'r'),
298 1 => array('pipe', 'w'),
299 2 => array('file', '/dev/null', 'a')
301 $oZcatCmd = new \Nominatim\Shell('zcat', $sFilename);
303 $hGzipProcess = proc_open($oZcatCmd->escapedCmd(), $aDescriptors, $ahGzipPipes);
304 if (!is_resource($hGzipProcess)) fail('unable to start zcat');
305 $aReadPipe = $ahGzipPipes[1];
306 fclose($ahGzipPipes[0]);
308 $oCmd->addParams('--file', $sFilename);
309 $aReadPipe = array('pipe', 'r');
311 $aDescriptors = array(
313 1 => array('pipe', 'w'),
314 2 => array('file', '/dev/null', 'a')
318 $hProcess = proc_open($oCmd->escapedCmd(), $aDescriptors, $ahPipes, null, $oCmd->aEnv);
319 if (!is_resource($hProcess)) fail('unable to start pgsql');
320 // TODO: error checking
321 while (!feof($ahPipes[1])) {
322 echo fread($ahPipes[1], 4096);
325 $iReturn = proc_close($hProcess);
327 fail("pgsql returned with error code ($iReturn)");
330 fclose($ahGzipPipes[1]);
331 proc_close($hGzipProcess);
335 private function replaceSqlPatterns($sSql)
337 $sSql = str_replace('{www-user}', getSetting('DATABASE_WEBUSER'), $sSql);
340 '{ts:address-data}' => getSetting('TABLESPACE_ADDRESS_DATA'),
341 '{ts:address-index}' => getSetting('TABLESPACE_ADDRESS_INDEX'),
342 '{ts:search-data}' => getSetting('TABLESPACE_SEARCH_DATA'),
343 '{ts:search-index}' => getSetting('TABLESPACE_SEARCH_INDEX'),
344 '{ts:aux-data}' => getSetting('TABLESPACE_AUX_DATA'),
345 '{ts:aux-index}' => getSetting('TABLESPACE_AUX_INDEX')
348 foreach ($aPatterns as $sPattern => $sTablespace) {
350 $sSql = str_replace($sPattern, 'TABLESPACE "'.$sTablespace.'"', $sSql);
352 $sSql = str_replace($sPattern, '', $sSql);