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 """

    10 import re

    11 from decimal import Decimal

    12 

    13 from django.db.backends.oracle.base import DatabaseOperations

    14 from django.contrib.gis.db.backends.base import BaseSpatialOperations

    15 from django.contrib.gis.db.backends.oracle.adapter import OracleSpatialAdapter

    16 from django.contrib.gis.db.backends.util import SpatialFunction

    17 from django.contrib.gis.geometry.backend import Geometry

    18 from django.contrib.gis.measure import Distance

    19 

    20 class 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 

    29 class 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 

    39 class 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 

    45 class 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 

    56 class 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

    68 dtypes = (Decimal, Distance, float, int, long)

    69 

    70 class 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