--- title: "GeoDjango Database API" version: 1.8 locale: en source: https://docs.djangoproject.com/en/1.8/ref/contrib/gis/db-api/ canonical: https://djangodocs.dev/en/1.8/ref/contrib/gis/db-api/ --- # 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](http://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions.html): > 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`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-gis-contains) are available in GeoDjango when using MySQL, the results returned are really equivalent to what would be returned when using [`bbcontains`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-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`](/en/1.8/ref/contrib/gis/geos/#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 [^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`](/en/1.8/ref/contrib/gis/geos/#django.contrib.gis.geos.GEOSGeometry) objects, refer to the [GEOS tutorial](/en/1.8/ref/contrib/gis/geos/#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`](/en/1.8/ref/models/fields/#django.db.models.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](/en/1.8/ref/contrib/gis/model-api/)): ``` >>> qs = Zipcode.objects.filter(__=) >>> qs = Zipcode.objects.exclude(...) ``` For example: ``` >>> qs = Zipcode.objects.filter(poly__contains=pnt) ``` In this case, `poly` is the geographic field, [`contains`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-gis-contains) is the spatial lookup type, and `pnt` is the parameter (which may be a [`GEOSGeometry`](/en/1.8/ref/contrib/gis/geos/#django.contrib.gis.geos.GEOSGeometry) object or a string of GeoJSON , WKT, or HEXEWKB). A complete reference can be found in the [spatial lookup reference](/en/1.8/ref/contrib/gis/geoquerysets/#spatial-lookups). > **Note** > > GeoDjango constructs spatial SQL with the [`GeoQuerySet`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet), a > subclass of [`QuerySet`](/en/1.8/ref/models/querysets/#django.db.models.query.QuerySet). The > [`GeoManager`](/en/1.8/ref/contrib/gis/model-api/#django.contrib.gis.db.models.GeoManager) instance attached to your model is what > enables use of [`GeoQuerySet`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.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](/en/1.8/ref/contrib/gis/model-api/#selecting-an-srid) section in the [GeoDjango Model API](/en/1.8/ref/contrib/gis/model-api/) documentation for more details. ### Distance Lookups *Availability*: PostGIS, Oracle, SpatiaLite The following distance lookups are available: - [`distance_lt`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-distance_lt) - [`distance_lte`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-distance_lte) - [`distance_gt`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-distance_gt) - [`distance_gte`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-distance_gte) - [`dwithin`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-dwithin) > **Note** > > For *measuring*, rather than querying on distances, use the > [`GeoQuerySet.distance()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.distance) method. Distance lookups take a tuple parameter comprising: 1. A geometry to base calculations from; and 2. A number or [`Distance`](/en/1.8/ref/contrib/gis/measure/#django.contrib.gis.measure.Distance) object containing the distance. If a [`Distance`](/en/1.8/ref/contrib/gis/measure/#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. [^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](/en/1.8/ref/contrib/gis/model-api/#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 [GeoDjango distance tests](https://github.com/django/django/blob/master/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) 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`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-bbcontains) | X | | X | X | | [`bboverlaps`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-bboverlaps) | X | | X | X | | [`contained`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-contained) | X | | X | X | | [`contains`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-gis-contains) | X | X | X | X | | [`contains_properly`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-contains_properly) | X | | | | | [`coveredby`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-coveredby) | X | X | | | | [`covers`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-covers) | X | X | | | | [`crosses`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-crosses) | X | | | X | | [`disjoint`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-disjoint) | X | X | X | X | | [`distance_gt`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-distance_gt) | X | X | | X | | [`distance_gte`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-distance_gte) | X | X | | X | | [`distance_lt`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-distance_lt) | X | X | | X | | [`distance_lte`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-distance_lte) | X | X | | X | | [`dwithin`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-dwithin) | X | X | | | | [`equals`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-equals) | X | X | X | X | | [`exact`](/en/1.8/ref/models/querysets/#std-fieldlookup-exact) | X | X | X | X | | [`intersects`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-intersects) | X | X | X | X | | [`overlaps`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-overlaps) | X | X | X | X | | [`relate`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-relate) | X | X | | X | | [`same_as`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-same_as) | X | X | X | X | | [`touches`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-touches) | X | X | X | X | | [`within`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-within) | X | X | X | X | | [`left`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-left) | X | | | | | [`right`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-right) | X | | | | | [`overlaps_left`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-overlaps_left) | X | | | | | [`overlaps_right`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-overlaps_right) | X | | | | | [`overlaps_above`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-overlaps_above) | X | | | | | [`overlaps_below`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-overlaps_below) | X | | | | | [`strictly_above`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-strictly_above) | X | | | | | [`strictly_below`](/en/1.8/ref/contrib/gis/geoquerysets/#std-fieldlookup-strictly_below) | X | | | | ### `GeoQuerySet` Methods The following table provides a summary of what [`GeoQuerySet`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.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()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.area) | X | X | X | | [`GeoQuerySet.centroid()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.centroid) | X | X | X | | [`GeoQuerySet.difference()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.difference) | X | X | X | | [`GeoQuerySet.distance()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.distance) | X | X | X | | [`GeoQuerySet.envelope()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.envelope) | X | | X | | [`GeoQuerySet.force_rhr()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.force_rhr) | X | | | | [`GeoQuerySet.geohash()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.geohash) | X | | | | [`GeoQuerySet.geojson()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.geojson) | X | | X | | [`GeoQuerySet.gml()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.gml) | X | X | X | | [`GeoQuerySet.intersection()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.intersection) | X | X | X | | [`GeoQuerySet.kml()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.kml) | X | | X | | [`GeoQuerySet.length()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.length) | X | X | X | | [`GeoQuerySet.mem_size()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.mem_size) | X | | | | [`GeoQuerySet.num_geom()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.num_geom) | X | X | X | | [`GeoQuerySet.num_points()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.num_points) | X | X | X | | [`GeoQuerySet.perimeter()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.perimeter) | X | X | | | [`GeoQuerySet.point_on_surface()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.point_on_surface) | X | X | X | | [`GeoQuerySet.reverse_geom()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.reverse_geom) | X | X | | | [`GeoQuerySet.scale()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.scale) | X | | X | | [`GeoQuerySet.snap_to_grid()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.snap_to_grid) | X | | | | [`GeoQuerySet.svg()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.svg) | X | | X | | [`GeoQuerySet.sym_difference()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.sym_difference) | X | X | X | | [`GeoQuerySet.transform()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.transform) | X | X | X | | [`GeoQuerySet.translate()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.GeoQuerySet.translate) | X | | X | | [`GeoQuerySet.union()`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.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`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.Collect) | X | | (from v3.0) | | [`Extent`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.Extent) | X | X | (from v3.0) | | [`Extent3D`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.Extent3D) | X | | | | [`MakeLine`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.MakeLine) | X | | | | [`Union`](/en/1.8/ref/contrib/gis/geoquerysets/#django.contrib.gis.db.models.Union) | X | X | X | **Footnotes** [^1]: *See* Open Geospatial Consortium, Inc., [OpenGIS Simple Feature Specification For SQL](http://www.opengis.org/docs/99-049.pdf), 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](http://postgis.net/docs/manual-2.1/using_postgis_dbmanagement.html#EWKB_EWKT), PostGIS documentation at Ch. 4.1.2. [^3]: *See* Howard Butler, Martin Daly, Allan Doyle, Tim Schaub, & Christopher Schmidt, [The GeoJSON Format Specification](http://geojson.org/geojson-spec.html), Revision 1.0 (June 16, 2008). [^4]: *See* [PostGIS documentation](http://postgis.net/docs/manual-2.1/ST_Distance_Sphere.html) on `ST_distance_sphere`. [^5]: *See* [Creating Spatial Indexes](http://dev.mysql.com/doc/refman/5.6/en/creating-spatial-indexes.html) in the MySQL Reference Manual: > For MyISAM tables, `SPATIAL INDEX` creates an R-tree index. For storage > engines that support nonspatial indexing of spatial columns, the engine > creates a B-tree index. A B-tree index on spatial values will be useful > for exact-value lookups, but not for range scans. [^6]: Refer [MySQL Spatial Limitations](#mysql-spatial-limitations) section for more details.