X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/4b28b4fed469ef5826e61633f82b93a29e1c9b3f..189f74a40de0f0f7ff1346a66d9beffe026d9e23:/lib-sql/functions/interpolation.sql diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql index 64775678..9bb91021 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))+ @@ -82,27 +84,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; @@ -156,7 +166,6 @@ DECLARE linegeo GEOMETRY; splitline GEOMETRY; sectiongeo GEOMETRY; - interpol_postcode TEXT; postcode TEXT; stepmod SMALLINT; BEGIN @@ -174,8 +183,6 @@ BEGIN 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.address->'interpolation'); @@ -207,13 +214,18 @@ BEGIN FOR nextnode IN SELECT DISTINCT ON (nodeidpos) osm_id, address, geometry, + -- 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, 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 debug %}RAISE WARNING 'processing point % (%)', nextnode.hnr, ST_AsText(nextnode.geometry);{% endif %} IF linegeo is null THEN linegeo := NEW.linegeo; ELSE @@ -260,13 +272,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);