一、实验目的：
对于出租车轨迹数据可以进行正确的预处理
二、实验内容：
1. 数据质量提升
观察在实验一中导入的出租车GPS数据，发现其中的数据质量问题，并进行修复。
提示 原始数据中可能存在以下问题： a. 记录重复问题；b. 车辆的时刻表在变而GPS坐标不变；c. 车辆状态字段频繁跳动。常见数据修复手段：a. 删除问题数据；b. 对问题数据进行修改
2. 在百度地图上绘制某辆出租车的轨迹
（1）从数据表中提取某辆车的GPS记录并按时间排序，得到该车辆的轨迹，为便于实验，可以从轨迹中选择20个点在百度地图上绘制。
（2）将所选择的轨迹点由GPS坐标系转化为百度坐标系。
（3）在百度地图中绘制出该条轨迹的线路。
三、实验步骤
1. 构造测试数据集
首先构造一个测试数据集，数据集中包括有完整数据、缺失值数据、重复值数据、异常数据等，然后导入到SQL表中。
（1）重复数据
（2）缺失数据
（3）车辆状态字段频繁跳动
（4）时刻表变，但经纬度没变
2. 数据质量提升
（1）数据缺失值处理
对数据表中的数据进行缺失值处理，由于样本数据量比较大，缺失值数据比较少，因此此处就直接将缺失值数据删除。
代码：
import pymysql
# 连接数据库
try:
db = pymysql.connect(host='localhost', user='root', password='123456', database='traffic_data', charset='utf8')
cursor = db.cursor()
# 获取表结构
cursor.execute("SHOW COLUMNS FROM traffic_data.test;")
columns = [column[0] for column in cursor.fetchall()]
# 查询每列的空缺值数量
null_counts = {}
for column in columns:
null_query = f"SELECT COUNT(*) FROM traffic_data.test WHERE {column} IS NULL;"
cursor.execute(null_query)
null_count = cursor.fetchone()[0]
null_counts[column] = null_count
# 输出每列的空缺值数量
print("Null value counts for each column:")
for column, count in null_counts.items():
print(f"{column}: {count}")
# 删除含有空缺值的记录
for column in columns:
if null_counts[column] > 0:
delete_query = f"DELETE FROM traffic_data.test WHERE {column} IS NULL;"
cursor.execute(delete_query)
db.commit() # 提交事务以保存更改
print("含有空缺值的记录已删除。")
except Exception as e:
print("数据库操作失败:", e)
if 'db' in locals():
db.rollback() # 回滚事务以撤销更改
finally:
if 'db' in locals():
db.close() # 关闭数据库连接利用测试数据集，测试程序，
能正确将数据表中的空缺值删除，因此对本实验的整体出租车数据进行空缺值处理：
发现并没有空缺值。
（2）数据重复值处理
由于数据表的数据量太大，创建索引：
ALTER TABLE test1 ADD INDEX idx_id_date_time (ID, DATE, TIME);这里认为某辆出租车在同一日期同一时间同一海拔高度只能有一条数据，如果有多条数据记录，则为重复数据，因此每组重复数据记录，只保留其中一条数据记录，其余的应该删除。利用测试数据，测试程序，运行结果：
从结果来看，已经筛选出重复数据记录，并对每组重复数据只保留了其中的一条记录，其余重复记录已经删除，在测试集中共有2314条重复记录。在SQL中查看测试集test1是否已经成功进行重复值处理：
已经处理成功，接下来就对整个出租车数据集进行处理，代码：
import pymysql
# 数据库连接配置
db_config = {
'host': 'localhost',
'user': 'root',
'password': '123456',
'db': 'traffic_data',
'charset': 'utf8',
'cursorclass': pymysql.cursors.DictCursor
}
# 连接数据库
try:
db = pymysql.connect(**db_config)
cursor = db.cursor()
except Exception as e:
print("连接数据库失败:", e)
exit()
# 设置批次大小
batch_size = 5000
try:
# 开启事务
db.begin()
# 记录删除的总影响行数
total_affected_rows = 0
# 构建查询重复记录的 SQL 语句
query = """
SELECT t1.GPS_ID
FROM traffic_data AS t1
INNER JOIN traffic_data AS t2
ON t1.ID = t2.ID AND t1.GPS_ID > t2.GPS_ID
WHERE t1.DATE = t2.DATE
AND t1.TIME = t2.TIME
"""
cursor.execute(query)
# 获取所有重复记录的 GPS_ID
duplicate_ids = [row['GPS_ID'] for row in cursor.fetchall()]
# 分批删除重复记录
for i in range(0, len(duplicate_ids), batch_size):
delete_batch = duplicate_ids[i:i + batch_size]
# 构建并执行删除命令
if delete_batch:
gps_ids_to_delete = ', '.join(str(gps_id) for gps_id in delete_batch)
delete_query = f"""
DELETE FROM traffic_data
WHERE GPS_ID IN ({gps_ids_to_delete})
"""
cursor.execute(delete_query)
affected_rows = cursor.rowcount
total_affected_rows += affected_rows
db.commit() # 提交当前批次的事务
print(f"已删除重复记录总数为: {total_affected_rows}")
except Exception as e:
db.rollback()
print("删除操作出错:", e)
finally:
# 关闭自动提交
cursor.execute("SET autocommit = 0;")
cursor.close()
db.close()运行结果如下：
已经查询到该出租车数据表中的重复数据，并且将1415130条重复数据记录删除。
（3）车辆的时刻表在变而GPS坐标不变
首先我们将数据表中的数据记录进行排序，先按照DATE日期升序排列，再按照TIME时间升序排列，最后按照车牌号ID升序排列SQL代码为：
SELECT * FROM traffic_data.traffic_data
ORDER BY DATE ASC, ID ASC, TIME ASC;排序部分展示结果：
从上面数据来看，我们可以对该数据表进行以一辆出租车为单位进行处理，即对于某个车牌号的出租车，若某条记录的TIME在变化并且上一条数据记录的SPEED不为0时，而LONGTITUDE 、LATITUDE与上一条数据记录比较时都没变化时，则上一条记录为异常数据，统计共有多少条异常数据。然后对异常数据进行处理，将异常数据的SPEED用0替代。因为时刻表在变，而经纬度度没变可能是出租车上下客、等红绿灯，速度为0，从而使经纬度没有变化。
测试集，测试程序：
能正确找到异常数据并处理，接下来对整体出租车数据进行处理，得到结果：
代码：
import pymysql
# 数据库连接配置
db_config = {
'host': 'localhost',
'user': 'root',
'password': '123456',
'db': 'traffic_data',
'charset': 'utf8',
'cursorclass': pymysql.cursors.DictCursor
}
# 连接数据库
try:
db = pymysql.connect(**db_config)
cursor = db.cursor()
except Exception as e:
print("连接数据库失败:", e)
exit()
# 设置批次大小
batch_size = 1000 # 可以根据实际情况调整批次大小
# 初始化异常计数
anomaly_count = 0
try:
# 开启事务
db.begin()
# SQL查询语句
sql_select = """
SELECT * FROM traffic_data
ORDER BY DATE ASC, TIME ASC, ID ASC;
"""
# 执行SQL查询语句
cursor.execute(sql_select)
# 获取所有记录列表
results = cursor.fetchall()
# 初始化前一条记录变量和用于批量更新的数据列表
prev_row = None
updates = []
# 遍历结果集
for row in results:
if prev_row and row['ID'] == prev_row['ID']:
# 检查经度或纬度是否发生变化，并且上一条记录的速度不为0
if (row['LONGTITUDE'] != prev_row['LONGTITUDE'] or row['LATITUDE'] != prev_row['LATITUDE']) and prev_row['SPEED'] != 0:
# 记录异常数据的更新信息
updates.append((0, prev_row['ID'], prev_row['DATE'], prev_row['TIME']))
anomaly_count += 1 # 更新异常数据计数
# 检查是否需要执行批量更新
if len(updates) >= batch_size:
cursor.executemany("""
UPDATE traffic_data
SET SPEED = %s
WHERE ID = %s AND DATE = %s AND TIME = %s;
""", updates)
db.commit() # 提交当前批次的事务
updates = [] # 清空更新列表，为下一批准备
prev_row = row
# 执行剩余的批量更新
if updates:
cursor.executemany("""
UPDATE traffic_data
SET SPEED = %s
WHERE ID = %s AND DATE = %s AND TIME = %s;
""", updates)
db.commit() # 提交剩余的事务
except Exception as e:
db.rollback()
print("操作出错:", e)
finally:
# 输出异常数据记录总数
print(f"异常数据记录总共有: {anomaly_count}条,并且已经正确处理异常数据。")
# 关闭数据库连接
cursor.close()
db.close()（4）明显的速度错误
在前面处理数据时，发现有些速度数据存在错误，有些速度数据为负数或者过大。用SQL语句查询如下：
SELECT * FROM traffic_data.traffic_data
WHERE SPEED>180 OR SPEED<0;查询结果：
SPEED速度居然有达748.4km/h的记录，很明显这类数据是错误的记录。用SQL查询该数据表中共有多少条该类错误记录:
SELECT count(*) FROM traffic_data.traffic_data
WHERE SPEED>180 OR SPEED<0;查询结果：
共有359条数据速度有错误，同时我国对小车高速路速度限速120km/s，并且超速20%以内在高速公路上行驶将不会受到扣分的处罚，这意味着按照120km/h的限速值计算，高速路段最高可行驶至143km/h。同时网上都传重庆出租车是出了名的快，可能某段路程出租车的时速超过了143km/h，但是速度也不能无限加快，得看出租车的性能。通过网上查阅资料，发现重庆市出租车性能最大能支撑180km/h的速度，因此将速度大于180的速度视为数据记录错误。但是为了保持车辆的在时间和位置上的连贯性，将速度为负的改为0，速度超过120km/h的记录改为120km/h。SQL语句实现：
use traffic_data;
SET SQL_SAFE_UPDATES = 0;
UPDATE traffic_data
SET SPEED = CASE
WHEN SPEED < 0 THEN 0
WHEN SPEED > 180 THEN 120
ELSE SPEED
END;
SET SQL_SAFE_UPDATES = 1;运行代码，处理结束后，再查询速度小于0和超过180的记录数为0，表明处理成功。
2.在百度地图上绘制某辆出租车的轨迹
（1）提取某辆车的GPS记录并按时间排序
首先，我们需要从traffic_data数据表中提取特定车辆的记录，如车牌ID为渝0100800933，然后按时间排序。
代码：
# 提取出租车数据
import pymysql
import datetime
# 数据库连接配置
db_config = {
'host': 'localhost',
'user': 'root',
'password': '123456',
'db': 'traffic_data',
'charset': 'utf8',
'cursorclass': pymysql.cursors.DictCursor
}
# 连接数据库
try:
db = pymysql.connect(**db_config)
cursor = db.cursor()
except Exception as e:
print("连接数据库失败:", e)
exit()
# 查询特定车辆的GPS记录
vehicle_id = '渝0100800933' # 假设我们要查询的车辆ID
try:
query = """
SELECT * FROM traffic_data
WHERE ID = %s
ORDER BY TIME ASC
LIMIT 20; -- 限制结果为20个点
"""
cursor.execute(query, (vehicle_id,))
trajectory = cursor.fetchall()
for point in trajectory:
print(point)
except Exception as e:
print("查询失败:", e)
finally:
# 关闭数据库连接
cursor.close()
db.close()提取的数据为：
（2）将GPS坐标转换为百度坐标系
将GPS坐标转换为百度坐标系，百度提供了一个Web服务API来进行这种转换，即需要将GPS坐标（LONGTITUDE 和 LATITUDE）发送到百度API，然后接收转换后的坐标。
代码：
import pymysql
import json
from datetime import datetime, date, time, timedelta
import urllib.request # 确保正确导入urllib.request
# 数据库连接配置
db_config = {
'host': 'localhost',
'user': 'root',
'password': '123456',
'db': 'traffic_data',
'charset': 'utf8mb4',
}
# 百度API密钥
baidu_ak = 'XNeLiUBdjxiq7miI5pB7nzZqLvqdSFAy'
# 定义坐标转换函数
def convert_to_baidu_coordinates(gps):
url = f"http://api.map.baidu.com/geoconv/v1/?coords={gps}&from=1&to=5&ak={baidu_ak}&quiet=1" # 修正URL
try:
response = urllib.request.urlopen(url)
data = json.loads(response.read())
if data['status'] == 0:
return data['result'][0]['x'], data['result'][0]['y']
else:
print(f"坐标转换失败: {gps}")
return long, lat # 返回原始坐标
except Exception as e:
print(f"坐标转换异常: {e}")
return long, lat # 如果发生异常，返回原始坐标
# SQL查询语句
sql = "SELECT LONGTITUDE, LATITUDE, DATE, TIME FROM traffic_data WHERE id = '渝0100001827' ORDER BY TIME LIMIT 30"
# 连接到MySQL数据库
connection = pymysql.connect(**db_config)
try:
with connection.cursor() as cursor:
cursor.execute(sql)
all_records = cursor.fetchall()
if len(all_records) < 30:
print("记录数少于20条，无法选择20个点。")
else:
data_set1 = []
data_set2 = []
# 转换data_set1中的坐标并构建path字符串
for long, lat, date_, time_ in all_records:
converted_long, converted_lat = convert_to_baidu_coordinates(f"{long},{lat}")
if converted_long is not None and converted_lat is not None:
path = f"{converted_long},{converted_lat};"
data_set1.append({
'path': path,
'roadclass': 'green'
})
else:
print(f"坐标转换失败，已跳过记录: ({long},{lat})")
# 从20个点中选取特定点添加到data_set2，并转换坐标
selected_indices = [0,1,2,2,3,4,5,6,7,8, 9,10,11,12,13,14,15,16,17,18, 19,20,21,22,23,24,25,26,27,28,29] # 根据记录ID选择特定的点
for idx in selected_indices:
long, lat, date_, time_ = all_records[idx]
converted_long, converted_lat = convert_to_baidu_coordinates(f"{long},{lat}")
if converted_long is not None and converted_lat is not None:
# 将 timedelta 对象转换为 datetime.time 对象
seconds = int(time_.total_seconds())
hours, remainder = divmod(seconds, 3600)
minutes, seconds = divmod(remainder, 60)
time_obj = time(hour=hours, minute=minutes, second=seconds)
datetime_ = datetime.combine(date_, time_obj)
data_set2.append({
'myid': f"point{idx + 1}",
'mypoint': f"{converted_long},{converted_lat}",
'passtime': datetime_.strftime('%Y-%m-%d %H:%M:%S')
})
else:
print(f"坐标转换失败，已跳过记录: ({long},{lat})")
# 构建最终的JS内容并写入文件
js_content = "var data_set1 = " + json.dumps(data_set1, indent=2) + ";\n"
js_content += "var data_set2 = " + json.dumps(data_set2, indent=2) + ";\n"
with open('route0.js', 'w') as js_file:
js_file.write(js_content)
finally:
connection.close()得到的转换后的百度坐标系写入JS文件中，部分展示如下：
（3）在百度地图中绘制轨迹
基于上面提取的20个点，及其转换成的百度坐标系，调用百度API将这些点在百度地图上显示，HTML文件为：
import pymysql
import json
from datetime import datetime, date, time, timedelta
import urllib.request # 确保正确导入urllib.request
# 数据库连接配置
db_config = {
'host': 'localhost',
'user': 'root',
'password': '123456',
'db': 'traffic_data',
'charset': 'utf8mb4',
}
# 百度API密钥
baidu_ak = 'XNeLiUBdjxiq7miI5pB7nzZqLvqdSFAy'
# 定义坐标转换函数
def convert_to_baidu_coordinates(gps):
url = f"http://api.map.baidu.com/geoconv/v1/?coords={gps}&from=1&to=5&ak={baidu_ak}&quiet=1" # 修正URL
try:
response = urllib.request.urlopen(url)
data = json.loads(response.read())
if data['status'] == 0:
return data['result'][0]['x'], data['result'][0]['y']
else:
print(f"坐标转换失败: {gps}")
return long, lat # 返回原始坐标
except Exception as e:
print(f"坐标转换异常: {e}")
return long, lat # 如果发生异常，返回原始坐标
# SQL查询语句
sql = "SELECT LONGTITUDE, LATITUDE, DATE, TIME FROM traffic_data WHERE id = '渝0100001827' ORDER BY TIME LIMIT 30"
# 连接到MySQL数据库
connection = pymysql.connect(**db_config)
try:
with connection.cursor() as cursor:
cursor.execute(sql)
all_records = cursor.fetchall()
if len(all_records) < 30:
print("记录数少于20条，无法选择20个点。")
else:
data_set1 = []
data_set2 = []
# 转换data_set1中的坐标并构建path字符串
for long, lat, date_, time_ in all_records:
converted_long, converted_lat = convert_to_baidu_coordinates(f"{long},{lat}")
if converted_long is not None and converted_lat is not None:
path = ';'.join(f"{converted_long},{converted_lat}" for converted_long, converted_lat in
zip(converted_long, converted_lat))
data_set1.append({
'path': path,
'roadclass': 'green'
})
else:
print(f"坐标转换失败，已跳过记录: ({long},{lat})")
# 从20个点中选取特定点添加到data_set2，并转换坐标
selected_indices = [0,1,2,2,3,4,5,6,7,8, 9,10,11,12,13,14,15,16,17,18, 19,20,21,22,23,24,25,26,27,28,29] # 根据记录ID选择特定的点
for idx in selected_indices:
long, lat, date_, time_ = all_records[idx]
converted_long, converted_lat = convert_to_baidu_coordinates(f"{long},{lat}")
if converted_long is not None and converted_lat is not None:
# 将 timedelta 对象转换为 datetime.time 对象
seconds = int(time_.total_seconds())
hours, remainder = divmod(seconds, 3600)
minutes, seconds = divmod(remainder, 60)
time_obj = time(hour=hours, minute=minutes, second=seconds)
datetime_ = datetime.combine(date_, time_obj)
data_set2.append({
'myid': f"point{idx + 1}",
'mypoint': f"{converted_long},{converted_lat}",
'passtime': datetime_.strftime('%Y-%m-%d %H:%M:%S')
})
else:
print(f"坐标转换失败，已跳过记录: ({long},{lat})")
# 构建最终的JS内容并写入文件
js_content = "var data_set1 = " + json.dumps(data_set1, indent=2) + ";\n"
js_content += "var data_set2 = " + json.dumps(data_set2, indent=2) + ";\n"
with open('route0.js', 'w') as js_file:
js_file.write(js_content)
finally:
connection.close()结果展示：
4.总结
由于本文的实验数据量比较大，所有用常规的方式进行数据处理，如数据清洗等，会比较消耗资源和时间，因此一般可以建立适当的索引、使用批处理的方法，可以加快处理的速度。
扫一扫
955
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
