20.20 Spatial backends: PostGIS, SpatiaLite, MySQL and Oracle

A subclass of the plain backend

Each backend’s base.py defines a wrapper that subclasses the plain backend’s and replaces the classes it makes its helpers from, BaseDatabaseWrapper.ops_class, BaseDatabaseWrapper.features_class, BaseDatabaseWrapper.introspection_class and BaseDatabaseWrapper.SchemaEditorClass (The connection object: BaseDatabaseWrapper and what a backend provides).

The operations come in two halves: PostGISOperations has BaseSpatialOperations first among its bases and PostgreSQL’s operations second, and every backend’s operations and features, over BaseSpatialFeatures, are built so. The spatial half calls super() into the plain half: BaseSpatialOperations.check_expression_support refuses an aggregate of BaseSpatialOperations.disallowed_aggregates, then asks the plain backend, and BaseSpatialOperations.get_db_converters adds a geometry’s converter to the plain backend’s (The SQL a backend writes: BaseDatabaseOperations).

BaseSpatialOperations: what a backend answers

BaseSpatialOperations declares what every spatial backend answers, raising NotImplementedError where only the backend can, and flags such as BaseSpatialOperations.postgis say which backend it is.

The column. BaseSpatialOperations.geo_db_type gives a field’s column type. PostGIS writes "geometry(POINT,4326)", with "Z" after the type for three dimensions and "geography" for a geography field; MySQL writes the type alone, "POINT"; Oracle writes "MDSYS.SDO_GEOMETRY" for every type; SpatiaLite writes nothing.

The parameter. BaseSpatialField.get_db_prep_value wraps a geometry in the backend’s adapter class, PostGISOperations.Adapter and its like (Spatial fields: the geometry fields and RasterField). BaseSpatialOperations.get_geom_placeholder_sql, which PostGIS replaces, writes its placeholder: the constructor from text, BaseSpatialOperations.from_text, with the field’s SRID, or with the value’s inside the transform function when the two differ. Where the features say there is no table of coordinate systems the SRID is left out, which a comment gives as compatibility with MySQL, ticket 27464.

The way back. GeometryField.select_format wraps a column selected outside a subquery in the template BaseSpatialOperations.select: "ST_AsBinary(%s)" on MySQL, "SDO_UTIL.TO_WKBGEOMETRY(%s)" on Oracle, the bare column on PostGIS under psycopg 3. The converter from BaseSpatialOperations.get_geometry_converter reads the value with GEOS’s WKB reader into the field’s GeometryField.geom_class; MySQL’s and Oracle’s, which select plain WKB, also set the field’s SRID.

The names. BaseSpatialOperations.spatial_function_name gives a function’s name in SQL: the backend’s entry in BaseSpatialOperations.function_names, else BaseSpatialOperations.geom_func_prefix before Django’s name, so that on PostGIS "AsWKT" became "ST_AsText" and "Centroid" "ST_Centroid"; BaseSpatialOperations.spatial_aggregate_name names aggregates. A function in BaseSpatialOperations.unsupported_functions is refused with NotSupportedError; the base class lists every function there, so a backend supports none it does not claim, and PostGIS’s set is empty. The lookups’ operators and BaseSpatialOperations.get_distance, which turns a distance into the SQL’s number, belong to Spatial lookups, functions and distances.

BaseSpatialFeatures: what each database can do

BaseSpatialFeatures sets the GIS flags, among them BaseSpatialFeatures.gis_enabled, false on BaseDatabaseFeatures, which a raster field checks (What a database can do: BaseDatabaseFeatures). A flag for each function is computed on demand from the unsupported functions, "has_Centroid_function" true on PostGIS, and one for a name that is not a function raises ValueError.

PostGIS

PostGISOperations.spatial_version is the setting POSTGIS_VERSION where it is set, (3, 5, 2) in the example. Otherwise it asks the server for "version()", so that a failure of PostgreSQL itself is not blamed on PostGIS (ticket 24862), then for "postgis_lib_version()". DatabaseWrapper.prepare_database, which migrate calls, runs "CREATE EXTENSION IF NOT EXISTS postgis" where "pg_extension" lacks it (The migrate command, showmigrations and sqlmigrate).

A PostGISAdapter holds a geometry’s EWKB, its WKB with the SRID inside. Under psycopg 3, DatabaseWrapper.register_geometry_adapters fetches on a new connection the type numbers of "geometry", "geography" and "raster", once for each alias, and registers the dumpers postgis_adapters makes for them, which send the value typed as one of the three. The placeholder is a bare "%s", inside "ST_Transform" when the SRIDs differ. A point of the Grand Harbour in SRID 4326 and in UTM zone 33N, compiled with no server:

Mooring.objects.filter(position__intersects=Point(14.515, 35.8925, srid=4326))
... WHERE ST_Intersects("charts_mooring"."position", %s)
params: (PostGISAdapter,)

Mooring.objects.filter(position__intersects=Point(456229, 3972134, srid=32633))
... WHERE ST_Intersects("charts_mooring"."position", ST_Transform(%s, %s))
params: (PostGISAdapter, 4326)

The schema editor gives a field with BaseSpatialField.spatial_index a GiST index, with the operator class "GIST_GEOMETRY_OPS_ND" for a geometry, not a geography, of three dimensions. For a depth sounding and a shipping lane it wrote, with no server and the key column left out:

# charts/models.py
class Sounding(models.Model):
    position = models.PointField(dim=3)


class Lane(models.Model):
    route = models.LineStringField(geography=True)

CREATE TABLE "charts_sounding" (..., "position" geometry(POINTZ,4326) NOT NULL);
CREATE INDEX "charts_sounding_position_0d47193e_id" ON "charts_sounding" USING GIST ("position" GIST_GEOMETRY_OPS_ND);
CREATE TABLE "charts_lane" (..., "route" geography(LINESTRING,4326) NOT NULL);
CREATE INDEX "charts_lane_route_ec6f76d6_id" ON "charts_lane" USING GIST ("route");

A geography field in another SRID than 4326 was refused with NotSupportedError, “PostGIS only supports geography columns with an SRID of 4326.”. What the backend asks the server, the version without the setting, the extension, a row of "spatial_ref_sys", the run could not ask.

SpatiaLite

DatabaseWrapper.get_new_connection enables extension loading and loads the setting SPATIALITE_LIBRARY_PATH where it is set, or else the first that loads of "mod_spatialite.so", "mod_spatialite" and what ctypes.util.find_library finds. Its prepare_database runs "InitSpatialMetaData(1)", or "InitSpatialMetaDataFull(1)" from SpatiaLite 5, where "geometry_columns" is missing. SpatialiteSchemaEditor.column_sql returns no column for a geometry field but collects calls of "AddGeometryColumn" and "CreateSpatialIndex", run once the table exists (SQLite).

MySQL and MariaDB

MySQL sends a geometry as WKT in a WKTAdapter through "ST_GeomFromText". The two databases differ in operators and functions, among them "relate" on MariaDB only and "covers" on MySQL only, and MariaDB allows the operator "coveredby" and the aggregate Collect only from 12.0.1. A spatial index is made only for a field that is not null, and only where the table’s storage engine is MyISAM, Aria or InnoDB (MySQL and MariaDB).

Oracle

SDOOperator writes most of Oracle’s lookups as a function compared with 'TRUE'. The schema editor adds a row to "USER_SDO_GEOM_METADATA" for each geometry column, with the field’s extent and tolerance, and makes an index of type "MDSYS.SPATIAL_INDEX". OracleSpatialAdapter sends WKT, after turning a polygon’s rings to the orientation its docstring says Oracle requires.

The metadata models and introspection

BaseSpatialOperations.geometry_columns and BaseSpatialOperations.spatial_ref_sys return two unmanaged models of the app label "gis", over "geometry_columns" and "spatial_ref_sys" on PostGIS and SpatiaLite, "USER_SDO_GEOM_METADATA" and "CS_SRS" on Oracle; MySQL has none. The second has SpatialRefSysMixin, which makes a GDAL SpatialReference from the row’s WKT or, failing that, from the PROJ string of a PostGIS or SpatiaLite row, and answers units and spheroid from it; a field reads its units there, on MySQL from GDAL. Introspection tells the GIS inspectdb a geometry column’s type, on Oracle only the generic geometry field, and, where the metadata keeps them, its SRID and dimensions (GeoDjango’s forms, admin, feeds and data tools).