1. CRAD数据库整体设计思路1.1 为什么需要一套统一的“高铁航线”数据做交通领域的数据分析最头疼的事情之一就是数据源太散。高铁运力数据、班次信息分散在铁路系统的各个公开渠道里航空数据又分别散落在民航系统的班期计划、航班动态和票价接口里。想把2003到2022年这二十年间中国高铁和航空航线放在同一张表里做对比研究光是整理数据就能耗掉大半个项目的周期。CRAD这个名字听起来挺正式实际上它的核心价值就一句话把高铁和航空两套运输系统的数据用统一字段、统一粒度、统一时间口径放进一个数据库里。最早的版本其实只有高铁线路数据后来发现单看高铁没法回答很多实际问题——比如一条高铁开通后同航线上的航班量到底受了多大冲击。所以后面迭代版本把航空航线数据也合了进来形成了一个真正意义上的综合交通航线数据库。所谓航线这里不单指航空公司的航班航线还包括高铁的线路走向。只要一条高铁线开通运营它对应的起点站、终点站、途经站、里程、设计时速、开通年月这些信息都会记录下来。航空部分是每一对起降城市之间的计划航班量、执飞航司、机型分布等信息这样两边就能在“城市对”级别做对齐分析。1.2 字段粒度与时间跨度的设计考量从2003年开始是因为那条里程碑式的高铁试验线在这一年动工。那一年海口线已经开通了但设计时速并不快严格来说还不算高速铁路。从2003年到2008年京津城际开通再到后面四纵四横、八纵八横的逐步成网这二十年是中国高铁从无到有、从试点到全面铺开的关键周期。如果想研究高铁网络演变对沿线城市的影响起点定在2003年非常合理。数据粒度的设计我踩过几次坑。刚开始做的时候只记录到“线路”级别也就是一条高铁线路一行字段里写清楚经过哪些城市这样整理起来很快但用起来非常痛苦。你想分析某一年所有高铁站点之间的连通情况线路级别的数据根本不够必须自己用字符串去拆途经站拆完还得清洗极其繁琐。后来我把数据结构拆成了三个层级线路主表记录高铁线路的基本属性一条线路一行字段略。站点停靠表记录每条线路经过的所有站点及顺序一个站点一条记录。城市对连通表把站点停靠通过首尾城市抽出来形成任意两个城市之间是否有高铁直达、当天多少班次、最快旅行时间等指标。航空部分则直接使用了城市对的维度因为航班数据天然就是按起降点组织的。这样两边对齐的时候只需要按城市名做关联不需要再做维度转置整个查询逻辑简化了一大截。1.3 技术选型从CSV到关系型数据库最初版的CRAD用的是几个大CSV文件文件命名靠日期和版本号区分查询靠pandas在本地内存里跑。好处是门槛低、分享方便坏处是数据一多就卡而且你根本没法做高效的复杂关联查询。比如你想查某条航线三年内每个月的高铁班次和航班量变化趋势CSV方案要把三张表load进来再逐月筛选、分组、合并写出来的代码既啰嗦又容易出错。后来我花了一个周末把数据全部迁到了SQLite。为什么选SQLite而不是MySQL或者PostgreSQL最直接的原因是这个数据集属于典型的单人读多写少场景体积也不大——所有的原始表和中间表加起来不超过2GB。SQLite零配置、单文件、备份简单往U盘里一拷就能带走配合支持SQL的标准生态任何工具都能读。如果是团队协作或者准备做成线上服务那才需要考虑切到PostgreSQL。数据库表结构的设计原则有三个能用代码生成的字段绝不手工录入、能拆出来的维度绝不堆在同一个字段里、所有的日期统一成标准格式。后面实际使用过程中这些原则帮我省掉了大量的清洗时间。2. 数据架构、核心表结构与来源处理2.1 核心表结构与字段说明CRAD数据库目前有七张核心表每一张负责一个维度的数据。因为整个主题是高铁和航线所以有三张表是绝对的核心hsr_lines高铁线路主表。字段包括线路名称、起点城市、终点城市、线路长度公里、设计时速km/h、是否客运专线、开通年份、运营状态等。hsr_stops高铁站点停靠表。字段包括线路ID、站点序号、站点名称、站点所属城市、站点等级特等站/一等站等、是否枢纽站。air_routes航空航线表。字段包括年份、季度、起点城市三字码、终点城市三字码、航班量班次/周、执飞航司数量、平均机型座位数、是否季节性航线。其余四张辅助表分别承担城市信息归一、经纬度坐标、行政区划映射和年表信息方便做地理可视化和按区域筛选。字段类型上我有一些个人习惯。所有年份一律用INTEGER不用VARCHAR这样在筛选区间的时候速度更快也天然避免“2003”和“2003年”这种脏数据。所有里程、速度、航班量用数值类型车站名称统一用城市加站名后缀的方式比如“北京南”而不是“南站”避免同名站引起的混乱。2.2 数据来源与清洗逻辑高铁线路的基本属性主要参考了国铁公开的运营线路表、历年的铁路建设规划文件以及百科类站点上的线路词条。这三类来源互相印证遇到数字不一致的时候就取官方公告或者实际开通运营版本的数值。航空部分的数据来源相对统一主要基于民航局历年公布的航班计划表。这份计划表数据格式比较规范但也有一些常见的坑某些航班的起降城市在表中是全称加三字码混用部分国际航班和国内航班混在同一个文件里还有季节性航线在不同季度的航班量完全不同。清洗的时候就先把三字码单独抽出来作为关联键再根据航线起点和终点是否都在国内来判断是不是纯国内航线。文本清洗最耗时的是城市名称的归一化。很多数据源里“北京”、“北京市”、“北京首都”、“PEK”指的是同一个对象清洗时要建立一个城市别名映射表把不同格式的称呼统一转成标准的城市名。这个映射表我前后维护了三轮第一轮是手工整理第二轮根据航司数据里的三字码自动回填第三轮靠实际查询异常反向补充。三轮下来基本碰到了各种奇奇怪怪的写法都能一键归一。2.3 数据质量校验与去重策略数据入库之前必须做质量校验否则分析做到一半发现某个数字明显异常又得从头排查非常浪费时间。我的校验策略是分层检查第一层是格式校验。日期字段必须是合法日期数值字段必须能转成数字城市字段不能为空。这一层用脚本自动跑跑完直接输出所有不通过的行号方便定位原始文件的位置。第二层是业务逻辑校验。比如高铁开通年份必须大于等于2003年且小于等于2022年线路长度必须大于0航空航班量必须大于0。这类规则看着简单但实际操作中经常抓到一些源头数据录入错误像是把港珠澳大桥的公里数填到某条高铁线上这类离谱数据如果没有业务校验根本挡不住。第三层是重复值校验。同一线路、同一起终点、同一年份出现两条记录时就要判断是数据源本身重复还是确实存在不同情况。处理原则是如果是完全重复只保留一条如果是年份相同但数值不同以数据源可信度高的版本为准并在备注字段中保留另一版本的数值。去重策略上我在线路主表和航空航线表都建了唯一索引。线路表以线路名称加开通年份做唯一键航空航线表以年份加季度加起终点三字码做唯一键。这样从源头就能阻断重复数据进入正式表。3. 数据库建设实操流程3.1 环境准备与建库建表我当时用的环境是Python 3.9 SQLite 3.37数据库文件放在项目根目录的data/文件夹下数据库连接通过标准库sqlite3完成。如果你机子上装了DBeaver或者Navicat也可以用图形工具去建表但为了后续批量操作的方便我还是推荐在Python脚本里一次性执行建表语句。实际建表的SQL语句大致如下CREATE TABLE IF NOT EXISTS hsr_lines ( line_id INTEGER PRIMARY KEY AUTOINCREMENT, line_name TEXT NOT NULL UNIQUE, start_city TEXT NOT NULL, end_city TEXT NOT NULL, length_km REAL, design_speed INTEGER, open_year INTEGER ); CREATE TABLE IF NOT EXISTS hsr_stops ( stop_id INTEGER PRIMARY KEY AUTOINCREMENT, line_id INTEGER NOT NULL, stop_seq INTEGER NOT NULL, station_name TEXT NOT NULL, city_name TEXT NOT NULL, station_grade TEXT, is_hub INTEGER DEFAULT 0, FOREIGN KEY (line_id) REFERENCES hsr_lines(line_id) ); CREATE TABLE IF NOT EXISTS air_routes ( route_id INTEGER PRIMARY KEY AUTOINCREMENT, year INTEGER NOT NULL, quarter INTEGER NOT NULL, origin_city TEXT NOT NULL, dest_city TEXT NOT NULL, flight_count INTEGER, carrier_count INTEGER, avg_seats INTEGER, UNIQUE(year, quarter, origin_city, dest_city) );几个细节说明一下。is_hub字段用0和1表示是否为枢纽站比直接用文本“是”“否”更节省空间也方便条件筛选。UNIQUE约束在建表时就直接设定好不要等数据进去了再靠脚本去查重那样效率太低。所有主键用自增整数业务字段里保证唯一性两者互不干扰。3.2 数据导入与批处理脚本数据导入是整个项目初期最无聊也最容易翻车的工作。我第一版是手工用Excel整理后直接导入后来数据量大了以后发现手工操作容易漏而且Excel的日期格式经常被自动转换导致入库后发现年份字段成了1905年这种离谱值。后来我写了三个批处理脚本分别负责高铁线路、高铁站点和航空航线的导入。脚本的逻辑是先读取清洁过的CSV文件做基本格式校验然后按行插入目标表插入失败时打印出错行号和错误原因最后统计成功和失败的记录数。要注意pandas的to_sql方法虽然方便但在大批量插入时默认不会做逐行校验一旦中途报错你根本不知道是哪一行的问题。我自己更习惯逐行读取、逐行插入慢是慢了但排错成本低很多。如果数据特别大可以用批量事务的方式每1000行提交一次这样既能控制内存占用又能定位到问题行段。import sqlite3 import csv conn sqlite3.connect(data/crad.db) cur conn.cursor() with open(data/hsr_lines_clean.csv, encodingutf-8) as f: reader csv.DictReader(f) for i, row in enumerate(reader, start1): try: cur.execute( INSERT INTO hsr_lines (line_name, start_city, end_city, length_km, design_speed, open_year) VALUES (?,?,?,?,?,?), (row[line_name], row[start_city], row[end_city], float(row[length_km]), int(row[design_speed]), int(row[open_year])) ) except Exception as e: print(frow {i}: {e}) conn.commit() conn.close()3.3 数据库索引与查询优化CRAD的数据量不算夸张几百万行顶天了。但如果你在查询时不加索引全表扫描的耗时还是很明显的尤其当你要反复做城市对的关联查询时。主键自带索引不用多说额外需要手动加索引的是我在业务查询里频繁用到的筛选字段。高铁站点表要按城市名查所以给city_name字段建索引航空航线表要按年份和起点城市查所以给year和origin_city建联合索引。建索引的SQL长这样CREATE INDEX idx_hsr_stops_city ON hsr_stops(city_name); CREATE INDEX idx_air_routes_year_origin ON air_routes(year, origin_city); CREATE INDEX idx_air_routes_cities ON air_routes(origin_city, dest_city);索引不是越多越好每建一个索引都会拖慢写入速度但我们的场景是读多写少所以把高频查询的字段全部索引化是非常划算的。实际体验下来加完这几个索引之后原来要等两三秒的跨表聚合查询现在基本一秒内就能出结果。我还为几个高频分析场景做了物化视图。SQLite本身不直接支持物化视图我的做法是预先跑好查询语句把结果存成一张单独的汇总表。比如“城市对年度班次汇总表”就是把所有原始数据按城市对和年份聚合后存起来做可视化的时候直接读这张表不用再跑一次全链路聚合响应速度完全不一样。3.4 数据库同步与备份策略单机数据库虽然简单但同步和备份的问题不能忽视。这个项目因为涉及多台电脑之间交换数据我一开始是直接复制数据库文件后来发现很容易出现版本冲突——改了这台电脑上的数据忘了同步到另一台再用的时候都不知道哪份是最新的。后面我做了一套简单的同步机制数据库文件名带日期版本号每次修改完毕通过网盘目录同步同时在库内加一张metadata表记录当前版本号和最后更新时间。用之前先查这张表能快速判断手上的版本是不是最新的。备份方面SQLite备份很简单——直接复制文件就行。但要注意在写入过程中直接复制可能会得到不一致的数据库副本所以最好用SQLite自带的备份API或者在程序里执行VACUUM INTO命令来生成安全备份。VACUUM INTO backup/crad_20240115.db;这条命令会把当前数据库的完整一致性快照写入指定文件中间不需要停服务也不影响正在执行的查询。我是每天晚上10点自动跑一次这个命令保留最近30天的备份季度性地把旧备份归档到移动硬盘。4. 典型应用场景与SQL实战4.1 学术研究高铁开通对航线的影响分析这个数据库最有代表性的应用场景就是研究高铁网络扩张对航空运输的影响。方法不算复杂核心思路是选一组“同时具备高铁和航空服务”的城市对对比高铁开通前后的航班量变化同时找一组“没有高铁直达”的城市对作为对照组。实际操作时第一步要查出所有存在高铁直达的城市对。利用hsr_lines和hsr_stops两张表关联把每条线路的起点和终点城市抽取出来即可。具体SQL可以这样写SELECT start_city AS city_a, end_city AS city_b, open_year FROM hsr_lines WHERE open_year IS NOT NULL;第二步是把这些城市对和航空航线表关联。比如我们要看2010年之前开通高铁的线路对沿线城市航空量的影响就可以先把2010年前的高铁城市对抽出来再和2010年的航空航线数据做差集或交集找出哪些航线在高铁开通后仍然保留、哪些被压缩甚至停飞。这个分析做下来得到的最典型结论是800公里以内的航线受高铁冲击最大航班量普遍呈下降趋势1500公里以上的航线受影响相对有限。这个结论虽然已经是行业共识但用CRAD这样的统一数据源去复现一遍对于学生做毕业论文或者想进入交通数据分析行业的工程师练手都是非常完美的入门实践。4.2 出行场景跨运输方式中转查询除了学术研究CRAD在实用的出行规划场景里也有用武之地。很多人出行时纠结坐高铁还是飞传统的做法是分别打开铁路12306和航班App去比对但在数据层面这个问题完全可以落成一条查询。比如你想知道2022年从西安到上海有多少种出行方案核心就是查两个数据源高铁线路表里有没有西安到上海的直达线路航空航线表里有没有对应航班。更进一步如果两地没有高铁直达你还可以用站点停靠表和城市对连通表去匹配是否有“一程高铁一程航班”的中转方案。在SQL层面这是一个典型的图式查询虽然SQLite对这个支持有限但通过多表JOIN还是能实现。具体思路是先把所有高铁可直达的城市对查出来再关联到需求城市对如果命中说明可以高铁直达如果没命中再去查询在某个中转城市换乘的可能性。这种查询写出来的SQL虽然有几层嵌套但一旦把表结构建好整个逻辑非常清晰。4.3 可视化展示的前置查询做数据可视化时CRAD的价值在于减少了大量数据预处理时间。不管是用Tableau、Power BI还是用Python的Plotly画图数据源基本都是从数据库直接查汇总结果。比如制作“2003-2022年中国高铁线路开通时间轴”的图表只需要查线路表里的开通年份和线路长度按年份分组求和即可SELECT open_year, COUNT(*) AS line_count, ROUND(SUM(length_km), 1) AS total_km FROM hsr_lines GROUP BY open_year ORDER BY open_year;再比如制作“各城市被高铁连接数排名”的地图则需要用站点停靠表按城市汇总SELECT city_name, COUNT(DISTINCT line_id) AS line_num FROM hsr_stops GROUP BY city_name ORDER BY line_num DESC;整个数据库的设计思路就是让查询结果尽可能接近最终展示形态让分析人员不需要再做二次加工。因为数据库字段事先做了城市归一化、年份统一、航线方向统一可视化阶段可以直接拖字段不会有脏数据突然跑出来毁掉图表的情况。5. 常见问题与排查技巧实录5.1 数据导入乱码与编码问题CRAD在分享给朋友用的过程中反馈最多的一个问题是CSV文件导入后在数据库里出现乱码。这个问题的根源几乎都是文件编码不一致。我最初导出的CSV文件用的是UTF-8编码但有些Windows环境下默认打开是GBK导致中文全部变成问号或者乱码。解决办法有两类。一是数据导出时统一带上UTF-8 BOM头这样Excel打开时能自动识别编码不会乱码。二是在导入脚本里强制指定编码参数不要依赖默认值with open(data/air_routes.csv, encodingutf-8-sig) as f: reader csv.DictReader(f)用了utf-8-sig之后麻烦基本就消失了。这里要提醒一下如果遇到的是Excel导出的文件大概率是GBK编码导入的时候用encodinggbk否则会报UnicodeDecodeError。这是数据导入阶段最常踩的坑。5.2 字段类型不一致导致JOIN失败第二个高频问题出现在多表关联时。比如hsr_lines表里的start_city存的是标准的城市名称“西安”而air_routes表里的origin_city在某几行数据里存的是“西安咸阳”或者“XIY”这样的话直接JOIN就会漏掉一批数据。解决思路是设计一个单独的城市字典表把所有机场三字码、城市全称、城市常用别名、行政区划代码全部映射到同一个标准城市ID。关联的时候先用城市字典表做一步转换把所有别名统一映射到标准名称然后再做业务表之间的关联。这个过程虽然多了一次查询但能彻底解决数据源不一致的老大难问题。如果你的数据库已经建好了也可以定期跑一遍容错检查找出那些不在城市字典表里的孤值再逐个分析是否要做映射补充。我一般每个月跑一次确保没有新冒出来的别名脏数据。5.3 大数据量查询变慢的排查思路有用户反馈查询某几年的全部航线数据时等了很久才出结果。这种情况先别急着换机器通常挨个排查下来问题都出在缺少索引或者查询写法没有命中索引上。先用EXPLAIN QUERY PLAN看一下SQLite实际执行的查询计划EXPLAIN QUERY PLAN SELECT * FROM air_routes WHERE origin_city 北京 AND year BETWEEN 2015 AND 2020;如果结果显示SCAN air_routes而不是SEARCH air_routes USING INDEX说明索引没有生效需要检查WHERE条件里的字段顺序是不是和联合索引一致。联合索引的生效前提是查询条件中最左前缀字段必须出现所以我把air_routes表的联合索引建成了(year, origin_city)而查询时如果只查城市不查年份就得单独再用城市索引这就是为什么我在上面建了两个索引。如果你的数据量已经到千万级以上SQLite可能就不太够用了这时候再考虑迁到PostgreSQL或者ClickHouse也不迟。但就CRAD目前这个量级做好索引设计SQLite完全能胜任。5.4 数据库同步工具与版本管理经验数据库的版本管理其实比很多人想象的重要。CRAD迭代了很多版如果每次都在原文件上改过段时间就会发现某个字段被覆盖了某个修复过的数据又在最新版里出现了。我的经验是借助简单的命名规范配合网盘同步类似crad_v2.3_20240115.db这种格式永远保留一个带版本号和日期的完整文件。如果有条件上git可以用Git LFS来管理数据库文件的版本但要注意数据库文件是二进制格式git的diff功能对它无效只能做到版本对比和回滚真正的内容差异还是得靠库内的metadata表记录。还有人问数据库同步软件怎么选。其实对于单人、单数据库这个规模网盘同步已经够用。如果是团队机制需要多人同时写入那就老老实实换PostgreSQL再配一套像Navicat的团队协作功能或者定制的同步任务。一切从数据规模和团队规模出发不要一上来就用重方案反而是给自己增加维护成本。5.5 常见问题速查表我根据实际使用情况整理了下面这张速查表方便你快速定位问题现象可能原因排查与解决导入CSV后中文乱码文件编码不匹配统一用utf-8-sig读取或用gbk重试同一条线路出现重复记录唯一索引未生效或数据源重复检查UNIQUE约束按线路名和年份去重关联查询漏数据城市名称不一致用城市字典表做别名映射查询速度慢缺少索引或未命中索引运行EXPLAIN QUERY PLAN检查执行计划字段是文本格式无法计算建表时字段类型错误重建表时用INTEGER/REAL避免用VARCHAR存数字备份文件损坏不可用直接复制正在写入的db文件改用VACUUM INTO做一致性备份6. 后续扩展方向与个人实操体会CRAD发展到2022年版已经具备一个成熟研究型数据库的基本素质。但数据类项目永远不会真正完结后续可以扩展的方向至少还有三个一是把运营数据从年度细化到月度甚至日度满足更精细的时间序列分析二是加入票价维度高铁票价和机票价格都能抓取到这样就能做价格弹性研究三是把对外接口做出来比如提供RESTful API让其他人不用下载整个数据库文件直接通过接口按条件查询数据。在实际使用中我还有一个体会——数据库的价值不止在数据本身更在于你为了整理它而建立的那套清洗与校验流程。CRAD建库过程中用到的城市别名映射表、去重策略、编码规范单独拿出来都可以复用到任何其他交通数据集上。如果你也想做类似的方向建议从一个小范围开始比如先整理某一个省份的高铁线路数据跑通整个流程后再逐步扩展。一开始就想着把所有数据全部覆盖大概率会在半路因为清洗问题而放弃。最后分享一个小技巧不管是做数据分析还是写论文拿到CRAD这类数据库第一件事不是急着写复杂查询而是先花10分钟浏览每张表的内容和统计信息确认字段含义、数据范围、缺失值情况。这10分钟看着是浪费实际上能帮你少走很多弯路——我见过不少人拿着数据就开始跑回归模型跑出来结果极其诡异回头一查是年份字段混入了文本格式导致的。先摸清数据底细再谈分析这是所有数据项目里最值得坚持的习惯。
