X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/a2a1901b090defca71fddc6ac3d5165048bb4621..ccab565a4a73213742be716ea796677829f1a91c:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index b5ac15a3..1829c63d 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -265,6 +265,7 @@ DECLARE BEGIN rank_search := 30; rank_address := 30; + postcode := upper(postcode); IF country_code = 'gb' THEN IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN @@ -318,19 +319,31 @@ LANGUAGE plpgsql IMMUTABLE; CREATE OR REPLACE FUNCTION get_nearest_postcode(country VARCHAR(2), geom GEOMETRY) RETURNS TEXT AS $$ DECLARE - item RECORD; + outcode TEXT; + cnt INTEGER; BEGIN - FOR item IN - SELECT postcode FROM location_postcode - WHERE ST_DWithin(geom, location_postcode.geometry, 0.05) - AND location_postcode.country_code = country - ORDER BY ST_Distance(geom, location_postcode.geometry) - LIMIT 1 - LOOP - RETURN item.postcode; - END LOOP; + -- If the geometry is an area then only one postcode must be within + -- that area, otherwise consider the area as not having a postcode. + IF ST_GeometryType(geom) in ('ST_Polygon','ST_MultiPolygon') THEN + SELECT min(postcode), count(*) FROM + (SELECT postcode FROM location_postcode + WHERE ST_Contains(geom, location_postcode.geometry) LIMIT 2) sub + INTO outcode, cnt; + + IF cnt = 1 THEN + RETURN outcode; + ELSE + RETURN null; + END IF; + END IF; - RETURN null; + SELECT postcode FROM location_postcode + WHERE ST_DWithin(geom, location_postcode.geometry, 0.05) + AND location_postcode.country_code = country + ORDER BY ST_Distance(geom, location_postcode.geometry) LIMIT 1 + INTO outcode; + + RETURN outcode; END; $$ LANGUAGE plpgsql; @@ -824,9 +837,9 @@ BEGIN RETURN NULL; END IF; - NEW.name := hstore('ref', NEW.postcode); + NEW.name := hstore('ref', NEW.address->'postcode'); - SELECT * FROM get_postcode_rank(NEW.country_code, NEW.postcode) + SELECT * FROM get_postcode_rank(NEW.country_code, NEW.address->'postcode') INTO NEW.rank_search, NEW.rank_address; ELSEIF NEW.class = 'place' THEN @@ -902,6 +915,9 @@ BEGIN ELSE NEW.rank_address := 0; END IF; + ELSEIF NEW.class = 'leisure' and NEW.type in ('park') THEN + NEW.rank_search := 24; + NEW.rank_address := 0; ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN NEW.rank_search := 18; NEW.rank_address := 0; @@ -1043,8 +1059,8 @@ DECLARE linegeo GEOMETRY; splitline GEOMETRY; sectiongeo GEOMETRY; + interpol_postcode TEXT; postcode TEXT; - seg_postcode TEXT; BEGIN -- deferred delete IF OLD.indexed_status = 100 THEN @@ -1063,9 +1079,11 @@ BEGIN NEW.address->'place', NEW.partition, place_centroid, NEW.linegeo); - - IF NEW.address is not NULL and NEW.address ? 'postcode' THEN - NEW.postcode = NEW.address->'postcode'; + IF NEW.address is not NULL AND NEW.address ? 'postcode' AND NEW.address->'postcode' not similar to '%(,|;)%' THEN + interpol_postcode := NEW.address->'postcode'; + housenum := getorcreate_postcode_id(NEW.address->'postcode'); + ELSE + interpol_postcode := NULL; END IF; -- if the line was newly inserted, split the line as necessary @@ -1078,7 +1096,6 @@ BEGIN linegeo := NEW.linegeo; startnumber := NULL; - postcode := NEW.postcode; FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP @@ -1111,15 +1128,24 @@ BEGIN sectiongeo := ST_Reverse(sectiongeo); END IF; - seg_postcode := coalesce(postcode, - prevnode.address->'postcode', - nextnode.address->'postcode'); + -- determine postcode + postcode := coalesce(interpol_postcode, + prevnode.address->'postcode', + nextnode.address->'postcode', + postcode); + + IF postcode is NULL THEN + SELECT placex.postcode FROM placex WHERE place_id = NEW.parent_place_id INTO postcode; + END IF; + IF postcode is NULL THEN + postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry); + END IF; IF NEW.startnumber IS NULL THEN NEW.startnumber := startnumber; NEW.endnumber := endnumber; NEW.linegeo := sectiongeo; - NEW.postcode := seg_postcode; + NEW.postcode := upper(trim(postcode)); ELSE insert into location_property_osmline (linegeo, partition, osm_id, parent_place_id, @@ -1128,7 +1154,7 @@ BEGIN geometry_sector, indexed_status) values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id, startnumber, endnumber, NEW.interpolationtype, - NEW.address, seg_postcode, + NEW.address, postcode, NEW.country_code, NEW.geometry_sector, 0); END IF; END IF; @@ -1329,7 +1355,7 @@ BEGIN --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation_members[i]; FOR linked_node_id IN SELECT place_id FROM placex WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint - and class = NEW.class and type = NEW.type + and class = NEW.class and type in ('river', 'stream', 'canal', 'drain', 'ditch') and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name') LOOP UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id; @@ -1534,34 +1560,15 @@ BEGIN --DEBUG: RAISE WARNING 'Got parent details from search name'; -- determine postcode - IF NEW.address is not null AND NEW.address ? 'postcode' THEN - NEW.postcode = NEW.address->'postcode'; - ELSE - SELECT postcode FROM placex WHERE place_id = NEW.parent_place_id INTO NEW.postcode; - END IF; - IF NEW.postcode is null THEN - NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid); - END IF; - - -- Merge the postcode into the parent's address if necessary - IF NEW.postcode IS NOT NULL THEN - --DEBUG: RAISE WARNING 'Merging postcode into parent'; - isin_tokens := '{}'::int[]; - address_street_word_id := getorcreate_word_id(make_standard_name(NEW.postcode)); - IF address_street_word_id is not null - and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN - isin_tokens := isin_tokens || address_street_word_id; - END IF; - address_street_word_id := getorcreate_name_id(make_standard_name(NEW.postcode)); - IF address_street_word_id is not null - and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN - isin_tokens := isin_tokens || address_street_word_id; - END IF; - IF isin_tokens != '{}'::int[] THEN - UPDATE search_name - SET nameaddress_vector = search_name.nameaddress_vector || isin_tokens - WHERE place_id = NEW.parent_place_id; - END IF; + IF NEW.rank_search > 4 THEN + IF NEW.address is not null AND NEW.address ? 'postcode' THEN + NEW.postcode = upper(trim(NEW.address->'postcode')); + ELSE + SELECT postcode FROM placex WHERE place_id = NEW.parent_place_id INTO NEW.postcode; + END IF; + IF NEW.postcode is null THEN + NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid); + END IF; END IF; -- If there is no name it isn't searchable, don't bother to create a search record @@ -1579,7 +1586,7 @@ BEGIN -- Just be happy with inheriting from parent road only IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN - result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.address->'postcode', NEW.geometry); + result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry); --DEBUG: RAISE WARNING 'Place added to location table'; END IF; @@ -1750,7 +1757,7 @@ 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 + IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R' THEN perform create_country(NEW.name, lower(NEW.country_code)); --DEBUG: RAISE WARNING 'Country names updated'; END IF; @@ -1940,7 +1947,7 @@ BEGIN IF NEW.address is not null AND NEW.address ? 'postcode' AND NEW.address->'postcode' not similar to '%(,|;)%' THEN - NEW.postcode := NEW.address->'postcode'; + NEW.postcode := upper(trim(NEW.address->'postcode')); END IF; IF NEW.postcode is null AND NEW.rank_search > 8 THEN @@ -1951,7 +1958,7 @@ BEGIN IF NEW.name IS NOT NULL THEN IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN - result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.address->'postcode', NEW.geometry); + result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry); --DEBUG: RAISE WARNING 'added to location (full)'; END IF; @@ -2193,7 +2200,9 @@ BEGIN -- To paraphrase, if there isn't an existing item, OR if the admin level has changed IF existingplacex.osm_type IS NULL OR - (coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.class = 'boundary' AND existingplacex.type = 'administrative') + (existingplacex.class = 'boundary' AND + ((coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.type = 'administrative') OR + (existingplacex.type != NEW.type))) THEN IF existingplacex.osm_type IS NOT NULL THEN @@ -2431,11 +2440,19 @@ BEGIN -- %NOAUXDATA% IF 0 THEN IF for_place_id IS NULL THEN select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux - WHERE place_id = in_place_id + WHERE place_id = in_place_id INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype; END IF; -- %NOAUXDATA% END IF; + -- postcode table + IF for_place_id IS NULL THEN + select parent_place_id, country_code, rank_address, postcode, 'place', 'postcode' + FROM location_postcode + WHERE place_id = in_place_id + INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode, searchclass, searchtype; + END IF; + IF for_place_id IS NULL THEN select parent_place_id, country_code, housenumber, rank_search, postcode, name, class, type from placex WHERE place_id = in_place_id and rank_search > 27 @@ -2494,7 +2511,7 @@ BEGIN from place_addressline join placex on (address_place_id = placex.place_id) where place_addressline.place_id = for_place_id and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress) - and address_place_id != for_place_id + and address_place_id != for_place_id and linked_place_id is null and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode) order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc LOOP