X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/9abb96fa6b87ba2086b0bf32eed0310690931cfe..5a6da83577935cf4622efa6c7ec32baf80d370fc:/sql/partition-functions.src.sql?ds=sidebyside diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index 41758c83..97520f99 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -1,4 +1,38 @@ -create or replace function getNearFeatures(in_partition INTEGER, feature 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 @@ -8,15 +42,10 @@ BEGIN 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 ST_Intersects(geometry, feature) and rank_search < maxrank + WHERE geometry && feature + AND is_relevant_geometry(ST_Relate(geometry, feature), ST_GeometryType(feature)) + 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 - 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 LOOP RETURN NEXT r; END LOOP; @@ -27,7 +56,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 @@ -124,7 +153,7 @@ BEGIN FROM search_name_-partition- WHERE name_vector && isin_token AND centroid && ST_Expand(point, 0.04) - AND search_rank between 16 and 22 + AND search_rank between 16 and 25 ORDER BY ST_Distance(centroid, point) ASC limit 1; RETURN parent; END IF; @@ -133,7 +162,7 @@ BEGIN RAISE EXCEPTION 'Unknown partition %', in_partition; END $$ -LANGUAGE plpgsql; +LANGUAGE plpgsql STABLE; create or replace function insertSearchName( @@ -214,48 +243,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); @@ -266,25 +297,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;