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 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 (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, 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 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 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 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)
