コード例 #1
0
def test_length_geom_linestring_missing_epsg_from_global_settings(session):
    if session.bind.name == "postgresql":
        pytest.skip(
            "Postgres already has a constrain that checks on the length")
    factories.ChannelFactory(the_geom="SRID=4326;LINESTRING("
                             "-0.38222938832999598 -0.13872236685816669, "
                             "-0.38222930900909202 -0.13872236685816669)", )
    factories.ChannelFactory(the_geom="SRID=4326;LINESTRING("
                             "-0.38222938468305784 -0.13872235682908687, "
                             "-0.38222931083256106 -0.13872235591735235, "
                             "-0.38222930992082654 -0.13872207236791409, "
                             "-0.38222940929989008 -0.13872235591735235)", )

    q = Query(models.Channel).filter(
        geo_func.ST_Length(
            geo_func.ST_Transform(
                models.Channel.the_geom,
                Query(models.GlobalSetting.epsg_code).limit(1))) < 0.05)
    check_length_linestring = QueryCheck(
        column=models.Channel.the_geom,
        invalid=q,
        message=
        "Length of the v2_channel is too short, should be at least 0.05m",
    )

    errors = check_length_linestring.get_invalid(session)
    assert len(errors) == 0
コード例 #2
0
ファイル: database.py プロジェクト: rl-institut/NESP2_website
def query_generation_assets():
    """Look for on and off grid generation assets"""

    res = db_session.query(
        GenerationAssets.name,
        func.ST_AsText(
            func.ST_Transform(
                func.ST_GeomFromWKB(GenerationAssets.geom, srid=3857),
                4326)).label("geom"),
        GenerationAssets.capacity_kw,
        GenerationAssets.asset_type,
        GenerationAssets.technology_type,
    )

    features = []
    for r in res:
        if r.geom is not None:
            gjson = Feature(
                geometry=Point(loadswkt(r.geom).coords[0]),
                properties={
                    "name": r.name,
                    "capacity_kw": r.capacity_kw,
                    "technology_type": r.technology_type,
                    "asset_type": r.asset_type,
                },
            )
            features.append(gjson)

    return FeatureCollection(features)
コード例 #3
0
def geojson_to_centroid_wkt(feature, src_srid, dest_srid=4326):
    wkt = WKTElement(
        shape(geojson.loads(json.dumps(feature))).centroid.wkt, src_srid)
    if src_srid != dest_srid:
        wkt = functions.ST_Transform(wkt, dest_srid)

    return ST_Force_2D(wkt)
コード例 #4
0
 def converted_geom(self):
     return geojson.loads(
         json.dumps(
             json.loads(
                 db.session.scalar(
                     func.ST_AsGeoJSON(func.ST_Transform(self.geom,
                                                         4326))))))
コード例 #5
0
def get_county_geojson(bwks=None):
    # Please notice the missing filter statement!
    if bwks:
        query = db_session.query(
            MergedDistrictDiff.bwk,
            functions.ST_AsGeoJSON(
                functions.ST_Union(
                    functions.ST_Transform(MergedDistrictDiff.geom,
                                           4326))).label("geom"),
            *sum_party_results(MergedDistrictDiff)).filter(
                MergedDistrictDiff.bwk.in_(bwks)).group_by(
                    MergedDistrictDiff.bwk)
    else:
        query = db_session.query(
            MergedDistrictDiff.bwk,
            functions.ST_AsGeoJSON(
                functions.ST_Union(
                    functions.ST_Transform(MergedDistrictDiff.geom,
                                           4326))).label("geom"),
            *sum_party_results(MergedDistrictDiff)).group_by(
                MergedDistrictDiff.bwk)

    geojsons = []
    for bwk, geom, cdu, spd, gruene, die_linke, fdp, afd in query.all():
        candidates = db_session.query(Candidate).filter(Candidate.bwk == bwk)
        geojson = json.loads(geom)
        geojson['properties'] = {
            'bwk': bwk,
            'candidates': {
                candidate.party_key(): candidate.get_json()
                for candidate in candidates
            },
            'result': {
                'cdu': int(cdu),
                'spd': int(spd),
                'gruene': int(gruene),
                'die_linke': int(die_linke),
                'fdp': int(fdp),
                'afd': int(afd)
            }
        }
        geojsons.append(geojson)

    return geojsons
コード例 #6
0
def get_district_geojson(district):
    query = db_session.query(
        district,
        functions.ST_AsGeoJSON(functions.ST_Transform(district.geom, 4326)))

    geojsons = []
    for district, geom in query.all():
        geojson = json.loads(geom)
        geojson["properties"] = district.get_geojson_dict()
        geojsons.append(geojson)

    return geojsons
コード例 #7
0
def buildGeom(lat, lon):
    success = False
    geom = None
    if lat and lon:
        try:
            wkt = 'POINT({0} {1})'.format(lon, lat)
            geom = func.ST_Transform(WKTElement(wkt, srid=4326), 2913)
            success = True
        except Exception as e:
            msg = "failed to convert lat {0} lon {1} to geom: ".format(
                lat, lon)
            app.logger.warn(msg)
    return success, geom
コード例 #8
0
ファイル: database.py プロジェクト: rl-institut/NESP2_website
def query_osm_power_lines():
    lines = db_session.query(
        func.ST_Transform(PowerLines.geom, 4326).label("geom"))

    features = []

    for r in lines:
        if r.geom is not None:
            features.append(
                Feature(geometry=LineString(
                    loadswkb(bytes(r.geom.data)).coords), ))

    return FeatureCollection(features)
コード例 #9
0
def geometry(obj):
    dataObj = dictClassData.get(obj, None)
    if not dataObj:
        return jsonify('error')
    # on fait une requête pour ne sélectionner que les géométries, en faisant la transfromation en geojson
    query = select([
        dictClassData[obj].gid.label('gid'),
        func.ST_AsGeoJSON(func.ST_Transform(dictClassData[obj].geom,
                                            4326)).label('geom')
    ]).where(dictClassData[obj].geom != None)
    dataQuery = db.session.execute(query).fetchall()
    geom_all = []
    for data in dataQuery:
        data = dict(data)
        geom_all.append({'gid': data['gid'], 'geometry': data['geom']})
    return jsonify(geom_all)
コード例 #10
0
ファイル: test_db.py プロジェクト: walkframe/d2a
    def test_polygon(self, srid, LakeTable, polygon_biwa, polygon_hamana,
                     insert_lake_biwa, insert_lake_hamana):
        # Lake Biwa: 670km^2
        # Lake Hamana: 65km^2

        # lake area > 600km^2
        where = functions.ST_Area(
            functions.ST_Transform(LakeTable.c.geom,
                                   26986)) > 600 * 1000 * 1000
        stmt = select([
            LakeTable.c.geom,
        ]).select_from(LakeTable).order_by(LakeTable.c.id).where(where)
        actual = self._callFUT(stmt)
        assert len(actual) == 1
        polygon = wkb.loads(actual[0]['geom'].tobytes())
        assert str(polygon) == polygon_biwa
コード例 #11
0
ファイル: database.py プロジェクト: rl-institut/NESP2_website
def query_osm_power_stations():
    res = db_session.query(
        func.ST_AsText(
            func.ST_Transform(func.ST_AsEWKB(PowerStations.geom),
                              4326)).label("geom"),
        PowerStations.tags,
    )

    features = []

    for r in res:
        if r.geom is not None:
            features.append(
                Feature(geometry=Point(loadswkt(r.geom).coords[0]),
                        properties=r.tags))

    return FeatureCollection(features)
コード例 #12
0
def get_ndvi():
    # print(dict(request.data))
    # data = Ndvi.query.filter(Ndvi.geom!=None).all()
    query = select([
        Ndvi.gid.label('gid'),
        func.ST_AsGeoJSON(func.ST_Transform(Ndvi.geom, 4326)).label('geom')
    ]).where(Ndvi.geom != None)
    dataQuery = db.session.execute(query).fetchall()
    data_all = []

    for ndvi in dataQuery:
        ndvi = dict(ndvi)
        data_all.append({
            'type': 'Feature',
            'properties': {
                'gid': ndvi['gid'],
            },
            'geometry': json.loads(ndvi['geom'])
        })
    return jsonify(data_all)
コード例 #13
0
def elevation(conn: directive.connection,
              tables: directive.tables,
              dem: directive.dem_file,
              oid: hug.types.number,
              segments: hug.types.in_range(1, 500) = 100):
    "Return the elevation profile of the way."

    if dem is None:
        raise hug.HTTPNotFound()

    r = tables.ways.data

    sel = sa.select([
        gf.ST_Points(
            gf.ST_Collect(
                gf.ST_PointN(r.c.geom, 1),
                gf.ST_LineInterpolatePoints(r.c.geom, 1.0 / segments))),
        sa.func.ST_Length2dSpheroid(
            gf.ST_Transform(r.c.geom, 4326),
            'SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]]'
        )
    ]).where(r.c.id == oid)

    res = conn.execute(sel).first()

    if res is None:
        raise hug.HTTPNotFound()

    geom = to_shape(res[0])
    ele = RouteElevation(oid, dem, geom.bounds)

    xcoord, ycoord = zip(*((p.x, p.y) for p in geom))
    geomlen = res[1]
    pos = [geomlen * i / float(segments) for i in range(segments + 1)]

    ele.add_segment(xcoord, ycoord, pos)

    return ele.as_dict()
コード例 #14
0
def info(conn: directive.connection, tables: directive.tables,
         osmdata: directive.osmdata, locale: directive.locale,
         oid: hug.types.number):
    "Return general information about the way."

    r = tables.ways.data
    o = osmdata.way.data
    ws = tables.joined_ways.data

    w = tables.ways.data
    geom = sa.select([gf.ST_Collect(w.c.geom).label('geom')])\
             .where(ws.c.id == oid)\
             .where(w.c.id == ws.c.child)\
             .alias()

    fields = [
        r.c.id, r.c.name, r.c.intnames, r.c.symbol, r.c.ref, r.c.piste,
        o.c.tags, geom
    ]

    sql = sa.select(fields).where(r.c.id == oid).where(o.c.id == oid).alias()

    fields = list(sql.c)
    fields.append(
        sa.func.ST_Length2dSpheroid(
            gf.ST_Transform(sql.c.geom, 4326),
            'SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]]'
        ).label("length"))
    fields.append(sql.c.geom.ST_Envelope().label('bbox'))

    row = conn.execute(sa.select(fields)).first()

    if row is None:
        raise hug.HTTPNotFound()

    return DetailedRouteItem(row, locale, objtype='wayset')
コード例 #15
0
def elevation(conn: directive.connection,
              tables: directive.tables,
              dem: directive.dem_file,
              oid: hug.types.number,
              segments: hug.types.in_range(1, 500) = 100):
    "Return the elevation profile of the route."

    if dem is None:
        raise hug.HTTPNotFound()

    r = tables.routes.data

    sel = sa.select([gf.ST_Points(gf.ST_Collect(
                         gf.ST_PointN(r.c.geom, 1),
                         gf.ST_LineInterpolatePoints(r.c.geom, 1.0/segments))),
                     sa.func.ST_Length2dSpheroid(gf.ST_Transform(r.c.geom, 4326),
                           'SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]]')
                    ]).where(r.c.id == oid)\
            .where(r.c.geom.ST_GeometryType() == 'ST_LineString')

    res = conn.execute(sel).first()

    if res is not None:
        geom = to_shape(res[0])
        ele = RouteElevation(oid, dem, geom.bounds)

        xcoord, ycoord = zip(*((p.x, p.y) for p in geom))
        geomlen = res[1]
        pos = [geomlen * i / float(segments) for i in range(segments + 1)]

        ele.add_segment(xcoord, ycoord, pos)

        return ele.as_dict()

    # special treatment for multilinestrings
    sel = sa.select([r.c.geom,
                     sa.literal_column("""ST_Length2dSpheroid(ST_MakeLine(ARRAY[ST_Points(ST_Transform(geom,4326))]),
                             'SPHEROID[\"WGS 84\",6378137,298.257223563,AUTHORITY["EPSG",\"7030\"]]')"""),
                     r.c.geom.ST_NPoints(),
                     gf.ST_Length(r.c.geom)])\
                .where(r.c.id == oid)

    res = conn.execute(sel).first()

    if res is None or res[0] is None:
        raise hug.HTTPNotFound()

    geom = to_shape(res[0])
    # Computing length in Mercator is slightly off, correct it via the
    # actual length.
    dist_fac = res[1] / res[3]
    ele = RouteElevation(oid, dem, geom.bounds)

    if res[2] > 10000:
        geom = geom.simplify(res[2] / 500, preserve_topology=False)
    elif res[2] > 4000:
        geom = geom.simplify(res[2] / 1000, preserve_topology=False)

    prev = None
    for seg in geom:
        p = seg.coords[0]
        xcoords = array('d', [p[0]])
        ycoords = array('d', [p[1]])
        pos = array('d')
        if prev is not None:
            pos.append(prev[2][-1] + \
                    Point(prev[0][-1], prev[1][-1]).distance(Point(*p)) * dist_fac)
        else:
            pos.append(0.0)
        for p in seg.coords[1:]:
            pos.append(pos[-1] +
                       Point(xcoords[-1], ycoords[-1]).distance(Point(*p)) *
                       dist_fac)
            xcoords.append(p[0])
            ycoords.append(p[1])

        ele.add_segment(xcoords, ycoords, pos)
        prev = (xcoords, ycoords, pos)

    return ele.as_dict()
コード例 #16
0
def transform(col):
    return geo_func.ST_Transform(col, epsg_code_query())
コード例 #17
0
    def _handle_parameters(self):

        response = self.request.response

        # Make sure the _LOCATION_ cookie is correctly set: The old version GUI
        # version used to store the map center and the zoom level which is not
        # understood by new GUI (which stores the map extent as 4 coordinates)
        if '_LOCATION_' in self.request.cookies:
            c = urllib.parse.unquote(self.request.cookies['_LOCATION_'])
            if len(c.split('|')) == 3:
                response.delete_cookie('_LOCATION_')

        # Check if language (_LOCALE_) is set
        if self.request is not None:
            if '_LOCALE_' in self.request.params:
                response.set_cookie('_LOCALE_',
                                    self.request.params.get('_LOCALE_'),
                                    timedelta(days=90))
            elif '_LOCALE_' in self.request.cookies:
                pass

        # Check if profile (_PROFILE_) is set
        if self.request is not None:
            # TODO
            if '_PROFILE_' in self.request.params:
                # Set the profile cookie
                profile_code = self.request.params.get('_PROFILE_')
                response.set_cookie('_PROFILE_', profile_code,
                                    timedelta(days=90))

                # Update _LOCATION_ from cookies to profile geometry bbox
                # retrieved from database
                profile_db = DBSession.query(Profile). \
                    filter(Profile.code == profile_code). \
                    first()

                if profile_db is not None:

                    # Calculate and transform bounding box
                    # bbox = DBSession.scalar(geofunctions.ST_Envelope(profile_db.geometry))
                    bbox = DBSession.scalar(
                        geofunctions.ST_Envelope(
                            geofunctions.ST_Transform(profile_db.geometry,
                                                      900913)))

                    gjson = geojson.loads(
                        DBSession.scalar(geofunctions.ST_AsGeoJSON(bbox)))

                    coords = gjson['coordinates'][0]
                    p1 = coords[0]
                    p2 = coords[2]

                    l = '%s,%s' % (','.join([str(x) for x in p1]), ','.join(
                        [str(x) for x in p2]))

                    response.set_cookie('_LOCATION_', urllib.parse.quote(l),
                                        timedelta(days=90))

            elif '_PROFILE_' in self.request.cookies:
                # Profile already set, leave it
                pass
            else:
                # If no profile is set, set the default profile
                response.set_cookie('_PROFILE_',
                                    get_default_profile(self.request),
                                    timedelta(days=90))