class NeighborhoodModel(db.Model):
__bind_key__ = 'nyc'
__tablename__ = 'nyc_neighborhoods'
gid = db.Column(db.Integer, primary_key=True)
boroname = db.Column(db.String(43))
name = db.Column(db.String(64))
geom = db.Column(Geometry(geometry_type='POINT', srid=26918))
class AirLineModel(db.Model):
__tablename__ = 'airline'
id = db.Column(db.Integer, primary_key=True)
airlinecode = db.Column(db.String(80), unique=True,
nullable=False) #air line number
airlinegps = db.Column(Geometry(geometry_type='LINESTRING', srid=4326),
nullable=False)
class ObjectUseAirlineModel(db.Model):
__tablename__ = 'object_use_airline'
id = db.Column(db.Integer, primary_key=True)
object_id = db.Column(db.String(50), nullable=False)
flight_task_number = db.Column(db.String(50), nullable=False)
pub_date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
airline_id = db.Column(db.Integer,
db.ForeignKey('airline.id'),
nullable=False)
class ExceptionTypeModel(db.Model):
__tablename__ = 'exceptiontype'
lastappeared_id = db.Column(db.Integer,
db.ForeignKey('lastappeared.id',
ondelete="CASCADE"),
primary_key=True)
exceptiontype = db.Column(db.String())
def dictRepr(self):
return {
"lastappeared_id": self.lastappeared_id,
"exceptiontype": self.exceptiontype
}
class CarsModel(db.Model):
__tablename__ = 'cars'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String())
model = db.Column(db.String())
doors = db.Column(db.Integer())
def __init__(self, name, model, doors):
self.name = name
self.model = model
self.doors = doors
def __repr__(self):
return f"<Car {self.name}>"
class TrajectoryMixin:
@declared_attr
def lastappeared_id(cls):
return db.Column(db.Integer,
db.ForeignKey('lastappeared.id', ondelete="CASCADE"),
primary_key=True)
gps_line = db.Column(Geometry(geometry_type='LINESTRINGM', srid=4326))
def gps_points(self):
gps_points = []
for i in range(1, db.session.scalar(self.gps_line.ST_NPoints()) + 1):
gps_point = {}
gps_point["occurtime"] = datetime.fromtimestamp(
db.session.scalar(self.gps_line.ST_PointN(i).ST_M())).strftime(
"%Y-%m-%d %H:%M:%S")
gps_point["long"] = db.session.scalar(
self.gps_line.ST_PointN(i).ST_X())
gps_point["lat"] = db.session.scalar(
self.gps_line.ST_PointN(i).ST_Y())
gps_points.append(gps_point)
return gps_points
def dictRepr(self, **kwargs):
d = {"id": self.lastappeared_id, "gps_points": self.gps_points()}
if "similar" in kwargs:
d["similar"] = kwargs["similar"]
return d
class ExceptionInfoModel(db.Model):
__tablename__ = 'exceptioninfo'
id = db.Column(db.Integer, primary_key=True)
lastappeared_id = db.Column(db.String(50))
exception_type = db.Column(db.String(255), nullable=False)
start_time = db.Column(db.DateTime, nullable=False)
end_time = db.Column(db.DateTime, nullable=False)
reason = db.Column(db.String(255), nullable=False)
lastmodified_time = db.Column(db.DateTime, nullable=False)
def update(self, info):
self.end_time = info["end_time"]
self.reason = info["reason"]
def dictRepr(self):
info = {
"id":
self.id,
"lastappeared_id ":
self.lastappeared_id,
"start_time":
self.start_time.strftime("%Y-%m-%d %H:%M:%S"),
"end_time":
self.end_time.strftime("%Y-%m-%d %H:%M:%S"),
"exception_type":
self.exception_type,
"reason":
self.reason,
"lastmodified_time":
self.lastmodified_time.strftime("%Y-%m-%d %H:%M:%S")
}
return info
class ImportantRegion(db.Model):
__tablename__ = 'importantregion'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), unique=True, nullable=False)
geom = db.Column(Geometry(geometry_type='POLYGON', srid=4326),
nullable=False)
def dictRepr(self):
p = json.loads(db.session.scalar(
self.geom.ST_AsGeoJSON()))["coordinates"]
return {
"id": self.id,
"name": self.name,
"gps_points": [{
'long': s[0],
'lat': s[1]
} for r in p for s in r]
}
class TrajectoryMixinWithLastmodifiedDateTime(TrajectoryMixin):
lastmodified_datetime = db.Column(db.DateTime,
nullable=False,
default=datetime.now)
def dictRepr(self, **kwargs):
d = super().dictRepr(**kwargs)
d["lastmodified_datetime"] = self.lastmodified_datetime.strftime(
"%Y-%m-%d %H:%M:%S")
return d
class ObjectTrajactoryAfterSegmentModel(
TrajectoryMixinWithLastmodifiedDateTime, db.Model):
__tablename__ = 'objecttrajactoryaftersegments'
segment_id = db.Column(db.Integer, nullable=False, primary_key=True)
max_turn_radius = db.Column(db.Float, nullable=False) #最大转弯半径阈值
real_turn_radius = db.Column(db.Float, nullable=False) # 实际转弯半径
lastmodified_datetime = db.Column(db.DateTime,
nullable=False,
default=datetime.now) #最近分段时间
def dictRepr(self, **kwargs):
d = super().dictRepr(**kwargs)
d["segment_id"] = self.segment_id
d["max_turn_radius"] = self.max_turn_radius
d["real_turn_radius"] = self.real_turn_radius
return d
@staticmethod
def trajectorysegment(id, max_turn_radius=10.0):
"""
技术需求：按用户设定的转弯半径阈值，将轨迹分段。
首先从该表objecttrajactoryaftersegments获取该id对应的已经处理好的所有轨迹段
如果没有，则从表objecttrajactory获取该id的对应的原始轨迹，
然后使用分段算法对原始轨迹处理并保存到表objecttrajactoryaftersegments，并将处理好的轨迹分段返回给用户
如果有，需要比较最近更新时间和转弯半径阈值，如果有变化，也需要重新计算
要求：仿真一个至少有三个转弯的轨迹，通过该算法分成四段
"""
p = ObjectTrajactoryAfterSegmentModel.query.filter_by(
lastappeared_id=id).all() ## 这应该返回的是数组
if p is None:
p = ObjectTrajactoryModel.query.get(id)
# please do something here
# segement the trajectory to several parts,save these parts to objecttrajactoryaftersegments ,return them
return p
class LastappearedModel(db.Model):
__tablename__ = 'lastappeared'
def defaultDate():
return datetime.now().date()
def defaultTime():
return datetime.now().time()
id = db.Column(db.Integer, primary_key=True)
object_id = db.Column(db.String(50), nullable=False)
lastmodified_date = db.Column(db.Date, nullable=False, default=defaultDate)
lastmodified_time = db.Column(db.Time, nullable=False, default=defaultTime)
gps_point = db.Column(Geometry(geometry_type='POINTM', srid=4326),
nullable=False)
exception_type = db.relationship('ExceptionTypeModel',
uselist=False,
backref='lastappeared',
lazy=True,
cascade="all, delete",
passive_deletes=True)
machine_type = db.relationship('MachineTypeModel',
uselist=False,
backref='lastappeared',
lazy=True,
cascade="all, delete",
passive_deletes=True)
object_trajactory = db.relationship('ObjectTrajactoryModel',
uselist=False,
backref='lastappeared',
lazy=True,
cascade="all, delete",
passive_deletes=True)
__table_args__ = (db.UniqueConstraint('object_id', 'lastmodified_date'), )
def __init__(self, **kwargs):
if "lastmodified_time" in kwargs.keys() and type(
kwargs["lastmodified_time"]) is str:
lastmodified_date_time_str = kwargs["lastmodified_time"]
kwargs["lastmodified_date_time"] = datetime.strptime(
lastmodified_date_time_str, "%Y-%m-%d %H:%M:%S")
else:
kwargs["lastmodified_date_time"] = datetime.now()
if "lastmodified_date" not in kwargs.keys() or type(
kwargs["lastmodified_date"]) is str:
kwargs["lastmodified_date"] = kwargs[
"lastmodified_date_time"].date()
if "lastmodified_time" not in kwargs.keys() or type(
kwargs["lastmodified_time"]) is str:
kwargs["lastmodified_time"] = kwargs[
"lastmodified_date_time"].time()
if "lat" in kwargs.keys() and "long" in kwargs.keys(
) and "gps_point" not in kwargs.keys():
kwargs["gps_point"] = 'SRID=4326;POINTM({} {} {})'.format(
kwargs["long"], kwargs["lat"],
datetime.timestamp(kwargs["lastmodified_date_time"]))
del kwargs["lat"]
del kwargs["long"]
del kwargs["lastmodified_date_time"]
super(LastappearedModel, self).__init__(**kwargs)
def update(self, body):
if "lastmodified_time" in body.keys() and type(
body["lastmodified_time"]) is str:
lastmodified_date_time_str = body["lastmodified_time"]
lastmodified_date_time = datetime.strptime(
lastmodified_date_time_str, "%Y-%m-%d %H:%M:%S")
else:
lastmodified_date_time = datetime.now()
self.lastmodified_date = lastmodified_date_time.date()
self.lastmodified_time = lastmodified_date_time.time()
if "lat" in body.keys() and "long" in body.keys():
self.gps_point = 'SRID=4326;POINTM({} {} {})'.format(
body["long"], body["lat"],
datetime.timestamp(lastmodified_date_time))
def lat(self):
return db.session.scalar(self.gps_point.ST_Y())
def long(self):
return db.session.scalar(self.gps_point.ST_X())
def M(self):
return db.session.scalar(self.gps_point.ST_M())
def dictRepr(self):
info = {
"id": self.id,
"object_id": self.object_id,
"lastmodified_date": self.lastmodified_date.strftime("%Y-%m-%d"),
"lastmodified_time": self.lastmodified_time.strftime("%H:%M:%S"),
"long": self.long(),
"lat": self.lat(),
"M": self.M()
}
if self.exception_type is not None:
info["exception_type"] = self.exception_type.exceptiontype
if self.machine_type is not None:
info["machine_type"] = self.machine_type.machinetype
return info
class Geometries3Model(db.Model):
__bind_key__ = 'nyc'
__tablename__ = 'geometries3'
name = db.Column(db.String(64), primary_key=True)
geom = db.Column(Geometry(geometry_type='LINESTRINGM', srid=4326))
def lastappeared_id(cls):
return db.Column(db.Integer,
db.ForeignKey('lastappeared.id', ondelete="CASCADE"),
primary_key=True)
