forked from django/django
/
db-api.txt
407 lines (316 loc) · 19.9 KB
/
db-api.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
======================
GeoDjango Database API
======================
.. _spatial-backends:
Spatial Backends
================
.. module:: django.contrib.gis.db.backends
:synopsis: GeoDjango's spatial database backends.
GeoDjango currently provides the following spatial database backends:
* ``django.contrib.gis.db.backends.postgis``
* ``django.contrib.gis.db.backends.mysql``
* ``django.contrib.gis.db.backends.oracle``
* ``django.contrib.gis.db.backends.spatialite``
.. _mysql-spatial-limitations:
MySQL Spatial Limitations
-------------------------
Before MySQL 5.6.1, spatial extensions only support bounding box operations
(what MySQL calls minimum bounding rectangles, or MBR). Specifically, MySQL did
not conform to the OGC standard. Django supports spatial functions operating on
real geometries available in modern MySQL versions. However, the spatial
functions are not as rich as other backends like PostGIS.
Raster Support
--------------
``RasterField`` is currently only implemented for the PostGIS backend. Spatial
lookups are available for raster fields, but spatial database functions and
aggregates aren't implemented for raster fields.
Creating and Saving Models with Geometry Fields
===============================================
Here is an example of how to create a geometry object (assuming the ``Zipcode``
model)::
>>> from zipcode.models import Zipcode
>>> z = Zipcode(code=77096, poly='POLYGON(( 10 10, 10 20, 20 20, 20 15, 10 10))')
>>> z.save()
:class:`~django.contrib.gis.geos.GEOSGeometry` objects may also be used to save geometric models::
>>> from django.contrib.gis.geos import GEOSGeometry
>>> poly = GEOSGeometry('POLYGON(( 10 10, 10 20, 20 20, 20 15, 10 10))')
>>> z = Zipcode(code=77096, poly=poly)
>>> z.save()
Moreover, if the ``GEOSGeometry`` is in a different coordinate system (has a
different SRID value) than that of the field, then it will be implicitly
transformed into the SRID of the model's field, using the spatial database's
transform procedure::
>>> poly_3084 = GEOSGeometry('POLYGON(( 10 10, 10 20, 20 20, 20 15, 10 10))', srid=3084) # SRID 3084 is 'NAD83(HARN) / Texas Centric Lambert Conformal'
>>> z = Zipcode(code=78212, poly=poly_3084)
>>> z.save()
>>> from django.db import connection
>>> print(connection.queries[-1]['sql']) # printing the last SQL statement executed (requires DEBUG=True)
INSERT INTO "geoapp_zipcode" ("code", "poly") VALUES (78212, ST_Transform(ST_GeomFromWKB('\\001 ... ', 3084), 4326))
Thus, geometry parameters may be passed in using the ``GEOSGeometry`` object, WKT
(Well Known Text [#fnwkt]_), HEXEWKB (PostGIS specific -- a WKB geometry in
hexadecimal [#fnewkb]_), and GeoJSON (see :rfc:`7946`). Essentially, if the
input is not a ``GEOSGeometry`` object, the geometry field will attempt to
create a ``GEOSGeometry`` instance from the input.
For more information creating :class:`~django.contrib.gis.geos.GEOSGeometry`
objects, refer to the :ref:`GEOS tutorial <geos-tutorial>`.
.. _creating-and-saving-raster-models:
Creating and Saving Models with Raster Fields
=============================================
When creating raster models, the raster field will implicitly convert the input
into a :class:`~django.contrib.gis.gdal.GDALRaster` using lazy-evaluation.
The raster field will therefore accept any input that is accepted by the
:class:`~django.contrib.gis.gdal.GDALRaster` constructor.
Here is an example of how to create a raster object from a raster file
``volcano.tif`` (assuming the ``Elevation`` model)::
>>> from elevation.models import Elevation
>>> dem = Elevation(name='Volcano', rast='/path/to/raster/volcano.tif')
>>> dem.save()
:class:`~django.contrib.gis.gdal.GDALRaster` objects may also be used to save
raster models::
>>> from django.contrib.gis.gdal import GDALRaster
>>> rast = GDALRaster({'width': 10, 'height': 10, 'name': 'Canyon', 'srid': 4326,
... 'scale': [0.1, -0.1], 'bands': [{"data": range(100)}]})
>>> dem = Elevation(name='Canyon', rast=rast)
>>> dem.save()
Note that this equivalent to::
>>> dem = Elevation.objects.create(
... name='Canyon',
... rast={'width': 10, 'height': 10, 'name': 'Canyon', 'srid': 4326,
... 'scale': [0.1, -0.1], 'bands': [{"data": range(100)}]},
... )
.. _spatial-lookups-intro:
Spatial Lookups
===============
GeoDjango's lookup types may be used with any manager method like
``filter()``, ``exclude()``, etc. However, the lookup types unique to
GeoDjango are only available on spatial fields.
Filters on 'normal' fields (e.g. :class:`~django.db.models.CharField`)
may be chained with those on geographic fields. Geographic lookups accept
geometry and raster input on both sides and input types can be mixed freely.
The general structure of geographic lookups is described below. A complete
reference can be found in the :ref:`spatial lookup reference<spatial-lookups>`.
Geometry Lookups
----------------
Geographic queries with geometries take the following general form (assuming
the ``Zipcode`` model used in the :doc:`model-api`)::
>>> qs = Zipcode.objects.filter(<field>__<lookup_type>=<parameter>)
>>> qs = Zipcode.objects.exclude(...)
For example::
>>> qs = Zipcode.objects.filter(poly__contains=pnt)
>>> qs = Elevation.objects.filter(poly__contains=rst)
In this case, ``poly`` is the geographic field, :lookup:`contains <gis-contains>`
is the spatial lookup type, ``pnt`` is the parameter (which may be a
:class:`~django.contrib.gis.geos.GEOSGeometry` object or a string of
GeoJSON , WKT, or HEXEWKB), and ``rst`` is a
:class:`~django.contrib.gis.gdal.GDALRaster` object.
.. _spatial-lookup-raster:
Raster Lookups
--------------
The raster lookup syntax is similar to the syntax for geometries. The only
difference is that a band index can be specified as additional input. If no band
index is specified, the first band is used by default (index ``0``). In that
case the syntax is identical to the syntax for geometry lookups.
To specify the band index, an additional parameter can be specified on both
sides of the lookup. On the left hand side, the double underscore syntax is
used to pass a band index. On the right hand side, a tuple of the raster and
band index can be specified.
This results in the following general form for lookups involving rasters
(assuming the ``Elevation`` model used in the :doc:`model-api`)::
>>> qs = Elevation.objects.filter(<field>__<lookup_type>=<parameter>)
>>> qs = Elevation.objects.filter(<field>__<band_index>__<lookup_type>=<parameter>)
>>> qs = Elevation.objects.filter(<field>__<lookup_type>=(<raster_input, <band_index>)
For example::
>>> qs = Elevation.objects.filter(rast__contains=geom)
>>> qs = Elevation.objects.filter(rast__contains=rst)
>>> qs = Elevation.objects.filter(rast__1__contains=geom)
>>> qs = Elevation.objects.filter(rast__contains=(rst, 1))
>>> qs = Elevation.objects.filter(rast__1__contains=(rst, 1))
On the left hand side of the example, ``rast`` is the geographic raster field
and :lookup:`contains <gis-contains>` is the spatial lookup type. On the right
hand side, ``geom`` is a geometry input and ``rst`` is a
:class:`~django.contrib.gis.gdal.GDALRaster` object. The band index defaults to
``0`` in the first two queries and is set to ``1`` on the others.
While all spatial lookups can be used with raster objects on both sides, not all
underlying operators natively accept raster input. For cases where the operator
expects geometry input, the raster is automatically converted to a geometry.
It's important to keep this in mind when interpreting the lookup results.
The type of raster support is listed for all lookups in the :ref:`compatibility
table <spatial-lookup-compatibility>`. Lookups involving rasters are currently
only available for the PostGIS backend.
.. _distance-queries:
Distance Queries
================
Introduction
------------
Distance calculations with spatial data is tricky because, unfortunately,
the Earth is not flat. Some distance queries with fields in a geographic
coordinate system may have to be expressed differently because of
limitations in PostGIS. Please see the :ref:`selecting-an-srid` section
in the :doc:`model-api` documentation for more details.
.. _distance-lookups-intro:
Distance Lookups
----------------
*Availability*: PostGIS, MariaDB, MySQL, Oracle, SpatiaLite, PGRaster (Native)
The following distance lookups are available:
* :lookup:`distance_lt`
* :lookup:`distance_lte`
* :lookup:`distance_gt`
* :lookup:`distance_gte`
* :lookup:`dwithin` (except MariaDB and MySQL)
.. note::
For *measuring*, rather than querying on distances, use the
:class:`~django.contrib.gis.db.models.functions.Distance` function.
Distance lookups take a tuple parameter comprising:
#. A geometry or raster to base calculations from; and
#. A number or :class:`~django.contrib.gis.measure.Distance` object containing the distance.
If a :class:`~django.contrib.gis.measure.Distance` object is used,
it may be expressed in any units (the SQL generated will use units
converted to those of the field); otherwise, numeric parameters are assumed
to be in the units of the field.
.. note::
In PostGIS, ``ST_Distance_Sphere`` does *not* limit the geometry types
geographic distance queries are performed with. [#fndistsphere15]_ However,
these queries may take a long time, as great-circle distances must be
calculated on the fly for *every* row in the query. This is because the
spatial index on traditional geometry fields cannot be used.
For much better performance on WGS84 distance queries, consider using
:ref:`geography columns <geography-type>` in your database instead because
they are able to use their spatial index in distance queries.
You can tell GeoDjango to use a geography column by setting ``geography=True``
in your field definition.
For example, let's say we have a ``SouthTexasCity`` model (from the
:source:`GeoDjango distance tests <tests/gis_tests/distapp/models.py>` ) on a
*projected* coordinate system valid for cities in southern Texas::
from django.contrib.gis.db import models
class SouthTexasCity(models.Model):
name = models.CharField(max_length=30)
# A projected coordinate system (only valid for South Texas!)
# is used, units are in meters.
point = models.PointField(srid=32140)
Then distance queries may be performed as follows::
>>> from django.contrib.gis.geos import GEOSGeometry
>>> from django.contrib.gis.measure import D # ``D`` is a shortcut for ``Distance``
>>> from geoapp.models import SouthTexasCity
# Distances will be calculated from this point, which does not have to be projected.
>>> pnt = GEOSGeometry('POINT(-96.876369 29.905320)', srid=4326)
# If numeric parameter, units of field (meters in this case) are assumed.
>>> qs = SouthTexasCity.objects.filter(point__distance_lte=(pnt, 7000))
# Find all Cities within 7 km, > 20 miles away, and > 100 chains away (an obscure unit)
>>> qs = SouthTexasCity.objects.filter(point__distance_lte=(pnt, D(km=7)))
>>> qs = SouthTexasCity.objects.filter(point__distance_gte=(pnt, D(mi=20)))
>>> qs = SouthTexasCity.objects.filter(point__distance_gte=(pnt, D(chain=100)))
Raster queries work the same way by replacing the geometry field ``point`` with
a raster field, or the ``pnt`` object with a raster object, or both. To specify
the band index of a raster input on the right hand side, a 3-tuple can be
passed to the lookup as follows::
>>> qs = SouthTexasCity.objects.filter(point__distance_gte=(rst, 2, D(km=7)))
Where the band with index 2 (the third band) of the raster ``rst`` would be
used for the lookup.
.. _compatibility-table:
Compatibility Tables
====================
.. _spatial-lookup-compatibility:
Spatial Lookups
---------------
The following table provides a summary of what spatial lookups are available
for each spatial database backend. The PostGIS Raster (PGRaster) lookups are
divided into the three categories described in the :ref:`raster lookup details
<spatial-lookup-raster>`: native support ``N``, bilateral native support ``B``,
and geometry conversion support ``C``.
================================= ========= ======== ========= ============ ========== ========
Lookup Type PostGIS Oracle MariaDB MySQL [#]_ SpatiaLite PGRaster
================================= ========= ======== ========= ============ ========== ========
:lookup:`bbcontains` X X X X N
:lookup:`bboverlaps` X X X X N
:lookup:`contained` X X X X N
:lookup:`contains <gis-contains>` X X X X X B
:lookup:`contains_properly` X B
:lookup:`coveredby` X X X B
:lookup:`covers` X X X B
:lookup:`crosses` X X X X C
:lookup:`disjoint` X X X X X B
:lookup:`distance_gt` X X X X X N
:lookup:`distance_gte` X X X X X N
:lookup:`distance_lt` X X X X X N
:lookup:`distance_lte` X X X X X N
:lookup:`dwithin` X X X B
:lookup:`equals` X X X X X C
:lookup:`exact <same_as>` X X X X X B
:lookup:`intersects` X X X X X B
:lookup:`isvalid` X X X (≥ 5.7.5) X
:lookup:`overlaps` X X X X X B
:lookup:`relate` X X X X C
:lookup:`same_as` X X X X X B
:lookup:`touches` X X X X X B
:lookup:`within` X X X X X B
:lookup:`left` X C
:lookup:`right` X C
:lookup:`overlaps_left` X B
:lookup:`overlaps_right` X B
:lookup:`overlaps_above` X C
:lookup:`overlaps_below` X C
:lookup:`strictly_above` X C
:lookup:`strictly_below` X C
================================= ========= ======== ========= ============ ========== ========
.. _database-functions-compatibility:
Database functions
------------------
The following table provides a summary of what geography-specific database
functions are available on each spatial backend.
.. currentmodule:: django.contrib.gis.db.models.functions
==================================== ======= ============== ============ =========== =================
Function PostGIS Oracle MariaDB MySQL SpatiaLite
==================================== ======= ============== ============ =========== =================
:class:`Area` X X X X X
:class:`AsGeoJSON` X X X (≥ 10.2.4) X (≥ 5.7.5) X
:class:`AsGML` X X X
:class:`AsKML` X X
:class:`AsSVG` X X
:class:`AsWKB` X X X X X
:class:`AsWKT` X X X X X
:class:`Azimuth` X X (LWGEOM/RTTOPO)
:class:`BoundingCircle` X X
:class:`Centroid` X X X X X
:class:`Difference` X X X X X
:class:`Distance` X X X X X
:class:`Envelope` X X X X X
:class:`ForcePolygonCW` X X
:class:`GeoHash` X X (≥ 5.7.5) X (LWGEOM/RTTOPO)
:class:`Intersection` X X X X X
:class:`IsValid` X X X (≥ 5.7.5) X
:class:`Length` X X X X X
:class:`LineLocatePoint` X X
:class:`MakeValid` X X (LWGEOM/RTTOPO)
:class:`MemSize` X
:class:`NumGeometries` X X X X X
:class:`NumPoints` X X X X X
:class:`Perimeter` X X X
:class:`PointOnSurface` X X X X
:class:`Reverse` X X X
:class:`Scale` X X
:class:`SnapToGrid` X X
:class:`SymDifference` X X X X X
:class:`Transform` X X X
:class:`Translate` X X
:class:`Union` X X X X X
==================================== ======= ============== ============ =========== =================
Aggregate Functions
-------------------
The following table provides a summary of what GIS-specific aggregate functions
are available on each spatial backend. Please note that MySQL does not
support any of these aggregates, and is thus excluded from the table.
.. currentmodule:: django.contrib.gis.db.models
======================= ======= ====== ==========
Aggregate PostGIS Oracle SpatiaLite
======================= ======= ====== ==========
:class:`Collect` X X
:class:`Extent` X X X
:class:`Extent3D` X
:class:`MakeLine` X X
:class:`Union` X X X
======================= ======= ====== ==========
.. rubric:: Footnotes
.. [#fnwkt] *See* Open Geospatial Consortium, Inc., `OpenGIS Simple Feature Specification For SQL <https://portal.ogc.org/files/?artifact_id=829>`_, Document 99-049 (May 5, 1999), at Ch. 3.2.5, p. 3-11 (SQL Textual Representation of Geometry).
.. [#fnewkb] *See* `PostGIS EWKB, EWKT and Canonical Forms <https://postgis.net/docs/using_postgis_dbmanagement.html#EWKB_EWKT>`_, PostGIS documentation at Ch. 4.1.2.
.. [#fndistsphere15] *See* `PostGIS documentation <https://postgis.net/docs/ST_DistanceSphere.html>`_ on ``ST_DistanceSphere``.
.. [#] Refer :ref:`mysql-spatial-limitations` section for more details.