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

  1"""
  2 This module contains the spatial lookup types, and the `get_geo_where_clause`
  3 routine for Oracle Spatial.
  4
  5 Please note that WKT support is broken on the XE version, and thus
  6 this backend will not work on such platforms.  Specifically, XE lacks
  7 support for an internal JVM, and Java libraries are required to use
  8 the WKT constructors.
  9"""
 10import re
 11from decimal import Decimal
 12
 13from django.db.backends.oracle.base import DatabaseOperations
 14from django.contrib.gis.db.backends.base import BaseSpatialOperations
 15from django.contrib.gis.db.backends.oracle.adapter import OracleSpatialAdapter
 16from django.contrib.gis.db.backends.util import SpatialFunction
 17from django.contrib.gis.geometry.backend import Geometry
 18from django.contrib.gis.measure import Distance
 19
 20class SDOOperation(SpatialFunction):
 21    "Base class for SDO* Oracle operations."
 22    sql_template = "%(function)s(%(geo_col)s, %(geometry)s) %(operator)s '%(result)s'"
 23
 24    def __init__(self, func, **kwargs):
 25        kwargs.setdefault('operator', '=')
 26        kwargs.setdefault('result', 'TRUE')
 27        super(SDOOperation, self).__init__(func, **kwargs)
 28
 29class SDODistance(SpatialFunction):
 30    "Class for Distance queries."
 31    sql_template = ('%(function)s(%(geo_col)s, %(geometry)s, %(tolerance)s) '
 32                    '%(operator)s %(result)s')
 33    dist_func = 'SDO_GEOM.SDO_DISTANCE'
 34    def __init__(self, op, tolerance=0.05):
 35        super(SDODistance, self).__init__(self.dist_func,
 36                                          tolerance=tolerance,
 37                                          operator=op, result='%s')
 38
 39class SDODWithin(SpatialFunction):
 40    dwithin_func = 'SDO_WITHIN_DISTANCE'
 41    sql_template = "%(function)s(%(geo_col)s, %(geometry)s, %%s) = 'TRUE'"
 42    def __init__(self):
 43        super(SDODWithin, self).__init__(self.dwithin_func)
 44
 45class SDOGeomRelate(SpatialFunction):
 46    "Class for using SDO_GEOM.RELATE."
 47    relate_func = 'SDO_GEOM.RELATE'
 48    sql_template = ("%(function)s(%(geo_col)s, '%(mask)s', %(geometry)s, "
 49                    "%(tolerance)s) %(operator)s '%(mask)s'")
 50    def __init__(self, mask, tolerance=0.05):
 51        # SDO_GEOM.RELATE(...) has a peculiar argument order: column, mask, geom, tolerance.
 52        # Moreover, the runction result is the mask (e.g., 'DISJOINT' instead of 'TRUE').
 53        super(SDOGeomRelate, self).__init__(self.relate_func, operator='=',
 54                                            mask=mask, tolerance=tolerance)
 55
 56class SDORelate(SpatialFunction):
 57    "Class for using SDO_RELATE."
 58    masks = 'TOUCH|OVERLAPBDYDISJOINT|OVERLAPBDYINTERSECT|EQUAL|INSIDE|COVEREDBY|CONTAINS|COVERS|ANYINTERACT|ON'
 59    mask_regex = re.compile(r'^(%s)(\+(%s))*$' % (masks, masks), re.I)
 60    sql_template = "%(function)s(%(geo_col)s, %(geometry)s, 'mask=%(mask)s') = 'TRUE'"
 61    relate_func = 'SDO_RELATE'
 62    def __init__(self, mask):
 63        if not self.mask_regex.match(mask):
 64            raise ValueError('Invalid %s mask: "%s"' % (self.relate_func, mask))
 65        super(SDORelate, self).__init__(self.relate_func, mask=mask)
 66
 67# Valid distance types and substitutions
 68dtypes = (Decimal, Distance, float, int, long)
 69
 70class OracleOperations(DatabaseOperations, BaseSpatialOperations):
 71    compiler_module = "django.contrib.gis.db.backends.oracle.compiler"
 72
 73    name = 'oracle'
 74    oracle = True
 75    valid_aggregates = dict([(a, None) for a in ('Union', 'Extent')])
 76
 77    Adapter = OracleSpatialAdapter
 78    Adaptor = Adapter # Backwards-compatibility alias.
 79
 80    area = 'SDO_GEOM.SDO_AREA'
 81    gml= 'SDO_UTIL.TO_GMLGEOMETRY'
 82    centroid = 'SDO_GEOM.SDO_CENTROID'
 83    difference = 'SDO_GEOM.SDO_DIFFERENCE'
 84    distance = 'SDO_GEOM.SDO_DISTANCE'
 85    extent= 'SDO_AGGR_MBR'
 86    intersection= 'SDO_GEOM.SDO_INTERSECTION'
 87    length = 'SDO_GEOM.SDO_LENGTH'
 88    num_geom = 'SDO_UTIL.GETNUMELEM'
 89    num_points = 'SDO_UTIL.GETNUMVERTICES'
 90    perimeter = length
 91    point_on_surface = 'SDO_GEOM.SDO_POINTONSURFACE'
 92    reverse = 'SDO_UTIL.REVERSE_LINESTRING'
 93    sym_difference = 'SDO_GEOM.SDO_XOR'
 94    transform = 'SDO_CS.TRANSFORM'
 95    union = 'SDO_GEOM.SDO_UNION'
 96    unionagg = 'SDO_AGGR_UNION'
 97
 98    # We want to get SDO Geometries as WKT because it is much easier to
 99    # instantiate GEOS proxies from WKT than SDO_GEOMETRY(...) strings.
100    # However, this adversely affects performance (i.e., Java is called
101    # to convert to WKT on every query).  If someone wishes to write a
102    # SDO_GEOMETRY(...) parser in Python, let me know =)
103    select = 'SDO_UTIL.TO_WKTGEOMETRY(%s)'
104
105    distance_functions = {
106        'distance_gt' : (SDODistance('>'), dtypes),
107        'distance_gte' : (SDODistance('>='), dtypes),
108        'distance_lt' : (SDODistance('<'), dtypes),
109        'distance_lte' : (SDODistance('<='), dtypes),
110        'dwithin' : (SDODWithin(), dtypes),
111        }
112
113    geometry_functions = {
114        'contains' : SDOOperation('SDO_CONTAINS'),
115        'coveredby' : SDOOperation('SDO_COVEREDBY'),
116        'covers' : SDOOperation('SDO_COVERS'),
117        'disjoint' : SDOGeomRelate('DISJOINT'),
118        'intersects' : SDOOperation('SDO_OVERLAPBDYINTERSECT'), # TODO: Is this really the same as ST_Intersects()?
119        'equals' : SDOOperation('SDO_EQUAL'),
120        'exact' : SDOOperation('SDO_EQUAL'),
121        'overlaps' : SDOOperation('SDO_OVERLAPS'),
122        'same_as' : SDOOperation('SDO_EQUAL'),
123        'relate' : (SDORelate, basestring), # Oracle uses a different syntax, e.g., 'mask=inside+touch'
124        'touches' : SDOOperation('SDO_TOUCH'),
125        'within' : SDOOperation('SDO_INSIDE'),
126        }
127    geometry_functions.update(distance_functions)
128
129    gis_terms = ['isnull']
130    gis_terms += geometry_functions.keys()
131    gis_terms = dict([(term, None) for term in gis_terms])
132
133    truncate_params = {'relate' : None}
134
135    def __init__(self, connection):
136        super(OracleOperations, self).__init__()
137        self.connection = connection
138
139    def convert_extent(self, clob):
140        if clob:
141            # Generally, Oracle returns a polygon for the extent -- however,
142            # it can return a single point if there's only one Point in the
143            # table.
144            ext_geom = Geometry(clob.read())
145            gtype = str(ext_geom.geom_type)
146            if gtype == 'Polygon':
147                # Construct the 4-tuple from the coordinates in the polygon.
148                shell = ext_geom.shell
149                ll, ur = shell[0][:2], shell[2][:2]
150            elif gtype == 'Point':
151                ll = ext_geom.coords[:2]
152                ur = ll
153            else:
154                raise Exception('Unexpected geometry type returned for extent: %s' % gtype)
155            xmin, ymin = ll
156            xmax, ymax = ur
157            return (xmin, ymin, xmax, ymax)
158        else:
159            return None
160
161    def convert_geom(self, clob, geo_field):
162        if clob:
163            return Geometry(clob.read(), geo_field.srid)
164        else:
165            return None
166
167    def geo_db_type(self, f):
168        """
169        Returns the geometry database type for Oracle.  Unlike other spatial
170        backends, no stored procedure is necessary and it's the same for all
171        geometry types.
172        """
173        return 'MDSYS.SDO_GEOMETRY'
174
175    def get_distance(self, f, value, lookup_type):
176        """
177        Returns the distance parameters given the value and the lookup type.
178        On Oracle, geometry columns with a geodetic coordinate system behave
179        implicitly like a geography column, and thus meters will be used as
180        the distance parameter on them.
181        """
182        if not value:
183            return []
184        value = value[0]
185        if isinstance(value, Distance):
186            if f.geodetic(self.connection):
187                dist_param = value.m
188            else:
189                dist_param = getattr(value, Distance.unit_attname(f.units_name(self.connection)))
190        else:
191            dist_param = value
192
193        # dwithin lookups on oracle require a special string parameter
194        # that starts with "distance=".
195        if lookup_type == 'dwithin':
196            dist_param = 'distance=%s' % dist_param
197
198        return [dist_param]
199
200    def get_geom_placeholder(self, f, value):
201        """
202        Provides a proper substitution value for Geometries that are not in the
203        SRID of the field.  Specifically, this routine will substitute in the
204        SDO_CS.TRANSFORM() function call.
205        """
206        if value is None:
207            return 'NULL'
208
209        def transform_value(val, srid):
210            return val.srid != srid
211
212        if hasattr(value, 'expression'):
213            if transform_value(value, f.srid):
214                placeholder = '%s(%%s, %s)' % (self.transform, f.srid)
215            else:
216                placeholder = '%s'
217            # No geometry value used for F expression, substitue in
218            # the column name instead.
219            return placeholder % '%s.%s' % tuple(map(self.quote_name, value.cols[value.expression]))
220        else:
221            if transform_value(value, f.srid):
222                return '%s(SDO_GEOMETRY(%%s, %s), %s)' % (self.transform, value.srid, f.srid)
223            else:
224                return 'SDO_GEOMETRY(%%s, %s)' % f.srid
225
226    def spatial_lookup_sql(self, lvalue, lookup_type, value, field, qn):
227        "Returns the SQL WHERE clause for use in Oracle spatial SQL construction."
228        alias, col, db_type = lvalue
229
230        # Getting the quoted table name as `geo_col`.
231        geo_col = '%s.%s' % (qn(alias), qn(col))
232
233        # See if a Oracle Geometry function matches the lookup type next
234        lookup_info = self.geometry_functions.get(lookup_type, False)
235        if lookup_info:
236            # Lookup types that are tuples take tuple arguments, e.g., 'relate' and
237            # 'dwithin' lookup types.
238            if isinstance(lookup_info, tuple):
239                # First element of tuple is lookup type, second element is the type
240                # of the expected argument (e.g., str, float)
241                sdo_op, arg_type = lookup_info
242                geom = value[0]
243
244                # Ensuring that a tuple _value_ was passed in from the user
245                if not isinstance(value, tuple):
246                    raise ValueError('Tuple required for `%s` lookup type.' % lookup_type)
247                if len(value) != 2:
248                    raise ValueError('2-element tuple required for %s lookup type.' % lookup_type)
249
250                # Ensuring the argument type matches what we expect.
251                if not isinstance(value[1], arg_type):
252                    raise ValueError('Argument type should be %s, got %s instead.' % (arg_type, type(value[1])))
253
254                if lookup_type == 'relate':
255                    # The SDORelate class handles construction for these queries,
256                    # and verifies the mask argument.
257                    return sdo_op(value[1]).as_sql(geo_col, self.get_geom_placeholder(field, geom))
258                else:
259                    # Otherwise, just call the `as_sql` method on the SDOOperation instance.
260                    return sdo_op.as_sql(geo_col, self.get_geom_placeholder(field, geom))
261            else:
262                # Lookup info is a SDOOperation instance, whose `as_sql` method returns
263                # the SQL necessary for the geometry function call. For example:
264                #  SDO_CONTAINS("geoapp_country"."poly", SDO_GEOMTRY('POINT(5 23)', 4326)) = 'TRUE'
265                return lookup_info.as_sql(geo_col, self.get_geom_placeholder(field, value))
266        elif lookup_type == 'isnull':
267            # Handling 'isnull' lookup type
268            return "%s IS %sNULL" % (geo_col, (not value and 'NOT ' or ''))
269
270        raise TypeError("Got invalid lookup_type: %s" % repr(lookup_type))
271
272    def spatial_aggregate_sql(self, agg):
273        """
274        Returns the spatial aggregate SQL template and function for the
275        given Aggregate instance.
276        """
277        agg_name = agg.__class__.__name__.lower()
278        if agg_name == 'union' : agg_name += 'agg'
279        if agg.is_extent:
280            sql_template = '%(function)s(%(field)s)'
281        else:
282            sql_template = '%(function)s(SDOAGGRTYPE(%(field)s,%(tolerance)s))'
283        sql_function = getattr(self, agg_name)
284        return self.select % sql_template, sql_function
285
286    # Routines for getting the OGC-compliant models.
287    def geometry_columns(self):
288        from django.contrib.gis.db.backends.oracle.models import GeometryColumns
289        return GeometryColumns
290
291    def spatial_ref_sys(self):
292        from django.contrib.gis.db.backends.oracle.models import SpatialRefSys
293        return SpatialRefSys