X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/2ab9e4acd3a2ceb5ff871b6b6afc786377e8739f..3c7519444867cd9c6b3003c4c88729df21b8c265:/sql/partition-functions.src.sql diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index 34b4d390..703c08af 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -10,26 +10,47 @@ CREATE TYPE nearfeaturecentr AS ( centroid GEOMETRY ); -create or replace function getNearFeatures(in_partition INTEGER, feature GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeaturecentr AS $$ +-- feature intersects geoemtry +-- for areas and linestrings they must touch at least along a line +CREATE OR REPLACE FUNCTION is_relevant_geometry(de9im TEXT, geom_type TEXT) +RETURNS BOOLEAN +AS $$ +BEGIN + IF substring(de9im from 1 for 2) != 'FF' THEN + RETURN TRUE; + END IF; + + IF geom_type = 'ST_Point' THEN + RETURN substring(de9im from 4 for 1) = '0'; + END IF; + + IF geom_type in ('ST_LineString', 'ST_MultiLineString') THEN + RETURN substring(de9im from 4 for 1) = '1'; + END IF; + + RETURN substring(de9im from 4 for 1) = '2'; +END +$$ LANGUAGE plpgsql IMMUTABLE; + +create or replace function getNearFeatures(in_partition INTEGER, feature GEOMETRY, maxrank INTEGER) RETURNS setof nearfeaturecentr AS $$ DECLARE r nearfeaturecentr%rowtype; BEGIN -- start IF in_partition = -partition- THEN - FOR r IN - SELECT place_id, keywords, rank_address, rank_search, min(ST_Distance(feature, centroid)) as distance, isguess, postcode, centroid + FOR r IN + SELECT place_id, keywords, rank_address, rank_search, + min(ST_Distance(feature, centroid)) as distance, + isguess, postcode, centroid FROM location_area_large_-partition- - WHERE ST_Intersects(geometry, feature) - AND rank_search < maxrank AND rank_address < maxrank + WHERE geometry && feature + AND is_relevant_geometry(ST_Relate(geometry, feature), ST_GeometryType(feature)) + AND rank_address < maxrank + -- Postcodes currently still use rank_search to define for which + -- features they are relevant. + AND not (rank_address in (5, 11) and rank_search > maxrank) GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid - ORDER BY rank_address, isin_tokens && keywords desc, isguess asc, - ST_Distance(feature, centroid) * - CASE - WHEN rank_address = 16 AND rank_search = 15 THEN 0.2 -- capital city - WHEN rank_address = 16 AND rank_search = 16 THEN 0.25 -- city - WHEN rank_address = 16 AND rank_search = 17 THEN 0.5 -- town - ELSE 1 END ASC -- everything else LOOP RETURN NEXT r; END LOOP; @@ -42,6 +63,54 @@ END $$ LANGUAGE plpgsql STABLE; +CREATE OR REPLACE FUNCTION get_places_for_addr_tags(in_partition SMALLINT, + feature GEOMETRY, + address HSTORE, country TEXT) + RETURNS SETOF nearfeaturecentr + AS $$ +DECLARE + r nearfeaturecentr%rowtype; + item RECORD; +BEGIN + FOR item IN + SELECT (get_addr_tag_rank(key, country)).*, key, name FROM + (SELECT skeys(address) as key, svals(address) as name) x + LOOP + IF item.from_rank is null THEN + CONTINUE; + END IF; + +-- start + IF in_partition = -partition- THEN + SELECT place_id, keywords, rank_address, rank_search, + min(ST_Distance(feature, centroid)) as distance, + isguess, postcode, centroid INTO r + FROM location_area_large_-partition- + WHERE geometry && ST_Expand(feature, item.extent) + AND rank_address between item.from_rank and item.to_rank + AND word_ids_from_name(item.name) && keywords + GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid + ORDER BY bool_or(ST_Intersects(geometry, feature)), distance LIMIT 1; + IF r.place_id is null THEN + -- If we cannot find a place for the term, just return the + -- search term for the given name. That ensures that the address + -- element can still be searched for, even though it will not be + -- displayed. + RETURN NEXT ROW(null, addr_ids_from_name(item.name), null, null, + null, null, null, null)::nearfeaturecentr; + ELSE + RETURN NEXT r; + END IF; + CONTINUE; + END IF; +-- end + + RAISE EXCEPTION 'Unknown partition %', in_partition; + END LOOP; +END; +$$ +LANGUAGE plpgsql STABLE; + create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$ DECLARE BEGIN @@ -110,7 +179,7 @@ BEGIN INTO parent WHERE name_vector && isin_token AND centroid && ST_Expand(point, 0.015) - AND search_rank between 26 and 27 + AND address_rank between 26 and 27 ORDER BY ST_Distance(centroid, point) ASC limit 1; RETURN parent; END IF; @@ -137,7 +206,7 @@ BEGIN FROM search_name_-partition- WHERE name_vector && isin_token AND centroid && ST_Expand(point, 0.04) - AND search_rank between 16 and 22 + AND address_rank between 16 and 25 ORDER BY ST_Distance(centroid, point) ASC limit 1; RETURN parent; END IF; @@ -148,7 +217,6 @@ END $$ LANGUAGE plpgsql STABLE; - create or replace function insertSearchName( in_partition INTEGER, in_place_id BIGINT, in_name_vector INTEGER[], in_rank_search INTEGER, in_rank_address INTEGER, in_geometry GEOMETRY) @@ -159,8 +227,8 @@ BEGIN IF in_partition = -partition- THEN DELETE FROM search_name_-partition- values WHERE place_id = in_place_id; IF in_rank_address > 0 THEN - INSERT INTO search_name_-partition- (place_id, search_rank, address_rank, name_vector, centroid) - values (in_place_id, in_rank_search, in_rank_address, in_name_vector, in_geometry); + INSERT INTO search_name_-partition- (place_id, address_rank, name_vector, centroid) + values (in_place_id, in_rank_address, in_name_vector, in_geometry); END IF; RETURN TRUE; END IF;