X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/051f3720ce142b7bc70c4d7e30f28cf9db106d88..6415c9cf95f24811744d3af91ab99d996faf4738:/lib-sql/functions/interpolation.sql diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql index fb822033..2fd21e8a 100644 --- a/lib-sql/functions/interpolation.sql +++ b/lib-sql/functions/interpolation.sql @@ -67,10 +67,6 @@ BEGIN END LOOP; END IF; - IF parent_place_id is null THEN - RETURN 0; - END IF; - RETURN parent_place_id; END; $$ @@ -84,27 +80,35 @@ CREATE OR REPLACE FUNCTION reinsert_interpolation(way_id BIGINT, addr HSTORE, DECLARE existing BIGINT[]; BEGIN - -- 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); + IF addr is NULL OR NOT addr ? 'interpolation' + OR NOT (addr->'interpolation' in ('odd', 'even', 'all') + or addr->'interpolation' similar to '[1-9]') + THEN + -- the new interpolation is illegal, simply remove existing entries + DELETE FROM location_property_osmline WHERE osm_id = way_id; 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:]); + -- 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; END IF; @@ -117,6 +121,8 @@ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION osmline_insert() RETURNS TRIGGER AS $$ +DECLARE + centroid GEOMETRY; BEGIN NEW.place_id := nextval('seq_place'); NEW.indexed_date := now(); @@ -131,10 +137,11 @@ BEGIN END IF; NEW.indexed_status := 1; --STATUS_NEW - NEW.country_code := lower(get_country_code(NEW.linegeo)); + centroid := get_center_point(NEW.linegeo); + NEW.country_code := lower(get_country_code(centroid)); NEW.partition := get_partition(NEW.country_code); - NEW.geometry_sector := geometry_sector(NEW.partition, NEW.linegeo); + NEW.geometry_sector := geometry_sector(NEW.partition, centroid); END IF; RETURN NEW; @@ -156,7 +163,7 @@ DECLARE newend INTEGER; moddiff SMALLINT; linegeo GEOMETRY; - splitline GEOMETRY; + splitpoint FLOAT; sectiongeo GEOMETRY; postcode TEXT; stepmod SMALLINT; @@ -172,8 +179,15 @@ BEGIN END IF; NEW.parent_place_id := get_interpolation_parent(NEW.token_info, NEW.partition, - ST_PointOnSurface(NEW.linegeo), - NEW.linegeo); + get_center_point(NEW.linegeo), + NEW.linegeo); + + -- Cannot find a parent street. We will not be able to display a reliable + -- address, so drop entire interpolation. + IF NEW.parent_place_id is NULL THEN + DELETE FROM location_property_osmline where place_id = OLD.place_id; + RETURN NULL; + END IF; NEW.token_info := token_strip_info(NEW.token_info); IF NEW.address ? '_inherited' THEN @@ -211,19 +225,32 @@ BEGIN -- formatted postcode and therefore 'postcode' contains a derived -- variant. CASE WHEN address ? 'postcode' THEN placex.postcode ELSE NULL::text END as postcode, - substring(address->'housenumber','[0-9]+')::integer as hnr + (address->'housenumber')::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' + and address->'housenumber' ~ '^[0-9]{1,6}$' + and ST_Distance(NEW.linegeo, geometry) < 0.0005 ORDER BY nodeidpos LOOP {% if debug %}RAISE WARNING 'processing point % (%)', nextnode.hnr, ST_AsText(nextnode.geometry);{% endif %} 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); + splitpoint := ST_LineLocatePoint(linegeo, nextnode.geometry); + IF splitpoint = 0 THEN + -- Corner case where the splitpoint falls on the first point + -- and thus would not return a geometry. Skip that section. + sectiongeo := NULL; + ELSEIF splitpoint = 1 THEN + -- Point is at the end of the line. + sectiongeo := linegeo; + linegeo := NULL; + ELSE + -- Split the line. + sectiongeo := ST_LineSubstring(linegeo, 0, splitpoint); + linegeo := ST_LineSubstring(linegeo, splitpoint, 1); + END IF; END IF; IF prevnode.hnr is not null @@ -231,6 +258,9 @@ BEGIN -- regularly mapped housenumbers. -- (Conveniently also fails if one of the house numbers is not a number.) and abs(prevnode.hnr - nextnode.hnr) > NEW.step + -- If the interpolation geometry is broken or two nodes are at the + -- same place, then splitting might produce a point. Ignore that. + and ST_GeometryType(sectiongeo) = 'ST_LineString' THEN IF prevnode.hnr < nextnode.hnr THEN startnumber := prevnode.hnr; @@ -292,12 +322,12 @@ BEGIN NEW.address, postcode, NEW.country_code, NEW.geometry_sector, 0); END IF; + 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; + -- early break if we are out of line string, + -- might happen when a line string loops back on itself + IF linegeo is null or ST_GeometryType(linegeo) != 'ST_LineString' THEN + RETURN NEW; END IF; prevnode := nextnode;