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
