def test_ST_DumpPoints(self):
from sqlalchemy.sql import func
from geoalchemy2 import WKBElement
lake_id = self._create_one()
lake = session.query(Lake).get(lake_id)
dump = lake.geom.ST_DumpPoints()
q = session.query(dump.path.label('path'),
dump.geom.label('geom')).all()
eq_(len(q), 2)
p1 = q[0]
ok_(isinstance(p1.path, list))
eq_(p1.path, [1])
ok_(isinstance(p1.geom, WKBElement))
p1 = session.execute(func.ST_AsText(p1.geom)).scalar()
eq_(p1, 'POINT(0 0)')
p2 = q[1]
ok_(isinstance(p2.path, list))
eq_(p2.path, [2])
ok_(isinstance(p2.geom, WKBElement))
p2 = session.execute(func.ST_AsText(p2.geom)).scalar()
eq_(p2, 'POINT(1 1)')
def test_intersection(self):
l = session.query(Lake).filter(Lake.lake_name == 'Lake Blue').one()
r = session.query(Road).filter(Road.road_name == 'Geordie Rd').one()
s = session.query(Spot).filter(Spot.spot_height == 454.66).one()
eq_(
session.scalar(
func.ST_AsText(l.lake_geom.intersection(s.spot_location))),
'GEOMETRYCOLLECTION EMPTY')
eq_(
session.scalar(
func.ST_AsText(
session.scalar(l.lake_geom.intersection(r.road_geom)))),
'POINT(-89.0710987261147 43.243949044586)')
l = session.query(Lake).filter(Lake.lake_name == 'Lake White').one()
r = session.query(Road).filter(Road.road_name == 'Paul St').one()
eq_(
session.scalar(
func.ST_AsText(
session.scalar(l.lake_geom.intersection(r.road_geom)))),
'LINESTRING(-88.1430673666454 42.6255500261493,-88.1140839697546 42.6230657349872)'
)
ok_(
session.query(Lake).filter(
Lake.lake_geom.intersection(r.road_geom).wkt ==
'LINESTRING(-88.1430673666454 42.6255500261493,-88.1140839697546 42.6230657349872)'
).first() is not None)
def column_expression(self, col):
if self.coerce == "text":
return func.ST_AsText(col, type_=self)
elif self.coerce == "binary":
return func.ST_AsBinary(col, type_=self)
else:
assert False
def column_expression(self, col):
value = ST_AsText(col, type_=self)
if value is None:
value = func.ST_AsText(col, type_=self)
return value
def getSamplingFeatureByGeometry(self,wkt_geometry):
try:
#ST_Equals(geometry, geometry)
return self._session.query(Samplingfeature).filter(func.ST_AsText(Samplingfeature.FeatureGeometry) == func.ST_AsText(wkt_geometry)).first()
except Exception, e:
print e
return None
def point_elevation(geometry, format_out, dataset):
"""
Performs PostGIS query to enrich a point geometry.
:param geometry: Input point to be enriched with elevation
:type geometry: shapely.geometry.Point
:param format_out: Specifies output format. One of ['geojson', 'point']
:type format_out: string
:param dataset: Elevation dataset to use for querying
:type dataset: string
:raises InvalidUsage: internal HTTP 500 error with more detailed description.
:returns: 3D Point as GeoJSON or WKT
:rtype: string
"""
Model = _getModel(dataset)
input_crs = _get_crs(dataset)
if geometry.geom_type == "Point":
query_point2d = db.session \
.query(ST_Transform(func.ST_SetSRID(func.St_PointFromText(geometry.wkt), 4326), input_crs).label('geom')) \
.subquery() \
.alias('points2d')
query_getelev = db.session \
.query(ST_Transform(query_point2d.c.geom, 4326).label('geom'),
ST_Value(Model.rast, query_point2d.c.geom, False).label('z')) \
.filter(ST_Intersects(Model.rast, query_point2d.c.geom)) \
.subquery().alias('getelevation')
if format_out == 'geojson':
query_final = db.session \
.query(func.ST_AsGeoJSON(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
ST_Y(query_getelev.c.geom),
query_getelev.c.z.cast(Integer)),
coord_precision)))
else:
query_final = db.session \
.query(func.ST_AsText(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
ST_Y(query_getelev.c.geom),
query_getelev.c.z.cast(Integer)),
coord_precision)))
else:
raise InvalidUsage(
400, 4002,
"Needs to be a Point, not {}!".format(geometry.geom_type))
try:
result = query_final.one()
return result[0]
except NoResultFound:
raise InvalidUsage(
404, 4002,
f'{tuple(geometry.coords)[0]} has no elevation value in {dataset}')
def point_elevation(geometry, format_out, dataset):
"""
Performs PostGIS query to enrich a point geometry.
:param geometry: Input point to be enriched with elevation
:type geometry: shapely.geometry.Point
:param format_out: Specifies output format. One of ['geojson', 'point']
:type format_out: string
:param dataset: Elevation dataset to use for querying
:type dataset: string
:raises InvalidUsage: internal HTTP 500 error with more detailed description.
:returns: 3D Point as GeoJSON or WKT
:rtype: string
"""
Model = _getModel(dataset)
if geometry.geom_type == "Point":
query_point2d = db.session \
.query(func.ST_SetSRID(func.St_PointFromText(geometry.wkt), 4326).label('geom')) \
.subquery() \
.alias('points2d')
query_getelev = db.session \
.query(query_point2d.c.geom,
ST_Value(Model.rast, query_point2d.c.geom).label('z')) \
.filter(ST_Intersects(Model.rast, query_point2d.c.geom)) \
.subquery().alias('getelevation')
if format_out == 'geojson':
query_final = db.session \
.query(func.ST_AsGeoJSON(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
ST_Y(query_getelev.c.geom),
query_getelev.c.z),
coord_precision)))
else:
query_final = db.session \
.query(func.ST_AsText(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
ST_Y(query_getelev.c.geom),
query_getelev.c.z),
coord_precision)))
else:
raise InvalidUsage(
400, 4002,
"Needs to be a Point, not {}!".format(geometry.geom_type))
try:
return query_final[0][0]
except:
raise InvalidUsage(
404, 4002,
'The requested geometry is outside the bounds of {}'.format(
dataset))
def dump():
app.config.from_object('config.default')
database.init_app(app)
place_ids = [int(line[:-1]) for line in open('top_place_ids')]
q = Place.query.filter(Place.place_id.in_(place_ids)).add_columns(func.ST_AsText(Place.geom))
for place, geom in q:
d = object_as_dict(place)
d['geom'] = geom
click.echo(d)
def tasks_page(task_id):
if not re.match(
r'^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$',
task_id):
return render_template("tasknotfound.html"), 404
try:
task = db_session.query(Task).filter(Task.id == task_id).one()
task_info = db_session.query(Task.id, \
Task.log_actual_point_count, \
Task.log_execution_time, \
func.ST_AsText(Task.geom).label('wkt'), \
func.ST_XMin(Task.geom).label('minx'), \
func.ST_YMin(Task.geom).label('miny'), \
func.ST_XMax(Task.geom).label('maxx'), \
func.ST_YMax(Task.geom).label('maxy'), \
(Task.log_actual_point_count/func.ST_Area(Task.geom)).label('density'), \
).filter(Task.id==task_id).one()
task_dict = task_info._asdict()
task_dict['classes'] = task.get_classnames()
except NoResultFound:
return render_template("tasknotfound.html"), 404
status = task.get_status()
datasets = Dataset.query.all()
dataset_i = datasets.index(task.dataset)
if status == 'SUCCESS':
filename = app.config['RESULTS_FOLDER'] + task_id + '.laz'
if (os.path.exists(filename)):
return render_template("index.html",
active_dataset_i=dataset_i,
datasets=datasets,
task=task_dict,
status='okay',
download_url=task.get_relative_url())
else:
return render_template("index.html",
active_dataset_i=dataset_i,
datasets=datasets,
task=task_dict,
status='deleted')
elif status == 'PENDING' or status == 'RETRY':
return render_template("index.html",
active_dataset_i=dataset_i,
datasets=datasets,
task=task_dict,
status='pending',
refresh=True)
else:
return render_template("index.html",
active_dataset_i=dataset_i,
datasets=datasets,
task=task_dict,
status='failure')
def process_formdata(self, valuelist):
super(GeoJSONField, self).process_formdata(valuelist)
if str(self.data) is '':
self.data = None
if self.data is not None:
web_shape = self.session.scalar(
func.ST_AsText(
func.ST_Transform(
func.ST_GeomFromText(
shape(self.data).wkt, self.web_srid),
self.transform_srid)))
self.data = 'SRID=' + str(self.srid) + ';' + str(web_shape)
def get_trips():
vertices = request.form.get('vertices')
start_date = request.form.get('date_start')
end_date = request.form.get('date_end')
show_pickups = request.form.get('show_pickups')
show_dropoffs = request.form.get('show_dropoffs')
vertices = vertices.split(',')
pickup_points = []
dropoff_points = []
# We are grabbing the pickup and dropoff points in two separate queries, and adding them together at the end
if show_pickups == 'true':
trips = db.session.query(Trip).with_entities(func.count(Trip.pickup), func.ST_AsText(Trip.pickup));
trips = append_query_trips(trips, (len(vertices) == 4), vertices);
pickup_points = trips.filter(Trip.date.between(start_date, end_date)).group_by(Trip.pickup).all();
if show_dropoffs == 'true':
trips = db.session.query(Trip).with_entities(func.count(Trip.dropoff), func.ST_AsText(Trip.dropoff));
trips = append_query_trips(trips, (len(vertices) == 4), vertices);
dropoff_points = trips.filter(Trip.date.between(start_date, end_date)).group_by(Trip.dropoff).all();
# We are iterating through these here to assign a type to each.
result = []
for i in pickup_points:
result.append({
'point': i[1],
'count': i[0],
'type': 'pickup'
})
for i in dropoff_points:
result.append({
'point': i[1],
'count': i[0],
'type': 'dropoff'
})
return jsonify(results=result)
def search_pointings():
form = forms.SearchPointingsForm()
form.populate_graceids()
form.populate_creator_groups(current_user.get_id())
if request.method == 'POST':
formgraceid = form.graceids.data
alternateids = db.session.query(models.gw_alert).filter(
models.gw_alert.alternateid == formgraceid
).all()
if len(alternateids):
graceid = alternateids[0].graceid
else:
graceid = form.graceids.data
filter = []
filter.append(models.pointing_event.graceid.contains(graceid))
filter.append(models.pointing_event.pointingid == models.pointing.id)
if form.status_choices.data != '' and form.status_choices.data != 'all':
filter.append(models.pointing.status == form.status_choices.data)
if len(form.band_choices.data):
if "all" not in form.band_choices.data:
filter.append(models.pointing.band.in_(form.band_choices.data))
if form.my_points.data:
filter.append(models.pointing.submitterid == current_user.get_id())
filter.append(models.pointing.submitterid == models.users.id)
filter.append(models.pointing.instrumentid == models.instrument.id)
results = db.session.query(models.pointing.id,
func.ST_AsText(models.pointing.position).label('position'),
models.pointing.instrumentid,
models.pointing.band,
models.pointing.pos_angle,
models.pointing.depth,
models.pointing.time,
models.pointing.status,
models.pointing.doi_url,
models.pointing.doi_id,
models.instrument.instrument_name,
models.users.username,
models.pointing.submitterid
).filter(*filter).all()
return render_template('search_pointings.html', form=form, search_result=results)
return render_template('search_pointings.html', form=form)
def main(argv=sys.argv):
if len(argv) < 2:
usage(argv)
config_uri = argv[1]
filepath = argv[2]
setup_logging(config_uri)
settings = get_appsettings(config_uri)
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession.configure(bind=engine)
Base.metadata.create_all(engine)
with transaction.manager:
# script
with collection(filepath) as cities:
for city in cities:
data = extract_adminzone_data(city)
az = AdminZone(**data)
DBSession.add(az)
cities_with_arr = [{
'name': 'PARIS',
'code_city': '056',
'code_department': '75'
}, {
'name': 'MARSEILLE',
'code_city': '055',
'code_department': '13'
}, {
'name': 'LYON',
'code_city': '123',
'code_department': '69'
}]
with transaction.manager:
for city in cities_with_arr:
wkt_geoms, populations = zip(*DBSession.query(func.ST_AsText(AdminZone.geometry), AdminZone.population)\
.filter(AdminZone.name.contains(city['name']))\
.filter(AdminZone.admin_level==ADMIN_LEVEL_CITY_ARR)\
.all())
city_geom = cascaded_union(
[loads(wkt_geom) for wkt_geom in wkt_geoms])
if city_geom.type == 'Polygon':
city_geom = MultiPolygon([city_geom])
az = AdminZone(
geometry="SRID=%s;" % SRID + city_geom.wkt,
admin_level=ADMIN_LEVEL_CITY,
name=city['name'],
code_city=city['code_city'],
code_department=city['code_department'],
population=sum(populations),
)
DBSession.add(az)
def get_data(keywords: str) -> pd.DataFrame:
"""
get poi related data from POI table
Parameters
----------
keywords : str
search for a particular tag
Returns
----------
pd.DataFrame
DataFrame read from POI
Warning
----------
Required for UBD package
See Also
----------
ubd.dbconnect.postgres : database connector basing on sqlalchemy
ubd.geofunc.GeohashOperator : geohash operation class
ubd.dbconnect.POI : POI table class
"""
db = postgres()
go = GeohashOperator()
Session = sessionmaker(bind=db.engine)
session = Session()
df_aoi = pd.read_sql(
session.query(POI.name,
func.ST_AsText(POI.aoi).label('geohash'), POI.province,
POI.area, POI.district).filter(
POI.tag.match(f"%{keywords}%")).filter(
POI.aoi.isnot(None)).statement, session.bind)
df_poi = pd.read_sql(
session.query(POI.name,
func.substr(POI.geohash, 1, 7).label('geohash'),
POI.province, POI.area, POI.district).filter(
POI.tag.match(f"%{keywords}%")).filter(
POI.aoi.is_(None)).statement, session.bind)
df_aoi['geohash'] = df_aoi['geohash'].apply(
lambda x: list(go.polygon_geohasher(wkt.loads(x), 3, 7)))
return pd.concat([df_aoi.explode('geohash'), df_poi])
def search_by_sightings(self, position_query: str) -> Dict[Bird, float]:
matches = dict()
match = re.match('position:([0-9.]+),([0-9.]+);r=([0-9.]+)',
position_query)
if match:
lat = match.group(1)
lon = match.group(2)
radius = float(match.group(3))
result = self.session.query(Bird, func.count(Sighting.bird)) \
.join(Sighting.bird) \
.join(Sighting.position) \
.filter(func.ST_DistanceSphere(func.ST_GeomFromText(func.ST_AsText(Position.point), 4326), func.ST_GeomFromText(f'POINT({lon} {lat})', 4326)) < 1000 * radius) \
.group_by(Bird) \
.all()
total = sum([count for _, count in result])
for bird, count in result:
score = count / total
matches[bird] = score
return matches
def pointings_from_IDS(ids, filter=[]):
filter.append(instrument.id == pointing.instrumentid)
filter.append(pointing_event.pointingid.in_(ids))
filter.append(pointing.id.in_(ids))
pointings = db.session.query(
pointing.id,
func.ST_AsText(pointing.position).label('position'),
pointing.instrumentid, pointing.band, pointing.pos_angle,
pointing.depth, pointing.depth_err, pointing.depth_unit,
pointing.time, pointing.status, instrument.instrument_name,
instrument.instrument_type,
pointing_event.graceid).filter(*filter).all()
pointing_returns = {}
for p in pointings:
pointing_returns[str(p.id)] = p
return pointing_returns
def test_postgis_transformation(self):
from sqlalchemy import func
v = self.session.query(func.ST_AsText(
func.ST_Transform(
func.ST_GeomFromText(
('POLYGON(('
'743238 2967416,'
'743238 2967450,'
'743265 2967450,'
'743265.625 2967416,'
'743238 2967416))'),
2249), 4326))).one()[0]
self.assertEqual(v, (
'POLYGON(('
'-71.1776848522251 42.3902896512902,'
'-71.1776843766326 42.3903829478009,'
'-71.1775844305465 42.3903826677917,'
'-71.1775825927231 42.3902893647987,'
'-71.1776848522251 42.3902896512902))'
))
def getIntersectingPoints(wktStr):
"""
Takes an EWKT string of a Strava Activity Stream's latlngs and returns a list of float points which reside within
the privacy areas.
@param wktStr: String. EWKT representation of Strava Activity Stream latlngs
@return: List of strings. Points are returned as WGS 1984 coordinate strings in the format lon,lat
"""
# geometricProj = 32610
collectionExtract = 3
# Open session
session = Session()
# Get coordinates from within privacy zones
try:
# Create a labled common table expression to query privacy zones geometries collected into a single multi-polygon
privacy_cte = session.query(
sqlfunc.ST_CollectionExtract(sqlfunc.ST_Collect(
AOI.geom), collectionExtract).label("ctelab")).filter(
AOI.privacy == "Yes").cte()
# points_cte = session.query(sqlfunc.ST_DumpPoints(sqlfunc.st_geomfromewkt(wktStr)))
# Take provided EWKT string and convert to GeoAlchemy geometry
# lineString = sqlfunc.ST_GeomFromEWKT(wktStr)
# application.logger.debug(f"Geoalchemy Geom is: \n{dir(lineString)}")
# Get a list of points from the linestring which fall inside the privacy zone
# ST_DumpPoints provides a point geometry per iterative loop which is converted to a text representation using As_Text
pointQuery = session.query(
sqlfunc.ST_AsText(
sqlfunc.ST_DumpPoints(
sqlfunc.ST_Intersection(sqlfunc.ST_GeomFromEWKT(wktStr),
privacy_cte.c.ctelab)).geom))
# pointQuery = session.query(sqlfunc.ST_AsText(
# sqlfunc.ST_DumpPoints(sqlfunc.ST_Intersection(sqlfunc.ST_GeomFromEWKT(wktStr),
# privacy_cte.c.ctelab)).geom)).filter(privacy_cte.c.ctelab.
# ST_Intersects(sqlfunc.ST_GeomFromEWKT(wktStr)))
coordinateList = []
for i in pointQuery:
# application.logger.debug(f"Point query response is: {i}")
# strip out the WKT parts of the coordinates, only want list of [lon,lat]
coordinateList.append(formatPointResponse(i))
finally:
session.close()
return coordinateList
def ajax_event_galaxies():
args = request.args
alertid = args['alertid']
event_galaxies = []
galLists = db.session.query(models.gw_galaxy_list).filter(
models.gw_galaxy_list.alertid == alertid,
).all()
galList_ids = list(set([x.id for x in galLists]))
galEntries = db.session.query(
models.gw_galaxy_entry.name,
func.ST_AsText(models.gw_galaxy_entry.position).label('position'),
models.gw_galaxy_entry.score,
models.gw_galaxy_entry.info,
models.gw_galaxy_entry.listid,
models.gw_galaxy_entry.rank,
).filter(
models.gw_galaxy_entry.listid.in_(galList_ids)
).all()
for glist in galLists:
markers = []
entries = [x for x in galEntries if x.listid == glist.id]
for e in entries:
ra, dec = function.sanatize_pointing(e.position)
markers.append({
"name":e.name,
"ra": ra,
"dec": dec,
"info":function.sanatize_gal_info(e, glist)
})
event_galaxies.append({
"name":glist.groupname,
"color":"",
"markers":markers
})
return(jsonify(event_galaxies))
async def reverse_geocode(coordinate: Coordinate,
db: Session = Depends(get_db)):
ooaza = find_ooaza(db, coordinate)
# Get polygon data as a string
polygon_text: str = db.scalar(func.ST_AsText(ooaza[3]))
# "POLYGON((x y, x y, ...))" -> ["x y", "x y", ...]
polygon_text_list = polygon_text.replace('POLYGON', '')[2:-2].split(',')
# ["x y", "x y", ...] -> [[x, y], [x, y], ...]
polygon = map(lambda t: list(map(lambda p: float(p), t.split(' '))),
polygon_text_list)
response = GeoJson(
type="FeatureCollection",
features=[
GeoJsonFeature(
type="Feature",
geometry=GeoJsonGeometry(type="LineString",
coordinates=list(polygon)),
properties=GeocodingProps(
address=f"{ooaza[0]}{ooaza[1]}{ooaza[2]}"),
)
],
)
return response
def get_clusters_per_point_query(self, session, gridpoints, riskdate):
days_prev = self.dycast_parameters.temporal_domain
enddate = riskdate
startdate = riskdate - datetime.timedelta(days=(days_prev))
points_query = self.get_points_query_from_gridpoints(gridpoints)
return session.query(func.array_agg(
func.json_build_object(
"case_id",
Case.id,
"report_date",
Case.report_date,
"location",
func.ST_AsText(Case.location)
)).label('case_array'),
points_query.c.point.geom.label('point')) \
.join(points_query, literal(True)) \
.filter(Case.report_date >= startdate,
Case.report_date <= enddate,
func.ST_DWithin(Case.location,
points_query.c.point.geom,
self.dycast_parameters.spatial_domain)) \
.group_by(points_query.c.point.geom)
def line_elevation(geometry, format_out, dataset):
"""
Performs PostGIS query to enrich a line geometry.
:param geometry: Input 2D line to be enriched with elevation
:type geometry: Shapely geometry
:param format_out: Specifies output format. One of ['geojson', 'polyline',
'encodedpolyline']
:type format_out: string
:param dataset: Elevation dataset to use for querying
:type dataset: string
:raises InvalidUsage: internal HTTP 500 error with more detailed description.
:returns: 3D line as GeoJSON or WKT
:rtype: string
"""
Model = _getModel(dataset)
if geometry.geom_type == 'LineString':
query_points2d = db.session\
.query(func.ST_SetSRID(ST_DumpPoints(geometry.wkt).geom, 4326) \
.label('geom')) \
.subquery().alias('points2d')
query_getelev = db.session \
.query(query_points2d.c.geom,
ST_Value(Model.rast, query_points2d.c.geom).label('z')) \
.filter(ST_Intersects(Model.rast, query_points2d.c.geom)) \
.subquery().alias('getelevation')
query_points3d = db.session \
.query(func.ST_SetSRID(func.ST_MakePoint(ST_X(query_getelev.c.geom),
ST_Y(query_getelev.c.geom),
query_getelev.c.z),
4326).label('geom')) \
.subquery().alias('points3d')
if format_out == 'geojson':
# Return GeoJSON directly in PostGIS
query_final = db.session \
.query(func.ST_AsGeoJson(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision))))
else:
# Else return the WKT of the geometry
query_final = db.session \
.query(func.ST_AsText(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision))))
else:
raise InvalidUsage(
400, 4002,
"Needs to be a LineString, not a {}!".format(geometry.geom_type))
# Behaviour when all vertices are out of bounds
if query_final[0][0] == None:
raise InvalidUsage(
404, 4002,
'The requested geometry is outside the bounds of {}'.format(
dataset))
return query_final[0][0]
def ajax_alertinstruments_footprints():
args = request.args
graceid = args.get('graceid')
graceid = models.gw_alert.graceidfromalternate(graceid)
pointing_status = args.get('pointing_status')
tos_mjd= float(args.get('tos_mjd'))
if pointing_status is None:
pointing_status = enums.pointing_status.completed
pointing_filter = []
pointing_filter.append(models.pointing_event.graceid == graceid)
pointing_filter.append(models.pointing_event.pointingid == models.pointing.id)
if pointing_status == 'pandc':
ors = []
ors.append(models.pointing.status == enums.pointing_status.completed)
ors.append(models.pointing.status == enums.pointing_status.planned)
pointing_filter.append(fsq.sqlalchemy.or_(*ors))
elif (pointing_status is not None and pointing_status != 'all' and pointing_status != ''):
pointing_filter.append(models.pointing.status == pointing_status)
pointing_info = db.session.query(
models.pointing.instrumentid,
models.pointing.pos_angle,
models.pointing.time,
func.ST_AsText(models.pointing.position).label('position'),
models.pointing.band,
models.pointing.depth,
models.pointing.depth_unit,
models.pointing.status
).filter(*pointing_filter).all()
instrumentids = [x.instrumentid for x in pointing_info]
instrumentinfo = db.session.query(
models.instrument.instrument_name,
models.instrument.nickname,
models.instrument.id
).filter(
models.instrument.id.in_(instrumentids)
).all()
footprintinfo = db.session.query(
func.ST_AsText(models.footprint_ccd.footprint).label('footprint'),
models.footprint_ccd.instrumentid
).filter(
models.footprint_ccd.instrumentid.in_(instrumentids)
).all()
inst_overlays = []
colorlist=['#ffe119', '#4363d8', '#f58231', '#42d4f4', '#f032e6', '#fabebe', '#469990', '#e6beff', '#9A6324', '#fffac8', '#800000', '#aaffc3', '#000075', '#a9a9a9']
for i,inst in enumerate([x for x in instrumentinfo if x.id != 49]):
name = inst.nickname if inst.nickname and inst.nickname != 'None' else inst.instrument_name
try:
color = colorlist[i]
except:
color = colors[inst.id]
pass
footprint_ccds = [x.footprint for x in footprintinfo if x.instrumentid == inst.id]
sanatized_ccds = function.sanatize_footprint_ccds(footprint_ccds)
inst_pointings = [x for x in pointing_info if x.instrumentid == inst.id]
pointing_geometries = []
for p in inst_pointings:
t = astropy.time.Time([p.time])
ra, dec = function.sanatize_pointing(p.position)
for ccd in sanatized_ccds:
pointing_footprint = function.project_footprint(ccd, ra, dec, p.pos_angle)
pointing_geometries.append({
"polygon":pointing_footprint,
"time":round(t.mjd[0]-tos_mjd, 3)
})
inst_overlays.append({
"display":True,
"name":name,
"color":color,
"contours":pointing_geometries
})
return jsonify(inst_overlays)
def calc_prob_coverage(graceid, mappathinfo, inst_cov, band_cov, depth, depth_unit, approx_cov):
cache_key = f'prob_{graceid}_{mappathinfo}_{inst_cov}_{band_cov}_{depth}_{depth_unit}_{approx_cov}'
ztfid = 47
ztf_approx_id = 76
decamid = 38
decam_approx_id = 77
approx_dict = {
ztfid: ztf_approx_id,
decamid: decam_approx_id
}
areas = []
times = []
probs = []
s3 = boto3.client('s3')
try:
with tempfile.NamedTemporaryFile() as f:
# this HP module does not appear to be able to read files from memory
# so we use a temporary file here which deletes itself as soon as the
# context manager is exited.
s3.download_fileobj(config.AWS_BUCKET, mappathinfo, f)
GWmap = hp.read_map(f.name)
#bestpixel = np.argmax(GWmap)
nside = hp.npix2nside(len(GWmap))
except ClientError:
raise HTTPException('<b>Calculator ERROR: Map not found. Please contact the administrator.</b>')
except Exception:
raise HTTPException('<b> Map ERROR. Please contact the administrator. </b>')
pointing_filter = []
pointing_filter.append(models.pointing_event.graceid == graceid)
pointing_filter.append(models.pointing.status == 'completed')
pointing_filter.append(models.pointing_event.pointingid == models.pointing.id)
pointing_filter.append(models.pointing.instrumentid != 49)
if inst_cov != '':
insts_cov = [int(x) for x in inst_cov.split(',')]
pointing_filter.append(models.pointing.instrumentid.in_(insts_cov))
if band_cov != '':
bands_cov = [x for x in band_cov.split(',')]
pointing_filter.append(models.pointing.band.in_(bands_cov))
if depth_unit != 'None' and depth_unit != '':
pointing_filter.append(models.pointing.depth_unit == depth_unit)
if depth != None and function.isFloat(depth):
if 'mag' in depth_unit:
pointing_filter.append(models.pointing.depth >= float(depth))
elif 'flux' in depth_unit:
pointing_filter.append(models.pointing.depth <= float(depth))
else:
raise HTTPException('Unknown depth unit.')
pointings_sorted = db.session.query(
models.pointing.instrumentid,
models.pointing.pos_angle,
func.ST_AsText(models.pointing.position).label('position'),
models.pointing.band,
models.pointing.depth,
models.pointing.time
).filter(
*pointing_filter
).order_by(
models.pointing.time.asc()
).all()
instrumentids = [x.instrumentid for x in pointings_sorted]
for apid in approx_dict.keys():
if apid in instrumentids:
instrumentids.append(approx_dict[apid])
#filter and query the relevant instrument footprints
footprintinfo = db.session.query(
func.ST_AsText(models.footprint_ccd.footprint).label('footprint'),
models.footprint_ccd.instrumentid
).filter(
models.footprint_ccd.instrumentid.in_(instrumentids)
).all()
#get GW T0 time
time_of_signal = db.session.query(
models.gw_alert.time_of_signal
).filter(
models.gw_alert.graceid == graceid
).filter(
models.gw_alert.time_of_signal != None
).order_by(
models.gw_alert.datecreated.desc()
).first()[0]
if time_of_signal == None:
raise HTTPException("<i><font color='red'>ERROR: Please contact administrator</font></i>")
qps = []
qpsarea=[]
NSIDE4area = 512 #this gives pixarea of 0.013 deg^2 per pixel
pixarea = hp.nside2pixarea(NSIDE4area, degrees=True)
for p in pointings_sorted:
ra, dec = function.sanatize_pointing(p.position)
if approx_cov:
if p.instrumentid in approx_dict.keys():
footprint_ccds = [x.footprint for x in footprintinfo if x.instrumentid == approx_dict[p.instrumentid]]
else:
footprint_ccds = [x.footprint for x in footprintinfo if x.instrumentid == p.instrumentid]
else:
footprint_ccds = [x.footprint for x in footprintinfo if x.instrumentid == p.instrumentid]
sanatized_ccds = function.sanatize_footprint_ccds(footprint_ccds)
for ccd in sanatized_ccds:
pointing_footprint = function.project_footprint(ccd, ra, dec, p.pos_angle)
ras_poly = [x[0] for x in pointing_footprint][:-1]
decs_poly = [x[1] for x in pointing_footprint][:-1]
xyzpoly = astropy.coordinates.spherical_to_cartesian(1, np.deg2rad(decs_poly), np.deg2rad(ras_poly))
qp = hp.query_polygon(nside,np.array(xyzpoly).T)
qps.extend(qp)
#do a separate calc just for area coverage. hardcode NSIDE to be high enough so sampling error low
qparea = hp.query_polygon(NSIDE4area, np.array(xyzpoly).T)
qpsarea.extend(qparea)
#deduplicate indices, so that pixels already covered are not double counted
deduped_indices=list(dict.fromkeys(qps))
deduped_indices_area = list(dict.fromkeys(qpsarea))
area = pixarea * len(deduped_indices_area)
prob = 0
for ind in deduped_indices:
prob += GWmap[ind]
elapsed = p.time - time_of_signal
elapsed = elapsed.total_seconds()/3600
times.append(elapsed)
probs.append(prob)
areas.append(area)
cache.set(f'{cache_key}_times', times)
cache.set(f'{cache_key}_probs', probs)
cache.set(f'{cache_key}_areas', areas)
return cache_key
def column_expression(self, col):
return func.ST_AsText(col, type_=self)
def instrument_info():
instid = request.args.get('id')
instrument = db.session.query(
models.instrument,
models.users.username,
func.ST_AsText(models.footprint_ccd.footprint).label('footprint')
).filter(
models.instrument.id == instid,
models.instrument.id == models.footprint_ccd.instrumentid,
models.instrument.submitterid == models.users.id
).all()
events_contributed = db.session.query(
models.pointing,
models.gw_alert.graceid
).filter(
models.pointing.instrumentid == instid,
models.pointing_event.pointingid == models.pointing.id,
models.gw_alert.graceid == models.pointing_event.graceid,
).all()
gids = sorted(list(set([x.graceid for x in events_contributed])), reverse=True)
events = []
for g in gids:
numevents = len([x for x in events_contributed if x.graceid == g])
events.append({'event':g, 'count':str(numevents)})
if len(instrument) > 0:
inst = instrument[0].instrument
username = instrument[0].username
sanatized_ccds = function.sanatize_footprint_ccds([x.footprint for x in instrument])
trace = []
vertices = sanatized_ccds
for vert in vertices:
xs = [v[0] for v in vert]
ys =[v[1] for v in vert]
trace1 = go.Scatter(
x=xs,
y=ys,
line_color='blue',
fill='tozeroy',
fillcolor='violet'
)
trace.append(trace1)
fig = go.Figure(data=trace)
fig.update_layout(
showlegend=False,
xaxis_title = 'degrees',
yaxis_title = 'degrees',
width=500,
height=500,
yaxis=dict(
matches='x',
scaleanchor="x",
scaleratio=1,
constrain='domain',
)
)
data = fig
graphJSON = json.dumps(data, cls=plotly.utils.PlotlyJSONEncoder)
return render_template('instrument_info.html', inst=inst, graph=graphJSON, events=events, username=username)
def get_address_by_id(item_id):
location_item = s.query(
UserLocation, func.ST_X(UserLocation.gps), func.ST_Y(UserLocation.gps),
func.ST_AsText(UserLocation.gps)).filter(
UserLocation.user_address_id == item_id).first()
return location_item
def import_db_data(cfg):
"""Import data from DB using SQLA DB models
Parameters
----------
cfg : :obj:`dict`
Config to be used to create model
Returns
-------
:obj:`dict`
Imported data
"""
data = {}
srid = int(config.get('geo', 'srid'))
session = db_session('windnode_abw')
year = pd.to_datetime(cfg['date_from']).year
datetime_index_full_year = pd.date_range(start=f'{year}-01-01 00:00:00',
end=f'{year}-12-31 23:00:00',
freq=cfg['freq'])
########################################################
# import municipalities including stats and substation #
########################################################
logger.info('Importing municipalities...')
muns_query = session.query(
WnAbwMun.ags, WnAbwMun.name,
func.ST_AsText(func.ST_Transform(
WnAbwMun.geom, srid)).label('geom'), WnAbwRelSubstIdAgsId.subst_id,
WnAbwMundata.area, WnAbwMundata.gen_capacity_wind,
WnAbwMundata.gen_capacity_pv_roof_small,
WnAbwMundata.gen_capacity_pv_roof_large,
WnAbwMundata.gen_capacity_pv_ground, WnAbwMundata.gen_capacity_hydro,
WnAbwMundata.gen_capacity_bio,
WnAbwMundata.gen_capacity_sewage_landfill_gas,
WnAbwMundata.gen_capacity_conventional_large,
WnAbwMundata.gen_capacity_conventional_small,
WnAbwMundata.gen_count_wind, WnAbwMundata.gen_count_pv_roof_small,
WnAbwMundata.gen_count_pv_roof_large, WnAbwMundata.gen_count_pv_ground,
WnAbwMundata.gen_count_hydro, WnAbwMundata.gen_count_bio,
WnAbwMundata.gen_count_sewage_landfill_gas,
WnAbwMundata.gen_count_conventional_large,
WnAbwMundata.gen_count_conventional_small,
WnAbwMundata.dem_el_energy_hh, WnAbwMundata.dem_el_energy_rca,
WnAbwMundata.dem_el_energy_ind, WnAbwMundata.dem_th_energy_hh,
WnAbwMundata.dem_th_energy_rca).join(WnAbwMundata).join(
WnAbwRelSubstIdAgsId).order_by(WnAbwMun.ags)
muns = pd.read_sql_query(muns_query.statement,
session.bind,
index_col='ags')
# got one dataset per subst -> muns are duplicated -> create subst list
muns['subst_id'] = muns.groupby(muns.index)['subst_id'].apply(list)
# delete duplicates brought by groupby (drop_duplicates do not work here)
muns = muns[~muns.duplicated(subset='gen')]
# convert geom to shapely
data['muns'] = convert_df_wkt_to_shapely(df=muns, cols=['geom'])
###############################################
# import population and employment timeseries #
###############################################
logger.info('Importing demography...')
demography_query = session.query(WnAbwDemography.ags_id.label('ags'),
WnAbwDemography.year,
WnAbwDemography.population,
WnAbwDemography.employees).order_by(
WnAbwDemography.year,
WnAbwDemography.ags_id)
data['demography'] = pd.read_sql_query(demography_query.statement,
session.bind,
index_col=['year', 'ags'])
############################
# import demand timeseries #
############################
logger.info('Importing demand timeseries...')
demandts_query = session.query(
WnAbwDemandTs.ags_id.label('ags'), WnAbwDemandTs.el_hh,
WnAbwDemandTs.el_rca, WnAbwDemandTs.el_ind, WnAbwDemandTs.th_hh_efh,
WnAbwDemandTs.th_hh_mfh,
WnAbwDemandTs.th_rca).order_by(WnAbwDemandTs.timestamp)
data['demand_ts_init'] = reformat_timeseries(
pd.read_sql_query(demandts_query.statement, session.bind))
data['demand_ts_init'].index = datetime_index_full_year
############################
# import feedin timeseries #
############################
logger.info('Importing feedin timeseries...')
feedints_query = session.query(
WnAbwFeedinTs.ags_id.label('ags'), WnAbwFeedinTs.wind_sq,
WnAbwFeedinTs.wind_fs, WnAbwFeedinTs.pv_ground, WnAbwFeedinTs.pv_roof,
WnAbwFeedinTs.hydro, WnAbwFeedinTs.bio, WnAbwFeedinTs.conventional,
WnAbwFeedinTs.solar_heat).order_by(WnAbwFeedinTs.timestamp)
data['feedin_ts_init'] = reformat_timeseries(
pd.read_sql_query(feedints_query.statement, session.bind))
data['feedin_ts_init'].index = datetime_index_full_year
#########################
# import DSM timeseries #
#########################
logger.info('Importing DSM timeseries...')
dsmts_query = session.query(
WnAbwDsmTs.ags_id.label('ags'),
WnAbwDsmTs.Lastprofil,
WnAbwDsmTs.Flex_Minus,
WnAbwDsmTs.Flex_Minus_Max,
WnAbwDsmTs.Flex_Plus,
WnAbwDsmTs.Flex_Plus_Max,
).order_by(WnAbwDsmTs.timestamp)
data['dsm_ts'] = reformat_timeseries(
pd.read_sql_query(dsmts_query.statement, session.bind))
data['dsm_ts'].index = datetime_index_full_year
#################################
# import temperature timeseries #
#################################
logger.info('Importing temperature timeseries...')
tempts_query = session.query(
WnAbwTempTs.ags_id.label('ags'), WnAbwTempTs.air_temp,
WnAbwTempTs.soil_temp).order_by(WnAbwTempTs.timestamp)
data['temp_ts_init'] = reformat_timeseries(
pd.read_sql_query(tempts_query.statement, session.bind))
data['temp_ts_init'].index = datetime_index_full_year
#####################################################################
# import HV grid (buses, lines, trafos, substations+grid districts) #
#####################################################################
logger.info('Importing HV grid...')
gridhvbus_query = session.query(
WnAbwGridHvBus.bus_id,
WnAbwGridHvBus.v_nom,
WnAbwGridHvBus.hvmv_subst_id,
WnAbwGridHvBus.region_bus,
WnAbwGridHvBus.ags_id.label('ags'),
func.ST_AsText(func.ST_Transform(WnAbwGridHvBus.geom,
srid)).label('geom'),
).order_by(WnAbwGridHvBus.bus_id)
data['buses'] = pd.read_sql_query(gridhvbus_query.statement,
session.bind,
index_col='bus_id')
data['buses'] = convert_df_wkt_to_shapely(df=data['buses'], cols=['geom'])
gridhvlines_query = session.query(
WnAbwGridHvLine.line_id,
WnAbwGridHvLine.bus0,
WnAbwGridHvLine.bus1,
WnAbwGridHvLine.x,
WnAbwGridHvLine.r,
WnAbwGridHvLine.g,
WnAbwGridHvLine.b,
WnAbwGridHvLine.s_nom,
WnAbwGridHvLine.length,
WnAbwGridHvLine.cables,
func.ST_AsText(func.ST_Transform(WnAbwGridHvLine.geom,
srid)).label('geom'),
).order_by(WnAbwGridHvLine.line_id)
data['lines'] = pd.read_sql_query(gridhvlines_query.statement,
session.bind)
data['lines'] = convert_df_wkt_to_shapely(df=data['lines'], cols=['geom'])
gridhvtrafo_query = session.query(
WnAbwGridHvTransformer.trafo_id,
WnAbwGridHvTransformer.bus0,
WnAbwGridHvTransformer.bus1,
WnAbwGridHvTransformer.x,
WnAbwGridHvTransformer.r,
WnAbwGridHvTransformer.g,
WnAbwGridHvTransformer.b,
WnAbwGridHvTransformer.s_nom,
WnAbwGridHvTransformer.tap_ratio,
WnAbwGridHvTransformer.phase_shift,
WnAbwGridHvTransformer.ags_id.label('ags'),
func.ST_AsText(
func.ST_Transform(WnAbwGridHvTransformer.geom_point,
srid)).label('geom'),
).order_by(WnAbwGridHvTransformer.trafo_id)
data['trafos'] = pd.read_sql_query(gridhvtrafo_query.statement,
session.bind,
index_col='trafo_id')
data['trafos'] = convert_df_wkt_to_shapely(df=data['trafos'],
cols=['geom'])
gridhvmvsubst_query = session.query(
WnAbwGridHvmvSubstation.subst_id,
WnAbwGridHvmvSubstation.otg_id.label('bus_id'),
WnAbwGridHvmvSubstation.voltage,
func.ST_AsText(func.ST_Transform(
WnAbwGridHvmvSubstation.geom, srid)).label('geom'),
func.ST_AsText(func.ST_Transform(
WnAbwGridMvGriddistrict.geom, srid)).label('geom_mvgd'),
).join(
WnAbwGridMvGriddistrict,
WnAbwGridHvmvSubstation.subst_id == WnAbwGridMvGriddistrict.subst_id).\
order_by(WnAbwGridHvmvSubstation.subst_id)
data['subst'] = pd.read_sql_query(gridhvmvsubst_query.statement,
session.bind,
index_col='subst_id')
data['subst'] = convert_df_wkt_to_shapely(df=data['subst'],
cols=['geom', 'geom_mvgd'])
#####################
# import generators #
#####################
logger.info('Importing generators...')
generators_query = session.query(
WnAbwPowerplant.id, WnAbwPowerplant.ags_id, WnAbwPowerplant.capacity,
WnAbwPowerplant.chp, WnAbwPowerplant.com_month,
WnAbwPowerplant.com_year, WnAbwPowerplant.energy_source_level_1,
WnAbwPowerplant.energy_source_level_2,
WnAbwPowerplant.energy_source_level_3, WnAbwPowerplant.technology,
WnAbwPowerplant.thermal_capacity, WnAbwPowerplant.capacity_in,
func.ST_AsText(func.ST_Transform(WnAbwPowerplant.geometry,
srid)).label('geom'))
data['generators'] = pd.read_sql_query(generators_query.statement,
session.bind,
index_col='id')
data['generators'] = convert_df_wkt_to_shapely(df=data['generators'],
cols=['geom'])
#####################################
# import heating structure (hh+rca) #
#####################################
logger.info('Importing heating structure...')
heating_structure_query = session.query(
WnAbwHeatingStructure.ags_id, WnAbwHeatingStructure.year,
WnAbwHeatingStructure.energy_source,
WnAbwHeatingStructure.tech_share_hh_efh.label('hh_efh'),
WnAbwHeatingStructure.tech_share_hh_mfh.label('hh_mfh'),
WnAbwHeatingStructure.tech_share_rca.label('rca'))
data['heating_structure'] = pd.read_sql_query(
heating_structure_query.statement,
session.bind,
index_col=['ags_id', 'energy_source', 'year'])
#######################################################
# import technical assumptions (costs, eff, emissions #
#######################################################
# convert units to MW/MWh (cf. table "Kosten_Emissionen_Wirkungsgrade")
logger.info('Importing technical assumptions...')
tech_assumptions_query = session.query(
WnAbwTechAssumptions.technology, WnAbwTechAssumptions.year,
WnAbwTechAssumptions.technology_name,
(WnAbwTechAssumptions.capex * 1000).label('capex'),
(WnAbwTechAssumptions.opex_fix * 1000).label('opex_fix'),
(WnAbwTechAssumptions.opex_var * 1000).label('opex_var'),
WnAbwTechAssumptions.lifespan, WnAbwTechAssumptions.emissions_fix,
WnAbwTechAssumptions.emissions_var,
(WnAbwTechAssumptions.sys_eff / 100).label('sys_eff'),
(WnAbwTechAssumptions.wacc / 100).label('wacc'))
data['tech_assumptions'] = pd.read_sql_query(
tech_assumptions_query.statement,
session.bind,
index_col=['technology', 'year'])
#####################################
# import WEC and PV potential areas #
#####################################
logger.info('Importing RE potential areas...')
pot_areas_pv_query = session.query(
WnAbwPotentialAreasPv.ags_id, WnAbwPotentialAreasPv.scenario,
WnAbwPotentialAreasPv.area_ha,
func.ST_AsText(func.ST_Transform(WnAbwPotentialAreasPv.geom,
srid)).label('geom'))
data['pot_areas_pv'] = pd.read_sql_query(pot_areas_pv_query.statement,
session.bind,
index_col=['ags_id', 'scenario'])
pot_areas_pv_roof_query = session.query(
WnAbwPotentialAreasPvRoof.ags_id,
WnAbwPotentialAreasPvRoof.area_resid_ha,
WnAbwPotentialAreasPvRoof.area_ind_ha)
data['pot_areas_pv_roof'] = pd.read_sql_query(
pot_areas_pv_roof_query.statement, session.bind, index_col=['ags_id'])
pot_areas_wec_query = session.query(
WnAbwPotentialAreasWec.ags_id, WnAbwPotentialAreasWec.scenario,
WnAbwPotentialAreasWec.area_ha,
func.ST_AsText(func.ST_Transform(WnAbwPotentialAreasWec.geom,
srid)).label('geom'))
data['pot_areas_wec'] = pd.read_sql_query(pot_areas_wec_query.statement,
session.bind,
index_col=['ags_id', 'scenario'])
return data
def line_elevation(geometry, format_out, dataset):
"""
Performs PostGIS query to enrich a line geometry.
:param geometry: Input 2D line to be enriched with elevation
:type geometry: Shapely geometry
:param format_out: Specifies output format. One of ['geojson', 'polyline',
'encodedpolyline']
:type format_out: string
:param dataset: Elevation dataset to use for querying
:type dataset: string
:raises InvalidUsage: internal HTTP 500 error with more detailed description.
:returns: 3D line as GeoJSON or WKT
:rtype: string
"""
Model = _getModel(dataset)
input_crs = _get_crs(dataset)
if geometry.geom_type == 'LineString':
query_points2d = db.session\
.query(ST_Transform(func.ST_SetSRID(ST_DumpPoints(geometry.wkt).geom, 4326), input_crs) \
.label('geom')) \
.subquery().alias('points2d')
query_getelev = db.session \
.query(ST_Transform(query_points2d.c.geom, 4326).label('geom'),
ST_Value(Model.rast, query_points2d.c.geom, False).label('z')) \
.filter(ST_Intersects(Model.rast, query_points2d.c.geom)) \
.subquery().alias('getelevation')
query_points3d = db.session \
.query(func.ST_MakePoint(ST_X(query_getelev.c.geom),
ST_Y(query_getelev.c.geom),
query_getelev.c.z.cast(Integer)
).label('geom')) \
.subquery().alias('points3d')
if format_out == 'geojson':
# Return GeoJSON directly in PostGIS
query_final = db.session \
.query(func.ST_AsGeoJson(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision))))
else:
# Else return the WKT of the geometry
query_final = db.session \
.query(func.ST_AsText(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision))))
else:
raise InvalidUsage(
400, 4002,
"Needs to be a LineString, not a {}!".format(geometry.geom_type))
try:
result = query_final.one()
except NoResultFound:
raise InvalidUsage(
404, 4002,
f'{tuple(geometry.coords)[0]} has no elevation value in {dataset}')
return result[0]
