def geojson(request): try: westlng = request.GET["westlng"] eastlng = request.GET["eastlng"] northlat = request.GET["northlat"] southlat = request.GET["southlat"] except KeyError: return json_response({}) wkt = ( "POLYGON((" "%(w)s %(s)s, " "%(w)s %(n)s, " "%(e)s %(n)s, " "%(e)s %(s)s, " "%(w)s %(s)s" "))" % {"w": westlng, "e": eastlng, "s": southlat, "n": northlat} ) qs = Parcel.objects.filter(geom__intersects=wkt).prefetch_mapped() features = [] serializer = UIParcelSerializer() for parcel in qs: feature = Feature(parcel.id) feature.geometry = { "type": parcel.geom.geom_type, "coordinates": parcel.geom.coords, } feature.properties = serializer.one(parcel) features.append(feature) output = GeoJSON.GeoJSON().encode(features, to_string=False) return json_response(output)
def entry_to_feature(self, entry_dom): id = 1 try: id = entry_dom.getElementsByTagName("id")[0].firstChild.nodeValue except: id = 1 feature = Feature(str(id)) geometry = self.extract_entry_geometry(entry_dom) if not geometry: return None feature.geometry = geometry for node in entry_dom.childNodes: try: attr_name = node.tagName.split(":")[-1] if attr_name not in [ 'point', 'line', 'polygon', 'id', 'where' ]: try: feature.properties[ attr_name] = node.firstChild.nodeValue except: pass except: pass return feature
def decode(self, query_set, generator = False): results = [] for res in query_set: feature = Feature(res.pk) if self.pickled_geometry: feature.geometry = pickle.loads(res.geometry) elif self.geodjango: geometry = None geom = getattr(res, self.geodjango) if geom: geometry = {} geometry['type'] = geom.geom_type geometry['coordinates'] = geom.coords feature.geometry = geometry if self.pickled_properties: props = getattr(res, self.pickled_properties) feature.properties = pickle.loads(props.encode("utf-8")) if self.properties: for p in self.properties: feature.properties[p] = getattr(res, p) results.append(feature) return results
def entry_to_feature(self, entry_dom): id = 1 try: id = entry_dom.getElementsByTagName("id")[0].firstChild.nodeValue except: id = 1 feature = Feature(str(id)) geometry = self.extract_entry_geometry(entry_dom) if not geometry: return None feature.geometry = geometry for node in entry_dom.childNodes: try: attr_name = node.tagName.split(":")[-1] if attr_name not in ['point', 'line', 'polygon', 'id', 'where']: try: feature.properties[attr_name] = node.firstChild.nodeValue except: pass except: pass return feature
def _createFeature(self, feature_dict, id = None): """Private. Not designed to be used externally.""" feature = Feature(id) if feature_dict.has_key('geometry'): feature.geometry = feature_dict['geometry'] if feature_dict.has_key('properties'): feature.properties = feature_dict['properties'] return feature
def _createFeature(self, feature_dict, id=None): """Private. Not designed to be used externally.""" feature = Feature(id) if feature_dict.has_key("geometry"): feature.geometry = feature_dict["geometry"] if feature_dict.has_key("properties"): feature.properties = feature_dict["properties"] return feature
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 select(self, action): """Load data from one of the OpenStreetMap APIs using urllib.""" if self.osmxapi: data = self.select_osmxapi(action) else: data = self.select_main(action) doc = xml.dom.minidom.parseString(data) nodes = {} features = [] for node in doc.getElementsByTagName("node"): properties = {} interesting = False for tag in node.getElementsByTagName("tag"): key = tag.getAttribute("k") properties[key] = tag.getAttribute("v") if not key in self.uninteresting_tags: interesting = True id = int(node.getAttribute("id")) nodes[id] = [ float(node.getAttribute("lon")), float(node.getAttribute("lat")) ] if interesting == True: geom = {'type': 'Point', 'coordinates': nodes[id]} features.append( Feature(id=id, geometry=geom, srs=self.srid_out, props=properties)) for way in doc.getElementsByTagName("way"): geometry = {'type': 'LineString', 'coordinates': []} for nd in way.getElementsByTagName('nd'): geometry['coordinates'].append(nodes[int( nd.getAttribute("ref"))]) properties = {} for tag in way.getElementsByTagName("tag"): key = tag.getAttribute("k") properties[key] = tag.getAttribute("v") features.append( Feature(id=int(way.getAttribute("id")), geometry=geometry, srs=self.srid_out, props=properties)) return features
def convert_photo (self, xml): node_names = self.get_node_names(xml) props = {'img_url' : self.get_url(xml)} owners = xml.xpath('./owner') if len(owners) > 0: props['owner'] = owners[0].attrib['nsid'] props['username'] = owners[0].attrib['username'] for i in node_names: if i == "tags": tags = [ tag.text for tag in xml.xpath('./%s' % str(i))[0] ] props[i] = ",".join(tags) else: nodes = xml.xpath('./%s' % str(i)) if len(nodes) > 0: if len(list(nodes[0])) == 0: if nodes[0].text is None: props[i] = "" else: props[i] = nodes[0].text try: coordinates = self.get_coordinates(xml) except: raise return Feature(id=xml.attrib["id"], geometry={'type':"Point", 'coordinates':coordinates}, geometry_attr="geometry", srs=self.srid_out, props=props)
def encode_tweet(self, tweet): try: geom = self.get_geometry(tweet) except: raise props = {} node_names = self.get_node_names(tweet) for attribute in node_names: keys = attribute.split(".") value = tweet for key in keys: if value[key] is None: break value = value[key] if type(value) is not dict and type(value) is not list: if type(value) is unicode: props[attribute] = value else: props[attribute] = unicode(str(value), self.encoding) return Feature( id=tweet["id"], geometry=geom, geometry_attr="geometry", srs=self.srid_out, props=props )
def convert_single_photo_xml_to_feature(self, xml): """Convert the Flickr Photo XML to a Feature object. XML arg is an xml.dom.minidom object. this reads the response from the getInfo method.""" attrs = xml.attributes.items() props = {} for attr in attrs: props[attr[0]] = attr[1] props['owner'] = xml.getElementsByTagName( "owner")[0].attributes['nsid'].value for i in ['title', 'description']: node = xml.getElementsByTagName(i)[0] if node.firstChild: props[i] = node.firstChild.nodeValue loc = xml.getElementsByTagName("location")[0] coordinates = [ float(loc.attributes['longitude'].value), float(loc.attributes['latitude'].value) ] return Feature(id=xml.getAttribute("id"), geometry={ 'type': "Point", 'coordinates': coordinates }, srs=self.srid_out, props=props)
def freeze_features (self, features): result = [] for ogrfeat in features: feat = Feature(ogrfeat.GetFID()) geom = ogrfeat.GetGeometryRef() feat.geometry = OGR.freeze_geometry(geom) for n, defn in enumerate(self.fields): key = defn.GetName() if self.attribute_cols and not key.lower() in self.attribute_cols: continue value = ogrfeat.GetField(n) if isinstance(value, str): value = unicode(value, "utf-8") feat.properties[key] = value result.append(feat) ogrfeat.Destroy() return result
def freeze_features(self, features): result = [] for ogrfeat in features: feat = Feature(ogrfeat.GetFID()) geom = ogrfeat.GetGeometryRef() feat.geometry = OGR.freeze_geometry(geom) for n, defn in enumerate(self.fields): key = defn.GetName() if self.attribute_cols and not key.lower( ) in self.attribute_cols: continue value = ogrfeat.GetField(n) if isinstance(value, str): value = unicode(value, "utf-8") feat.properties[key] = value result.append(feat) ogrfeat.Destroy() return result
def decode(self, query_set, generator=False): results = [] for res in query_set: feature = Feature(res.pk) if self.pickled_geometry: feature.geometry = pickle.loads(res.geometry) elif self.geodjango: geometry = None geom = getattr(res, self.geodjango) if geom: geometry = {} geometry['type'] = geom.geom_type geometry['coordinates'] = geom.coords feature.geometry = geometry if self.pickled_properties: props = getattr(res, self.pickled_properties) feature.properties = pickle.loads(props.encode("utf-8")) if self.properties: for p in self.properties: feature.properties[p] = getattr(res, p) results.append(feature) return results
def convert_photo_xml_to_feature(self, xml): """Convert the Flickr Photo XML to a Feature object. XML arg is an xml.dom.minidom object. This reads a photo which is part of a list of photos returned by the search method.""" attrs = xml.attributes.items() props = {} for attr in attrs: props[attr[0]] = attr[1] coordinates = [float(props['longitude']), float(props['latitude'])] props['img_url'] = "http://farm%s.static.flickr.com/%s/%s_%s_m.jpg" % (props['farm'], props['server'], props['id'], props['secret']) del props['latitude'] del props['longitude'] return Feature(xml.getAttribute("id"), {'type':"Point", 'coordinates':coordinates}, props)
def decode(self, layer): features = [] layer.ResetReading() feature = layer.GetNextFeature() while feature: id = feature.GetFID() geometry = self.ExportToJson(feature.GetGeometryRef()) props = {} for i in range(feature.GetFieldCount()): props[feature.GetFieldDefnRef( i).GetName()] = feature.GetFieldAsString(i) features.append(Feature(id, geometry, props)) feature = layer.GetNextFeature() return features
def decode(self, query_set, generator = False): results = [] for res in query_set: feature = Feature(res.id) if self.pickled_geometry: feature.geometry = pickle.loads(res.geometry) elif self.geodjango: geom = getattr(res, self.geodjango) geometry = {} geometry['type'] = geom.geom_type geometry['coordinates'] = geom.coords feature.geometry = geometry if self.pickled_properties: props = getattr(res, self.pickled_properties) feature.properties = pickle.loads(props.encode("utf-8")) if self.properties: for p in self.properties: # This simple change allows us to span relationships between models: # feature.properties[p] = getattr(res, p) feature.properties[p] = reduce(getattr, p.split('__'), res) # An argument can be passed to access querysets (one to many relationships) # from each Feature returned, appending the querysets to the value's 'properties' if self.queries: for q in self.queries: itemslist = [] for queryresult in q.getset(res): item = {} for k,v in queryresult.iteritems(): item[k] = v itemslist.append(item) feature.properties[q.queryparameters] = itemslist results.append(feature) return results
def select(self, action): data = urllib.urlopen( "http://api.twittervision.com/user/current_status/%s.json" % self.username).read() user_data = simplejson.loads(data) geom = { 'type': 'Point', 'coordinates': [ user_data['location']['longitude'], user_data['location']['latitude'] ] } f = Feature(int(user_data["id"]), geom) return [f]
def entry_to_feature(self, placemark_dom): feature = Feature() points = placemark_dom.getElementsByTagName("Point") lines = placemark_dom.getElementsByTagName("LineString") polys = placemark_dom.getElementsByTagName("Polygon") if len(points): coords = points[0].getElementsByTagName("coordinates")[0].firstChild.nodeValue.strip().split(",") feature.geometry = {'type':'Point', 'coordinates':map(float,coords)} elif len(lines): coordstring = lines[0].getElementsByTagName("coordinates")[0].firstChild.nodeValue.strip() coords = coordstring.split(" ") coords = map(lambda x: x.split(","), coords) feature.geometry = {'type':'LineString', 'coordinates':coords} elif len(polys): rings = [] poly = polys[0] outer = poly.getElementsByTagName("outerBoundaryIs")[0] outer_coordstring = outer.getElementsByTagName("coordinates")[0].firstChild.nodeValue.strip() outer_coords = outer_coordstring.split(" ") outer_coords = map(lambda x: map(float,x.split(",")), outer_coords) rings.append(outer_coords) inners = poly.getElementsByTagName("innerBoundaryIs") for inner in inners: inner_coords = inner.getElementsByTagName("coordinates")[0].firstChild.nodeValue.strip().split(" ") inner_coords = map(lambda x: map(float,x.split(",")), inner_coords) rings.append(inner_coords) feature.geometry = {'type':'Polygon', 'coordinates':rings} else: raise Exception("KML parser only understands points and lines, and polys. You seem to be missing something.") nodeList = placemark_dom.childNodes if len(placemark_dom.getElementsByTagName("Metadata")): nodeList += placemark_dom.getElementsByTagName("Metadata")[0].childNodes for node in nodeList: try: attr_name = node.tagName.split(":")[-1] value = node.firstChild.nodeValue if node.tagName not in ['Point', 'LineString', 'Polygon', 'name', 'Metadata'] and not value.startswith("Properties:"): feature.properties[attr_name] = value except: pass try: feature.properties['title'] = placemark_dom.getElementsByTagName("name")[0].firstChild.nodeValue except: pass return feature
def entry_to_feature(self, placemark_dom): feature = Feature() points = placemark_dom.getElementsByTagName("Point") lines = placemark_dom.getElementsByTagName("LineString") polys = placemark_dom.getElementsByTagName("Polygon") if len(points): coords = points[0].getElementsByTagName("coordinates")[0].firstChild.nodeValue.strip().split(",") feature.geometry = {"type": "Point", "coordinates": map(float, coords)} elif len(lines): coordstring = lines[0].getElementsByTagName("coordinates")[0].firstChild.nodeValue.strip() coords = coordstring.split(" ") coords = map(lambda x: x.split(","), coords) feature.geometry = {"type": "LineString", "coordinates": coords} elif len(polys): rings = [] poly = polys[0] outer = poly.getElementsByTagName("outerBoundaryIs")[0] outer_coordstring = outer.getElementsByTagName("coordinates")[0].firstChild.nodeValue.strip() outer_coords = outer_coordstring.split(" ") outer_coords = map(lambda x: map(float, x.split(",")), outer_coords) rings.append(outer_coords) inners = poly.getElementsByTagName("innerBoundaryIs") for inner in inners: inner_coords = inner.getElementsByTagName("coordinates")[0].firstChild.nodeValue.strip().split(" ") inner_coords = map(lambda x: map(float, x.split(",")), inner_coords) rings.append(inner_coords) feature.geometry = {"type": "Polygon", "coordinates": rings} else: raise Exception( "KML parser only understands points and lines, and polys. You seem to be missing something." ) nodeList = placemark_dom.childNodes if len(placemark_dom.getElementsByTagName("Metadata")): nodeList += placemark_dom.getElementsByTagName("Metadata")[0].childNodes for node in nodeList: try: attr_name = node.tagName.split(":")[-1] value = node.firstChild.nodeValue if node.tagName not in ["Point", "LineString", "Polygon", "name", "Metadata"] and not value.startswith( "Properties:" ): feature.properties[attr_name] = value except: pass try: feature.properties["title"] = placemark_dom.getElementsByTagName("name")[0].firstChild.nodeValue except: pass return feature
def select(self, action): obj_list = [] if action.id: obj = self.model.get_by_id(action.id) if obj: obj_list = [obj] else: obj_list = self.model.all() if action.bbox: if geohash_support: bbox = action.bbox hash1 = Geoindex(bbox[0:2]) hash2 = Geoindex(bbox[2:]) obj_list = obj_list.filter("geohash <=", str(hash2)).filter( "geohash >=", str(hash1)) else: raise Exception("No GeoHash support -> No bbox support.") if action.attributes: current_key = None for key, value in action.attributes.items(): if isinstance(value, dict): obj_list.filter( "%s %s" % (value['column'], self.query_action_string[value['type']]), value['value']) else: obj_list.filter("%s =" % key, value) return_list = [] for obj in obj_list: props = {} for key in obj.dynamic_properties(): props[key] = getattr(obj, key) return_list.append( Feature(id=obj.key().id(), geometry=from_wkt(obj.geometry), srs=self.srid_out, props=props)) return return_list
def decode(self, query_set, generator=False): results = [] for res in query_set: feature = Feature(res.id) if self.pickled_geometry: feature.geometry = pickle.loads(res.geometry) elif self.geodjango: geom = getattr(res, self.geodjango) geometry = {} geometry['type'] = geom.geom_type geometry['coordinates'] = geom.coords feature.geometry = geometry if self.pickled_properties: props = getattr(res, self.pickled_properties) feature.properties = pickle.loads(props.encode("utf-8")) if self.properties: for p in self.properties: # This simple change allows us to span relationships between models: # feature.properties[p] = getattr(res, p) feature.properties[p] = reduce(getattr, p.split('__'), res) # An argument can be passed to access querysets (one to many relationships) # from each Feature returned, appending the querysets to the value's 'properties' if self.queries: for q in self.queries: itemslist = [] for queryresult in q.getset(res): item = {} for k, v in queryresult.iteritems(): item[k] = v itemslist.append(item) feature.properties[q.queryparameters] = itemslist results.append(feature) return results
def decode(self, data): features = [Feature(1, self.from_wkt(data))] return features
def decode(self, query_set, generator=False): results = [] for res in query_set: feature = Feature(res.id) if self.pickled_geometry: feature.geometry = pickle.loads(res.geometry) elif self.geodjango: geom = getattr(res, self.geodjango) geometry = {} geometry["type"] = geom.geom_type geometry["coordinates"] = geom.coords feature.geometry = geometry if self.pickled_properties: props = getattr(res, self.pickled_properties) feature.properties = pickle.loads(props.encode("utf-8")) if self.properties: for p in self.properties: feature.properties[p] = getattr(res, p) if self.style: feature.properties["style"] = self.style if self.relation_data: for method, models in self.relation_data.iteritems(): if method == "set_count": for model in models: try: result = getattr(res, model + "_set") count = getattr(result, "count") feature.properties[model + "_" + method] = count() except AttributeError, err: feature.properties[model + "_" + method] = "AttributeError" if method == "values_list": for model in models: try: result = getattr(res, model) all_list = list(result.values_list()) feature.properties[model + "_" + method] = all_list except AttributeError, err: feature.properties[model + "_" + method] = "AttributeError" if method == "display": for model in models: try: display = "get_%s_display" % (model) result = getattr(res, display) feature.properties[model + "_" + method] = result() except AttributeError, err: feature.properties[model + "_" + method] = "AttributeError"
def select(self, action): obj_list = [] max_features = action.maxfeatures or 1000 if action.id: obj = self.model.get_by_id(action.id) if obj: obj_list = [obj] else: obj_list = self.model.all() if action.attributes: current_key = None for key, value in action.attributes.items(): if isinstance(value, dict): obj_list = obj_list.filter( "%s %s" % (value['column'], self.query_action_string[value['type']]), value['value']) else: try: value = int(value) except: pass obj_list = obj_list.filter("%s =" % key, value) if action.bbox: #geocell likes N,E,S,W bbox with W, S, E, N = action.bbox #also needs to be valid wgs84 coords W = max(W, -180) E = min(E, 180) S = max(S, -90) N = min(N, 90) obj_list = self.model.bounding_box_fetch( obj_list, geotypes.Box(N, E, S, W), max_results=max_features) return_list = [] for obj in obj_list[:max_features]: props = {} #get attribs for model for key in self.model.fields(): if not key in self.excluded_fields: try: props[key] = getattr(obj, key) except: props[key] = None #get additional attribs for expando stuff for key in obj.dynamic_properties(): try: props[key] = getattr(obj, key) except: props[key] = None try: geom = from_wkt(obj.geometry) except: logging.error('fail on obj %s' % key) continue return_list.append( Feature(id=action.id, geometry=from_wkt(obj.geometry), srs=self.srid_out, props=props)) return return_list
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): 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
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 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)
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): 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