make details API work with sqlite incl. unit tests

[nominatim.git] / nominatim / db / sqlalchemy_types.py

diff --git a/nominatim/db/sqlalchemy_types.py b/nominatim/db/sqlalchemy_types.py
index 9e1f9fcecf8c1dc819e616bbbe708f40ccfd7949..8e8cc9c8aa47c90d2fc6c4e6291c62c083df21ec 100644 (file)
--- a/nominatim/db/sqlalchemy_types.py
+++ b/nominatim/db/sqlalchemy_types.py
@@ -11,12 +11,35 @@ from typing import Callable, Any, cast
 import sys
 
 import sqlalchemy as sa
+from sqlalchemy.ext.compiler import compiles
 from sqlalchemy import types
 
 from nominatim.typing import SaColumn, SaBind
 
 #pylint: disable=all
 
+class Geometry_DistanceSpheroid(sa.sql.expression.FunctionElement[float]):
+    """ Function to compute the spherical distance in meters.
+    """
+    type = sa.Float()
+    name = 'Geometry_DistanceSpheroid'
+    inherit_cache = True
+
+
+@compiles(Geometry_DistanceSpheroid) # type: ignore[no-untyped-call, misc]
+def _default_distance_spheroid(element: SaColumn,
+                               compiler: 'sa.Compiled', **kw: Any) -> str:
+    return "ST_DistanceSpheroid(%s,"\
+           " 'SPHEROID[\"WGS 84\",6378137,298.257223563, AUTHORITY[\"EPSG\",\"7030\"]]')"\
+             % compiler.process(element.clauses, **kw)
+
+
+@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)
+
+
 class Geometry(types.UserDefinedType): # type: ignore[type-arg]
     """ Simplified type decorator for PostGIS geometry. This type
         only supports geometries in 4326 projection.
@@ -47,6 +70,10 @@ class Geometry(types.UserDefinedType): # type: ignore[type-arg]
         return process
 
 
+    def column_expression(self, col: SaColumn) -> SaColumn:
+        return sa.func.ST_AsEWKB(col)
+
+
     def bind_expression(self, bindvalue: SaBind) -> SaColumn:
         return sa.func.ST_GeomFromText(bindvalue, sa.text('4326'), type_=self)
 
@@ -70,11 +97,12 @@ class Geometry(types.UserDefinedType): # type: ignore[type-arg]
 
 
         def ST_DWithin_no_index(self, other: SaColumn, distance: SaColumn) -> SaColumn:
-            return sa.func._ST_DWithin(self, other, distance, type_=sa.Boolean)
+            return sa.func.ST_DWithin(sa.func.coalesce(sa.null(), self),
+                                      other, distance, type_=sa.Boolean)
 
 
-        def ST_Intersects_no_index(self, other: SaColumn) -> SaColumn:
-            return sa.func._ST_Intersects(self, other, type_=sa.Boolean)
+        def ST_Intersects_no_index(self, other: SaColumn) -> 'sa.Operators':
+            return sa.func.coalesce(sa.null(), self).op('&&')(other)
 
 
         def ST_Distance(self, other: SaColumn) -> SaColumn:
@@ -115,3 +143,41 @@ class Geometry(types.UserDefinedType): # type: ignore[type-arg]
 
         def ST_LineLocatePoint(self, other: SaColumn) -> SaColumn:
             return sa.func.ST_LineLocatePoint(self, other, type_=sa.Float)
+
+
+        def distance_spheroid(self, other: SaColumn) -> SaColumn:
+            return Geometry_DistanceSpheroid(self, other)
+
+
+@compiles(Geometry, 'sqlite') # type: ignore[no-untyped-call]
+def get_col_spec(self, *args, **kwargs): # type: ignore[no-untyped-def]
+    return 'GEOMETRY'
+
+
+SQLITE_FUNCTION_ALIAS = (
+    ('ST_AsEWKB', sa.Text, 'AsEWKB'),
+    ('ST_GeomFromEWKT', Geometry, 'GeomFromEWKT'),
+    ('ST_AsGeoJSON', sa.Text, 'AsGeoJSON'),
+    ('ST_AsKML', sa.Text, 'AsKML'),
+    ('ST_AsSVG', sa.Text, 'AsSVG'),
+)
+
+def _add_function_alias(func: str, ftype: type, alias: str) -> None:
+    _FuncDef = type(func, (sa.sql.functions.GenericFunction, ), {
+        "type": ftype,
+        "name": func,
+        "identifier": func,
+        "inherit_cache": True})
+
+    func_templ = f"{alias}(%s)"
+
+    def _sqlite_impl(element: Any, compiler: Any, **kw: Any) -> Any:
+        return func_templ % compiler.process(element.clauses, **kw)
+
+    compiles(_FuncDef, 'sqlite')(_sqlite_impl) # type: ignore[no-untyped-call]
+
+for alias in SQLITE_FUNCTION_ALIAS:
+    _add_function_alias(*alias)
+
+
+