From 8fc3dd9457ba702511c65e9d14c8c5cdcdd9246f Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Sun, 30 Jul 2023 23:51:36 +0200 Subject: [PATCH] fix query over classtype tables The case statement prevented the index on the classtype tables from being used. Move the case statement inside the geometry function instead. --- nominatim/api/search/db_searches.py | 9 +++++---- nominatim/db/sqlalchemy_types.py | 6 +++++- 2 files changed, 10 insertions(+), 5 deletions(-) diff --git a/nominatim/api/search/db_searches.py b/nominatim/api/search/db_searches.py index 5c1d98c9..85dc3019 100644 --- a/nominatim/api/search/db_searches.py +++ b/nominatim/api/search/db_searches.py @@ -287,10 +287,11 @@ class NearSearch(AbstractSearch): # radius for the lookup. sql = sql.join(table, t.c.place_id == table.c.place_id)\ .join(tgeom, - sa.case((sa.and_(tgeom.c.rank_address < 9, - tgeom.c.geometry.is_area()), - tgeom.c.geometry.ST_Contains(table.c.centroid)), - else_ = tgeom.c.centroid.ST_DWithin(table.c.centroid, 0.05)))\ + table.c.centroid.ST_CoveredBy( + sa.case((sa.and_(tgeom.c.rank_address < 9, + tgeom.c.geometry.is_area()), + tgeom.c.geometry), + else_ = tgeom.c.centroid.ST_Expand(0.05))))\ .order_by(tgeom.c.centroid.ST_Distance(table.c.centroid)) sql = sql.where(t.c.rank_address.between(MIN_RANK_PARAM, MAX_RANK_PARAM)) diff --git a/nominatim/db/sqlalchemy_types.py b/nominatim/db/sqlalchemy_types.py index f31966cd..7d3789aa 100644 --- a/nominatim/db/sqlalchemy_types.py +++ b/nominatim/db/sqlalchemy_types.py @@ -74,7 +74,11 @@ class Geometry(types.UserDefinedType): # type: ignore[type-arg] def ST_Contains(self, other: SaColumn) -> SaColumn: - return sa.func.ST_Contains(self, other, type_=sa.Float) + return sa.func.ST_Contains(self, other, type_=sa.Boolean) + + + def ST_CoveredBy(self, other: SaColumn) -> SaColumn: + return sa.func.ST_CoveredBy(self, other, type_=sa.Boolean) def ST_ClosestPoint(self, other: SaColumn) -> SaColumn: -- 2.45.1