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
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)
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)
def converted_geom(self):
return geojson.loads(
json.dumps(
json.loads(
db.session.scalar(
func.ST_AsGeoJSON(func.ST_Transform(self.geom,
4326))))))
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
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
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
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)
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)
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
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)
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)
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()
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')
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()
def transform(col):
return geo_func.ST_Transform(col, epsg_code_query())
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))