|
目录
一、实行目的... 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;
|
选择的记录如下：
编写代码绘制图像，代码如下：
<!DOCTYPE html>
<html>
<head>
<title>百度地图轨迹绘制示例</title>
<!-- 引入百度地图API -->
<script type="text/javascript" src="http://api.map.baidu.com/api?v=2.0&ak=此处为AK KEY,为了信息安全不展示"></script>
<style>
html, body, #map {
width: 100%;
height: 100%;
margin: 0;
padding: 0;
}
</style>
</head>
<body>
<div id="map"></div>
<script type="text/javascript">
// 创建地图实例
var map = new BMap.Map("map");
// 设置地图中心点和缩放级别
map.centerAndZoom(new BMap.Point(106.561548, 29.553562), 15);
// 启用滚轮缩放
map.enableScrollWheelZoom();
// 界说坐标点数组
var points = [
// ... 您的20个坐标点
new BMap.Point( 106.558047 , 29.556890 ),
new BMap.Point( 106.558230 , 29.557102 ),
new BMap.Point( 106.558525 , 29.557482 ),
new BMap.Point( 106.558885 , 29.558200 ),
new BMap.Point( 106.559030 , 29.558553 ),
new BMap.Point( 106.560947 , 29.558095 ),
new BMap.Point( 106.562913 , 29.557660 ),
new BMap.Point( 106.563845 , 29.556953),
new BMap.Point( 106.565138 , 29.556737 ),
new BMap.Point( 106.564973 , 29.556678 ),
new BMap.Point( 106.566440 , 29.556377 ),
new BMap.Point( 106.568153 , 29.556713 ),
new BMap.Point( 106.561548 , 29.553562 ),
new BMap.Point( 106.561440 , 29.543227 ),
new BMap.Point( 106.579302 , 29.546057 ),
new BMap.Point( 106.580548 , 29.543102 ),
new BMap.Point( 106.577453 , 29.540718 ),
new BMap.Point( 106.574210 , 29.537488 ),
new BMap.Point( 106.574247 , 29.537228 ),
new BMap.Point( 106.573743 , 29.536555 )
];
// 绘制轨迹线
var polyline = new BMap.Polyline(points, {strokeColor: "RED", strokeWeight: 3, strokeOpacity: 1});
map.addOverlay(polyline);
// 为每个坐标点添加标记
for (var i = 0; i < points.length; i++) {
var marker = new BMap.Marker(points, {title: i === 0 ? "起点" : i === points.length - 1 ? "尽头" : ""});
map.addOverlay(marker);
// 为标记添加点击事件监听器
marker.addEventListener("click", (function(index) {
return function() {
// 创建信息窗口
var content = "点 " + index + ": 经度 " + this.getPosition().lng + ", 纬度 " + this.getPosition().lat;
var infoWindow = new BMap.InfoWindow(content, {width: 200, height: 100});
// 打开信息窗口
map.openInfoWindow(infoWindow, this.getPosition());
}
})(i));
}
</script>
</body>
</html>
|
绘制结果如下，点击每个点，会出现窗口显示该点是第几个坐标点，并且显示坐标：
对于上图的点在地图中为长江中，本文为了严谨在源数据中查察了该条数据，发现确实是这个坐标，并不是在各种传输和处理中发生了数据错误，源数据如下：
一开始推测是出租车或地图体系设定了偏移量，防止正确坐标公开的本事，但是查察资料可知百度地图采用的是不同的坐标系，源数据采用的是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）种绘图代码，如下：
<!DOCTYPE html>
<html>
<head>
<title>百度地图轨迹绘制示例</title>
<!-- 引入百度地图API -->
<script type="text/javascript" src="http://api.map.baidu.com/api?v=2.0&ak=不展示个人私用ak key"></script>
<style>
html, body, #map {
width: 100%;
height: 100%;
margin: 0;
padding: 0;
}
</style>
</head>
<body>
<div id="map"></div>
<script type="text/javascript">
// 创建地图实例
var map = new BMap.Map("map");
// 设置地图中心点和缩放级别
map.centerAndZoom(new BMap.Point(106.561548, 29.553562), 15);
// 启用滚轮缩放
map.enableScrollWheelZoom();
// 界说坐标点数组
var points = [
// ... 您的20个坐标点，百度坐标系
new BMap.Point(106.56818945549561, 29.560354861139906),
new BMap.Point(106.56837232527141, 29.56056614149288),
new BMap.Point(106.56866671021035, 29.560945354510398),
new BMap.Point(106.56902643246268, 29.56166231772796),
new BMap.Point(106.56917128045349, 29.562014600016184),
new BMap.Point(106.57108745621896, 29.56154732445254),
new BMap.Point(106.57305365564785, 29.561099438075896),
new BMap.Point(106.57398610663881, 29.560385289399388),
new BMap.Point(106.57528015057888, 29.560158213772223),
new BMap.Point(106.57511489310097, 29.560100573762384),
new BMap.Point(106.5765835549039, 29.559785989538952),
new BMap.Point(106.57829924009575, 29.56010404048995),
new BMap.Point(106.5716886617966, 29.557009084207003),
new BMap.Point(106.57157765804568, 29.546670637246514),
new BMap.Point(106.58947915285313, 29.549286749345466),
new BMap.Point(106.59072901826465, 29.54630961912352),
new BMap.Point(106.58762033523408, 29.543974713714608),
new BMap.Point(106.5843642720141, 29.540792275811725),
new BMap.Point(106.58440111237734, 29.54053185074006),
new BMap.Point(106.58389499775093, 29.539865707606058)
];
// 绘制轨迹线
var polyline = new BMap.Polyline(points, {strokeColor: "RED", strokeWeight: 3, strokeOpacity: 1});
map.addOverlay(polyline);
// 为每个坐标点添加标记
for (var i = 0; i < points.length; i++) {
var marker = new BMap.Marker(points, {title: i === 0 ? "起点" : i === points.length - 1 ? "尽头" : ""});
map.addOverlay(marker);
// 为标记添加点击事件监听器
marker.addEventListener("click", (function(index) {
return function() {
// 创建信息窗口
var content = "点 " + index + ": 经度 " + this.getPosition().lng + ", 纬度 " + this.getPosition().lat;
var infoWindow = new BMap.InfoWindow(content, {width: 200, height: 100});
// 打开信息窗口
map.openInfoWindow(infoWindow, this.getPosition());
}
})(i));
}
</script>
</body>
</html>
|
绘制结果如下，同一点击轨迹上的各个坐标点会显示当前是第几个坐标并且显示百度坐标系下的经纬度，和使用GPS坐标系下的经纬度绘制轨迹比力，发现是GPS坐标与百度地图存在差异，所以会出现点在长江种的现象：
三、心得体会
1、在mysql中使用复杂语句进行数据清洗处理的方法，详见上文代码；
2、在mysql中机动采用不同方法利用数据特性达到加快运行效率的作用，例如删除时候表在表而GPS坐标稳固的数据，可以先将数据按照：车牌递增或递减、日期递减、时间递减的次序存为一个表，然后逐个比力相邻两行数据，如果下一条数据坐标稳固，且是同一辆车，就删除当前数据，因为当前数据正是较晚的数据，在如许比力下去。如果不如许，而采用表毗连的方法，每个记录都会与全部记录比力，复杂度为N^N，而这个方法复杂度为N，大大淘汰了运行时间；
3、绘制地图的时候，可以添加坐标点标识，并且点击坐标显示当前是第几个点与坐标，如下图：
4、源数据采用的是GPS坐标，要转化为百度坐标绘图才正确，如下：
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
