/docs/ref/contrib/gis/db-api.txt
Plain Text | 349 lines | 276 code | 73 blank | 0 comment | 0 complexity | f15902b626c02380ce4a726e890bd33e MD5 | raw file
1.. _ref-gis-db-api: 2 3====================== 4GeoDjango Database API 5====================== 6 7.. module:: django.contrib.gis.db.models 8 :synopsis: GeoDjango's database API. 9 10.. _spatial-backends: 11 12Spatial Backends 13================ 14 15.. versionadded:: 1.2 16 17In Django 1.2, support for :doc:`multiple databases </topics/db/multi-db>` was 18introduced. In order to support multiple databases, GeoDjango has segregated 19its functionality into full-fledged spatial database backends: 20 21* :mod:`django.contrib.gis.db.backends.postgis` 22* :mod:`django.contrib.gis.db.backends.mysql` 23* :mod:`django.contrib.gis.db.backends.oracle` 24* :mod:`django.contrib.gis.db.backends.spatialite` 25 26Database Settings Backwards-Compatibility 27----------------------------------------- 28 29In :doc:`Django 1.2 </releases/1.2>`, the way 30to :ref:`specify databases <specifying-databases>` in your settings was changed. 31The old database settings format (e.g., the ``DATABASE_*`` settings) 32is backwards compatible with GeoDjango, and will automatically use the 33appropriate spatial backend as long as :mod:`django.contrib.gis` is in 34your :setting:`INSTALLED_APPS`. For example, if you have the following in 35your settings:: 36 37 DATABASE_ENGINE='postgresql_psycopg2' 38 39 ... 40 41 INSTALLED_APPS = ( 42 ... 43 'django.contrib.gis', 44 ... 45 ) 46 47Then, :mod:`django.contrib.gis.db.backends.postgis` is automatically used as your 48spatial backend. 49 50.. _mysql-spatial-limitations: 51 52MySQL Spatial Limitations 53------------------------- 54 55MySQL's spatial extensions only support bounding box operations 56(what MySQL calls minimum bounding rectangles, or MBR). Specifically, 57`MySQL does not conform to the OGC standard <http://dev.mysql.com/doc/refman/5.1/en/functions-that-test-spatial-relationships-between-geometries.html>`_: 58 59 Currently, MySQL does not implement these functions 60 [``Contains``, ``Crosses``, ``Disjoint``, ``Intersects``, ``Overlaps``, 61 ``Touches``, ``Within``] 62 according to the specification. Those that are implemented return 63 the same result as the corresponding MBR-based functions. 64 65In other words, while spatial lookups such as :lookup:`contains <gis-contains>` 66are available in GeoDjango when using MySQL, the results returned are really 67equivalent to what would be returned when using :lookup:`bbcontains` 68on a different spatial backend. 69 70.. warning:: 71 72 True spatial indexes (R-trees) are only supported with 73 MyISAM tables on MySQL. [#fnmysqlidx]_ In other words, when using 74 MySQL spatial extensions you have to choose between fast spatial 75 lookups and the integrity of your data -- MyISAM tables do 76 not support transactions or foreign key constraints. 77 78Creating and Saving Geographic Models 79===================================== 80Here is an example of how to create a geometry object (assuming the ``Zipcode`` 81model):: 82 83 >>> from zipcode.models import Zipcode 84 >>> z = Zipcode(code=77096, poly='POLYGON(( 10 10, 10 20, 20 20, 20 15, 10 10))') 85 >>> z.save() 86 87:class:`~django.contrib.gis.geos.GEOSGeometry` objects may also be used to save geometric models:: 88 89 >>> from django.contrib.gis.geos import GEOSGeometry 90 >>> poly = GEOSGeometry('POLYGON(( 10 10, 10 20, 20 20, 20 15, 10 10))') 91 >>> z = Zipcode(code=77096, poly=poly) 92 >>> z.save() 93 94Moreover, if the ``GEOSGeometry`` is in a different coordinate system (has a 95different SRID value) than that of the field, then it will be implicitly 96transformed into the SRID of the model's field, using the spatial database's 97transform procedure:: 98 99 >>> 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' 100 >>> z = Zipcode(code=78212, poly=poly_3084) 101 >>> z.save() 102 >>> from django.db import connection 103 >>> print connection.queries[-1]['sql'] # printing the last SQL statement executed (requires DEBUG=True) 104 INSERT INTO "geoapp_zipcode" ("code", "poly") VALUES (78212, ST_Transform(ST_GeomFromWKB('\\001 ... ', 3084), 4326)) 105 106Thus, geometry parameters may be passed in using the ``GEOSGeometry`` object, WKT 107(Well Known Text [#fnwkt]_), HEXEWKB (PostGIS specific -- a WKB geometry in 108hexadecimal [#fnewkb]_), and GeoJSON [#fngeojson]_ (requires GDAL). Essentially, 109if the input is not a ``GEOSGeometry`` object, the geometry field will attempt to 110create a ``GEOSGeometry`` instance from the input. 111 112For more information creating :class:`~django.contrib.gis.geos.GEOSGeometry` 113objects, refer to the :ref:`GEOS tutorial <geos-tutorial>`. 114 115.. _spatial-lookups-intro: 116 117Spatial Lookups 118=============== 119 120GeoDjango's lookup types may be used with any manager method like 121``filter()``, ``exclude()``, etc. However, the lookup types unique to 122GeoDjango are only available on geometry fields. 123Filters on 'normal' fields (e.g. :class:`~django.db.models.CharField`) 124may be chained with those on geographic fields. Thus, geographic queries 125take the following general form (assuming the ``Zipcode`` model used in the 126:ref:`ref-gis-model-api`):: 127 128 >>> qs = Zipcode.objects.filter(<field>__<lookup_type>=<parameter>) 129 >>> qs = Zipcode.objects.exclude(...) 130 131For example:: 132 133 >>> qs = Zipcode.objects.filter(poly__contains=pnt) 134 135In this case, ``poly`` is the geographic field, :lookup:`contains <gis-contains>` 136is the spatial lookup type, and ``pnt`` is the parameter (which may be a 137:class:`~django.contrib.gis.geos.GEOSGeometry` object or a string of 138GeoJSON , WKT, or HEXEWKB). 139 140A complete reference can be found in the :ref:`spatial lookup reference 141<spatial-lookups>`. 142 143.. note:: 144 145 GeoDjango constructs spatial SQL with the :class:`GeoQuerySet`, a 146 subclass of :class:`~django.db.models.QuerySet`. The 147 :class:`GeoManager` instance attached to your model is what 148 enables use of :class:`GeoQuerySet`. 149 150.. _distance-queries: 151 152Distance Queries 153================ 154 155Introduction 156------------ 157Distance calculations with spatial data is tricky because, unfortunately, 158the Earth is not flat. Some distance queries with fields in a geographic 159coordinate system may have to be expressed differently because of 160limitations in PostGIS. Please see the :ref:`selecting-an-srid` section 161in the :ref:`ref-gis-model-api` documentation for more details. 162 163.. _distance-lookups-intro: 164 165Distance Lookups 166---------------- 167*Availability*: PostGIS, Oracle, SpatiaLite 168 169The following distance lookups are available: 170 171* :lookup:`distance_lt` 172* :lookup:`distance_lte` 173* :lookup:`distance_gt` 174* :lookup:`distance_gte` 175* :lookup:`dwithin` 176 177.. note:: 178 179 For *measuring*, rather than querying on distances, use the 180 :meth:`GeoQuerySet.distance` method. 181 182Distance lookups take a tuple parameter comprising: 183 184#. A geometry to base calculations from; and 185#. A number or :class:`~django.contrib.gis.measure.Distance` object containing the distance. 186 187If a :class:`~django.contrib.gis.measure.Distance` object is used, 188it may be expressed in any units (the SQL generated will use units 189converted to those of the field); otherwise, numeric parameters are assumed 190to be in the units of the field. 191 192.. note:: 193 194 For users of PostGIS 1.4 and below, the routine ``ST_Distance_Sphere`` 195 is used by default for calculating distances on geographic coordinate systems 196 (e.g., WGS84) -- which may only be called with point geometries [#fndistsphere14]_. 197 Thus, geographic distance lookups on traditional PostGIS geometry columns are 198 only allowed on :class:`PointField` model fields using a point for the 199 geometry parameter. 200 201.. note:: 202 203 In PostGIS 1.5, ``ST_Distance_Sphere`` does *not* limit the geometry types 204 geographic distance queries are performed with. [#fndistsphere15]_ However, 205 these queries may take a long time, as great-circle distances must be 206 calculated on the fly for *every* row in the query. This is because the 207 spatial index on traditional geometry fields cannot be used. 208 209 For much better performance on WGS84 distance queries, consider using 210 :ref:`geography columns <geography-type>` in your database instead because 211 they are able to use their spatial index in distance queries. 212 You can tell GeoDjango to use a geography column by setting ``geography=True`` 213 in your field definition. 214 215For example, let's say we have a ``SouthTexasCity`` model (from the 216`GeoDjango distance tests`__ ) on a *projected* coordinate system valid for cities 217in southern Texas:: 218 219 from django.contrib.gis.db import models 220 221 class SouthTexasCity(models.Model): 222 name = models.CharField(max_length=30) 223 # A projected coordinate system (only valid for South Texas!) 224 # is used, units are in meters. 225 point = models.PointField(srid=32140) 226 objects = models.GeoManager() 227 228Then distance queries may be performed as follows:: 229 230 >>> from django.contrib.gis.geos import * 231 >>> from django.contrib.gis.measure import D # ``D`` is a shortcut for ``Distance`` 232 >>> from geoapp import SouthTexasCity 233 # Distances will be calculated from this point, which does not have to be projected. 234 >>> pnt = fromstr('POINT(-96.876369 29.905320)', srid=4326) 235 # If numeric parameter, units of field (meters in this case) are assumed. 236 >>> qs = SouthTexasCity.objects.filter(point__distance_lte=(pnt, 7000)) 237 # Find all Cities within 7 km, > 20 miles away, and > 100 chains away (an obscure unit) 238 >>> qs = SouthTexasCity.objects.filter(point__distance_lte=(pnt, D(km=7))) 239 >>> qs = SouthTexasCity.objects.filter(point__distance_gte=(pnt, D(mi=20))) 240 >>> qs = SouthTexasCity.objects.filter(point__distance_gte=(pnt, D(chain=100))) 241 242__ http://code.djangoproject.com/browser/django/trunk/django/contrib/gis/tests/distapp/models.py 243 244.. _compatibility-table: 245 246Compatibility Tables 247==================== 248 249.. _spatial-lookup-compatibility: 250 251Spatial Lookups 252--------------- 253 254The following table provides a summary of what spatial lookups are available 255for each spatial database backend. 256 257================================= ========= ======== ============ ========== 258Lookup Type PostGIS Oracle MySQL [#]_ SpatiaLite 259================================= ========= ======== ============ ========== 260:lookup:`bbcontains` X X X 261:lookup:`bboverlaps` X X X 262:lookup:`contained` X X X 263:lookup:`contains <gis-contains>` X X X X 264:lookup:`contains_properly` X 265:lookup:`coveredby` X X 266:lookup:`covers` X X 267:lookup:`crosses` X X 268:lookup:`disjoint` X X X X 269:lookup:`distance_gt` X X X 270:lookup:`distance_gte` X X X 271:lookup:`distance_lt` X X X 272:lookup:`distance_lte` X X X 273:lookup:`dwithin` X X 274:lookup:`equals` X X X X 275:lookup:`exact` X X X X 276:lookup:`intersects` X X X X 277:lookup:`overlaps` X X X X 278:lookup:`relate` X X X 279:lookup:`same_as` X X X X 280:lookup:`touches` X X X X 281:lookup:`within` X X X X 282:lookup:`left` X 283:lookup:`right` X 284:lookup:`overlaps_left` X 285:lookup:`overlaps_right` X 286:lookup:`overlaps_above` X 287:lookup:`overlaps_below` X 288:lookup:`strictly_above` X 289:lookup:`strictly_below` X 290================================= ========= ======== ============ ========== 291 292.. _geoqueryset-method-compatibility: 293 294``GeoQuerySet`` Methods 295----------------------- 296The following table provides a summary of what :class:`GeoQuerySet` methods 297are available on each spatial backend. Please note that MySQL does not 298support any of these methods, and is thus excluded from the table. 299 300==================================== ======= ====== ========== 301Method PostGIS Oracle SpatiaLite 302==================================== ======= ====== ========== 303:meth:`GeoQuerySet.area` X X X 304:meth:`GeoQuerySet.centroid` X X X 305:meth:`GeoQuerySet.collect` X 306:meth:`GeoQuerySet.difference` X X X 307:meth:`GeoQuerySet.distance` X X X 308:meth:`GeoQuerySet.envelope` X X 309:meth:`GeoQuerySet.extent` X X 310:meth:`GeoQuerySet.extent3d` X 311:meth:`GeoQuerySet.force_rhr` X 312:meth:`GeoQuerySet.geohash` X 313:meth:`GeoQuerySet.geojson` X 314:meth:`GeoQuerySet.gml` X X 315:meth:`GeoQuerySet.intersection` X X X 316:meth:`GeoQuerySet.kml` X 317:meth:`GeoQuerySet.length` X X X 318:meth:`GeoQuerySet.make_line` X 319:meth:`GeoQuerySet.mem_size` X 320:meth:`GeoQuerySet.num_geom` X X X 321:meth:`GeoQuerySet.num_points` X X X 322:meth:`GeoQuerySet.perimeter` X X 323:meth:`GeoQuerySet.point_on_surface` X X X 324:meth:`GeoQuerySet.reverse_geom` X X 325:meth:`GeoQuerySet.scale` X X 326:meth:`GeoQuerySet.snap_to_grid` X 327:meth:`GeoQuerySet.svg` X X 328:meth:`GeoQuerySet.sym_difference` X X X 329:meth:`GeoQuerySet.transform` X X X 330:meth:`GeoQuerySet.translate` X X 331:meth:`GeoQuerySet.union` X X X 332:meth:`GeoQuerySet.unionagg` X X X 333==================================== ======= ====== ========== 334 335.. rubric:: Footnotes 336.. [#fnwkt] *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). 337.. [#fnewkb] *See* `PostGIS EWKB, EWKT and Canonical Forms <http://postgis.refractions.net/documentation/manual-1.5/ch04.html#EWKB_EWKT>`_, PostGIS documentation at Ch. 4.1.2. 338.. [#fngeojson] *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). 339.. [#fndistsphere14] *See* `PostGIS 1.4 documentation <http://postgis.refractions.net/documentation/manual-1.4/ST_Distance_Sphere.html>`_ on ``ST_distance_sphere``. 340.. [#fndistsphere15] *See* `PostGIS 1.5 documentation <http://postgis.refractions.net/documentation/manual-1.5/ST_Distance_Sphere.html>`_ on ``ST_distance_sphere``. 341.. [#fnmysqlidx] *See* `Creating Spatial Indexes <http://dev.mysql.com/doc/refman/5.1/en/creating-spatial-indexes.html>`_ 342 in the MySQL 5.1 Reference Manual: 343 344 For MyISAM tables, ``SPATIAL INDEX`` creates an R-tree index. For storage 345 engines that support nonspatial indexing of spatial columns, the engine 346 creates a B-tree index. A B-tree index on spatial values will be useful 347 for exact-value lookups, but not for range scans. 348 349.. [#] Refer :ref:`mysql-spatial-limitations` section for more details.