PageRenderTime 130ms CodeModel.GetById 40ms app.highlight 64ms RepoModel.GetById 16ms app.codeStats 1ms

/django/contrib/gis/db/backends/postgis/operations.py

https://code.google.com/p/mango-py/
Python | 589 lines | 421 code | 52 blank | 116 comment | 57 complexity | 49161eca7db8bde05a032b8e8c9a32b9 MD5 | raw file
  1import re
  2from decimal import Decimal
  3
  4from django.conf import settings
  5from django.contrib.gis.db.backends.base import BaseSpatialOperations
  6from django.contrib.gis.db.backends.util import SpatialOperation, SpatialFunction
  7from django.contrib.gis.db.backends.postgis.adapter import PostGISAdapter
  8from django.contrib.gis.geometry.backend import Geometry
  9from django.contrib.gis.measure import Distance
 10from django.core.exceptions import ImproperlyConfigured
 11from django.db.backends.postgresql_psycopg2.base import DatabaseOperations
 12from django.db.utils import DatabaseError
 13
 14#### Classes used in constructing PostGIS spatial SQL ####
 15class PostGISOperator(SpatialOperation):
 16    "For PostGIS operators (e.g. `&&`, `~`)."
 17    def __init__(self, operator):
 18        super(PostGISOperator, self).__init__(operator=operator)
 19
 20class PostGISFunction(SpatialFunction):
 21    "For PostGIS function calls (e.g., `ST_Contains(table, geom)`)."
 22    def __init__(self, prefix, function, **kwargs):
 23        super(PostGISFunction, self).__init__(prefix + function, **kwargs)
 24
 25class PostGISFunctionParam(PostGISFunction):
 26    "For PostGIS functions that take another parameter (e.g. DWithin, Relate)."
 27    sql_template = '%(function)s(%(geo_col)s, %(geometry)s, %%s)'
 28
 29class PostGISDistance(PostGISFunction):
 30    "For PostGIS distance operations."
 31    dist_func = 'Distance'
 32    sql_template = '%(function)s(%(geo_col)s, %(geometry)s) %(operator)s %%s'
 33
 34    def __init__(self, prefix, operator):
 35        super(PostGISDistance, self).__init__(prefix, self.dist_func,
 36                                              operator=operator)
 37
 38class PostGISSpheroidDistance(PostGISFunction):
 39    "For PostGIS spherical distance operations (using the spheroid)."
 40    dist_func = 'distance_spheroid'
 41    sql_template = '%(function)s(%(geo_col)s, %(geometry)s, %%s) %(operator)s %%s'
 42    def __init__(self, prefix, operator):
 43        # An extra parameter in `end_subst` is needed for the spheroid string.
 44        super(PostGISSpheroidDistance, self).__init__(prefix, self.dist_func,
 45                                                      operator=operator)
 46
 47class PostGISSphereDistance(PostGISDistance):
 48    "For PostGIS spherical distance operations."
 49    dist_func = 'distance_sphere'
 50
 51class PostGISRelate(PostGISFunctionParam):
 52    "For PostGIS Relate(<geom>, <pattern>) calls."
 53    pattern_regex = re.compile(r'^[012TF\*]{9}$')
 54    def __init__(self, prefix, pattern):
 55        if not self.pattern_regex.match(pattern):
 56            raise ValueError('Invalid intersection matrix pattern "%s".' % pattern)
 57        super(PostGISRelate, self).__init__(prefix, 'Relate')
 58
 59
 60class PostGISOperations(DatabaseOperations, BaseSpatialOperations):
 61    compiler_module = 'django.contrib.gis.db.models.sql.compiler'
 62    name = 'postgis'
 63    postgis = True
 64    version_regex = re.compile(r'^(?P<major>\d)\.(?P<minor1>\d)\.(?P<minor2>\d+)')
 65    valid_aggregates = dict([(k, None) for k in
 66                             ('Collect', 'Extent', 'Extent3D', 'MakeLine', 'Union')])
 67
 68    Adapter = PostGISAdapter
 69    Adaptor = Adapter # Backwards-compatibility alias.
 70
 71    def __init__(self, connection):
 72        super(PostGISOperations, self).__init__(connection)
 73
 74        # Trying to get the PostGIS version because the function
 75        # signatures will depend on the version used.  The cost
 76        # here is a database query to determine the version, which
 77        # can be mitigated by setting `POSTGIS_VERSION` with a 3-tuple
 78        # comprising user-supplied values for the major, minor, and
 79        # subminor revision of PostGIS.
 80        try:
 81            if hasattr(settings, 'POSTGIS_VERSION'):
 82                vtup = settings.POSTGIS_VERSION
 83                if len(vtup) == 3:
 84                    # The user-supplied PostGIS version.
 85                    version = vtup
 86                else:
 87                    # This was the old documented way, but it's stupid to
 88                    # include the string.
 89                    version = vtup[1:4]
 90            else:
 91                vtup = self.postgis_version_tuple()
 92                version = vtup[1:]
 93
 94            # Getting the prefix -- even though we don't officially support
 95            # PostGIS 1.2 anymore, keeping it anyway in case a prefix change
 96            # for something else is necessary.
 97            if version >= (1, 2, 2):
 98                prefix = 'ST_'
 99            else:
100                prefix = ''
101
102            self.geom_func_prefix = prefix
103            self.spatial_version = version
104        except DatabaseError:
105            raise ImproperlyConfigured('Cannot determine PostGIS version for database "%s". '
106                                       'GeoDjango requires at least PostGIS version 1.3. '
107                                       'Was the database created from a spatial database '
108                                       'template?' % self.connection.settings_dict['NAME']
109                                       )
110        except Exception, e:
111            # TODO: Raise helpful exceptions as they become known.
112            raise
113
114        # PostGIS-specific operators. The commented descriptions of these
115        # operators come from Section 7.6 of the PostGIS 1.4 documentation.
116        self.geometry_operators = {
117            # The "&<" operator returns true if A's bounding box overlaps or
118            # is to the left of B's bounding box.
119            'overlaps_left' : PostGISOperator('&<'),
120            # The "&>" operator returns true if A's bounding box overlaps or
121            # is to the right of B's bounding box.
122            'overlaps_right' : PostGISOperator('&>'),
123            # The "<<" operator returns true if A's bounding box is strictly
124            # to the left of B's bounding box.
125            'left' : PostGISOperator('<<'),
126            # The ">>" operator returns true if A's bounding box is strictly
127            # to the right of B's bounding box.
128            'right' : PostGISOperator('>>'),
129            # The "&<|" operator returns true if A's bounding box overlaps or
130            # is below B's bounding box.
131            'overlaps_below' : PostGISOperator('&<|'),
132            # The "|&>" operator returns true if A's bounding box overlaps or
133            # is above B's bounding box.
134            'overlaps_above' : PostGISOperator('|&>'),
135            # The "<<|" operator returns true if A's bounding box is strictly
136            # below B's bounding box.
137            'strictly_below' : PostGISOperator('<<|'),
138            # The "|>>" operator returns true if A's bounding box is strictly
139            # above B's bounding box.
140            'strictly_above' : PostGISOperator('|>>'),
141            # The "~=" operator is the "same as" operator. It tests actual
142            # geometric equality of two features. So if A and B are the same feature,
143            # vertex-by-vertex, the operator returns true.
144            'same_as' : PostGISOperator('~='),
145            'exact' : PostGISOperator('~='),
146            # The "@" operator returns true if A's bounding box is completely contained
147            # by B's bounding box.
148            'contained' : PostGISOperator('@'),
149            # The "~" operator returns true if A's bounding box completely contains
150            #  by B's bounding box.
151            'bbcontains' : PostGISOperator('~'),
152            # The "&&" operator returns true if A's bounding box overlaps
153            # B's bounding box.
154            'bboverlaps' : PostGISOperator('&&'),
155            }
156
157        self.geometry_functions = {
158            'equals' : PostGISFunction(prefix, 'Equals'),
159            'disjoint' : PostGISFunction(prefix, 'Disjoint'),
160            'touches' : PostGISFunction(prefix, 'Touches'),
161            'crosses' : PostGISFunction(prefix, 'Crosses'),
162            'within' : PostGISFunction(prefix, 'Within'),
163            'overlaps' : PostGISFunction(prefix, 'Overlaps'),
164            'contains' : PostGISFunction(prefix, 'Contains'),
165            'intersects' : PostGISFunction(prefix, 'Intersects'),
166            'relate' : (PostGISRelate, basestring),
167            }
168
169        # Valid distance types and substitutions
170        dtypes = (Decimal, Distance, float, int, long)
171        def get_dist_ops(operator):
172            "Returns operations for both regular and spherical distances."
173            return {'cartesian' : PostGISDistance(prefix, operator),
174                    'sphere' : PostGISSphereDistance(prefix, operator),
175                    'spheroid' : PostGISSpheroidDistance(prefix, operator),
176                    }
177        self.distance_functions = {
178            'distance_gt' : (get_dist_ops('>'), dtypes),
179            'distance_gte' : (get_dist_ops('>='), dtypes),
180            'distance_lt' : (get_dist_ops('<'), dtypes),
181            'distance_lte' : (get_dist_ops('<='), dtypes),
182            }
183
184        # Versions 1.2.2+ have KML serialization support.
185        if version < (1, 2, 2):
186            ASKML = False
187        else:
188            ASKML = 'ST_AsKML'
189            self.geometry_functions.update(
190                {'coveredby' : PostGISFunction(prefix, 'CoveredBy'),
191                 'covers' : PostGISFunction(prefix, 'Covers'),
192                 })
193            self.distance_functions['dwithin'] = (PostGISFunctionParam(prefix, 'DWithin'), dtypes)
194
195        # Adding the distance functions to the geometries lookup.
196        self.geometry_functions.update(self.distance_functions)
197
198        # The union aggregate and topology operation use the same signature
199        # in versions 1.3+.
200        if version < (1, 3, 0):
201            UNIONAGG = 'GeomUnion'
202            UNION = 'Union'
203            MAKELINE = False
204        else:
205            UNIONAGG = 'ST_Union'
206            UNION = 'ST_Union'
207            MAKELINE = 'ST_MakeLine'
208
209        # Only PostGIS versions 1.3.4+ have GeoJSON serialization support.
210        if version < (1, 3, 4):
211            GEOJSON = False
212        else:
213            GEOJSON = prefix + 'AsGeoJson'
214
215        # ST_ContainsProperly ST_MakeLine, and ST_GeoHash added in 1.4.
216        if version >= (1, 4, 0):
217            GEOHASH = 'ST_GeoHash'
218            BOUNDINGCIRCLE = 'ST_MinimumBoundingCircle'
219            self.geometry_functions['contains_properly'] = PostGISFunction(prefix, 'ContainsProperly')
220        else:
221            GEOHASH, BOUNDINGCIRCLE = False, False
222
223        # Geography type support added in 1.5.
224        if version >= (1, 5, 0):
225            self.geography = True
226            # Only a subset of the operators and functions are available
227            # for the geography type.
228            self.geography_functions = self.distance_functions.copy()
229            self.geography_functions.update({
230                    'coveredby' : self.geometry_functions['coveredby'],
231                    'covers' : self.geometry_functions['covers'],
232                    'intersects' : self.geometry_functions['intersects'],
233                    })
234            self.geography_operators = {
235                'bboverlaps' : PostGISOperator('&&'),
236                }
237
238        # Creating a dictionary lookup of all GIS terms for PostGIS.
239        gis_terms = ['isnull']
240        gis_terms += self.geometry_operators.keys()
241        gis_terms += self.geometry_functions.keys()
242        self.gis_terms = dict([(term, None) for term in gis_terms])
243
244        self.area = prefix + 'Area'
245        self.bounding_circle = BOUNDINGCIRCLE
246        self.centroid = prefix + 'Centroid'
247        self.collect = prefix + 'Collect'
248        self.difference = prefix + 'Difference'
249        self.distance = prefix + 'Distance'
250        self.distance_sphere = prefix + 'distance_sphere'
251        self.distance_spheroid = prefix + 'distance_spheroid'
252        self.envelope = prefix + 'Envelope'
253        self.extent = prefix + 'Extent'
254        self.extent3d = prefix + 'Extent3D'
255        self.force_rhr = prefix + 'ForceRHR'
256        self.geohash = GEOHASH
257        self.geojson = GEOJSON
258        self.gml = prefix + 'AsGML'
259        self.intersection = prefix + 'Intersection'
260        self.kml = ASKML
261        self.length = prefix + 'Length'
262        self.length3d = prefix + 'Length3D'
263        self.length_spheroid = prefix + 'length_spheroid'
264        self.makeline = MAKELINE
265        self.mem_size = prefix + 'mem_size'
266        self.num_geom = prefix + 'NumGeometries'
267        self.num_points =prefix + 'npoints'
268        self.perimeter = prefix + 'Perimeter'
269        self.perimeter3d = prefix + 'Perimeter3D'
270        self.point_on_surface = prefix + 'PointOnSurface'
271        self.polygonize = prefix + 'Polygonize'
272        self.reverse = prefix + 'Reverse'
273        self.scale = prefix + 'Scale'
274        self.snap_to_grid = prefix + 'SnapToGrid'
275        self.svg = prefix + 'AsSVG'
276        self.sym_difference = prefix + 'SymDifference'
277        self.transform = prefix + 'Transform'
278        self.translate = prefix + 'Translate'
279        self.union = UNION
280        self.unionagg = UNIONAGG
281
282    def check_aggregate_support(self, aggregate):
283        """
284        Checks if the given aggregate name is supported (that is, if it's
285        in `self.valid_aggregates`).
286        """
287        agg_name = aggregate.__class__.__name__
288        return agg_name in self.valid_aggregates
289
290    def convert_extent(self, box):
291        """
292        Returns a 4-tuple extent for the `Extent` aggregate by converting
293        the bounding box text returned by PostGIS (`box` argument), for
294        example: "BOX(-90.0 30.0, -85.0 40.0)".
295        """
296        ll, ur = box[4:-1].split(',')
297        xmin, ymin = map(float, ll.split())
298        xmax, ymax = map(float, ur.split())
299        return (xmin, ymin, xmax, ymax)
300
301    def convert_extent3d(self, box3d):
302        """
303        Returns a 6-tuple extent for the `Extent3D` aggregate by converting
304        the 3d bounding-box text returnded by PostGIS (`box3d` argument), for
305        example: "BOX3D(-90.0 30.0 1, -85.0 40.0 2)".
306        """
307        ll, ur = box3d[6:-1].split(',')
308        xmin, ymin, zmin = map(float, ll.split())
309        xmax, ymax, zmax = map(float, ur.split())
310        return (xmin, ymin, zmin, xmax, ymax, zmax)
311
312    def convert_geom(self, hex, geo_field):
313        """
314        Converts the geometry returned from PostGIS aggretates.
315        """
316        if hex:
317            return Geometry(hex)
318        else:
319            return None
320
321    def geo_db_type(self, f):
322        """
323        Return the database field type for the given geometry field.
324        Typically this is `None` because geometry columns are added via
325        the `AddGeometryColumn` stored procedure, unless the field
326        has been specified to be of geography type instead.
327        """
328        if f.geography:
329            if not self.geography:
330                raise NotImplementedError('PostGIS 1.5 required for geography column support.')
331
332            if f.srid != 4326:
333                raise NotImplementedError('PostGIS 1.5 supports geography columns '
334                                          'only with an SRID of 4326.')
335
336            return 'geography(%s,%d)'% (f.geom_type, f.srid)
337        else:
338            return None
339
340    def get_distance(self, f, dist_val, lookup_type):
341        """
342        Retrieve the distance parameters for the given geometry field,
343        distance lookup value, and the distance lookup type.
344
345        This is the most complex implementation of the spatial backends due to
346        what is supported on geodetic geometry columns vs. what's available on
347        projected geometry columns.  In addition, it has to take into account
348        the newly introduced geography column type introudced in PostGIS 1.5.
349        """
350        # Getting the distance parameter and any options.
351        if len(dist_val) == 1:
352            value, option = dist_val[0], None
353        else:
354            value, option = dist_val
355
356        # Shorthand boolean flags.
357        geodetic = f.geodetic(self.connection)
358        geography = f.geography and self.geography
359
360        if isinstance(value, Distance):
361            if geography:
362                dist_param = value.m
363            elif geodetic:
364                if lookup_type == 'dwithin':
365                    raise ValueError('Only numeric values of degree units are '
366                                     'allowed on geographic DWithin queries.')
367                dist_param = value.m
368            else:
369                dist_param = getattr(value, Distance.unit_attname(f.units_name(self.connection)))
370        else:
371            # Assuming the distance is in the units of the field.
372            dist_param = value
373
374        if (not geography and geodetic and lookup_type != 'dwithin'
375            and option == 'spheroid'):
376            # using distance_spheroid requires the spheroid of the field as
377            # a parameter.
378            return [f._spheroid, dist_param]
379        else:
380            return [dist_param]
381
382    def get_geom_placeholder(self, f, value):
383        """
384        Provides a proper substitution value for Geometries that are not in the
385        SRID of the field.  Specifically, this routine will substitute in the
386        ST_Transform() function call.
387        """
388        if value is None or value.srid == f.srid:
389            placeholder = '%s'
390        else:
391            # Adding Transform() to the SQL placeholder.
392            placeholder = '%s(%%s, %s)' % (self.transform, f.srid)
393
394        if hasattr(value, 'expression'):
395            # If this is an F expression, then we don't really want
396            # a placeholder and instead substitute in the column
397            # of the expression.
398            placeholder = placeholder % '%s.%s' % tuple(map(self.quote_name, value.cols[value.expression]))
399
400        return placeholder
401
402    def _get_postgis_func(self, func):
403        """
404        Helper routine for calling PostGIS functions and returning their result.
405        """
406        cursor = self.connection._cursor()
407        try:
408            try:
409                cursor.execute('SELECT %s()' % func)
410                row = cursor.fetchone()
411            except:
412                # Responsibility of callers to perform error handling.
413                raise
414        finally:
415            # Close out the connection.  See #9437.
416            self.connection.close()
417        return row[0]
418
419    def postgis_geos_version(self):
420        "Returns the version of the GEOS library used with PostGIS."
421        return self._get_postgis_func('postgis_geos_version')
422
423    def postgis_lib_version(self):
424        "Returns the version number of the PostGIS library used with PostgreSQL."
425        return self._get_postgis_func('postgis_lib_version')
426
427    def postgis_proj_version(self):
428        "Returns the version of the PROJ.4 library used with PostGIS."
429        return self._get_postgis_func('postgis_proj_version')
430
431    def postgis_version(self):
432        "Returns PostGIS version number and compile-time options."
433        return self._get_postgis_func('postgis_version')
434
435    def postgis_full_version(self):
436        "Returns PostGIS version number and compile-time options."
437        return self._get_postgis_func('postgis_full_version')
438
439    def postgis_version_tuple(self):
440        """
441        Returns the PostGIS version as a tuple (version string, major,
442        minor, subminor).
443        """
444        # Getting the PostGIS version
445        version = self.postgis_lib_version()
446        m = self.version_regex.match(version)
447
448        if m:
449            major = int(m.group('major'))
450            minor1 = int(m.group('minor1'))
451            minor2 = int(m.group('minor2'))
452        else:
453            raise Exception('Could not parse PostGIS version string: %s' % version)
454
455        return (version, major, minor1, minor2)
456
457    def proj_version_tuple(self):
458        """
459        Return the version of PROJ.4 used by PostGIS as a tuple of the
460        major, minor, and subminor release numbers.
461        """
462        proj_regex = re.compile(r'(\d+)\.(\d+)\.(\d+)')
463        proj_ver_str = self.postgis_proj_version()
464        m = proj_regex.search(proj_ver_str)
465        if m:
466            return tuple(map(int, [m.group(1), m.group(2), m.group(3)]))
467        else:
468            raise Exception('Could not determine PROJ.4 version from PostGIS.')
469
470    def num_params(self, lookup_type, num_param):
471        """
472        Helper routine that returns a boolean indicating whether the number of
473        parameters is correct for the lookup type.
474        """
475        def exactly_two(np): return np == 2
476        def two_to_three(np): return np >= 2 and np <=3
477        if (lookup_type in self.distance_functions and
478            lookup_type != 'dwithin'):
479            return two_to_three(num_param)
480        else:
481            return exactly_two(num_param)
482
483    def spatial_lookup_sql(self, lvalue, lookup_type, value, field, qn):
484        """
485        Constructs spatial SQL from the given lookup value tuple a
486        (alias, col, db_type), the lookup type string, lookup value, and
487        the geometry field.
488        """
489        alias, col, db_type = lvalue
490
491        # Getting the quoted geometry column.
492        geo_col = '%s.%s' % (qn(alias), qn(col))
493
494        if lookup_type in self.geometry_operators:
495            if field.geography and not lookup_type in self.geography_operators:
496                raise ValueError('PostGIS geography does not support the '
497                                 '"%s" lookup.' % lookup_type)
498            # Handling a PostGIS operator.
499            op = self.geometry_operators[lookup_type]
500            return op.as_sql(geo_col, self.get_geom_placeholder(field, value))
501        elif lookup_type in self.geometry_functions:
502            if field.geography and not lookup_type in self.geography_functions:
503                raise ValueError('PostGIS geography type does not support the '
504                                 '"%s" lookup.' % lookup_type)
505
506            # See if a PostGIS geometry function matches the lookup type.
507            tmp = self.geometry_functions[lookup_type]
508
509            # Lookup types that are tuples take tuple arguments, e.g., 'relate' and
510            # distance lookups.
511            if isinstance(tmp, tuple):
512                # First element of tuple is the PostGISOperation instance, and the
513                # second element is either the type or a tuple of acceptable types
514                # that may passed in as further parameters for the lookup type.
515                op, arg_type = tmp
516
517                # Ensuring that a tuple _value_ was passed in from the user
518                if not isinstance(value, (tuple, list)):
519                    raise ValueError('Tuple required for `%s` lookup type.' % lookup_type)
520
521                # Geometry is first element of lookup tuple.
522                geom = value[0]
523
524                # Number of valid tuple parameters depends on the lookup type.
525                nparams = len(value)
526                if not self.num_params(lookup_type, nparams):
527                    raise ValueError('Incorrect number of parameters given for `%s` lookup type.' % lookup_type)
528
529                # Ensuring the argument type matches what we expect.
530                if not isinstance(value[1], arg_type):
531                    raise ValueError('Argument type should be %s, got %s instead.' % (arg_type, type(value[1])))
532
533                # For lookup type `relate`, the op instance is not yet created (has
534                # to be instantiated here to check the pattern parameter).
535                if lookup_type == 'relate':
536                    op = op(self.geom_func_prefix, value[1])
537                elif lookup_type in self.distance_functions and lookup_type != 'dwithin':
538                    if not field.geography and field.geodetic(self.connection):
539                        # Geodetic distances are only availble from Points to
540                        # PointFields on PostGIS 1.4 and below.
541                        if not self.connection.ops.geography:
542                            if field.geom_type != 'POINT':
543                                raise ValueError('PostGIS spherical operations are only valid on PointFields.')
544
545                            if str(geom.geom_type) != 'Point':
546                                raise ValueError('PostGIS geometry distance parameter is required to be of type Point.')
547
548                        # Setting up the geodetic operation appropriately.
549                        if nparams == 3 and value[2] == 'spheroid':
550                            op = op['spheroid']
551                        else:
552                            op = op['sphere']
553                    else:
554                        op = op['cartesian']
555            else:
556                op = tmp
557                geom = value
558
559            # Calling the `as_sql` function on the operation instance.
560            return op.as_sql(geo_col, self.get_geom_placeholder(field, geom))
561
562        elif lookup_type == 'isnull':
563            # Handling 'isnull' lookup type
564            return "%s IS %sNULL" % (geo_col, (not value and 'NOT ' or ''))
565
566        raise TypeError("Got invalid lookup_type: %s" % repr(lookup_type))
567
568    def spatial_aggregate_sql(self, agg):
569        """
570        Returns the spatial aggregate SQL template and function for the
571        given Aggregate instance.
572        """
573        agg_name = agg.__class__.__name__
574        if not self.check_aggregate_support(agg):
575            raise NotImplementedError('%s spatial aggregate is not implmented for this backend.' % agg_name)
576        agg_name = agg_name.lower()
577        if agg_name == 'union': agg_name += 'agg'
578        sql_template = '%(function)s(%(field)s)'
579        sql_function = getattr(self, agg_name)
580        return sql_template, sql_function
581
582    # Routines for getting the OGC-compliant models.
583    def geometry_columns(self):
584        from django.contrib.gis.db.backends.postgis.models import GeometryColumns
585        return GeometryColumns
586
587    def spatial_ref_sys(self):
588        from django.contrib.gis.db.backends.postgis.models import SpatialRefSys
589        return SpatialRefSys