def encode_feature(self, feature):
if hasattr(self.datasource, 'srid_out') and self.datasource.srid_out is not None:
srs = self.datasource.srid_out
else:
if hasattr(feature, "geometry_attr"):
srs = str(feature.srs);
if 'EPSG' in srs:
srs = srs[5:]
else:
srs = 4326
wkt = "GeomFromText('" + WKT.to_wkt(feature.geometry) + "', %i)" % int(srs)
sql = "INSERT INTO featureserver (fid, "
for key, value in feature.properties.items():
if key != "geometry":
sql += "%s, " % key
sql += "geometry"
sql += ") VALUES ('%s', " % self.escapeSQL(str(feature.id).encode('utf-8'))
for key, value in feature.properties.items():
#key = self.getFormatedAttributName(key)
if value == None:
sql += "null, "
else:
sql += "'" + self.escapeSQL(value.encode('utf-8')) + "', "
sql += wkt
sql += ");"
self._cursor.execute(sql)
def insert(self, action):
self.begin()
if action.feature != None:
feature = action.feature
columns = ", ".join(self.column_names(feature) + [self.geom_col])
values = ", ".join(
self.value_formats(feature) + [
"ST_SetSRID('%s'::geometry, %s) " %
(WKT.to_wkt(feature.geometry), self.srid)
])
sql = "INSERT INTO \"%s\" (%s) VALUES (%s)" % (self.table, columns,
values)
cursor = self.db.cursor()
cursor.execute(str(sql), self.feature_values(feature))
cursor.execute("SELECT currval('%s');" % self.id_sequence())
action.id = cursor.fetchone()[0]
return InsertResult(action.id, "")
elif action.wfsrequest != None:
sql = action.wfsrequest.getStatement(self)
cursor = self.db.cursor()
cursor.execute(str(sql))
cursor.execute("SELECT currval('%s');" % self.id_sequence())
action.id = cursor.fetchone()[0]
return InsertResult(action.id, "")
return None
def encode_feature(self, feature):
if hasattr(self.datasource,
'srid_out') and self.datasource.srid_out is not None:
srs = self.datasource.srid_out
else:
if hasattr(feature, "geometry_attr"):
srs = str(feature.srs)
if 'EPSG' in srs:
srs = srs[5:]
else:
srs = 4326
wkt = "GeomFromText('" + WKT.to_wkt(
feature.geometry) + "', %i)" % int(srs)
sql = "INSERT INTO featureserver ("
for key, value in feature.properties.items():
if key != "geometry":
sql += "%s, " % key
sql += "geometry"
sql += ") VALUES ("
for key, value in feature.properties.items():
#key = self.getFormatedAttributName(key)
if value == None:
sql += "null, "
else:
sql += "'" + self.escapeSQL(value.encode('utf-8')) + "', "
sql += wkt
sql += ");"
self._cursor.execute(sql)
def wkt_to_geojson(wktStr):
"""Return GeoJSON from a WKT string."""
# Create instances of vectorfeatures WKT/GeoJSON Classes
inWKT = WKT.WKT()
outGeoJSON = GeoJSON.GeoJSON()
# Decode WKT to vectorformats features, and re-encode as GeoJSON
return outGeoJSON.encode(inWKT.decode(wktStr), to_string=True)
def insert(self, action, response=None):
self.begin()
if action.feature != None:
feature = action.feature
columns = ", ".join(self.column_names(feature) + [self.geom_col])
values = ", ".join(
self.value_formats(feature)
+ ["SetSRID('%s'::geometry, %s) " % (WKT.to_wkt(feature.geometry), self.srid)]
)
sql = 'INSERT INTO "%s" (%s) VALUES (%s)' % (self.table, columns, values)
cursor = self.db.cursor()
cursor.execute(str(sql), self.feature_values(feature))
cursor.execute("SELECT currval('%s');" % self.id_sequence())
action.id = cursor.fetchone()[0]
self.db.commit()
return self.select(action)
elif action.wfsrequest != None:
sql = action.wfsrequest.getStatement(self)
cursor = self.db.cursor()
cursor.execute(str(sql))
cursor.execute("SELECT currval('%s');" % self.id_sequence())
action.id = cursor.fetchone()[0]
self.db.commit()
response.addInsertResult(ActionResult(action.id, ""))
response.getSummary().increaseInserted()
return self.select(action)
return None
def update (self, action):
model = __import__(self.model, fromlist=['*'])
cls = getattr(model, self.cls)
feature = action.feature
obj = self.session.query(cls).get(int(action.id))
for prop in feature.properties.keys():
setattr(obj, prop, feature.properties[prop])
if self.geom_rel and self.geom_cls:
geom_obj = getattr(obj, self.geom_rel)
setattr(geom_obj, self.geom_col, WKT.to_wkt(feature.geometry))
self.session.add(geom_obj)
elif feature.geometry:
setattr(obj, self.geom_col, WKT.to_wkt(feature.geometry))
else:
pass
self.session.add(obj)
return self.select(action)
def update(self, action):
model = __import__(self.model, fromlist=['*'])
cls = getattr(model, self.cls)
feature = action.feature
obj = self.session.query(cls).get(int(action.id))
for prop in feature.properties.keys():
setattr(obj, prop, feature.properties[prop])
if self.geom_rel and self.geom_cls:
geom_obj = getattr(obj, self.geom_rel)
setattr(geom_obj, self.geom_col, WKT.to_wkt(feature.geometry))
self.session.add(geom_obj)
elif feature.geometry:
setattr(obj, self.geom_col, WKT.to_wkt(feature.geometry))
else:
pass
self.session.add(obj)
return self.select(action)
def feature_predicates (self, feature):
columns = self.column_names(feature)
values = self.value_formats(feature)
predicates = []
for pair in zip(columns, values):
if pair[0] != self.geom_col:
predicates.append(" %s = %s" % pair)
else:
predicates.append(" %s = %s " % (self.geom_col, WKT.to_wkt(feature.geometry)))
return predicates
def update (self, action):
feature = action.feature
sql = """UPDATE %s SET %s = SetSRID(%%(geom)s::geometry, %s),
attrs = %%(attrs)s WHERE %s = %(id)d""" % (
self.table, self.geom_col, self.srid, self.fid_col )
values = {'geom' : WKT.to_wkt(feature.geometry),
'id' : action.id,
'attrs': self._serializeattrs(feature.properties)}
cursor = self.db.cursor()
cursor.execute(str(sql), values)
return self.select(action)
def create (self, action):
feature = action.feature
values = {'geom' : WKT.to_wkt(feature.geometry),
'uuid' : uuid.uuid1().hex,
'attrs': self._serializeattrs(feature.properties)}
sql = """INSERT INTO %s (%s, uuid, attrs)
VALUES (SetSRID(%%(geom)s::geometry, %s),
%%(uuid)s, %%(attrs)s)""" % (
self.table, self.geom_col, self.srid)
cursor = self.db.cursor()
cursor.execute(str(sql), values)
return {}
def select (self, action):
cursor = self.db.cursor()
if action.id is not None:
sql = "SELECT AsText(%s) as fs_binary_geom_col, * FROM %s WHERE %s = %%(%s)d" % (
self.geom_col, self.table, self.fid_col, self.fid_col )
cursor.execute(str(sql), {self.fid_col: action.id})
result = [cursor.fetchone()]
else:
filters = []
attrs = {}
if action.bbox:
filters.append( "%s && SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s) and intersects(%s, SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s))" % (
(self.geom_col,) + tuple(action.bbox) + (self.srid,) + (self.geom_col,) + (tuple(action.bbox) + (self.srid,))))
if action.attributes:
match = Feature(props = action.attributes)
filters = self.feature_predicates(match)
attrs = action.attributes
sql = "SELECT AsText(%s) as fs_binary_geom_col, uuid, id, attrs FROM %s" % (self.geom_col, self.table)
#if filters:
# sql += " WHERE " + " AND ".join(filters)
if self.order:
sql += self.order
if action.maxfeatures:
sql += " LIMIT %d" % action.maxfeatures
else:
sql += " LIMIT 1000"
if action.startfeature:
sql += " OFFSET %d" % action.startfeature
cursor.execute(str(sql), attrs)
result = cursor.fetchall() # should use fetchmany(action.maxfeatures)
columns = [desc[0] for desc in cursor.description]
features = []
for row in result:
props = dict(zip(columns, row))
geom = WKT.from_wkt(props['fs_binary_geom_col'])
if props.has_key(self.geom_col): del props[self.geom_col]
del props['fs_binary_geom_col']
props.update(self._deserializeattrs(props['attrs']))
del props['attrs']
fid = props[self.fid_col]
del props[self.fid_col]
for key, value in props.items():
if isinstance(value, str):
props[key] = unicode(value, "utf-8")
features.append( Feature( fid, geom, props ) )
return features
def insert(self, action):
model = __import__(self.model, fromlist=['*'])
cls = getattr(model, self.cls)
feature = action.feature
obj = cls()
for prop in feature.properties.keys():
setattr(obj, prop, feature.properties[prop])
if self.geom_rel and self.geom_cls:
geom_cls = getattr(model, self.geom_cls)
geom_obj = geom_cls()
setattr(geom_obj, self.geom_col, WKT.to_wkt(feature.geometry))
try:
getattr(obj, self.geom_rel).append(geom_obj)
except:
# Handle specific exception
setattr(obj, self.geom_rel, geom_obj)
self.session.add(geom_obj)
elif feature.geometry:
setattr(obj, self.geom_col, WKT.to_wkt(feature.geometry))
else:
pass
self.session.add(obj)
return self.select(action)
def create (self, action):
model = __import__(self.model, fromlist=['*'])
cls = getattr(model, self.cls)
feature = action.feature
obj = cls()
for prop in feature.properties.keys():
setattr(obj, prop, feature.properties[prop])
if self.geom_rel and self.geom_cls:
geom_cls = getattr(model, self.geom_cls)
geom_obj = geom_cls()
setattr(geom_obj, self.geom_col, WKT.to_wkt(feature.geometry))
try:
getattr(obj, self.geom_rel).append(geom_obj)
except:
# Handle specific exception
setattr(obj, self.geom_rel, geom_obj)
self.session.add(geom_obj)
elif feature.geometry:
setattr(obj, self.geom_col, WKT.to_wkt(feature.geometry))
else:
pass
self.session.add(obj)
return self.select(action)
def create(self, action):
feature = action.feature
columns = ", ".join(self.column_names(feature) + [self.geom_col])
values = ", ".join(
self.value_formats(feature) + [
"SetSRID('%s'::geometry, %s) " %
(WKT.to_wkt(feature.geometry), self.srid)
])
sql = "INSERT INTO \"%s\" (%s) VALUES (%s)" % (self.table, columns,
values)
cursor = self.db.cursor()
cursor.execute(str(sql), self.feature_values(feature))
cursor.execute("SELECT currval('%s');" % self.id_sequence())
action.id = cursor.fetchone()[0]
self.db.commit()
return self.select(action)
def feature_predicates (self, feature):
columns = self.column_names(feature)
values = self.value_formats(feature)
predicates = []
for pair in zip(columns, values):
if pair[0] != self.geom_col:
if isinstance(pair[1], dict):
# Special Query: pair[0] is 'a', pair[1] is {'type', 'pred', 'value'}
# We build a Predicate here, then we replace pair[1] with pair[1] value below
if pair[1].has_key('value'):
predicates.append("%s %s %s" % (pair[0],
self.query_action_sql[pair[1]['type']],
pair[1]['pred']))
else:
predicates.append("%s = %s" % pair)
if feature.geometry.has_key("coordinates"):
predicates.append(" %s = SetSRID('%s'::geometry, %s) " % (self.geom_col, WKT.to_wkt(feature.geometry), self.srid))
return predicates
def insert (self, action):
feature = action.feature
bbox = feature.get_bbox()
columns = ", ".join([self.geom_col,'xmin,ymin,xmax,ymax'])
values = [WKT.to_wkt(feature.geometry)] + list(bbox)
sql = "INSERT INTO \"%s\" (%s) VALUES (?,?,?,?,?)" % ( self.table, columns)
cursor = self.db.cursor()
res = cursor.execute(str(sql), values)
action.id = res.lastrowid
#self.db.commit()
insert_tuples = [(res.lastrowid, k, v) for k,v in feature.properties.items()]
sql = "INSERT INTO \"%s_attrs\" (feature_id, key, value) VALUES (?, ?, ?)" % (self.table,)
cursor.executemany(sql,insert_tuples)
#self.db.commit()
return self.select(action)
def feature_predicates (self, feature):
columns = self.column_names(feature)
values = self.value_formats(feature)
predicates = []
for pair in zip(columns, values):
if pair[0] != self.geom_col:
if isinstance(pair[1], dict):
# Special Query: pair[0] is 'a', pair[1] is {'type', 'pred', 'value'}
# We build a Predicate here, then we replace pair[1] with pair[1] value below
if pair[1].has_key('value'):
predicates.append("%s %s %s" % (pair[1]['column'],
self.query_action_sql[pair[1]['type']],
pair[1]['pred']))
else:
predicates.append("%s = %s" % pair)
if feature.geometry and feature.geometry.has_key("coordinates"):
predicates.append(" %s = SetSRID('%s'::geometry, %s) " % (self.geom_col, WKT.to_wkt(feature.geometry), self.srid))
return predicates
def update (self, action):
feature = action.feature
bbox = feature.get_bbox()
predicates = self.feature_predicates(feature)
# this assumes updates can not introduce new attrs.... fix?
sql = "UPDATE \"%s_attrs\" SET value = :value WHERE key = :key AND %s = %d" % (
self.table, self.fid_col, action.id )
cursor = self.db.cursor()
predicate_list = []
for i in range(0, len(predicates) - 1, 2):
predicate_list.append( dict(key=predicates[i], value=predicates[i+1]) )
cursor.executemany(str(sql), predicate_list)
# should check if changed before doing this ...
geom_sql = "UPDATE %s SET %s = ?, xmin = ?, ymin = ?, xmax = ?, ymax = ? WHERE %s = %d" \
% (self.table, self.geom_col, self.fid_col, action.id)
cursor.execute(geom_sql, [WKT.to_wkt(feature.geometry)] + list(bbox))
#self.db.commit()
return self.select(action)
def select (self, action):
if not hasattr (action, 'parameters') or not action.parameters.has_key('area_filter'):
raise ApplicationException("Missing spatial area filter, supply a Well Known Text geometry for 'area_filter'")
else:
cursor = self.db.cursor()
# validate input WKT string
if not action.parameters['area_filter'].find ('POLYGON ('):
raise ApplicationException ("Invalid spatial filter, should be Well Known Text respresentation of a Polygon")
sql = "SELECT %s AS id, ST_AsText(%s) as fs_text_geom, %s FROM \"%s\"" % (self.fid_col, self.geom_col, self.attribute_cols, self.table)
sql += "('%s')" % action.parameters['area_filter']
try:
cursor.execute(str(sql))
except Exception, e:
raise ApplicationException ("<em>Internal error:</em><br><pre>%s</pre>" % (str (e)))
result = cursor.fetchall()
# column names from cursor
columns = [desc[0] for desc in cursor.description]
# output features to return
features = []
for row in result:
if not row: continue
# convert result set row record into dictionary
props = dict(zip(columns, row))
# skip records with no geometry
if not props['fs_text_geom']: continue
geom = WKT.from_wkt(props['fs_text_geom'])
# add feature id column and remove it from result set dictionary
id = props[self.fid_col]
del props[self.fid_col]
# all attribute columns, including geometry have been selected, remove the duplicate
if self.attribute_cols == '*':
del props[self.geom_col]
# no need for it anymore, geom is already been set
del props['fs_text_geom']
# attribute data for feature
data = {}
for key, value in props.items():
data[key] = value
props.clear()
# go through data fields and format according to type
for key, value in data.items():
if isinstance(value, str):
data[key] = unicode(value, self.encoding)
elif isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
# stringify datetimes
data[key] = str(value)
try:
if isinstance(value, decimal.Decimal):
data[key] = unicode(str(value), self.encoding)
except:
pass
if (geom):
features.append( Feature( id, geom, self.geom_col, self.srid_out, data ) )
return features
def select(self, action):
cursor = self.db.cursor()
features = []
sql_attrs = "SELECT key, value FROM \"%s_attrs\" WHERE feature_id = :feature_id" % (
self.table, )
selection_dict = {}
if action.id is not None:
sql = "SELECT * FROM \"%s\" WHERE %s = ?" % (self.table,
self.fid_col)
cursor.execute(str(sql), (action.id, ))
results = [cursor.fetchone()]
else:
match = Feature(props=action.attributes)
filters = match.properties.items()
sql = "SELECT DISTINCT(t.feature_id) as feature_id, t.%s as %s,\
t.%s as %s FROM \"%s\" t LEFT JOIN \"%s_attrs\" a ON a.feature_id =\
t.feature_id " % (self.geom_col, self.geom_col, self.fid_col,
self.fid_col, self.table, self.table)
select_dict = {}
if filters:
sql += "WHERE 1 "
for ii, (key, value) in enumerate(filters):
select_dict['key%i' % ii] = key
if isinstance(value, dict):
select_dict['value%i' % ii] = value['value']
sql += (" AND a.key = :key%i AND a.value " +
self.query_action_sql[value['type']] +
" :value%i") % (ii, ii)
else:
select_dict['value%i' % ii] = value
sql += " AND a.key = :key%i AND a.value = :value%i" % (
ii, ii)
bbox = ''
if action.bbox:
# skip sql interpolation as these are from calculation.
bbox = " AND %f > t.xmin \
AND t.xmax > %f \
AND %f > t.ymin \
AND t.ymax > %f "\
% (action.bbox[2], action.bbox[0], action.bbox[3], action.bbox[1])
sql += bbox
sql += self.order or ''
sql += " LIMIT %d" % (action.maxfeatures or 1000, )
if action.startfeature:
sql += " OFFSET %d" % action.startfeature
cursor.execute(str(sql), select_dict)
results = cursor.fetchall()
for row in results:
attrs = cursor.execute(
sql_attrs, dict(feature_id=row['feature_id'])).fetchall()
d = {}
#if attrs == []: continue
for attr in attrs:
d[attr[0]] = attr[1]
geom = WKT.from_wkt(row[self.geom_col])
id = row[self.fid_col]
if (geom):
features.append(Feature(id, geom, d))
return features
def select (self, action):
cursor = self.db.cursor()
features = []
sql_attrs = "SELECT key, value FROM \"%s_attrs\" WHERE feature_id = :feature_id" % (self.table,)
selection_dict = {}
if action.id is not None:
sql = "SELECT * FROM \"%s\" WHERE %s = ?" % ( self.table, self.fid_col)
cursor.execute(str(sql), (action.id,))
results = [ cursor.fetchone() ]
else:
match = Feature(props = action.attributes)
filters = match.properties.items()
sql = "SELECT DISTINCT(t.feature_id) as feature_id, t.%s as %s,\
t.%s as %s FROM \"%s\" t LEFT JOIN \"%s_attrs\" a ON a.feature_id =\
t.feature_id " % ( self.geom_col, self.geom_col, self.fid_col, self.fid_col, self.table, self.table )
select_dict = {}
if filters:
sql += "WHERE 1 "
for ii, (key, value) in enumerate(filters):
if isinstance(value, dict):
select_dict['key%i' % ii] = value['column']
select_dict['value%i' % ii] = value['value']
sql += (" AND a.key = :key%i AND a.value " + self.query_action_sql[value['type']] + " :value%i") % (ii, ii)
else:
select_dict['key%i' % ii] = key
select_dict['value%i' % ii] = value
sql += " AND a.key = :key%i AND a.value = :value%i" % (ii, ii)
bbox = ''
if action.bbox:
# skip sql interpolation as these are from calculation.
bbox = " AND %f > t.xmin \
AND t.xmax > %f \
AND %f > t.ymin \
AND t.ymax > %f "\
% (action.bbox[2], action.bbox[0], action.bbox[3], action.bbox[1])
sql += bbox
sql += self.order or ''
sql += " LIMIT %d" % (action.maxfeatures or 1000, )
if action.startfeature:
sql += " OFFSET %d" % action.startfeature
cursor.execute(str(sql), select_dict)
results = cursor.fetchall()
for row in results:
attrs = cursor.execute(sql_attrs, dict(feature_id=row['feature_id']) ).fetchall()
d = {}
#if attrs == []: continue
for attr in attrs:
d[attr[0]] = attr[1]
geom = WKT.from_wkt(row[self.geom_col])
id = row[self.fid_col]
if (geom):
features.append( Feature( id, geom, self.geom_col, self.srid_out, d ) )
return features
def select(self, action):
model = __import__(self.model, fromlist=['*'])
cls = getattr(model, self.cls)
geom_cls = None
if self.geom_cls:
geom_cls = getattr(model, self.geom_cls)
if action.id is not None:
result = [self.session.query(cls).get(action.id)]
else:
if self.geom_rel and self.geom_cls:
main_table = cls.__tablename__
geom_table = geom_cls.__tablename__
join_condition = self.join_condition or "%s.%s_id=%s.id" % (
main_table, geom_table, geom_table)
query = self.session.query(cls,
geom_cls).filter(join_condition)
else:
query = self.session.query(cls)
if action.attributes:
query = query.filter(
and_(*[
self.feature_predicate(v['column'], v['type'],
v['value'])
for k, v in action.attributes.iteritems()
]))
if action.bbox:
if self.geom_rel and self.geom_cls:
geom_element = getattr(geom_cls, self.geom_col)
else:
geom_element = getattr(cls, self.geom_col)
query = query.filter(
geom_element.intersects(
self.session.scalar(
func.GeomFromText(self.bbox2wkt(action.bbox),
self.srid))))
if self.order:
query = query.order_by(getattr(cls, self.order))
if action.maxfeatures:
query.limit(action.maxfeatures)
else:
query.limit(1000)
if action.startfeature:
query.offset(action.startfeature)
result = query.all()
features = []
for row_tuple in result:
props = {}
id = None
geom = None
if not isinstance(row_tuple, (tuple, list, dict, set)):
row_tuple = (row_tuple, )
for row in row_tuple:
if isinstance(row, cls):
cols = cls.__table__.c.keys()
for col in cols:
if col == self.fid_col:
id = getattr(row, col)
elif col == self.geom_col:
geom = WKT.from_wkt(
self.session.scalar(getattr(row, col).wkt))
else:
if self.attribute_cols == '*' or col in self.attribute_cols:
props[col] = getattr(row, col)
elif isinstance(row, geom_cls) and geom_cls:
cols = geom_cls.__table__.c.keys()
for col in cols:
if col == self.fid_col:
pass
elif col == self.geom_col:
geom = WKT.from_wkt(
self.session.scalar(getattr(row, col).wkt))
else:
if self.attribute_cols == '*' or col in self.attribute_cols:
props[col] = getattr(row, col)
else:
continue
for key, value in props.items():
if isinstance(value, str):
props[key] = unicode(value, self.encoding)
elif isinstance(value, datetime.datetime) or isinstance(
value, datetime.date):
# stringify datetimes
props[key] = str(value)
try:
if isinstance(value, decimal.Decimal):
props[key] = unicode(str(value), self.encoding)
except:
pass
if (geom):
features.append(
Feature(id=id,
geometry=geom,
geometry_attr=self.geom_col,
srs=self.srid_out,
props=props))
return features
class PostGIS(DataSource):
"""PostGIS datasource. Setting up the table is beyond the scope of
FeatureServer."""
query_action_types = ['lt', 'gt', 'ilike', 'like', 'gte', 'lte']
query_action_sql = {
'lt': '<',
'gt': '>',
'ilike': 'ilike',
'like': 'like',
'gte': '>=',
'lte': '<=',
'eq': '='
}
def __init__(self,
name,
srid=4326,
srid_out=4326,
fid="gid",
geometry="the_geom",
fe_attributes='true',
order="",
attribute_cols='*',
writable=True,
encoding="utf-8",
hstore='false',
hstore_attr="",
**args):
DataSource.__init__(self, name, **args)
self.table = args["layer"]
self.fid_col = fid
self.encoding = encoding
self.geom_col = geometry
self.order = order
self.srid = srid
self.srid_out = srid_out
self.db = None
self.dsn = args["dsn"]
self.writable = writable
self.attribute_cols = attribute_cols
self.fe_attributes = True
if fe_attributes.lower() == 'false':
self.fe_attributes = False
if hstore.lower() == 'true':
self.hstore = True
self.hstoreAttribute = hstore_attr
else:
self.hstore = False
self.hstoreAttribute = ""
def begin(self):
try:
self.db = psycopg.connect(self.dsn)
except Exception as e:
raise ConnectionException(
**{
'dump': str(e),
'layer': self.name,
'locator': 'PostGIS',
'code': e.pgcode
})
def commit(self):
if self.writable:
self.db.commit()
self.db.close()
def rollback(self):
if self.writable:
self.db.rollback()
self.db.close()
def column_names(self, feature):
return feature.properties.keys()
def value_formats(self, feature):
values = ["%%(%s)s" % self.geom_col]
values = []
for key, val in feature.properties.items():
valtype = type(val).__name__
if valtype == "dict":
val['pred'] = "%%(%s)s" % (key, )
values.append(val)
else:
fmt = "%%(%s)s" % (key, )
values.append(fmt)
return values
def feature_predicates(self, feature):
columns = self.column_names(feature)
values = self.value_formats(feature)
predicates = []
for pair in zip(columns, values):
if pair[0] != self.geom_col:
if isinstance(pair[1], dict):
# Special Query: pair[0] is 'a', pair[1] is {'type', 'pred', 'value'}
# We build a Predicate here, then we replace pair[1] with pair[1] value below
if pair[1].has_key('value'):
predicates.append(
"%s %s %s" %
(pair[1]['column'],
self.query_action_sql[pair[1]['type']],
pair[1]['pred']))
else:
predicates.append("%s = %s" % pair)
if feature.geometry and feature.geometry.has_key("coordinates"):
predicates.append(
" %s = ST_SetSRID('%s'::geometry, %s) " %
(self.geom_col, WKT.to_wkt(feature.geometry), self.srid))
return predicates
def feature_values(self, feature):
props = copy.deepcopy(feature.properties)
for key, val in props.iteritems():
if type(val) is unicode: ### b/c psycopg1 doesn't quote unicode
props[key] = val.encode(self.encoding)
if type(val) is dict:
props[key] = val['value']
return props
def id_sequence(self):
return self.table + "_" + self.fid_col + "_seq"
def insert(self, action):
self.begin()
if action.feature != None:
feature = action.feature
columns = ", ".join(self.column_names(feature) + [self.geom_col])
values = ", ".join(
self.value_formats(feature) + [
"ST_SetSRID('%s'::geometry, %s) " %
(WKT.to_wkt(feature.geometry), self.srid)
])
sql = "INSERT INTO \"%s\" (%s) VALUES (%s)" % (self.table, columns,
values)
cursor = self.db.cursor()
cursor.execute(str(sql), self.feature_values(feature))
cursor.execute("SELECT currval('%s');" % self.id_sequence())
action.id = cursor.fetchone()[0]
return InsertResult(action.id, "")
elif action.wfsrequest != None:
sql = action.wfsrequest.getStatement(self)
cursor = self.db.cursor()
cursor.execute(str(sql))
cursor.execute("SELECT currval('%s');" % self.id_sequence())
action.id = cursor.fetchone()[0]
return InsertResult(action.id, "")
return None
def update(self, action):
if action.feature != None:
feature = action.feature
predicates = ", ".join(self.feature_predicates(feature))
sql = "UPDATE \"%s\" SET %s WHERE %s = %d" % (
self.table, predicates, self.fid_col, action.id)
cursor = self.db.cursor()
cursor.execute(str(sql), self.feature_values(feature))
return UpdateResult(action.id, "")
elif action.wfsrequest != None:
sql = action.wfsrequest.getStatement(self)
cursor = self.db.cursor()
cursor.execute(str(sql))
return UpdateResult(action.id, "")
return None
def delete(self, action):
if action.feature != None:
sql = "DELETE FROM \"%s\" WHERE %s = %%(%s)d" % (
self.table, self.fid_col, self.fid_col)
cursor = self.db.cursor()
try:
cursor.execute(str(sql) % {self.fid_col: action.id})
except:
cursor.execute(str(sql), {self.fid_col: action.id})
return DeleteResult(action.id, "")
elif action.wfsrequest != None:
sql = action.wfsrequest.getStatement(self)
cursor = self.db.cursor()
try:
cursor.execute(str(sql) % {self.fid_col: action.id})
except:
cursor.execute(str(sql), {self.fid_col: action.id})
return DeleteResult(action.id, "")
return None
def select(self, action):
cursor = self.db.cursor()
if action.id is not None:
sql = "SELECT ST_AsText(ST_Transform(%s, %d)) as fs_text_geom, " % (
self.geom_col, int(self.srid_out))
if hasattr(self, 'version'):
sql += "%s as version, " % self.version
if hasattr(self, 'ele'):
sql += "%s as ele, " % self.ele
sql += "\"%s\"" % self.fid_col
if len(self.attribute_cols) > 0:
sql += ", %s" % self.attribute_cols
if hasattr(self, "additional_cols"):
cols = self.additional_cols.split(';')
additional_col = ",".join(cols)
sql += ", %s" % additional_col
sql += " FROM \"%s\" WHERE %s = %%(%s)s" % (
self.table, self.fid_col, self.fid_col)
#sql = "SELECT ST_AsText(ST_Transform(%s, %d)) as fs_text_geom, %s as ele, %s as version, \"%s, %s FROM \"%s\" WHERE %s = %%(%s)s" % (
# self.geom_col, int(self.srid_out), self.ele, self.version, self.fid_col, self.attribute_cols, self.table, self.fid_col, self.fid_col )
cursor.execute(str(sql), {self.fid_col: str(action.id)})
result = [cursor.fetchone()]
else:
filters = []
attrs = {}
if action.attributes:
match = Feature(props=action.attributes)
filters = self.feature_predicates(match)
for key, value in action.attributes.items():
if isinstance(value, dict):
attrs[key] = value['value']
else:
attrs[key] = value
if action.bbox:
filters.append(
"%s && ST_Transform(ST_SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s), %s) AND ST_Intersects(%s, ST_Transform(ST_SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s), %s))"
% ((self.geom_col, ) + tuple(action.bbox) +
(self.srid_out, ) + (self.srid, ) + (self.geom_col, ) +
(tuple(action.bbox) + (self.srid_out, ) +
(self.srid, ))))
sql = "SELECT ST_AsText(ST_Transform(%s, %d)) as fs_text_geom, " % (
self.geom_col, int(self.srid_out))
if hasattr(self, 'ele'):
sql += "%s as ele, " % self.ele
if hasattr(self, 'version'):
sql += "%s as version, " % self.version
sql += "\"%s\"" % self.fid_col
if len(self.attribute_cols) > 0:
sql += ", %s" % self.attribute_cols
# check OGC FE attributes
if self.fe_attributes and action.wfsrequest:
fe_cols = action.wfsrequest.getAttributes()
ad_cols = self.getColumns()
fe_cols = filter(lambda x: x not in ad_cols, fe_cols)
if len(fe_cols) > 0:
sql += ", %s" % ",".join(fe_cols)
if hasattr(self, "additional_cols"):
cols = self.additional_cols.split(';')
additional_col = ",".join(cols)
sql += ", %s" % additional_col
sql += " FROM \"%s\"" % (self.table)
#sql = "SELECT ST_AsText(Transform(%s, %d)) as fs_text_geom, %s as ele, %s as version, \"%s\", %s FROM \"%s\"" % (self.geom_col, int(self.srid_out), self.ele, self.version, self.fid_col, self.attribute_cols, self.table)
if filters:
sql += " WHERE " + " AND ".join(filters)
if action.wfsrequest:
if filters:
sql += " AND "
else:
sql += " WHERE "
sql += action.wfsrequest.render(self)
if self.order:
sql += " ORDER BY " + self.order
if action.maxfeatures:
sql += " LIMIT %d" % action.maxfeatures
#else:
# sql += " LIMIT 1000"
if action.startfeature:
sql += " OFFSET %d" % action.startfeature
try:
cursor.execute(str(sql), attrs)
except Exception, e:
if e.pgcode[:
2] == errorcodes.CLASS_SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION:
raise InvalidValueException(
**{
'dump': e.pgerror,
'layer': self.name,
'locator': 'PostGIS'
})
result = cursor.fetchall(
) # should use fetchmany(action.maxfeatures)
columns = [desc[0] for desc in cursor.description]
features = []
for row in result:
props = dict(zip(columns, row))
if not props['fs_text_geom']: continue
geom = WKT.from_wkt(props['fs_text_geom'])
id = props[self.fid_col]
del props[self.fid_col]
if self.attribute_cols == '*':
del props[self.geom_col]
del props['fs_text_geom']
for key, value in props.items():
if isinstance(value, str):
props[key] = unicode(value, self.encoding)
elif isinstance(value, datetime.datetime) or isinstance(
value, datetime.date):
# stringify datetimes
props[key] = str(value)
try:
if isinstance(value, decimal.Decimal):
props[key] = unicode(str(value), self.encoding)
except:
pass
if (geom):
features.append(
Feature(id, geom, self.geom_col, self.srid_out, props))
return features
def select(self, action):
cursor = self.db.cursor()
if action.id is not None:
sql = "SELECT AsText(%s) as fs_text_geom, \"%s\", %s FROM \"%s\" WHERE %s = %%(%s)s" % (
self.geom_col, self.fid_col, self.attribute_cols, self.table,
self.fid_col, self.fid_col)
cursor.execute(str(sql), {self.fid_col: str(action.id)})
result = [cursor.fetchone()]
else:
filters = []
attrs = {}
if action.attributes:
match = Feature(props=action.attributes)
filters = self.feature_predicates(match)
for key, value in action.attributes.items():
if isinstance(value, dict):
attrs[key] = value['value']
else:
attrs[key] = value
if action.bbox:
filters.append(
"%s && SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s) and intersects(%s, SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s))"
% ((self.geom_col, ) + tuple(action.bbox) + (self.srid, ) +
(self.geom_col, ) + (tuple(action.bbox) +
(self.srid, ))))
sql = "SELECT AsText(%s) as fs_text_geom, \"%s\", %s FROM \"%s\"" % (
self.geom_col, self.fid_col, self.attribute_cols, self.table)
if filters:
sql += " WHERE " + " AND ".join(filters)
if self.order:
sql += " ORDER BY " + self.order
if action.maxfeatures:
sql += " LIMIT %d" % action.maxfeatures
else:
sql += " LIMIT 1000"
if action.startfeature:
sql += " OFFSET %d" % action.startfeature
cursor.execute(str(sql), attrs)
result = cursor.fetchall(
) # should use fetchmany(action.maxfeatures)
columns = [desc[0] for desc in cursor.description]
features = []
for row in result:
props = dict(zip(columns, row))
if not props['fs_text_geom']: continue
geom = WKT.from_wkt(props['fs_text_geom'])
id = props[self.fid_col]
del props[self.fid_col]
if self.attribute_cols == '*':
del props[self.geom_col]
del props['fs_text_geom']
for key, value in props.items():
if isinstance(value, str):
props[key] = unicode(value, self.encoding)
elif isinstance(value, datetime.datetime) or isinstance(
value, datetime.date):
# stringify datetimes
props[key] = str(value)
try:
if isinstance(value, decimal.Decimal):
props[key] = unicode(str(value), self.encoding)
except:
pass
if (geom):
features.append(Feature(id, geom, props))
return features
def select (self, action):
if not hasattr (action, 'parameters') or not action.parameters.has_key('area_filter'):
raise ApplicationException("Missing spatial area filter, supply a Well Known Text geometry for 'area_filter'")
else:
cursor = self.db.cursor()
# validate input WKT string (TODO: implement as proper regexp)
if not action.parameters['area_filter'].find ('POLYGON ('):
raise ApplicationException ("Invalid spatial filter, should be Well Known Text respresentation of a Polygon")
sql = "SELECT ST_AsText(%s) as fs_text_geom, 1 AS \"%s\", %s FROM \"%s\"" % (self.geom_col, self.fid_col, self.attribute_cols, self.table)
sql += "('%s')" % action.parameters['area_filter']
try:
cursor.execute(str(sql))
except Exception, e:
raise ApplicationException ("<em>Internal error:</em><br><pre>%s</pre>" % (str (e)))
result = cursor.fetchall()
# column names from cursor
columns = [desc[0] for desc in cursor.description]
# output features to return
features = []
for row in result:
if not row: continue
# convert result set row record into dictionary
props = dict(zip(columns, row))
# skip records with no geometry
if not props['fs_text_geom']: continue
geom = WKT.from_wkt(props['fs_text_geom'])
# add feature id column and remove it from result set dictionary
id = props[self.fid_col]
del props[self.fid_col]
# all attribute columns, including geometry have been selected, remove the duplicate
if self.attribute_cols == '*':
del props[self.geom_col]
# no need for it anymore, geom is already been set
del props['fs_text_geom']
# the remainder of the items in the result set dictionary are only the attributes
# we need to convert to attributes, for Traffic Model these are embedded as arrays
data = {}
attributes = []
values = []
# expand arrays in result set to proper attributes
for key, value in props.items():
if key == "attributes":
# attributes is an array of attribute type (must be first)
attributes = value
else:
if key == "aggregates":
# no prefix needed for aggregate values
prefix = ""
else:
prefix = "[%s] " % key
values = value
# go through attributes and add values for each
for index, type in enumerate(attributes):
# prepend prefix to differentiate different types of aggregates
field = "%s%s" % (prefix, type)
data[field] = values[index]
props.clear()
# go through data fields and format according to type
for key, value in data.items():
if isinstance(value, str):
data[key] = unicode(value, self.encoding)
elif isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
# stringify datetimes
data[key] = str(value)
try:
if isinstance(value, decimal.Decimal):
data[key] = unicode(str(value), self.encoding)
except:
pass
if (geom):
features.append( Feature( id, geom, self.geom_col, self.srid_out, data ) )
return features
def select(self, action):
self.begin()
cursor = self._connection.cursor()
if action.id is not None:
sql = "SELECT AsText(Transform(%s, %d)) as fs_text_geom, " % (self.geom_col, int(self.srid_out))
if hasattr(self, 'version'):
sql += "%s as version, " % self.version
if hasattr(self, 'ele'):
sql += "%s as ele, " % self.ele
sql += "\"%s\"" % self.fid_col
if len(self.attribute_cols) > 0:
sql += ", %s" % self.attribute_cols
if hasattr(self, "additional_cols"):
cols = self.additional_cols.split(';')
additional_col = ",".join(cols)
sql += ", %s" % additional_col
sql += " FROM \"%s\" WHERE %s = :%s" % (self.table, self.fid_col, self.fid_col)
cursor.execute(str(sql), {self.fid_col: str(action.id)})
result = [cursor.fetchone()]
else:
filters = []
attrs = []
if action.attributes:
match = Feature(props = action.attributes)
filters = self.feature_predicates(match)
for key, value in action.attributes.items():
if isinstance(value, dict):
attrs[key] = value['value']
else:
attrs[key] = value
if action.bbox:
filters.append("Intersects(Transform(BuildMBR(%f, %f, %f, %f, %s), %s), geometry)" % (tuple(action.bbox) + (self.srid_out,) + (self.srid,)))
sql = "SELECT AsText(Transform(%s, %d)) as fs_text_geom, " % (self.geom_col, int(self.srid_out))
if hasattr(self, 'ele'):
sql += "%s as ele, " % self.ele
if hasattr(self, 'version'):
sql += "%s as version, " % self.version
sql += "\"%s\"" % self.fid_col
if len(self.attribute_cols) > 0:
sql += ", %s" % self.attribute_cols
# check OGC FE attributes
if self.fe_attributes and action.wfsrequest:
fe_cols = action.wfsrequest.getAttributes()
ad_cols = self.getColumns()
fe_cols = filter(lambda x: x not in ad_cols, fe_cols)
if len(fe_cols) > 0:
sql += ", %s" % ",".join(fe_cols)
if hasattr(self, "additional_cols"):
cols = self.additional_cols.split(';')
additional_col = ",".join(cols)
sql += ", %s" % additional_col
sql += " FROM \"%s\"" % (self.table)
if filters:
sql += " WHERE " + " AND ".join(filters)
if action.wfsrequest:
if filters:
sql += " AND "
else:
sql += " WHERE "
sql += action.wfsrequest.render(self)
if self.order:
sql += " ORDER BY " + self.order
if action.maxfeatures:
sql += " LIMIT %d" % action.maxfeatures
#else:
# sql += " LIMIT 1000"
if action.startfeature:
sql += " OFFSET %d" % action.startfeature
cursor.execute(str(sql), attrs)
result = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
features = []
for row in result:
props = dict(zip(columns, row))
if not props['fs_text_geom']: continue
geom = WKT.from_wkt(props['fs_text_geom'])
id = props[self.fid_col]
del props[self.fid_col]
if self.attribute_cols == '*':
del props[self.geom_col]
del props['fs_text_geom']
for key, value in props.items():
if isinstance(value, str):
props[key] = unicode(value, self.encoding)
elif isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
# stringify datetimes
props[key] = str(value)
try:
if isinstance(value, decimal.Decimal):
props[key] = unicode(str(value), self.encoding)
except:
pass
if (geom):
features.append( Feature( id, geom, self.geom_col, self.srid_out, props ) )
return features
def select (self, action):
model = __import__(self.model, fromlist=['*'])
cls = getattr(model, self.cls)
geom_cls = None
if self.geom_cls:
geom_cls = getattr(model, self.geom_cls)
if action.id is not None:
result = [self.session.query(cls).get(action.id)]
else:
if self.geom_rel and self.geom_cls:
main_table = cls.__tablename__
geom_table = geom_cls.__tablename__
join_condition = self.join_condition or "%s.%s_id=%s.id" % (
main_table, geom_table, geom_table)
query = self.session.query(cls, geom_cls).filter(join_condition)
else:
query = self.session.query(cls)
if action.attributes:
query = query.filter(
and_(
*[self.feature_predicate(getattr(cls, k),
v['type'], v['value'])
for k, v in action.attributes.iteritems()]
)
)
if action.bbox:
if self.geom_rel and self.geom_cls:
geom_element = getattr(geom_cls, self.geom_col)
else:
geom_element = getattr(cls, self.geom_col)
query = query.filter(geom_element.intersects(
self.session.scalar(func.GeomFromText(
self.bbox2wkt(action.bbox), self.srid))))
if self.order:
query = query.order_by(getattr(cls, self.order))
if action.maxfeatures:
query.limit(action.maxfeatures)
else:
query.limit(1000)
if action.startfeature:
query.offset(action.startfeature)
result = query.all()
features = []
for row_tuple in result:
props = {}
id = None
geom = None
if not isinstance(row_tuple, (tuple, list, dict, set)):
row_tuple = (row_tuple,)
for row in row_tuple:
if isinstance(row, cls):
cols = cls.__table__.c.keys()
for col in cols:
if col == self.fid_col:
id = getattr(row, col)
elif col == self.geom_col:
geom = WKT.from_wkt(self.session.scalar(getattr(row, col).wkt))
else:
if self.attribute_cols == '*' or col in self.attribute_cols:
props[col] = getattr(row, col)
elif isinstance(row, geom_cls) and geom_cls:
cols = geom_cls.__table__.c.keys()
for col in cols:
if col == self.fid_col:
pass
elif col == self.geom_col:
geom = WKT.from_wkt(self.session.scalar(getattr(row, col).wkt))
else:
if self.attribute_cols == '*' or col in self.attribute_cols:
props[col] = getattr(row, col)
else:
continue
for key, value in props.items():
if isinstance(value, str):
props[key] = unicode(value, self.encoding)
elif isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
# stringify datetimes
props[key] = str(value)
try:
if isinstance(value, decimal.Decimal):
props[key] = unicode(str(value), self.encoding)
except:
pass
if (geom):
features.append( Feature( id, geom, props ) )
return features
def create (self, action):
feature = action.feature
columns = ", ".join(self.column_names(feature)+[self.geom_col])
values = ", ".join(self.value_formats(feature)+["SetSRID('%s'::geometry, %s) " % (WKT.to_wkt(feature.geometry), self.srid)])
sql = "INSERT INTO \"%s\" (%s) VALUES (%s)" % (
self.table, columns, values)
cursor = self.db.cursor()
cursor.execute(str(sql), self.feature_values(feature))
cursor.execute("SELECT currval('%s');" % self.id_sequence())
action.id = cursor.fetchone()[0]
self.db.commit()
return self.select(action)
def insert (self, action):
self.begin()
if action.feature != None:
feature = action.feature
columns = ", ".join(self.column_names(feature)+[self.geom_col])
values = ", ".join(self.value_formats(feature)+["SetSRID('%s'::geometry, %s) " % (WKT.to_wkt(feature.geometry), self.srid)])
sql = "INSERT INTO \"%s\" (%s) VALUES (%s)" % (self.table, columns, values)
cursor = self.db.cursor()
cursor.execute(str(sql), self.feature_values(feature))
cursor.execute("SELECT currval('%s');" % self.id_sequence())
action.id = cursor.fetchone()[0]
return InsertResult(action.id, "")
elif action.wfsrequest != None:
sql = action.wfsrequest.getStatement(self)
cursor = self.db.cursor()
cursor.execute(str(sql))
cursor.execute("SELECT currval('%s');" % self.id_sequence())
action.id = cursor.fetchone()[0]
return InsertResult(action.id, "")
return None
def select (self, action):
cursor = self.db.cursor()
if action.id is not None:
sql = "SELECT AsText(%s) as fs_text_geom, \"%s\", %s FROM \"%s\" WHERE %s = %%(%s)s" % (
self.geom_col, self.fid_col, self.attribute_cols, self.table, self.fid_col, self.fid_col )
cursor.execute(str(sql), {self.fid_col: str(action.id)})
result = [cursor.fetchone()]
else:
filters = []
attrs = {}
if action.attributes:
match = Feature(props = action.attributes)
filters = self.feature_predicates(match)
for key, value in action.attributes.items():
if isinstance(value, dict):
attrs[key] = value['value']
else:
attrs[key] = value
if action.bbox:
filters.append( "%s && SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s) and intersects(%s, SetSRID('BOX3D(%f %f,%f %f)'::box3d, %s))" % (
(self.geom_col,) + tuple(action.bbox) + (self.srid,) + (self.geom_col,) + (tuple(action.bbox) + (self.srid,))))
sql = "SELECT AsText(%s) as fs_text_geom, \"%s\", %s FROM \"%s\"" % (self.geom_col, self.fid_col, self.attribute_cols, self.table)
if filters:
sql += " WHERE " + " AND ".join(filters)
if self.order:
sql += " ORDER BY " + self.order
if action.maxfeatures:
sql += " LIMIT %d" % action.maxfeatures
else:
sql += " LIMIT 1000"
if action.startfeature:
sql += " OFFSET %d" % action.startfeature
cursor.execute(str(sql), attrs)
result = cursor.fetchall() # should use fetchmany(action.maxfeatures)
columns = [desc[0] for desc in cursor.description]
features = []
for row in result:
props = dict(zip(columns, row))
if not props['fs_text_geom']: continue
geom = WKT.from_wkt(props['fs_text_geom'])
id = props[self.fid_col]
del props[self.fid_col]
if self.attribute_cols == '*':
del props[self.geom_col]
del props['fs_text_geom']
for key, value in props.items():
if isinstance(value, str):
props[key] = unicode(value, self.encoding)
elif isinstance(value, datetime.datetime) or isinstance(value, datetime.date):
# stringify datetimes
props[key] = str(value)
try:
if isinstance(value, decimal.Decimal):
props[key] = unicode(str(value), self.encoding)
except:
pass
if (geom):
features.append( Feature( id, geom, props ) )
return features
