+-- 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
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
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;
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
-- 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);
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,
projects / nominatim.git / blobdiff