X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/b3186d07f5860473c7d80185b9dfbb1804ff3ec3..df115c73b21a5efcd0e0cc815a4d8228093b62d0:/sql/partition-functions.src.sql diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index 46dc6137..ce00bcf0 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -1,3 +1,37 @@ +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 +); + + -- 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, isin_tokens INT[]) RETURNS setof nearfeaturecentr AS $$ DECLARE r nearfeaturecentr%rowtype; @@ -6,12 +40,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 * 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 + SELECT place_id, keywords, rank_address, rank_search, min(ST_Distance(feature, centroid)) as distance, isguess, postcode, centroid + FROM location_area_large_-partition- + WHERE geometry && feature + AND is_relevant_geometry(ST_Relate(geometry, feature), ST_GeometryType(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(feature, centroid) * CASE @@ -29,7 +63,7 @@ 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, in_rank_search INTEGER) RETURNS BOOLEAN AS $$ DECLARE @@ -55,8 +89,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 @@ -64,14 +98,16 @@ BEGIN 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 @@ -82,145 +118,72 @@ 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 @> isin_token - AND ST_DWithin(centroid, point, 0.01) - AND search_rank between 26 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 25 + ORDER BY ST_Distance(centroid, point) ASC limit 1; + RETURN parent; END IF; -- end RAISE EXCEPTION 'Unknown partition %', in_partition; END $$ -LANGUAGE plpgsql; - -create or replace function getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) - RETURNS setof nearfeature AS $$ -DECLARE - r nearfeature%rowtype; -BEGIN +LANGUAGE plpgsql STABLE; --- 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 @> isin_token - AND ST_DWithin(centroid, point, 0.03) - AND search_rank between 16 and 22 - 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 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, 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 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 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; IF in_rank_address > 0 THEN - INSERT INTO search_name_-partition- values (in_place_id, in_rank_search, in_rank_address, - in_name_vector, in_geometry); + 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; @@ -235,10 +198,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; @@ -261,7 +220,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 @@ -290,48 +250,50 @@ 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_LineInterpolatePoint(line,0); @@ -342,25 +304,22 @@ BEGIN 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;