目录
一、实验目的... 1
二、实验内容... 1
1、数据质量提升.. 1
（1）时刻表和车牌号相同的数据... 1
（2）时刻表在变而GPS坐标不变的数据.. 3
（3）明显错误的速度数据.. 5
（4）其它可能错误数据... 7
2、在百度地图上绘制某辆出租车的轨迹.. 8
（1）绘制20个点轨迹地图... 8
（2）GPS坐标系转化为百度坐标系... 12
（3）在百度地图中绘制出该条轨迹的线路。.. 16
三、心得体会... 19
一、实验目的
对于出租车轨迹数据可以进行正确的预处理。
二、实验内容
1、数据质量提升
观察在实验一中导入的出租车GPS数据，发现其中的数据质量问题，并进行修复。
（1）时刻表和车牌号相同的数据
对于记录重复的数据、同一车辆同一时刻记录的不同数据等情况下的数据，根据它们都具有的性质“时刻表和车牌号相同”，将其均归类为“时刻表和车牌号相同的数据”，对于这些数据，本文采用的数据处理方法为删除，并且保留最先记录的数据，依据是最先记录的数据一般情况下是准确的，后记录的数据可能存在时刻表未及时更新等问题。
删除数据的mysql代码如下：
首先复制一个新表gps1，在这个表里面进行删除数据操作：
| #复制一个新表gps1，在这个表里面进行操作 create table gps1 as SELECT * from gps; |
将不同DATE,TIME,ID组合的数据进行分组，并得到每一组数据中最小的`GPS_ID`值作为`MINID`，就是最早记录的值，删除表中`GPS_ID`不等于`MINID`的数据：
| delete from gps1 where GPS_ID NOT IN( SELECT MINID FROM( SELECT MIN(GPS_ID) AS MINID FROM GPS1 GROUP BY DATE,TIME,ID ) AS A ) |
上述语句完成了时刻表和车牌号相同的数据的删除，下面查询删除前后的数据量：
| SELECT COUNT(*) AS `删除后` FROM GPS1 ; SELECT COUNT(*) AS `原始` FROM GPS ; |
查询结果如下，因此可知，时刻表和车牌号相同的数据量为27758129-26306370= 1451759 (条)：
（2）时刻表在变而GPS坐标不变的数据
对于短时间内，同一辆车的多个记录，记录的经纬度不变，这说明车辆应该面临红灯、等人等情况，这些经纬度不变的数据完全是一直冗余的数据，因此将其中时间晚的数据删除处理。对于这部分数据处理有两种方法：
方法一，运行速度非常慢：
首先，将（1）中得到的数据再进行复制为GPS2，语句如下：
| CREATE TABLE GPS2 AS SELECT * FROM GPS1; |
随后，对数据进行处理，删除短时间内在同一经纬度下时间相差31秒的记录并删除，选择31秒的原因是观察到原始记录中正常情况下两条记录的记录间隔是15秒，语句如下：
| DELETE R1 FROM GPS2 AS R1 LEFT JOIN GPS2 AS R2 ON R1.DATE = R2.DATE AND R1.ID = R2.ID AND R1.LATITUDE = R2.LATITUDE AND R1.LONGTITUDE = R2.LONGTITUDE AND R1.GPS_ID > R2.GPS_ID -- 确保保留ID较小的记录，即时间较早的记录 AND TIMEDIFF(R1.TIME, R2.TIME) BETWEEN 0 AND 000031 -- 使用TIMEDIFF函数来比较时间差 WHERE R2.ID IS NOT NULL; |
由于数据量很大，这样进行表连接的复杂度为O(N^2)，运行速度非常慢，因此根据数据采用方法二。
方法二：
首先创建一个相同的表结构，名为`GPS2`：
| CREATE TABLE `gps2` ( `GPS_ID` int AUTO_INCREMENT, `DATE` date , `TIME` time , `ID` varchar(15) , `LONGTITUDE` DECIMAL(9, 6) , `LATITUDE` DECIMAL(8, 6) , `SPEED` float, `ALTITUDE` int , `PASSENGER` bit(1), PRIMARY KEY (`GPS_ID`) ); |
将（1）得到的数据`GPS1`数据按照日期递减、车牌号递增或递减、时间递减的方式排序并按这个顺序存入`GPS2`中：
| INSERT INTO GPS2 (DATE,TIME,ID,LONGTITUDE,LATITUDE,SPEED,ALTITUDE,PASSENGER) SELECT DATE,TIME,ID,LONGTITUDE,LATITUDE,SPEED,ALTITUDE,PASSENGER FROM GPS1 ORDER BY ID ASC, DATE DESC, TIME DESC; |
巧妙点：这样一来我们就可以利用数据顺序来比较相邻两两记录之间，删除与下一个数据的经纬度相同，且是同一辆车的数据，由于事先按照时间递减的顺序排序的，所以删除的数据是经纬度相同的相邻时间数据中较晚的数据，只保留了最早的数据，sql语句如下：
| DELETE FROM GPS2 WHERE GPS_ID IN( SELECT R1.GPS_ID FROM GPS2 AS R1 LEFT JOIN GPS2 AS R2 #GPS_ID是自增的，所以比较GPS_ID+1的下一条数据 ON R1.GPS_ID = R2.GPS_ID+1 AND R1.ID = R2.ID AND R1.LONGTITUDE = R2.LONGTITUDE AND R1.LATITUDE = R2.LATITUDE) |
最后查询剩余数据量：
减去（2）中得到的数据量，因此这部分删除的数据量为：6574692(条)。
（3）明显错误的速度数据
在前期尝试处理数据的过程中发现速度数据存在错误，有些速度数据为负数或者过大，查阅资料可知我国对小车高速路速度限速120km/s，此外，新修订的《道路交通安全违法行为记分管理办法》对于超速的处罚标准有所调整。根据新规，超速20%以内在高速公路上行驶将不会受到扣分的处罚，这意味着按照120km/h的限速值计算，新规实施后高速路段最高可行驶至143km/h而不会被扣分。因此源数据中速度为143km/h以上的数据可能是错误数据，但是不排除有的时候出租车出现了超速的情况，因此进一步查询了2017年重庆市出租车型号，得出重庆市出租车性能不能支撑超过180km/h的速度，因此，将速度大于180的速度视为记录错误，为了解决这种情况并且不丢失原本车辆的时间、位置信息，本文将有错误速度记录的速度修改为0km/h。语句如下：
首先将（2）中得到的数据复制为GPS3：
| CREATE TABLE GPS3 AS SELECT *FROM GPS2; |
查看错误速度数据：
| SELECT * FROM GPS3 WHERE SPEED>180 OR SPEED<0; SELECT count(*) FROM GPS3 WHERE SPEED>180 OR SPEED<0; |
运行结果如下，可以看见记录速度大于180km/h或者速度为负数的数据有307个：
将这些数据的速度改为0km/h：
| UPDATE GPS3 SET SPEED = 0 WHERE SPEED<0 OR SPEED >180; |
错误速度数据307个，修改为0。
随后将记录顺序换回时间递增的顺序：
| #换回时序递增顺序 CREATE TABLE `gps4` ( `GPS_ID` int AUTO_INCREMENT, `DATE` date , `TIME` time , `ID` varchar(15) , `LONGTITUDE` DECIMAL(9, 6) , `LATITUDE` DECIMAL(8, 6) , `SPEED` float, `ALTITUDE` int , `PASSENGER` bit(1), PRIMARY KEY (`GPS_ID`) ); INSERT INTO GPS4 (DATE,TIME,ID,LONGTITUDE,LATITUDE,SPEED,ALTITUDE,PASSENGER) SELECT DATE,TIME,ID,LONGTITUDE,LATITUDE,SPEED,ALTITUDE,PASSENGER FROM GPS3 ORDER BY ID ASC, DATE ASC, TIME ASC; |
结果如下：
（4）其它可能错误数据
经过三次清洗后的数据已经不存在重复、冗余数据，速度错误数据也修改为0km/s，目前为止数据量从27758129(条)变为19731678(条)。这些数据里面可能还存在错误数据，但是为了尽可能保留数据的原始特征，并且方便后续工作，本实验停止进一步处理数据，在后续实验中将会根据题目要求对数据进一步处理。
2、在百度地图上绘制某辆出租车的轨迹
（1）绘制20个点轨迹地图
从数据表中提取某辆车的GPS记录并按时间排序，得到该车辆的轨迹，为便于实验，可以从轨迹中选择20个点在百度地图上绘制。
在mysql中选择20个经纬度坐标，均来自同一出租车，并且按照时间排序：
| #20个坐标 SELECT LONGTITUDE,LATITUDE FROM GPS4 LIMIT 20; |
选择的记录如下：
编写代码绘制图像，代码如下：
|
|
绘制效果如下，点击每个点，会出现窗口显示该点是第几个坐标点，并且显示坐标：
对于上图的点在地图中为长江中，本文为了严谨在源数据中查看了该条数据，发现确实是这个坐标，并不是在各种传输和处理中发生了数据错误，源数据如下：
一开始推测是出租车或地图系统设定了偏移量，防止准确坐标公开的手段，但是查看资料可知百度地图采用的是不同的坐标系，源数据采用的是GPS坐标系，因此需要在百度地图开放平台使用“坐标转换服务”将GPS坐标转化为百度坐标。
（2）GPS坐标系转化为百度坐标系
将所选择的轨迹点由GPS坐标系转化为百度坐标系，参数设置如下：coords：106.558047,29.556890;106.558230,29.557102;106.558525,29.557482;106.558885,29.558200;106.559030,29.558553;106.560947,29.558095;106.562913,29.557660;106.563845,29.556953;106.565138,29.556737;106.564973,29.556678;106.566440,29.556377;106.568153,29.556713;106.561548,29.553562;106.561440,29.543227;106.579302,29.546057;106.580548,29.543102;106.577453,29.540718;106.574210,29.537488;106.574247,29.537228;106.573743,29.536555。model有多种，查看说明可知设置为2时，是将GPS坐标转化为百度坐标，可以复制各种语言代码运行也可以在线运行。
在线运行结果如下：
| { "status": 0,//状态0说明正常运行 "result": [ { "x": 106.56818945549561, "y": 29.560354861139906 }, { "x": 106.56837232527141, "y": 29.56056614149288 }, { "x": 106.56866671021035, "y": 29.560945354510398 }, { "x": 106.56902643246268, "y": 29.56166231772796 }, { "x": 106.56917128045349, "y": 29.562014600016184 }, { "x": 106.57108745621896, "y": 29.56154732445254 }, { "x": 106.57305365564785, "y": 29.561099438075896 }, { "x": 106.57398610663881, "y": 29.560385289399388 }, { "x": 106.57528015057888, "y": 29.560158213772223 }, { "x": 106.57511489310097, "y": 29.560100573762384 }, { "x": 106.5765835549039, "y": 29.559785989538952 }, { "x": 106.57829924009575, "y": 29.56010404048995 }, { "x": 106.5716886617966, "y": 29.557009084207003 }, { "x": 106.57157765804568, "y": 29.546670637246514 }, { "x": 106.58947915285313, "y": 29.549286749345466 }, { "x": 106.59072901826465, "y": 29.54630961912352 }, { "x": 106.58762033523408, "y": 29.543974713714608 }, { "x": 106.5843642720141, "y": 29.540792275811725 }, { "x": 106.58440111237734, "y": 29.54053185074006 }, { "x": 106.58389499775093, "y": 29.539865707606058 } ] } |
（3）在百度地图中绘制出该条轨迹的线路。
提示：（2）和（3）可以参考学习通资料中的“百度地图可视化”与“百度地图示例”
将百度坐标代码重新放入（1）种绘图代码，如下：
|
|
绘制结果如下，同一点击轨迹上的各个坐标点会显示当前是第几个坐标并且显示百度坐标系下的经纬度，和使用GPS坐标系下的经纬度绘制轨迹比较，发现是GPS坐标与百度地图存在差异，所以会出现点在长江种的现象：
| 转化为百度坐标系后绘图 | GPS坐标系绘图 |
三、心得体会
1、在mysql中使用复杂语句进行数据清洗处理的方法，详见上文代码；
2、在mysql中灵活采用不同方法利用数据特性达到加快运行效率的作用，例如删除时刻表在表而GPS坐标不变的数据，可以先将数据按照：车牌递增或递减、日期递减、时间递减的顺序存为一个表，然后逐个比较相邻两行数据，如果下一条数据坐标不变，且是同一辆车，就删除当前数据，因为当前数据正是较晚的数据，在这样比较下去。如果不这样，而采用表连接的方法，每个记录都会与所有记录比较，复杂度为N^N，而这个方法复杂度为N，大大减少了运行时间；
3、绘制地图的时候，可以添加坐标点标识，并且点击坐标显示当前是第几个点与坐标，如下图：
4、源数据采用的是GPS坐标，要转化为百度坐标绘图才准确，如下：
| 转化为百度坐标系后绘图 | GPS坐标系绘图 |