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

import re
from decimal import Decimal

from django.conf import settings
from django.contrib.gis.db.backends.base import BaseSpatialOperations
from django.contrib.gis.db.backends.util import SpatialOperation, SpatialFunction
from django.contrib.gis.db.backends.postgis.adapter import PostGISAdapter
from django.contrib.gis.geometry.backend import Geometry
from django.contrib.gis.measure import Distance
from django.core.exceptions import ImproperlyConfigured
from django.db.backends.postgresql_psycopg2.base import DatabaseOperations
from django.db.utils import DatabaseError

#### Classes used in constructing PostGIS spatial SQL ####
class PostGISOperator(SpatialOperation):
    "For PostGIS operators (e.g. `&&`, `~`)."
    def __init__(self, operator):
        super(PostGISOperator, self).__init__(operator=operator)

class PostGISFunction(SpatialFunction):
    "For PostGIS function calls (e.g., `ST_Contains(table, geom)`)."
    def __init__(self, prefix, function, **kwargs):
        super(PostGISFunction, self).__init__(prefix + function, **kwargs)

class PostGISFunctionParam(PostGISFunction):
    "For PostGIS functions that take another parameter (e.g. DWithin, Relate)."
    sql_template = '%(function)s(%(geo_col)s, %(geometry)s, %%s)'

class PostGISDistance(PostGISFunction):
    "For PostGIS distance operations."
    dist_func = 'Distance'
    sql_template = '%(function)s(%(geo_col)s, %(geometry)s) %(operator)s %%s'

    def __init__(self, prefix, operator):
        super(PostGISDistance, self).__init__(prefix, self.dist_func,
                                              operator=operator)

class PostGISSpheroidDistance(PostGISFunction):
    "For PostGIS spherical distance operations (using the spheroid)."
    dist_func = 'distance_spheroid'
    sql_template = '%(function)s(%(geo_col)s, %(geometry)s, %%s) %(operator)s %%s'
    def __init__(self, prefix, operator):
        # An extra parameter in `end_subst` is needed for the spheroid string.
        super(PostGISSpheroidDistance, self).__init__(prefix, self.dist_func,
                                                      operator=operator)

class PostGISSphereDistance(PostGISDistance):
    "For PostGIS spherical distance operations."
    dist_func = 'distance_sphere'

class PostGISRelate(PostGISFunctionParam):
    "For PostGIS Relate(<geom>, <pattern>) calls."
    pattern_regex = re.compile(r'^[012TF\*]{9}$')
    def __init__(self, prefix, pattern):
        if not self.pattern_regex.match(pattern):
            raise ValueError('Invalid intersection matrix pattern "%s".' % pattern)
        super(PostGISRelate, self).__init__(prefix, 'Relate')


class PostGISOperations(DatabaseOperations, BaseSpatialOperations):
    compiler_module = 'django.contrib.gis.db.models.sql.compiler'
    name = 'postgis'
    postgis = True
    version_regex = re.compile(r'^(?P<major>\d)\.(?P<minor1>\d)\.(?P<minor2>\d+)')
    valid_aggregates = dict([(k, None) for k in
                             ('Collect', 'Extent', 'Extent3D', 'MakeLine', 'Union')])

    Adapter = PostGISAdapter
    Adaptor = Adapter # Backwards-compatibility alias.

    def __init__(self, connection):
        super(PostGISOperations, self).__init__(connection)

        # Trying to get the PostGIS version because the function
        # signatures will depend on the version used.  The cost
        # here is a database query to determine the version, which
        # can be mitigated by setting `POSTGIS_VERSION` with a 3-tuple
        # comprising user-supplied values for the major, minor, and
        # subminor revision of PostGIS.
        try:
            if hasattr(settings, 'POSTGIS_VERSION'):
                vtup = settings.POSTGIS_VERSION
                if len(vtup) == 3:
                    # The user-supplied PostGIS version.
                    version = vtup
                else:
                    # This was the old documented way, but it's stupid to
                    # include the string.
                    version = vtup[1:4]
            else:
                vtup = self.postgis_version_tuple()
                version = vtup[1:]

            # Getting the prefix -- even though we don't officially support
            # PostGIS 1.2 anymore, keeping it anyway in case a prefix change
            # for something else is necessary.
            if version >= (1, 2, 2):
                prefix = 'ST_'
            else:
                prefix = ''

            self.geom_func_prefix = prefix
            self.spatial_version = version
        except DatabaseError:
            raise ImproperlyConfigured('Cannot determine PostGIS version for database "%s". '
                                       'GeoDjango requires at least PostGIS version 1.3. '
                                       'Was the database created from a spatial database '
                                       'template?' % self.connection.settings_dict['NAME']
                                       )
        except Exception, e:
            # TODO: Raise helpful exceptions as they become known.
            raise

        # PostGIS-specific operators. The commented descriptions of these
        # operators come from Section 7.6 of the PostGIS 1.4 documentation.
        self.geometry_operators = {
            # The "&<" operator returns true if A's bounding box overlaps or
            # is to the left of B's bounding box.
            'overlaps_left' : PostGISOperator('&<'),
            # The "&>" operator returns true if A's bounding box overlaps or
            # is to the right of B's bounding box.
            'overlaps_right' : PostGISOperator('&>'),
            # The "<<" operator returns true if A's bounding box is strictly
            # to the left of B's bounding box.
            'left' : PostGISOperator('<<'),
            # The ">>" operator returns true if A's bounding box is strictly
            # to the right of B's bounding box.
            'right' : PostGISOperator('>>'),
            # The "&<|" operator returns true if A's bounding box overlaps or
            # is below B's bounding box.
            'overlaps_below' : PostGISOperator('&<|'),
            # The "|&>" operator returns true if A's bounding box overlaps or
            # is above B's bounding box.
            'overlaps_above' : PostGISOperator('|&>'),
            # The "<<|" operator returns true if A's bounding box is strictly
            # below B's bounding box.
            'strictly_below' : PostGISOperator('<<|'),
            # The "|>>" operator returns true if A's bounding box is strictly
            # above B's bounding box.
            'strictly_above' : PostGISOperator('|>>'),
            # The "~=" operator is the "same as" operator. It tests actual
            # geometric equality of two features. So if A and B are the same feature,
            # vertex-by-vertex, the operator returns true.
            'same_as' : PostGISOperator('~='),
            'exact' : PostGISOperator('~='),
            # The "@" operator returns true if A's bounding box is completely contained
            # by B's bounding box.
            'contained' : PostGISOperator('@'),
            # The "~" operator returns true if A's bounding box completely contains
            #  by B's bounding box.
            'bbcontains' : PostGISOperator('~'),
            # The "&&" operator returns true if A's bounding box overlaps
            # B's bounding box.
            'bboverlaps' : PostGISOperator('&&'),
            }

        self.geometry_functions = {
            'equals' : PostGISFunction(prefix, 'Equals'),
            'disjoint' : PostGISFunction(prefix, 'Disjoint'),
            'touches' : PostGISFunction(prefix, 'Touches'),
            'crosses' : PostGISFunction(prefix, 'Crosses'),
            'within' : PostGISFunction(prefix, 'Within'),
            'overlaps' : PostGISFunction(prefix, 'Overlaps'),
            'contains' : PostGISFunction(prefix, 'Contains'),
            'intersects' : PostGISFunction(prefix, 'Intersects'),
            'relate' : (PostGISRelate, basestring),
            }

        # Valid distance types and substitutions
        dtypes = (Decimal, Distance, float, int, long)
        def get_dist_ops(operator):
            "Returns operations for both regular and spherical distances."
            return {'cartesian' : PostGISDistance(prefix, operator),
                    'sphere' : PostGISSphereDistance(prefix, operator),
                    'spheroid' : PostGISSpheroidDistance(prefix, operator),
                    }
        self.distance_functions = {
            'distance_gt' : (get_dist_ops('>'), dtypes),
            'distance_gte' : (get_dist_ops('>='), dtypes),
            'distance_lt' : (get_dist_ops('<'), dtypes),
            'distance_lte' : (get_dist_ops('<='), dtypes),
            }

        # Versions 1.2.2+ have KML serialization support.
        if version < (1, 2, 2):
            ASKML = False
        else:
            ASKML = 'ST_AsKML'
            self.geometry_functions.update(
                {'coveredby' : PostGISFunction(prefix, 'CoveredBy'),
                 'covers' : PostGISFunction(prefix, 'Covers'),
                 })
            self.distance_functions['dwithin'] = (PostGISFunctionParam(prefix, 'DWithin'), dtypes)

        # Adding the distance functions to the geometries lookup.
        self.geometry_functions.update(self.distance_functions)

        # The union aggregate and topology operation use the same signature
        # in versions 1.3+.
        if version < (1, 3, 0):
            UNIONAGG = 'GeomUnion'
            UNION = 'Union'
            MAKELINE = False
        else:
            UNIONAGG = 'ST_Union'
            UNION = 'ST_Union'
            MAKELINE = 'ST_MakeLine'

        # Only PostGIS versions 1.3.4+ have GeoJSON serialization support.
        if version < (1, 3, 4):
            GEOJSON = False
        else:
            GEOJSON = prefix + 'AsGeoJson'

        # ST_ContainsProperly ST_MakeLine, and ST_GeoHash added in 1.4.
        if version >= (1, 4, 0):
            GEOHASH = 'ST_GeoHash'
            BOUNDINGCIRCLE = 'ST_MinimumBoundingCircle'
            self.geometry_functions['contains_properly'] = PostGISFunction(prefix, 'ContainsProperly')
        else:
            GEOHASH, BOUNDINGCIRCLE = False, False

        # Geography type support added in 1.5.
        if version >= (1, 5, 0):
            self.geography = True
            # Only a subset of the operators and functions are available
            # for the geography type.
            self.geography_functions = self.distance_functions.copy()
            self.geography_functions.update({
                    'coveredby' : self.geometry_functions['coveredby'],
                    'covers' : self.geometry_functions['covers'],
                    'intersects' : self.geometry_functions['intersects'],
                    })
            self.geography_operators = {
                'bboverlaps' : PostGISOperator('&&'),
                }

        # Creating a dictionary lookup of all GIS terms for PostGIS.
        gis_terms = ['isnull']
        gis_terms += self.geometry_operators.keys()
        gis_terms += self.geometry_functions.keys()
        self.gis_terms = dict([(term, None) for term in gis_terms])

        self.area = prefix + 'Area'
        self.bounding_circle = BOUNDINGCIRCLE
        self.centroid = prefix + 'Centroid'
        self.collect = prefix + 'Collect'
        self.difference = prefix + 'Difference'
        self.distance = prefix + 'Distance'
        self.distance_sphere = prefix + 'distance_sphere'
        self.distance_spheroid = prefix + 'distance_spheroid'
        self.envelope = prefix + 'Envelope'
        self.extent = prefix + 'Extent'
        self.extent3d = prefix + 'Extent3D'
        self.force_rhr = prefix + 'ForceRHR'
        self.geohash = GEOHASH
        self.geojson = GEOJSON
        self.gml = prefix + 'AsGML'
        self.intersection = prefix + 'Intersection'
        self.kml = ASKML
        self.length = prefix + 'Length'
        self.length3d = prefix + 'Length3D'
        self.length_spheroid = prefix + 'length_spheroid'
        self.makeline = MAKELINE
        self.mem_size = prefix + 'mem_size'
        self.num_geom = prefix + 'NumGeometries'
        self.num_points =prefix + 'npoints'
        self.perimeter = prefix + 'Perimeter'
        self.perimeter3d = prefix + 'Perimeter3D'
        self.point_on_surface = prefix + 'PointOnSurface'
        self.polygonize = prefix + 'Polygonize'
        self.reverse = prefix + 'Reverse'
        self.scale = prefix + 'Scale'
        self.snap_to_grid = prefix + 'SnapToGrid'
        self.svg = prefix + 'AsSVG'
        self.sym_difference = prefix + 'SymDifference'
        self.transform = prefix + 'Transform'
        self.translate = prefix + 'Translate'
        self.union = UNION
        self.unionagg = UNIONAGG

    def check_aggregate_support(self, aggregate):
        """
        Checks if the given aggregate name is supported (that is, if it's
        in `self.valid_aggregates`).
        """
        agg_name = aggregate.__class__.__name__
        return agg_name in self.valid_aggregates

    def convert_extent(self, box):
        """
        Returns a 4-tuple extent for the `Extent` aggregate by converting
        the bounding box text returned by PostGIS (`box` argument), for
        example: "BOX(-90.0 30.0, -85.0 40.0)".
        """
        ll, ur = box[4:-1].split(',')
        xmin, ymin = map(float, ll.split())
        xmax, ymax = map(float, ur.split())
        return (xmin, ymin, xmax, ymax)

    def convert_extent3d(self, box3d):
        """
        Returns a 6-tuple extent for the `Extent3D` aggregate by converting
        the 3d bounding-box text returnded by PostGIS (`box3d` argument), for
        example: "BOX3D(-90.0 30.0 1, -85.0 40.0 2)".
        """
        ll, ur = box3d[6:-1].split(',')
        xmin, ymin, zmin = map(float, ll.split())
        xmax, ymax, zmax = map(float, ur.split())
        return (xmin, ymin, zmin, xmax, ymax, zmax)

    def convert_geom(self, hex, geo_field):
        """
        Converts the geometry returned from PostGIS aggretates.
        """
        if hex:
            return Geometry(hex)
        else:
            return None

    def geo_db_type(self, f):
        """
        Return the database field type for the given geometry field.
        Typically this is `None` because geometry columns are added via
        the `AddGeometryColumn` stored procedure, unless the field
        has been specified to be of geography type instead.
        """
        if f.geography:
            if not self.geography:
                raise NotImplementedError('PostGIS 1.5 required for geography column support.')

            if f.srid != 4326:
                raise NotImplementedError('PostGIS 1.5 supports geography columns '
                                          'only with an SRID of 4326.')

            return 'geography(%s,%d)'% (f.geom_type, f.srid)
        else:
            return None

    def get_distance(self, f, dist_val, lookup_type):
        """
        Retrieve the distance parameters for the given geometry field,
        distance lookup value, and the distance lookup type.

        This is the most complex implementation of the spatial backends due to
        what is supported on geodetic geometry columns vs. what's available on
        projected geometry columns.  In addition, it has to take into account
        the newly introduced geography column type introudced in PostGIS 1.5.
        """
        # Getting the distance parameter and any options.
        if len(dist_val) == 1:
            value, option = dist_val[0], None
        else:
            value, option = dist_val

        # Shorthand boolean flags.
        geodetic = f.geodetic(self.connection)
        geography = f.geography and self.geography

        if isinstance(value, Distance):
            if geography:
                dist_param = value.m
            elif geodetic:
                if lookup_type == 'dwithin':
                    raise ValueError('Only numeric values of degree units are '
                                     'allowed on geographic DWithin queries.')
                dist_param = value.m
            else:
                dist_param = getattr(value, Distance.unit_attname(f.units_name(self.connection)))
        else:
            # Assuming the distance is in the units of the field.
            dist_param = value

        if (not geography and geodetic and lookup_type != 'dwithin'
            and option == 'spheroid'):
            # using distance_spheroid requires the spheroid of the field as
            # a parameter.
            return [f._spheroid, dist_param]
        else:
            return [dist_param]

    def get_geom_placeholder(self, f, value):
        """
        Provides a proper substitution value for Geometries that are not in the
        SRID of the field.  Specifically, this routine will substitute in the
        ST_Transform() function call.
        """
        if value is None or value.srid == f.srid:
            placeholder = '%s'
        else:
            # Adding Transform() to the SQL placeholder.
            placeholder = '%s(%%s, %s)' % (self.transform, f.srid)

        if hasattr(value, 'expression'):
            # If this is an F expression, then we don't really want
            # a placeholder and instead substitute in the column
            # of the expression.
            placeholder = placeholder % '%s.%s' % tuple(map(self.quote_name, value.cols[value.expression]))

        return placeholder

    def _get_postgis_func(self, func):
        """
        Helper routine for calling PostGIS functions and returning their result.
        """
        cursor = self.connection._cursor()
        try:
            try:
                cursor.execute('SELECT %s()' % func)
                row = cursor.fetchone()
            except:
                # Responsibility of callers to perform error handling.
                raise
        finally:
            # Close out the connection.  See #9437.
            self.connection.close()
        return row[0]

    def postgis_geos_version(self):
        "Returns the version of the GEOS library used with PostGIS."
        return self._get_postgis_func('postgis_geos_version')

    def postgis_lib_version(self):
        "Returns the version number of the PostGIS library used with PostgreSQL."
        return self._get_postgis_func('postgis_lib_version')

    def postgis_proj_version(self):
        "Returns the version of the PROJ.4 library used with PostGIS."
        return self._get_postgis_func('postgis_proj_version')

    def postgis_version(self):
        "Returns PostGIS version number and compile-time options."
        return self._get_postgis_func('postgis_version')

    def postgis_full_version(self):
        "Returns PostGIS version number and compile-time options."
        return self._get_postgis_func('postgis_full_version')

    def postgis_version_tuple(self):
        """
        Returns the PostGIS version as a tuple (version string, major,
        minor, subminor).
        """
        # Getting the PostGIS version
        version = self.postgis_lib_version()
        m = self.version_regex.match(version)

        if m:
            major = int(m.group('major'))
            minor1 = int(m.group('minor1'))
            minor2 = int(m.group('minor2'))
        else:
            raise Exception('Could not parse PostGIS version string: %s' % version)

        return (version, major, minor1, minor2)

    def proj_version_tuple(self):
        """
        Return the version of PROJ.4 used by PostGIS as a tuple of the
        major, minor, and subminor release numbers.
        """
        proj_regex = re.compile(r'(\d+)\.(\d+)\.(\d+)')
        proj_ver_str = self.postgis_proj_version()
        m = proj_regex.search(proj_ver_str)
        if m:
            return tuple(map(int, [m.group(1), m.group(2), m.group(3)]))
        else:
            raise Exception('Could not determine PROJ.4 version from PostGIS.')

    def num_params(self, lookup_type, num_param):
        """
        Helper routine that returns a boolean indicating whether the number of
        parameters is correct for the lookup type.
        """
        def exactly_two(np): return np == 2
        def two_to_three(np): return np >= 2 and np <=3
        if (lookup_type in self.distance_functions and
            lookup_type != 'dwithin'):
            return two_to_three(num_param)
        else:
            return exactly_two(num_param)

    def spatial_lookup_sql(self, lvalue, lookup_type, value, field, qn):
        """
        Constructs spatial SQL from the given lookup value tuple a
        (alias, col, db_type), the lookup type string, lookup value, and
        the geometry field.
        """
        alias, col, db_type = lvalue

        # Getting the quoted geometry column.
        geo_col = '%s.%s' % (qn(alias), qn(col))

        if lookup_type in self.geometry_operators:
            if field.geography and not lookup_type in self.geography_operators:
                raise ValueError('PostGIS geography does not support the '
                                 '"%s" lookup.' % lookup_type)
            # Handling a PostGIS operator.
            op = self.geometry_operators[lookup_type]
            return op.as_sql(geo_col, self.get_geom_placeholder(field, value))
        elif lookup_type in self.geometry_functions:
            if field.geography and not lookup_type in self.geography_functions:
                raise ValueError('PostGIS geography type does not support the '
                                 '"%s" lookup.' % lookup_type)

            # See if a PostGIS geometry function matches the lookup type.
            tmp = self.geometry_functions[lookup_type]

            # Lookup types that are tuples take tuple arguments, e.g., 'relate' and
            # distance lookups.
            if isinstance(tmp, tuple):
                # First element of tuple is the PostGISOperation instance, and the
                # second element is either the type or a tuple of acceptable types
                # that may passed in as further parameters for the lookup type.
                op, arg_type = tmp

                # Ensuring that a tuple _value_ was passed in from the user
                if not isinstance(value, (tuple, list)):
                    raise ValueError('Tuple required for `%s` lookup type.' % lookup_type)

                # Geometry is first element of lookup tuple.
                geom = value[0]

                # Number of valid tuple parameters depends on the lookup type.
                nparams = len(value)
                if not self.num_params(lookup_type, nparams):
                    raise ValueError('Incorrect number of parameters given for `%s` lookup type.' % lookup_type)

                # Ensuring the argument type matches what we expect.
                if not isinstance(value[1], arg_type):
                    raise ValueError('Argument type should be %s, got %s instead.' % (arg_type, type(value[1])))

                # For lookup type `relate`, the op instance is not yet created (has
                # to be instantiated here to check the pattern parameter).
                if lookup_type == 'relate':
                    op = op(self.geom_func_prefix, value[1])
                elif lookup_type in self.distance_functions and lookup_type != 'dwithin':
                    if not field.geography and field.geodetic(self.connection):
                        # Geodetic distances are only availble from Points to
                        # PointFields on PostGIS 1.4 and below.
                        if not self.connection.ops.geography:
                            if field.geom_type != 'POINT':
                                raise ValueError('PostGIS spherical operations are only valid on PointFields.')

                            if str(geom.geom_type) != 'Point':
                                raise ValueError('PostGIS geometry distance parameter is required to be of type Point.')

                        # Setting up the geodetic operation appropriately.
                        if nparams == 3 and value[2] == 'spheroid':
                            op = op['spheroid']
                        else:
                            op = op['sphere']
                    else:
                        op = op['cartesian']
            else:
                op = tmp
                geom = value

            # Calling the `as_sql` function on the operation instance.
            return op.as_sql(geo_col, self.get_geom_placeholder(field, geom))

        elif lookup_type == 'isnull':
            # Handling 'isnull' lookup type
            return "%s IS %sNULL" % (geo_col, (not value and 'NOT ' or ''))

        raise TypeError("Got invalid lookup_type: %s" % repr(lookup_type))

    def spatial_aggregate_sql(self, agg):
        """
        Returns the spatial aggregate SQL template and function for the
        given Aggregate instance.
        """
        agg_name = agg.__class__.__name__
        if not self.check_aggregate_support(agg):
            raise NotImplementedError('%s spatial aggregate is not implmented for this backend.' % agg_name)
        agg_name = agg_name.lower()
        if agg_name == 'union': agg_name += 'agg'
        sql_template = '%(function)s(%(field)s)'
        sql_function = getattr(self, agg_name)
        return sql_template, sql_function

    # Routines for getting the OGC-compliant models.
    def geometry_columns(self):
        from django.contrib.gis.db.backends.postgis.models import GeometryColumns
        return GeometryColumns

    def spatial_ref_sys(self):
        from django.contrib.gis.db.backends.postgis.models import SpatialRefSys
        return SpatialRefSys