X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/87b250e739862b5ff51c4508f87501bba47f82f8..a8ec250993b0ed0e48bbefd8fb3bcbb861c5f2a9:/sql/partition-functions.src.sql?ds=inline diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index 9ea70409..703c08af 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -1,24 +1,56 @@ -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 +); + +-- 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) RETURNS setof nearfeaturecentr AS $$ DECLARE r nearfeaturecentr%rowtype; 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 - ORDER BY rank_address, isin_tokens && keywords desc, isguess asc, - ST_Distance(point, 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 - WHEN rank_address = 16 AND rank_search = 17 THEN 0.5 -- town - ELSE 1 END ASC -- everything else + FOR r IN + 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_address < maxrank + -- Postcodes currently still use rank_search to define for which + -- features they are relevant. + AND not (rank_address in (5, 11) and rank_search > maxrank) + GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid LOOP RETURN NEXT r; END LOOP; @@ -29,158 +61,175 @@ 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 get_places_for_addr_tags(in_partition SMALLINT, + feature GEOMETRY, + address HSTORE, country TEXT) + RETURNS SETOF 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; -- start - IF in_partition = -partition- THEN - DELETE from location_area_large_-partition- WHERE place_id = in_place_id; - RETURN TRUE; - END IF; + 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 + 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; -- end - RAISE EXCEPTION 'Unknown partition %', in_partition; - - RETURN FALSE; -END + RAISE EXCEPTION 'Unknown partition %', in_partition; + END LOOP; +END; $$ -LANGUAGE plpgsql; +LANGUAGE plpgsql STABLE; -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_centroid GEOMETRY, in_geometry GEOMETRY) 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 - 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); + DELETE from location_area_country WHERE place_id = in_place_id; 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); + DELETE from location_area_large_-partition- WHERE place_id = in_place_id; RETURN TRUE; END IF; -- end RAISE EXCEPTION 'Unknown partition %', in_partition; + RETURN FALSE; 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 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, postcode TEXT, + in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$ DECLARE - r nearfeature%rowtype; BEGIN + IF in_rank_address = 0 THEN + RETURN TRUE; + END IF; + + 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 - 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; + 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 RAISE EXCEPTION 'Unknown partition %', in_partition; + RETURN FALSE; END $$ LANGUAGE plpgsql; -create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, 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 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 FROM search_name_-partition- + INTO parent + WHERE name_vector && isin_token + AND centroid && ST_Expand(point, 0.015) + AND address_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 getNearestPostcode(in_partition INTEGER, point GEOMETRY) - RETURNS TEXT AS $$ +CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER, + point GEOMETRY, + isin_token INTEGER[]) + RETURNS BIGINT + AS $$ DECLARE - out_postcode TEXT; + parent BIGINT; 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; + SELECT place_id + INTO parent + FROM search_name_-partition- + WHERE name_vector && isin_token + AND centroid && ST_Expand(point, 0.04) + AND address_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; +LANGUAGE plpgsql STABLE; 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, address_rank, name_vector, centroid) + values (in_place_id, in_rank_address, in_name_vector, in_geometry); + END IF; RETURN TRUE; END IF; -- end @@ -194,10 +243,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 +265,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 +295,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;