From 3b6c2c9155ada3706c9095efc51fcd4213141b4f Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Thu, 16 Jan 2020 21:43:26 +0100 Subject: [PATCH] getNearestNamed*Feature functions better return values --- sql/functions/aux_property.sql | 11 +++--- sql/functions/utils.sql | 9 ++--- sql/partition-functions.src.sql | 59 ++++++++++++++++----------------- sql/tiger_import_start.sql | 13 ++++---- 4 files changed, 41 insertions(+), 51 deletions(-) diff --git a/sql/functions/aux_property.sql b/sql/functions/aux_property.sql index b622f78b..841f240a 100644 --- a/sql/functions/aux_property.sql +++ b/sql/functions/aux_property.sql @@ -12,7 +12,7 @@ DECLARE out_partition INTEGER; out_parent_place_id BIGINT; location RECORD; - address_street_word_id INTEGER; + address_street_word_ids INTEGER[]; out_postcode TEXT; BEGIN @@ -21,11 +21,10 @@ BEGIN out_partition := get_partition(in_countrycode); out_parent_place_id := null; - address_street_word_id := get_name_id(make_standard_name(in_street)); - IF address_street_word_id IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP - out_parent_place_id := location.place_id; - END LOOP; + address_street_word_ids := word_ids_from_name(in_street); + IF address_street_word_ids IS NOT NULL THEN + out_parent_place_id := getNearestNamedRoadFeature(out_partition, place_centroid, + address_street_word_ids); END IF; IF out_parent_place_id IS NULL THEN diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql index 80eb12c5..741a3969 100644 --- a/sql/functions/utils.sql +++ b/sql/functions/utils.sql @@ -248,7 +248,6 @@ LANGUAGE plpgsql STABLE; -- \param centroid Location of the address. -- -- \return Place ID of the parent if one was found, NULL otherwise. --- The returned parent is always a street (rank 26/27 and a way). CREATE OR REPLACE FUNCTION find_parent_for_address(street TEXT, place TEXT, partition SMALLINT, centroid GEOMETRY) @@ -263,9 +262,7 @@ BEGIN -- Note that addr:street links can only be indexed, once the street itself is indexed word_ids := word_ids_from_name(street); IF word_ids is not null THEN - SELECT place_id - FROM getNearestNamedRoadFeature(partition, centroid, word_ids) - INTO parent_place_id; + parent_place_id := getNearestNamedRoadFeature(partition, centroid, word_ids); IF parent_place_id is not null THEN --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent.place_id; RETURN parent_place_id; @@ -277,9 +274,7 @@ BEGIN IF place is not null THEN word_ids := word_ids_from_name(place); IF word_ids is not null THEN - SELECT place_id - FROM getNearestNamedPlaceFeature(partition, centroid, word_ids) - INTO parent_place_id; + parent_place_id := getNearestNamedPlaceFeature(partition, centroid, word_ids); IF parent_place_id is not null THEN --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent.place_id; RETURN parent_place_id; diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index f770e83e..50f50db4 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -82,54 +82,51 @@ END $$ LANGUAGE plpgsql; -create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) - RETURNS setof nearfeature AS $$ +CREATE OR REPLACE FUNCTION getNearestNamedRoadFeature(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 centroid && ST_Expand(point, 0.015) - AND search_rank between 26 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 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 getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) - RETURNS setof nearfeature AS $$ +CREATE OR REPLACE FUNCTION getNearestNamedPlaceFeature(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 centroid && ST_Expand(point, 0.04) - AND search_rank between 16 and 22 - 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 22 + ORDER BY ST_Distance(centroid, point) ASC limit 1; + RETURN parent; END IF; -- end diff --git a/sql/tiger_import_start.sql b/sql/tiger_import_start.sql index ef55e11c..c5246854 100644 --- a/sql/tiger_import_start.sql +++ b/sql/tiger_import_start.sql @@ -2,7 +2,7 @@ DROP TABLE IF EXISTS location_property_tiger_import; CREATE TABLE location_property_tiger_import (linegeo GEOMETRY, place_id BIGINT, partition INTEGER, parent_place_id BIGINT, startnumber INTEGER, endnumber INTEGER, interpolationtype TEXT, postcode TEXT); CREATE OR REPLACE FUNCTION tiger_line_import(linegeo GEOMETRY, in_startnumber INTEGER, - in_endnumber INTEGER, interpolationtype TEXT, + in_endnumber INTEGER, interpolationtype TEXT, in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER AS $$ DECLARE @@ -14,7 +14,7 @@ DECLARE out_partition INTEGER; out_parent_place_id BIGINT; location RECORD; - address_street_word_id INTEGER; + address_street_word_ids INTEGER[]; BEGIN @@ -56,11 +56,10 @@ BEGIN out_partition := get_partition('us'); out_parent_place_id := null; - address_street_word_id := get_name_id(make_standard_name(in_street)); - IF address_street_word_id IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, ARRAY[address_street_word_id]) LOOP - out_parent_place_id := location.place_id; - END LOOP; + address_street_word_ids := word_ids_from_name(in_street); + IF address_street_word_ids IS NOT NULL THEN + out_parent_place_id := getNearestNamedRoadFeature(out_partition, place_centroid, + address_street_word_ids); END IF; IF out_parent_place_id IS NULL THEN -- 2.39.5