X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/d2bd6aa78dc467aea4d395c9406d7088aca233ce..ecf4693a799055b3795ca691337941f931cf0a59:/lib-sql/functions/partition-functions.sql?ds=sidebyside diff --git a/lib-sql/functions/partition-functions.sql b/lib-sql/functions/partition-functions.sql index cfa151de..20ec3da6 100644 --- a/lib-sql/functions/partition-functions.sql +++ b/lib-sql/functions/partition-functions.sql @@ -1,3 +1,10 @@ +-- SPDX-License-Identifier: GPL-2.0-only +-- +-- This file is part of Nominatim. (https://nominatim.org) +-- +-- Copyright (C) 2022 by the Nominatim developer community. +-- For a full list of authors see the git log. + DROP TYPE IF EXISTS nearfeaturecentr CASCADE; CREATE TYPE nearfeaturecentr AS ( place_id BIGINT, @@ -10,7 +17,7 @@ CREATE TYPE nearfeaturecentr AS ( centroid GEOMETRY ); --- feature intersects geoemtry +-- feature intersects geometry -- 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 @@ -32,7 +39,10 @@ BEGIN END $$ LANGUAGE plpgsql IMMUTABLE; -create or replace function getNearFeatures(in_partition INTEGER, feature GEOMETRY, maxrank INTEGER) RETURNS setof nearfeaturecentr AS $$ +CREATE OR REPLACE function getNearFeatures(in_partition INTEGER, feature GEOMETRY, + feature_centroid GEOMETRY, + maxrank INTEGER) +RETURNS setof nearfeaturecentr AS $$ DECLARE r nearfeaturecentr%rowtype; BEGIN @@ -41,7 +51,11 @@ BEGIN IF in_partition = {{ partition }} THEN FOR r IN SELECT place_id, keywords, rank_address, rank_search, - min(ST_Distance(feature, centroid)) as distance, + CASE WHEN isguess THEN ST_Distance(feature, centroid) + ELSE min(ST_Distance(feature_centroid, geometry)) + -- tie breaker when distance is the same (i.e. way is on boundary) + + 0.00001 * ST_Distance(feature, centroid) + END as distance, isguess, postcode, centroid FROM location_area_large_{{ partition }} WHERE geometry && feature @@ -63,54 +77,36 @@ 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 + +CREATE OR REPLACE FUNCTION get_address_place(in_partition SMALLINT, feature GEOMETRY, + from_rank SMALLINT, to_rank SMALLINT, + extent FLOAT, token_info JSONB, key TEXT) + RETURNS 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; - {% for partition in db.partitions %} - 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 + 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 + WHERE geometry && ST_Expand(feature, extent) + AND rank_address between from_rank and to_rank + AND token_matches_address(token_info, key, 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; + ORDER BY bool_or(ST_Intersects(geometry, feature)) DESC, distance LIMIT 1; + RETURN r; + END IF; {% endfor %} - RAISE EXCEPTION 'Unknown partition %', in_partition; - END LOOP; + RAISE EXCEPTION 'Unknown partition %', in_partition; 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 @@ -166,18 +162,21 @@ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION getNearestNamedRoadPlaceId(in_partition INTEGER, point GEOMETRY, - isin_token INTEGER[]) + token_info JSONB) RETURNS BIGINT AS $$ DECLARE parent BIGINT; BEGIN + IF not token_has_addr_street(token_info) THEN + RETURN NULL; + END IF; {% for partition in db.partitions %} IF in_partition = {{ partition }} THEN SELECT place_id FROM search_name_{{ partition }} INTO parent - WHERE name_vector && isin_token + WHERE token_matches_street(token_info, name_vector) AND centroid && ST_Expand(point, 0.015) AND address_rank between 26 and 27 ORDER BY ST_Distance(centroid, point) ASC limit 1; @@ -192,19 +191,22 @@ LANGUAGE plpgsql STABLE; CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER, point GEOMETRY, - isin_token INTEGER[]) + token_info JSONB) RETURNS BIGINT AS $$ DECLARE parent BIGINT; BEGIN + IF not token_has_addr_place(token_info) THEN + RETURN NULL; + END IF; {% for partition in db.partitions %} IF in_partition = {{ partition }} THEN SELECT place_id INTO parent FROM search_name_{{ partition }} - WHERE name_vector && isin_token + WHERE token_matches_place(token_info, name_vector) AND centroid && ST_Expand(point, 0.04) AND address_rank between 16 and 25 ORDER BY ST_Distance(centroid, point) ASC limit 1;