X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/ddc46acd266c26aefb5131eeede1d9056c19ee5b..c4e72e6ca9a72a9e151ca189cb8febacae36ecfa:/sql/partition-functions.src.sql?ds=sidebyside diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index d9da9b42..110dd0cd 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -1,4 +1,4 @@ -create or replace function getNearFeatures(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeaturecentr AS $$ +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 +6,14 @@ 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 + 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_Contains(geometry, point) and rank_search < maxrank + 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(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 @@ -55,20 +55,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); + INSERT INTO location_area_country (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); 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 @@ -111,7 +116,7 @@ END $$ LANGUAGE plpgsql; -create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER) +create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) RETURNS setof nearfeature AS $$ DECLARE r nearfeature%rowtype; @@ -123,7 +128,7 @@ 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 @> ARRAY[isin_token] + WHERE name_vector @> isin_token AND ST_DWithin(centroid, point, 0.01) AND search_rank between 26 and 27 ORDER BY distance ASC limit 1 @@ -139,7 +144,7 @@ END $$ LANGUAGE plpgsql; -create or replace function getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER) +create or replace function getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) RETURNS setof nearfeature AS $$ DECLARE r nearfeature%rowtype; @@ -151,7 +156,7 @@ 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 @> ARRAY[isin_token] + WHERE name_vector @> isin_token AND ST_DWithin(centroid, point, 0.03) AND search_rank between 16 and 22 ORDER BY distance ASC limit 1 @@ -168,29 +173,6 @@ $$ LANGUAGE plpgsql; -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[], @@ -200,21 +182,25 @@ 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); + 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; - INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address, - in_name_vector, in_geometry); + 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; - INSERT INTO search_name_-partition- values (in_place_id, in_rank_search, in_rank_address, - in_name_vector, in_geometry); + 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 @@ -254,7 +240,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 @@ -327,9 +314,9 @@ BEGIN RETURN; 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