Debian Codegraph @

docs/ref/contrib/gis/db-api.txt

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