X-Git-Url: https://git.openstreetmap.org/nominatim.git/blobdiff_plain/71ddeb40a66e0fef307350cdcd7cb823c90eb132..e10d11c6c7a5a96f645b9155a55e56f874ed6042:/sql/partition-functions.src.sql diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index f4df9135..3959661e 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -6,12 +6,12 @@ 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, centroid FROM ( + SELECT place_id, keywords, rank_address, rank_search, min(ST_Distance(feature, centroid)) as distance, isguess, postcode, centroid FROM ( SELECT * FROM location_area_large_-partition- WHERE ST_Intersects(geometry, feature) and rank_search < maxrank UNION ALL SELECT * FROM location_area_country WHERE ST_Intersects(geometry, feature) and rank_search < maxrank ) as location_area - GROUP BY place_id, keywords, rank_address, rank_search, isguess, centroid + 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 @@ -55,8 +55,8 @@ $$ LANGUAGE plpgsql; create or replace function insertLocationAreaLarge( - in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[], - in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, + in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), in_keywords INTEGER[], + in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, postcode TEXT, in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$ DECLARE BEGIN @@ -72,8 +72,8 @@ BEGIN -- start IF in_partition = -partition- THEN - INSERT INTO location_area_large_-partition- (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, centroid, geometry) - values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry); + INSERT INTO location_area_large_-partition- (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry) + values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, postcode, in_centroid, in_geometry); RETURN TRUE; END IF; -- end @@ -84,38 +84,6 @@ END $$ LANGUAGE plpgsql; -create or replace function getNearestNamedFeature(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_token INTEGER) RETURNS setof nearfeature AS $$ -DECLARE - r nearfeature%rowtype; -BEGIN - --- start - IF in_partition = -partition- THEN - FOR r IN - SELECT place_id, name_vector, address_rank, search_rank, - ST_Distance(centroid, point) as distance, null as isguess - FROM search_name_-partition- - WHERE name_vector @> ARRAY[isin_token] - AND search_rank < maxrank - UNION ALL - SELECT place_id, name_vector, address_rank, search_rank, - ST_Distance(centroid, point) as distance, null as isguess - FROM search_name_country - WHERE name_vector @> ARRAY[isin_token] - AND search_rank < maxrank - ORDER BY distance ASC limit 1 - LOOP - RETURN NEXT r; - END LOOP; - RETURN; - END IF; --- end - - RAISE EXCEPTION 'Unknown partition %', in_partition; -END -$$ -LANGUAGE plpgsql; - create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) RETURNS setof nearfeature AS $$ DECLARE @@ -128,8 +96,8 @@ BEGIN SELECT place_id, name_vector, address_rank, search_rank, ST_Distance(centroid, point) as distance, null as isguess FROM search_name_-partition- - WHERE name_vector @> isin_token - AND ST_DWithin(centroid, point, 0.01) + WHERE name_vector && isin_token + AND ST_DWithin(centroid, point, 0.015) AND search_rank between 26 and 27 ORDER BY distance ASC limit 1 LOOP @@ -156,8 +124,8 @@ BEGIN SELECT place_id, name_vector, address_rank, search_rank, ST_Distance(centroid, point) as distance, null as isguess FROM search_name_-partition- - WHERE name_vector @> isin_token - AND ST_DWithin(centroid, point, 0.03) + WHERE name_vector && isin_token + AND ST_DWithin(centroid, point, 0.04) AND search_rank between 16 and 22 ORDER BY distance ASC limit 1 LOOP @@ -174,26 +142,11 @@ LANGUAGE plpgsql; create or replace function insertSearchName( - in_partition INTEGER, in_place_id BIGINT, in_country_code VARCHAR(2), - in_name_vector INTEGER[], in_nameaddress_vector INTEGER[], - in_rank_search INTEGER, in_rank_address INTEGER, in_importance FLOAT, - in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$ + in_partition INTEGER, in_place_id BIGINT, in_name_vector INTEGER[], + in_rank_search INTEGER, in_rank_address INTEGER, in_geometry GEOMETRY) +RETURNS BOOLEAN AS $$ DECLARE BEGIN - - DELETE FROM search_name WHERE place_id = in_place_id; - INSERT INTO search_name (place_id, search_rank, address_rank, importance, country_code, name_vector, nameaddress_vector, centroid) - values (in_place_id, in_rank_search, in_rank_address, in_importance, in_country_code, in_name_vector, in_nameaddress_vector, in_centroid); - - IF in_rank_search <= 4 THEN - DELETE FROM search_name_country WHERE place_id = in_place_id; - IF in_rank_address > 0 THEN - INSERT INTO search_name_country (place_id, search_rank, address_rank, name_vector, centroid) - values (in_place_id, in_rank_search, in_rank_address, in_name_vector, in_geometry); - END IF; - RETURN TRUE; - END IF; - -- start IF in_partition = -partition- THEN DELETE FROM search_name_-partition- values WHERE place_id = in_place_id; @@ -214,10 +167,6 @@ LANGUAGE plpgsql; create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$ DECLARE BEGIN - - DELETE from search_name WHERE place_id = in_place_id; - DELETE from search_name_country WHERE place_id = in_place_id; - -- start IF in_partition = -partition- THEN DELETE from search_name_-partition- WHERE place_id = in_place_id;