1 import re

     2 from decimal import Decimal

     3 

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

     5 from django.contrib.gis.db.backends.util import SpatialOperation, SpatialFunction

     6 from django.contrib.gis.db.backends.spatialite.adapter import SpatiaLiteAdapter

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

     8 from django.contrib.gis.measure import Distance

     9 from django.core.exceptions import ImproperlyConfigured

    10 from django.db.backends.sqlite3.base import DatabaseOperations

    11 from django.db.utils import DatabaseError

    12 

    13 class SpatiaLiteOperator(SpatialOperation):

    14     "For SpatiaLite operators (e.g. `&&`, `~`)."

    15     def __init__(self, operator):

    16         super(SpatiaLiteOperator, self).__init__(operator=operator)

    17 

    18 class SpatiaLiteFunction(SpatialFunction):

    19     "For SpatiaLite function calls."

    20     def __init__(self, function, **kwargs):

    21         super(SpatiaLiteFunction, self).__init__(function, **kwargs)

    22 

    23 class SpatiaLiteFunctionParam(SpatiaLiteFunction):

    24     "For SpatiaLite functions that take another parameter."

    25     sql_template = '%(function)s(%(geo_col)s, %(geometry)s, %%s)'

    26 

    27 class SpatiaLiteDistance(SpatiaLiteFunction):

    28     "For SpatiaLite distance operations."

    29     dist_func = 'Distance'

    30     sql_template = '%(function)s(%(geo_col)s, %(geometry)s) %(operator)s %%s'

    31 

    32     def __init__(self, operator):

    33         super(SpatiaLiteDistance, self).__init__(self.dist_func,

    34                                                  operator=operator)

    35 

    36 class SpatiaLiteRelate(SpatiaLiteFunctionParam):

    37     "For SpatiaLite Relate(<geom>, <pattern>) calls."

    38     pattern_regex = re.compile(r'^[012TF\*]{9}$')

    39     def __init__(self, pattern):

    40         if not self.pattern_regex.match(pattern):

    41             raise ValueError('Invalid intersection matrix pattern "%s".' % pattern)

    42         super(SpatiaLiteRelate, self).__init__('Relate')

    43 

    44 # Valid distance types and substitutions

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

    46 def get_dist_ops(operator):

    47     "Returns operations for regular distances; spherical distances are not currently supported."

    48     return (SpatiaLiteDistance(operator),)

    49 

    50 class SpatiaLiteOperations(DatabaseOperations, BaseSpatialOperations):

    51     compiler_module = 'django.contrib.gis.db.models.sql.compiler'

    52     name = 'spatialite'

    53     spatialite = True

    54     version_regex = re.compile(r'^(?P<major>\d)\.(?P<minor1>\d)\.(?P<minor2>\d+)')

    55     valid_aggregates = dict([(k, None) for k in ('Extent', 'Union')])

    56 

    57     Adapter = SpatiaLiteAdapter

    58     Adaptor = Adapter # Backwards-compatibility alias.

    59 

    60     area = 'Area'

    61     centroid = 'Centroid'

    62     contained = 'MbrWithin'

    63     difference = 'Difference'

    64     distance = 'Distance'

    65     envelope = 'Envelope'

    66     intersection = 'Intersection'

    67     length = 'GLength' # OpenGis defines Length, but this conflicts with an SQLite reserved keyword

    68     num_geom = 'NumGeometries'

    69     num_points = 'NumPoints'

    70     point_on_surface = 'PointOnSurface'

    71     scale = 'ScaleCoords'

    72     svg = 'AsSVG'

    73     sym_difference = 'SymDifference'

    74     transform = 'Transform'

    75     translate = 'ShiftCoords'

    76     union = 'GUnion' # OpenGis defines Union, but this conflicts with an SQLite reserved keyword

    77     unionagg = 'GUnion'

    78 

    79     from_text = 'GeomFromText'

    80     from_wkb = 'GeomFromWKB'

    81     select = 'AsText(%s)'

    82 

    83     geometry_functions = {

    84         'equals' : SpatiaLiteFunction('Equals'),

    85         'disjoint' : SpatiaLiteFunction('Disjoint'),

    86         'touches' : SpatiaLiteFunction('Touches'),

    87         'crosses' : SpatiaLiteFunction('Crosses'),

    88         'within' : SpatiaLiteFunction('Within'),

    89         'overlaps' : SpatiaLiteFunction('Overlaps'),

    90         'contains' : SpatiaLiteFunction('Contains'),

    91         'intersects' : SpatiaLiteFunction('Intersects'),

    92         'relate' : (SpatiaLiteRelate, basestring),

    93         # Retruns true if B's bounding box completely contains A's bounding box.

    94         'contained' : SpatiaLiteFunction('MbrWithin'),

    95         # Returns true if A's bounding box completely contains B's bounding box.

    96         'bbcontains' : SpatiaLiteFunction('MbrContains'),

    97         # Returns true if A's bounding box overlaps B's bounding box.

    98         'bboverlaps' : SpatiaLiteFunction('MbrOverlaps'),

    99         # These are implemented here as synonyms for Equals

   100         'same_as' : SpatiaLiteFunction('Equals'),

   101         'exact' : SpatiaLiteFunction('Equals'),

   102         }

   103 

   104     distance_functions = {

   105         'distance_gt' : (get_dist_ops('>'), dtypes),

   106         'distance_gte' : (get_dist_ops('>='), dtypes),

   107         'distance_lt' : (get_dist_ops('<'), dtypes),

   108         'distance_lte' : (get_dist_ops('<='), dtypes),

   109         }

   110     geometry_functions.update(distance_functions)

   111 

   112     def __init__(self, connection):

   113         super(DatabaseOperations, self).__init__()

   114         self.connection = connection

   115 

   116         # Determine the version of the SpatiaLite library.

   117         try:

   118             vtup = self.spatialite_version_tuple()

   119             version = vtup[1:]

   120             if version < (2, 3, 0):

   121                 raise ImproperlyConfigured('GeoDjango only supports SpatiaLite versions '

   122                                            '2.3.0 and above')

   123             self.spatial_version = version

   124         except ImproperlyConfigured:

   125             raise

   126         except Exception, msg:

   127             raise ImproperlyConfigured('Cannot determine the SpatiaLite version for the "%s" '

   128                                        'database (error was "%s").  Was the SpatiaLite initialization '

   129                                        'SQL loaded on this database?' %

   130                                        (self.connection.settings_dict['NAME'], msg))

   131 

   132         # Creating the GIS terms dictionary.

   133         gis_terms = ['isnull']

   134         gis_terms += self.geometry_functions.keys()

   135         self.gis_terms = dict([(term, None) for term in gis_terms])

   136 

   137     def check_aggregate_support(self, aggregate):

   138         """

   139         Checks if the given aggregate name is supported (that is, if it's

   140         in `self.valid_aggregates`).

   141         """

   142         agg_name = aggregate.__class__.__name__

   143         return agg_name in self.valid_aggregates

   144 

   145     def convert_geom(self, wkt, geo_field):

   146         """

   147         Converts geometry WKT returned from a SpatiaLite aggregate.

   148         """

   149         if wkt:

   150             return Geometry(wkt, geo_field.srid)

   151         else:

   152             return None

   153 

   154     def geo_db_type(self, f):

   155         """

   156         Returns None because geometry columnas are added via the

   157         `AddGeometryColumn` stored procedure on SpatiaLite.

   158         """

   159         return None

   160 

   161     def get_distance(self, f, value, lookup_type):

   162         """

   163         Returns the distance parameters for the given geometry field,

   164         lookup value, and lookup type.  SpatiaLite only supports regular

   165         cartesian-based queries (no spheroid/sphere calculations for point

   166         geometries like PostGIS).

   167         """

   168         if not value:

   169             return []

   170         value = value[0]

   171         if isinstance(value, Distance):

   172             if f.geodetic(self.connection):

   173                 raise ValueError('SpatiaLite does not support distance queries on '

   174                                  'geometry fields with a geodetic coordinate system. '

   175                                  'Distance objects; use a numeric value of your '

   176                                  'distance in degrees instead.')

   177             else:

   178                 dist_param = getattr(value, Distance.unit_attname(f.units_name(self.connection)))

   179         else:

   180             dist_param = value

   181         return [dist_param]

   182 

   183     def get_geom_placeholder(self, f, value):

   184         """

   185         Provides a proper substitution value for Geometries that are not in the

   186         SRID of the field.  Specifically, this routine will substitute in the

   187         Transform() and GeomFromText() function call(s).

   188         """

   189         def transform_value(value, srid):

   190             return not (value is None or value.srid == srid)

   191         if hasattr(value, 'expression'):

   192             if transform_value(value, f.srid):

   193                 placeholder = '%s(%%s, %s)' % (self.transform, f.srid)

   194             else:

   195                 placeholder = '%s'

   196             # No geometry value used for F expression, substitue in

   197             # the column name instead.

   198             return placeholder % '%s.%s' % tuple(map(self.quote_name, value.cols[value.expression]))

   199         else:

   200             if transform_value(value, f.srid):

   201                 # Adding Transform() to the SQL placeholder.

   202                 return '%s(%s(%%s,%s), %s)' % (self.transform, self.from_text, value.srid, f.srid)

   203             else:

   204                 return '%s(%%s,%s)' % (self.from_text, f.srid)

   205 

   206     def _get_spatialite_func(self, func):

   207         """

   208         Helper routine for calling SpatiaLite functions and returning

   209         their result.

   210         """

   211         cursor = self.connection._cursor()

   212         try:

   213             try:

   214                 cursor.execute('SELECT %s' % func)

   215                 row = cursor.fetchone()

   216             except:

   217                 # Responsibility of caller to perform error handling.

   218                 raise

   219         finally:

   220             cursor.close()

   221         return row[0]

   222 

   223     def geos_version(self):

   224         "Returns the version of GEOS used by SpatiaLite as a string."

   225         return self._get_spatialite_func('geos_version()')

   226 

   227     def proj4_version(self):

   228         "Returns the version of the PROJ.4 library used by SpatiaLite."

   229         return self._get_spatialite_func('proj4_version()')

   230 

   231     def spatialite_version(self):

   232         "Returns the SpatiaLite library version as a string."

   233         return self._get_spatialite_func('spatialite_version()')

   234 

   235     def spatialite_version_tuple(self):

   236         """

   237         Returns the SpatiaLite version as a tuple (version string, major,

   238         minor, subminor).

   239         """

   240         # Getting the SpatiaLite version.

   241         try:

   242             version = self.spatialite_version()

   243         except DatabaseError:

   244             # The `spatialite_version` function first appeared in version 2.3.1

   245             # of SpatiaLite, so doing a fallback test for 2.3.0 (which is

   246             # used by popular Debian/Ubuntu packages).

   247             version = None

   248             try:

   249                 tmp = self._get_spatialite_func("X(GeomFromText('POINT(1 1)'))")

   250                 if tmp == 1.0: version = '2.3.0'

   251             except DatabaseError:

   252                 pass

   253             # If no version string defined, then just re-raise the original

   254             # exception.

   255             if version is None: raise

   256 

   257         m = self.version_regex.match(version)

   258         if m:

   259             major = int(m.group('major'))

   260             minor1 = int(m.group('minor1'))

   261             minor2 = int(m.group('minor2'))

   262         else:

   263             raise Exception('Could not parse SpatiaLite version string: %s' % version)

   264 

   265         return (version, major, minor1, minor2)

   266 

   267     def spatial_aggregate_sql(self, agg):

   268         """

   269         Returns the spatial aggregate SQL template and function for the

   270         given Aggregate instance.

   271         """

   272         agg_name = agg.__class__.__name__

   273         if not self.check_aggregate_support(agg):

   274             raise NotImplementedError('%s spatial aggregate is not implmented for this backend.' % agg_name)

   275         agg_name = agg_name.lower()

   276         if agg_name == 'union': agg_name += 'agg'

   277         sql_template = self.select % '%(function)s(%(field)s)'

   278         sql_function = getattr(self, agg_name)

   279         return sql_template, sql_function

   280 

   281     def spatial_lookup_sql(self, lvalue, lookup_type, value, field, qn):

   282         """

   283         Returns the SpatiaLite-specific SQL for the given lookup value

   284         [a tuple of (alias, column, db_type)], lookup type, lookup

   285         value, the model field, and the quoting function.

   286         """

   287         alias, col, db_type = lvalue

   288 

   289         # Getting the quoted field as `geo_col`.

   290         geo_col = '%s.%s' % (qn(alias), qn(col))

   291 

   292         if lookup_type in self.geometry_functions:

   293             # See if a SpatiaLite geometry function matches the lookup type.

   294             tmp = self.geometry_functions[lookup_type]

   295 

   296             # Lookup types that are tuples take tuple arguments, e.g., 'relate' and

   297             # distance lookups.

   298             if isinstance(tmp, tuple):

   299                 # First element of tuple is the SpatiaLiteOperation instance, and the

   300                 # second element is either the type or a tuple of acceptable types

   301                 # that may passed in as further parameters for the lookup type.

   302                 op, arg_type = tmp

   303 

   304                 # Ensuring that a tuple _value_ was passed in from the user

   305                 if not isinstance(value, (tuple, list)):

   306                     raise ValueError('Tuple required for `%s` lookup type.' % lookup_type)

   307 

   308                 # Geometry is first element of lookup tuple.

   309                 geom = value[0]

   310 

   311                 # Number of valid tuple parameters depends on the lookup type.

   312                 if len(value) != 2:

   313                     raise ValueError('Incorrect number of parameters given for `%s` lookup type.' % lookup_type)

   314 

   315                 # Ensuring the argument type matches what we expect.

   316                 if not isinstance(value[1], arg_type):

   317                     raise ValueError('Argument type should be %s, got %s instead.' % (arg_type, type(value[1])))

   318 

   319                 # For lookup type `relate`, the op instance is not yet created (has

   320                 # to be instantiated here to check the pattern parameter).

   321                 if lookup_type == 'relate':

   322                     op = op(value[1])

   323                 elif lookup_type in self.distance_functions:

   324                     op = op[0]

   325             else:

   326                 op = tmp

   327                 geom = value

   328             # Calling the `as_sql` function on the operation instance.

   329             return op.as_sql(geo_col, self.get_geom_placeholder(field, geom))

   330         elif lookup_type == 'isnull':

   331             # Handling 'isnull' lookup type

   332             return "%s IS %sNULL" % (geo_col, (not value and 'NOT ' or ''))

   333 

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

   335 

   336     # Routines for getting the OGC-compliant models.

   337     def geometry_columns(self):

   338         from django.contrib.gis.db.backends.spatialite.models import GeometryColumns

   339         return GeometryColumns

   340 

   341     def spatial_ref_sys(self):

   342         from django.contrib.gis.db.backends.spatialite.models import SpatialRefSys

   343         return SpatialRefSys