X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/efddd1339de9876e4f1681ee0629bb09e76ae1a1..47714357cb800c69d006b3a868356bf80d09549a:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index 54e2a8d3..5d2b7a22 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -547,7 +547,7 @@ BEGIN -- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre); -- Try for a OSM polygon - FOR nearcountry IN select country_code from location_area_country where country_code is not null and not isguess and st_covers(geometry, place_centre) limit 1 + FOR nearcountry IN select country_code from location_area_country where country_code is not null and st_covers(geometry, place_centre) limit 1 LOOP RETURN nearcountry.country_code; END LOOP; @@ -768,6 +768,28 @@ END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION osmline_reinsert(node_id BIGINT, geom GEOMETRY) + RETURNS BOOLEAN + AS $$ +DECLARE + existingline RECORD; +BEGIN + SELECT w.id FROM planet_osm_ways w, location_property_osmline p + WHERE p.linegeo && geom and p.osm_id = w.id and p.indexed_status = 0 + and node_id = any(w.nodes) INTO existingline; + + IF existingline.id is not NULL THEN + DELETE FROM location_property_osmline WHERE osm_id = existingline.id; + INSERT INTO location_property_osmline (osm_id, address, linegeo) + SELECT osm_id, address, geometry FROM place + WHERE osm_type = 'W' and osm_id = existingline.id; + END IF; + + RETURN true; +END; +$$ +LANGUAGE plpgsql; + CREATE OR REPLACE FUNCTION osmline_insert() RETURNS TRIGGER AS $$ @@ -887,11 +909,7 @@ BEGIN END IF; -- some postcorrections - IF NEW.class = 'place' THEN - IF NEW.type in ('continent', 'sea', 'country', 'state') AND NEW.osm_type = 'N' THEN - NEW.rank_address := 0; - END IF; - ELSEIF NEW.class = 'waterway' AND NEW.osm_type = 'R' THEN + IF NEW.class = 'waterway' AND NEW.osm_type = 'R' THEN -- Slightly promote waterway relations so that they are processed -- before their members. NEW.rank_search := NEW.rank_search - 1; @@ -908,16 +926,14 @@ BEGIN NEW.country_code := NULL; END IF; --- Block import below rank 22 --- IF NEW.rank_search > 22 THEN --- RETURN NULL; --- END IF; - --DEBUG: RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type; RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down - IF NEW.rank_address > 0 THEN + IF NEW.osm_type = 'N' and NEW.rank_search > 28 THEN + -- might be part of an interpolation + result := osmline_reinsert(NEW.osm_id, NEW.geometry); + ELSEIF NEW.rank_address > 0 THEN IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN -- Performance: We just can't handle re-indexing for country level changes IF st_area(NEW.geometry) < 1 THEN @@ -1162,6 +1178,7 @@ TRIGGER DECLARE place_centroid GEOMETRY; + near_centroid GEOMETRY; search_maxdistance FLOAT[]; search_mindistance FLOAT[]; @@ -1238,6 +1255,8 @@ BEGIN END IF; --DEBUG: RAISE WARNING 'Copy over address tags'; + -- housenumber is a computed field, so start with an empty value + NEW.housenumber := NULL; IF NEW.address is not NULL THEN IF NEW.address ? 'conscriptionnumber' THEN i := getorcreate_housenumber_id(make_standard_name(NEW.address->'conscriptionnumber')); @@ -1266,6 +1285,8 @@ BEGIN -- Speed up searches - just use the centroid of the feature -- cheaper but less acurate place_centroid := ST_PointOnSurface(NEW.geometry); + -- For searching near features rather use the centroid + near_centroid := ST_Envelope(NEW.geometry); NEW.centroid := null; NEW.postcode := null; --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid); @@ -1337,10 +1358,9 @@ BEGIN END LOOP; NEW.importance := null; - select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.country_code) INTO NEW.wikipedia,NEW.importance; - IF NEW.importance IS NULL THEN - select language||':'||title,importance from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance; - END IF; + SELECT wikipedia, importance + FROM compute_importance(NEW.extratags, NEW.country_code, NEW.osm_type, NEW.osm_id) + INTO NEW.wikipedia,NEW.importance; --DEBUG: RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance; @@ -1396,7 +1416,7 @@ BEGIN IF NEW.parent_place_id IS NULL AND addr_street IS NOT NULL THEN address_street_word_ids := get_name_ids(make_standard_name(addr_street)); IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id; + SELECT place_id from getNearestNamedRoadFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; END IF; END IF; --DEBUG: RAISE WARNING 'Checked for addr:street (%)', NEW.parent_place_id; @@ -1404,7 +1424,7 @@ BEGIN IF NEW.parent_place_id IS NULL AND addr_place IS NOT NULL THEN address_street_word_ids := get_name_ids(make_standard_name(addr_place)); IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id; + SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; END IF; END IF; --DEBUG: RAISE WARNING 'Checked for addr:place (%)', NEW.parent_place_id; @@ -1439,7 +1459,7 @@ BEGIN IF location.address ? 'street' THEN address_street_word_ids := get_name_ids(make_standard_name(location.address->'street')); IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id; + SELECT place_id from getNearestNamedRoadFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; EXIT WHEN NEW.parent_place_id is not NULL; END IF; END IF; @@ -1448,7 +1468,7 @@ BEGIN IF location.address ? 'place' THEN address_street_word_ids := get_name_ids(make_standard_name(location.address->'place')); IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id; + SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; EXIT WHEN NEW.parent_place_id is not NULL; END IF; END IF; @@ -1477,7 +1497,7 @@ BEGIN -- Still nothing, just use the nearest road IF NEW.parent_place_id IS NULL THEN - SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) INTO NEW.parent_place_id; + SELECT place_id FROM getNearestRoadFeature(NEW.partition, near_centroid) INTO NEW.parent_place_id; END IF; --DEBUG: RAISE WARNING 'Checked for nearest way (%)', NEW.parent_place_id; @@ -1500,7 +1520,7 @@ BEGIN NEW.postcode := location.postcode; END IF; IF NEW.postcode is null THEN - NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid); + NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry); END IF; END IF; @@ -1579,9 +1599,10 @@ BEGIN -- mark the linked place (excludes from search results) UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - -- keep a note of the node id in case we need it for wikipedia in a bit - linked_node_id := linkedPlacex.osm_id; - select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance; + select wikipedia, importance + FROM compute_importance(linkedPlacex.extratags, NEW.country_code, + 'N', linkedPlacex.osm_id) + INTO linked_wikipedia,linked_importance; --DEBUG: RAISE WARNING 'Linked label member'; END LOOP; @@ -1618,9 +1639,10 @@ BEGIN -- mark the linked place (excludes from search results) UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - -- keep a note of the node id in case we need it for wikipedia in a bit - linked_node_id := linkedPlacex.osm_id; - select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance; + select wikipedia, importance + FROM compute_importance(linkedPlacex.extratags, NEW.country_code, + 'N', linkedPlacex.osm_id) + INTO linked_wikipedia,linked_importance; --DEBUG: RAISE WARNING 'Linked admin_center'; END IF; @@ -1663,9 +1685,10 @@ BEGIN -- mark the linked place (excludes from search results) UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - -- keep a note of the node id in case we need it for wikipedia in a bit - linked_node_id := linkedPlacex.osm_id; - select language||':'||title,importance from get_wikipedia_match(linkedPlacex.extratags, NEW.country_code) INTO linked_wikipedia,linked_importance; + select wikipedia, importance + FROM compute_importance(linkedPlacex.extratags, NEW.country_code, + 'N', linkedPlacex.osm_id) + INTO linked_wikipedia,linked_importance; --DEBUG: RAISE WARNING 'Linked named place'; END LOOP; END IF; @@ -1693,13 +1716,6 @@ BEGIN (NEW.importance is null or NEW.importance < linked_importance) THEN NEW.importance = linked_importance; END IF; - - -- Still null? how about looking it up by the node id - IF NEW.importance IS NULL THEN - --DEBUG: RAISE WARNING 'Looking up importance by linked node id'; - select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance; - END IF; - END IF; -- make sure all names are in the word table @@ -1803,7 +1819,7 @@ BEGIN -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress; -- Add it to the list of search terms - IF NOT %REVERSE-ONLY% AND location.rank_search > 4 THEN + IF NOT %REVERSE-ONLY% THEN nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); END IF; INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address) @@ -2204,12 +2220,13 @@ BEGIN indexed_status = 2, geometry = NEW.geometry where place_id = existingplacex.place_id; - -- if a node(=>house), which is part of a interpolation line, changes (e.g. the street attribute) => mark this line for reparenting -- (already here, because interpolation lines are reindexed before nodes, so in the second call it would be too late) - IF NEW.osm_type='N' and NEW.class='place' and NEW.type='house' THEN - -- Is this node part of an interpolation line? search for it in location_property_osmline and mark the interpolation line for reparenting - update location_property_osmline p set indexed_status = 2 from planet_osm_ways w where p.linegeo && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes); + IF NEW.osm_type='N' + and (coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore) + or existing.geometry::text != NEW.geometry::text) + THEN + result:= osmline_reinsert(NEW.osm_id, NEW.geometry); END IF; -- linked places should get potential new naming and addresses @@ -2318,6 +2335,7 @@ DECLARE searchhousename HSTORE; searchrankaddress INTEGER; searchpostcode TEXT; + postcode_isaddress BOOL; searchclass TEXT; searchtype TEXT; countryname HSTORE; @@ -2325,6 +2343,8 @@ BEGIN -- The place ein question might not have a direct entry in place_addressline. -- Look for the parent of such places then and save if in for_place_id. + postcode_isaddress := true; + -- first query osmline (interpolation lines) IF in_housenumber >= 0 THEN SELECT parent_place_id, country_code, in_housenumber::text, 30, postcode, @@ -2361,7 +2381,7 @@ BEGIN -- postcode table IF for_place_id IS NULL THEN - SELECT parent_place_id, country_code, rank_address, postcode, 'place', 'postcode' + SELECT parent_place_id, country_code, rank_search, postcode, 'place', 'postcode' FROM location_postcode WHERE place_id = in_place_id INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode, @@ -2441,7 +2461,10 @@ BEGIN searchcountrycode := location.country_code; END IF; IF location.type in ('postcode', 'postal_code') THEN - location.isaddress := FALSE; + postcode_isaddress := false; + IF location.osm_type != 'R' THEN + location.isaddress := FALSE; + END IF; END IF; countrylocation := ROW(location.place_id, location.osm_type, location.osm_id, location.name, location.class, location.type, @@ -2485,7 +2508,7 @@ BEGIN IF searchpostcode IS NOT NULL THEN location := ROW(null, null, null, hstore('ref', searchpostcode), 'place', - 'postcode', null, true, true, 5, 0)::addressline; + 'postcode', null, false, postcode_isaddress, 5, 0)::addressline; RETURN NEXT location; END IF; @@ -2599,7 +2622,7 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; -DROP TYPE wikipedia_article_match CASCADE; +DROP TYPE IF EXISTS wikipedia_article_match CASCADE; create type wikipedia_article_match as ( language TEXT, title TEXT, @@ -2656,6 +2679,42 @@ END; $$ LANGUAGE plpgsql; +DROP TYPE IF EXISTS place_importance CASCADE; +create type place_importance as ( + importance FLOAT, + wikipedia TEXT +); + +CREATE OR REPLACE FUNCTION compute_importance(extratags HSTORE, country_code varchar(2), osm_type varchar(1), osm_id BIGINT) + RETURNS place_importance + AS $$ +DECLARE + match RECORD; + result place_importance; +BEGIN + FOR match IN SELECT * FROM get_wikipedia_match(extratags, country_code) + WHERE language is not NULL + LOOP + result.importance := match.importance; + result.wikipedia := match.language || ':' || match.title; + RETURN result; + END LOOP; + + IF extratags ? 'wikidata' THEN + FOR match IN SELECT * FROM wikipedia_article + WHERE wd_page_title = extratags->'wikidata' + ORDER BY language = 'en' DESC, langcount DESC LIMIT 1 LOOP + result.importance := match.importance; + result.wikipedia := match.language || ':' || match.title; + RETURN result; + END LOOP; + END IF; + + RETURN null; +END; +$$ +LANGUAGE plpgsql; + CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT, maxdepth INTEGER) RETURNS SETOF GEOMETRY AS $$