X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/6a7e0d652b1d40a397e1c1386d500101796676c4..83d2c440d51d3e1e29b51397b5a6c82856e285af:/lib-sql/functions/interpolation.sql diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql index a797cad3..098db26c 100644 --- a/lib-sql/functions/interpolation.sql +++ b/lib-sql/functions/interpolation.sql @@ -1,3 +1,10 @@ +-- SPDX-License-Identifier: GPL-2.0-only +-- +-- This file is part of Nominatim. (https://nominatim.org) +-- +-- Copyright (C) 2022 by the Nominatim developer community. +-- For a full list of authors see the git log. + -- Functions for address interpolation objects in location_property_osmline. -- Splits the line at the given point and returns the two parts @@ -12,39 +19,47 @@ $$ LANGUAGE plpgsql IMMUTABLE; +CREATE OR REPLACE FUNCTION get_interpolation_address(in_address HSTORE, wayid BIGINT) +RETURNS HSTORE + AS $$ +DECLARE + location RECORD; + waynodes BIGINT[]; +BEGIN + IF akeys(in_address) != ARRAY['interpolation'] THEN + RETURN in_address; + END IF; + + SELECT nodes INTO waynodes FROM planet_osm_ways WHERE id = wayid; + FOR location IN + SELECT placex.address, placex.osm_id FROM placex + WHERE osm_type = 'N' and osm_id = ANY(waynodes) + and placex.address is not null + and (placex.address ? 'street' or placex.address ? 'place') + and indexed_status < 100 + LOOP + -- mark it as a derived address + RETURN location.address || in_address || hstore('_inherited', ''); + END LOOP; + + RETURN in_address; +END; +$$ +LANGUAGE plpgsql STABLE; + + + -- find the parent road of the cut road parts -CREATE OR REPLACE FUNCTION get_interpolation_parent(wayid BIGINT, street TEXT, - place TEXT, partition SMALLINT, +CREATE OR REPLACE FUNCTION get_interpolation_parent(token_info JSONB, + partition SMALLINT, centroid GEOMETRY, geom GEOMETRY) RETURNS BIGINT AS $$ DECLARE - addr_street TEXT; - addr_place TEXT; parent_place_id BIGINT; - - waynodes BIGINT[]; - location RECORD; BEGIN - addr_street = street; - addr_place = place; - - IF addr_street is null and addr_place is null THEN - select nodes from planet_osm_ways where id = wayid INTO waynodes; - FOR location IN SELECT placex.address from placex - where osm_type = 'N' and osm_id = ANY(waynodes) - and placex.address is not null - and (placex.address ? 'street' or placex.address ? 'place') - and indexed_status < 100 - limit 1 LOOP - addr_street = location.address->'street'; - addr_place = location.address->'place'; - END LOOP; - END IF; - - parent_place_id := find_parent_for_address(addr_street, addr_place, - partition, centroid); + parent_place_id := find_parent_for_address(token_info, partition, centroid); IF parent_place_id is null THEN FOR location IN SELECT place_id FROM placex @@ -67,24 +82,38 @@ $$ LANGUAGE plpgsql STABLE; -CREATE OR REPLACE FUNCTION osmline_reinsert(node_id BIGINT, geom GEOMETRY) - RETURNS BOOLEAN +CREATE OR REPLACE FUNCTION reinsert_interpolation(way_id BIGINT, addr HSTORE, + geom GEOMETRY) + RETURNS INT AS $$ DECLARE - existingline RECORD; + existing BIGINT[]; BEGIN - SELECT w.id FROM planet_osm_ways w, location_property_osmline p - WHERE p.linegeo && geom and p.osm_id = w.id and p.indexed_status = 0 - and node_id = any(w.nodes) INTO existingline; - - IF existingline.id is not NULL THEN - DELETE FROM location_property_osmline WHERE osm_id = existingline.id; - INSERT INTO location_property_osmline (osm_id, address, linegeo) - SELECT osm_id, address, geometry FROM place - WHERE osm_type = 'W' and osm_id = existingline.id; - END IF; - - RETURN true; + -- Get the existing entry from the interpolation table. + SELECT array_agg(place_id) INTO existing + FROM location_property_osmline WHERE osm_id = way_id; + + IF existing IS NULL or array_length(existing, 1) = 0 THEN + INSERT INTO location_property_osmline (osm_id, address, linegeo) + VALUES (way_id, addr, geom); + ELSE + -- Update the interpolation table: + -- The first entry gets the original data, all other entries + -- are removed and will be recreated on indexing. + -- (An interpolation can be split up, if it has more than 2 address nodes) + UPDATE location_property_osmline + SET address = addr, + linegeo = geom, + startnumber = null, + indexed_status = 1 + WHERE place_id = existing[1]; + IF array_length(existing, 1) > 1 THEN + DELETE FROM location_property_osmline + WHERE place_id = any(existing[2:]); + END IF; + END IF; + + RETURN 1; END; $$ LANGUAGE plpgsql; @@ -147,15 +176,14 @@ BEGIN NEW.interpolationtype = NEW.address->'interpolation'; place_centroid := ST_PointOnSurface(NEW.linegeo); - NEW.parent_place_id = get_interpolation_parent(NEW.osm_id, NEW.address->'street', - NEW.address->'place', - NEW.partition, place_centroid, NEW.linegeo); + NEW.parent_place_id = get_interpolation_parent(NEW.token_info, NEW.partition, + place_centroid, NEW.linegeo); - 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; + interpol_postcode := token_normalized_postcode(NEW.address->'postcode'); + + NEW.token_info := token_strip_info(NEW.token_info); + IF NEW.address ? '_inherited' THEN + NEW.address := hstore('interpolation', NEW.interpolationtype); END IF; -- if the line was newly inserted, split the line as necessary @@ -202,12 +230,13 @@ BEGIN -- determine postcode postcode := coalesce(interpol_postcode, - prevnode.address->'postcode', - nextnode.address->'postcode', + token_normalized_postcode(prevnode.address->'postcode'), + token_normalized_postcode(nextnode.address->'postcode'), postcode); IF postcode is NULL THEN - SELECT placex.postcode FROM placex WHERE place_id = NEW.parent_place_id INTO postcode; + SELECT token_normalized_postcode(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); @@ -217,7 +246,7 @@ BEGIN NEW.startnumber := startnumber; NEW.endnumber := endnumber; NEW.linegeo := sectiongeo; - NEW.postcode := upper(trim(postcode)); + NEW.postcode := postcode; ELSE insert into location_property_osmline (linegeo, partition, osm_id, parent_place_id,