交通大数据实验一

目录

一、实验目的... 1

二、实验内容... 1

（1）mysql表建立.. 1

（2）数据存入mysql数据库... 3

（3）申请百度地图APK. 6

三、心得体会... 7

一、实验目的

导入交通大数据与百度可视化准备

二、实验内容

1、将提供的出租车GPS数据导入Mysql数据库

以上为出租车GPS数据字段说明。

（1）在mysql数据库中新建出租车数据表，注意：数据表的索引的建立。

（2）编写python程序，读取txt文件，并将与编号相对应的GPS数据写入数据表中。

答：

（1）mysql表建立

由于重庆市所有地区的经纬度位数相同，因此在构建mysql数据表时定义经纬度时可以控制位数，便于节省空间，如下所示：

同理，ID和海拔也可以通过提前查看先验知识在构建表结构的时候进行优化，由于没有主键，定义一个自增的序号作为主键，因此最终构建表结构语句如下：

CREATE TABLE `gps`  (   `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`) );

源数据中的字段对应在表格中的字段类型如下，由于字段“GPS_Type”为1，因此为了节约空间以及存入时间，忽略这个字段，如下：

字段	数据库中类型	说明
GPS_ID	int	主键，从1自增
DATE	date	日期(YYYY:MM:DD)
TIME	time	时间(HH:MM:SS)
ID	varchar(15)	车牌不超过15位字符
LONGTITUDE	decimal(9,6)	经度小数部分不超过六位，整数部分不超过3位
LATITUDE	decimal(8,6)	纬度小数部分不超过六位，整数部分不超过2位
SPEED	float	————
ALTITUDE	int	海拔为整数
PASSENGER	bit(1)	不超过1位
GPS_Type	————	全为1，可忽略

数据库中表格如下：

（2）数据存入mysql数据库

使用python代码将源数据中满足“ID”尾号为“3”、“5”的数据存入数据库中，流程如下：

存入过程的输出如下，存入一个文件，便会输出一个提示：

完成数据插入mysql的代码如下：

import pandas as pd import os from sqlalchemy import create_engine class FliterData:     def __init__(self,folder_addr = "D:\\A_File\\Curriculums\\TrafficBD_P&A\\experiments\\all_data"):         self.folder_addr = folder_addr     def fliter_as(self,*args):         self.names = ['DATE','TIME','ID1','ID','LONGTITUDE','LATITUDE','SPEED','ALTITUDE','PASSENGER','GPS_Type']         count = 0         #创建一个错误数据df存错误数据         self.error_df = pd.DataFrame(columns=self.names)         self.error_df.drop('ID1', axis=1, inplace=True)         self.error_df.drop('GPS_Type', axis=1, inplace=True)         # 遍历文件夹         for filename in os.listdir(self.folder_addr):             file_path = os.path.join(self.folder_addr,filename)             if os.path.isfile(file_path):                 # 创建一个空df保存结果                 self.df1 = pd.DataFrame(columns=self.names)                 self.df1.drop('ID1', axis=1, inplace=True)                 self.df1.drop('GPS_Type', axis=1, inplace=True)                 #读取                 self.df = pd.read_csv(file_path, sep=',', names=self.names, encoding='GBK', converters={'TIME': str})                 # 删除名为'ID1'的列                 # axis=1参数表示操作的是列而不是行，inplace=True参数表示直接在原始DataFrame上进行修改，而不是创建一个新的DataFrame。                 self.df.drop('ID1', axis=1, inplace=True)                 self.df.drop('GPS_Type', axis=1, inplace=True)                 len1 = len(args)                 for i in range(len1):                     mask = self.df['ID'].str.endswith(args[i])                     # 使用布尔索引留下以args[i]结尾的行                     self.df1 = pd.concat([self.df1, self.df[mask]], ignore_index=True)                 #存入数据库,创建数据库引擎                 engine = create_engine('mysql+pymysql://root:123456@localhost:3306/demodb')                 self.df1.to_sql('gps',con=engine,if_exists='append',index=False)                 print("{}载入数据库成功！".format(filename)) if __name__ == "__main__":     fliter = FliterData()     df = fliter.fliter_as('5','3')

本部分实验报告需要提交数据表的设计结构（包括字段名，数据类型，字段长度，存储引擎），所得表的数据量，共涉及多少辆出租车？

最终结果如下，可以看到数据量为“27758129”：

查询一共有多少辆出租车结果如下，一共有出租车“2402”辆：

（3）申请百度地图APK

申请百度地图APK，并且在实验报告中附上所申请百度地图apk的截图。

百度地图开放平台网站：百度地图开放平台 | 百度地图API SDK | 地图开发

三、心得体会

（1）优化表结构：先验知识可知源数据中经纬度最大的小数部分有6位，又根据重庆市的经纬度范围可以精度中整数部分为3位，纬度中整数部分为2位，因此为了节省时间和空间，在创建表的时候可以针对设置为“decimal(9,6)”和“decimal(8,6)”。不设置为“float(9,6)”的原因是float会变化精度，可能使得第六位小数再存入数据库后发生变化；

（2）加快存储速率：本文预先使用的方法是将所有的数据读入一个“DataFrame”，再保存为txt或csv文件，随后在navicat中存入数据库。但是本实验涉及数据太大，会导致很大一部分数据丢失，因此修改为在python中存入数据库，搜索资料发现有一个方法“to_sql”可以直接将DataFrame存入mysql中对应表格，无需手动编写连接、插入等代码。另外，为了加快存入速率，本文读取一个文件就存一个，并非读取所有文件后存入。

（3）读取前导0：由于TIME字段前面有若干0，直接read_csv会导致0读取不到，因此需要添加参数：self.df = pd.read_csv(file_path, sep=',', names=self.names, encoding='GBK', converters={'TIME': str})。

（4）关于读取速度：为了减少空间消耗与加快读取速度，本文原计划将SPEED字段类型设置为“decimal(4.1)”，速度不会超过四位数，且先验得知小数部分为1，但是运行到3.1-253个文件时出现错误，因为某些速度存在错误数据，速度为负数，因此最终将SPEED类型改为了float。