|
1 .. _ref-gis-db-api: |
|
2 |
|
3 ====================== |
|
4 GeoDjango Database API |
|
5 ====================== |
|
6 |
|
7 .. module:: django.contrib.gis.db.models |
|
8 :synopsis: GeoDjango's database API. |
|
9 |
|
10 .. _spatial-backends: |
|
11 |
|
12 Spatial Backends |
|
13 ================ |
|
14 |
|
15 .. versionadded:: 1.2 |
|
16 |
|
17 In Django 1.2, support for :doc:`multiple databases </topics/db/multi-db>` was |
|
18 introduced. In order to support multiple databases, GeoDjango has segregated |
|
19 its 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 |
|
26 Database Settings Backwards-Compatibility |
|
27 ----------------------------------------- |
|
28 |
|
29 In :doc:`Django 1.2 </releases/1.2>`, the way |
|
30 to :ref:`specify databases <specifying-databases>` in your settings was changed. |
|
31 The old database settings format (e.g., the ``DATABASE_*`` settings) |
|
32 is backwards compatible with GeoDjango, and will automatically use the |
|
33 appropriate spatial backend as long as :mod:`django.contrib.gis` is in |
|
34 your :setting:`INSTALLED_APPS`. For example, if you have the following in |
|
35 your settings:: |
|
36 |
|
37 DATABASE_ENGINE='postgresql_psycopg2' |
|
38 |
|
39 ... |
|
40 |
|
41 INSTALLED_APPS = ( |
|
42 ... |
|
43 'django.contrib.gis', |
|
44 ... |
|
45 ) |
|
46 |
|
47 Then, :mod:`django.contrib.gis.db.backends.postgis` is automatically used as your |
|
48 spatial backend. |
|
49 |
|
50 .. _mysql-spatial-limitations: |
|
51 |
|
52 MySQL Spatial Limitations |
|
53 ------------------------- |
|
54 |
|
55 MySQL'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 |
|
65 In other words, while spatial lookups such as :lookup:`contains <gis-contains>` |
|
66 are available in GeoDjango when using MySQL, the results returned are really |
|
67 equivalent to what would be returned when using :lookup:`bbcontains` |
|
68 on 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 |
|
78 Creating and Saving Geographic Models |
|
79 ===================================== |
|
80 Here is an example of how to create a geometry object (assuming the ``Zipcode`` |
|
81 model):: |
|
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 |
|
94 Moreover, if the ``GEOSGeometry`` is in a different coordinate system (has a |
|
95 different SRID value) than that of the field, then it will be implicitly |
|
96 transformed into the SRID of the model's field, using the spatial database's |
|
97 transform 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 |
|
106 Thus, geometry parameters may be passed in using the ``GEOSGeometry`` object, WKT |
|
107 (Well Known Text [#fnwkt]_), HEXEWKB (PostGIS specific -- a WKB geometry in |
|
108 hexadecimal [#fnewkb]_), and GeoJSON [#fngeojson]_ (requires GDAL). Essentially, |
|
109 if the input is not a ``GEOSGeometry`` object, the geometry field will attempt to |
|
110 create a ``GEOSGeometry`` instance from the input. |
|
111 |
|
112 For more information creating :class:`~django.contrib.gis.geos.GEOSGeometry` |
|
113 objects, refer to the :ref:`GEOS tutorial <geos-tutorial>`. |
|
114 |
|
115 .. _spatial-lookups-intro: |
|
116 |
|
117 Spatial Lookups |
|
118 =============== |
|
119 |
|
120 GeoDjango's lookup types may be used with any manager method like |
|
121 ``filter()``, ``exclude()``, etc. However, the lookup types unique to |
|
122 GeoDjango are only available on geometry fields. |
|
123 Filters on 'normal' fields (e.g. :class:`~django.db.models.CharField`) |
|
124 may be chained with those on geographic fields. Thus, geographic queries |
|
125 take 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 |
|
131 For example:: |
|
132 |
|
133 >>> qs = Zipcode.objects.filter(poly__contains=pnt) |
|
134 |
|
135 In this case, ``poly`` is the geographic field, :lookup:`contains <gis-contains>` |
|
136 is 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 |
|
138 GeoJSON , WKT, or HEXEWKB). |
|
139 |
|
140 A 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 |
|
152 Distance Queries |
|
153 ================ |
|
154 |
|
155 Introduction |
|
156 ------------ |
|
157 Distance calculations with spatial data is tricky because, unfortunately, |
|
158 the Earth is not flat. Some distance queries with fields in a geographic |
|
159 coordinate system may have to be expressed differently because of |
|
160 limitations in PostGIS. Please see the :ref:`selecting-an-srid` section |
|
161 in the :ref:`ref-gis-model-api` documentation for more details. |
|
162 |
|
163 .. _distance-lookups-intro: |
|
164 |
|
165 Distance Lookups |
|
166 ---------------- |
|
167 *Availability*: PostGIS, Oracle, SpatiaLite |
|
168 |
|
169 The 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 |
|
182 Distance 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 |
|
187 If a :class:`~django.contrib.gis.measure.Distance` object is used, |
|
188 it may be expressed in any units (the SQL generated will use units |
|
189 converted to those of the field); otherwise, numeric parameters are assumed |
|
190 to 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 |
|
215 For example, let's say we have a ``SouthTexasCity`` model (from the |
|
216 `GeoDjango distance tests`__ ) on a *projected* coordinate system valid for cities |
|
217 in 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 |
|
228 Then 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 |
|
246 Compatibility Tables |
|
247 ==================== |
|
248 |
|
249 .. _spatial-lookup-compatibility: |
|
250 |
|
251 Spatial Lookups |
|
252 --------------- |
|
253 |
|
254 The following table provides a summary of what spatial lookups are available |
|
255 for each spatial database backend. |
|
256 |
|
257 ================================= ========= ======== ============ ========== |
|
258 Lookup 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 ----------------------- |
|
296 The following table provides a summary of what :class:`GeoQuerySet` methods |
|
297 are available on each spatial backend. Please note that MySQL does not |
|
298 support any of these methods, and is thus excluded from the table. |
|
299 |
|
300 ==================================== ======= ====== ========== |
|
301 Method 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. |