From: Sarah Hoffmann Date: Wed, 8 Aug 2018 20:05:17 +0000 (+0200) Subject: Merge remote-tracking branch 'upstream/master' X-Git-Tag: deploy~314 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/7b44cb9577ca6b9fb6c82be86a343730cf4ed5b8?hp=ec8963f3d2e18a197aee8576889400fb058150d3 Merge remote-tracking branch 'upstream/master' --- diff --git a/lib/ReverseGeocode.php b/lib/ReverseGeocode.php index 5648fedf..60f35449 100644 --- a/lib/ReverseGeocode.php +++ b/lib/ReverseGeocode.php @@ -113,9 +113,8 @@ class ReverseGeocode $sSQL .= ' FROM placex'; $sSQL .= ' WHERE osm_type = \'N\''; $sSQL .= ' AND country_code = \''.$sCountryCode.'\''; - $sSQL .= ' AND rank_search > 4'; - $sSQL .= ' AND rank_search <= ' .min(25, $iMaxRank); - $sSQL .= ' AND type != \'postcode\''; + $sSQL .= ' AND rank_search between 5 and ' .min(25, $iMaxRank); + $sSQL .= ' AND class = \'place\' AND type != \'postcode\''; $sSQL .= ' AND name IS NOT NULL '; $sSQL .= ' and indexed_status = 0 and linked_place_id is null'; $sSQL .= ' AND ST_DWithin('.$sPointSQL.', geometry, 5.0)) p '; @@ -194,13 +193,12 @@ class ReverseGeocode // using rank_search because of a better differentiation // for place nodes at rank_address 16 $sSQL .= ' AND rank_search > '.$iRankSearch; - $sSQL .= ' AND rank_search <= ' .$iMaxRank; + $sSQL .= ' AND rank_search <= '.$iMaxRank; $sSQL .= ' AND class = \'place\''; $sSQL .= ' AND type != \'postcode\''; $sSQL .= ' AND name IS NOT NULL '; $sSQL .= ' AND indexed_status = 0 AND linked_place_id is null'; - // preselection through bbox - $sSQL .= ' AND (SELECT geometry FROM placex WHERE place_id = '.$iPlaceID.') && geometry'; + $sSQL .= ' AND ST_DWithin('.$sPointSQL.', geometry, reverse_place_diameter('.$iRankSearch.'::smallint))'; $sSQL .= ' ORDER BY distance ASC,'; $sSQL .= ' rank_address DESC'; $sSQL .= ' limit 500) as a'; diff --git a/sql/functions.sql b/sql/functions.sql index 9a1f228d..33a5e6b9 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -864,6 +864,10 @@ BEGIN SELECT * FROM get_postcode_rank(NEW.country_code, NEW.address->'postcode') INTO NEW.rank_search, NEW.rank_address; + IF NOT ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon') THEN + NEW.rank_address := 0; + END IF; + ELSEIF NEW.class = 'place' THEN IF NEW.type in ('continent') THEN NEW.rank_search := 2; diff --git a/sql/indices.src.sql b/sql/indices.src.sql index 0bdebffb..32ac8a3d 100644 --- a/sql/indices.src.sql +++ b/sql/indices.src.sql @@ -14,14 +14,24 @@ CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search) {ts:sear CREATE INDEX idx_placex_rank_address ON placex USING BTREE (rank_address) {ts:search-index}; CREATE INDEX idx_placex_pendingsector ON placex USING BTREE (rank_search,geometry_sector) {ts:address-index} where indexed_status > 0; CREATE INDEX idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) {ts:search-index} where parent_place_id IS NOT NULL; -CREATE INDEX idx_placex_geometry_reverse_lookupPoint ON placex USING gist (geometry) {ts:search-index} -where (name is not null or housenumber is not null or rank_address between 26 and 27) and class not in ('railway','tunnel','bridge','man_made') and rank_address >= 26 and indexed_status = 0 and linked_place_id is null; -CREATE INDEX idx_placex_geometry_reverse_lookupPolygon ON placex USING gist (geometry) {ts:search-index} where St_GeometryType(geometry) in ('ST_Polygon', 'ST_MultiPolygon') and rank_address between 4 and 25 and type != 'postcode' and name is not null and indexed_status = 0 and linked_place_id is null; -CREATE INDEX idx_placex_geometry_reverse_placeNode_rank_search ON placex USING gist (geometry) {ts:search-index} where -osm_type = 'N' and rank_search > 0 and rank_search <= 25 and class = 'place' and type != 'postcode' and name is not null and indexed_status = 0 and linked_place_id is null; -CREATE INDEX idx_placex_geometry_reverse_placeNode_rank_address ON placex USING gist (geometry) {ts:search-index} where -osm_type = 'N' and rank_address > 0 and rank_address <= 25 and type != 'postcode' and name is not null and indexed_status = 0 and linked_place_id is null; -GRANT SELECT ON Table country_osm_grid to "{www-user}"; + +CREATE INDEX idx_placex_geometry_reverse_lookupPoint + ON placex USING gist (geometry) {ts:search-index} + WHERE (name is not null or housenumber is not null or rank_address between 26 and 27) + AND class not in ('railway','tunnel','bridge','man_made') + AND rank_address >= 26 AND indexed_status = 0 AND linked_place_id is null; +CREATE INDEX idx_placex_geometry_reverse_lookupPolygon + ON placex USING gist (geometry) {ts:search-index} + WHERE St_GeometryType(geometry) in ('ST_Polygon', 'ST_MultiPolygon') + AND rank_address between 4 and 25 AND type != 'postcode' + AND name is not null AND indexed_status = 0 AND linked_place_id is null; +CREATE INDEX idx_placex_geometry_reverse_placeNode + ON placex USING gist (geometry) {ts:search-index} + WHERE osm_type = 'N' AND rank_search between 5 and 25 + AND class = 'place' AND type != 'postcode' + AND name is not null AND indexed_status = 0 AND linked_place_id is null; + +GRANT SELECT ON table country_osm_grid to "{www-user}"; CREATE INDEX idx_location_area_country_place_id ON location_area_country USING BTREE (place_id) {ts:address-index}; diff --git a/test/bdd/db/import/addressing.feature b/test/bdd/db/import/addressing.feature index 2bff8249..83dc4774 100644 --- a/test/bdd/db/import/addressing.feature +++ b/test/bdd/db/import/addressing.feature @@ -20,3 +20,41 @@ Feature: Address computation | object | address | cached_rank_address | | W1 | W10 | 10 | | W1 | W11 | 10 | + + Scenario: buildings with only addr:postcodes do not appear in the address of a way + Given the scene admin-areas + And the named places + | osm | class | type | admin | addr+postcode | geometry | + | R1 | boundary | administrative | 6 | 112 | :b0 | + | R34 | boundary | administrative | 8 | 112 DE | :b1:E | + | R4 | boundary | administrative | 10 | 112 DE 34 | :b2:N | + And the named places + | osm | class | type | geometry | + | W93 | highway | residential | :w2N | + And the places + | osm | class | type | addr+postcode | geometry | + | W22 | place | postcode | 445023 | :building:w2N | + When importing + Then place_addressline doesn't contain + | object | address | + | W93 | W22 | + + Scenario: postcode boundaries do appear in the address of a way + Given the scene admin-areas + And the named places + | osm | class | type | admin | addr+postcode | geometry | + | R1 | boundary | administrative | 6 | 112 | :b0 | + | R34 | boundary | administrative | 8 | 112 DE | :b1:E | + And the places + | osm | class | type | addr+postcode | geometry | + | R4 | place | postcode | 112 DE 34 | :b2:N | + And the named places + | osm | class | type | geometry | + | W93 | highway | residential | :w2N | + And the places + | osm | class | type | addr+postcode | geometry | + | W22 | place | postcode | 445023 | :building:w2N | + When importing + Then place_addressline contains + | object | address | + | W93 | R4 | diff --git a/test/bdd/db/import/placex.feature b/test/bdd/db/import/placex.feature index f3695642..7c5c73e2 100644 --- a/test/bdd/db/import/placex.feature +++ b/test/bdd/db/import/placex.feature @@ -70,9 +70,9 @@ Feature: Import into placex When importing Then placex contains | object | postcode | country_code | rank_search | rank_address | - | N1 | E45 2CD | gb | 25 | 5 | - | N2 | E45 2 | gb | 23 | 5 | - | N3 | Y45 | gb | 21 | 5 | + | N1 | E45 2CD | gb | 25 | 0 | + | N2 | E45 2 | gb | 23 | 0 | + | N3 | Y45 | gb | 21 | 0 | Scenario: wrongly formatted GB postcodes are down-ranked Given the places @@ -82,8 +82,8 @@ Feature: Import into placex When importing Then placex contains | object | country_code | rank_search | rank_address | - | N1 | gb | 30 | 30 | - | N2 | gb | 30 | 30 | + | N1 | gb | 30 | 0 | + | N2 | gb | 30 | 0 | Scenario: search and address rank for DE postcodes correctly assigned Given the places @@ -95,10 +95,10 @@ Feature: Import into placex When importing Then placex contains | object | country_code | rank_search | rank_address | - | N1 | de | 21 | 11 | - | N2 | de | 30 | 30 | - | N3 | de | 30 | 30 | - | N4 | de | 30 | 30 | + | N1 | de | 21 | 0 | + | N2 | de | 30 | 0 | + | N3 | de | 30 | 0 | + | N4 | de | 30 | 0 | Scenario: search and address rank for other postcodes are correctly assigned Given the places @@ -115,15 +115,15 @@ Feature: Import into placex When importing Then placex contains | object | country_code | rank_search | rank_address | - | N1 | ca | 21 | 11 | - | N2 | ca | 21 | 11 | - | N3 | ca | 21 | 11 | - | N4 | ca | 21 | 11 | - | N5 | ca | 21 | 11 | - | N6 | ca | 21 | 11 | - | N7 | ca | 25 | 11 | - | N8 | ca | 25 | 11 | - | N9 | ca | 25 | 11 | + | N1 | ca | 21 | 0 | + | N2 | ca | 21 | 0 | + | N3 | ca | 21 | 0 | + | N4 | ca | 21 | 0 | + | N5 | ca | 21 | 0 | + | N6 | ca | 21 | 0 | + | N7 | ca | 25 | 0 | + | N8 | ca | 25 | 0 | + | N9 | ca | 25 | 0 | Scenario: search and address ranks for places are correctly assigned Given the named places diff --git a/test/bdd/db/import/postcodes.feature b/test/bdd/db/import/postcodes.feature index 7fde34d3..940ac5b6 100644 --- a/test/bdd/db/import/postcodes.feature +++ b/test/bdd/db/import/postcodes.feature @@ -82,7 +82,7 @@ Feature: Import of postcodes | object | postcode | | W22 | 112 DE 34 | - Scenario: Roads get postcodes from nearby buildings without other info + Scenario: Roads get postcodes from nearby named buildings without other info Given the scene admin-areas And the named places | osm | class | type | geometry | @@ -95,6 +95,19 @@ Feature: Import of postcodes | object | postcode | | W93 | 445023 | + Scenario: Roads get postcodes from nearby unnamed buildings without other info + Given the scene admin-areas + And the named places + | osm | class | type | geometry | + | W93 | highway | residential | :w2N | + And the named places + | osm | class | type | addr+postcode | geometry | + | W22 | place | postcode | 445023 | :building:w2N | + When importing + Then placex contains + | object | postcode | + | W93 | 445023 | + Scenario: Postcodes from admin boundaries are preferred over estimated postcodes Given the scene admin-areas And the named places diff --git a/test/bdd/steps/db_ops.py b/test/bdd/steps/db_ops.py index a0924840..b50f1d59 100644 --- a/test/bdd/steps/db_ops.py +++ b/test/bdd/steps/db_ops.py @@ -498,6 +498,21 @@ def check_place_addressline(context): context.db.commit() +@then("place_addressline doesn't contain") +def check_place_addressline_exclude(context): + cur = context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) + + for row in context.table: + pid = NominatimID(row['object']).get_place_id(cur) + apid = NominatimID(row['address']).get_place_id(cur) + cur.execute(""" SELECT * FROM place_addressline + WHERE place_id = %s AND address_place_id = %s""", + (pid, apid)) + eq_(0, cur.rowcount, + "Row found for place %s and address %s" % (row['object'], row['address'])) + + context.db.commit() + @then("(?P\w+) expands to(?P no)? interpolation") def check_location_property_osmline(context, oid, neg): cur = context.db.cursor(cursor_factory=psycopg2.extras.DictCursor)