From fea4dbba50d5a53ef982b1eb273e0bb81a2cd036 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Wed, 26 Jan 2022 12:05:04 +0100 Subject: [PATCH] inherit tags from interpolation not parent Nodes on an interpolation now only get the address tags of interpolations and then compute their own parent from that. They no longer inherit the parent directly. --- lib-sql/functions/interpolation.sql | 221 ++++++++++++++------------ lib-sql/functions/placex_triggers.sql | 29 ++-- lib-sql/tables.sql | 2 +- 3 files changed, 140 insertions(+), 112 deletions(-) diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql index 098db26c..c0181556 100644 --- a/lib-sql/functions/interpolation.sql +++ b/lib-sql/functions/interpolation.sql @@ -7,17 +7,6 @@ -- Functions for address interpolation objects in location_property_osmline. --- Splits the line at the given point and returns the two parts --- in a multilinestring. -CREATE OR REPLACE FUNCTION split_line_on_node(line GEOMETRY, point GEOMETRY) -RETURNS GEOMETRY - AS $$ -BEGIN - RETURN ST_Split(ST_Snap(line, point, 0.0005), point); -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - CREATE OR REPLACE FUNCTION get_interpolation_address(in_address HSTORE, wayid BIGINT) RETURNS HSTORE @@ -128,8 +117,10 @@ BEGIN IF NEW.indexed_status IS NULL THEN IF NEW.address is NULL OR NOT NEW.address ? 'interpolation' - OR NEW.address->'interpolation' NOT IN ('odd', 'even', 'all') THEN - -- other interpolation types than odd/even/all (e.g. numeric ones) are not supported + OR NOT (NEW.address->'interpolation' in ('odd', 'even', 'all') + or NEW.address->'interpolation' similar to '[1-9]') + THEN + -- alphabetic interpolation is not supported RETURN NULL; END IF; @@ -150,18 +141,20 @@ CREATE OR REPLACE FUNCTION osmline_update() RETURNS TRIGGER AS $$ DECLARE - place_centroid GEOMETRY; waynodes BIGINT[]; prevnode RECORD; nextnode RECORD; startnumber INTEGER; endnumber INTEGER; - housenum INTEGER; + newstart INTEGER; + newend INTEGER; + moddiff SMALLINT; linegeo GEOMETRY; splitline GEOMETRY; sectiongeo GEOMETRY; interpol_postcode TEXT; postcode TEXT; + stepmod SMALLINT; BEGIN -- deferred delete IF OLD.indexed_status = 100 THEN @@ -173,107 +166,139 @@ BEGIN RETURN NEW; END IF; - NEW.interpolationtype = NEW.address->'interpolation'; - - place_centroid := ST_PointOnSurface(NEW.linegeo); - NEW.parent_place_id = get_interpolation_parent(NEW.token_info, NEW.partition, - place_centroid, NEW.linegeo); + NEW.parent_place_id := get_interpolation_parent(NEW.token_info, NEW.partition, + ST_PointOnSurface(NEW.linegeo), + NEW.linegeo); 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); + NEW.address := hstore('interpolation', NEW.address->'interpolation'); END IF; - -- if the line was newly inserted, split the line as necessary + -- If the line was newly inserted, split the line as necessary. IF OLD.indexed_status = 1 THEN - select nodes from planet_osm_ways where id = NEW.osm_id INTO waynodes; + IF NEW.address->'interpolation' in ('odd', 'even') THEN + NEW.step := 2; + stepmod := CASE WHEN NEW.address->'interpolation' = 'odd' THEN 1 ELSE 0 END; + ELSE + NEW.step := CASE WHEN NEW.address->'interpolation' = 'all' + THEN 1 + ELSE (NEW.address->'interpolation')::SMALLINT END; + stepmod := NULL; + END IF; + + SELECT nodes INTO waynodes + FROM planet_osm_ways WHERE id = NEW.osm_id; - IF array_upper(waynodes, 1) IS NULL THEN - RETURN NEW; + IF array_upper(waynodes, 1) IS NULL THEN + RETURN NEW; + END IF; + + linegeo := null; + SELECT null::integer as hnr INTO prevnode; + + -- Go through all nodes on the interpolation line that have a housenumber. + FOR nextnode IN + SELECT DISTINCT ON (nodeidpos) + osm_id, address, geometry, + substring(address->'housenumber','[0-9]+')::integer as hnr + FROM placex, generate_series(1, array_upper(waynodes, 1)) nodeidpos + WHERE osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT + and address is not NULL and address ? 'housenumber' + ORDER BY nodeidpos + LOOP + RAISE WARNING 'processing point % (%)', nextnode.hnr, ST_AsText(nextnode.geometry); + IF linegeo is null THEN + linegeo := NEW.linegeo; + ELSE + splitline := ST_Split(ST_Snap(linegeo, nextnode.geometry, 0.0005), nextnode.geometry); + sectiongeo := ST_GeometryN(splitline, 1); + linegeo := ST_GeometryN(splitline, 2); END IF; - linegeo := NEW.linegeo; - startnumber := NULL; - - FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP - - select osm_id, address, geometry - from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT - and address is not NULL and address ? 'housenumber' limit 1 INTO nextnode; - --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id; - IF nextnode.osm_id IS NOT NULL THEN - --RAISE NOTICE 'place_id is not null'; - IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN - -- Make sure that the point is actually on the line. That might - -- be a bit paranoid but ensures that the algorithm still works - -- should osm2pgsql attempt to repair geometries. - splitline := split_line_on_node(linegeo, nextnode.geometry); - sectiongeo := ST_GeometryN(splitline, 1); - linegeo := ST_GeometryN(splitline, 2); + IF prevnode.hnr is not null + -- Check if there are housenumbers to interpolate between the + -- regularly mapped housenumbers. + -- (Conveniently also fails if one of the house numbers is not a number.) + and abs(prevnode.hnr - nextnode.hnr) > NEW.step + THEN + IF prevnode.hnr < nextnode.hnr THEN + startnumber := prevnode.hnr; + endnumber := nextnode.hnr; + ELSE + startnumber := nextnode.hnr; + endnumber := prevnode.hnr; + sectiongeo := ST_Reverse(sectiongeo); + END IF; + + -- Adjust the interpolation, so that only inner housenumbers + -- are taken into account. + IF stepmod is null THEN + newstart := startnumber + NEW.step; + ELSE + newstart := startnumber + 1; + moddiff := newstart % NEW.step - stepmod; + IF moddiff < 0 THEN + newstart := newstart + (NEW.step + moddiff); ELSE - sectiongeo = linegeo; - END IF; - endnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer; - - IF startnumber IS NOT NULL AND endnumber IS NOT NULL - AND startnumber != endnumber - AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN - - IF (startnumber > endnumber) THEN - housenum := endnumber; - endnumber := startnumber; - startnumber := housenum; - sectiongeo := ST_Reverse(sectiongeo); - END IF; - - -- determine postcode - postcode := coalesce(interpol_postcode, - token_normalized_postcode(prevnode.address->'postcode'), - token_normalized_postcode(nextnode.address->'postcode'), - postcode); - - IF postcode is NULL THEN - 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); - END IF; - - IF NEW.startnumber IS NULL THEN - NEW.startnumber := startnumber; - NEW.endnumber := endnumber; - NEW.linegeo := sectiongeo; - NEW.postcode := postcode; - ELSE - insert into location_property_osmline - (linegeo, partition, osm_id, parent_place_id, - startnumber, endnumber, interpolationtype, - address, postcode, country_code, - geometry_sector, indexed_status) - values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id, - startnumber, endnumber, NEW.interpolationtype, - NEW.address, postcode, - NEW.country_code, NEW.geometry_sector, 0); - END IF; + newstart := newstart + moddiff; END IF; + END IF; + newend := newstart + ((endnumber - 1 - newstart) / NEW.step) * NEW.step; + + -- If newstart and newend are the same, then this returns a point. + sectiongeo := ST_LineSubstring(sectiongeo, + (newstart - startnumber)::float / (endnumber - startnumber)::float, + (newend - startnumber)::float / (endnumber - startnumber)::float); + startnumber := newstart; + endnumber := newend; + + -- determine postcode + postcode := coalesce(interpol_postcode, + token_normalized_postcode(prevnode.address->'postcode'), + token_normalized_postcode(nextnode.address->'postcode'), + postcode); + IF postcode is NULL THEN + 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); + END IF; - -- early break if we are out of line string, - -- might happen when a line string loops back on itself - IF ST_GeometryType(linegeo) != 'ST_LineString' THEN - RETURN NEW; - END IF; + -- Add the interpolation. If this is the first segment, just modify + -- the interpolation to be inserted, otherwise add an additional one + -- (marking it indexed already). + IF NEW.startnumber IS NULL THEN + NEW.startnumber := startnumber; + NEW.endnumber := endnumber; + NEW.linegeo := sectiongeo; + NEW.postcode := postcode; + ELSE + INSERT INTO location_property_osmline + (linegeo, partition, osm_id, parent_place_id, + startnumber, endnumber, step, + address, postcode, country_code, + geometry_sector, indexed_status) + VALUES (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id, + startnumber, endnumber, NEW.step, + NEW.address, postcode, + NEW.country_code, NEW.geometry_sector, 0); + END IF; - startnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer; - prevnode := nextnode; + -- early break if we are out of line string, + -- might happen when a line string loops back on itself + IF ST_GeometryType(linegeo) != 'ST_LineString' THEN + RETURN NEW; END IF; - END LOOP; + END IF; + + prevnode := nextnode; + END LOOP; END IF; - -- marking descendants for reparenting is not needed, because there are - -- actually no descendants for interpolation lines RETURN NEW; END; $$ diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 5db76b7b..6ab73a3b 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -29,9 +29,9 @@ DECLARE BEGIN -- For POI nodes, check if the address should be derived from a surrounding -- building. - IF p.rank_search < 30 OR p.osm_type != 'N' OR p.address is not null THEN + IF p.rank_search < 30 OR p.osm_type != 'N' THEN result.address := p.address; - ELSE + ELSEIF p.address is null THEN -- The additional && condition works around the misguided query -- planner of postgis 3.0. SELECT placex.address || hstore('_inherited', '') INTO result.address @@ -42,6 +42,20 @@ BEGIN and (placex.address ? 'housenumber' or placex.address ? 'street' or placex.address ? 'place') and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') LIMIT 1; + ELSE + result.address := p.address; + -- See if we can inherit addtional address tags from an interpolation. + -- These will become permanent. + FOR location IN + SELECT (address - 'interpolation'::text - 'housenumber'::text) as address + FROM place, planet_osm_ways w + WHERE place.osm_type = 'W' and place.address ? 'interpolation' + and place.geometry && p.geometry + and place.osm_id = w.id + and p.osm_id = any(w.nodes) + LOOP + result.address := location.address || result.address; + END LOOP; END IF; result.address := result.address - '_unlisted_place'::TEXT; @@ -131,17 +145,6 @@ BEGIN END IF; IF parent_place_id is null and poi_osm_type = 'N' THEN - -- Is this node part of an interpolation? - FOR location IN - SELECT q.parent_place_id - FROM location_property_osmline q, planet_osm_ways x - WHERE q.linegeo && bbox and startnumber is not null - and x.id = q.osm_id and poi_osm_id = any(x.nodes) - LOOP - {% if debug %}RAISE WARNING 'Get parent from interpolation: %', location.parent_place_id;{% endif %} - RETURN location.parent_place_id; - END LOOP; - FOR location IN SELECT p.place_id, p.osm_id, p.rank_search, p.address, coalesce(p.centroid, ST_Centroid(p.geometry)) as centroid diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index 10713661..0c0f78fc 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -95,10 +95,10 @@ CREATE TABLE location_property_osmline ( indexed_date TIMESTAMP, startnumber INTEGER, endnumber INTEGER, + step SMALLINT, partition SMALLINT, indexed_status SMALLINT, linegeo GEOMETRY, - interpolationtype TEXT, address HSTORE, token_info JSONB, -- custom column for tokenizer use only postcode TEXT, -- 2.39.5