- fr
- Language: en
GeoDjango Database API¶
Spatial 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’s spatial extensions only support bounding box operations (what MySQL calls minimum bounding rectangles, or MBR). Specifically, MySQL does not conform to the OGC standard:
Currently, MySQL does not implement these functions [Contains
,Crosses
,Disjoint
,Intersects
,Overlaps
,Touches
,Within
] according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions.
In other words, while spatial lookups such as contains
are available in GeoDjango when using MySQL, the results returned are really
equivalent to what would be returned when using bbcontains
on a different spatial backend.
Warning
True spatial indexes (R-trees) are only supported with MyISAM tables on MySQL. [5] In other words, when using MySQL spatial extensions you have to choose between fast spatial lookups and the integrity of your data – MyISAM tables do not support transactions or foreign key constraints.
Creating and Saving Geographic Models¶
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()
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 [1]), HEXEWKB (PostGIS specific – a WKB geometry in
hexadecimal [2]), and GeoJSON [3] (requires GDAL). 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 GEOSGeometry
objects, refer to the GEOS tutorial.
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 geometry fields.
Filters on ‘normal’ fields (e.g. CharField
)
may be chained with those on geographic fields. Thus, geographic queries
take the following general form (assuming the Zipcode
model used in the
GeoDjango Model API):
>>> qs = Zipcode.objects.filter(<field>__<lookup_type>=<parameter>)
>>> qs = Zipcode.objects.exclude(...)
For example:
>>> qs = Zipcode.objects.filter(poly__contains=pnt)
In this case, poly
is the geographic field, contains
is the spatial lookup type, and pnt
is the parameter (which may be a
GEOSGeometry
object or a string of
GeoJSON , WKT, or HEXEWKB).
A complete reference can be found in the spatial lookup reference.
Note
GeoDjango constructs spatial SQL with the GeoQuerySet
, a
subclass of QuerySet
. The
GeoManager
instance attached to your model is what
enables use of GeoQuerySet
.
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 Selecting an SRID section in the GeoDjango Model API documentation for more details.
Distance Lookups¶
Availability: PostGIS, Oracle, SpatiaLite
The following distance lookups are available:
Note
For measuring, rather than querying on distances, use the
GeoQuerySet.distance()
method.
Distance lookups take a tuple parameter comprising:
- A geometry to base calculations from; and
- A number or
Distance
object containing the distance.
If a 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. [4] 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
geography columns 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
GeoDjango distance tests ) 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)
objects = models.GeoManager()
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)))
Compatibility Tables¶
Spatial Lookups¶
The following table provides a summary of what spatial lookups are available for each spatial database backend.
Lookup Type | PostGIS | Oracle | MySQL [6] | SpatiaLite |
---|---|---|---|---|
bbcontains |
X | X | X | |
bboverlaps |
X | X | X | |
contained |
X | X | X | |
contains |
X | X | X | X |
contains_properly |
X | |||
coveredby |
X | X | ||
covers |
X | X | ||
crosses |
X | X | ||
disjoint |
X | X | X | X |
distance_gt |
X | X | X | |
distance_gte |
X | X | X | |
distance_lt |
X | X | X | |
distance_lte |
X | X | X | |
dwithin |
X | X | ||
equals |
X | X | X | X |
exact |
X | X | X | X |
intersects |
X | X | X | X |
overlaps |
X | X | X | X |
relate |
X | X | X | |
same_as |
X | X | X | X |
touches |
X | X | X | X |
within |
X | X | X | X |
left |
X | |||
right |
X | |||
overlaps_left |
X | |||
overlaps_right |
X | |||
overlaps_above |
X | |||
overlaps_below |
X | |||
strictly_above |
X | |||
strictly_below |
X |
GeoQuerySet
Methods¶
The following table provides a summary of what GeoQuerySet
methods
are available on each spatial backend. Please note that MySQL does not
support any of these methods, and is thus excluded from the table.
Method | PostGIS | Oracle | SpatiaLite |
---|---|---|---|
GeoQuerySet.area() |
X | X | X |
GeoQuerySet.centroid() |
X | X | X |
GeoQuerySet.difference() |
X | X | X |
GeoQuerySet.distance() |
X | X | X |
GeoQuerySet.envelope() |
X | X | |
GeoQuerySet.force_rhr() |
X | ||
GeoQuerySet.geohash() |
X | ||
GeoQuerySet.geojson() |
X | X | |
GeoQuerySet.gml() |
X | X | X |
GeoQuerySet.intersection() |
X | X | X |
GeoQuerySet.kml() |
X | X | |
GeoQuerySet.length() |
X | X | X |
GeoQuerySet.mem_size() |
X | ||
GeoQuerySet.num_geom() |
X | X | X |
GeoQuerySet.num_points() |
X | X | X |
GeoQuerySet.perimeter() |
X | X | |
GeoQuerySet.point_on_surface() |
X | X | X |
GeoQuerySet.reverse_geom() |
X | X | |
GeoQuerySet.scale() |
X | X | |
GeoQuerySet.snap_to_grid() |
X | ||
GeoQuerySet.svg() |
X | X | |
GeoQuerySet.sym_difference() |
X | X | X |
GeoQuerySet.transform() |
X | X | X |
GeoQuerySet.translate() |
X | X | |
GeoQuerySet.union() |
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.
Aggregate | PostGIS | Oracle | SpatiaLite |
---|---|---|---|
Collect |
X | (from v3.0) | |
Extent |
X | X | (from v3.0) |
Extent3D |
X | ||
MakeLine |
X | ||
Union |
X | X | X |
Footnotes
[1] | See Open Geospatial Consortium, Inc., OpenGIS Simple Feature Specification For SQL, Document 99-049 (May 5, 1999), at Ch. 3.2.5, p. 3-11 (SQL Textual Representation of Geometry). |
[2] | See PostGIS EWKB, EWKT and Canonical Forms, PostGIS documentation at Ch. 4.1.2. |
[3] | See Howard Butler, Martin Daly, Allan Doyle, Tim Schaub, & Christopher Schmidt, The GeoJSON Format Specification, Revision 1.0 (June 16, 2008). |
[4] | See PostGIS documentation on ST_distance_sphere . |
[5] | See Creating Spatial Indexes in the MySQL Reference Manual:
|
[6] | Refer MySQL Spatial Limitations section for more details. |