X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/dddfa3a075f11145432b69ec053f8145dce9612b..330b8d2fdfdd935da123abf9ecf96b3bc7a12be5:/lib-sql/functions/interpolation.sql diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql index 96a105ae..b0797df2 100644 --- a/lib-sql/functions/interpolation.sql +++ b/lib-sql/functions/interpolation.sql @@ -52,7 +52,9 @@ BEGIN IF parent_place_id is null THEN FOR location IN SELECT place_id FROM placex - WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26 + WHERE ST_DWithin(geom, placex.geometry, 0.001) + and placex.rank_search = 26 + and placex.osm_type = 'W' -- needed for index selection ORDER BY CASE WHEN ST_GeometryType(geom) = 'ST_Line' THEN (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+ ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+ @@ -65,10 +67,6 @@ BEGIN END LOOP; END IF; - IF parent_place_id is null THEN - RETURN 0; - END IF; - RETURN parent_place_id; END; $$ @@ -82,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; @@ -154,7 +160,7 @@ DECLARE newend INTEGER; moddiff SMALLINT; linegeo GEOMETRY; - splitline GEOMETRY; + splitpoint FLOAT; sectiongeo GEOMETRY; postcode TEXT; stepmod SMALLINT; @@ -173,6 +179,13 @@ BEGIN ST_PointOnSurface(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 NEW.address := hstore('interpolation', NEW.address->'interpolation'); @@ -209,19 +222,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 @@ -229,6 +255,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; @@ -290,12 +319,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;