From: Sarah Hoffmann Date: Tue, 17 Oct 2023 08:57:52 +0000 (+0200) Subject: remove type info from SQLALchemy condition functions X-Git-Tag: v4.4.0~87^2~2 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/613c8635a81eedcdb5c52b4da3030fb2ad6ed7a6 remove type info from SQLALchemy condition functions A boolean type makes the SQLite dialect produce a costruct like 'func() = 1' in WHERE condition. While syntactically correct, it tends to confuse the query planer. --- diff --git a/nominatim/db/sqlalchemy_functions.py b/nominatim/db/sqlalchemy_functions.py index 65b813cb..cb04f762 100644 --- a/nominatim/db/sqlalchemy_functions.py +++ b/nominatim/db/sqlalchemy_functions.py @@ -17,14 +17,13 @@ from nominatim.typing import SaColumn # pylint: disable=all -class PlacexGeometryReverseLookuppolygon(sa.sql.functions.GenericFunction[bool]): +class PlacexGeometryReverseLookuppolygon(sa.sql.functions.GenericFunction[Any]): """ Check for conditions that allow partial index use on 'idx_placex_geometry_reverse_lookupPolygon'. Needs to be constant, so that the query planner picks them up correctly in prepared statements. """ - type = sa.Boolean() name = 'PlacexGeometryReverseLookuppolygon' inherit_cache = True @@ -51,8 +50,7 @@ def _sqlite_intersects(element: SaColumn, " AND placex.linked_place_id is null)") -class IntersectsReverseDistance(sa.sql.functions.GenericFunction[bool]): - type = sa.Boolean() +class IntersectsReverseDistance(sa.sql.functions.GenericFunction[Any]): name = 'IntersectsReverseDistance' inherit_cache = True @@ -66,12 +64,12 @@ class IntersectsReverseDistance(sa.sql.functions.GenericFunction[bool]): def default_reverse_place_diameter(element: SaColumn, compiler: 'sa.Compiled', **kw: Any) -> str: table = element.tablename - return f"{table}.rank_address between 4 and 25"\ + return f"({table}.rank_address between 4 and 25"\ f" AND {table}.type != 'postcode'"\ f" AND {table}.name is not null"\ f" AND {table}.linked_place_id is null"\ f" AND {table}.osm_type = 'N'" + \ - " AND ST_Buffer(%s, reverse_place_diameter(%s)) && %s" % \ + " AND ST_Buffer(%s, reverse_place_diameter(%s)) && %s)" % \ tuple(map(lambda c: compiler.process(c, **kw), element.clauses)) @@ -81,7 +79,7 @@ def sqlite_reverse_place_diameter(element: SaColumn, geom1, rank, geom2 = list(element.clauses) table = element.tablename - return (f"{table}.rank_address between 4 and 25"\ + return (f"({table}.rank_address between 4 and 25"\ f" AND {table}.type != 'postcode'"\ f" AND {table}.name is not null"\ f" AND {table}.linked_place_id is null"\ @@ -91,15 +89,14 @@ def sqlite_reverse_place_diameter(element: SaColumn, " (SELECT place_id FROM placex_place_node_areas"\ " WHERE ROWID IN (SELECT ROWID FROM SpatialIndex"\ " WHERE f_table_name = 'placex_place_node_areas'"\ - " AND search_frame = %s))") % ( + " AND search_frame = %s)))") % ( compiler.process(geom1, **kw), compiler.process(geom2, **kw), compiler.process(rank, **kw), compiler.process(geom2, **kw)) -class IsBelowReverseDistance(sa.sql.functions.GenericFunction[bool]): - type = sa.Boolean() +class IsBelowReverseDistance(sa.sql.functions.GenericFunction[Any]): name = 'IsBelowReverseDistance' inherit_cache = True @@ -132,8 +129,7 @@ def select_index_placex_geometry_reverse_lookupplacenode(table: str) -> 'sa.Text f" AND {table}.osm_type = 'N'") -class IsAddressPoint(sa.sql.functions.GenericFunction[bool]): - type = sa.Boolean() +class IsAddressPoint(sa.sql.functions.GenericFunction[Any]): name = 'IsAddressPoint' inherit_cache = True @@ -162,11 +158,10 @@ def sqlite_is_address_point(element: SaColumn, compiler.process(name, **kw)) -class CrosscheckNames(sa.sql.functions.GenericFunction[bool]): +class CrosscheckNames(sa.sql.functions.GenericFunction[Any]): """ Check if in the given list of names in parameters 1 any of the names from the JSON array in parameter 2 are contained. """ - type = sa.Boolean() name = 'CrosscheckNames' inherit_cache = True diff --git a/nominatim/db/sqlalchemy_types.py b/nominatim/db/sqlalchemy_types.py index 036b25dd..a36e8c46 100644 --- a/nominatim/db/sqlalchemy_types.py +++ b/nominatim/db/sqlalchemy_types.py @@ -41,10 +41,9 @@ def _spatialite_distance_spheroid(element: SaColumn, return "COALESCE(Distance(%s, true), 0.0)" % compiler.process(element.clauses, **kw) -class Geometry_IsLineLike(sa.sql.expression.FunctionElement[bool]): +class Geometry_IsLineLike(sa.sql.expression.FunctionElement[Any]): """ Check if the geometry is a line or multiline. """ - type = sa.Boolean() name = 'Geometry_IsLineLike' inherit_cache = True @@ -63,10 +62,9 @@ def _sqlite_is_line_like(element: SaColumn, compiler.process(element.clauses, **kw) -class Geometry_IsAreaLike(sa.sql.expression.FunctionElement[bool]): +class Geometry_IsAreaLike(sa.sql.expression.FunctionElement[Any]): """ Check if the geometry is a polygon or multipolygon. """ - type = sa.Boolean() name = 'Geometry_IsLineLike' inherit_cache = True @@ -85,10 +83,9 @@ def _sqlite_is_area_like(element: SaColumn, compiler.process(element.clauses, **kw) -class Geometry_IntersectsBbox(sa.sql.expression.FunctionElement[bool]): +class Geometry_IntersectsBbox(sa.sql.expression.FunctionElement[Any]): """ Check if the bounding boxes of the given geometries intersect. """ - type = sa.Boolean() name = 'Geometry_IntersectsBbox' inherit_cache = True @@ -103,16 +100,15 @@ def _default_intersects(element: SaColumn, @compiles(Geometry_IntersectsBbox, 'sqlite') # type: ignore[no-untyped-call, misc] def _sqlite_intersects(element: SaColumn, compiler: 'sa.Compiled', **kw: Any) -> str: - return "MbrIntersects(%s)" % compiler.process(element.clauses, **kw) + return "MbrIntersects(%s) = 1" % compiler.process(element.clauses, **kw) -class Geometry_ColumnIntersectsBbox(sa.sql.expression.FunctionElement[bool]): +class Geometry_ColumnIntersectsBbox(sa.sql.expression.FunctionElement[Any]): """ Check if the bounding box of the geometry intersects with the given table column, using the spatial index for the column. The index must exist or the query may return nothing. """ - type = sa.Boolean() name = 'Geometry_ColumnIntersectsBbox' inherit_cache = True @@ -128,7 +124,7 @@ def default_intersects_column(element: SaColumn, def spatialite_intersects_column(element: SaColumn, compiler: 'sa.Compiled', **kw: Any) -> str: arg1, arg2 = list(element.clauses) - return "MbrIntersects(%s, %s) and "\ + return "MbrIntersects(%s, %s) = 1 and "\ "%s.ROWID IN (SELECT ROWID FROM SpatialIndex "\ "WHERE f_table_name = '%s' AND f_geometry_column = '%s' "\ "AND search_frame = %s)" %( @@ -138,13 +134,12 @@ def spatialite_intersects_column(element: SaColumn, compiler.process(arg2, **kw)) -class Geometry_ColumnDWithin(sa.sql.expression.FunctionElement[bool]): +class Geometry_ColumnDWithin(sa.sql.expression.FunctionElement[Any]): """ Check if the geometry is within the distance of the given table column, using the spatial index for the column. The index must exist or the query may return nothing. """ - type = sa.Boolean() name = 'Geometry_ColumnDWithin' inherit_cache = True @@ -320,8 +315,7 @@ for alias in SQLITE_FUNCTION_ALIAS: _add_function_alias(*alias) -class ST_DWithin(sa.sql.functions.GenericFunction[bool]): - type = sa.Boolean() +class ST_DWithin(sa.sql.functions.GenericFunction[Any]): name = 'ST_DWithin' inherit_cache = True