Esempio n. 1
0
    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
Esempio n. 3
0
    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)
Esempio n. 4
0
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)
Esempio n. 6
0
    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)
Esempio n. 7
0
    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
Esempio n. 8
0
    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
Esempio n. 9
0
    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