From: Sarah Hoffmann Date: Mon, 28 Apr 2014 18:19:01 +0000 (+0200) Subject: Merge remote-tracking branch 'upstream/master' X-Git-Tag: deploy~540 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/163b488591181e05a1ca0f80923ff7d5d309c71b?hp=-c Merge remote-tracking branch 'upstream/master' --- 163b488591181e05a1ca0f80923ff7d5d309c71b diff --combined sql/functions.sql index 980528ae,6a465e0b..775ff168 --- a/sql/functions.sql +++ b/sql/functions.sql @@@ -271,6 -271,20 +271,20 @@@ END $$ LANGUAGE plpgsql IMMUTABLE; + CREATE OR REPLACE FUNCTION get_name_ids(lookup_word TEXT) + RETURNS INTEGER[] + AS $$ + DECLARE + lookup_token TEXT; + return_word_ids INTEGER[]; + BEGIN + lookup_token := ' '||trim(lookup_word); + SELECT array_agg(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_ids; + RETURN return_word_ids; + END; + $$ + LANGUAGE plpgsql IMMUTABLE; + CREATE OR REPLACE FUNCTION array_merge(a INTEGER[], b INTEGER[]) RETURNS INTEGER[] AS $$ @@@ -922,11 -936,6 +936,11 @@@ DECLAR BEGIN --DEBUG: RAISE WARNING '% %',NEW.osm_type,NEW.osm_id; + -- remove operator tag for most places, messes too much with search_name indexes + IF NEW.class not in ('amenity', 'shop') THEN + NEW.name := delete(NEW.name, 'operator'); + END IF; + -- just block these IF NEW.class in ('landuse','natural') and NEW.name is null THEN -- RAISE WARNING 'empty landuse %',NEW.osm_id; @@@ -1109,6 -1118,11 +1123,11 @@@ ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN NEW.rank_search := 18; NEW.rank_address := 0; + ELSEIF NEW.class = 'natural' and NEW.type = 'sea' THEN + NEW.rank_search := 4; + NEW.rank_address := NEW.rank_search; + ELSEIF NEW.class = 'natural' and NEW.type in ('coastline') THEN + RETURN NULL; -- any feature more than 5 square miles is probably worth indexing ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN NEW.rank_search := 22; @@@ -1130,11 -1144,6 +1149,6 @@@ ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' THEN NEW.rank_search := 26; NEW.rank_address := NEW.rank_search; - ELSEIF NEW.class = 'natural' and NEW.type = 'sea' THEN - NEW.rank_search := 4; - 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; @@@ -1265,6 -1274,7 +1279,7 @@@ DECLAR search_maxrank INTEGER; address_maxrank INTEGER; address_street_word_id INTEGER; + address_street_word_ids INTEGER[]; parent_place_id_rank BIGINT; isin TEXT[]; @@@ -1421,7 -1431,7 +1436,7 @@@ BEGI 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)::bigint - and rank_search = 26 INTO NEW.parent_place_id; + and rank_search = 26 and name is not null INTO NEW.parent_place_id; END IF; END LOOP; END IF; @@@ -1450,7 -1460,7 +1465,7 @@@ 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)::bigint - and rank_search = 26 INTO NEW.parent_place_id; + and rank_search = 26 and name is not null INTO NEW.parent_place_id; END IF; END LOOP; END IF; @@@ -1500,7 -1510,7 +1515,7 @@@ 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)::bigint - and rank_search = 26 INTO NEW.parent_place_id; + and rank_search = 26 and name is not null INTO NEW.parent_place_id; END IF; END LOOP; END IF; @@@ -1510,18 -1520,18 +1525,18 @@@ --RAISE WARNING 'x3 %',NEW.parent_place_id; IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN - address_street_word_id := get_name_id(make_standard_name(NEW.street)); - IF address_street_word_id IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_id) LOOP + address_street_word_ids := get_name_ids(make_standard_name(NEW.street)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP NEW.parent_place_id := location.place_id; END LOOP; END IF; END IF; IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN - address_street_word_id := get_name_id(make_standard_name(NEW.addr_place)); - IF address_street_word_id IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_id) LOOP + address_street_word_ids := get_name_id(make_standard_name(NEW.addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP NEW.parent_place_id := location.place_id; END LOOP; END IF; @@@ -2022,11 -2032,6 +2037,11 @@@ BEGI --DEBUG: RAISE WARNING '%', existingplacex; END IF; + -- remove operator tag for most places, messes too much with search_name indexes + IF NEW.class not in ('amenity', 'shop') THEN + NEW.name := delete(NEW.name, 'operator'); + END IF; + -- Just block these - lots and pointless IF NEW.class in ('landuse','natural') and NEW.name is null THEN -- if the name tag was removed, older versions might still be lurking in the place table @@@ -2217,12 -2222,6 +2232,12 @@@ END IF; + -- refuse to update multiplpoygons with too many objects, too much of a performance hit + IF ST_NumGeometries(NEW.geometry) > 2000 THEN + RAISE WARNING 'Dropping update of % % because of geometry complexity.', NEW.osm_type, NEW.osm_id; + RETURN NULL; + END IF; + IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '') OR coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '')