X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/3d0f8bdc39d08c00b57ac18fc6483a6774129bb4..2e6ff1b7508e738c1ecb4647aa5890422b771121:/lib-sql/functions/placex_triggers.sql diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 6143a1ed..367d2149 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -16,7 +16,9 @@ CREATE TYPE prepare_update_info AS ( country_code TEXT, class TEXT, type TEXT, - linked_place_id BIGINT + linked_place_id BIGINT, + centroid_x float, + centroid_y float ); -- Retrieve the data needed by the indexer for updating the place. @@ -47,7 +49,7 @@ BEGIN and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') LIMIT 1; ELSE - -- See if we can inherit addtional address tags from an interpolation. + -- See if we can inherit additional address tags from an interpolation. -- These will become permanent. FOR location IN SELECT (address - 'interpolation'::text - 'housenumber'::text) as address @@ -71,6 +73,8 @@ BEGIN result.type := p.type; result.country_code := p.country_code; result.rank_address := p.rank_address; + result.centroid_x := ST_X(p.centroid); + result.centroid_y := ST_Y(p.centroid); -- Names of linked places need to be merged in, so search for a linkable -- place already here. @@ -193,6 +197,7 @@ BEGIN SELECT place_id FROM placex WHERE bbox && geometry AND _ST_Covers(geometry, ST_Centroid(bbox)) AND rank_address between 5 and 25 + AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') ORDER BY rank_address desc LOOP RETURN location.place_id; @@ -208,6 +213,7 @@ BEGIN SELECT place_id FROM placex WHERE bbox && geometry AND _ST_Covers(geometry, ST_Centroid(bbox)) AND rank_address between 5 and 25 + AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') ORDER BY rank_address desc LOOP RETURN location.place_id; @@ -271,7 +277,9 @@ BEGIN -- If extratags has a place tag, look for linked nodes by their place type. -- Area and node still have to have the same name. - IF bnd.extratags ? 'place' and bnd_name is not null THEN + IF bnd.extratags ? 'place' and bnd.extratags->'place' != 'postcode' + and bnd_name is not null + THEN FOR linked_placex IN SELECT * FROM placex WHERE (position(lower(name->'name') in bnd_name) > 0 @@ -280,7 +288,6 @@ BEGIN AND placex.osm_type = 'N' AND (placex.linked_place_id is null or placex.linked_place_id = bnd.place_id) AND placex.rank_search < 26 -- needed to select the right index - AND placex.type != 'postcode' AND ST_Covers(bnd.geometry, placex.geometry) LOOP {% if debug %}RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;{% endif %} @@ -842,7 +849,8 @@ BEGIN FROM placex WHERE osm_type = 'R' and class = 'boundary' and type = 'administrative' and admin_level < NEW.admin_level and admin_level > 3 - and rank_address > 0 + and rank_address between 1 and 25 -- for index selection + and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- for index selection and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid) ORDER BY admin_level desc LIMIT 1 LOOP @@ -870,8 +878,9 @@ BEGIN FROM placex, LATERAL compute_place_rank(country_code, 'A', class, type, admin_level, False, null) prank - WHERE class = 'place' and rank_address < 24 + WHERE class = 'place' and rank_address between 1 and 23 and prank.address_rank >= NEW.rank_address + and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index and geometry && NEW.geometry and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal @@ -892,6 +901,8 @@ BEGIN LATERAL compute_place_rank(country_code, 'A', class, type, admin_level, False, null) prank WHERE prank.address_rank < 24 + and rank_address between 1 and 25 -- select right index + and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index and prank.address_rank >= NEW.rank_address and geometry && NEW.geometry and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test @@ -912,7 +923,10 @@ BEGIN LATERAL compute_place_rank(country_code, 'A', class, type, admin_level, False, null) prank WHERE osm_type = 'R' - and prank.address_rank = NEW.rank_address + and rank_address between 1 and 25 -- select right index + and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index + and ((class = 'place' and prank.address_rank = NEW.rank_address) + or (class = 'boundary' and rank_address = NEW.rank_address)) and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid) LIMIT 1 LOOP @@ -951,7 +965,7 @@ BEGIN NEW.importance := null; SELECT wikipedia, importance - FROM compute_importance(NEW.extratags, NEW.country_code, NEW.osm_type, NEW.osm_id) + FROM compute_importance(NEW.extratags, NEW.country_code, NEW.rank_search, NEW.centroid) INTO NEW.wikipedia,NEW.importance; {% if debug %}RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance;{% endif %} @@ -992,7 +1006,7 @@ BEGIN {% if debug %}RAISE WARNING 'Got parent details from search name';{% endif %} -- determine postcode - NEW.postcode := coalesce(token_normalized_postcode(NEW.address->'postcode'), + NEW.postcode := coalesce(token_get_postcode(NEW.token_info), location.postcode, get_nearest_postcode(NEW.country_code, NEW.centroid)); @@ -1032,8 +1046,8 @@ BEGIN {% if debug %}RAISE WARNING 'Using full index mode for % %', NEW.osm_type, NEW.osm_id;{% endif %} IF linked_place is not null THEN -- Recompute the ranks here as the ones from the linked place might - -- have been shifted to accomodate surrounding boundaries. - SELECT place_id, osm_id, class, type, extratags, + -- have been shifted to accommodate surrounding boundaries. + SELECT place_id, osm_id, class, type, extratags, rank_search, centroid, geometry, (compute_place_rank(country_code, osm_type, class, type, admin_level, (extratags->'capital') = 'yes', null)).* @@ -1074,7 +1088,7 @@ BEGIN SELECT wikipedia, importance FROM compute_importance(location.extratags, NEW.country_code, - 'N', location.osm_id) + location.rank_search, NEW.centroid) INTO linked_wikipedia,linked_importance; -- Use the maximum importance if one could be computed from the linked object. @@ -1097,13 +1111,22 @@ BEGIN END IF; END IF; + {% if not disable_diff_updates %} + IF OLD.rank_address != NEW.rank_address THEN + -- After a rank shift all addresses containing us must be updated. + UPDATE placex p SET indexed_status = 2 FROM place_addressline pa + WHERE pa.address_place_id = NEW.place_id and p.place_id = pa.place_id + and p.indexed_status = 0 and p.rank_address between 4 and 25; + END IF; + {% endif %} + IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R' THEN -- Update the list of country names. -- Only take the name from the largest area for the given country code - -- in the hope that this is the authoritive one. + -- in the hope that this is the authoritative one. -- Also replace any old names so that all mapping mistakes can -- be fixed through regular OSM updates. FOR location IN @@ -1150,8 +1173,7 @@ BEGIN {% if debug %}RAISE WARNING 'RETURN insert_addresslines: %, %, %', NEW.parent_place_id, NEW.postcode, nameaddress_vector;{% endif %} - NEW.postcode := coalesce(token_normalized_postcode(NEW.address->'postcode'), - NEW.postcode); + NEW.postcode := coalesce(token_get_postcode(NEW.token_info), NEW.postcode); -- if we have a name add this to the name search table IF NEW.name IS NOT NULL THEN @@ -1192,7 +1214,7 @@ BEGIN NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry); END IF; - {% if debug %}RAISE WARNING 'place update % % finsihed.', NEW.osm_type, NEW.osm_id;{% endif %} + {% if debug %}RAISE WARNING 'place update % % finished.', NEW.osm_type, NEW.osm_id;{% endif %} NEW.token_info := token_strip_info(NEW.token_info); RETURN NEW;