X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/ee2863200ccef14104cc0e7ac14d3cd8f0a1ce81..fca2d0061780c5e7fb8b11a9fa2b618137b3cc8e:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index 2d7b029d..faa759cc 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -5,7 +5,6 @@ -- score integer --); - CREATE OR REPLACE FUNCTION getclasstypekey(c text, t text) RETURNS TEXT AS $$ DECLARE @@ -296,6 +295,34 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; +CREATE OR REPLACE FUNCTION create_country(src HSTORE, lookup_country_code varchar(2)) RETURNS VOID + AS $$ +DECLARE + s TEXT; + w INTEGER; + words TEXT[]; + item RECORD; + j INTEGER; +BEGIN + FOR item IN SELECT (each(src)).* LOOP + + s := make_standard_name(item.value); + w := getorcreate_country(s, lookup_country_code); + + words := regexp_split_to_array(item.value, E'[,;()]'); + IF array_upper(words, 1) != 1 THEN + FOR j IN 1..array_upper(words, 1) LOOP + s := make_standard_name(words[j]); + IF s != '' THEN + w := getorcreate_country(s, lookup_country_code); + END IF; + END LOOP; + END IF; + END LOOP; +END; +$$ +LANGUAGE plpgsql; + CREATE OR REPLACE FUNCTION make_keywords(src HSTORE) RETURNS INTEGER[] AS $$ DECLARE @@ -543,6 +570,20 @@ END; $$ 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 @@ -596,7 +637,7 @@ BEGIN -- 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 @@ -663,7 +704,7 @@ CREATE OR REPLACE FUNCTION update_location( 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; @@ -922,9 +963,9 @@ BEGIN 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 || (('name:'||default_language) => (NEW.name -> 'name')); + 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 || ('name' => (NEW.name -> ('name:'||default_language))); + NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language))); END IF; END IF; END IF; @@ -951,7 +992,7 @@ BEGIN RETURN NULL; END IF; - NEW.name := 'ref'=>NEW.postcode; + NEW.name := hstore('ref', NEW.postcode); IF NEW.calculated_country_code = 'gb' THEN @@ -1167,7 +1208,13 @@ BEGIN 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; @@ -1276,7 +1323,7 @@ BEGIN 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); + result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search); UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id; END IF; @@ -1307,9 +1354,9 @@ BEGIN 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 || (('name:'||default_language) => (NEW.name -> 'name')); + 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 || ('name' => (NEW.name -> ('name:'||default_language))); + NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language))); END IF; END IF; END IF; @@ -1341,6 +1388,9 @@ BEGIN --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 @@ -1484,6 +1534,7 @@ BEGIN -- 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 @@ -1626,6 +1677,11 @@ BEGIN END IF; + -- make sure all names are in the word table + IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN + perform create_country(NEW.name, lower(NEW.country_code)); + END IF; + NEW.parent_place_id = 0; parent_place_id_rank = 0; @@ -1653,6 +1709,16 @@ BEGIN 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; + END LOOP; + END IF; + -- RAISE WARNING 'ISIN: %', isin_tokens; -- Process area matches @@ -1680,7 +1746,9 @@ BEGIN -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress; -- Add it to the list of search terms - nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); + IF location.rank_search > 4 THEN + nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); + END IF; INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, location_isaddress, location.distance, location.rank_address); IF location_isaddress THEN @@ -1711,15 +1779,16 @@ BEGIN --RAISE WARNING ' ISIN: %',location; - nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); - INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); - address_havelevel[location.rank_address] := true; + IF location.rank_search > 4 THEN + nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); + INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); + address_havelevel[location.rank_address] := true; - IF location.rank_address > parent_place_id_rank THEN - NEW.parent_place_id = location.place_id; - parent_place_id_rank = location.rank_address; + IF location.rank_address > parent_place_id_rank THEN + NEW.parent_place_id = location.place_id; + parent_place_id_rank = location.rank_address; + END IF; END IF; - END LOOP; END IF; @@ -1740,7 +1809,7 @@ BEGIN location_distance := location.distance * 1.5; END IF; - IF location.distance < location_distance THEN + IF location.rank_search > 4 AND location.distance < location_distance THEN -- Add it to the list of search terms nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); @@ -1815,7 +1884,7 @@ BEGIN --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; @@ -2121,7 +2190,9 @@ BEGIN 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; @@ -2277,7 +2348,7 @@ BEGIN hadcountry := false; FOR location IN select placex.place_id, osm_type, osm_id, - CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name, + CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name, class, type, admin_level, true as fromarea, true as isaddress, CASE WHEN rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address, 0 as distance, calculated_country_code @@ -2310,7 +2381,7 @@ BEGIN FOR location IN select placex.place_id, osm_type, osm_id, - CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name, + CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name, class, type, admin_level, fromarea, isaddress, CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address, distance,calculated_country_code @@ -2355,7 +2426,7 @@ BEGIN END IF; IF searchcountrycode IS NOT NULL THEN - location := ROW(null, null, null, 'ref'=>searchcountrycode, 'place', 'country_code', null, true, false, 4, 0)::addressline; + location := ROW(null, null, null, hstore('ref', searchcountrycode), 'place', 'country_code', null, true, false, 4, 0)::addressline; RETURN NEXT location; END IF; @@ -2366,12 +2437,12 @@ BEGIN END IF; IF searchhousenumber IS NOT NULL THEN - location := ROW(in_place_id, null, null, 'ref'=>searchhousenumber, 'place', 'house_number', null, true, true, 28, 0)::addressline; + location := ROW(in_place_id, null, null, hstore('ref', searchhousenumber), 'place', 'house_number', null, true, true, 28, 0)::addressline; RETURN NEXT location; END IF; IF searchpostcode IS NOT NULL THEN - location := ROW(null, null, null, 'ref'=>searchpostcode, 'place', 'postcode', null, true, true, 5, 0)::addressline; + location := ROW(null, null, null, hstore('ref', searchpostcode), 'place', 'postcode', null, true, true, 5, 0)::addressline; RETURN NEXT location; END IF; @@ -2867,3 +2938,70 @@ BEGIN 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;