From 1347abb1e74dd72a6b9f98c40c54c261b2463373 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Tue, 4 Aug 2020 12:08:50 +0200 Subject: [PATCH] be more strict what areas make up an address Exclude boundaries that touch a line in only one point and that touch areas only along the boundary. Fixes #1900. --- sql/partition-functions.src.sql | 25 ++++++- test/bdd/db/import/addressing.feature | 93 +++++++++++++++++++++++++++ 2 files changed, 117 insertions(+), 1 deletion(-) diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index 34b4d390..17db9c16 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -10,6 +10,28 @@ CREATE TYPE nearfeaturecentr AS ( 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; @@ -20,7 +42,8 @@ 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) + 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, diff --git a/test/bdd/db/import/addressing.feature b/test/bdd/db/import/addressing.feature index f4297f30..ddb7b438 100644 --- a/test/bdd/db/import/addressing.feature +++ b/test/bdd/db/import/addressing.feature @@ -21,6 +21,99 @@ Feature: Address computation | W1 | W10 | 10 | | W1 | W11 | 10 | + + Scenario: Roads following a boundary should contain both states + Given the grid + | 1 | | | 2 | | 3 | + | | | 8 | 7 | | | + | 4 | | | 5 | | 6 | + And the named places + | osm | class | type | geometry | + | W1 | highway | road | 2, 7, 8 | + And the named places + | osm | class | type | admin | geometry | + | W10 | boundary | administrative | 5 | (1, 2, 5, 4, 1) | + | W11 | boundary | administrative | 5 | (2, 3, 6, 5, 2) | + When importing + Then place_addressline contains + | object | address | cached_rank_address | + | W1 | W10 | 10 | + | W1 | W11 | 10 | + + Scenario: Roads should not contain boundaries they touch in a end point + Given the grid + | 1 | | | 2 | | 3 | + | | 7 | | 8 | | | + | 4 | | | 5 | | 6 | + And the named places + | osm | class | type | geometry | + | W1 | highway | road | 7, 8 | + And the named places + | osm | class | type | admin | geometry | + | W10 | boundary | administrative | 5 | (1, 2, 8, 5, 4, 1) | + | W11 | boundary | administrative | 5 | (2, 3, 6, 5, 8, 2) | + When importing + Then place_addressline contains + | object | address | cached_rank_address | + | W1 | W10 | 10 | + Then place_addressline doesn't contain + | object | address | + | W1 | W11 | + + Scenario: Roads should not contain boundaries they touch in a end point + Given the grid + | 1 | | | 2 | | 3 | + | | 7 | | 8 | | | + | 4 | | 9 | 5 | | 6 | + And the named places + | osm | class | type | geometry | + | W1 | highway | road | 7, 8, 9 | + And the named places + | osm | class | type | admin | geometry | + | W10 | boundary | administrative | 5 | (1, 2, 8, 5, 4, 1) | + | W11 | boundary | administrative | 5 | (2, 3, 6, 5, 8, 2) | + When importing + Then place_addressline contains + | object | address | cached_rank_address | + | W1 | W10 | 10 | + Then place_addressline doesn't contain + | object | address | + | W1 | W11 | + + Scenario: Locality points should contain all boundaries they touch + Given the grid + | 1 | | | 2 | | 3 | + | | | | 8 | | | + | 4 | | | 5 | | 6 | + And the named places + | osm | class | type | geometry | + | N1 | place | locality | 8 | + And the named places + | osm | class | type | admin | geometry | + | W10 | boundary | administrative | 5 | (1, 2, 8, 5, 4, 1) | + | W11 | boundary | administrative | 5 | (2, 3, 6, 5, 8, 2) | + When importing + Then place_addressline contains + | object | address | cached_rank_address | + | N1 | W10 | 10 | + | N1 | W11 | 10 | + + Scenario: Areas should not contain boundaries they touch + Given the grid + | 1 | | | 2 | | 3 | + | | | | | | | + | 4 | | | 5 | | 6 | + And the named places + | osm | class | type | geometry | + | W1 | landuse | industrial | (1, 2, 5, 4, 1) | + And the named places + | osm | class | type | admin | geometry | + | W10 | boundary | administrative | 5 | (2, 3, 6, 5, 2) | + When importing + Then place_addressline doesn't contain + | object | address | + | W1 | W10 | + Scenario: buildings with only addr:postcodes do not appear in the address of a way Given the scene admin-areas And the named places -- 2.45.2