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