3 function checkInFile($aCMDResult)
5 if ($aCMDResult['import-data'] || $aCMDResult['all']) {
6 if (!isset($aCMDResult['osm-file'])) {
7 fail('missing --osm-file for data import');
10 if (!file_exists($aCMDResult['osm-file'])) {
11 fail('the path supplied to --osm-file does not exist');
14 if (!is_readable($aCMDResult['osm-file'])) {
15 fail('osm-file "'.$aCMDResult['osm-file'].'" not readable');
20 function prepSystem($aCMDResult)
22 // by default, use all but one processor, but never more than 15.
23 $iInstances = isset($aCMDResult['threads'])
24 ? $aCMDResult['threads']
25 : (min(16, getProcessorCount()) - 1);
27 if ($iInstances < 1) {
29 warn("resetting threads to $iInstances");
32 // Assume we can steal all the cache memory in the box (unless told otherwise)
33 if (isset($aCMDResult['osm2pgsql-cache'])) {
34 $iCacheMemory = $aCMDResult['osm2pgsql-cache'];
36 $iCacheMemory = getCacheMemoryMB();
39 $sModulePath = CONST_Database_Module_Path;
40 info('module path: ' . $sModulePath);
42 return array($iCacheMemory,$iInstances);
45 function prepDB($aCMDResult)
47 $sModulePath = CONST_Database_Module_Path;
48 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
49 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
51 if ($aCMDResult['create-db'] || $aCMDResult['all']) {
53 $bDidSomething = true;
54 $oDB = DB::connect(CONST_Database_DSN, false);
55 if (!PEAR::isError($oDB)) {
56 fail('database already exists ('.CONST_Database_DSN.')');
59 $sCreateDBCmd = 'createdb -E UTF-8 -p '.$aDSNInfo['port'].' '.$aDSNInfo['database'];
60 if (isset($aDSNInfo['username']) && $aDSNInfo['username']) {
61 $sCreateDBCmd .= ' -U ' . $aDSNInfo['username'];
63 if (isset($aDSNInfo['hostspec']) && $aDSNInfo['hostspec']) {
64 $sCreateDBCmd .= ' -h ' . $aDSNInfo['hostspec'];
68 if (isset($aDSNInfo['password']) && $aDSNInfo['password']) {
69 $aProcEnv = array_merge(array('PGPASSWORD' => $aDSNInfo['password']), $_ENV);
72 $result = runWithEnv($sCreateDBCmd, $aProcEnv);
73 if ($result != 0) fail('Error executing external command: '.$sCreateDBCmd);
76 if ($aCMDResult['setup-db'] || $aCMDResult['all']) {
78 $bDidSomething = true;
82 $fPostgresVersion = getPostgresVersion($oDB);
83 echo 'Postgres version found: '.$fPostgresVersion."\n";
85 if ($fPostgresVersion < 9.1) {
86 fail('Minimum supported version of Postgresql is 9.1.');
89 pgsqlRunScript($aCMDResult, 'CREATE EXTENSION IF NOT EXISTS hstore');
90 pgsqlRunScript($aCMDResult, 'CREATE EXTENSION IF NOT EXISTS postgis');
92 // For extratags and namedetails the hstore_to_json converter is
93 // needed which is only available from Postgresql 9.3+. For older
94 // versions add a dummy function that returns nothing.
95 $iNumFunc = chksql($oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'"));
98 pgsqlRunScript($aCMDResult, "create function hstore_to_json(dummy hstore) returns text AS 'select null::text' language sql immutable");
99 warn('Postgresql is too old. extratags and namedetails API not available.');
102 $fPostgisVersion = getPostgisVersion($oDB);
103 echo 'Postgis version found: '.$fPostgisVersion."\n";
105 if ($fPostgisVersion < 2.1) {
106 // Functions were renamed in 2.1 and throw an annoying deprecation warning
107 pgsqlRunScript($aCMDResult, 'ALTER FUNCTION st_line_interpolate_point(geometry, double precision) RENAME TO ST_LineInterpolatePoint');
108 pgsqlRunScript($aCMDResult, 'ALTER FUNCTION ST_Line_Locate_Point(geometry, geometry) RENAME TO ST_LineLocatePoint');
110 if ($fPostgisVersion < 2.2) {
111 pgsqlRunScript($aCMDResult, 'ALTER FUNCTION ST_Distance_Spheroid(geometry, geometry, spheroid) RENAME TO ST_DistanceSpheroid');
114 $i = chksql($oDB->getOne("select count(*) from pg_user where usename = '".CONST_Database_Web_User."'"));
116 echo "\nERROR: Web user '".CONST_Database_Web_User."' does not exist. Create it with:\n";
117 echo "\n createuser ".CONST_Database_Web_User."\n\n";
121 if (!checkModulePresence()) {
122 fail('error loading nominatim.so module');
125 if (!file_exists(CONST_ExtraDataPath.'/country_osm_grid.sql.gz')) {
126 echo 'Error: you need to download the country_osm_grid first:';
127 echo "\n wget -O ".CONST_ExtraDataPath."/country_osm_grid.sql.gz https://www.nominatim.org/data/country_grid.sql.gz\n";
130 pgsqlRunScriptFile($aCMDResult, CONST_BasePath.'/data/country_name.sql');
131 pgsqlRunScriptFile($aCMDResult, CONST_BasePath.'/data/country_naturalearthdata.sql');
132 pgsqlRunScriptFile($aCMDResult, CONST_BasePath.'/data/country_osm_grid.sql.gz');
133 pgsqlRunScriptFile($aCMDResult, CONST_BasePath.'/data/gb_postcode_table.sql');
136 if (file_exists(CONST_BasePath.'/data/gb_postcode_data.sql.gz')) {
137 pgsqlRunScriptFile($aCMDResult, CONST_BasePath.'/data/gb_postcode_data.sql.gz');
139 warn('external UK postcode table not found.');
142 if (CONST_Use_Extra_US_Postcodes) {
143 pgsqlRunScriptFile($aCMDResult, CONST_BasePath.'/data/us_postcode.sql');
146 if ($aCMDResult['no-partitions']) {
147 pgsqlRunScript($aCMDResult, 'update country_name set partition = 0');
150 // the following will be needed by create_functions later but
151 // is only defined in the subsequently called T
152 // Create dummies here that will be overwritten by the proper
153 // versions in create-tables.
154 pgsqlRunScript($aCMDResult, 'CREATE TABLE IF NOT EXISTS place_boundingbox ()');
155 pgsqlRunScript($aCMDResult, 'CREATE TYPE wikipedia_article_match AS ()', false);
160 function importData($aCMDResult, $iCacheMemory, $aDSNInfo)
164 $osm2pgsql = CONST_Osm2pgsql_Binary;
165 if (!file_exists($osm2pgsql)) {
166 echo "Check CONST_Osm2pgsql_Binary in your local settings file.\n";
167 echo "Normally you should not need to set this manually.\n";
168 fail("osm2pgsql not found in '$osm2pgsql'");
171 if (!is_null(CONST_Osm2pgsql_Flatnode_File) && CONST_Osm2pgsql_Flatnode_File) {
172 $osm2pgsql .= ' --flat-nodes '.CONST_Osm2pgsql_Flatnode_File;
174 if (CONST_Tablespace_Osm2pgsql_Data)
175 $osm2pgsql .= ' --tablespace-slim-data '.CONST_Tablespace_Osm2pgsql_Data;
176 if (CONST_Tablespace_Osm2pgsql_Index)
177 $osm2pgsql .= ' --tablespace-slim-index '.CONST_Tablespace_Osm2pgsql_Index;
178 if (CONST_Tablespace_Place_Data)
179 $osm2pgsql .= ' --tablespace-main-data '.CONST_Tablespace_Place_Data;
180 if (CONST_Tablespace_Place_Index)
181 $osm2pgsql .= ' --tablespace-main-index '.CONST_Tablespace_Place_Index;
182 $osm2pgsql .= ' -lsc -O gazetteer --hstore --number-processes 1';
183 $osm2pgsql .= ' -C '.$iCacheMemory;
184 $osm2pgsql .= ' -P '.$aDSNInfo['port'];
185 if (isset($aDSNInfo['username']) && $aDSNInfo['username']) {
186 $osm2pgsql .= ' -U ' . $aDSNInfo['username'];
188 if (isset($aDSNInfo['hostspec']) && $aDSNInfo['hostspec']) {
189 $osm2pgsql .= ' -H ' . $aDSNInfo['hostspec'];
193 if (isset($aDSNInfo['password']) && $aDSNInfo['password']) {
194 $aProcEnv = array_merge(array('PGPASSWORD' => $aDSNInfo['password']), $_ENV);
197 $osm2pgsql .= ' -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file'];
198 runWithEnv($osm2pgsql, $aProcEnv);
201 if (!$aCMDResult['ignore-errors'] && !chksql($oDB->getRow('select * from place limit 1'))) {
206 function createFunctions($aCMDResult)
208 info('Create Functions');
210 if (!checkModulePresence()) {
211 fail('error loading nominatim.so module');
214 createSqlFunctions($aCMDResult);
217 function createTables($aCMDResult)
219 info('Create Tables');
221 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tables.sql');
222 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
223 $sTemplate = replaceTablespace(
225 CONST_Tablespace_Address_Data,
228 $sTemplate = replaceTablespace(
229 '{ts:address-index}',
230 CONST_Tablespace_Address_Index,
233 $sTemplate = replaceTablespace(
235 CONST_Tablespace_Search_Data,
238 $sTemplate = replaceTablespace(
240 CONST_Tablespace_Search_Index,
243 $sTemplate = replaceTablespace(
245 CONST_Tablespace_Aux_Data,
248 $sTemplate = replaceTablespace(
250 CONST_Tablespace_Aux_Index,
253 pgsqlRunScript($aCMDResult, $sTemplate, false);
255 // re-run the functions
256 info('Recreate Functions');
257 createSqlFunctions($aCMDResult);
260 function createPartitionTables($aCMDResult)
262 info('Create Partition Tables');
264 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
265 $sTemplate = replaceTablespace(
267 CONST_Tablespace_Address_Data,
270 $sTemplate = replaceTablespace(
271 '{ts:address-index}',
272 CONST_Tablespace_Address_Index,
275 $sTemplate = replaceTablespace(
277 CONST_Tablespace_Search_Data,
280 $sTemplate = replaceTablespace(
282 CONST_Tablespace_Search_Index,
285 $sTemplate = replaceTablespace(
287 CONST_Tablespace_Aux_Data,
290 $sTemplate = replaceTablespace(
292 CONST_Tablespace_Aux_Index,
296 pgsqlRunPartitionScript($aCMDResult, $sTemplate);
299 function createPartitionFunctions($aCMDResult)
301 info('Create Partition Functions');
303 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
305 pgsqlRunPartitionScript($aCMDResult, $sTemplate);
308 function importWikipediaArticles()
310 $sWikiArticlesFile = CONST_Wikipedia_Data_Path.'/wikipedia_article.sql.bin';
311 $sWikiRedirectsFile = CONST_Wikipedia_Data_Path.'/wikipedia_redirect.sql.bin';
312 if (file_exists($sWikiArticlesFile)) {
313 info('Importing wikipedia articles');
314 pgsqlRunDropAndRestore($sWikiArticlesFile);
316 warn('wikipedia article dump file not found - places will have default importance');
318 if (file_exists($sWikiRedirectsFile)) {
319 info('Importing wikipedia redirects');
320 pgsqlRunDropAndRestore($sWikiRedirectsFile);
322 warn('wikipedia redirect dump file not found - some place importance values may be missing');
326 function loadData($aCMDResult, $iInstances)
328 info('Drop old Data');
331 if (!pg_query($oDB->connection, 'TRUNCATE word')) fail(pg_last_error($oDB->connection));
333 if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
335 if (!pg_query($oDB->connection, 'TRUNCATE location_property_osmline')) fail(pg_last_error($oDB->connection));
337 if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
339 if (!pg_query($oDB->connection, 'TRUNCATE place_boundingbox')) fail(pg_last_error($oDB->connection));
341 if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
343 if (!pg_query($oDB->connection, 'TRUNCATE search_name')) fail(pg_last_error($oDB->connection));
345 if (!pg_query($oDB->connection, 'TRUNCATE search_name_blank')) fail(pg_last_error($oDB->connection));
347 if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
349 if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
352 $sSQL = 'select distinct partition from country_name';
353 $aPartitions = chksql($oDB->getCol($sSQL));
354 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
355 foreach ($aPartitions as $sPartition) {
356 if (!pg_query($oDB->connection, 'TRUNCATE location_road_'.$sPartition)) fail(pg_last_error($oDB->connection));
360 // used by getorcreate_word_id to ignore frequent partial words
361 $sSQL = 'CREATE OR REPLACE FUNCTION get_maxwordfreq() RETURNS integer AS ';
362 $sSQL .= '$$ SELECT '.CONST_Max_Word_Frequency.' as maxwordfreq; $$ LANGUAGE SQL IMMUTABLE';
363 if (!pg_query($oDB->connection, $sSQL)) {
364 fail(pg_last_error($oDB->connection));
368 // pre-create the word list
369 if (!$aCMDResult['disable-token-precalc']) {
370 info('Loading word list');
371 pgsqlRunScriptFile($aCMDResult, CONST_BasePath.'/data/words.sql');
375 $sColumns = 'osm_type, osm_id, class, type, name, admin_level, address, extratags, geometry';
377 $aDBInstances = array();
378 $iLoadThreads = max(1, $iInstances - 1);
379 for ($i = 0; $i < $iLoadThreads; $i++) {
380 $aDBInstances[$i] =& getDB(true);
381 $sSQL = "INSERT INTO placex ($sColumns) SELECT $sColumns FROM place WHERE osm_id % $iLoadThreads = $i";
382 $sSQL .= " and not (class='place' and type='houses' and osm_type='W'";
383 $sSQL .= " and ST_GeometryType(geometry) = 'ST_LineString')";
384 $sSQL .= ' and ST_IsValid(geometry)';
385 if ($aCMDResult['verbose']) echo "$sSQL\n";
386 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) {
387 fail(pg_last_error($aDBInstances[$i]->connection));
390 // last thread for interpolation lines
391 $aDBInstances[$iLoadThreads] =& getDB(true);
392 $sSQL = 'insert into location_property_osmline';
393 $sSQL .= ' (osm_id, address, linegeo)';
394 $sSQL .= ' SELECT osm_id, address, geometry from place where ';
395 $sSQL .= "class='place' and type='houses' and osm_type='W' and ST_GeometryType(geometry) = 'ST_LineString'";
396 if ($aCMDResult['verbose']) echo "$sSQL\n";
397 if (!pg_send_query($aDBInstances[$iLoadThreads]->connection, $sSQL)) {
398 fail(pg_last_error($aDBInstances[$iLoadThreads]->connection));
402 for ($i = 0; $i <= $iLoadThreads; $i++) {
403 while (($hPGresult = pg_get_result($aDBInstances[$i]->connection)) !== false) {
404 $resultStatus = pg_result_status($hPGresult);
405 // PGSQL_EMPTY_QUERY, PGSQL_COMMAND_OK, PGSQL_TUPLES_OK,
406 // PGSQL_COPY_OUT, PGSQL_COPY_IN, PGSQL_BAD_RESPONSE,
407 // PGSQL_NONFATAL_ERROR and PGSQL_FATAL_ERROR
408 echo 'Query result ' . $i . ' is: ' . $resultStatus . "\n";
409 if ($resultStatus != PGSQL_COMMAND_OK && $resultStatus != PGSQL_TUPLES_OK) {
410 $resultError = pg_result_error($hPGresult);
411 echo '-- error text ' . $i . ': ' . $resultError . "\n";
417 fail('SQL errors loading placex and/or location_property_osmline tables');
420 info('Reanalysing database');
421 pgsqlRunScript($aCMDResult, 'ANALYSE');
423 $sDatabaseDate = getDatabaseDate($oDB);
424 pg_query($oDB->connection, 'TRUNCATE import_status');
425 if ($sDatabaseDate === false) {
426 warn('could not determine database date.');
428 $sSQL = "INSERT INTO import_status (lastimportdate) VALUES('".$sDatabaseDate."')";
429 pg_query($oDB->connection, $sSQL);
430 echo "Latest data imported from $sDatabaseDate.\n";
434 function importTigerData($aCMDResult, $iInstances)
436 info('Import Tiger data');
438 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_start.sql');
439 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
440 $sTemplate = replaceTablespace(
442 CONST_Tablespace_Aux_Data,
445 $sTemplate = replaceTablespace(
447 CONST_Tablespace_Aux_Index,
450 pgsqlRunScript($aCMDResult, $sTemplate, false);
452 $aDBInstances = array();
453 for ($i = 0; $i < $iInstances; $i++) {
454 $aDBInstances[$i] =& getDB(true);
457 foreach (glob(CONST_Tiger_Data_Path.'/*.sql') as $sFile) {
459 $hFile = fopen($sFile, 'r');
460 $sSQL = fgets($hFile, 100000);
464 for ($i = 0; $i < $iInstances; $i++) {
465 if (!pg_connection_busy($aDBInstances[$i]->connection)) {
466 while (pg_get_result($aDBInstances[$i]->connection));
467 $sSQL = fgets($hFile, 100000);
469 if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
471 if ($iLines == 1000) {
485 for ($i = 0; $i < $iInstances; $i++) {
486 if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
493 info('Creating indexes on Tiger data');
494 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_finish.sql');
495 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
496 $sTemplate = replaceTablespace(
498 CONST_Tablespace_Aux_Data,
501 $sTemplate = replaceTablespace(
503 CONST_Tablespace_Aux_Index,
506 pgsqlRunScript($aCMDResult, $sTemplate, false);
509 function calculatePostcodes($aCMDResult)
511 info('Calculate Postcodes');
513 if (!pg_query($oDB->connection, 'TRUNCATE location_postcode')) {
514 fail(pg_last_error($oDB->connection));
517 $sSQL = 'INSERT INTO location_postcode';
518 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
519 $sSQL .= "SELECT nextval('seq_place'), 1, country_code,";
520 $sSQL .= " upper(trim (both ' ' from address->'postcode')) as pc,";
521 $sSQL .= ' ST_Centroid(ST_Collect(ST_Centroid(geometry)))';
522 $sSQL .= ' FROM placex';
523 $sSQL .= " WHERE address ? 'postcode' AND address->'postcode' NOT SIMILAR TO '%(,|;)%'";
524 $sSQL .= ' AND geometry IS NOT null';
525 $sSQL .= ' GROUP BY country_code, pc';
527 if (!pg_query($oDB->connection, $sSQL)) {
528 fail(pg_last_error($oDB->connection));
531 if (CONST_Use_Extra_US_Postcodes) {
532 // only add postcodes that are not yet available in OSM
533 $sSQL = 'INSERT INTO location_postcode';
534 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
535 $sSQL .= "SELECT nextval('seq_place'), 1, 'us', postcode,";
536 $sSQL .= ' ST_SetSRID(ST_Point(x,y),4326)';
537 $sSQL .= ' FROM us_postcode WHERE postcode NOT IN';
538 $sSQL .= ' (SELECT postcode FROM location_postcode';
539 $sSQL .= " WHERE country_code = 'us')";
540 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
543 // add missing postcodes for GB (if available)
544 $sSQL = 'INSERT INTO location_postcode';
545 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
546 $sSQL .= "SELECT nextval('seq_place'), 1, 'gb', postcode, geometry";
547 $sSQL .= ' FROM gb_postcode WHERE postcode NOT IN';
548 $sSQL .= ' (SELECT postcode FROM location_postcode';
549 $sSQL .= " WHERE country_code = 'gb')";
550 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
552 if (!$aCMDResult['all']) {
553 $sSQL = "DELETE FROM word WHERE class='place' and type='postcode'";
554 $sSQL .= 'and word NOT IN (SELECT postcode FROM location_postcode)';
555 if (!pg_query($oDB->connection, $sSQL)) {
556 fail(pg_last_error($oDB->connection));
559 $sSQL = 'SELECT count(getorcreate_postcode_id(v)) FROM ';
560 $sSQL .= '(SELECT distinct(postcode) as v FROM location_postcode) p';
562 if (!pg_query($oDB->connection, $sSQL)) {
563 fail(pg_last_error($oDB->connection));
567 function index($aCMDResult, $aDSNInfo, $iInstances)
570 $sBaseCmd = CONST_InstallPath.'/nominatim/nominatim -i -d '.$aDSNInfo['database'].' -P '.$aDSNInfo['port'].' -t '.$iInstances.$sOutputFile;
571 if (isset($aDSNInfo['hostspec']) && $aDSNInfo['hostspec']) {
572 $sBaseCmd .= ' -H ' . $aDSNInfo['hostspec'];
574 if (isset($aDSNInfo['username']) && $aDSNInfo['username']) {
575 $sBaseCmd .= ' -U ' . $aDSNInfo['username'];
578 if (isset($aDSNInfo['password']) && $aDSNInfo['password']) {
579 $aProcEnv = array_merge(array('PGPASSWORD' => $aDSNInfo['password']), $_ENV);
582 info('Index ranks 0 - 4');
583 $iStatus = runWithEnv($sBaseCmd.' -R 4', $aProcEnv);
585 fail('error status ' . $iStatus . ' running nominatim!');
587 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript($aCMDResult, 'ANALYSE');
588 info('Index ranks 5 - 25');
589 $iStatus = runWithEnv($sBaseCmd.' -r 5 -R 25', $aProcEnv);
591 fail('error status ' . $iStatus . ' running nominatim!');
593 if (!$aCMDResult['index-noanalyse']) pgsqlRunScript($aCMDResult, 'ANALYSE');
594 info('Index ranks 26 - 30');
595 $iStatus = runWithEnv($sBaseCmd.' -r 26', $aProcEnv);
597 fail('error status ' . $iStatus . ' running nominatim!');
600 info('Index postcodes');
602 $sSQL = 'UPDATE location_postcode SET indexed_status = 0';
603 if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
606 function createSearchIndices($aCMDResult)
608 info('Create Search indices');
610 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
611 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
612 $sTemplate = replaceTablespace(
613 '{ts:address-index}',
614 CONST_Tablespace_Address_Index,
617 $sTemplate = replaceTablespace(
619 CONST_Tablespace_Search_Index,
622 $sTemplate = replaceTablespace(
624 CONST_Tablespace_Aux_Index,
628 pgsqlRunScript($aCMDResult, $sTemplate);
631 function createCountryNames($aCMDResult)
633 info('Create search index for default country names');
635 pgsqlRunScript($aCMDResult, "select getorcreate_country(make_standard_name('uk'), 'gb')");
636 pgsqlRunScript($aCMDResult, "select getorcreate_country(make_standard_name('united states'), 'us')");
637 pgsqlRunScript($aCMDResult, 'select count(*) from (select getorcreate_country(make_standard_name(country_code), country_code) from country_name where country_code is not null) as x');
638 pgsqlRunScript($aCMDResult, "select count(*) from (select getorcreate_country(make_standard_name(name->'name'), country_code) from country_name where name ? 'name') as x");
640 $sSQL = 'select count(*) from (select getorcreate_country(make_standard_name(v), country_code) from (select country_code, skeys(name) as k, svals(name) as v from country_name) x where k ';
641 if (CONST_Languages) {
644 foreach (explode(',', CONST_Languages) as $sLang) {
645 $sSQL .= $sDelim."'name:$sLang'";
650 // all include all simple name tags
651 $sSQL .= "like 'name:%'";
654 pgsqlRunScript($aCMDResult, $sSQL);
657 function drop($aCMDResult)
659 info('Drop tables only required for updates');
660 // The implementation is potentially a bit dangerous because it uses
661 // a positive selection of tables to keep, and deletes everything else.
662 // Including any tables that the unsuspecting user might have manually
663 // created. USE AT YOUR OWN PERIL.
666 // tables we want to keep. everything else goes.
667 $aKeepTables = array(
673 'location_property*',
686 $aDropTables = array();
687 $aHaveTables = chksql($oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'"));
689 foreach ($aHaveTables as $sTable) {
691 foreach ($aKeepTables as $sKeep) {
692 if (fnmatch($sKeep, $sTable)) {
697 if (!$bFound) array_push($aDropTables, $sTable);
700 foreach ($aDropTables as $sDrop) {
701 if ($aCMDResult['verbose']) echo "dropping table $sDrop\n";
702 @pg_query($oDB->connection, "DROP TABLE $sDrop CASCADE");
703 // ignore warnings/errors as they might be caused by a table having
704 // been deleted already by CASCADE
707 if (!is_null(CONST_Osm2pgsql_Flatnode_File) && CONST_Osm2pgsql_Flatnode_File) {
708 if ($aCMDResult['verbose']) echo 'deleting '.CONST_Osm2pgsql_Flatnode_File."\n";
709 unlink(CONST_Osm2pgsql_Flatnode_File);
713 function didSomething($bDidSomething)
715 if (!$bDidSomething) {
716 showUsage($aCMDOptions, true);
718 echo "Summary of warnings:\n\n";
721 info('Setup finished.');
725 // *********************************
727 function pgsqlRunScriptFile($aCMDResult, $sFilename)
729 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
730 // Convert database DSN to psql parameters
731 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
732 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
733 $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'];
734 if (!$aCMDResult['verbose']) {
737 if (isset($aDSNInfo['hostspec']) && $aDSNInfo['hostspec']) {
738 $sCMD .= ' -h ' . $aDSNInfo['hostspec'];
740 if (isset($aDSNInfo['username']) && $aDSNInfo['username']) {
741 $sCMD .= ' -U ' . $aDSNInfo['username'];
744 if (isset($aDSNInfo['password']) && $aDSNInfo['password']) {
745 $aProcEnv = array_merge(array('PGPASSWORD' => $aDSNInfo['password']), $_ENV);
748 if (preg_match('/\\.gz$/', $sFilename)) {
749 $aDescriptors = array(
750 0 => array('pipe', 'r'),
751 1 => array('pipe', 'w'),
752 2 => array('file', '/dev/null', 'a')
754 $hGzipProcess = proc_open('zcat '.$sFilename, $aDescriptors, $ahGzipPipes);
755 if (!is_resource($hGzipProcess)) fail('unable to start zcat');
756 $aReadPipe = $ahGzipPipes[1];
757 fclose($ahGzipPipes[0]);
759 $sCMD .= ' -f '.$sFilename;
760 $aReadPipe = array('pipe', 'r');
762 $aDescriptors = array(
764 1 => array('pipe', 'w'),
765 2 => array('file', '/dev/null', 'a')
768 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes, null, $aProcEnv);
769 if (!is_resource($hProcess)) fail('unable to start pgsql');
770 // TODO: error checking
771 while (!feof($ahPipes[1])) {
772 echo fread($ahPipes[1], 4096);
775 $iReturn = proc_close($hProcess);
777 fail("pgsql returned with error code ($iReturn)");
780 fclose($ahGzipPipes[1]);
781 proc_close($hGzipProcess);
785 function pgsqlRunScript($aCMDResult, $sScript, $bfatal = true)
790 $aCMDResult['verbose'],
791 $aCMDResult['ignore-errors']
795 function pgsqlRunPartitionScript($aCMDResult, $sTemplate)
800 $sSQL = 'select distinct partition from country_name';
801 $aPartitions = chksql($oDB->getCol($sSQL));
802 if (!$aCMDResult['no-partitions']) $aPartitions[] = 0;
804 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
805 foreach ($aMatches as $aMatch) {
807 foreach ($aPartitions as $sPartitionName) {
808 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
810 $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
813 pgsqlRunScript($aCMDResult, $sTemplate);
816 function pgsqlRunRestoreData($sDumpFile)
818 // Convert database DSN to psql parameters
819 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
820 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
821 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc -a '.$sDumpFile;
823 $aDescriptors = array(
824 0 => array('pipe', 'r'),
825 1 => array('pipe', 'w'),
826 2 => array('file', '/dev/null', 'a')
829 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
830 if (!is_resource($hProcess)) fail('unable to start pg_restore');
834 // TODO: error checking
835 while (!feof($ahPipes[1])) {
836 echo fread($ahPipes[1], 4096);
840 $iReturn = proc_close($hProcess);
843 function pgsqlRunDropAndRestore($sDumpFile)
845 // Convert database DSN to psql parameters
846 $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
847 if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
848 $sCMD = 'pg_restore -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database'].' -Fc --clean '.$sDumpFile;
849 if (isset($aDSNInfo['hostspec']) && $aDSNInfo['hostspec']) {
850 $sCMD .= ' -h ' . $aDSNInfo['hostspec'];
852 if (isset($aDSNInfo['username']) && $aDSNInfo['username']) {
853 $sCMD .= ' -U ' . $aDSNInfo['username'];
856 if (isset($aDSNInfo['password']) && $aDSNInfo['password']) {
857 $aProcEnv = array_merge(array('PGPASSWORD' => $aDSNInfo['password']), $_ENV);
860 $iReturn = runWithEnv($sCMD, $aProcEnv);
863 function passthpassthruCheckReturn($sCmd)
866 passthru($sCmd, $iResult);
869 function replaceTablespace($sTemplate, $sTablespace, $sSql)
872 $sSql = str_replace($sTemplate, 'TABLESPACE "'.$sTablespace.'"', $sSql);
874 $sSql = str_replace($sTemplate, '', $sSql);
880 function createSqlFunctions($aCMDResult)
882 $sModulePath = CONST_Database_Module_Path;
883 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
884 $sTemplate = str_replace('{modulepath}', $sModulePath, $sTemplate);
885 if ($aCMDResult['enable-diff-updates']) {
886 $sTemplate = str_replace('RETURN NEW; -- %DIFFUPDATES%', '--', $sTemplate);
888 if ($aCMDResult['enable-debug-statements']) {
889 $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
891 if (CONST_Limit_Reindexing) {
892 $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
894 if (!CONST_Use_US_Tiger_Data) {
895 $sTemplate = str_replace('-- %NOTIGERDATA% ', '', $sTemplate);
897 if (!CONST_Use_Aux_Location_data) {
898 $sTemplate = str_replace('-- %NOAUXDATA% ', '', $sTemplate);
900 pgsqlRunScript($aCMDResult, $sTemplate);
903 function checkModulePresence()
905 // Try accessing the C module, so we know early if something is wrong
906 // and can simply error out.
907 $sModulePath = CONST_Database_Module_Path;
908 $sSQL = "CREATE FUNCTION nominatim_test_import_func(text) RETURNS text AS '";
909 $sSQL .= $sModulePath."/nominatim.so', 'transliteration' LANGUAGE c IMMUTABLE STRICT";
910 $sSQL .= ';DROP FUNCTION nominatim_test_import_func(text);';
913 $oResult = $oDB->query($sSQL);
917 if (PEAR::isError($oResult)) {
918 echo "\nERROR: Failed to load nominatim module. Reason:\n";
919 echo $oResult->userinfo."\n\n";
926 function createSetupArgvArray()
930 'Create and setup nominatim search system',
931 array('help', 'h', 0, 1, 0, 0, false, 'Show Help'),
932 array('quiet', 'q', 0, 1, 0, 0, 'bool', 'Quiet output'),
933 array('verbose', 'v', 0, 1, 0, 0, 'bool', 'Verbose output'),
935 array('osm-file', '', 0, 1, 1, 1, 'realpath', 'File to import'),
936 array('threads', '', 0, 1, 1, 1, 'int', 'Number of threads (where possible)'),
938 array('all', '', 0, 1, 0, 0, 'bool', 'Do the complete process'),
940 array('create-db', '', 0, 1, 0, 0, 'bool', 'Create nominatim db'),
941 array('setup-db', '', 0, 1, 0, 0, 'bool', 'Build a blank nominatim db'),
942 array('import-data', '', 0, 1, 0, 0, 'bool', 'Import a osm file'),
943 array('osm2pgsql-cache', '', 0, 1, 1, 1, 'int', 'Cache size used by osm2pgsql'),
944 array('create-functions', '', 0, 1, 0, 0, 'bool', 'Create functions'),
945 array('enable-diff-updates', '', 0, 1, 0, 0, 'bool', 'Turn on the code required to make diff updates work'),
946 array('enable-debug-statements', '', 0, 1, 0, 0, 'bool', 'Include debug warning statements in pgsql commands'),
947 array('ignore-errors', '', 0, 1, 0, 0, 'bool', 'Continue import even when errors in SQL are present (EXPERT)'),
948 array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
949 array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'),
950 array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'),
951 array('no-partitions', '', 0, 1, 0, 0, 'bool', 'Do not partition search indices (speeds up import of single country extracts)'),
952 array('import-wikipedia-articles', '', 0, 1, 0, 0, 'bool', 'Import wikipedia article dump'),
953 array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'),
954 array('disable-token-precalc', '', 0, 1, 0, 0, 'bool', 'Disable name precalculation (EXPERT)'),
955 array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'),
956 array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
957 array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'),
958 array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'),
959 array('index-noanalyse', '', 0, 1, 0, 0, 'bool', 'Do not perform analyse operations during index (EXPERT)'),
960 array('create-search-indices', '', 0, 1, 0, 0, 'bool', 'Create additional indices required for search and update'),
961 array('create-country-names', '', 0, 1, 0, 0, 'bool', 'Create default list of searchable country names'),
962 array('drop', '', 0, 1, 0, 0, 'bool', 'Drop tables needed for updates, making the database readonly (EXPERIMENTAL)'),