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]