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