-- score integer
--);
-
CREATE OR REPLACE FUNCTION getclasstypekey(c text, t text) RETURNS TEXT
AS $$
DECLARE
SELECT min(word_id), max(search_name_count) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id, count;
IF return_word_id IS NULL THEN
return_word_id := nextval('seq_word');
- INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), null, null, null, null, 0, null);
+ INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, 0);
ELSE
IF count > get_maxwordfreq() THEN
return_word_id := NULL;
SELECT min(word_id) FROM word WHERE word_token = lookup_token and class='place' and type='house' into return_word_id;
IF return_word_id IS NULL THEN
return_word_id := nextval('seq_word');
- INSERT INTO word VALUES (return_word_id, lookup_token, null, null, 'place', 'house', null, 0, null);
+ INSERT INTO word VALUES (return_word_id, lookup_token, null, 'place', 'house', null, 0);
END IF;
RETURN return_word_id;
END;
SELECT min(word_id) FROM word WHERE word_token = lookup_token and country_code=lookup_country_code into return_word_id;
IF return_word_id IS NULL THEN
return_word_id := nextval('seq_word');
- INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, lookup_country_code, 0, null);
+ INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, lookup_country_code, 0);
END IF;
RETURN return_word_id;
END;
SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type into return_word_id;
IF return_word_id IS NULL THEN
return_word_id := nextval('seq_word');
- INSERT INTO word VALUES (return_word_id, lookup_token, null, null, lookup_class, lookup_type, null, 0, null);
+ INSERT INTO word VALUES (return_word_id, lookup_token, null, lookup_class, lookup_type, null, 0);
END IF;
RETURN return_word_id;
END;
SELECT min(word_id) FROM word WHERE word_token = lookup_token into return_word_id;
IF return_word_id IS NULL THEN
return_word_id := nextval('seq_word');
- INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, null, 0, null);
+ INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, 0);
END IF;
RETURN return_word_id;
END;
SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type and operator = op into return_word_id;
IF return_word_id IS NULL THEN
return_word_id := nextval('seq_word');
- INSERT INTO word VALUES (return_word_id, lookup_token, null, null, lookup_class, lookup_type, null, 0, op, null);
+ INSERT INTO word VALUES (return_word_id, lookup_token, null, lookup_class, lookup_type, null, 0, op);
END IF;
RETURN return_word_id;
END;
SELECT min(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id;
IF return_word_id IS NULL THEN
return_word_id := nextval('seq_word');
- INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), src_word, null, null, null, 0, null);
+ INSERT INTO word VALUES (return_word_id, lookup_token, src_word, null, null, null, 0);
-- nospace_lookup_token := replace(replace(lookup_token, '-',''), ' ','');
-- IF ' '||nospace_lookup_token != lookup_token THEN
-- INSERT INTO word VALUES (return_word_id, '-'||nospace_lookup_token, null, src_word, null, null, null, 0, null);
result := result || w;
END IF;
+ w := getorcreate_word_id(s);
+
+ 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
result := result || w;
END IF;
+ w := getorcreate_word_id(s);
+
+ 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
$$
LANGUAGE plpgsql IMMUTABLE;
+CREATE OR REPLACE FUNCTION get_country_language_codes(search_country_code VARCHAR(2)) RETURNS TEXT[]
+ AS $$
+DECLARE
+ nearcountry RECORD;
+BEGIN
+ FOR nearcountry IN select country_default_language_codes from country_name where country_code = search_country_code limit 1
+ LOOP
+ RETURN lower(nearcountry.country_default_language_codes);
+ END LOOP;
+ RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
CREATE OR REPLACE FUNCTION get_partition(place geometry, in_country_code VARCHAR(10)) RETURNS INTEGER
AS $$
DECLARE
-- RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
- x := deleteLocationArea(partition, place_id);
+ x := deleteLocationArea(partition, place_id, rank_search);
isarea := false;
IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN
DECLARE
b BOOLEAN;
BEGIN
- b := deleteLocationArea(partition, place_id);
+ b := deleteLocationArea(partition, place_id, rank_search);
-- result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
RETURN add_location(place_id, place_country_code, name, rank_search, rank_address, geometry);
END;
DECLARE
newpoints INTEGER;
- waynodes integer[];
- nodeid INTEGER;
+ waynodes BIGINT[];
+ nodeid BIGINT;
prevnode RECORD;
nextnode RECORD;
startnumber INTEGER;
FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
- select min(place_id) from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::INTEGER and type = 'house' INTO search_place_id;
+ select min(place_id) from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' INTO search_place_id;
IF search_place_id IS NULL THEN
-- null record of right type
- select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::INTEGER and type = 'house' limit 1 INTO nextnode;
+ select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' limit 1 INTO nextnode;
select ST_SetSRID(ST_Point(lon::float/10000000,lat::float/10000000),4326) from planet_osm_nodes where id = waynodes[nodeidpos] INTO nextnode.geometry;
IF nextnode.geometry IS NULL THEN
-- we don't have any information about this point, most likely
NEW.rank_address := NEW.rank_search;
ELSEIF NEW.class = 'natural' and NEW.type in ('coastline') THEN
RETURN NULL;
+ ELSEIF NEW.class = 'mountain_pass' THEN
+ NEW.rank_search := 20;
+ NEW.rank_address := 0;
END IF;
END IF;
END IF;
IF diameter > 0 THEN
-- RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter;
- update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null);
+ IF NEW.rank_search >= 26 THEN
+ -- roads may cause reparenting for >27 rank places
+ update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
+ ELSE
+ -- for all other places the search terms may change as well
+ update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null);
+ END IF;
END IF;
END IF;
RETURN NULL;
END IF;
- IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 OR NEW.linked_place_id is not null THEN
+ IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
RETURN NEW;
END IF;
RETURN NEW;
END IF;
+ -- TODO: this test is now redundant?
IF OLD.indexed_status != 0 THEN
- --DEBUG: RAISE WARNING 'placex_update_0 % %',NEW.osm_type,NEW.osm_id;
NEW.indexed_date = now();
- IF NEW.class = 'place' AND NEW.type = 'houses' THEN
- i := create_interpolation(NEW.osm_id, NEW.housenumber);
+ result := deleteSearchName(NEW.partition, NEW.place_id);
+ DELETE FROM place_addressline WHERE place_id = NEW.place_id;
+ DELETE FROM place_boundingbox where place_id = NEW.place_id;
+ result := deleteRoad(NEW.partition, NEW.place_id);
+ result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
+ UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
+
+ IF NEW.linked_place_id is not null THEN
RETURN NEW;
END IF;
- IF OLD.indexed_status > 0 THEN
- result := deleteSearchName(NEW.partition, NEW.place_id);
- DELETE FROM place_addressline WHERE place_id = NEW.place_id;
- DELETE FROM place_boundingbox where place_id = NEW.place_id;
- result := deleteRoad(NEW.partition, NEW.place_id);
- result := deleteLocationArea(NEW.partition, NEW.place_id);
- UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
+ IF NEW.class = 'place' AND NEW.type = 'houses' THEN
+ i := create_interpolation(NEW.osm_id, NEW.housenumber);
+ RETURN NEW;
END IF;
-- Speed up searches - just use the centroid of the feature
--RAISE WARNING 'finding street for %', NEW;
+ -- We won't get a better centroid, besides these places are too small to care
+ NEW.centroid := place_centroid;
+
NEW.parent_place_id := null;
-- to do that we have to find our parent road
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 relation %',relation;
- SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer
+ SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint
and rank_search = 26 INTO NEW.parent_place_id;
END IF;
END LOOP;
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
+ SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint
and rank_search = 26 INTO NEW.parent_place_id;
END IF;
END LOOP;
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 '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
+ SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint
and rank_search = 26 INTO NEW.parent_place_id;
END IF;
END LOOP;
-- Merge address from parent
nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector);
+ nameaddress_vector := array_merge(nameaddress_vector, location.name_vector);
--return NEW;
-- Performance, it would be more acurate to do all the rest of the import process but it takes too long
-- Just be happy with inheriting from parent road only
result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
END IF;
- result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid);
+ result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
return NEW;
END IF;
-- merge in the label name, re-init word vector
IF NOT linkedPlacex.name IS NULL THEN
NEW.name := linkedPlacex.name || NEW.name;
- name_vector := make_keywords(NEW.name);
+ name_vector := array_merge(name_vector, make_keywords(linkedPlacex.name));
END IF;
-- merge in extra tags
NEW.extratags := linkedPlacex.extratags || NEW.extratags;
END IF;
+ IF NOT NEW.extratags ? linkedPlacex.class THEN
+ NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type);
+ END IF;
+
-- mark the linked place (excludes from search results)
UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name')
AND NEW.rank_address = linkedPlacex.rank_address THEN
-
-- If we don't already have one use this as the centre point of the geometry
IF NEW.centroid IS NULL THEN
NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
NEW.extratags := linkedPlacex.extratags || NEW.extratags;
END IF;
+ IF NOT NEW.extratags ? linkedPlacex.class THEN
+ NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type);
+ END IF;
+
-- mark the linked place (excludes from search results)
UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
-- merge in extra tags
NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ IF NOT NEW.extratags ? linkedPlacex.class THEN
+ NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type);
+ END IF;
+
-- mark the linked place (excludes from search results)
UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
IF NEW.centroid IS NOT NULL THEN
place_centroid := NEW.centroid;
+ -- Place might have had only a name tag before but has now received translations
+ -- from the linked place. Make sure a name tag for the default language exists in
+ -- this case.
+ IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
+ default_language := get_country_language_code(NEW.calculated_country_code);
+ IF default_language IS NOT NULL THEN
+ IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
+ NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
+ ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
+ NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language)));
+ END IF;
+ END IF;
+ END IF;
END IF;
-- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
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 AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
isin_tokens := isin_tokens || address_street_word_id;
END IF;
+
+ -- merge word into address vector
+ address_street_word_id := get_word_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ END IF;
END LOOP;
END IF;
END IF;
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 AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
isin_tokens := isin_tokens || address_street_word_id;
END IF;
+
+ -- merge into address vector
+ address_street_word_id := get_word_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ END IF;
END LOOP;
END IF;
END IF;
+
+ -- for the USA we have an additional address table. Merge in zip codes from there too
+ IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN
+ FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
+ address_street_word_id := get_name_id(make_standard_name(location.postcode));
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ isin_tokens := isin_tokens || address_street_word_id;
+
+ -- also merge in the single word version
+ address_street_word_id := get_word_id(make_standard_name(location.postcode));
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ END LOOP;
+ END IF;
+
-- RAISE WARNING 'ISIN: %', isin_tokens;
-- Process area matches
result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.calculated_country_code, NEW.geometry);
END IF;
- result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid);
+ result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
--- INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.calculated_country_code, name_vector, nameaddress_vector, place_centroid);
END IF;
-- If we've not managed to pick up a better one - default centroid
--DEBUG: RAISE WARNING 'placex_delete:08 % %',OLD.osm_type,OLD.osm_id;
IF OLD.rank_address < 26 THEN
- b := deleteLocationArea(OLD.partition, OLD.place_id);
+ b := deleteLocationArea(OLD.partition, OLD.place_id, OLD.rank_search);
END IF;
--DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id;
END IF;
END LOOP;
- RETURN null;
+ -- anything will do as a fallback - just take the first name type thing there is
+ search := avals(name);
+ RETURN search[1];
END;
$$
LANGUAGE plpgsql IMMUTABLE;
$$
LANGUAGE plpgsql;
-DROP TYPE addressline CASCADE;
+DROP TYPE IF EXISTS addressline CASCADE;
create type addressline as (
place_id BIGINT,
osm_type CHAR(1),
- osm_id INTEGER,
+ osm_id BIGINT,
name HSTORE,
class TEXT,
type TEXT,
END;
$$
LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT) RETURNS BOOLEAN
+ AS $$
+DECLARE
+ osmid BIGINT;
+ osmtype character(1);
+ pclass text;
+ ptype text;
+BEGIN
+ SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
+ DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+ DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+ -- force delete from place/placex by making it a very small geometry
+ UPDATE place set geometry = ST_SetSRID(ST_Point(0,0), 4326) where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+ DELETE FROM place where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+
+ RETURN TRUE;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION place_force_update(placeid BIGINT) RETURNS BOOLEAN
+ AS $$
+DECLARE
+ placegeom GEOMETRY;
+ geom GEOMETRY;
+ diameter FLOAT;
+ rank INTEGER;
+BEGIN
+ SELECT geometry, rank_search FROM placex WHERE place_id = placeid INTO placegeom, rank;
+ IF placegeom IS NOT NULL AND ST_IsValid(placegeom) THEN
+ IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
+ FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
+ update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
+ AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null);
+ update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
+ AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null);
+ END LOOP;
+ ELSE
+ diameter := 0;
+ IF rank = 11 THEN
+ diameter := 0.05;
+ ELSEIF rank < 18 THEN
+ diameter := 0.1;
+ ELSEIF rank < 20 THEN
+ diameter := 0.05;
+ ELSEIF rank = 21 THEN
+ diameter := 0.001;
+ ELSEIF rank < 24 THEN
+ diameter := 0.02;
+ ELSEIF rank < 26 THEN
+ diameter := 0.002; -- 100 to 200 meters
+ ELSEIF rank < 28 THEN
+ diameter := 0.001; -- 50 to 100 meters
+ END IF;
+ IF diameter > 0 THEN
+ update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null);
+ END IF;
+ END IF;
+ RETURN TRUE;
+ END IF;
+
+ RETURN FALSE;
+END;
+$$
+LANGUAGE plpgsql;
projects / nominatim.git / blobdiff