From: Sarah Hoffmann Date: Mon, 19 Oct 2020 08:39:01 +0000 (+0200) Subject: add explicit bbox contains check X-Git-Tag: v3.6.0~48 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/bf4d75458c6edf1fb957a8600aa6718811bb52e3?hp=-c add explicit bbox contains check Now that the containment check uses ST_Relate, we need to add a separate bbox contains check to ensure that Postgis does the efficient check first. Note that we still cannot get rid of the overlap(&&) check because then Postgis will use the wrong indexes. --- bf4d75458c6edf1fb957a8600aa6718811bb52e3 diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql index 9bc3469b..696a7c34 100644 --- a/sql/functions/placex_triggers.sql +++ b/sql/functions/placex_triggers.sql @@ -618,18 +618,21 @@ BEGIN NEW.rank_address := parent_address_level + 2; END IF; END IF; - -- Second check that the boundary is not completely contained in a - -- place area with a higher address rank - FOR location IN - SELECT rank_address FROM placex - WHERE class = 'place' and rank_address < 24 - and rank_address > NEW.rank_address - and geometry && NEW.geometry - and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal - ORDER BY rank_address desc LIMIT 1 - LOOP - NEW.rank_address := location.rank_address + 2; - END LOOP; + IF NEW.rank_address > 9 THEN + -- Second check that the boundary is not completely contained in a + -- place area with a higher address rank + FOR location IN + SELECT rank_address FROM placex + WHERE class = 'place' and rank_address < 24 + and rank_address > NEW.rank_address + and geometry && NEW.geometry + and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test + and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal + ORDER BY rank_address desc LIMIT 1 + LOOP + NEW.rank_address := location.rank_address + 2; + END LOOP; + END IF; ELSEIF NEW.class = 'place' and NEW.osm_type = 'N' and NEW.rank_address between 16 and 23 THEN