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
