Fixed #28518 -- Improved performance of loading geometries from DB.

8 files changed, 94 insertions, 60 deletions

diff --git a/django/contrib/gis/db/backends/base/operations.py b/django/contrib/gis/db/backends/base/operations.py
index 5d2578d01a..170775f063 100644
--- a/django/contrib/gis/db/backends/base/operations.py
+++ b/django/contrib/gis/db/backends/base/operations.py
@@ -1,4 +1,6 @@
+from django.contrib.gis.db.models import GeometryField
 from django.contrib.gis.db.models.functions import Distance
+from django.utils.functional import cached_property
 
 
 class BaseSpatialOperations:
@@ -13,6 +15,10 @@ class BaseSpatialOperations:
     # How the geometry column should be selected.
     select = None
 
+    @cached_property
+    def select_extent(self):
+        return self.select
+
     # Does the spatial database have a geometry or geography type?
     geography = False
     geometry = False
@@ -117,3 +123,15 @@ class BaseSpatialOperations:
         raise NotImplementedError('subclasses of BaseSpatialOperations must a provide spatial_ref_sys() method')
 
     distance_expr_for_lookup = staticmethod(Distance)
+
+    def get_db_converters(self, expression):
+        converters = super().get_db_converters(expression)
+        if isinstance(expression.output_field, GeometryField):
+            converters.append(self.get_geometry_converter(expression))
+        return converters
+
+    def get_geometry_converter(self, expression):
+        raise NotImplementedError(
+            'Subclasses of BaseSpatialOperations must provide a '
+            'get_geometry_converter() method.'
+        )
diff --git a/django/contrib/gis/db/backends/mysql/features.py b/django/contrib/gis/db/backends/mysql/features.py
index 7eb375d571..16e8100b44 100644
--- a/django/contrib/gis/db/backends/mysql/features.py
+++ b/django/contrib/gis/db/backends/mysql/features.py
@@ -2,6 +2,7 @@ from django.contrib.gis.db.backends.base.features import BaseSpatialFeatures
 from django.db.backends.mysql.features import (
     DatabaseFeatures as MySQLDatabaseFeatures,
 )
+from django.utils.functional import cached_property
 
 
 class DatabaseFeatures(BaseSpatialFeatures, MySQLDatabaseFeatures):
@@ -14,3 +15,7 @@ class DatabaseFeatures(BaseSpatialFeatures, MySQLDatabaseFeatures):
     supports_real_shape_operations = False
     supports_null_geometries = False
     supports_num_points_poly = False
+
+    @cached_property
+    def supports_empty_geometry_collection(self):
+        return self.connection.mysql_version >= (5, 7, 5)
diff --git a/django/contrib/gis/db/backends/mysql/operations.py b/django/contrib/gis/db/backends/mysql/operations.py
index 6b8c20c561..fd3db2c7a4 100644
--- a/django/contrib/gis/db/backends/mysql/operations.py
+++ b/django/contrib/gis/db/backends/mysql/operations.py
@@ -3,7 +3,9 @@ from django.contrib.gis.db.backends.base.operations import (
     BaseSpatialOperations,
 )
 from django.contrib.gis.db.backends.utils import SpatialOperator
-from django.contrib.gis.db.models import GeometryField, aggregates
+from django.contrib.gis.db.models import aggregates
+from django.contrib.gis.geos import GEOSGeometry
+from django.contrib.gis.geos.prototypes.io import wkb_r
 from django.contrib.gis.measure import Distance
 from django.db.backends.mysql.operations import DatabaseOperations
 from django.utils.functional import cached_property
@@ -29,12 +31,8 @@ class MySQLOperations(BaseSpatialOperations, DatabaseOperations):
         return self.connection.mysql_version < (5, 7, 6)
 
     @cached_property
-    def uses_invalid_empty_geometry_collection(self):
-        return self.connection.mysql_version >= (5, 7, 5)
-
-    @cached_property
     def select(self):
-        return self.geom_func_prefix + 'AsText(%s)'
+        return self.geom_func_prefix + 'AsBinary(%s)'
 
     @cached_property
     def from_text(self):
@@ -97,15 +95,12 @@ class MySQLOperations(BaseSpatialOperations, DatabaseOperations):
             dist_param = value
         return [dist_param]
 
-    def get_db_converters(self, expression):
-        converters = super().get_db_converters(expression)
-        if isinstance(expression.output_field, GeometryField) and self.uses_invalid_empty_geometry_collection:
-            converters.append(self.convert_invalid_empty_geometry_collection)
-        return converters
+    def get_geometry_converter(self, expression):
+        read = wkb_r().read
+        srid = expression.output_field.srid
+        if srid == -1:
+            srid = None
 
-    # https://dev.mysql.com/doc/refman/en/spatial-function-argument-handling.html
-    # MySQL 5.7.5 adds support for the empty geometry collections, but they are represented with invalid WKT.
-    def convert_invalid_empty_geometry_collection(self, value, expression, connection):
-        if value == b'GEOMETRYCOLLECTION()':
-            return b'GEOMETRYCOLLECTION EMPTY'
-        return value
+        def converter(value, expression, connection):
+            return None if value is None else GEOSGeometry(read(memoryview(value)), srid)
+        return converter
diff --git a/django/contrib/gis/db/backends/oracle/operations.py b/django/contrib/gis/db/backends/oracle/operations.py
index 57a78ae39a..b23cfc3ab1 100644
--- a/django/contrib/gis/db/backends/oracle/operations.py
+++ b/django/contrib/gis/db/backends/oracle/operations.py
@@ -16,6 +16,8 @@ from django.contrib.gis.db.backends.oracle.adapter import OracleSpatialAdapter
 from django.contrib.gis.db.backends.utils import SpatialOperator
 from django.contrib.gis.db.models import aggregates
 from django.contrib.gis.geometry.backend import Geometry
+from django.contrib.gis.geos import GEOSGeometry
+from django.contrib.gis.geos.prototypes.io import wkb_r
 from django.contrib.gis.measure import Distance
 from django.db.backends.oracle.operations import DatabaseOperations
 
@@ -85,7 +87,7 @@ class OracleOperations(BaseSpatialOperations, DatabaseOperations):
     # However, this adversely affects performance (i.e., Java is called
     # to convert to WKT on every query).  If someone wishes to write a
     # SDO_GEOMETRY(...) parser in Python, let me know =)
-    select = 'SDO_UTIL.TO_WKTGEOMETRY(%s)'
+    select = 'SDO_UTIL.TO_WKBGEOMETRY(%s)'
 
     gis_operators = {
         'contains': SDOOperator(func='SDO_CONTAINS'),
@@ -112,24 +114,12 @@ class OracleOperations(BaseSpatialOperations, DatabaseOperations):
     def geo_quote_name(self, name):
         return super().geo_quote_name(name).upper()
 
-    def get_db_converters(self, expression):
-        converters = super().get_db_converters(expression)
-        internal_type = expression.output_field.get_internal_type()
-        geometry_fields = (
-            'PointField', 'GeometryField', 'LineStringField',
-            'PolygonField', 'MultiPointField', 'MultiLineStringField',
-            'MultiPolygonField', 'GeometryCollectionField',
-        )
-        if internal_type in geometry_fields:
-            converters.append(self.convert_textfield_value)
-        return converters
-
     def convert_extent(self, clob):
         if clob:
             # Generally, Oracle returns a polygon for the extent -- however,
             # it can return a single point if there's only one Point in the
             # table.
-            ext_geom = Geometry(clob.read())
+            ext_geom = Geometry(memoryview(clob.read()))
             gtype = str(ext_geom.geom_type)
             if gtype == 'Polygon':
                 # Construct the 4-tuple from the coordinates in the polygon.
@@ -207,3 +197,13 @@ class OracleOperations(BaseSpatialOperations, DatabaseOperations):
         if placeholder == 'NULL':
             return []
         return super().modify_insert_params(placeholder, params)
+
+    def get_geometry_converter(self, expression):
+        read = wkb_r().read
+        srid = expression.output_field.srid
+        if srid == -1:
+            srid = None
+
+        def converter(value, expression, connection):
+            return None if value is None else GEOSGeometry(read(memoryview(value.read())), srid)
+        return converter
diff --git a/django/contrib/gis/db/backends/postgis/operations.py b/django/contrib/gis/db/backends/postgis/operations.py
index 335ffc8c8d..9199d16e2b 100644
--- a/django/contrib/gis/db/backends/postgis/operations.py
+++ b/django/contrib/gis/db/backends/postgis/operations.py
@@ -7,6 +7,8 @@ from django.contrib.gis.db.backends.base.operations import (
 from django.contrib.gis.db.backends.utils import SpatialOperator
 from django.contrib.gis.db.models import GeometryField, RasterField
 from django.contrib.gis.gdal import GDALRaster
+from django.contrib.gis.geos import GEOSGeometry
+from django.contrib.gis.geos.prototypes.io import wkb_r
 from django.contrib.gis.measure import Distance
 from django.core.exceptions import ImproperlyConfigured
 from django.db.backends.postgresql.operations import DatabaseOperations
@@ -132,6 +134,12 @@ class PostGISOperations(BaseSpatialOperations, DatabaseOperations):
 
     unsupported_functions = set()
 
+    @cached_property
+    def select(self):
+        return '%s::bytea'
+
+    select_extent = None
+
     def __init__(self, connection):
         super().__init__(connection)
 
@@ -381,3 +389,7 @@ class PostGISOperations(BaseSpatialOperations, DatabaseOperations):
             isinstance(arg, GDALRaster)
         )
         return ST_Polygon(arg) if is_raster else arg
+
+    def get_geometry_converter(self, expression):
+        read = wkb_r().read
+        return lambda value, expression, connection: None if value is None else GEOSGeometry(read(value))
diff --git a/django/contrib/gis/db/backends/spatialite/operations.py b/django/contrib/gis/db/backends/spatialite/operations.py
index 0b5911c83d..536834ec8d 100644
--- a/django/contrib/gis/db/backends/spatialite/operations.py
+++ b/django/contrib/gis/db/backends/spatialite/operations.py
@@ -10,6 +10,8 @@ from django.contrib.gis.db.backends.spatialite.adapter import SpatiaLiteAdapter
 from django.contrib.gis.db.backends.utils import SpatialOperator
 from django.contrib.gis.db.models import aggregates
 from django.contrib.gis.geometry.backend import Geometry
+from django.contrib.gis.geos import GEOSGeometry
+from django.contrib.gis.geos.prototypes.io import wkb_r, wkt_r
 from django.contrib.gis.measure import Distance
 from django.core.exceptions import ImproperlyConfigured
 from django.db.backends.sqlite3.operations import DatabaseOperations
@@ -35,7 +37,6 @@ class SpatiaLiteOperations(BaseSpatialOperations, DatabaseOperations):
     unionagg = 'GUnion'
 
     from_text = 'GeomFromText'
-    select = 'AsText(%s)'
 
     gis_operators = {
         # Binary predicates
@@ -64,6 +65,10 @@ class SpatiaLiteOperations(BaseSpatialOperations, DatabaseOperations):
     disallowed_aggregates = (aggregates.Extent3D,)
 
     @cached_property
+    def select(self):
+        return 'CAST (AsEWKB(%s) AS BLOB)' if self.spatial_version >= (4, 3, 0) else 'AsText(%s)'
+
+    @cached_property
     def function_names(self):
         return {
             'Length': 'ST_Length',
@@ -192,3 +197,14 @@ class SpatiaLiteOperations(BaseSpatialOperations, DatabaseOperations):
     def spatial_ref_sys(self):
         from django.contrib.gis.db.backends.spatialite.models import SpatialiteSpatialRefSys
         return SpatialiteSpatialRefSys
+
+    def get_geometry_converter(self, expression):
+        if self.spatial_version >= (4, 3, 0):
+            read = wkb_r().read
+            return lambda value, expression, connection: None if value is None else GEOSGeometry(read(value))
+        else:
+            read = wkt_r().read
+            srid = expression.output_field.srid
+            if srid == -1:
+                srid = None
+            return lambda value, expression, connection: None if value is None else GEOSGeometry(read(value), srid)
diff --git a/django/contrib/gis/db/models/fields.py b/django/contrib/gis/db/models/fields.py
index bd1f427845..b571f9977a 100644
--- a/django/contrib/gis/db/models/fields.py
+++ b/django/contrib/gis/db/models/fields.py
@@ -51,23 +51,6 @@ def get_srid_info(srid, connection):
     return _srid_cache[alias][srid]
 
 
-class GeoSelectFormatMixin:
-    def select_format(self, compiler, sql, params):
-        """
-        Return the selection format string, depending on the requirements
-        of the spatial backend.  For example, Oracle and MySQL require custom
-        selection formats in order to retrieve geometries in OGC WKT. For all
-        other fields, return a simple '%s' format string.
-        """
-        connection = compiler.connection
-        if connection.ops.select:
-            # This allows operations to be done on fields in the SELECT,
-            # overriding their values -- used by the Oracle and MySQL
-            # spatial backends to get database values as WKT.
-            sql = connection.ops.select % sql
-        return sql, params
-
-
 class BaseSpatialField(Field):
     """
     The Base GIS Field.
@@ -205,7 +188,7 @@ class BaseSpatialField(Field):
         return obj
 
 
-class GeometryField(GeoSelectFormatMixin, BaseSpatialField):
+class GeometryField(BaseSpatialField):
     """
     The base Geometry field -- maps to the OpenGIS Specification Geometry type.
     """
@@ -255,14 +238,6 @@ class GeometryField(GeoSelectFormatMixin, BaseSpatialField):
             kwargs['geography'] = self.geography
         return name, path, args, kwargs
 
-    def from_db_value(self, value, expression, connection):
-        if value:
-            value = Geometry(value)
-            srid = value.srid
-            if not srid and self.srid != -1:
-                value.srid = self.srid
-        return value
-
     def contribute_to_class(self, cls, name, **kwargs):
         super().contribute_to_class(cls, name, **kwargs)
 
@@ -280,6 +255,15 @@ class GeometryField(GeoSelectFormatMixin, BaseSpatialField):
             defaults['widget'] = forms.Textarea
         return super().formfield(**defaults)
 
+    def select_format(self, compiler, sql, params):
+        """
+        Return the selection format string, depending on the requirements
+        of the spatial backend. For example, Oracle and MySQL require custom
+        selection formats in order to retrieve geometries in OGC WKB.
+        """
+        select = compiler.connection.ops.select
+        return select % sql if select else sql, params
+
 
 # The OpenGIS Geometry Type Fields
 class PointField(GeometryField):
@@ -324,7 +308,7 @@ class GeometryCollectionField(GeometryField):
     description = _("Geometry collection")
 
 
-class ExtentField(GeoSelectFormatMixin, Field):
+class ExtentField(Field):
     "Used as a return value from an extent aggregate"
 
     description = _("Extent Aggregate Field")
@@ -332,6 +316,10 @@ class ExtentField(GeoSelectFormatMixin, Field):
     def get_internal_type(self):
         return "ExtentField"
 
+    def select_format(self, compiler, sql, params):
+        select = compiler.connection.ops.select_extent
+        return select % sql if select else sql, params
+
 
 class RasterField(BaseSpatialField):
     """
diff --git a/tests/gis_tests/geoapp/test_functions.py b/tests/gis_tests/geoapp/test_functions.py
index bbf29b0aff..0e644cefd6 100644
--- a/tests/gis_tests/geoapp/test_functions.py
+++ b/tests/gis_tests/geoapp/test_functions.py
@@ -248,7 +248,7 @@ class GISFunctionsTests(TestCase):
         geom = Point(5, 23, srid=4326)
         qs = Country.objects.annotate(inter=functions.Intersection('mpoly', geom))
         for c in qs:
-            if spatialite or (mysql and not connection.ops.uses_invalid_empty_geometry_collection) or oracle:
+            if spatialite or (mysql and not connection.features.supports_empty_geometry_collection) or oracle:
                 # When the intersection is empty, some databases return None.
                 expected = None
             else: