country_default_language_code character varying(2),
partition integer
);
+GRANT SELECT ON country_name TO "www-data";
COPY country_name (country_code, name, country_default_language_code, partition) FROM stdin;
}
PQclear(res);
- res = PQexec(thread_data[i].conn, "set enable_seqscan = false");
+ /*res = PQexec(thread_data[i].conn, "set enable_seqscan = false");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed disabling sequential scan: %s\n", PQerrorMessage(conn));
exit(EXIT_FAILURE);
}
- PQclear(res);
+ PQclear(res);*/
nominatim_exportCreatePreparedQueries(thread_data[i].conn);
}
usleep(1000);
// Aim for one update per second
- if (sleepcount++ > 500)
+ if (sleepcount++ > 2000)
{
rankPerSecond = ((float)rankCountTuples + (float)count) / MAX(difftime(time(0), rankStartTime),1);
fprintf(stderr, " Done %i in %i @ %f per second - Rank %i ETA (seconds): %f\n", (rankCountTuples + count), (int)(difftime(time(0), rankStartTime)), rankPerSecond, rank, ((float)(rankTotalTuples - (rankCountTuples + count)))/rankPerSecond);
@define('CONST_Database_DSN', 'pgsql://@/nominatim');
// Paths
- @define('CONST_Path_Postgresql_Contrib', '/usr/share/postgresql/9.0/contrib');
+ @define('CONST_Postgresql_Version', '9.1');
+ @define('CONST_Path_Postgresql_Contrib', '/usr/share/postgresql/'.CONST_Postgresql_Version.'/contrib');
@define('CONST_Path_Postgresql_Postgis', CONST_Path_Postgresql_Contrib.'/postgis-1.5');
+ @define('CONST_Osm2pgsql_Binary', CONST_BasePath.'/osm2pgsql/osm2pgsql');
+ @define('CONST_Osmosis_Binary', CONST_BasePath.'/osmosis-0.38/bin/osmosis');
// Website settings
@define('CONST_ClosedForIndexing', false);
END IF;
r := a;
FOR i IN 1..array_upper(b, 1) LOOP
- IF NOT (ARRAY[b[i]] && r) THEN
+ IF NOT (ARRAY[b[i]] <@ r) THEN
r := r || b[i];
END IF;
END LOOP;
s := make_standard_name(item.value);
w := getorcreate_name_id(s, item.value);
- result := result | w;
+
+ IF not(ARRAY[w] <@ result) THEN
+ result := result || w;
+ END IF;
words := string_to_array(s, ' ');
IF array_upper(words, 1) IS NOT NULL THEN
FOR j IN 1..array_upper(words, 1) LOOP
IF (words[j] != '') THEN
w = getorcreate_word_id(words[j]);
- IF NOT (ARRAY[w] && result) THEN
- result := result | w;
+ IF NOT (ARRAY[w] <@ result) THEN
+ result := result || w;
END IF;
END IF;
END LOOP;
s := make_standard_name(words[j]);
IF s != '' THEN
w := getorcreate_word_id(s);
- IF NOT (ARRAY[w] && result) THEN
- result := result | w;
+ IF NOT (ARRAY[w] <@ result) THEN
+ result := result || w;
END IF;
END IF;
END LOOP;
s := make_standard_name(s);
IF s != '' THEN
w := getorcreate_name_id(s, item.value);
- IF NOT (ARRAY[w] && result) THEN
- result := result | w;
+ IF NOT (ARRAY[w] <@ result) THEN
+ result := result || w;
END IF;
END IF;
END IF;
s := make_standard_name(src);
w := getorcreate_name_id(s);
- IF NOT (ARRAY[w] && result) THEN
+ IF NOT (ARRAY[w] <@ result) THEN
result := result || w;
END IF;
FOR j IN 1..array_upper(words, 1) LOOP
IF (words[j] != '') THEN
w = getorcreate_word_id(words[j]);
- IF NOT (ARRAY[w] && result) THEN
+ IF NOT (ARRAY[w] <@ result) THEN
result := result || w;
END IF;
END IF;
and search_name.place_id = place_addressline.place_id
LOOP
delete from search_name where place_id = childplace.place_id;
- childplace.nameaddress_vector := uniq(sort_asc(childplace.nameaddress_vector + to_add));
- IF childplace.place_id = parent_place_id THEN
- childplace.name_vector := uniq(sort_asc(childplace.name_vector + to_add));
+ IF not (ARRAY[to_add] <@ childplace.nameaddress_vector) THEN
+ childplace.nameaddress_vector := childplace.nameaddress_vector || to_add;
+ END IF;
+ IF childplace.place_id = parent_place_id and not (ARRAY[to_add] <@ childplace.name_vector) THEN
+ childplace.name_vector := childplace.name_vector || to_add;
END IF;
insert into search_name (place_id, search_rank, address_rank, country_code, name_vector, nameaddress_vector, centroid)
values (childplace.place_id, childplace.search_rank, childplace.address_rank, childplace.country_code,
--RAISE WARNING 'x1';
-- Is this node part of a way?
- FOR way IN select id from planet_osm_ways where nodes && ARRAY[NEW.osm_id::integer] LOOP
+ FOR way IN select id from planet_osm_ways where nodes @> ARRAY[NEW.osm_id::integer] LOOP
--RAISE WARNING '%', way;
FOR location IN select * from placex where osm_type = 'W' and osm_id = way.id
LOOP
END IF;
-- Is the WAY part of a relation
- FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id::integer] and members @> ARRAY['w'||location.osm_id]
- LOOP
- -- At the moment we only process one type of relation - associatedStreet
- IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
- IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
---RAISE WARNING 'node in way that is in a relation %',relation;
- SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer
- and rank_search = 26 INTO NEW.parent_place_id;
+ IF NEW.parent_place_id IS NULL THEN
+ FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id::integer] and members @> ARRAY['w'||location.osm_id]
+ LOOP
+ -- At the moment we only process one type of relation - associatedStreet
+ IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
+ IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
+ --RAISE WARNING 'node in way that is in a relation %',relation;
+ SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer
+ and rank_search = 26 INTO NEW.parent_place_id;
+ END IF;
+ END LOOP;
END IF;
END LOOP;
- END IF;
- END LOOP;
+ END IF;
-- If the way contains an explicit name of a street copy it
IF NEW.street IS NULL AND location.street IS NOT NULL THEN
IF array_upper(isin, 1) IS NOT NULL THEN
FOR i IN 1..array_upper(isin, 1) LOOP
address_street_word_id := get_name_id(make_standard_name(isin[i]));
- IF address_street_word_id IS NOT NULL THEN
- isin_tokens := isin_tokens + address_street_word_id;
+ IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ isin_tokens := isin_tokens || address_street_word_id;
END IF;
END LOOP;
END IF;
- isin_tokens := uniq(sort(isin_tokens));
END IF;
IF NEW.postcode IS NOT NULL THEN
isin := regexp_split_to_array(NEW.postcode, E'[;,]');
IF array_upper(isin, 1) IS NOT NULL THEN
FOR i IN 1..array_upper(isin, 1) LOOP
address_street_word_id := get_name_id(make_standard_name(isin[i]));
- IF address_street_word_id IS NOT NULL THEN
- isin_tokens := isin_tokens + address_street_word_id;
+ IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ isin_tokens := isin_tokens || address_street_word_id;
END IF;
END LOOP;
END IF;
- isin_tokens := uniq(sort(isin_tokens));
END IF;
--RAISE WARNING 'ISIN: %', isin_tokens;
FOR location IN
select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1
LOOP
- searchnodes := searchnodes | location.nodes;
+ IF not (ARRAY[location.nodes] <@ searchnodes) THEN
+ searchnodes := searchnodes || location.nodes;
+ END IF;
END LOOP;
END LOOP;
IF array_upper(search, 1) IS NOT NULL AND array_upper(location.name, 1) IS NOT NULL THEN
FOR j IN 1..array_upper(search, 1) LOOP
FOR k IN 1..array_upper(location.name, 1) LOOP
- IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result && ARRAY[trim(location.name[k].value)] AND (for_postcode IS NULL OR location.name[k].value ilike for_postcode||'%') THEN
+ IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result @> ARRAY[trim(location.name[k].value)] AND (for_postcode IS NULL OR location.name[k].value ilike for_postcode||'%') THEN
result[(100 - location.rank_address)] := trim(location.name[k].value);
found := location.rank_address;
END IF;
CREATE TABLE search_name_country () INHERITS (search_name_blank);
CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id);
CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid);
-CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector gin__int_ops) WITH (fastupdate = off);
-CREATE INDEX idx_search_name_country_nameaddress_vector ON search_name_country USING GIN (nameaddress_vector gin__int_ops) WITH (fastupdate = off);
+CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector) WITH (fastupdate = off);
+CREATE INDEX idx_search_name_country_nameaddress_vector ON search_name_country USING GIN (nameaddress_vector) WITH (fastupdate = off);
-- start
CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large);
CREATE TABLE search_name_-partition- () INHERITS (search_name_blank);
CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id);
CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid);
-CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector gin__int_ops) WITH (fastupdate = off);
-CREATE INDEX idx_search_name_-partition-_nameaddress_vector ON search_name_-partition- USING GIN (nameaddress_vector gin__int_ops) WITH (fastupdate = off);
+CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off);
+CREATE INDEX idx_search_name_-partition-_nameaddress_vector ON search_name_-partition- USING GIN (nameaddress_vector) WITH (fastupdate = off);
CREATE TABLE location_property_-partition- () INHERITS (location_property);
CREATE INDEX idx_location_property_-partition-_place_id ON location_property_-partition- USING BTREE (place_id);
);
CREATE INDEX idx_query_log ON query_log USING BTREE (starttime);
GRANT INSERT ON query_log TO "www-data" ;
+GRANT UPDATE ON query_log TO "www-data" ;
CREATE TABLE new_query_log (
type text,
CREATE INDEX idx_location_property_aux_place_id ON location_property_aux USING BTREE (place_id);
CREATE INDEX idx_location_property_aux_parent_place_id ON location_property_aux USING BTREE (parent_place_id);
CREATE INDEX idx_location_property_aux_housenumber_parent_place_id ON location_property_aux USING BTREE (parent_place_id, housenumber);
+GRANT SELECT ON location_property_aux TO "www-data";
CREATE TABLE location_property_tiger () INHERITS (location_property);
CREATE INDEX idx_location_property_tiger_place_id ON location_property_tiger USING BTREE (place_id);
CREATE INDEX idx_location_property_tiger_parent_place_id ON location_property_tiger USING BTREE (parent_place_id);
CREATE INDEX idx_location_property_tiger_housenumber_parent_place_id ON location_property_tiger USING BTREE (parent_place_id, housenumber);
+GRANT SELECT ON location_property_tiger TO "www-data";
drop table IF EXISTS search_name_blank CASCADE;
CREATE TABLE search_name_blank (
drop table IF EXISTS search_name;
CREATE TABLE search_name () INHERITS (search_name_blank);
-CREATE INDEX search_name_name_vector_idx ON search_name USING GIN (name_vector gin__int_ops) WITH (fastupdate = off);
-CREATE INDEX searchnameplacesearch_search_nameaddress_vector_idx ON search_name USING GIN (nameaddress_vector gin__int_ops) WITH (fastupdate = off);
+CREATE INDEX search_name_name_vector_idx ON search_name USING GIN (name_vector) WITH (fastupdate = off);
+CREATE INDEX searchnameplacesearch_search_nameaddress_vector_idx ON search_name USING GIN (nameaddress_vector) WITH (fastupdate = off);
CREATE INDEX idx_search_name_centroid ON search_name USING GIST (centroid);
CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id);
$oDB =& getDB();
passthru('createlang plpgsql '.$aDSNInfo['database']);
- pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/_int.sql');
- pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/hstore.sql');
+ $pgver = (float) CONST_Postgresql_Version;
+ if ($pgver < 9.0) {
+ pgsqlRunScriptFile(CONST_Path_Postgresql_Contrib.'/hstore.sql');
+ } else {
+ pgsqlRunScript('CREATE EXTENSION hstore');
+ }
pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
echo "Import\n";
$bDidSomething = true;
- $osm2pgsql = CONST_BasePath.'/osm2pgsql/osm2pgsql';
- if (!file_exists($osm2pgsql)) $osm2pgsql = trim(`which osm2pgsql`);
+ $osm2pgsql = CONST_Osm2pgsql_Binary;
if (!file_exists($osm2pgsql)) fail("please download and build osm2pgsql");
- passthru($osm2pgsql.' -lsc -O gazetteer -C 10000 --hstore -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file']);
+ passthru($osm2pgsql.' -lsc -O gazetteer -C 12000 --hstore -d '.$aDSNInfo['database'].' '.$aCMDResult['osm-file']);
+ pgsqlRunScript('ANALYSE');
$oDB =& getDB();
$x = $oDB->getRow('select * from place limit 1');
{
$bDidSomething = true;
- if (!file_exists(CONST_BasePath.'/osmosis-0.38/bin/osmosis')) fail("please download osmosis");
+ if (!file_exists(CONST_Osmosis_Binary)) fail("please download osmosis");
if (file_exists(CONST_BasePath.'/settings/configuration.txt')) echo "settings/configuration.txt already exists\n";
- else passthru(CONST_BasePath.'/osmosis-0.38/bin/osmosis --read-replication-interval-init '.CONST_BasePath.'/settings');
+ else passthru(CONST_Osmosis_Binary.' --read-replication-interval-init '.CONST_BasePath.'/settings');
$sDate = $aCMDResult['osmosis-init-date'];
$sURL = 'http://toolserver.org/~mazder/replicate-sequences/?'.$sDate;
array('index-estrate', '', 0, 1, 1, 1, 'int', 'Estimated indexed items per second (def:30)'),
array('deduplicate', '', 0, 1, 0, 0, 'bool', 'Deduplicate tokens'),
+ array('no-npi', '', 0, 1, 0, 0, 'bool', 'Do not write npi index files'),
);
getCmdOpt($_SERVER['argv'], $aCMDOptions, $aResult, true, true);
if (($aResult['import-hourly'] || $aResult['import-daily']) && file_exists($sNextFile))
{
// Import the file
- $sCMD = $sBasePath.'/osm2pgsql/osm2pgsql -klas -C 2000 -O gazetteer -d '.$sDatabaseName.' '.$sNextFile;
+ $sCMD = CONST_Osm2pgsql_Binary.' -klas -C 2000 -O gazetteer -d '.$sDatabaseName.' '.$sNextFile;
echo $sCMD."\n";
exec($sCMD, $sJunk, $iErrorLevel);
2 => array("pipe", "w") // stderr
);
$aPipes = array();
- $sCMD = $sBasePath.'/osm2pgsql/osm2pgsql -klas -C 2000 -O gazetteer -d '.$sDatabaseName.' -';
+ $sCMD = CONST_Osm2pgsql_Binary.' -klas -C 2000 -O gazetteer -d '.$sDatabaseName.' -';
echo $sCMD."\n";
$hProc = proc_open($sCMD, $aSpec, $aPipes);
if (!is_resource($hProc))
if ($aResult['import-osmosis'] || $aResult['import-osmosis-all'])
{
$sImportFile = CONST_BasePath.'/data/osmosischange.osc';
- $sOsmosisCMD = CONST_BasePath.'/osmosis-0.38/bin/osmosis';
+ $sOsmosisCMD = CONST_Osmosis_Binary;
$sOsmosisConfigDirectory = CONST_BasePath.'/settings';
$sDatabaseName = 'nominatim';
$sCMDDownload = $sOsmosisCMD.' --read-replication-interval workingDirectory='.$sOsmosisConfigDirectory.' --simplify-change --write-xml-change '.$sImportFile;
- $sCMDImport = $sBasePath.'/osm2pgsql/osm2pgsql -klas -C 2000 -O gazetteer -d '.$sDatabaseName.' '.$sImportFile;
- $sCMDIndex = $sBasePath.'/nominatim/nominatim -i -t 15 -F ';
-// $sCMDIndex = $sBasePath.'/nominatim/nominatim -i -t 15 ';
+ $sCMDImport = CONST_Osm2pgsql_Binary.' -klas -C 2000 -O gazetteer -d '.$sDatabaseName.' '.$sImportFile;
+ $sCMDIndex = $sBasePath.'/nominatim/nominatim -i -t '.$aResult['index-instances'];
+ if (!$aResult['no-npi']) {
+ $sCMDIndex .= '-F ';
+ }
while(true)
{
$fStartTime = time();
if (!is_dir($sFileDir)) mkdir($sFileDir, 0777, true);
$sThisIndexCmd = $sCMDIndex;
- $sThisIndexCmd .= $sFileDir;
- $sThisIndexCmd .= '/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT);
- $sThisIndexCmd .= ".npi.out";
- echo "$sThisIndexCmd\n";
+ if (!$aResult['no-npi']) {
+ $sThisIndexCmd .= $sFileDir;
+ $sThisIndexCmd .= '/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT);
+ $sThisIndexCmd .= ".npi.out";
- preg_match('#^([0-9]{4})-([0-9]{2})-([0-9]{2})#', $sBatchEnd, $aBatchMatch);
- $sFileDir = CONST_BasePath.'/export/index/';
- $sFileDir .= $aBatchMatch[1].'/'.$aBatchMatch[2];
+ preg_match('#^([0-9]{4})-([0-9]{2})-([0-9]{2})#', $sBatchEnd, $aBatchMatch);
+ $sFileDir = CONST_BasePath.'/export/index/';
+ $sFileDir .= $aBatchMatch[1].'/'.$aBatchMatch[2];
- if (!is_dir($sFileDir)) mkdir($sFileDir, 0777, true);
- file_put_contents($sFileDir.'/'.$aBatchMatch[3].'.idx', "$sBatchEnd\t$iFileID\n", FILE_APPEND);
-
- exec($sThisIndexCmd, $sJunk, $iErrorLevel);
- if ($iErrorLevel)
- {
- echo "Error: $iErrorLevel\n";
- exit;
+ if (!is_dir($sFileDir)) mkdir($sFileDir, 0777, true);
+ file_put_contents($sFileDir.'/'.$aBatchMatch[3].'.idx', "$sBatchEnd\t$iFileID\n", FILE_APPEND);
}
- $sFileDir = CONST_BasePath.'/export/diff/';
- $sFileDir .= str_pad(floor($iFileID/1000000), 3, '0', STR_PAD_LEFT);
- $sFileDir .= '/'.str_pad(floor($iFileID/1000) % 1000, 3, '0', STR_PAD_LEFT);
-
- $sThisIndexCmd = 'bzip2 -z9 '.$sFileDir.'/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT).".npi.out";
echo "$sThisIndexCmd\n";
exec($sThisIndexCmd, $sJunk, $iErrorLevel);
if ($iErrorLevel)
exit;
}
- rename($sFileDir.'/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT).".npi.out.bz2",
- $sFileDir.'/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT).".npi.bz2");
+ if (!$aResult['no-npi']) {
+ $sFileDir = CONST_BasePath.'/export/diff/';
+ $sFileDir .= str_pad(floor($iFileID/1000000), 3, '0', STR_PAD_LEFT);
+ $sFileDir .= '/'.str_pad(floor($iFileID/1000) % 1000, 3, '0', STR_PAD_LEFT);
+
+ $sThisIndexCmd = 'bzip2 -z9 '.$sFileDir.'/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT).".npi.out";
+ echo "$sThisIndexCmd\n";
+ exec($sThisIndexCmd, $sJunk, $iErrorLevel);
+ if ($iErrorLevel)
+ {
+ echo "Error: $iErrorLevel\n";
+ exit;
+ }
+
+ rename($sFileDir.'/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT).".npi.out.bz2",
+ $sFileDir.'/'.str_pad($iFileID % 1000, 3, '0', STR_PAD_LEFT).".npi.bz2");
+ }
echo "Completed for $sBatchEnd in ".round((time()-$fCMDStartTime)/60,2)." minutes\n";
$sSQL = "INSERT INTO import_osmosis_log values ('$sBatchEnd',$iFileSize,'".date('Y-m-d H:i:s',$fCMDStartTime)."','".date('Y-m-d H:i:s')."','index')";
projects / nominatim.git / commitdiff