def test_update_extent(self):
package = factories.Dataset()
geojson = json.loads(self.geojson_examples["point"])
shape = asShape(geojson)
package_extent = PackageExtent(
package_id=package["id"],
the_geom=WKTElement(shape.wkt, self.db_srid),
)
package_extent.save()
if legacy_geoalchemy:
assert(
Session.scalar(package_extent.the_geom.geometry_type) ==
"ST_Point"
)
else:
from sqlalchemy import func
assert(
Session.query(
func.ST_GeometryType(package_extent.the_geom)
).first()[0] ==
"ST_Point"
)
# Update the geometry (Point -> Polygon)
geojson = json.loads(self.geojson_examples["polygon"])
shape = asShape(geojson)
package_extent.the_geom = WKTElement(shape.wkt, self.db_srid)
package_extent.save()
assert(package_extent.package_id == package["id"])
if legacy_geoalchemy:
assert(
Session.scalar(package_extent.the_geom.geometry_type) ==
"ST_Polygon"
)
assert(
Session.scalar(package_extent.the_geom.srid) == self.db_srid
)
else:
assert(
Session.query(
func.ST_GeometryType(package_extent.the_geom)
).first()[0] ==
"ST_Polygon"
)
assert(package_extent.the_geom.srid == self.db_srid)
def getBeachWaterQual():
"""
Queries Postgres AWS RDS to return the most recent water quality report data for each beach that is tested in SB
County.
Data are spread across tables with mapped relationships.
This query joins the relevant tables and uses "distinct" on the waterQuality beach ID field, selecting only one
record per beach, then "order_by" is used on the joined MD5 table to grab only the most recent record per beach.
:return: List:
Nested lists containing SQL Alchemy query results:
3 query result objects:
waterQuality, waterqualityMD5 beaches
1 string:
geometry type of associated beach
2 floats:
x and y coordinates of the associated beach
"""
session = Session()
records = session.query(waterQuality, waterQualityMD5, beaches, sqlfunc.ST_GeometryType(beaches.geom),
sqlfunc.st_x(beaches.geom), sqlfunc.st_y(beaches.geom)) \
.join(waterQualityMD5) \
.join(beaches) \
.distinct(waterQuality.beach_id) \
.order_by(waterQuality.beach_id, waterQualityMD5.insdate.desc()).all()
# Can't close session here because these results are used in another function
# session.close()
return records
def test_update_extent(self):
''' '''
package = factories.Dataset()
geojson = json.loads(self.geojson_examples[u'point'])
shape = asShape(geojson)
package_extent = PackageExtent(package_id=package[u'id'],
the_geom=WKTElement(shape.wkt,
self.db_srid))
package_extent.save()
if legacy_geoalchemy:
assert_equals(
Session.scalar(package_extent.the_geom.geometry_type),
u'ST_Point')
else:
from sqlalchemy import func
assert_equals(
Session.query(
func.ST_GeometryType(package_extent.the_geom)).first()[0],
u'ST_Point')
# Update the geometry (Point -> Polygon)
geojson = json.loads(self.geojson_examples[u'polygon'])
shape = asShape(geojson)
package_extent.the_geom = WKTElement(shape.wkt, self.db_srid)
package_extent.save()
assert_equals(package_extent.package_id, package[u'id'])
if legacy_geoalchemy:
assert_equals(
Session.scalar(package_extent.the_geom.geometry_type),
u'ST_Polygon')
assert_equals(
Session.scalar(package_extent.the_geom.srid),
self.db_srid)
else:
assert_equals(
Session.query(
func.ST_GeometryType(package_extent.the_geom)).first()[0],
u'ST_Polygon')
assert_equals(package_extent.the_geom.srid, self.db_srid)
def organisation(id):
org = Organisation.query.get(id)
points = org.other_features.filter(
func.ST_GeometryType(Feature.geometry) == 'ST_Point').all()
other_features = org.other_features.filter(
func.ST_GeometryType(Feature.geometry) != 'ST_Point')
features_by_type = {}
for f in other_features:
feature_type_key = f.data['properties']['feature'].split(':')[0]
if f.name is not None:
f.data['properties']['content'] = f.name
if feature_type_key not in features_by_type.keys():
features_by_type[feature_type_key] = [f.data]
else:
features_by_type[feature_type_key].append(f.data)
return render_template('organisation.html',
organisation=org,
points=points,
features_by_type=features_by_type)
def test_spatial_extra_edit(self):
''' '''
app = self._get_test_app()
user = factories.User()
env = {u'REMOTE_USER': user[u'name'].encode(u'ascii')}
dataset = factories.Dataset(user=user)
offset = toolkit.url_for(controller=u'package',
action=u'edit',
id=dataset[u'id'])
res = app.get(offset, extra_environ=env)
form = res.forms[1]
form[u'extras__0__key'] = u'spatial'
form[u'extras__0__value'] = self.geojson_examples[u'point']
res = helpers.submit_and_follow(app, form, env, u'save')
assert u'Error' not in res, res
res = app.get(offset, extra_environ=env)
form = res.forms[1]
form[u'extras__0__key'] = u'spatial'
form[u'extras__0__value'] = self.geojson_examples[u'polygon']
res = helpers.submit_and_follow(app, form, env, u'save')
assert u'Error' not in res, res
package_extent = Session.query(PackageExtent) \
.filter(PackageExtent.package_id == dataset[u'id']).first()
assert_equals(package_extent.package_id, dataset[u'id'])
if legacy_geoalchemy:
assert_equals(
Session.scalar(package_extent.the_geom.geometry_type),
u'ST_Polygon')
assert_equals(Session.scalar(package_extent.the_geom.srid),
self.db_srid)
else:
from sqlalchemy import func
assert_equals(
Session.query(func.ST_GeometryType(
package_extent.the_geom)).first()[0], u'ST_Polygon')
assert_equals(package_extent.the_geom.srid, self.db_srid)
def test_ST_GeometryType(self):
from sqlalchemy.sql import select, func
lake_id = self._create_one()
s = select([func.ST_GeometryType(Lake.__table__.c.geom)])
r1 = session.execute(s).scalar()
eq_(r1, 'ST_LineString')
lake = session.query(Lake).get(lake_id)
r2 = session.execute(lake.geom.ST_GeometryType()).scalar()
eq_(r2, 'ST_LineString')
r3 = session.query(Lake.geom.ST_GeometryType()).scalar()
eq_(r3, 'ST_LineString')
r4 = session.query(Lake).filter(
Lake.geom.ST_GeometryType() == 'ST_LineString').one()
ok_(isinstance(r4, Lake))
eq_(r4.id, lake_id)
def test_spatial_extra_edit(self, app):
user = factories.User()
env = {"REMOTE_USER": user["name"].encode("ascii")}
dataset = factories.Dataset(user=user)
offset = url_for("dataset.edit", id=dataset["id"])
res = app.get(offset, extra_environ=env)
data = {
"name": dataset['name'],
"extras__0__key": u"spatial",
"extras__0__value": self.geojson_examples["point"]
}
res = app.post(offset, environ_overrides=env, data=data)
assert "Error" not in res, res
res = app.get(offset, extra_environ=env)
data = {
"name": dataset['name'],
"extras__0__key": u"spatial",
"extras__0__value": self.geojson_examples["polygon"]
}
res = app.post(offset, environ_overrides=env, data=data)
assert "Error" not in res, res
package_extent = (Session.query(PackageExtent).filter(
PackageExtent.package_id == dataset["id"]).first())
assert package_extent.package_id == dataset["id"]
from sqlalchemy import func
assert (Session.query(func.ST_GeometryType(
package_extent.the_geom)).first()[0] == "ST_Polygon")
assert package_extent.the_geom.srid == self.db_srid
def add_airspace(self, country_code, airspace_class, name, base, top, geom_str):
try:
geom = loads(geom_str)
except ReadingError:
print name + "(" + airspace_class + ") is not a polygon (maybe not enough points?)"
return False
# orient polygon clockwise
geom = polygon.orient(geom, sign=-1)
if not airspace_class:
print name + " has no airspace class"
return False
base = self.normalise_height(base, name)
top = self.normalise_height(top, name)
flightlevel_re = re.compile(r'^FL (\d+)$')
match = flightlevel_re.match(base)
if match and int(match.group(1)) >= 200:
print name + " has it's base above FL 200 and is therefore disregarded"
return False
airspace = Airspace()
airspace.country_code = country_code
airspace.airspace_class = airspace_class
airspace.name = name
airspace.base = base
airspace.top = top
# Check geometry type, disregard everything except POLYGON
if geom.geom_type != 'Polygon':
print name + " is not a polygon (it's a " + geom.geom_type + ")"
return False
wkb = from_shape(geom, srid=4326)
# Try to fix invalid (self-intersecting) geometries
valid_dump = (func.ST_Dump(func.ST_MakeValid(wkb))).geom
valid_query = db.session.query(func.ST_SetSRID(valid_dump, 4326)).order_by(func.ST_Area(valid_dump).desc()).first()
if not valid_query:
print 'Error importing ' + name
print 'Could not validate geometry'
return False
else:
wkb = valid_query[0]
geom_type = db.session.query(func.ST_GeometryType(wkb)).first()[0]
if geom_type != 'ST_Polygon':
print name + " got some errors makeing it valid..."
return False
tolerance = 0.0000001
simplify = lambda x: func.ST_SimplifyPreserveTopology(x, tolerance)
airspace.the_geom = case(
[
(func.ST_IsValid(wkb), wkb),
(func.ST_IsValid(simplify(wkb)), simplify(wkb)),
],
else_=None)
db.session.add(airspace)
return True
def test_spatial_extra_edit(self, app):
user = factories.User()
env = {"REMOTE_USER": user["name"].encode("ascii")}
dataset = factories.Dataset(user=user)
if tk.check_ckan_version(min_version="2.9"):
offset = url_for("dataset.edit", id=dataset["id"])
else:
offset = url_for(controller="package", action="edit", id=dataset["id"])
res = app.get(offset, extra_environ=env)
if tk.check_ckan_version(min_version="2.9"):
data = {
"name": dataset['name'],
"extras__0__key": u"spatial",
"extras__0__value": self.geojson_examples["point"]
}
res = app.post(offset, environ_overrides=env, data=data)
else:
form = res.forms[1]
form['extras__0__key'] = u'spatial'
form['extras__0__value'] = self.geojson_examples['point']
res = helpers.submit_and_follow(app, form, env, 'save')
assert "Error" not in res, res
res = app.get(offset, extra_environ=env)
if tk.check_ckan_version(min_version="2.9"):
data = {
"name": dataset['name'],
"extras__0__key": u"spatial",
"extras__0__value": self.geojson_examples["polygon"]
}
res = app.post(offset, environ_overrides=env, data=data)
else:
form = res.forms[1]
form['extras__0__key'] = u'spatial'
form['extras__0__value'] = self.geojson_examples['polygon']
res = helpers.submit_and_follow(app, form, env, 'save')
assert "Error" not in res, res
package_extent = (
Session.query(PackageExtent)
.filter(PackageExtent.package_id == dataset["id"])
.first()
)
assert package_extent.package_id == dataset["id"]
from sqlalchemy import func
assert (
Session.query(
func.ST_GeometryType(package_extent.the_geom)
).first()[0]
== "ST_Polygon"
)
assert package_extent.the_geom.srid == self.db_srid