From: Sarah Hoffmann Date: Sun, 28 Jul 2019 13:18:12 +0000 (+0200) Subject: Merge pull request #1443 from lonvia/reorganize-search-name-partition-tables X-Git-Tag: v3.4.0~19 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/511204c158d4e218d2c2ca83e03b308b26487aa0?hp=65daef70c1f9aa80e6d37fc12198309e49b883ca Merge pull request #1443 from lonvia/reorganize-search-name-partition-tables Reorganize search name partition tables --- diff --git a/sql/functions.sql b/sql/functions.sql index bad4b17c..f696e231 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -1286,7 +1286,7 @@ BEGIN -- cheaper but less acurate place_centroid := ST_PointOnSurface(NEW.geometry); -- For searching near features rather use the centroid - near_centroid := ST_Centroid(NEW.geometry); + near_centroid := ST_Envelope(NEW.geometry); NEW.centroid := null; NEW.postcode := null; --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid); @@ -1521,7 +1521,7 @@ BEGIN NEW.postcode := location.postcode; END IF; IF NEW.postcode is null THEN - NEW.postcode := get_nearest_postcode(NEW.country_code, near_centroid); + NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry); END IF; END IF; @@ -1791,7 +1791,7 @@ BEGIN CASE WHEN NEW.rank_search >= 26 AND NEW.rank_search < 30 THEN NEW.geometry - ELSE near_centroid END, + ELSE place_centroid END, search_maxrank, isin_tokens) LOOP IF location.rank_address != location_rank_search THEN diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index 20f71584..f770e83e 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -95,7 +95,7 @@ BEGIN ST_Distance(centroid, point) as distance, null as isguess FROM search_name_-partition- WHERE name_vector && isin_token - AND ST_DWithin(centroid, point, 0.015) + AND centroid && ST_Expand(point, 0.015) AND search_rank between 26 and 27 ORDER BY distance ASC limit 1 LOOP @@ -123,7 +123,7 @@ BEGIN ST_Distance(centroid, point) as distance, null as isguess FROM search_name_-partition- WHERE name_vector && isin_token - AND ST_DWithin(centroid, point, 0.04) + AND centroid && ST_Expand(point, 0.04) AND search_rank between 16 and 22 ORDER BY distance ASC limit 1 LOOP diff --git a/sql/partition-tables.src.sql b/sql/partition-tables.src.sql index f651e7ac..57806898 100644 --- a/sql/partition-tables.src.sql +++ b/sql/partition-tables.src.sql @@ -42,8 +42,8 @@ CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large CREATE TABLE search_name_-partition- () INHERITS (search_name_blank) {ts:address-data}; CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id) {ts:address-index}; -CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid) {ts:address-index}; -CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off) {ts:address-index}; +CREATE INDEX idx_search_name_-partition-_centroid_street ON search_name_-partition- USING GIST (centroid) {ts:address-index} where search_rank between 26 and 27; +CREATE INDEX idx_search_name_-partition-_centroid_place ON search_name_-partition- USING GIST (centroid) {ts:address-index} where search_rank between 2 and 25; DROP TABLE IF EXISTS location_road_-partition-; CREATE TABLE location_road_-partition- ( diff --git a/test/bdd/db/import/parenting.feature b/test/bdd/db/import/parenting.feature index a3648539..9d8286c2 100644 --- a/test/bdd/db/import/parenting.feature +++ b/test/bdd/db/import/parenting.feature @@ -224,7 +224,7 @@ Feature: Parenting of objects When importing Then placex contains | object | parent_place_id | - | W1 | W3 | + | W1 | W2 | Scenario: Building with addr:street tags Given the scene building-on-street-corner diff --git a/test/bdd/db/update/parenting.feature b/test/bdd/db/update/parenting.feature new file mode 100644 index 00000000..50a647ad --- /dev/null +++ b/test/bdd/db/update/parenting.feature @@ -0,0 +1,38 @@ +@DB +Feature: Update parenting of objects + +Scenario: POI inside building inherits addr:street change + Given the scene building-on-street-corner + And the named places + | osm | class | type | geometry | + | N1 | amenity | bank | :n-inner | + | N2 | shop | bakery | :n-edge-NS | + | N3 | shop | supermarket| :n-edge-WE | + And the places + | osm | class | type | addr_place | housenr | geometry | + | W1 | building | yes | nowhere | 3 | :w-building | + And the places + | osm | class | type | name | geometry | + | W2 | highway | primary | bar | :w-WE | + | W3 | highway | residential | foo | :w-NS | + When importing + Then placex contains + | object | parent_place_id | housenumber | + | W1 | W2 | 3 | + | N1 | W3 | 3 | + | N2 | W3 | 3 | + | N3 | W2 | 3 | + When updating places + | osm | class | type | street | addr_place | housenr | geometry | + | W1 | building | yes | foo | nowhere | 3 | :w-building | + And updating places + | osm | class | type | name | geometry | + | N3 | shop | supermarket| well | :n-edge-WE | + Then placex contains + | object | parent_place_id | housenumber | + | W1 | W3 | 3 | + | N1 | W3 | 3 | + | N2 | W3 | 3 | + | N3 | W3 | 3 | + +