X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/041794615320f836ed58511437d29ba0b1379777..8a1af9b56659d4ef956f45da2928687a17dea20a:/nominatim/db/sqlalchemy_types.py

diff --git a/nominatim/db/sqlalchemy_types.py b/nominatim/db/sqlalchemy_types.py
index df2bf150..a36e8c46 100644
--- a/nominatim/db/sqlalchemy_types.py
+++ b/nominatim/db/sqlalchemy_types.py
@@ -38,13 +38,12 @@ def _default_distance_spheroid(element: SaColumn,
 @compiles(Geometry_DistanceSpheroid, 'sqlite') # type: ignore[no-untyped-call, misc]
 def _spatialite_distance_spheroid(element: SaColumn,
                                   compiler: 'sa.Compiled', **kw: Any) -> str:
-    return "Distance(%s, true)" % compiler.process(element.clauses, **kw)
+    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,7 +100,70 @@ 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[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.
+    """
+    name = 'Geometry_ColumnIntersectsBbox'
+    inherit_cache = True
+
+
+@compiles(Geometry_ColumnIntersectsBbox) # type: ignore[no-untyped-call, misc]
+def default_intersects_column(element: SaColumn,
+                              compiler: 'sa.Compiled', **kw: Any) -> str:
+    arg1, arg2 = list(element.clauses)
+    return "%s && %s" % (compiler.process(arg1, **kw), compiler.process(arg2, **kw))
+
+
+@compiles(Geometry_ColumnIntersectsBbox, 'sqlite') # type: ignore[no-untyped-call, misc]
+def spatialite_intersects_column(element: SaColumn,
+                                 compiler: 'sa.Compiled', **kw: Any) -> str:
+    arg1, arg2 = list(element.clauses)
+    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)" %(
+              compiler.process(arg1, **kw),
+              compiler.process(arg2, **kw),
+              arg1.table.name, arg1.table.name, arg1.name,
+              compiler.process(arg2, **kw))
+
+
+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.
+    """
+    name = 'Geometry_ColumnDWithin'
+    inherit_cache = True
+
+
+@compiles(Geometry_ColumnDWithin) # type: ignore[no-untyped-call, misc]
+def default_dwithin_column(element: SaColumn,
+                           compiler: 'sa.Compiled', **kw: Any) -> str:
+    return "ST_DWithin(%s)" % compiler.process(element.clauses, **kw)
+
+@compiles(Geometry_ColumnDWithin, 'sqlite') # type: ignore[no-untyped-call, misc]
+def spatialite_dwithin_column(element: SaColumn,
+                              compiler: 'sa.Compiled', **kw: Any) -> str:
+    geom1, geom2, dist = list(element.clauses)
+    return "ST_Distance(%s, %s) < %s and "\
+           "%s.ROWID IN (SELECT ROWID FROM SpatialIndex "\
+                        "WHERE f_table_name = '%s' AND f_geometry_column = '%s' "\
+                        "AND search_frame = ST_Expand(%s, %s))" %(
+              compiler.process(geom1, **kw),
+              compiler.process(geom2, **kw),
+              compiler.process(dist, **kw),
+              geom1.table.name, geom1.table.name, geom1.name,
+              compiler.process(geom2, **kw),
+              compiler.process(dist, **kw))
+
 
 
 class Geometry(types.UserDefinedType): # type: ignore[type-arg]
@@ -147,7 +207,10 @@ class Geometry(types.UserDefinedType): # type: ignore[type-arg]
     class comparator_factory(types.UserDefinedType.Comparator): # type: ignore[type-arg]
 
         def intersects(self, other: SaColumn) -> 'sa.Operators':
-            return Geometry_IntersectsBbox(self, other)
+            if isinstance(self.expr, sa.Column):
+                return Geometry_ColumnIntersectsBbox(self.expr, other)
+
+            return Geometry_IntersectsBbox(self.expr, other)
 
 
         def is_line_like(self) -> SaColumn:
@@ -159,16 +222,19 @@ class Geometry(types.UserDefinedType): # type: ignore[type-arg]
 
 
         def ST_DWithin(self, other: SaColumn, distance: SaColumn) -> SaColumn:
-            return sa.func.ST_DWithin(self, other, distance, type_=sa.Boolean)
+            if isinstance(self.expr, sa.Column):
+                return Geometry_ColumnDWithin(self.expr, other, distance)
+
+            return sa.func.ST_DWithin(self.expr, other, distance)
 
 
         def ST_DWithin_no_index(self, other: SaColumn, distance: SaColumn) -> SaColumn:
             return sa.func.ST_DWithin(sa.func.coalesce(sa.null(), self),
-                                      other, distance, type_=sa.Boolean)
+                                      other, distance)
 
 
         def ST_Intersects_no_index(self, other: SaColumn) -> 'sa.Operators':
-            return sa.func.coalesce(sa.null(), self).op('&&')(other)
+            return Geometry_IntersectsBbox(sa.func.coalesce(sa.null(), self), other)
 
 
         def ST_Distance(self, other: SaColumn) -> SaColumn:
@@ -249,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