def test_ST_Buffer_Mixed_SRID(self): self._create_one_lake() with pytest.raises(InternalError): session.query(Lake).filter( func.ST_Within('POINT(0 0)', Lake.geom.ST_Buffer(2))).one()
def test_ST_Buffer(self): lake_id = self._create_one_lake() s = select([func.ST_Buffer(Lake.__table__.c.geom, 2, type_=geometry_type)]) r1 = session.execute(s).scalar() assert isinstance(r1, WKBElement) lake = session.query(Lake).get(lake_id) r2 = session.execute(lake.geom.ST_Buffer(2, type_=geometry_type)).scalar() assert isinstance(r2, WKBElement) r3 = session.query(Lake.geom.ST_Buffer(2, type_=geometry_type)).scalar() assert isinstance(r3, WKBElement) assert r1.data == r2.data == r3.data r4 = session.query(Lake).filter( func.ST_Within(WKTElement('POINT(0 0)', srid=4326), Lake.geom.ST_Buffer(2, type_=geometry_type))).one() assert isinstance(r4, Lake) assert r4.id == lake_id
def get(self): parser = reqparse.RequestParser() parser.add_argument('machinetype') parser.add_argument('exceptiontype') parser.add_argument('lastmodified_time_range') parser.add_argument('lastoccur_region') args = parser.parse_args() machinetype = args['machinetype'] exceptiontype = args['exceptiontype'] lastmodified_time_range = args['lastmodified_time_range'] lastoccur_region = args['lastoccur_region'] filters = LastappearedModel.query if machinetype is not None: filters = LastappearedModel.query.filter(LastappearedModel.machine_type.has(MachineTypeModel.machinetype.in_ (machinetype.split(",")))) if exceptiontype is not None: filters = filters.filter(LastappearedModel.exception_type.has(ExceptionTypeModel.exceptiontype.in_(exceptiontype.split(",")))) if lastmodified_time_range is not None: [start,end] = lastmodified_time_range.split(",") if start: filters = filters.filter(LastappearedModel.lastmodified_time >= datetime.strptime(start, "%Y-%m-%d %H:%M:%S")) if end: filters = filters.filter(LastappearedModel.lastmodified_time <= datetime.strptime(end, "%Y-%m-%d %H:%M:%S")) if lastoccur_region is not None: filters = filters.filter(func.ST_Within(LastappearedModel.gps_point,'SRID=4326;POLYGON(({}))'.format(lastoccur_region))) return [row.dictRepr() for row in filters.all()]
def update_entries(session, start, end, logger=None): """Compute takeoffs and landings.""" if logger is None: logger = app.logger logger.info("Compute takeoffs and landings.") # considered time interval should not exceed a complete day if end - start > timedelta(days=1): abort_message = "TakeoffLanding: timeinterval start='{}' and end='{}' is too big.".format( start, end) logger.warn(abort_message) return abort_message # check if we have any airport airports_query = session.query(Airport).limit(1) if not airports_query.all(): abort_message = "TakeoffLanding: Cannot calculate takeoff and landings without any airport! Please import airports first." logger.warn(abort_message) return abort_message # takeoff / landing detection is based on 3 consecutive points all below a certain altitude AGL takeoff_speed = 55 # takeoff detection: 1st point below, 2nd and 3rd above this limit landing_speed = 40 # landing detection: 1st point above, 2nd and 3rd below this limit min_takeoff_climb_rate = -5 # takeoff detection: glider should not sink too much max_landing_climb_rate = 5 # landing detection: glider should not climb too much duration = 100 # the points must not exceed this duration radius = 5000 # the points must not exceed this radius around the 2nd point max_agl = 200 # takeoff / landing must not exceed this altitude AGL # get beacons for selected time range, one per device_id and timestamp sq = (session.query(AircraftBeacon).distinct( AircraftBeacon.device_id, AircraftBeacon.timestamp).order_by( AircraftBeacon.device_id, AircraftBeacon.timestamp, AircraftBeacon.error_count).filter( AircraftBeacon.agl < max_agl).filter( between(AircraftBeacon.timestamp, start, end)).subquery()) # make a query with current, previous and next position sq2 = session.query( sq.c.device_id, func.lag(sq.c.device_id).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("device_id_prev"), func.lead(sq.c.device_id).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("device_id_next"), sq.c.timestamp, func.lag(sq.c.timestamp).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("timestamp_prev"), func.lead(sq.c.timestamp).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("timestamp_next"), sq.c.location, func.lag(sq.c.location).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("location_wkt_prev"), func.lead(sq.c.location).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("location_wkt_next"), sq.c.track, func.lag(sq.c.track).over(partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("track_prev"), func.lead( sq.c.track).over(partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("track_next"), sq.c.ground_speed, func.lag(sq.c.ground_speed).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("ground_speed_prev"), func.lead(sq.c.ground_speed).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("ground_speed_next"), sq.c.altitude, func.lag(sq.c.altitude).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("altitude_prev"), func.lead(sq.c.altitude).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("altitude_next"), sq.c.climb_rate, func.lag(sq.c.climb_rate).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("climb_rate_prev"), func.lead(sq.c.climb_rate).over( partition_by=sq.c.device_id, order_by=sq.c.timestamp).label("climb_rate_next"), ).subquery() # consider only positions with predecessor and successor and limit distance and duration between points sq3 = (session.query(sq2).filter( and_(sq2.c.device_id_prev != null(), sq2.c.device_id_next != null())).filter( and_( func.ST_DistanceSphere(sq2.c.location, sq2.c.location_wkt_prev) < radius, func.ST_DistanceSphere(sq2.c.location, sq2.c.location_wkt_next) < radius)).filter(sq2.c.timestamp_next - sq2.c.timestamp_prev < timedelta( seconds=duration)).subquery()) # find possible takeoffs and landings sq4 = ( session.query( sq3.c.timestamp, case([ ( sq3.c.ground_speed > takeoff_speed, sq3.c.location_wkt_prev ), # on takeoff we take the location from the previous fix because it is nearer to the airport (sq3.c.ground_speed <= takeoff_speed, sq3.c.location), ]).label("location"), case([ (sq3.c.ground_speed > landing_speed, sq3.c.track), (sq3.c.ground_speed <= landing_speed, sq3.c.track_prev) ]).label( "track" ), # on landing we take the track from the previous fix because gliders tend to leave the runway quickly sq3.c.ground_speed, sq3.c.altitude, case([(sq3.c.ground_speed > takeoff_speed, True), (sq3.c.ground_speed < landing_speed, False) ]).label("is_takeoff"), sq3.c.device_id, ).filter( or_( and_(sq3.c.ground_speed_prev < takeoff_speed, sq3.c.ground_speed > takeoff_speed, sq3.c.ground_speed_next > takeoff_speed, sq3.c.climb_rate > min_takeoff_climb_rate), # takeoff and_(sq3.c.ground_speed_prev > landing_speed, sq3.c.ground_speed < landing_speed, sq3.c.ground_speed_next < landing_speed, sq3.c.climb_rate < max_landing_climb_rate), # landing )).subquery()) # consider them if the are near airports ... sq5 = (session.query( sq4.c.timestamp, sq4.c.track, sq4.c.is_takeoff, sq4.c.device_id, Airport.id.label("airport_id"), func.ST_DistanceSphere( sq4.c.location, Airport.location_wkt).label("airport_distance")).filter( and_(func.ST_Within(sq4.c.location, Airport.border), between(Airport.style, 2, 5))).subquery()) # ... and take the nearest airport sq6 = (session.query(sq5.c.timestamp, sq5.c.track, sq5.c.is_takeoff, sq5.c.device_id, sq5.c.airport_id).distinct( sq5.c.timestamp, sq5.c.track, sq5.c.is_takeoff, sq5.c.device_id).order_by( sq5.c.timestamp, sq5.c.track, sq5.c.is_takeoff, sq5.c.device_id, sq5.c.airport_distance).subquery()) # consider them only if they are not already existing in db takeoff_landing_query = session.query(sq6).filter(~exists().where( and_(TakeoffLanding.timestamp == sq6.c.timestamp, TakeoffLanding.device_id == sq6.c.device_id, TakeoffLanding.airport_id == sq6.c.airport_id))) # ... and save them ins = insert(TakeoffLanding).from_select( (TakeoffLanding.timestamp, TakeoffLanding.track, TakeoffLanding.is_takeoff, TakeoffLanding.device_id, TakeoffLanding.airport_id), takeoff_landing_query) result = session.execute(ins) session.commit() insert_counter = result.rowcount finish_message = "TakeoffLandings: {} inserted".format(insert_counter) logger.info(finish_message) return finish_message