From: Sarah Hoffmann Date: Wed, 26 Feb 2020 19:21:10 +0000 (+0100) Subject: Merge pull request #1698 from lonvia/cleanup-partition-functions X-Git-Tag: v3.5.0~71 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/b00d16fd7d41b41eb41128fdc4866dd3d7a67a95?hp=c1ef56c870575a881ff81f409b696af0f7838a6a Merge pull request #1698 from lonvia/cleanup-partition-functions Cleanup partition functions --- diff --git a/sql/functions/aux_property.sql b/sql/functions/aux_property.sql index cee5cfb2..6dd99eb2 100644 --- a/sql/functions/aux_property.sql +++ b/sql/functions/aux_property.sql @@ -28,8 +28,8 @@ BEGIN END IF; IF out_parent_place_id IS NULL THEN - FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP - out_parent_place_id := location.place_id; + SELECT getNearestRoadPlaceId(out_partition, place_centroid) + INTO out_parent_place_id; END LOOP; END IF; diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql index fb3f2dc5..ffc83fa3 100644 --- a/sql/functions/placex_triggers.sql +++ b/sql/functions/placex_triggers.sql @@ -135,8 +135,7 @@ BEGIN IF fallback THEN IF ST_Area(bbox) < 0.01 THEN -- for smaller features get the nearest road - SELECT place_id FROM getNearestRoadFeature(poi_partition, bbox) - INTO parent_place_id; + SELECT getNearestRoadPlaceId(poi_partition, bbox) INTO parent_place_id; --DEBUG: RAISE WARNING 'Checked for nearest way (%)', parent_place_id; ELSE -- for larger features simply find the area with the largest rank that diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index 41758c83..8f78032e 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -1,3 +1,15 @@ +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; @@ -27,7 +39,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 @@ -133,7 +145,7 @@ BEGIN RAISE EXCEPTION 'Unknown partition %', in_partition; END $$ -LANGUAGE plpgsql; +LANGUAGE plpgsql STABLE; create or replace function insertSearchName( @@ -214,48 +226,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 +280,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; diff --git a/sql/partition-tables.src.sql b/sql/partition-tables.src.sql index 57806898..8749243e 100644 --- a/sql/partition-tables.src.sql +++ b/sql/partition-tables.src.sql @@ -1,30 +1,3 @@ -drop type if exists nearplace cascade; -create type nearplace as ( - place_id BIGINT -); - -drop type if exists nearfeature cascade; -create type nearfeature as ( - place_id BIGINT, - keywords int[], - rank_address smallint, - rank_search smallint, - distance float, - isguess boolean -); - -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 -); - drop table IF EXISTS search_name_blank CASCADE; CREATE TABLE search_name_blank ( place_id BIGINT, diff --git a/sql/tiger_import_start.sql b/sql/tiger_import_start.sql index b9c4fcfc..4b9c33fc 100644 --- a/sql/tiger_import_start.sql +++ b/sql/tiger_import_start.sql @@ -63,15 +63,13 @@ BEGIN END IF; IF out_parent_place_id IS NULL THEN - FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP - out_parent_place_id := location.place_id; - END LOOP; + SELECT getNearestParallelRoadFeature(out_partition, linegeo) + INTO out_parent_place_id; END IF; IF out_parent_place_id IS NULL THEN - FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP - out_parent_place_id := location.place_id; - END LOOP; + SELECT getNearestRoadPlaceId(out_partition, place_centroid) + INTO out_parent_place_id; END IF; --insert street(line) into import table