| 1 |
========================== |
| 2 |
GIS QuerySet API Reference |
| 3 |
========================== |
| 4 |
|
| 5 |
.. currentmodule:: django.contrib.gis.db.models |
| 6 |
|
| 7 |
.. _spatial-lookups: |
| 8 |
|
| 9 |
Spatial Lookups |
| 10 |
=============== |
| 11 |
|
| 12 |
The spatial lookups in this section are available for :class:`GeometryField` |
| 13 |
and :class:`RasterField`. |
| 14 |
|
| 15 |
For an introduction, see the :ref:`spatial lookups introduction |
| 16 |
<spatial-lookups-intro>`. For an overview of what lookups are |
| 17 |
compatible with a particular spatial backend, refer to the |
| 18 |
:ref:`spatial lookup compatibility table <spatial-lookup-compatibility>`. |
| 19 |
|
| 20 |
Lookups with rasters |
| 21 |
-------------------- |
| 22 |
|
| 23 |
All examples in the reference below are given for geometry fields and inputs, |
| 24 |
but the lookups can be used the same way with rasters on both sides. Whenever |
| 25 |
a lookup doesn't support raster input, the input is automatically |
| 26 |
converted to a geometry where necessary using the `ST_Polygon |
| 27 |
<https://postgis.net/docs/RT_ST_Polygon.html>`_ function. See also the |
| 28 |
:ref:`introduction to raster lookups <spatial-lookup-raster>`. |
| 29 |
|
| 30 |
The database operators used by the lookups can be divided into three categories: |
| 31 |
|
| 32 |
- Native raster support ``N``: the operator accepts rasters natively on both |
| 33 |
sides of the lookup, and raster input can be mixed with geometry inputs. |
| 34 |
|
| 35 |
- Bilateral raster support ``B``: the operator supports rasters only if both |
| 36 |
sides of the lookup receive raster inputs. Raster data is automatically |
| 37 |
converted to geometries for mixed lookups. |
| 38 |
|
| 39 |
- Geometry conversion support ``C``. The lookup does not have native raster |
| 40 |
support, all raster data is automatically converted to geometries. |
| 41 |
|
| 42 |
The examples below show the SQL equivalent for the lookups in the different |
| 43 |
types of raster support. The same pattern applies to all spatial lookups. |
| 44 |
|
| 45 |
==== ============================== ======================================================= |
| 46 |
Case Lookup SQL Equivalent |
| 47 |
==== ============================== ======================================================= |
| 48 |
N, B ``rast__contains=rst`` ``ST_Contains(rast, rst)`` |
| 49 |
N, B ``rast__1__contains=(rst, 2)`` ``ST_Contains(rast, 1, rst, 2)`` |
| 50 |
B, C ``rast__contains=geom`` ``ST_Contains(ST_Polygon(rast), geom)`` |
| 51 |
B, C ``rast__1__contains=geom`` ``ST_Contains(ST_Polygon(rast, 1), geom)`` |
| 52 |
B, C ``poly__contains=rst`` ``ST_Contains(poly, ST_Polygon(rst))`` |
| 53 |
B, C ``poly__contains=(rst, 1)`` ``ST_Contains(poly, ST_Polygon(rst, 1))`` |
| 54 |
C ``rast__crosses=rst`` ``ST_Crosses(ST_Polygon(rast), ST_Polygon(rst))`` |
| 55 |
C ``rast__1__crosses=(rst, 2)`` ``ST_Crosses(ST_Polygon(rast, 1), ST_Polygon(rst, 2))`` |
| 56 |
C ``rast__crosses=geom`` ``ST_Crosses(ST_Polygon(rast), geom)`` |
| 57 |
C ``poly__crosses=rst`` ``ST_Crosses(poly, ST_Polygon(rst))`` |
| 58 |
==== ============================== ======================================================= |
| 59 |
|
| 60 |
Spatial lookups with rasters are only supported for PostGIS backends |
| 61 |
(denominated as PGRaster in this section). |
| 62 |
|
| 63 |
.. fieldlookup:: bbcontains |
| 64 |
|
| 65 |
``bbcontains`` |
| 66 |
-------------- |
| 67 |
|
| 68 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Contain.html>`__, |
| 69 |
MariaDB, MySQL, SpatiaLite, PGRaster (Native) |
| 70 |
|
| 71 |
Tests if the geometry or raster field's bounding box completely contains the |
| 72 |
lookup geometry's bounding box. |
| 73 |
|
| 74 |
Example:: |
| 75 |
|
| 76 |
Zipcode.objects.filter(poly__bbcontains=geom) |
| 77 |
|
| 78 |
========== ========================== |
| 79 |
Backend SQL Equivalent |
| 80 |
========== ========================== |
| 81 |
PostGIS ``poly ~ geom`` |
| 82 |
MariaDB ``MBRContains(poly, geom)`` |
| 83 |
MySQL ``MBRContains(poly, geom)`` |
| 84 |
SpatiaLite ``MbrContains(poly, geom)`` |
| 85 |
========== ========================== |
| 86 |
|
| 87 |
.. fieldlookup:: bboverlaps |
| 88 |
|
| 89 |
``bboverlaps`` |
| 90 |
-------------- |
| 91 |
|
| 92 |
*Availability*: `PostGIS <https://postgis.net/docs/geometry_overlaps.html>`__, |
| 93 |
MariaDB, MySQL, SpatiaLite, PGRaster (Native) |
| 94 |
|
| 95 |
Tests if the geometry field's bounding box overlaps the lookup geometry's |
| 96 |
bounding box. |
| 97 |
|
| 98 |
Example:: |
| 99 |
|
| 100 |
Zipcode.objects.filter(poly__bboverlaps=geom) |
| 101 |
|
| 102 |
========== ========================== |
| 103 |
Backend SQL Equivalent |
| 104 |
========== ========================== |
| 105 |
PostGIS ``poly && geom`` |
| 106 |
MariaDB ``MBROverlaps(poly, geom)`` |
| 107 |
MySQL ``MBROverlaps(poly, geom)`` |
| 108 |
SpatiaLite ``MbrOverlaps(poly, geom)`` |
| 109 |
========== ========================== |
| 110 |
|
| 111 |
.. fieldlookup:: contained |
| 112 |
|
| 113 |
``contained`` |
| 114 |
------------- |
| 115 |
|
| 116 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Contained.html>`__, |
| 117 |
MariaDB, MySQL, SpatiaLite, PGRaster (Native) |
| 118 |
|
| 119 |
Tests if the geometry field's bounding box is completely contained by the |
| 120 |
lookup geometry's bounding box. |
| 121 |
|
| 122 |
Example:: |
| 123 |
|
| 124 |
Zipcode.objects.filter(poly__contained=geom) |
| 125 |
|
| 126 |
========== ========================== |
| 127 |
Backend SQL Equivalent |
| 128 |
========== ========================== |
| 129 |
PostGIS ``poly @ geom`` |
| 130 |
MariaDB ``MBRWithin(poly, geom)`` |
| 131 |
MySQL ``MBRWithin(poly, geom)`` |
| 132 |
SpatiaLite ``MbrWithin(poly, geom)`` |
| 133 |
========== ========================== |
| 134 |
|
| 135 |
.. fieldlookup:: gis-contains |
| 136 |
|
| 137 |
``contains`` |
| 138 |
------------ |
| 139 |
|
| 140 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Contains.html>`__, |
| 141 |
Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral) |
| 142 |
|
| 143 |
Tests if the geometry field spatially contains the lookup geometry. |
| 144 |
|
| 145 |
Example:: |
| 146 |
|
| 147 |
Zipcode.objects.filter(poly__contains=geom) |
| 148 |
|
| 149 |
========== ============================ |
| 150 |
Backend SQL Equivalent |
| 151 |
========== ============================ |
| 152 |
PostGIS ``ST_Contains(poly, geom)`` |
| 153 |
Oracle ``SDO_CONTAINS(poly, geom)`` |
| 154 |
MariaDB ``ST_Contains(poly, geom)`` |
| 155 |
MySQL ``ST_Contains(poly, geom)`` |
| 156 |
SpatiaLite ``Contains(poly, geom)`` |
| 157 |
========== ============================ |
| 158 |
|
| 159 |
.. fieldlookup:: contains_properly |
| 160 |
|
| 161 |
``contains_properly`` |
| 162 |
--------------------- |
| 163 |
|
| 164 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_ContainsProperly.html>`__, |
| 165 |
PGRaster (Bilateral) |
| 166 |
|
| 167 |
Returns true if the lookup geometry intersects the interior of the |
| 168 |
geometry field, but not the boundary (or exterior). |
| 169 |
|
| 170 |
Example:: |
| 171 |
|
| 172 |
Zipcode.objects.filter(poly__contains_properly=geom) |
| 173 |
|
| 174 |
========== =================================== |
| 175 |
Backend SQL Equivalent |
| 176 |
========== =================================== |
| 177 |
PostGIS ``ST_ContainsProperly(poly, geom)`` |
| 178 |
========== =================================== |
| 179 |
|
| 180 |
.. fieldlookup:: coveredby |
| 181 |
|
| 182 |
``coveredby`` |
| 183 |
------------- |
| 184 |
|
| 185 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_CoveredBy.html>`__, |
| 186 |
Oracle, MySQL, PGRaster (Bilateral), SpatiaLite |
| 187 |
|
| 188 |
Tests if no point in the geometry field is outside the lookup geometry. |
| 189 |
[#fncovers]_ |
| 190 |
|
| 191 |
Example:: |
| 192 |
|
| 193 |
Zipcode.objects.filter(poly__coveredby=geom) |
| 194 |
|
| 195 |
========== ============================= |
| 196 |
Backend SQL Equivalent |
| 197 |
========== ============================= |
| 198 |
PostGIS ``ST_CoveredBy(poly, geom)`` |
| 199 |
Oracle ``SDO_COVEREDBY(poly, geom)`` |
| 200 |
MySQL ``MBRCoveredBy(poly, geom)`` |
| 201 |
SpatiaLite ``CoveredBy(poly, geom)`` |
| 202 |
========== ============================= |
| 203 |
|
| 204 |
.. versionchanged:: 5.2 |
| 205 |
|
| 206 |
MySQL support was added. |
| 207 |
|
| 208 |
.. fieldlookup:: covers |
| 209 |
|
| 210 |
``covers`` |
| 211 |
---------- |
| 212 |
|
| 213 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Covers.html>`__, |
| 214 |
Oracle, MySQL, PGRaster (Bilateral), SpatiaLite |
| 215 |
|
| 216 |
Tests if no point in the lookup geometry is outside the geometry field. |
| 217 |
[#fncovers]_ |
| 218 |
|
| 219 |
Example:: |
| 220 |
|
| 221 |
Zipcode.objects.filter(poly__covers=geom) |
| 222 |
|
| 223 |
========== ========================== |
| 224 |
Backend SQL Equivalent |
| 225 |
========== ========================== |
| 226 |
PostGIS ``ST_Covers(poly, geom)`` |
| 227 |
Oracle ``SDO_COVERS(poly, geom)`` |
| 228 |
MySQL ``MBRCovers(poly, geom)`` |
| 229 |
SpatiaLite ``Covers(poly, geom)`` |
| 230 |
========== ========================== |
| 231 |
|
| 232 |
.. versionchanged:: 5.2 |
| 233 |
|
| 234 |
MySQL support was added. |
| 235 |
|
| 236 |
.. fieldlookup:: crosses |
| 237 |
|
| 238 |
``crosses`` |
| 239 |
----------- |
| 240 |
|
| 241 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Crosses.html>`__, |
| 242 |
MariaDB, MySQL, SpatiaLite, PGRaster (Conversion) |
| 243 |
|
| 244 |
Tests if the geometry field spatially crosses the lookup geometry. |
| 245 |
|
| 246 |
Example:: |
| 247 |
|
| 248 |
Zipcode.objects.filter(poly__crosses=geom) |
| 249 |
|
| 250 |
========== ========================== |
| 251 |
Backend SQL Equivalent |
| 252 |
========== ========================== |
| 253 |
PostGIS ``ST_Crosses(poly, geom)`` |
| 254 |
MariaDB ``ST_Crosses(poly, geom)`` |
| 255 |
MySQL ``ST_Crosses(poly, geom)`` |
| 256 |
SpatiaLite ``Crosses(poly, geom)`` |
| 257 |
========== ========================== |
| 258 |
|
| 259 |
.. fieldlookup:: disjoint |
| 260 |
|
| 261 |
``disjoint`` |
| 262 |
------------ |
| 263 |
|
| 264 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Disjoint.html>`__, |
| 265 |
Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral) |
| 266 |
|
| 267 |
Tests if the geometry field is spatially disjoint from the lookup geometry. |
| 268 |
|
| 269 |
Example:: |
| 270 |
|
| 271 |
Zipcode.objects.filter(poly__disjoint=geom) |
| 272 |
|
| 273 |
========== ================================================= |
| 274 |
Backend SQL Equivalent |
| 275 |
========== ================================================= |
| 276 |
PostGIS ``ST_Disjoint(poly, geom)`` |
| 277 |
Oracle ``SDO_GEOM.RELATE(poly, 'DISJOINT', geom, 0.05)`` |
| 278 |
MariaDB ``ST_Disjoint(poly, geom)`` |
| 279 |
MySQL ``ST_Disjoint(poly, geom)`` |
| 280 |
SpatiaLite ``Disjoint(poly, geom)`` |
| 281 |
========== ================================================= |
| 282 |
|
| 283 |
.. fieldlookup:: equals |
| 284 |
|
| 285 |
``equals`` |
| 286 |
---------- |
| 287 |
|
| 288 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Equals.html>`__, |
| 289 |
Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Conversion) |
| 290 |
|
| 291 |
Tests if the geometry field is spatially equal to the lookup geometry. |
| 292 |
|
| 293 |
Example:: |
| 294 |
|
| 295 |
Zipcode.objects.filter(poly__equals=geom) |
| 296 |
|
| 297 |
========== ================================================= |
| 298 |
Backend SQL Equivalent |
| 299 |
========== ================================================= |
| 300 |
PostGIS ``ST_Equals(poly, geom)`` |
| 301 |
Oracle ``SDO_EQUAL(poly, geom)`` |
| 302 |
MariaDB ``ST_Equals(poly, geom)`` |
| 303 |
MySQL ``ST_Equals(poly, geom)`` |
| 304 |
SpatiaLite ``Equals(poly, geom)`` |
| 305 |
========== ================================================= |
| 306 |
|
| 307 |
.. fieldlookup:: exact |
| 308 |
:noindex: |
| 309 |
.. fieldlookup:: same_as |
| 310 |
|
| 311 |
``exact``, ``same_as`` |
| 312 |
---------------------- |
| 313 |
|
| 314 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Same.html>`__, |
| 315 |
Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral) |
| 316 |
|
| 317 |
Tests if the geometry field is "equal" to the lookup geometry. On Oracle, |
| 318 |
MySQL, and SpatiaLite, it tests spatial equality, while on PostGIS it tests |
| 319 |
equality of bounding boxes. |
| 320 |
|
| 321 |
Example:: |
| 322 |
|
| 323 |
Zipcode.objects.filter(poly=geom) |
| 324 |
|
| 325 |
========== ================================================= |
| 326 |
Backend SQL Equivalent |
| 327 |
========== ================================================= |
| 328 |
PostGIS ``poly ~= geom`` |
| 329 |
Oracle ``SDO_EQUAL(poly, geom)`` |
| 330 |
MariaDB ``ST_Equals(poly, geom)`` |
| 331 |
MySQL ``ST_Equals(poly, geom)`` |
| 332 |
SpatiaLite ``Equals(poly, geom)`` |
| 333 |
========== ================================================= |
| 334 |
|
| 335 |
.. fieldlookup:: intersects |
| 336 |
|
| 337 |
``intersects`` |
| 338 |
-------------- |
| 339 |
|
| 340 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Intersects.html>`__, |
| 341 |
Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral) |
| 342 |
|
| 343 |
Tests if the geometry field spatially intersects the lookup geometry. |
| 344 |
|
| 345 |
Example:: |
| 346 |
|
| 347 |
Zipcode.objects.filter(poly__intersects=geom) |
| 348 |
|
| 349 |
========== ================================================= |
| 350 |
Backend SQL Equivalent |
| 351 |
========== ================================================= |
| 352 |
PostGIS ``ST_Intersects(poly, geom)`` |
| 353 |
Oracle ``SDO_OVERLAPBDYINTERSECT(poly, geom)`` |
| 354 |
MariaDB ``ST_Intersects(poly, geom)`` |
| 355 |
MySQL ``ST_Intersects(poly, geom)`` |
| 356 |
SpatiaLite ``Intersects(poly, geom)`` |
| 357 |
========== ================================================= |
| 358 |
|
| 359 |
.. fieldlookup:: isempty |
| 360 |
|
| 361 |
``isempty`` |
| 362 |
----------- |
| 363 |
|
| 364 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_IsEmpty.html>`__ |
| 365 |
|
| 366 |
Tests if the geometry is empty. |
| 367 |
|
| 368 |
Example:: |
| 369 |
|
| 370 |
Zipcode.objects.filter(poly__isempty=True) |
| 371 |
|
| 372 |
.. fieldlookup:: isvalid |
| 373 |
|
| 374 |
``isvalid`` |
| 375 |
----------- |
| 376 |
|
| 377 |
*Availability*: MySQL, `PostGIS <https://postgis.net/docs/ST_IsValid.html>`__, |
| 378 |
Oracle, SpatiaLite |
| 379 |
|
| 380 |
Tests if the geometry is valid. |
| 381 |
|
| 382 |
Example:: |
| 383 |
|
| 384 |
Zipcode.objects.filter(poly__isvalid=True) |
| 385 |
|
| 386 |
========================== ================================================================ |
| 387 |
Backend SQL Equivalent |
| 388 |
========================== ================================================================ |
| 389 |
MySQL, PostGIS, SpatiaLite ``ST_IsValid(poly)`` |
| 390 |
Oracle ``SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(poly, 0.05) = 'TRUE'`` |
| 391 |
========================== ================================================================ |
| 392 |
|
| 393 |
.. fieldlookup:: overlaps |
| 394 |
|
| 395 |
``overlaps`` |
| 396 |
------------ |
| 397 |
|
| 398 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Overlaps.html>`__, |
| 399 |
Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral) |
| 400 |
|
| 401 |
Tests if the geometry field spatially overlaps the lookup geometry. |
| 402 |
|
| 403 |
========== ============================ |
| 404 |
Backend SQL Equivalent |
| 405 |
========== ============================ |
| 406 |
PostGIS ``ST_Overlaps(poly, geom)`` |
| 407 |
Oracle ``SDO_OVERLAPS(poly, geom)`` |
| 408 |
MariaDB ``ST_Overlaps(poly, geom)`` |
| 409 |
MySQL ``ST_Overlaps(poly, geom)`` |
| 410 |
SpatiaLite ``Overlaps(poly, geom)`` |
| 411 |
========== ============================ |
| 412 |
|
| 413 |
.. fieldlookup:: relate |
| 414 |
|
| 415 |
``relate`` |
| 416 |
---------- |
| 417 |
|
| 418 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Relate.html>`__, |
| 419 |
MariaDB, Oracle, SpatiaLite, PGRaster (Conversion) |
| 420 |
|
| 421 |
Tests if the geometry field is spatially related to the lookup geometry by |
| 422 |
the values given in the given pattern. This lookup requires a tuple parameter, |
| 423 |
``(geom, pattern)``; the form of ``pattern`` will depend on the spatial backend: |
| 424 |
|
| 425 |
MariaDB, PostGIS, and SpatiaLite |
| 426 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 427 |
|
| 428 |
On these spatial backends the intersection pattern is a string comprising |
| 429 |
nine characters, which define intersections between the interior, boundary, |
| 430 |
and exterior of the geometry field and the lookup geometry. |
| 431 |
The intersection pattern matrix may only use the following characters: |
| 432 |
``1``, ``2``, ``T``, ``F``, or ``*``. This lookup type allows users to "fine tune" |
| 433 |
a specific geometric relationship consistent with the DE-9IM model. [#fnde9im]_ |
| 434 |
|
| 435 |
Geometry example:: |
| 436 |
|
| 437 |
# A tuple lookup parameter is used to specify the geometry and |
| 438 |
# the intersection pattern (the pattern here is for 'contains'). |
| 439 |
Zipcode.objects.filter(poly__relate=(geom, "T*T***FF*")) |
| 440 |
|
| 441 |
PostGIS and MariaDB SQL equivalent: |
| 442 |
|
| 443 |
.. code-block:: sql |
| 444 |
|
| 445 |
SELECT ... WHERE ST_Relate(poly, geom, 'T*T***FF*') |
| 446 |
|
| 447 |
SpatiaLite SQL equivalent: |
| 448 |
|
| 449 |
.. code-block:: sql |
| 450 |
|
| 451 |
SELECT ... WHERE Relate(poly, geom, 'T*T***FF*') |
| 452 |
|
| 453 |
Raster example:: |
| 454 |
|
| 455 |
Zipcode.objects.filter(poly__relate=(rast, 1, "T*T***FF*")) |
| 456 |
Zipcode.objects.filter(rast__2__relate=(rast, 1, "T*T***FF*")) |
| 457 |
|
| 458 |
PostGIS SQL equivalent: |
| 459 |
|
| 460 |
.. code-block:: sql |
| 461 |
|
| 462 |
SELECT ... WHERE ST_Relate(poly, ST_Polygon(rast, 1), 'T*T***FF*') |
| 463 |
SELECT ... WHERE ST_Relate(ST_Polygon(rast, 2), ST_Polygon(rast, 1), 'T*T***FF*') |
| 464 |
|
| 465 |
Oracle |
| 466 |
~~~~~~ |
| 467 |
|
| 468 |
Here the relation pattern is comprised of at least one of the nine relation |
| 469 |
strings: ``TOUCH``, ``OVERLAPBDYDISJOINT``, ``OVERLAPBDYINTERSECT``, |
| 470 |
``EQUAL``, ``INSIDE``, ``COVEREDBY``, ``CONTAINS``, ``COVERS``, ``ON``, and |
| 471 |
``ANYINTERACT``. Multiple strings may be combined with the logical Boolean |
| 472 |
operator OR, for example, ``'inside+touch'``. [#fnsdorelate]_ The relation |
| 473 |
strings are case-insensitive. |
| 474 |
|
| 475 |
Example:: |
| 476 |
|
| 477 |
Zipcode.objects.filter(poly__relate=(geom, "anyinteract")) |
| 478 |
|
| 479 |
Oracle SQL equivalent: |
| 480 |
|
| 481 |
.. code-block:: sql |
| 482 |
|
| 483 |
SELECT ... WHERE SDO_RELATE(poly, geom, 'anyinteract') |
| 484 |
|
| 485 |
.. fieldlookup:: touches |
| 486 |
|
| 487 |
``touches`` |
| 488 |
----------- |
| 489 |
|
| 490 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Touches.html>`__, |
| 491 |
Oracle, MariaDB, MySQL, SpatiaLite |
| 492 |
|
| 493 |
Tests if the geometry field spatially touches the lookup geometry. |
| 494 |
|
| 495 |
Example:: |
| 496 |
|
| 497 |
Zipcode.objects.filter(poly__touches=geom) |
| 498 |
|
| 499 |
========== ========================== |
| 500 |
Backend SQL Equivalent |
| 501 |
========== ========================== |
| 502 |
PostGIS ``ST_Touches(poly, geom)`` |
| 503 |
MariaDB ``ST_Touches(poly, geom)`` |
| 504 |
MySQL ``ST_Touches(poly, geom)`` |
| 505 |
Oracle ``SDO_TOUCH(poly, geom)`` |
| 506 |
SpatiaLite ``Touches(poly, geom)`` |
| 507 |
========== ========================== |
| 508 |
|
| 509 |
.. fieldlookup:: within |
| 510 |
|
| 511 |
``within`` |
| 512 |
---------- |
| 513 |
|
| 514 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Within.html>`__, |
| 515 |
Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral) |
| 516 |
|
| 517 |
Tests if the geometry field is spatially within the lookup geometry. |
| 518 |
|
| 519 |
Example:: |
| 520 |
|
| 521 |
Zipcode.objects.filter(poly__within=geom) |
| 522 |
|
| 523 |
========== ========================== |
| 524 |
Backend SQL Equivalent |
| 525 |
========== ========================== |
| 526 |
PostGIS ``ST_Within(poly, geom)`` |
| 527 |
MariaDB ``ST_Within(poly, geom)`` |
| 528 |
MySQL ``ST_Within(poly, geom)`` |
| 529 |
Oracle ``SDO_INSIDE(poly, geom)`` |
| 530 |
SpatiaLite ``Within(poly, geom)`` |
| 531 |
========== ========================== |
| 532 |
|
| 533 |
.. fieldlookup:: left |
| 534 |
|
| 535 |
``left`` |
| 536 |
-------- |
| 537 |
|
| 538 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Left.html>`__, |
| 539 |
PGRaster (Conversion) |
| 540 |
|
| 541 |
Tests if the geometry field's bounding box is strictly to the left of the |
| 542 |
lookup geometry's bounding box. |
| 543 |
|
| 544 |
Example:: |
| 545 |
|
| 546 |
Zipcode.objects.filter(poly__left=geom) |
| 547 |
|
| 548 |
PostGIS equivalent: |
| 549 |
|
| 550 |
.. code-block:: sql |
| 551 |
|
| 552 |
SELECT ... WHERE poly << geom |
| 553 |
|
| 554 |
.. fieldlookup:: right |
| 555 |
|
| 556 |
``right`` |
| 557 |
--------- |
| 558 |
|
| 559 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Right.html>`__, |
| 560 |
PGRaster (Conversion) |
| 561 |
|
| 562 |
Tests if the geometry field's bounding box is strictly to the right of the |
| 563 |
lookup geometry's bounding box. |
| 564 |
|
| 565 |
Example:: |
| 566 |
|
| 567 |
Zipcode.objects.filter(poly__right=geom) |
| 568 |
|
| 569 |
PostGIS equivalent: |
| 570 |
|
| 571 |
.. code-block:: sql |
| 572 |
|
| 573 |
SELECT ... WHERE poly >> geom |
| 574 |
|
| 575 |
.. fieldlookup:: overlaps_left |
| 576 |
|
| 577 |
``overlaps_left`` |
| 578 |
----------------- |
| 579 |
|
| 580 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Overleft.html>`__, |
| 581 |
PGRaster (Bilateral) |
| 582 |
|
| 583 |
Tests if the geometry field's bounding box overlaps or is to the left of the lookup |
| 584 |
geometry's bounding box. |
| 585 |
|
| 586 |
Example:: |
| 587 |
|
| 588 |
Zipcode.objects.filter(poly__overlaps_left=geom) |
| 589 |
|
| 590 |
PostGIS equivalent: |
| 591 |
|
| 592 |
.. code-block:: sql |
| 593 |
|
| 594 |
SELECT ... WHERE poly &< geom |
| 595 |
|
| 596 |
|
| 597 |
.. fieldlookup:: overlaps_right |
| 598 |
|
| 599 |
``overlaps_right`` |
| 600 |
------------------ |
| 601 |
|
| 602 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Overright.html>`__, |
| 603 |
PGRaster (Bilateral) |
| 604 |
|
| 605 |
Tests if the geometry field's bounding box overlaps or is to the right of the lookup |
| 606 |
geometry's bounding box. |
| 607 |
|
| 608 |
Example:: |
| 609 |
|
| 610 |
Zipcode.objects.filter(poly__overlaps_right=geom) |
| 611 |
|
| 612 |
PostGIS equivalent: |
| 613 |
|
| 614 |
.. code-block:: sql |
| 615 |
|
| 616 |
SELECT ... WHERE poly &> geom |
| 617 |
|
| 618 |
.. fieldlookup:: overlaps_above |
| 619 |
|
| 620 |
``overlaps_above`` |
| 621 |
------------------ |
| 622 |
|
| 623 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Overabove.html>`__, |
| 624 |
PGRaster (Conversion) |
| 625 |
|
| 626 |
Tests if the geometry field's bounding box overlaps or is above the lookup |
| 627 |
geometry's bounding box. |
| 628 |
|
| 629 |
Example:: |
| 630 |
|
| 631 |
Zipcode.objects.filter(poly__overlaps_above=geom) |
| 632 |
|
| 633 |
PostGIS equivalent: |
| 634 |
|
| 635 |
.. code-block:: sql |
| 636 |
|
| 637 |
SELECT ... WHERE poly |&> geom |
| 638 |
|
| 639 |
.. fieldlookup:: overlaps_below |
| 640 |
|
| 641 |
``overlaps_below`` |
| 642 |
------------------ |
| 643 |
|
| 644 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Overbelow.html>`__, |
| 645 |
PGRaster (Conversion) |
| 646 |
|
| 647 |
Tests if the geometry field's bounding box overlaps or is below the lookup |
| 648 |
geometry's bounding box. |
| 649 |
|
| 650 |
Example:: |
| 651 |
|
| 652 |
Zipcode.objects.filter(poly__overlaps_below=geom) |
| 653 |
|
| 654 |
PostGIS equivalent: |
| 655 |
|
| 656 |
.. code-block:: sql |
| 657 |
|
| 658 |
SELECT ... WHERE poly &<| geom |
| 659 |
|
| 660 |
.. fieldlookup:: strictly_above |
| 661 |
|
| 662 |
``strictly_above`` |
| 663 |
------------------ |
| 664 |
|
| 665 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Above.html>`__, |
| 666 |
PGRaster (Conversion) |
| 667 |
|
| 668 |
Tests if the geometry field's bounding box is strictly above the lookup |
| 669 |
geometry's bounding box. |
| 670 |
|
| 671 |
Example:: |
| 672 |
|
| 673 |
Zipcode.objects.filter(poly__strictly_above=geom) |
| 674 |
|
| 675 |
PostGIS equivalent: |
| 676 |
|
| 677 |
.. code-block:: sql |
| 678 |
|
| 679 |
SELECT ... WHERE poly |>> geom |
| 680 |
|
| 681 |
.. fieldlookup:: strictly_below |
| 682 |
|
| 683 |
``strictly_below`` |
| 684 |
------------------ |
| 685 |
|
| 686 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Geometry_Below.html>`__, |
| 687 |
PGRaster (Conversion) |
| 688 |
|
| 689 |
Tests if the geometry field's bounding box is strictly below the lookup |
| 690 |
geometry's bounding box. |
| 691 |
|
| 692 |
Example:: |
| 693 |
|
| 694 |
Zipcode.objects.filter(poly__strictly_below=geom) |
| 695 |
|
| 696 |
PostGIS equivalent: |
| 697 |
|
| 698 |
.. code-block:: sql |
| 699 |
|
| 700 |
SELECT ... WHERE poly <<| geom |
| 701 |
|
| 702 |
|
| 703 |
.. _distance-lookups: |
| 704 |
|
| 705 |
Distance Lookups |
| 706 |
================ |
| 707 |
|
| 708 |
*Availability*: PostGIS, Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Native) |
| 709 |
|
| 710 |
For an overview on performing distance queries, please refer to |
| 711 |
the :ref:`distance queries introduction <distance-queries>`. |
| 712 |
|
| 713 |
Distance lookups take the following form: |
| 714 |
|
| 715 |
.. code-block:: text |
| 716 |
|
| 717 |
<field>__<distance lookup>=(<geometry/raster>, <distance value>[, "spheroid"]) |
| 718 |
<field>__<distance lookup>=(<raster>, <band_index>, <distance value>[, "spheroid"]) |
| 719 |
<field>__<band_index>__<distance lookup>=(<raster>, <band_index>, <distance value>[, "spheroid"]) |
| 720 |
|
| 721 |
The value passed into a distance lookup is a tuple; the first two |
| 722 |
values are mandatory, and are the geometry to calculate distances to, |
| 723 |
and a distance value (either a number in units of the field, a |
| 724 |
:class:`~django.contrib.gis.measure.Distance` object, or a :doc:`query |
| 725 |
expression </ref/models/expressions>`). To pass a band index to the lookup, use |
| 726 |
a 3-tuple where the second entry is the band index. |
| 727 |
|
| 728 |
On every distance lookup except :lookup:`dwithin`, an optional element, |
| 729 |
``'spheroid'``, may be included to use the more accurate spheroid distance |
| 730 |
calculation functions on fields with a geodetic coordinate system. |
| 731 |
|
| 732 |
On PostgreSQL, the ``'spheroid'`` option uses `ST_DistanceSpheroid |
| 733 |
<https://postgis.net/docs/ST_Distance_Spheroid.html>`__ instead of |
| 734 |
`ST_DistanceSphere <https://postgis.net/docs/ST_DistanceSphere.html>`__. The |
| 735 |
simpler `ST_Distance <https://postgis.net/docs/ST_Distance.html>`__ function is |
| 736 |
used with projected coordinate systems. Rasters are converted to geometries for |
| 737 |
spheroid based lookups. |
| 738 |
|
| 739 |
.. fieldlookup:: distance_gt |
| 740 |
|
| 741 |
``distance_gt`` |
| 742 |
--------------- |
| 743 |
|
| 744 |
Returns models where the distance to the geometry field from the lookup |
| 745 |
geometry is greater than the given distance value. |
| 746 |
|
| 747 |
Example:: |
| 748 |
|
| 749 |
Zipcode.objects.filter(poly__distance_gt=(geom, D(m=5))) |
| 750 |
|
| 751 |
========== ================================================== |
| 752 |
Backend SQL Equivalent |
| 753 |
========== ================================================== |
| 754 |
PostGIS ``ST_Distance/ST_Distance_Sphere(poly, geom) > 5`` |
| 755 |
MariaDB ``ST_Distance(poly, geom) > 5`` |
| 756 |
MySQL ``ST_Distance(poly, geom) > 5`` |
| 757 |
Oracle ``SDO_GEOM.SDO_DISTANCE(poly, geom, 0.05) > 5`` |
| 758 |
SpatiaLite ``Distance(poly, geom) > 5`` |
| 759 |
========== ================================================== |
| 760 |
|
| 761 |
.. fieldlookup:: distance_gte |
| 762 |
|
| 763 |
``distance_gte`` |
| 764 |
---------------- |
| 765 |
|
| 766 |
Returns models where the distance to the geometry field from the lookup |
| 767 |
geometry is greater than or equal to the given distance value. |
| 768 |
|
| 769 |
Example:: |
| 770 |
|
| 771 |
Zipcode.objects.filter(poly__distance_gte=(geom, D(m=5))) |
| 772 |
|
| 773 |
========== =================================================== |
| 774 |
Backend SQL Equivalent |
| 775 |
========== =================================================== |
| 776 |
PostGIS ``ST_Distance/ST_Distance_Sphere(poly, geom) >= 5`` |
| 777 |
MariaDB ``ST_Distance(poly, geom) >= 5`` |
| 778 |
MySQL ``ST_Distance(poly, geom) >= 5`` |
| 779 |
Oracle ``SDO_GEOM.SDO_DISTANCE(poly, geom, 0.05) >= 5`` |
| 780 |
SpatiaLite ``Distance(poly, geom) >= 5`` |
| 781 |
========== =================================================== |
| 782 |
|
| 783 |
.. fieldlookup:: distance_lt |
| 784 |
|
| 785 |
``distance_lt`` |
| 786 |
--------------- |
| 787 |
|
| 788 |
Returns models where the distance to the geometry field from the lookup |
| 789 |
geometry is less than the given distance value. |
| 790 |
|
| 791 |
Example:: |
| 792 |
|
| 793 |
Zipcode.objects.filter(poly__distance_lt=(geom, D(m=5))) |
| 794 |
|
| 795 |
========== ================================================== |
| 796 |
Backend SQL Equivalent |
| 797 |
========== ================================================== |
| 798 |
PostGIS ``ST_Distance/ST_Distance_Sphere(poly, geom) < 5`` |
| 799 |
MariaDB ``ST_Distance(poly, geom) < 5`` |
| 800 |
MySQL ``ST_Distance(poly, geom) < 5`` |
| 801 |
Oracle ``SDO_GEOM.SDO_DISTANCE(poly, geom, 0.05) < 5`` |
| 802 |
SpatiaLite ``Distance(poly, geom) < 5`` |
| 803 |
========== ================================================== |
| 804 |
|
| 805 |
.. fieldlookup:: distance_lte |
| 806 |
|
| 807 |
``distance_lte`` |
| 808 |
---------------- |
| 809 |
|
| 810 |
Returns models where the distance to the geometry field from the lookup |
| 811 |
geometry is less than or equal to the given distance value. |
| 812 |
|
| 813 |
Example:: |
| 814 |
|
| 815 |
Zipcode.objects.filter(poly__distance_lte=(geom, D(m=5))) |
| 816 |
|
| 817 |
========== =================================================== |
| 818 |
Backend SQL Equivalent |
| 819 |
========== =================================================== |
| 820 |
PostGIS ``ST_Distance/ST_Distance_Sphere(poly, geom) <= 5`` |
| 821 |
MariaDB ``ST_Distance(poly, geom) <= 5`` |
| 822 |
MySQL ``ST_Distance(poly, geom) <= 5`` |
| 823 |
Oracle ``SDO_GEOM.SDO_DISTANCE(poly, geom, 0.05) <= 5`` |
| 824 |
SpatiaLite ``Distance(poly, geom) <= 5`` |
| 825 |
========== =================================================== |
| 826 |
|
| 827 |
.. fieldlookup:: dwithin |
| 828 |
|
| 829 |
``dwithin`` |
| 830 |
----------- |
| 831 |
|
| 832 |
Returns models where the distance to the geometry field from the lookup |
| 833 |
geometry are within the given distance from one another. Note that you can only |
| 834 |
provide :class:`~django.contrib.gis.measure.Distance` objects if the targeted |
| 835 |
geometries are in a projected system. For geographic geometries, you should use |
| 836 |
units of the geometry field (e.g. degrees for ``WGS84``) . |
| 837 |
|
| 838 |
Example:: |
| 839 |
|
| 840 |
Zipcode.objects.filter(poly__dwithin=(geom, D(m=5))) |
| 841 |
|
| 842 |
========== ====================================== |
| 843 |
Backend SQL Equivalent |
| 844 |
========== ====================================== |
| 845 |
PostGIS ``ST_DWithin(poly, geom, 5)`` |
| 846 |
Oracle ``SDO_WITHIN_DISTANCE(poly, geom, 5)`` |
| 847 |
SpatiaLite ``PtDistWithin(poly, geom, 5)`` |
| 848 |
========== ====================================== |
| 849 |
|
| 850 |
.. _gis-aggregation-functions: |
| 851 |
|
| 852 |
Aggregate Functions |
| 853 |
------------------- |
| 854 |
|
| 855 |
Django provides some GIS-specific aggregate functions. For details on how to |
| 856 |
use these aggregate functions, see :doc:`the topic guide on aggregation |
| 857 |
</topics/db/aggregation>`. |
| 858 |
|
| 859 |
===================== ===================================================== |
| 860 |
Keyword Argument Description |
| 861 |
===================== ===================================================== |
| 862 |
``tolerance`` This keyword is for Oracle only. It is for the |
| 863 |
tolerance value used by the ``SDOAGGRTYPE`` |
| 864 |
procedure; the `Oracle documentation`__ has more |
| 865 |
details. |
| 866 |
===================== ===================================================== |
| 867 |
|
| 868 |
__ https://docs.oracle.com/en/database/oracle/oracle-database/21/spatl/ |
| 869 |
spatial-concepts.html#GUID-CE10AB14-D5EA-43BA-A647-DAC9EEF41EE6 |
| 870 |
|
| 871 |
Example: |
| 872 |
|
| 873 |
.. code-block:: pycon |
| 874 |
|
| 875 |
>>> from django.contrib.gis.db.models import Extent, Union |
| 876 |
>>> WorldBorder.objects.aggregate(Extent("mpoly"), Union("mpoly")) |
| 877 |
|
| 878 |
``Collect`` |
| 879 |
~~~~~~~~~~~ |
| 880 |
|
| 881 |
.. class:: Collect(geo_field, filter=None) |
| 882 |
|
| 883 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Collect.html>`__, MySQL, |
| 884 |
SpatiaLite |
| 885 |
|
| 886 |
Returns a ``GEOMETRYCOLLECTION`` or a ``MULTI`` geometry object from the geometry |
| 887 |
column. This is analogous to a simplified version of the :class:`Union` |
| 888 |
aggregate, except it can be several orders of magnitude faster than performing |
| 889 |
a union because it rolls up geometries into a collection or multi object, not |
| 890 |
caring about dissolving boundaries. |
| 891 |
|
| 892 |
.. versionchanged:: 5.1 |
| 893 |
|
| 894 |
MySQL 8.0.24+ support was added. |
| 895 |
|
| 896 |
``Extent`` |
| 897 |
~~~~~~~~~~ |
| 898 |
|
| 899 |
.. class:: Extent(geo_field, filter=None) |
| 900 |
|
| 901 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Extent.html>`__, |
| 902 |
Oracle, SpatiaLite |
| 903 |
|
| 904 |
Returns the extent of all ``geo_field`` in the ``QuerySet`` as a 4-tuple, |
| 905 |
comprising the lower left coordinate and the upper right coordinate. |
| 906 |
|
| 907 |
Example: |
| 908 |
|
| 909 |
.. code-block:: pycon |
| 910 |
|
| 911 |
>>> qs = City.objects.filter(name__in=("Houston", "Dallas")).aggregate(Extent("poly")) |
| 912 |
>>> print(qs["poly__extent"]) |
| 913 |
(-96.8016128540039, 29.7633724212646, -95.3631439208984, 32.782058715820) |
| 914 |
|
| 915 |
``Extent3D`` |
| 916 |
~~~~~~~~~~~~ |
| 917 |
|
| 918 |
.. class:: Extent3D(geo_field, filter=None) |
| 919 |
|
| 920 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_3DExtent.html>`__ |
| 921 |
|
| 922 |
Returns the 3D extent of all ``geo_field`` in the ``QuerySet`` as a 6-tuple, |
| 923 |
comprising the lower left coordinate and upper right coordinate (each with x, y, |
| 924 |
and z coordinates). |
| 925 |
|
| 926 |
Example: |
| 927 |
|
| 928 |
.. code-block:: pycon |
| 929 |
|
| 930 |
>>> qs = City.objects.filter(name__in=("Houston", "Dallas")).aggregate(Extent3D("poly")) |
| 931 |
>>> print(qs["poly__extent3d"]) |
| 932 |
(-96.8016128540039, 29.7633724212646, 0, -95.3631439208984, 32.782058715820, 0) |
| 933 |
|
| 934 |
``MakeLine`` |
| 935 |
~~~~~~~~~~~~ |
| 936 |
|
| 937 |
.. class:: MakeLine(geo_field, filter=None) |
| 938 |
|
| 939 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_MakeLine.html>`__, |
| 940 |
SpatiaLite |
| 941 |
|
| 942 |
Returns a ``LineString`` constructed from the point field geometries in the |
| 943 |
``QuerySet``. Currently, ordering the queryset has no effect. |
| 944 |
|
| 945 |
Example: |
| 946 |
|
| 947 |
.. code-block:: pycon |
| 948 |
|
| 949 |
>>> qs = City.objects.filter(name__in=("Houston", "Dallas")).aggregate(MakeLine("poly")) |
| 950 |
>>> print(qs["poly__makeline"]) |
| 951 |
LINESTRING (-95.3631510000000020 29.7633739999999989, -96.8016109999999941 32.7820570000000018) |
| 952 |
|
| 953 |
``Union`` |
| 954 |
~~~~~~~~~ |
| 955 |
|
| 956 |
.. class:: Union(geo_field, filter=None) |
| 957 |
|
| 958 |
*Availability*: `PostGIS <https://postgis.net/docs/ST_Union.html>`__, |
| 959 |
Oracle, SpatiaLite |
| 960 |
|
| 961 |
This method returns a :class:`~django.contrib.gis.geos.GEOSGeometry` object |
| 962 |
comprising the union of every geometry in the queryset. Please note that use of |
| 963 |
``Union`` is processor intensive and may take a significant amount of time on |
| 964 |
large querysets. |
| 965 |
|
| 966 |
.. note:: |
| 967 |
|
| 968 |
If the computation time for using this method is too expensive, consider |
| 969 |
using :class:`Collect` instead. |
| 970 |
|
| 971 |
Example: |
| 972 |
|
| 973 |
.. code-block:: pycon |
| 974 |
|
| 975 |
>>> u = Zipcode.objects.aggregate(Union(poly)) # This may take a long time. |
| 976 |
>>> u = Zipcode.objects.filter(poly__within=bbox).aggregate( |
| 977 |
... Union(poly) |
| 978 |
... ) # A more sensible approach. |
| 979 |
|
| 980 |
.. rubric:: Footnotes |
| 981 |
.. [#fnde9im] *See* `OpenGIS Simple Feature Specification For SQL <https://portal.ogc.org/files/?artifact_id=829>`_, at Ch. 2.1.13.2, p. 2-13 (The Dimensionally Extended Nine-Intersection Model). |
| 982 |
.. [#fnsdorelate] *See* `SDO_RELATE documentation <https://docs.oracle.com/en/ |
| 983 |
database/oracle/oracle-database/18/spatl/spatial-operators-reference.html# |
| 984 |
GUID-97C17C18-F05E-49B4-BE11-E89B972E2A02>`_, from the Oracle Spatial and |
| 985 |
Graph Developer's Guide. |
| 986 |
.. [#fncovers] For an explanation of this routine, read `Quirks of the "Contains" Spatial Predicate <https://lin-ear-th-inking.blogspot.com/2007/06/subtleties-of-ogc-covers-spatial.html>`_ by Martin Davis (a PostGIS developer). |