1 """

     2  This module contains the spatial lookup types, and the get_geo_where_clause()

     3  routine for PostGIS.

     4 """

     5 

     6 import re

     7 from decimal import Decimal

     8 from django.db import connection

     9 from django.conf import settings

    10 from django.contrib.gis.measure import Distance

    11 from django.contrib.gis.db.backend.util import SpatialOperation, SpatialFunction

    12 

    13 qn = connection.ops.quote_name

    14 

    15 # Get the PostGIS version information.

    16 # To avoid the need to do a database query to determine the PostGIS version

    17 # each time the server starts up, one can optionally specify a

    18 # POSTGIS_VERSION setting. This setting is intentionally undocumented and

    19 # should be considered experimental, because an upcoming GIS backend

    20 # refactoring might remove the need for it.

    21 if hasattr(settings, 'POSTGIS_VERSION') and settings.POSTGIS_VERSION is not None:

    22     version_tuple = settings.POSTGIS_VERSION

    23 else:

    24     # This import is intentionally within the 'else' so that it isn't executed

    25     # if the POSTGIS_VERSION setting is available.

    26     from django.contrib.gis.db.backend.postgis.management import postgis_version_tuple

    27     version_tuple = postgis_version_tuple()

    28 POSTGIS_VERSION, MAJOR_VERSION, MINOR_VERSION1, MINOR_VERSION2 = version_tuple

    29 

    30 # The supported PostGIS versions.

    31 #  TODO: Confirm tests with PostGIS versions 1.1.x -- should work.  

    32 #        Versions <= 1.0.x do not use GEOS C API, and will not be supported.

    33 if MAJOR_VERSION != 1 or (MAJOR_VERSION == 1 and MINOR_VERSION1 < 1):

    34     raise Exception('PostGIS version %s not supported.' % POSTGIS_VERSION)

    35 

    36 # Versions of PostGIS >= 1.2.2 changed their naming convention to be

    37 #  'SQL-MM-centric' to conform with the ISO standard. Practically, this

    38 #  means that 'ST_' prefixes geometry function names.

    39 GEOM_FUNC_PREFIX = ''

    40 if MAJOR_VERSION >= 1:

    41     if (MINOR_VERSION1 > 2 or

    42         (MINOR_VERSION1 == 2 and MINOR_VERSION2 >= 2)):

    43         GEOM_FUNC_PREFIX = 'ST_'

    44 

    45     def get_func(func): return '%s%s' % (GEOM_FUNC_PREFIX, func)

    46 

    47     # Custom selection not needed for PostGIS because GEOS geometries are

    48     # instantiated directly from the HEXEWKB returned by default.  If

    49     # WKT is needed for some reason in the future, this value may be changed,

    50     # e.g,, 'AsText(%s)'.

    51     GEOM_SELECT = None

    52 

    53     # Functions used by the GeoManager & GeoQuerySet

    54     AREA = get_func('Area')

    55     ASGEOJSON = get_func('AsGeoJson')

    56     ASKML = get_func('AsKML')

    57     ASGML = get_func('AsGML')

    58     ASSVG = get_func('AsSVG')

    59     CENTROID = get_func('Centroid')

    60     COLLECT = get_func('Collect')

    61     DIFFERENCE = get_func('Difference')

    62     DISTANCE = get_func('Distance')

    63     DISTANCE_SPHERE = get_func('distance_sphere')

    64     DISTANCE_SPHEROID = get_func('distance_spheroid')

    65     ENVELOPE = get_func('Envelope')

    66     EXTENT = get_func('extent')

    67     GEOM_FROM_TEXT = get_func('GeomFromText')

    68     GEOM_FROM_WKB = get_func('GeomFromWKB')

    69     INTERSECTION = get_func('Intersection')

    70     LENGTH = get_func('Length')

    71     LENGTH_SPHEROID = get_func('length_spheroid')

    72     MAKE_LINE = get_func('MakeLine')

    73     MEM_SIZE = get_func('mem_size')

    74     NUM_GEOM = get_func('NumGeometries')

    75     NUM_POINTS = get_func('npoints')

    76     PERIMETER = get_func('Perimeter')

    77     POINT_ON_SURFACE = get_func('PointOnSurface')

    78     SCALE = get_func('Scale')

    79     SNAP_TO_GRID = get_func('SnapToGrid')

    80     SYM_DIFFERENCE = get_func('SymDifference')

    81     TRANSFORM = get_func('Transform')

    82     TRANSLATE = get_func('Translate')

    83 

    84     # Special cases for union, KML, and GeoJSON methods.

    85     if MINOR_VERSION1 < 3:

    86         UNIONAGG = 'GeomUnion'

    87         UNION = 'Union'

    88     else:

    89         UNIONAGG = 'ST_Union'

    90         UNION = 'ST_Union'

    91 

    92     if MINOR_VERSION1 == 1:

    93         ASKML = False

    94 

    95     # Only 1.3.4+ have AsGeoJson.

    96     if (MINOR_VERSION1 < 3 or 

    97         (MINOR_VERSION1 == 3 and MINOR_VERSION2 < 4)):

    98         ASGEOJSON = False

    99 else:

   100     raise NotImplementedError('PostGIS versions < 1.0 are not supported.')

   101 

   102 #### Classes used in constructing PostGIS spatial SQL ####

   103 class PostGISOperator(SpatialOperation):

   104     "For PostGIS operators (e.g. `&&`, `~`)."

   105     def __init__(self, operator):

   106         super(PostGISOperator, self).__init__(operator=operator, beg_subst='%s %s %%s')

   107 

   108 class PostGISFunction(SpatialFunction):

   109     "For PostGIS function calls (e.g., `ST_Contains(table, geom)`)."

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

   111         super(PostGISFunction, self).__init__(get_func(function), **kwargs)

   112 

   113 class PostGISFunctionParam(PostGISFunction):

   114     "For PostGIS functions that take another parameter (e.g. DWithin, Relate)."

   115     def __init__(self, func):

   116         super(PostGISFunctionParam, self).__init__(func, end_subst=', %%s)')

   117 

   118 class PostGISDistance(PostGISFunction):

   119     "For PostGIS distance operations."

   120     dist_func = 'Distance'

   121     def __init__(self, operator):

   122         super(PostGISDistance, self).__init__(self.dist_func, end_subst=') %s %s', 

   123                                               operator=operator, result='%%s')

   124 

   125 class PostGISSpheroidDistance(PostGISFunction):

   126     "For PostGIS spherical distance operations (using the spheroid)."

   127     dist_func = 'distance_spheroid'

   128     def __init__(self, operator):

   129         # An extra parameter in `end_subst` is needed for the spheroid string.

   130         super(PostGISSpheroidDistance, self).__init__(self.dist_func, 

   131                                                       beg_subst='%s(%s, %%s, %%s', 

   132                                                       end_subst=') %s %s',

   133                                                       operator=operator, result='%%s')

   134 

   135 class PostGISSphereDistance(PostGISFunction):

   136     "For PostGIS spherical distance operations."

   137     dist_func = 'distance_sphere'

   138     def __init__(self, operator):

   139         super(PostGISSphereDistance, self).__init__(self.dist_func, end_subst=') %s %s',

   140                                                     operator=operator, result='%%s')

   141                                                     

   142 class PostGISRelate(PostGISFunctionParam):

   143     "For PostGIS Relate(<geom>, <pattern>) calls."

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

   145     def __init__(self, pattern):

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

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

   148         super(PostGISRelate, self).__init__('Relate')

   149 

   150 #### Lookup type mapping dictionaries of PostGIS operations. ####

   151 

   152 # PostGIS-specific operators. The commented descriptions of these

   153 # operators come from Section 6.2.2 of the official PostGIS documentation.

   154 POSTGIS_OPERATORS = {

   155     # The "&<" operator returns true if A's bounding box overlaps or

   156     #  is to the left of B's bounding box.

   157     'overlaps_left' : PostGISOperator('&<'),

   158     # The "&>" operator returns true if A's bounding box overlaps or

   159     #  is to the right of B's bounding box.

   160     'overlaps_right' : PostGISOperator('&>'),

   161     # The "<<" operator returns true if A's bounding box is strictly

   162     #  to the left of B's bounding box.

   163     'left' : PostGISOperator('<<'),

   164     # The ">>" operator returns true if A's bounding box is strictly

   165     #  to the right of B's bounding box.

   166     'right' : PostGISOperator('>>'),

   167     # The "&<|" operator returns true if A's bounding box overlaps or

   168     #  is below B's bounding box.

   169     'overlaps_below' : PostGISOperator('&<|'),

   170     # The "|&>" operator returns true if A's bounding box overlaps or

   171     #  is above B's bounding box.

   172     'overlaps_above' : PostGISOperator('|&>'),

   173     # The "<<|" operator returns true if A's bounding box is strictly

   174     #  below B's bounding box.

   175     'strictly_below' : PostGISOperator('<<|'),

   176     # The "|>>" operator returns true if A's bounding box is strictly

   177     # above B's bounding box.

   178     'strictly_above' : PostGISOperator('|>>'),

   179     # The "~=" operator is the "same as" operator. It tests actual

   180     #  geometric equality of two features. So if A and B are the same feature,

   181     #  vertex-by-vertex, the operator returns true.

   182     'same_as' : PostGISOperator('~='),

   183     'exact' : PostGISOperator('~='),

   184     # The "@" operator returns true if A's bounding box is completely contained

   185     #  by B's bounding box.

   186     'contained' : PostGISOperator('@'),

   187     # The "~" operator returns true if A's bounding box completely contains

   188     #  by B's bounding box.

   189     'bbcontains' : PostGISOperator('~'),

   190     # The "&&" operator returns true if A's bounding box overlaps

   191     #  B's bounding box.

   192     'bboverlaps' : PostGISOperator('&&'),

   193     }

   194 

   195 # For PostGIS >= 1.2.2 the following lookup types will do a bounding box query

   196 # first before calling the more computationally expensive GEOS routines (called

   197 # "inline index magic"):

   198 # 'touches', 'crosses', 'contains', 'intersects', 'within', 'overlaps', and

   199 # 'covers'.

   200 POSTGIS_GEOMETRY_FUNCTIONS = {

   201     'equals' : PostGISFunction('Equals'),

   202     'disjoint' : PostGISFunction('Disjoint'),

   203     'touches' : PostGISFunction('Touches'),

   204     'crosses' : PostGISFunction('Crosses'),

   205     'within' : PostGISFunction('Within'),

   206     'overlaps' : PostGISFunction('Overlaps'),

   207     'contains' : PostGISFunction('Contains'),

   208     'intersects' : PostGISFunction('Intersects'),

   209     'relate' : (PostGISRelate, basestring),

   210     }

   211 

   212 # Valid distance types and substitutions

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

   214 def get_dist_ops(operator):

   215     "Returns operations for both regular and spherical distances."

   216     return (PostGISDistance(operator), PostGISSphereDistance(operator), PostGISSpheroidDistance(operator))

   217 DISTANCE_FUNCTIONS = {

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

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

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

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

   222     }

   223 

   224 if GEOM_FUNC_PREFIX == 'ST_':

   225     # The ST_DWithin, ST_CoveredBy, and ST_Covers routines become available in 1.2.2+

   226     POSTGIS_GEOMETRY_FUNCTIONS.update(

   227         {'coveredby' : PostGISFunction('CoveredBy'),

   228          'covers' : PostGISFunction('Covers'),

   229          })

   230     DISTANCE_FUNCTIONS['dwithin'] = (PostGISFunctionParam('DWithin'), dtypes)

   231 

   232 # Distance functions are a part of PostGIS geometry functions.

   233 POSTGIS_GEOMETRY_FUNCTIONS.update(DISTANCE_FUNCTIONS)

   234 

   235 # Any other lookup types that do not require a mapping.

   236 MISC_TERMS = ['isnull']

   237 

   238 # These are the PostGIS-customized QUERY_TERMS -- a list of the lookup types

   239 #  allowed for geographic queries.

   240 POSTGIS_TERMS = POSTGIS_OPERATORS.keys() # Getting the operators first

   241 POSTGIS_TERMS += POSTGIS_GEOMETRY_FUNCTIONS.keys() # Adding on the Geometry Functions

   242 POSTGIS_TERMS += MISC_TERMS # Adding any other miscellaneous terms (e.g., 'isnull')

   243 POSTGIS_TERMS = dict((term, None) for term in POSTGIS_TERMS) # Making a dictionary for fast lookups

   244 

   245 # For checking tuple parameters -- not very pretty but gets job done.

   246 def exactly_two(val): return val == 2

   247 def two_to_three(val): return val >= 2 and val <=3

   248 def num_params(lookup_type, val):

   249     if lookup_type in DISTANCE_FUNCTIONS and lookup_type != 'dwithin': return two_to_three(val)

   250     else: return exactly_two(val)

   251 

   252 #### The `get_geo_where_clause` function for PostGIS. ####

   253 def get_geo_where_clause(table_alias, name, lookup_type, geo_annot):

   254     "Returns the SQL WHERE clause for use in PostGIS SQL construction."

   255     # Getting the quoted field as `geo_col`.

   256     geo_col = '%s.%s' % (qn(table_alias), qn(name))

   257     if lookup_type in POSTGIS_OPERATORS:

   258         # See if a PostGIS operator matches the lookup type.

   259         return POSTGIS_OPERATORS[lookup_type].as_sql(geo_col)

   260     elif lookup_type in POSTGIS_GEOMETRY_FUNCTIONS:

   261         # See if a PostGIS geometry function matches the lookup type.

   262         tmp = POSTGIS_GEOMETRY_FUNCTIONS[lookup_type]

   263 

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

   265         # distance lookups.

   266         if isinstance(tmp, tuple):

   267             # First element of tuple is the PostGISOperation instance, and the

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

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

   270             op, arg_type = tmp

   271 

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

   273             if not isinstance(geo_annot.value, (tuple, list)): 

   274                 raise TypeError('Tuple required for `%s` lookup type.' % lookup_type)

   275            

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

   277             nparams = len(geo_annot.value)

   278             if not num_params(lookup_type, nparams):

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

   280             

   281             # Ensuring the argument type matches what we expect.

   282             if not isinstance(geo_annot.value[1], arg_type):

   283                 raise TypeError('Argument type should be %s, got %s instead.' % (arg_type, type(geo_annot.value[1])))

   284 

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

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

   287             if lookup_type == 'relate': 

   288                 op = op(geo_annot.value[1])

   289             elif lookup_type in DISTANCE_FUNCTIONS and lookup_type != 'dwithin':

   290                 if geo_annot.geodetic:

   291                     # Geodetic distances are only availble from Points to PointFields.

   292                     if geo_annot.geom_type != 'POINT':

   293                         raise TypeError('PostGIS spherical operations are only valid on PointFields.')

   294                     if geo_annot.value[0].geom_typeid != 0:

   295                         raise TypeError('PostGIS geometry distance parameter is required to be of type Point.')

   296                     # Setting up the geodetic operation appropriately.

   297                     if nparams == 3 and geo_annot.value[2] == 'spheroid': op = op[2]

   298                     else: op = op[1]

   299                 else:

   300                     op = op[0]

   301         else:

   302             op = tmp

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

   304         return op.as_sql(geo_col)

   305     elif lookup_type == 'isnull':

   306         # Handling 'isnull' lookup type

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

   308 

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