X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/87b250e739862b5ff51c4508f87501bba47f82f8..d3f7465ccad9cce6c84f0b089282c5ece550c5ed:/sql/partition-functions.src.sql diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index 9ea70409..34b4d390 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -1,4 +1,16 @@ -create or replace function getNearFeatures(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeaturecentr AS $$ +DROP TYPE IF EXISTS nearfeaturecentr CASCADE; +CREATE TYPE nearfeaturecentr AS ( + place_id BIGINT, + keywords int[], + rank_address smallint, + rank_search smallint, + distance float, + isguess boolean, + postcode TEXT, + centroid GEOMETRY +); + +create or replace function getNearFeatures(in_partition INTEGER, feature GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeaturecentr AS $$ DECLARE r nearfeaturecentr%rowtype; BEGIN @@ -6,14 +18,13 @@ BEGIN -- start IF in_partition = -partition- THEN FOR r IN - SELECT place_id, keywords, rank_address, rank_search, min(ST_Distance(point, centroid)) as distance, isguess, centroid FROM ( - SELECT * FROM location_area_large_-partition- WHERE ST_Contains(geometry, point) and rank_search < maxrank - UNION ALL - SELECT * FROM location_area_country WHERE ST_Contains(geometry, point) and rank_search < maxrank - ) as location_area - GROUP BY place_id, keywords, rank_address, rank_search, isguess, centroid + 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 + GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid ORDER BY rank_address, isin_tokens && keywords desc, isguess asc, - ST_Distance(point, centroid) * + 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 @@ -29,12 +40,17 @@ BEGIN RAISE EXCEPTION 'Unknown partition %', in_partition; END $$ -LANGUAGE plpgsql; +LANGUAGE plpgsql STABLE; -create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$ +create or replace function deleteLocationArea(in_partition INTEGER, in_place_id BIGINT, in_rank_search INTEGER) RETURNS BOOLEAN AS $$ DECLARE BEGIN + IF in_rank_search <= 4 THEN + DELETE from location_area_country WHERE place_id = in_place_id; + RETURN TRUE; + END IF; + -- start IF in_partition = -partition- THEN DELETE from location_area_large_-partition- WHERE place_id = in_place_id; @@ -50,20 +66,25 @@ $$ 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 + IF in_rank_address = 0 THEN + RETURN TRUE; + END IF; - IF in_rank_search <= 4 THEN - INSERT INTO location_area_country values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry); + IF in_rank_search <= 4 and not in_estimate THEN + INSERT INTO location_area_country (place_id, country_code, geometry) + values (in_place_id, in_country_code, in_geometry); RETURN TRUE; END IF; -- start IF in_partition = -partition- THEN - INSERT INTO location_area_large_-partition- 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 @@ -74,113 +95,73 @@ END $$ LANGUAGE plpgsql; -create or replace function getNearestNamedFeature(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_token INTEGER) RETURNS setof nearfeature AS $$ +CREATE OR REPLACE FUNCTION getNearestNamedRoadPlaceId(in_partition INTEGER, + point GEOMETRY, + isin_token INTEGER[]) + RETURNS BIGINT + AS $$ DECLARE - r nearfeature%rowtype; + parent BIGINT; 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; + SELECT place_id FROM search_name_-partition- + INTO parent + WHERE name_vector && isin_token + AND centroid && ST_Expand(point, 0.015) + AND search_rank between 26 and 27 + ORDER BY ST_Distance(centroid, point) ASC limit 1; + RETURN parent; END IF; -- end RAISE EXCEPTION 'Unknown partition %', in_partition; END $$ -LANGUAGE plpgsql; +LANGUAGE plpgsql STABLE; -create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER) - RETURNS setof nearfeature AS $$ +CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER, + point GEOMETRY, + isin_token INTEGER[]) + RETURNS BIGINT + AS $$ DECLARE - r nearfeature%rowtype; + parent BIGINT; 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 ST_DWithin(centroid, point, 0.03) - AND search_rank between 22 and 27 - ORDER BY distance ASC limit 1 - LOOP - RETURN NEXT r; - END LOOP; - RETURN; + SELECT place_id + INTO parent + FROM search_name_-partition- + WHERE name_vector && isin_token + AND centroid && ST_Expand(point, 0.04) + AND search_rank between 16 and 22 + ORDER BY ST_Distance(centroid, point) ASC limit 1; + RETURN parent; END IF; -- end RAISE EXCEPTION 'Unknown partition %', in_partition; END $$ -LANGUAGE plpgsql; +LANGUAGE plpgsql STABLE; -create or replace function getNearestPostcode(in_partition INTEGER, point GEOMETRY) - RETURNS TEXT AS $$ -DECLARE - out_postcode TEXT; -BEGIN - --- start - IF in_partition = -partition- THEN - SELECT postcode - FROM location_area_large_-partition- join placex using (place_id) - WHERE st_contains(location_area_large_-partition-.geometry, point) - AND class = 'place' and type = 'postcode' - ORDER BY st_distance(location_area_large_-partition-.centroid, point) ASC limit 1 - INTO out_postcode; - RETURN out_postcode; - END IF; --- end - - RAISE EXCEPTION 'Unknown partition %', in_partition; -END -$$ -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) 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 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; - INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address, in_importance, in_country_code, - in_name_vector, in_nameaddress_vector, in_centroid); - RETURN TRUE; - END IF; - -- start IF in_partition = -partition- THEN DELETE FROM search_name_-partition- values WHERE place_id = in_place_id; - INSERT INTO search_name_-partition- values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code, - in_name_vector, in_nameaddress_vector, in_centroid); + 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); + END IF; RETURN TRUE; END IF; -- end @@ -194,10 +175,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; @@ -220,7 +197,8 @@ BEGIN -- start IF in_partition = -partition- THEN DELETE FROM location_road_-partition- where place_id = in_place_id; - INSERT INTO location_road_-partition- values (in_partition, in_place_id, in_country_code, in_geometry); + INSERT INTO location_road_-partition- (partition, place_id, country_code, geometry) + values (in_partition, in_place_id, in_country_code, in_geometry); RETURN TRUE; END IF; -- end @@ -249,77 +227,76 @@ END $$ LANGUAGE plpgsql; -create or replace function getNearestRoadFeature(in_partition INTEGER, point GEOMETRY) RETURNS setof nearfeature AS $$ +CREATE OR REPLACE FUNCTION getNearestRoadPlaceId(in_partition INTEGER, point GEOMETRY) + RETURNS BIGINT + AS $$ DECLARE - r nearfeature%rowtype; - search_diameter FLOAT; + r RECORD; + search_diameter FLOAT; BEGIN -- start IF in_partition = -partition- THEN search_diameter := 0.00005; WHILE search_diameter < 0.1 LOOP - FOR r IN - SELECT place_id, null, null, null, - ST_Distance(geometry, point) as distance, null as isguess - FROM location_road_-partition- - WHERE ST_DWithin(geometry, point, search_diameter) - ORDER BY distance ASC limit 1 + FOR r IN + SELECT place_id FROM location_road_-partition- + WHERE ST_DWithin(geometry, point, search_diameter) + ORDER BY ST_Distance(geometry, point) ASC limit 1 LOOP - RETURN NEXT r; - RETURN; + RETURN r.place_id; END LOOP; search_diameter := search_diameter * 2; END LOOP; - RETURN; + RETURN NULL; END IF; -- end RAISE EXCEPTION 'Unknown partition %', in_partition; END $$ -LANGUAGE plpgsql; +LANGUAGE plpgsql STABLE; -create or replace function getNearestParellelRoadFeature(in_partition INTEGER, line GEOMETRY) RETURNS setof nearfeature AS $$ +CREATE OR REPLACE FUNCTION getNearestParallelRoadFeature(in_partition INTEGER, + line GEOMETRY) + RETURNS BIGINT + AS $$ DECLARE - r nearfeature%rowtype; - search_diameter FLOAT; + r RECORD; + search_diameter FLOAT; p1 GEOMETRY; p2 GEOMETRY; p3 GEOMETRY; BEGIN - IF st_geometrytype(line) not in ('ST_LineString') THEN - RETURN; + IF ST_GeometryType(line) not in ('ST_LineString') THEN + RETURN NULL; END IF; - p1 := ST_Line_Interpolate_Point(line,0); - p2 := ST_Line_Interpolate_Point(line,0.5); - p3 := ST_Line_Interpolate_Point(line,1); + p1 := ST_LineInterpolatePoint(line,0); + p2 := ST_LineInterpolatePoint(line,0.5); + p3 := ST_LineInterpolatePoint(line,1); -- start IF in_partition = -partition- THEN search_diameter := 0.0005; WHILE search_diameter < 0.01 LOOP - FOR r IN - SELECT place_id, null, null, null, - ST_Distance(geometry, line) as distance, null as isguess - FROM location_road_-partition- - WHERE ST_DWithin(line, geometry, search_diameter) - ORDER BY (ST_distance(geometry, p1)+ - ST_distance(geometry, p2)+ - ST_distance(geometry, p3)) ASC limit 1 + FOR r IN + SELECT place_id FROM location_road_-partition- + WHERE ST_DWithin(line, geometry, search_diameter) + ORDER BY (ST_distance(geometry, p1)+ + ST_distance(geometry, p2)+ + ST_distance(geometry, p3)) ASC limit 1 LOOP - RETURN NEXT r; - RETURN; + RETURN r.place_id; END LOOP; search_diameter := search_diameter * 2; END LOOP; - RETURN; + RETURN NULL; END IF; -- end RAISE EXCEPTION 'Unknown partition %', in_partition; END $$ -LANGUAGE plpgsql; +LANGUAGE plpgsql STABLE;