class TrajectoryTime(object):
LINE_INDEX = 0
MMSI_INDEX = 1
MARK_INDEX = 2
DEADWEIGHT_INDEX = 8
START_TIME_INDEX = 9
ARRIVE_TIME_INDEX = 10
SOURCE_INDEX = 11
LOAD_STATE_INDEX = 13
INPUT_OR_OUTPUT_INDEX = 14
def __init__(self, db_name, mmsi_db_name):
self.source_db = CommonDB(db_name)
self.mmsi_db_name = CommonDB(mmsi_db_name)
self.ship_level_info = {}
def start_fetch_data_transaction(self, sql):
self.source_db.run_sql(sql)
return self.source_db.db_cursor
@classmethod
def judge_same_line(cls, before_row, after_row):
return before_row[TrajectoryTime.LINE_INDEX] == after_row[
TrajectoryTime.LINE_INDEX]
@classmethod
def judge_same_ship(cls, before_row, after_row):
return before_row[TrajectoryTime.MMSI_INDEX] == after_row[TrajectoryTime.MMSI_INDEX] and \
before_row[TrajectoryTime.MARK_INDEX] == after_row[TrajectoryTime.MARK_INDEX]
def export_ship_level_info_to_csv(self, file_name, header):
with open(file_name, "wb") as csv_file:
csv_writer = csv.writer(csv_file)
csv_writer.writerow(header)
for key, values in self.ship_level_info.items():
value, count = values
mmsi, deadweight = key.split("-")
ship_static_info = self.fetch_ship_static_info(mmsi)
csv_writer.writerow([
mmsi,
value * 1.0 / count,
deadweight,
] + ship_static_info)
def update_ship_level_info(self, key, append_value):
if key in self.ship_level_info:
value, count = self.ship_level_info[key]
else:
value, count = 0, 0
value += append_value
count += 1
self.ship_level_info[key] = (value, count)
def fetch_ship_static_info(self, mmsi):
self.mmsi_db_name.run_sql(ConstSQL.FETCH_SHIP_STATIC_INFO.format(mmsi))
return list(self.mmsi_db_name.db_cursor.next())
def fetch_data(self, source_name, sql):
source_db = CommonDB(source_name)
source_db.run_sql(sql)
for row in source_db.db_cursor:
self.oil_graph.add_nodes_from([row[0], row[1]])
self.oil_graph.add_weighted_edges_from([(row[0], row[1],
int(row[2]))])
self.total_oil += int(row[2])
def get_use_time(db_name, csv_name):
db_file = CommonDB(db_name)
db_file.run_sql("SELECT * FROM china_trajectory_cn WHERE load_state = 1 AND input_or_output = 'Input' AND "
"vessel_type_sub = 'Crude Oil Tanker' ORDER BY arrive_Time;")
with open(csv_name, 'wb') as csv_file:
csv_writer = csv.writer(csv_file)
for row in db_file.db_cursor:
use_time = Utils.convert_str_time_to_utc(str(row[10])) - Utils.convert_str_time_to_utc(str(row[9]))
csv_writer.writerow(list(row) + [use_time])
def get_ship_count(source_path, source_table, target_db, ship_table, ship_rol_list, ):
db = CommonDB(target_db)
sql = "INSERT INTO {} SELECT DISTINCT MMSI, Vessel_type_sub FROM {}".format(ship_table, source_table)
db.import_data_from_path(source_path, ship_table, ship_rol_list, sql, )
db.run_sql("SELECT COUNT(DISTINCT MMSI) FROM {}".format(ship_table))
original_ship = db.db_cursor.next()[0]
print("original_ship:" + str(original_ship))
db.run_sql("SELECT COUNT(DISTINCT MMSI) FROM {} WHERE Vessel_type = 'Crude Oil Tanker'".format(
ship_table))
tanker_ship = db.db_cursor.next()[0]
print("tanker_ship:" + str(tanker_ship))
def get_all_ship_country(source_db, mmsi_db, csv_name):
result_csv = open(csv_name, 'wb')
csv_writer = csv.writer(result_csv)
csv_writer.writerow(['MMSI', 'country'])
source_db_file = CommonDB(source_db)
mmsi_db_file = CommonDB(mmsi_db)
mmsi_db_file.run_sql("""
SELECT DISTINCT mmsi
FROM china_trajectory_cn
WHERE load_state = 1 AND
input_or_output = 'Input' AND
vessel_type_sub = 'Crude Oil Tanker'
ORDER BY mmsi;
""")
for row in mmsi_db_file.db_cursor:
source_db_file.run_sql("SELECT mmsi, flag_country FROM OilTanker WHERE mmsi = {} LIMIT 1;".format(row[0]))
csv_writer.writerow(list(next(source_db_file.db_cursor)))
class DeadweightDB:
def __init__(self, db_name):
self.deadweight_db = CommonDB(db_name)
self.ships_deadweight = None
def init_ships_deadweight(self, table_name):
self.ships_deadweight = {}
self.deadweight_db.run_sql(
"SELECT mmsi, deadweight FROM {}".format(table_name))
for row in self.deadweight_db.db_cursor:
self.ships_deadweight[str(row[0])] = row[1]
return self.ships_deadweight
def get_deadweight_by_mmsi(self, mmsi):
if mmsi not in self.ships_deadweight:
print(mmsi)
return ""
return self.ships_deadweight[mmsi]
def plot_deadweight(param):
db_file = CommonDB(param)
db_file.run_sql("""
SELECT deadweight/10000 AS weight, count()
FROM china_trajectory_cn
WHERE load_state = 1 AND
input_or_output = 'Input' AND
vessel_type_sub = 'Crude Oil Tanker'
GROUP BY weight
ORDER BY weight;
""")
x = []
y = []
for row in db_file.db_cursor:
x.append(row[0])
y.append(row[1])
fig = plt.figure()
# plt.xlim(0, 400000)
ax1 = fig.add_subplot(111)
ax1.bar(x, y)
db_file.run_sql("""
SELECT deadweight/10000 AS weight, count()
FROM china_trajectory_cn
WHERE (arrive_time BETWEEN 20141100000000 AND 20150000000000 OR
arrive_time BETWEEN 20150500000000 AND 20150700000000) AND
load_state = 1 AND
input_or_output = 'Input' AND
vessel_type_sub = 'Crude Oil Tanker'
GROUP BY weight
ORDER BY weight;
""")
x = []
y = []
for row in db_file.db_cursor:
x.append(row[0])
y.append(row[1] * 20)
ax1.bar(x, y, color='red')
plt.show()
def plot_length_width(db_name):
db_file = CommonDB(db_name)
db_file.run_sql("""
SELECT length, width
FROM china_trajectory_cn
WHERE load_state = 1 AND
input_or_output = 'Input' AND
vessel_type_sub = 'Crude Oil Tanker' AND
length < 400 AND
width < 80
ORDER BY arrive_Time;
""")
x = []
y = []
for row in db_file.db_cursor:
x.append(row[0])
y.append(row[1])
fig = plt.figure()
ax1 = fig.add_subplot(111)
ax1.scatter(x, y, color='blue')
# plt.show()
db_file.run_sql("""
SELECT length, width
FROM china_trajectory_cn
WHERE (arrive_time BETWEEN 20141100000000 AND 20150000000000 OR
arrive_time BETWEEN 20150500000000 AND 20150700000000) AND
load_state = 1 AND
input_or_output = 'Input' AND
vessel_type_sub = 'Crude Oil Tanker'
ORDER BY arrive_Time;
""")
x = []
y = []
for row in db_file.db_cursor:
x.append(row[0])
y.append(row[1])
# fig = plt.figure()
ax1.scatter(x, y, color='red')
plt.show()
class DraftDB(object):
def __init__(self, db_name):
self.draft_db = CommonDB(db_name)
self.draft = None
def import_data(self, source_db, source_table, target_table, rol_list):
self.draft_db.create_new_table(target_table, rol_list)
sql = "INSERT INTO {} SELECT mmsi, mark, draft, count() FROM {} GROUP BY mmsi, mark, draft" \
.format(target_table, source_table)
self.draft_db.import_data(source_db, sql)
def insert_data(self, data_list, target_table):
insert_cursor = self.draft_db.db_file.cursor()
for data in data_list:
insert_cursor.execute(
"INSERT INTO {} VALUES(?,?,?,?,?)".format(target_table),
(data.mmsi, data.mark, data.draft, data.count,
data.load_state))
# self.draft_db.db_file.commit()
return
# def export_to_csv(self, data_list, ):
def single_ship_draft_state_identify(self, data_list, target_table):
kmeans_service = KmeansService(data_list, 'draft', 'count',
'load_state')
if kmeans_service.k_means_calculate() == Const.SUCCESS:
# 输出
self.insert_data(data_list, target_table)
pass
def ships_draft_state_identify(self, source_table, target_table, rol_list):
self.draft_db.create_new_table(target_table, rol_list)
self.draft_db.run_sql(
"SELECT * FROM {} ORDER BY mmsi, mark, draft".format(source_table))
data_list = []
for row in self.draft_db.db_cursor:
if len(data_list) == 0:
data_list.append(Draft(row[0], row[1], row[2], row[3]))
elif data_list[0].mmsi == row[0] and data_list[0].mark == row[1]:
data_list.append(Draft(row[0], row[1], row[2], row[3]))
else:
self.single_ship_draft_state_identify(data_list, target_table)
data_list = [Draft(row[0], row[1], row[2], row[3])]
# 最后一个的判断
self.single_ship_draft_state_identify(data_list, target_table)
self.draft_db.db_file.commit()
return
def fetch_draft_state(self):
draft_dict = DraftState(self.draft.mmsi, self.draft.mark,
float(self.draft.draft), self.draft.load_state)
for row in self.draft_db.db_cursor:
if row[0] != self.draft.mmsi or row[1] != self.draft.mark:
self.draft = Draft(row[0], row[1], float(row[2]), row[3],
row[4])
return draft_dict
else:
draft_dict.add_draft_state(float(row[2]), row[4])
self.draft = None
return draft_dict
def has_next_draft_state(self):
return self.draft is not None
def start_fetch_transaction(self, source_table):
self.draft_db.run_sql(
"SELECT * FROM {} ORDER BY mmsi, mark".format(source_table))
row = self.draft_db.db_cursor.next()
self.draft = Draft(row[0], row[1], float(row[2]), row[3], row[4])
return DraftState(-1, -1, -1, -1)
def close(self):
self.draft_db.close_db()
class SankeyFigure(object):
SOURCE_COUNTRY_INDEX = 0
TARGET_COUNTRY_INDEX = 1
VALUE_INDEX = 2
INPUT_OR_OUTPUT_INDEX = 3
# LOAD_STATE_INDEX = 1
def __init__(self, db_name, top, threshold=0):
self.data_source = CommonDB(db_name)
self.source_nodes = {}
self.target_nodes = {}
self.middle_nodes = {}
self.format_nodes = []
self.links = []
self.nodes_color = {}
self.color_index = 0
self.top = top
self.threshold = threshold
def show_import_and_export_figure(self,
sql,
figure_name,
title,
clean_data=True):
self.init_nodes_and_links(sql, clean_data)
self.show_sankey_figure(figure_name, title)
def init_nodes_and_links(self, sql, clean_data=True):
if clean_data or self.has_datas():
self.clean_data()
self.create_datas(sql)
def has_datas(self):
return self.format_nodes != [] and self.links != []
def create_datas(self, sql):
self.start_transaction(sql)
for line in self.data_source.db_cursor:
self.append_nodes_links_from_line(line)
self.convert_all_nodes_to_format()
def start_transaction(self, sql):
self.data_source.run_sql(sql)
def append_nodes_links_from_line(self, line):
source_port, target_port, value, input_or_output = self.parse_line(
line)
if value != 0:
self.append_nodes(source_port, target_port, value, input_or_output)
self.append_links(source_port, target_port, value)
# !!! 图表设置部分
def show_sankey_figure(self, figure_name, title):
sankey = Sankey(init_opts=opts.InitOpts(width='600px', height='600px'))
sankey.add(
"",
self.format_nodes,
self.links,
levels=[
opts.SankeyLevelsOpts(
depth=0,
linestyle_opts=opts.LineStyleOpts(color="source",
curve=0.5,
opacity=0.6),
itemstyle_opts=opts.ItemStyleOpts(border_width=0),
),
opts.SankeyLevelsOpts(
depth=1,
itemstyle_opts=opts.ItemStyleOpts(border_width=0),
linestyle_opts=opts.LineStyleOpts(color="target",
curve=0.5,
opacity=0.6),
),
opts.SankeyLevelsOpts(
depth=2,
itemstyle_opts=opts.ItemStyleOpts(border_width=0)),
],
pos_right="13%",
node_gap=1,
label_opts=opts.LabelOpts(position="right"),
)
sankey.set_global_opts(
title_opts=opts.TitleOpts(title=title, pos_left='center'))
sankey.render("figure_html\{}.html".format(figure_name))
def convert_all_nodes_to_format(self):
self.format_nodes = []
self.convert_nodes_to_format(self.source_nodes)
self.convert_nodes_to_format(self.middle_nodes)
self.convert_nodes_to_format(self.target_nodes)
def clean_data(self):
self.source_nodes = {}
self.target_nodes = {}
self.middle_nodes = {}
self.format_nodes = []
self.links = []
self.nodes_color = {}
self.color_index = 0
@staticmethod
def parse_line(line):
source_port = line[SankeyFigure.SOURCE_COUNTRY_INDEX]
target_port = line[SankeyFigure.TARGET_COUNTRY_INDEX]
input_or_output = line[SankeyFigure.INPUT_OR_OUTPUT_INDEX]
value = line[SankeyFigure.VALUE_INDEX]
if input_or_output == 'Input':
source_port = "source-" + source_port
elif input_or_output == 'Output':
target_port = "target-" + target_port
if value is not None:
value = int(value)
else:
value = 0
return source_port, target_port, value, input_or_output
def update_nodes(self, nodes, node, value):
if node in nodes:
origin_value = nodes[node]
value += origin_value
nodes[node] = value
def append_format_nodes_judge_by_value(self, node):
node_name, value = node
color = self.get_node_color(node_name)
format_node = {
"name": node_name,
"itemStyle": {
"color": color
},
}
if value <= self.threshold:
format_node["label"] = {"show": False}
self.format_nodes.append(format_node)
def get_node_color(self, node_name):
country_name = node_name.split("-")[-1]
if country_name not in self.nodes_color:
self.nodes_color[country_name] = ConstColor.COLOR_HEX_RAMP[
self.color_index]
self.color_index += 1
self.color_index = self.color_index % (len(
ConstColor.COLOR_HEX_RAMP))
return self.nodes_color[country_name]
def append_format_nodes(self, node, show_label):
node_name, value = node
color = self.get_node_color(node_name)
format_node = {
"name": node_name,
"itemStyle": {
"color": color
},
"label": {
"show": show_label
}
}
if show_label and value < self.threshold:
format_node["label"] = {"show": not show_label}
self.format_nodes.append(format_node)
def append_links(self, source_port, target_port, value):
self.links.append({
"source": source_port,
"target": target_port,
"value": value
})
def append_nodes(self, source, target, value, input_or_output):
if input_or_output == 'Input':
self.update_nodes(self.source_nodes, source, value)
self.update_nodes(self.middle_nodes, target, value)
elif input_or_output == 'Output':
self.update_nodes(self.middle_nodes, source, value)
self.update_nodes(self.target_nodes, target, value)
def convert_nodes_to_format(self, nodes):
sorted_nodes = sorted(nodes.items(),
key=lambda kv: (kv[1], kv[0]),
reverse=True)
for ahead_node in sorted_nodes[:self.top]:
self.append_format_nodes(ahead_node, show_label=True)
for behind_node in sorted_nodes[self.top:]:
self.append_format_nodes(behind_node, show_label=False)
class AISService(object):
def __init__(self, db_name=''):
self.ais_db = CommonDB(db_name)
self.ais_point = None
def import_data_from_path(
self,
source_path,
source_table,
target_table,
rol_list,
create_table=True,
filter_query="WHERE Vessel_type_sub='Crude Oil Tanker'"):
sql = "INSERT INTO {} SELECT * FROM {} {} GROUP BY MMSI, ts_pos_utc".format(
target_table, source_table, filter_query)
self.ais_db.import_data_from_path(source_path, target_table, rol_list,
sql, create_table)
# region 数据清洗
def clean_dirty_data(self,
table_name,
speed_threshold=None,
draft_threshold=None):
self.clean_mmsi_error_data(table_name)
self.clean_lack_error_data(table_name)
if speed_threshold is not None:
self.clean_speed_error_data(table_name, speed_threshold)
if draft_threshold is not None:
self.clean_draft_error_data(table_name, draft_threshold)
# 剔除掉mmsi错误的数据
def clean_mmsi_error_data(self, table_name):
self.ais_db.delete_data(table_name,
["MMSI > 999999999", "MMSI < 100000000"],
Const.OR_CONNECT_WORD)
# 剔除掉属性缺失的数据
def clean_lack_error_data(self, table_name):
filter_list = [
"speed is null", "draft is null", "longitude is null",
"latitude is null", "mmsi is null", "utc is null"
]
self.ais_db.delete_data(table_name, filter_list, Const.OR_CONNECT_WORD)
# 删除掉速度异常的数据
def clean_speed_error_data(self, table_name, speed_threshold):
self.ais_db.delete_data(
table_name, ["speed < 0", "speed > {}".format(speed_threshold)],
Const.OR_CONNECT_WORD)
# 删除掉吃水异常的数据
def clean_draft_error_data(self, table_name, draft_threshold):
self.ais_db.delete_data(
table_name, ["draft <= 0", "draft > {}".format(draft_threshold)],
Const.OR_CONNECT_WORD)
# endregion
# region 数据获取
def start_fetch_data_transaction(self, source_table):
self.ais_db.run_sql(
"SELECT mmsi, mark, imo, vessel_name, vessel_type, length, width, country, longitude, "
"latitude, draft, speed, utc FROM {} ORDER BY mmsi, mark, utc".
format(source_table))
row = self.ais_db.db_cursor.next()
self.ais_point = AISPoint(row[0], row[2], row[3], row[4], row[5],
row[6], row[7], row[8], row[9], row[10],
row[11], row[12], row[1])
return self.ais_point
def start_fetch_original_data_transaction(self, source_table):
self.ais_db.run_sql(
"SELECT mmsi, imo, vessel_name, vessel_type_sub, length, width, flag_country, longitude, "
"latitude, draft, speed, utc FROM {} ORDER BY mmsi, utc ".format(
source_table))
row = self.ais_db.db_cursor.next()
self.ais_point = AISPoint(row[0], row[1], row[2], row[3], row[4],
row[5], row[6], row[7], row[8], row[9],
row[10], row[11])
return self.ais_point
def has_next_ais_ship(self):
return self.ais_point is not None
# endregion
# region form trajectory
def form_trajectory(self, draft_dict, static_info_writer, line_index,
port_service, port_search_distance_threshold,
outliers_distance_threshold, outliers_speed_threshold):
# init
is_line_head = True
before_ship = self.ais_point
ais_points = []
load_state = draft_dict.fetch_draft_state(before_ship.draft)
outliers_count = 0
for row in self.ais_db.db_cursor:
after_ship = AISPoint(row[0], row[2], row[3], row[4], row[5],
row[6], row[7], row[8], row[9], row[10],
row[11], row[12], row[1])
if is_line_head:
before_ship, after_ship = self.line_head_outliers_detection(
before_ship, after_ship, outliers_distance_threshold,
outliers_speed_threshold)
is_line_head = False
if len(ais_points) == 0 or ais_points[-1] != before_ship:
ais_points.append(before_ship)
if after_ship.is_same_ship(before_ship):
# 判断是否异常点
if AISService.is_outliers(after_ship, before_ship,
outliers_distance_threshold,
outliers_speed_threshold):
outliers_count += 1
continue
if draft_dict.fetch_draft_state(
after_ship.draft) != load_state:
print(after_ship)
if len(ais_points) > 1:
AISService.export_trajectory_to_csv(
ais_points, load_state, port_service,
port_search_distance_threshold, static_info_writer,
line_index)
line_index += 1
load_state = draft_dict.fetch_draft_state(after_ship.draft)
self.ais_point = after_ship
ais_points = []
else:
AISService.export_trajectory_to_csv(
ais_points, load_state, port_service,
port_search_distance_threshold, static_info_writer,
line_index)
self.ais_point = after_ship
return line_index + 1, outliers_count
before_ship = after_ship
AISService.export_trajectory_to_csv(ais_points, load_state,
port_service,
port_search_distance_threshold,
static_info_writer, line_index)
self.ais_point = None
return line_index + 1, outliers_count
def skip_useless_trajectory(self):
for row in self.ais_db.db_cursor:
after_ship = AISPoint(row[0], row[2], row[3], row[4], row[5],
row[6], row[7], row[8], row[9], row[10],
row[11], row[12], row[1])
if not after_ship.is_same_ship(self.ais_point):
self.ais_point = after_ship
return
self.ais_point = None
return
# endregion
def close(self):
self.ais_db.close_db()
@staticmethod
def export_trajectory_to_csv(ais_points, load_state, port_service,
distance_threshold, csv_writer, line_index):
if len(ais_points) == 0:
return
first_point = ais_points[0]
source_port, source_distance = port_service.get_nearest_port(
arcpy.PointGeometry(
arcpy.Point(first_point.longitude, first_point.latitude)),
distance_threshold)
last_point = ais_points[-1]
target_port, target_distance = port_service.get_nearest_port(
arcpy.PointGeometry(
arcpy.Point(last_point.longitude, last_point.latitude)),
distance_threshold)
Utils.export_to_csv(ais_points, csv_writer, [
source_port.name, source_distance, target_port.name,
target_distance, load_state, line_index
])
def line_head_outliers_detection(self, before_ship, after_ship,
outliers_distance_threshold,
outliers_speed_threshold):
if not AISService.is_outliers(after_ship, before_ship,
outliers_distance_threshold,
outliers_speed_threshold):
return before_ship, after_ship
row = next(self.ais_db.db_cursor)
middle_ship = after_ship
after_ship = AISPoint(row[0], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], row[9], row[10], row[11],
row[12], row[1])
if AISService.is_outliers(middle_ship, after_ship,
outliers_distance_threshold,
outliers_speed_threshold):
return before_ship, after_ship
else:
return middle_ship, after_ship
def same_mmsi_identify(self, csv_writer, speed_threshold,
distance_threshold, point_percent):
ship_point = 1
before_ship = self.ais_point
sequentially = [[before_ship]]
for row in self.ais_db.db_cursor:
after_ship = AISPoint(row[0], row[1], row[2], row[3], row[4],
row[5], row[6], row[7], row[8], row[9],
row[10], row[11])
if after_ship.is_same_ship(before_ship):
self.sequentially_identify(after_ship, sequentially,
speed_threshold, distance_threshold)
ship_point += 1
else:
self.export_final_sequentially(sequentially, csv_writer,
point_percent * ship_point)
self.ais_point = after_ship
return
before_ship = after_ship
self.export_final_sequentially(sequentially, csv_writer,
point_percent * ship_point)
self.ais_point = None
return
@staticmethod
def is_outliers(after_ship, before_ship, outliers_distance_threshold,
outliers_speed_threshold):
average_speed = after_ship.get_average_speed_between(
before_ship, outliers_distance_threshold)
return average_speed > outliers_speed_threshold
@staticmethod
def sequentially_identify(ship_point, sequentially, speed_threshold,
distance_threshold):
for i in range(len(sequentially)):
speed = ship_point.get_average_speed_between(
sequentially[i][-1], distance_threshold)
if speed < speed_threshold:
sequentially[i].append(ship_point)
return
sequentially.append([ship_point])
return
@staticmethod
def export_final_sequentially(sequentially, csv_writer, point_threshold):
for i in range(len(sequentially)):
if len(sequentially[i]) < point_threshold:
continue
for ship_point in sequentially[i]:
ship_point.set_mark(i)
csv_writer.writerow(ship_point.export_to_csv())