From 01f0f453dd349d94854c7a8ea0ac700fc86d0a4e Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Tue, 14 May 2024 11:35:14 +0200 Subject: [PATCH] drop interpolations when no parent can be found A housenumber without the place it refers to is of only limited use. --- lib-sql/functions/interpolation.sql | 11 ++- test/bdd/db/import/interpolation.feature | 97 ++++++++++++++++++- .../osm2pgsql/update/interpolations.feature | 4 + test/bdd/osm2pgsql/update/postcodes.feature | 4 + 4 files changed, 110 insertions(+), 6 deletions(-) diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql index 928d55c5..8bc9ad42 100644 --- a/lib-sql/functions/interpolation.sql +++ b/lib-sql/functions/interpolation.sql @@ -67,10 +67,6 @@ BEGIN END LOOP; END IF; - IF parent_place_id is null THEN - RETURN 0; - END IF; - RETURN parent_place_id; END; $$ @@ -183,6 +179,13 @@ BEGIN ST_PointOnSurface(NEW.linegeo), NEW.linegeo); + -- Cannot find a parent street. We will not be able to display a reliable + -- address, so drop entire interpolation. + IF NEW.parent_place_id is NULL THEN + DELETE FROM location_property_osmline where place_id = OLD.place_id; + RETURN NULL; + END IF; + NEW.token_info := token_strip_info(NEW.token_info); IF NEW.address ? '_inherited' THEN NEW.address := hstore('interpolation', NEW.address->'interpolation'); diff --git a/test/bdd/db/import/interpolation.feature b/test/bdd/db/import/interpolation.feature index 3c67d652..6b784b78 100644 --- a/test/bdd/db/import/interpolation.feature +++ b/test/bdd/db/import/interpolation.feature @@ -2,9 +2,26 @@ Feature: Import of address interpolations Tests that interpolated addresses are added correctly + Scenario: Simple even interpolation line with two points and no street nearby + Given the grid with origin 1,1 + | 1 | | 9 | | 2 | + Given the places + | osm | class | type | housenr | + | N1 | place | house | 2 | + | N2 | place | house | 6 | + And the places + | osm | class | type | addr+interpolation | geometry | + | W1 | place | houses | even | 1,2 | + And the ways + | id | nodes | + | 1 | 1,2 | + When importing + Then W1 expands to no interpolation + Scenario: Simple even interpolation line with two points Given the grid with origin 1,1 | 1 | | 9 | | 2 | + | 4 | | | | 5 | Given the places | osm | class | type | housenr | | N1 | place | house | 2 | @@ -12,6 +29,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 1,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 1,2 | @@ -23,6 +43,7 @@ Feature: Import of address interpolations Scenario: Backwards even two point interpolation line Given the grid with origin 1,1 | 1 | 8 | 9 | 2 | + | 4 | | | 5 | Given the places | osm | class | type | housenr | | N1 | place | house | 2 | @@ -30,6 +51,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 2,1 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 2,1 | @@ -41,6 +65,7 @@ Feature: Import of address interpolations Scenario: Simple odd two point interpolation Given the grid with origin 1,1 | 1 | 8 | | | 9 | 2 | + | 4 | | | | 5 | | Given the places | osm | class | type | housenr | | N1 | place | house | 1 | @@ -48,6 +73,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | odd | 1,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 1,2 | @@ -59,6 +87,7 @@ Feature: Import of address interpolations Scenario: Simple all two point interpolation Given the grid with origin 1,1 | 1 | 8 | 9 | 2 | + | 4 | | | 5 | Given the places | osm | class | type | housenr | | N1 | place | house | 1 | @@ -66,6 +95,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | all | 1,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 1,2 | @@ -77,6 +109,7 @@ Feature: Import of address interpolations Scenario: Even two point interpolation line with intermediate empty node Given the grid | 1 | 8 | | 3 | 9 | 2 | + | 4 | | | | 5 | | Given the places | osm | class | type | housenr | | N1 | place | house | 2 | @@ -84,6 +117,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 1,3,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 1,3,2 | @@ -94,6 +130,7 @@ Feature: Import of address interpolations Scenario: Even two point interpolation line with intermediate duplicated empty node Given the grid + | 4 | | | | 5 | | 1 | 8 | 3 | 9 | 2 | Given the places | osm | class | type | housenr | @@ -102,6 +139,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 1,3,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 1,3,3,2 | @@ -112,6 +152,7 @@ Feature: Import of address interpolations Scenario: Simple even three point interpolation line Given the grid + | 4 | | | | | | 5 | | 1 | 8 | | 9 | 3 | 7 | 2 | Given the places | osm | class | type | housenr | @@ -121,6 +162,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 1,3,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 1,3,2 | @@ -144,6 +188,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 1,3,2,4 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 1,3,2,4 | And the ways | id | nodes | | 1 | 1,3,2,4 | @@ -157,6 +204,7 @@ Feature: Import of address interpolations Scenario: Reverse simple even three point interpolation line Given the grid | 1 | 8 | | 9 | 3 | 7 | 2 | + | 4 | | | | | | 5 | Given the places | osm | class | type | housenr | | N1 | place | house | 2 | @@ -165,6 +213,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 2,3,1 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 2,3,1 | @@ -177,6 +228,7 @@ Feature: Import of address interpolations Scenario: Even three point interpolation line with odd center point Given the grid | 1 | | 10 | | | 11 | 3 | 2 | + | 4 | | | | | | | 5 | Given the places | osm | class | type | housenr | | N1 | place | house | 2 | @@ -185,6 +237,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 1,3,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 1,3,2 | @@ -206,6 +261,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 1,2,3,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 1,2,3 | And the ways | id | nodes | | 1 | 1,2,3,2 | @@ -227,6 +285,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 1,2,3,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 1,2,3 | And the ways | id | nodes | | 1 | 1,2,3,2 | @@ -334,6 +395,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 144.9632341 -37.76163,144.9630541 -37.7628172,144.9629794 -37.7630755 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 144.9632341 -37.76163,144.9629794 -37.7630755 | And the ways | id | nodes | | 1 | 1,2,3 | @@ -346,6 +410,7 @@ Feature: Import of address interpolations Scenario: Place with missing address information Given the grid | 1 | | 2 | | | 3 | + | 4 | | | | | 5 | And the places | osm | class | type | housenr | | N1 | place | house | 23 | @@ -354,6 +419,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | odd | 1,2,3 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 1,2,3 | @@ -366,12 +434,16 @@ Feature: Import of address interpolations Given the places | osm | class | type | housenr | geometry | | W1 | place | houses | even | 1 1, 1 1.001 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 1 1, 1 1.001 | When importing Then W1 expands to no interpolation Scenario: Ways with nodes without housenumbers are ignored Given the grid - | 1 | | 2 | + | 1 | | 2 | + | 4 | | 5 | Given the places | osm | class | type | | N1 | place | house | @@ -379,12 +451,16 @@ Feature: Import of address interpolations Given the places | osm | class | type | housenr | geometry | | W1 | place | houses | even | 1,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | When importing Then W1 expands to no interpolation Scenario: Two point interpolation starting at 0 Given the grid with origin 1,1 | 1 | 10 | | | 11 | 2 | + | 4 | | | | | 5 | Given the places | osm | class | type | housenr | | N1 | place | house | 0 | @@ -392,6 +468,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 1,2 | + And the places + | osm | class | type | name | geometry | + | W10 | highway | residential | London Road |4,5 | And the ways | id | nodes | | 1 | 1,2 | @@ -402,7 +481,7 @@ Feature: Import of address interpolations When sending v1/reverse at 1,1 Then results contain | ID | osm | type | display_name | - | 0 | N1 | house | 0 | + | 0 | N1 | house | 0, London Road | Scenario: Parenting of interpolation with additional tags Given the grid @@ -438,6 +517,7 @@ Feature: Import of address interpolations Scenario Outline: Bad interpolation values are ignored Given the grid with origin 1,1 | 1 | | 9 | | 2 | + | 4 | | | | 5 | Given the places | osm | class | type | housenr | | N1 | place | house | 2 | @@ -445,6 +525,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | | 1,2 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 1,2 | @@ -470,6 +553,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 1,2,3,4 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 1,4 | And the ways | id | nodes | | 1 | 1,2,3,4 | @@ -484,6 +570,7 @@ Feature: Import of address interpolations Scenario: Interpolation line with duplicated points Given the grid | 7 | 10 | 8 | 11 | 9 | + | 4 | | | | 5 | Given the places | osm | class | type | housenr | geometry | | N1 | place | house | 2 | 7 | @@ -493,6 +580,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 7,8,8,9 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 4,5 | And the ways | id | nodes | | 1 | 1,2,3,4 | @@ -515,6 +605,9 @@ Feature: Import of address interpolations And the places | osm | class | type | addr+interpolation | geometry | | W1 | place | houses | even | 8,9 | + And the named places + | osm | class | type | geometry | + | W10 | highway | residential | 1,4 | And the ways | id | nodes | | 1 | 1,8,9,2,3,4 | diff --git a/test/bdd/osm2pgsql/update/interpolations.feature b/test/bdd/osm2pgsql/update/interpolations.feature index dae5168b..9ca26285 100644 --- a/test/bdd/osm2pgsql/update/interpolations.feature +++ b/test/bdd/osm2pgsql/update/interpolations.feature @@ -13,6 +13,7 @@ Feature: Updates of address interpolation objects """ n1 Taddr:housenumber=3 n2 Taddr:housenumber=17 + w33 Thighway=residential,name=Tao Nn1,n2 """ Then place contains | object | type | @@ -33,6 +34,7 @@ Feature: Updates of address interpolation objects | object | type | | N1:place | house | | N2:place | house | + | W33:highway | residential | Then location_property_osmline contains exactly | object | | 99:5 | @@ -73,6 +75,7 @@ Feature: Updates of address interpolation objects """ n1 Taddr:housenumber=3 n2 Taddr:housenumber=17 + w33 Thighway=residential Nn1,n2 w99 Thighway=residential Nn1,n2 """ Then place contains @@ -95,6 +98,7 @@ Feature: Updates of address interpolation objects | object | type | | N1:place | house | | N2:place | house | + | W33:highway | residential | And location_property_osmline contains exactly | object | | 99:5 | diff --git a/test/bdd/osm2pgsql/update/postcodes.feature b/test/bdd/osm2pgsql/update/postcodes.feature index 23f86f76..6bd61af7 100644 --- a/test/bdd/osm2pgsql/update/postcodes.feature +++ b/test/bdd/osm2pgsql/update/postcodes.feature @@ -136,12 +136,14 @@ Feature: Update of postcode only objects """ n1 Taddr:housenumber=3 n2 Taddr:housenumber=17 + w33 Thighway=residential Nn1,n2 w34 Tpostcode=4456 Nn1,n2 """ Then place contains exactly | object | type | | N1:place | house | | N2:place | house | + | W33:highway | residential | | W34:place | postcode | When updating osm data @@ -152,6 +154,7 @@ Feature: Update of postcode only objects | object | type | | N1:place | house | | N2:place | house | + | W33:highway | residential | | W34:place | houses | When indexing Then location_property_osmline contains exactly @@ -161,3 +164,4 @@ Feature: Update of postcode only objects | object | type | | N1:place | house | | N2:place | house | + | W33:highway | residential | -- 2.39.5