2
This module contains the spatial lookup types, and the get_geo_where_clause()
6
from decimal import Decimal
7
from django.db import connection
8
from django.contrib.gis.measure import Distance
9
from django.contrib.gis.db.backend.postgis.management import postgis_version_tuple
10
from django.contrib.gis.db.backend.util import SpatialOperation, SpatialFunction
11
qn = connection.ops.quote_name
13
# Getting the PostGIS version information
14
POSTGIS_VERSION, MAJOR_VERSION, MINOR_VERSION1, MINOR_VERSION2 = postgis_version_tuple()
16
# The supported PostGIS versions.
17
# TODO: Confirm tests with PostGIS versions 1.1.x -- should work.
18
# Versions <= 1.0.x do not use GEOS C API, and will not be supported.
19
if MAJOR_VERSION != 1 or (MAJOR_VERSION == 1 and MINOR_VERSION1 < 1):
20
raise Exception('PostGIS version %s not supported.' % POSTGIS_VERSION)
22
# Versions of PostGIS >= 1.2.2 changed their naming convention to be
23
# 'SQL-MM-centric' to conform with the ISO standard. Practically, this
24
# means that 'ST_' prefixes geometry function names.
26
if MAJOR_VERSION >= 1:
27
if (MINOR_VERSION1 > 2 or
28
(MINOR_VERSION1 == 2 and MINOR_VERSION2 >= 2)):
29
GEOM_FUNC_PREFIX = 'ST_'
31
def get_func(func): return '%s%s' % (GEOM_FUNC_PREFIX, func)
33
# Custom selection not needed for PostGIS because GEOS geometries are
34
# instantiated directly from the HEXEWKB returned by default. If
35
# WKT is needed for some reason in the future, this value may be changed,
39
# Functions used by the GeoManager & GeoQuerySet
40
AREA = get_func('Area')
41
ASKML = get_func('AsKML')
42
ASGML = get_func('AsGML')
43
ASSVG = get_func('AsSVG')
44
CENTROID = get_func('Centroid')
45
DIFFERENCE = get_func('Difference')
46
DISTANCE = get_func('Distance')
47
DISTANCE_SPHERE = get_func('distance_sphere')
48
DISTANCE_SPHEROID = get_func('distance_spheroid')
49
ENVELOPE = get_func('Envelope')
50
EXTENT = get_func('extent')
51
GEOM_FROM_TEXT = get_func('GeomFromText')
52
GEOM_FROM_WKB = get_func('GeomFromWKB')
53
INTERSECTION = get_func('Intersection')
54
LENGTH = get_func('Length')
55
LENGTH_SPHEROID = get_func('length_spheroid')
56
MAKE_LINE = get_func('MakeLine')
57
MEM_SIZE = get_func('mem_size')
58
NUM_GEOM = get_func('NumGeometries')
59
NUM_POINTS = get_func('npoints')
60
PERIMETER = get_func('Perimeter')
61
POINT_ON_SURFACE = get_func('PointOnSurface')
62
SCALE = get_func('Scale')
63
SYM_DIFFERENCE = get_func('SymDifference')
64
TRANSFORM = get_func('Transform')
65
TRANSLATE = get_func('Translate')
67
# Special cases for union and KML methods.
68
if MINOR_VERSION1 < 3:
69
UNIONAGG = 'GeomUnion'
75
if MINOR_VERSION1 == 1:
78
raise NotImplementedError('PostGIS versions < 1.0 are not supported.')
80
#### Classes used in constructing PostGIS spatial SQL ####
81
class PostGISOperator(SpatialOperation):
82
"For PostGIS operators (e.g. `&&`, `~`)."
83
def __init__(self, operator):
84
super(PostGISOperator, self).__init__(operator=operator, beg_subst='%s %s %%s')
86
class PostGISFunction(SpatialFunction):
87
"For PostGIS function calls (e.g., `ST_Contains(table, geom)`)."
88
def __init__(self, function, **kwargs):
89
super(PostGISFunction, self).__init__(get_func(function), **kwargs)
91
class PostGISFunctionParam(PostGISFunction):
92
"For PostGIS functions that take another parameter (e.g. DWithin, Relate)."
93
def __init__(self, func):
94
super(PostGISFunctionParam, self).__init__(func, end_subst=', %%s)')
96
class PostGISDistance(PostGISFunction):
97
"For PostGIS distance operations."
98
dist_func = 'Distance'
99
def __init__(self, operator):
100
super(PostGISDistance, self).__init__(self.dist_func, end_subst=') %s %s',
101
operator=operator, result='%%s')
103
class PostGISSpheroidDistance(PostGISFunction):
104
"For PostGIS spherical distance operations (using the spheroid)."
105
dist_func = 'distance_spheroid'
106
def __init__(self, operator):
107
# An extra parameter in `end_subst` is needed for the spheroid string.
108
super(PostGISSpheroidDistance, self).__init__(self.dist_func,
109
beg_subst='%s(%s, %%s, %%s',
111
operator=operator, result='%%s')
113
class PostGISSphereDistance(PostGISFunction):
114
"For PostGIS spherical distance operations."
115
dist_func = 'distance_sphere'
116
def __init__(self, operator):
117
super(PostGISSphereDistance, self).__init__(self.dist_func, end_subst=') %s %s',
118
operator=operator, result='%%s')
120
class PostGISRelate(PostGISFunctionParam):
121
"For PostGIS Relate(<geom>, <pattern>) calls."
122
pattern_regex = re.compile(r'^[012TF\*]{9}$')
123
def __init__(self, pattern):
124
if not self.pattern_regex.match(pattern):
125
raise ValueError('Invalid intersection matrix pattern "%s".' % pattern)
126
super(PostGISRelate, self).__init__('Relate')
128
#### Lookup type mapping dictionaries of PostGIS operations. ####
130
# PostGIS-specific operators. The commented descriptions of these
131
# operators come from Section 6.2.2 of the official PostGIS documentation.
132
POSTGIS_OPERATORS = {
133
# The "&<" operator returns true if A's bounding box overlaps or
134
# is to the left of B's bounding box.
135
'overlaps_left' : PostGISOperator('&<'),
136
# The "&>" operator returns true if A's bounding box overlaps or
137
# is to the right of B's bounding box.
138
'overlaps_right' : PostGISOperator('&>'),
139
# The "<<" operator returns true if A's bounding box is strictly
140
# to the left of B's bounding box.
141
'left' : PostGISOperator('<<'),
142
# The ">>" operator returns true if A's bounding box is strictly
143
# to the right of B's bounding box.
144
'right' : PostGISOperator('>>'),
145
# The "&<|" operator returns true if A's bounding box overlaps or
146
# is below B's bounding box.
147
'overlaps_below' : PostGISOperator('&<|'),
148
# The "|&>" operator returns true if A's bounding box overlaps or
149
# is above B's bounding box.
150
'overlaps_above' : PostGISOperator('|&>'),
151
# The "<<|" operator returns true if A's bounding box is strictly
152
# below B's bounding box.
153
'strictly_below' : PostGISOperator('<<|'),
154
# The "|>>" operator returns true if A's bounding box is strictly
155
# above B's bounding box.
156
'strictly_above' : PostGISOperator('|>>'),
157
# The "~=" operator is the "same as" operator. It tests actual
158
# geometric equality of two features. So if A and B are the same feature,
159
# vertex-by-vertex, the operator returns true.
160
'same_as' : PostGISOperator('~='),
161
'exact' : PostGISOperator('~='),
162
# The "@" operator returns true if A's bounding box is completely contained
163
# by B's bounding box.
164
'contained' : PostGISOperator('@'),
165
# The "~" operator returns true if A's bounding box completely contains
166
# by B's bounding box.
167
'bbcontains' : PostGISOperator('~'),
168
# The "&&" operator returns true if A's bounding box overlaps
170
'bboverlaps' : PostGISOperator('&&'),
173
# For PostGIS >= 1.2.2 the following lookup types will do a bounding box query
174
# first before calling the more computationally expensive GEOS routines (called
175
# "inline index magic"):
176
# 'touches', 'crosses', 'contains', 'intersects', 'within', 'overlaps', and
178
POSTGIS_GEOMETRY_FUNCTIONS = {
179
'equals' : PostGISFunction('Equals'),
180
'disjoint' : PostGISFunction('Disjoint'),
181
'touches' : PostGISFunction('Touches'),
182
'crosses' : PostGISFunction('Crosses'),
183
'within' : PostGISFunction('Within'),
184
'overlaps' : PostGISFunction('Overlaps'),
185
'contains' : PostGISFunction('Contains'),
186
'intersects' : PostGISFunction('Intersects'),
187
'relate' : (PostGISRelate, basestring),
190
# Valid distance types and substitutions
191
dtypes = (Decimal, Distance, float, int, long)
192
def get_dist_ops(operator):
193
"Returns operations for both regular and spherical distances."
194
return (PostGISDistance(operator), PostGISSphereDistance(operator), PostGISSpheroidDistance(operator))
195
DISTANCE_FUNCTIONS = {
196
'distance_gt' : (get_dist_ops('>'), dtypes),
197
'distance_gte' : (get_dist_ops('>='), dtypes),
198
'distance_lt' : (get_dist_ops('<'), dtypes),
199
'distance_lte' : (get_dist_ops('<='), dtypes),
202
if GEOM_FUNC_PREFIX == 'ST_':
203
# The ST_DWithin, ST_CoveredBy, and ST_Covers routines become available in 1.2.2+
204
POSTGIS_GEOMETRY_FUNCTIONS.update(
205
{'coveredby' : PostGISFunction('CoveredBy'),
206
'covers' : PostGISFunction('Covers'),
208
DISTANCE_FUNCTIONS['dwithin'] = (PostGISFunctionParam('DWithin'), dtypes)
210
# Distance functions are a part of PostGIS geometry functions.
211
POSTGIS_GEOMETRY_FUNCTIONS.update(DISTANCE_FUNCTIONS)
213
# Any other lookup types that do not require a mapping.
214
MISC_TERMS = ['isnull']
216
# These are the PostGIS-customized QUERY_TERMS -- a list of the lookup types
217
# allowed for geographic queries.
218
POSTGIS_TERMS = POSTGIS_OPERATORS.keys() # Getting the operators first
219
POSTGIS_TERMS += POSTGIS_GEOMETRY_FUNCTIONS.keys() # Adding on the Geometry Functions
220
POSTGIS_TERMS += MISC_TERMS # Adding any other miscellaneous terms (e.g., 'isnull')
221
POSTGIS_TERMS = dict((term, None) for term in POSTGIS_TERMS) # Making a dictionary for fast lookups
223
# For checking tuple parameters -- not very pretty but gets job done.
224
def exactly_two(val): return val == 2
225
def two_to_three(val): return val >= 2 and val <=3
226
def num_params(lookup_type, val):
227
if lookup_type in DISTANCE_FUNCTIONS and lookup_type != 'dwithin': return two_to_three(val)
228
else: return exactly_two(val)
230
#### The `get_geo_where_clause` function for PostGIS. ####
231
def get_geo_where_clause(table_alias, name, lookup_type, geo_annot):
232
"Returns the SQL WHERE clause for use in PostGIS SQL construction."
233
# Getting the quoted field as `geo_col`.
234
geo_col = '%s.%s' % (qn(table_alias), qn(name))
235
if lookup_type in POSTGIS_OPERATORS:
236
# See if a PostGIS operator matches the lookup type.
237
return POSTGIS_OPERATORS[lookup_type].as_sql(geo_col)
238
elif lookup_type in POSTGIS_GEOMETRY_FUNCTIONS:
239
# See if a PostGIS geometry function matches the lookup type.
240
tmp = POSTGIS_GEOMETRY_FUNCTIONS[lookup_type]
242
# Lookup types that are tuples take tuple arguments, e.g., 'relate' and
244
if isinstance(tmp, tuple):
245
# First element of tuple is the PostGISOperation instance, and the
246
# second element is either the type or a tuple of acceptable types
247
# that may passed in as further parameters for the lookup type.
250
# Ensuring that a tuple _value_ was passed in from the user
251
if not isinstance(geo_annot.value, (tuple, list)):
252
raise TypeError('Tuple required for `%s` lookup type.' % lookup_type)
254
# Number of valid tuple parameters depends on the lookup type.
255
nparams = len(geo_annot.value)
256
if not num_params(lookup_type, nparams):
257
raise ValueError('Incorrect number of parameters given for `%s` lookup type.' % lookup_type)
259
# Ensuring the argument type matches what we expect.
260
if not isinstance(geo_annot.value[1], arg_type):
261
raise TypeError('Argument type should be %s, got %s instead.' % (arg_type, type(geo_annot.value[1])))
263
# For lookup type `relate`, the op instance is not yet created (has
264
# to be instantiated here to check the pattern parameter).
265
if lookup_type == 'relate':
266
op = op(geo_annot.value[1])
267
elif lookup_type in DISTANCE_FUNCTIONS and lookup_type != 'dwithin':
268
if geo_annot.geodetic:
269
# Geodetic distances are only availble from Points to PointFields.
270
if geo_annot.geom_type != 'POINT':
271
raise TypeError('PostGIS spherical operations are only valid on PointFields.')
272
if geo_annot.value[0].geom_typeid != 0:
273
raise TypeError('PostGIS geometry distance parameter is required to be of type Point.')
274
# Setting up the geodetic operation appropriately.
275
if nparams == 3 and geo_annot.value[2] == 'spheroid': op = op[2]
281
# Calling the `as_sql` function on the operation instance.
282
return op.as_sql(geo_col)
283
elif lookup_type == 'isnull':
284
# Handling 'isnull' lookup type
285
return "%s IS %sNULL" % (geo_col, (not geo_annot.value and 'NOT ' or ''))
287
raise TypeError("Got invalid lookup_type: %s" % repr(lookup_type))