X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/c50c534d19889d7cdea46049d1214a0081a8dcb1..ed7f0d9e46cf6ecd8c3182e02156ff0e4d1cd864:/lib-sql/functions/interpolation.sql?ds=inline diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql index 64775678..b0797df2 100644 --- a/lib-sql/functions/interpolation.sql +++ b/lib-sql/functions/interpolation.sql @@ -15,7 +15,7 @@ DECLARE location RECORD; waynodes BIGINT[]; BEGIN - IF akeys(in_address) != ARRAY['interpolation'] THEN + IF in_address ? 'street' or in_address ? 'place' THEN RETURN in_address; END IF; @@ -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,9 +160,8 @@ DECLARE newend INTEGER; moddiff SMALLINT; linegeo GEOMETRY; - splitline GEOMETRY; + splitpoint FLOAT; sectiongeo GEOMETRY; - interpol_postcode TEXT; postcode TEXT; stepmod SMALLINT; BEGIN @@ -174,7 +179,12 @@ BEGIN ST_PointOnSurface(NEW.linegeo), NEW.linegeo); - interpol_postcode := token_normalized_postcode(NEW.address->'postcode'); + -- 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 @@ -207,19 +217,37 @@ BEGIN FOR nextnode IN SELECT DISTINCT ON (nodeidpos) osm_id, address, geometry, - substring(address->'housenumber','[0-9]+')::integer as hnr + -- Take the postcode from the node only if it has a housenumber itself. + -- Note that there is a corner-case where the node has a wrongly + -- formatted postcode and therefore 'postcode' contains a derived + -- variant. + CASE WHEN address ? 'postcode' THEN placex.postcode ELSE NULL::text END as postcode, + (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 - RAISE WARNING 'processing point % (%)', nextnode.hnr, ST_AsText(nextnode.geometry); + {% 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 @@ -227,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; @@ -260,13 +291,10 @@ BEGIN 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; + postcode := coalesce(prevnode.postcode, nextnode.postcode, postcode); + IF postcode is NULL and NEW.parent_place_id > 0 THEN + SELECT 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); @@ -291,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;