Skip to content

Commit c049672

Browse files
jbronnjbronn
committed
gis: spatial-backend enhancements:
 (1) GEOS no longer has psycopg2-specific routines, functionality now part of PostGIS adaptor in the spatial backend. (2) ST_GeomFromWKB() now used to enhance performance. (3) Moved GeometryProxy back to its original location. (4) Should resolve #5498, but not yet confirmed. (5) Test-sql files are now backend-specific. git-svn-id: http://code.djangoproject.com/svn/django/branches/gis@6508 bcc190cf-cafb-0310-a4f2-bffc1f526a37
1 parent b6c8bba commit c049672

File tree

15 files changed

+219
-144
lines changed

15 files changed

+219
-144
lines changed

‎django/contrib/gis/db/backend/__init__.py

Lines changed: 34 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -11,32 +11,41 @@
1111
the backend.
1212
(4) The `parse_lookup` function, used for spatial SQL construction by
1313
the GeoQuerySet.
14-
(5) The `create_spatial_db`, `geo_quotename`, and `get_geo_where_clause`
15-
routines (needed by `parse_lookup`.
14+
(5) The `create_spatial_db`, and `get_geo_where_clause`
15+
routines (needed by `parse_lookup`).
1616
1717
Currently only PostGIS is supported, but someday backends will be added for
1818
additional spatial databases (e.g., Oracle, DB2).
1919
"""
20+
from types import StringType, UnicodeType
2021
from django.conf import settings
2122
from django.db import connection
2223
from django.db.models.query import field_choices, find_field, get_where_clause, \
2324
FieldFound, LOOKUP_SEPARATOR, QUERY_TERMS
2425
from django.utils.datastructures import SortedDict
26+
from django.contrib.gis.geos import GEOSGeometry
2527

26-
# These routines default to False
27-
ASGML, ASKML, UNION = (False, False, False)
28+
# These routines (needed by GeoManager), default to False.
29+
ASGML, ASKML, TRANSFORM, UNION= (False, False, False, False)
2830

2931
if settings.DATABASE_ENGINE == 'postgresql_psycopg2':
3032
# PostGIS is the spatial database, getting the rquired modules,
3133
# renaming as necessary.
3234
from django.contrib.gis.db.backend.postgis import \
3335
PostGISField as GeoBackendField, POSTGIS_TERMS as GIS_TERMS, \
34-
PostGISProxy as GeometryProxy, \
35-
create_spatial_db, geo_quotename, get_geo_where_clause, \
36-
ASGML, ASKML, UNION
36+
create_spatial_db, get_geo_where_clause, gqn, \
37+
ASGML, ASKML, GEOM_SELECT, TRANSFORM, UNION
3738
else:
3839
raise NotImplementedError('No Geographic Backend exists for %s' % settings.DATABASE_NAME)
3940

41+
def geo_quotename(value):
42+
"""
43+
Returns the quotation used on a given Geometry value using the geometry
44+
quoting from the backend (the `gqn` function).
45+
"""
46+
if isinstance(value, (StringType, UnicodeType)): return gqn(value)
47+
else: return str(value)
48+
4049
#### query.py overloaded functions ####
4150
# parse_lookup() and lookup_inner() are modified from their django/db/models/query.py
4251
# counterparts to support constructing SQL for geographic queries.
@@ -263,38 +272,29 @@ def lookup_inner(path, lookup_type, value, opts, table, column):
263272
# If the field is a geometry field, then the WHERE clause will need to be obtained
264273
# with the get_geo_where_clause()
265274
if hasattr(field, '_geom'):
266-
# Do we have multiple arguments, e.g., ST_Relate, ST_DWithin lookup types
267-
# need more than argument.
275+
# Do we have multiple arguments, e.g., `relate`, `dwithin` lookup types
276+
# need more than argument.
268277
multiple_args = isinstance(value, tuple)
269278

270-
# Getting the geographic where clause.
271-
gwc = get_geo_where_clause(lookup_type, current_table + '.', column, value)
272-
273-
# Getting the geographic parameters from the field.
279+
# Getting the preparation SQL object from the field.
274280
if multiple_args:
275-
geo_params = field.get_db_prep_lookup(lookup_type, value[0])
281+
geo_prep = field.get_db_prep_lookup(lookup_type, value[0])
276282
else:
277-
geo_params = field.get_db_prep_lookup(lookup_type, value)
278-
279-
# If a dictionary was passed back from the field modify the where clause.
280-
param_dict = isinstance(geo_params, dict)
281-
if param_dict:
282-
subst_list = geo_params['where']
283-
if multiple_args: subst_list += map(geo_quotename, value[1:])
284-
geo_params = geo_params['params']
285-
gwc = gwc % tuple(subst_list)
286-
elif multiple_args:
287-
# Modify the where clause if we have multiple arguments -- the
288-
# first substitution will be for another placeholder (for the
289-
# geometry) since it is already apart of geo_params.
290-
subst_list = ['%s']
291-
subst_list += map(geo_quotename, value[1:])
292-
gwc = gwc % tuple(subst_list)
293-
294-
# Finally, appending onto the WHERE clause, and extending with any
295-
# additional parameters.
283+
geo_prep = field.get_db_prep_lookup(lookup_type, value)
284+
285+
# Getting the adapted geometry from the field.
286+
gwc = get_geo_where_clause(lookup_type, current_table + '.', column, value)
287+
288+
# A GeoFieldSQL object is returned by `get_db_prep_lookup` --
289+
# getting the substitution list and the geographic parameters.
290+
subst_list = geo_prep.where
291+
if multiple_args: subst_list += map(geo_quotename, value[1:])
292+
gwc = gwc % tuple(subst_list)
293+
294+
# Finally, appending onto the WHERE clause, and extending with
295+
# the additional parameters.
296296
where.append(gwc)
297-
params.extend(geo_params)
297+
params.extend(geo_prep.params)
298298
else:
299299
where.append(get_where_clause(lookup_type, current_table + '.', column, value, db_type))
300300
params.extend(field.get_db_prep_lookup(lookup_type, value))

‎django/contrib/gis/db/backend/postgis/__init__.py

Lines changed: 5 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1,24 +1,10 @@
11
"""
22
The PostGIS spatial database backend module.
33
"""
4-
from django.contrib.gis.db.backend.postgis.query import \
5-
get_geo_where_clause, geo_quotename, \
6-
GEOM_FUNC_PREFIX, POSTGIS_TERMS, \
7-
MAJOR_VERSION, MINOR_VERSION1, MINOR_VERSION2
84
from django.contrib.gis.db.backend.postgis.creation import create_spatial_db
9-
from django.contrib.gis.db.backend.postgis.field import PostGISField
5+
from django.contrib.gis.db.backend.postgis.field import PostGISField, gqn
106
from django.contrib.gis.db.backend.postgis.proxy import PostGISProxy
11-
12-
# Functions used by GeoManager methods, and not via lookup types.
13-
if MAJOR_VERSION == 1:
14-
if MINOR_VERSION1 == 3:
15-
ASKML = 'ST_AsKML'
16-
ASGML = 'ST_AsGML'
17-
UNION = 'ST_Union'
18-
elif MINOR_VERSION1 == 2 and MINOR_VERSION2 >= 1:
19-
ASKML = 'AsKML'
20-
ASGML = 'AsGML'
21-
UNION = 'GeomUnion'
22-
23-
24-
7+
from django.contrib.gis.db.backend.postgis.query import \
8+
get_geo_where_clause, GEOM_FUNC_PREFIX, POSTGIS_TERMS, \
9+
MAJOR_VERSION, MINOR_VERSION1, MINOR_VERSION2, \
10+
ASKML, ASGML, GEOM_FROM_TEXT, UNION, TRANSFORM, GEOM_SELECT

‎django/contrib/gis/db/backend/postgis/adaptor.py

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
"""
2+
This object provides quoting for GEOS geometries into PostgreSQL/PostGIS.
3+
"""
4+
5+
from django.contrib.gis.db.backend.postgis.query import GEOM_FROM_WKB
6+
from psycopg2 import Binary
7+
from psycopg2.extensions import ISQLQuote
8+
9+
class PostGISAdaptor(object):
10+
def __init__(self, geom, srid):
11+
"Initializes on the geometry and the SRID."
12+
# Getting the WKB and the SRID
13+
self.wkb = geom.wkb
14+
self.srid = srid
15+
16+
def __conform__(self, proto):
17+
# Does the given protocol conform to what Psycopg2 expects?
18+
if proto == ISQLQuote:
19+
return self
20+
else:
21+
raise Exception('Error implementing psycopg2 protocol. Is psycopg2 installed?')
22+
23+
def __str__(self):
24+
return self.getquoted()
25+
26+
def getquoted(self):
27+
"Returns a properly quoted string for use in PostgreSQL/PostGIS."
28+
# Want to use WKB, so wrap with psycopg2 Binary() to quote properly.
29+
return "%s(%s, %s)" % (GEOM_FROM_WKB, Binary(self.wkb), self.srid or -1)

‎django/contrib/gis/db/backend/postgis/field.py

Lines changed: 58 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,17 @@
1+
from types import StringType, UnicodeType
2+
from django.db import connection
13
from django.db.models.fields import Field # Django base Field class
24
from django.contrib.gis.geos import GEOSGeometry, GEOSException
3-
from django.contrib.gis.db.backend.postgis.query import POSTGIS_TERMS, geo_quotename as quotename
4-
from types import StringType
5+
from django.contrib.gis.db.backend.util import GeoFieldSQL
6+
from django.contrib.gis.db.backend.postgis.adaptor import PostGISAdaptor
7+
from django.contrib.gis.db.backend.postgis.query import POSTGIS_TERMS, TRANSFORM
8+
from psycopg2 import Binary
9+
10+
# Quotename & geographic quotename, respectively
11+
qn = connection.ops.quote_name
12+
def gqn(value):
13+
if isinstance(value, UnicodeType): value = value.encode('ascii')
14+
return "'%s'" % value
515

616
class PostGISField(Field):
717
def _add_geom(self, style, db_table):
@@ -10,36 +20,36 @@ def _add_geom(self, style, db_table):
1020
AddGeometryColumn(...) PostGIS (and OGC standard) stored procedure.
1121
1222
Takes the style object (provides syntax highlighting) and the
13-
database table as parameters.
23+
database table as parameters.
1424
"""
1525
sql = style.SQL_KEYWORD('SELECT ') + \
1626
style.SQL_TABLE('AddGeometryColumn') + '(' + \
17-
style.SQL_TABLE(quotename(db_table)) + ', ' + \
18-
style.SQL_FIELD(quotename(self.column)) + ', ' + \
27+
style.SQL_TABLE(gqn(db_table)) + ', ' + \
28+
style.SQL_FIELD(gqn(self.column)) + ', ' + \
1929
style.SQL_FIELD(str(self._srid)) + ', ' + \
20-
style.SQL_COLTYPE(quotename(self._geom)) + ', ' + \
30+
style.SQL_COLTYPE(gqn(self._geom)) + ', ' + \
2131
style.SQL_KEYWORD(str(self._dim)) + ');'
2232

2333
if not self.null:
2434
# Add a NOT NULL constraint to the field
2535
sql += '\n' + \
2636
style.SQL_KEYWORD('ALTER TABLE ') + \
27-
style.SQL_TABLE(quotename(db_table, dbl=True)) + \
37+
style.SQL_TABLE(qn(db_table)) + \
2838
style.SQL_KEYWORD(' ALTER ') + \
29-
style.SQL_FIELD(quotename(self.column, dbl=True)) + \
39+
style.SQL_FIELD(qn(self.column)) + \
3040
style.SQL_KEYWORD(' SET NOT NULL') + ';'
3141
return sql
3242

3343
def _geom_index(self, style, db_table,
3444
index_type='GIST', index_opts='GIST_GEOMETRY_OPS'):
3545
"Creates a GiST index for this geometry field."
3646
sql = style.SQL_KEYWORD('CREATE INDEX ') + \
37-
style.SQL_TABLE(quotename('%s_%s_id' % (db_table, self.column), dbl=True)) + \
47+
style.SQL_TABLE(qn('%s_%s_id' % (db_table, self.column))) + \
3848
style.SQL_KEYWORD(' ON ') + \
39-
style.SQL_TABLE(quotename(db_table, dbl=True)) + \
49+
style.SQL_TABLE(qn(db_table)) + \
4050
style.SQL_KEYWORD(' USING ') + \
4151
style.SQL_COLTYPE(index_type) + ' ( ' + \
42-
style.SQL_FIELD(quotename(self.column, dbl=True)) + ' ' + \
52+
style.SQL_FIELD(qn(self.column)) + ' ' + \
4353
style.SQL_KEYWORD(index_opts) + ' );'
4454
return sql
4555

@@ -64,8 +74,8 @@ def _post_delete_sql(self, style, db_table):
6474
"Drops the geometry column."
6575
sql = style.SQL_KEYWORD('SELECT ') + \
6676
style.SQL_KEYWORD('DropGeometryColumn') + '(' + \
67-
style.SQL_TABLE(quotename(db_table)) + ', ' + \
68-
style.SQL_FIELD(quotename(self.column)) + ');'
77+
style.SQL_TABLE(gqn(db_table)) + ', ' + \
78+
style.SQL_FIELD(gqn(self.column)) + ');'
6979
return sql
7080

7181
def db_type(self):
@@ -81,51 +91,63 @@ def get_db_prep_lookup(self, lookup_type, value):
8191
GEOS Geometries for the value.
8292
"""
8393
if lookup_type in POSTGIS_TERMS:
84-
if lookup_type == 'isnull': return [value] # special case for NULL geometries.
85-
if not bool(value): return [None] # If invalid value passed in.
94+
# special case for isnull lookup
95+
if lookup_type == 'isnull':
96+
return GeoFieldSQL([], [value])
97+
98+
# When the input is not a GEOS geometry, attempt to construct one
99+
# from the given string input.
86100
if isinstance(value, GEOSGeometry):
87-
# GEOSGeometry instance passed in.
88-
if value.srid != self._srid:
89-
# Returning a dictionary instructs the parse_lookup() to add
90-
# what's in the 'where' key to the where parameters, since we
91-
# need to transform the geometry in the query.
92-
return {'where' : ["ST_Transform(%s,%s)"],
93-
'params' : [value, self._srid]
94-
}
95-
else:
96-
# Just return the GEOSGeometry, it has its own psycopg2 adaptor.
97-
return [value]
98-
elif isinstance(value, StringType):
99-
# String instance passed in, assuming WKT.
100-
# TODO: Any validation needed here to prevent SQL injection?
101-
return ["SRID=%d;%s" % (self._srid, value)]
101+
pass
102+
elif isinstance(value, (StringType, UnicodeType)):
103+
try:
104+
value = GEOSGeometry(value)
105+
except GEOSException:
106+
raise TypeError("Could not create geometry from lookup value: %s" % str(value))
107+
else:
108+
raise TypeError('Cannot use parameter of %s type as lookup parameter.' % type(value))
109+
110+
# Getting the SRID of the geometry, or defaulting to that of the field if
111+
# it is None.
112+
if value.srid is None: srid = self._srid
113+
else: srid = value.srid
114+
115+
# The adaptor will be used by psycopg2 for quoting the WKB.
116+
adapt = PostGISAdaptor(value, srid)
117+
118+
if srid != self._srid:
119+
# Adding the necessary string substitutions and parameters
120+
# to perform a geometry transformation.
121+
return GeoFieldSQL(['%s(%%s,%%s)' % TRANSFORM],
122+
[adapt, self._srid])
102123
else:
103-
raise TypeError("Invalid type (%s) used for field lookup value." % str(type(value)))
124+
return GeoFieldSQL(['%s'], [adapt])
104125
else:
105126
raise TypeError("Field has invalid lookup: %s" % lookup_type)
106127

107128
def get_db_prep_save(self, value):
108129
"Prepares the value for saving in the database."
109130
if not bool(value): return None
110131
if isinstance(value, GEOSGeometry):
111-
return value
132+
return PostGISAdaptor(value, value.srid)
112133
else:
113134
raise TypeError('Geometry Proxy should only return GEOSGeometry objects.')
114135

115136
def get_internal_type(self):
116137
"""
117-
Returns NoField because a stored procedure is used by PostGIS to create the
138+
Returns NoField because a stored procedure is used by PostGIS to create
139+
the Geometry Fields.
118140
"""
119141
return 'NoField'
120142

121143
def get_placeholder(self, value):
122144
"""
123145
Provides a proper substitution value for Geometries that are not in the
124-
SRID of the field. Specifically, this routine will substitute in the
125-
ST_Transform() function call.
146+
SRID of the field. Specifically, this routine will substitute in the
147+
ST_Transform() function call.
126148
"""
127149
if isinstance(value, GEOSGeometry) and value.srid != self._srid:
128150
# Adding Transform() to the SQL placeholder.
129-
return 'ST_Transform(%%s, %s)' % self._srid
151+
return '%s(%%s, %s)' % (TRANSFORM, self._srid)
130152
else:
131153
return '%s'

0 commit comments

Comments
 (0)