做交通数据分析的朋友应该都有过这种体验想研究高铁对民航的冲击翻遍全网找不到一份能直接用的长时序航班与列车对照数据想算城市之间的出行可达性要么自己爬去哪儿、12306要么手搓正则从PDF班表里抠信息。累且不可复现。CRADChina Railway-Air Database中国高铁航线数据库就是奔着解决这个痛点来的。它覆盖2003至2022年整整二十年的高铁线路与国内航线数据把两个网络叠在同一个地理坐标系下让“高铁vs航空”这类分析从立项到出图的时间压缩到一个下午。这篇博文我会从数据模型设计、采集清洗、建库导入、应用场景和排查心得五个维度拆开讲把我在从零攒这套库的过程中踩过的坑、觉得“早该有人写出来”的细节全部摊开说。适合正在做交通网络分析的研究生、做城市与区域规划的从业者以及任何想拿一套干净数据快速验证“高铁开了之后航线还剩多少客流”这类问题的朋友。1. 项目概述CRAD到底在做什么1.1 为什么会有这个数据库先回答一个最直接的问题网上不是有航班数据、铁路数据、城市坐标数据吗为什么还要专门做一套CRAD原因是——它们不在同一个“坐标系”里。航线和铁路线路看起来都是“从A到B”但二者在数据结构上天然不对齐。航班数据通常是航班计划表包含航班号、起飞机场、降落机场、起飞时刻、执飞机型、每周班期铁路数据则是列车车次表包含车次、途经站、到发时刻、编组。机场有IATA三字码PEK、SHA这类高铁站虽然也有站代码但那套代码跟机场三字码完全不是一个体系。更麻烦的是地理坐标机场通常在城市边缘高铁站多数在城区做可达性分析时如果只用“城市中心点”代替误差能到几十公里。CRAD的核心思路就是把这两套数据统一成“城市对OD—交通方式—时序”的结构。每一条记录都明确标注这是哪两个城市之间、什么交通方式、哪一年、每天多少班、耗时多久、距离多少。这样研究者拿到手的不是一堆散点而是一张可以直接透视的宽表透视维度是年份、城市对、方式。1.2 谁适合用这个数据库从实际需求看有三类人最用得着。第一类是学术研究者。做高铁对民航替代效应、区域可达性演化、交通网络韧性这类课题过去最痛苦的环节就是数据整理有的课题组甚至花一整年去手工录入铁路时刻表。CRAD把2003到2022年的数据做成连续序列起码能把“数据准备”这个环节从几个月压缩到几小时。第二类是规划咨询从业者。交通规划、城市规划项目里经常要回答“这个城市通达性怎么样”“高铁网络扩展后哪些城市受益最大”这套库提供了现成的网络拓扑和OD班次数据配合ArcGIS或QGIS可以直接做可视化与指标计算。第三类是数据库技术爱好者。CRAD本身就是一个结构完整、覆盖长时序、包含空间属性的真实数据集拿来做数据库设计练习、查询优化试验、可视化展示都比用那种只有几十行的示例表过瘾得多。我认识的不少朋友就用它来练PostgreSQL的窗口函数和空间索引。2. 数据模型设计从“记录”到“网络”的核心思路2.1 高铁与航线的数据结构差异设计CRAD之前我先把高铁和航线的原始形态彻底拆了一遍。航线数据最干净一条航线就是一组“机场—机场”的稳定连接即便航班时刻随航季变化航线本身通常是连续存在的。高铁数据则完全不同一条高铁线路可能经过十几个站G字头列车有标杆车只停大站和站站停两种形态同一城市还有多个高铁站。如果不做抽象直接把“北京南—上海虹桥”和“北京南—南京南—上海虹桥”当成两条独立线路统计口径就乱了。所以我在模型里采用了“分层结构”第一层是实体层城市、站点/机场、线路铁路线或航线代号。第二层是OD层把具体车次/航班拆解成“城市对连接”同一趟列车经过的相邻城市对形成一条OD链。第三层是时序层以年份为单位记录每个OD对在不同年份的班次数量、运行时长、是否有直达服务。这个设计在实操里最大的好处是灵活。想算“北京到上海每天多少班高铁”直接查OD层想算“京沪通道总耗时演化”按年份聚合即可想画网络拓扑图把OD层按年份导出成边表就行。2.2 数据库选型与关系建模CRAD的底层存储我最终选了PostgreSQL没有用MySQL原因有三个一是PostgreSQL对空间数据的支持更完整PostGIS可以做点线面的空间索引和距离计算二是窗口函数、CTE递归查询在数据处理阶段极其顺手三是对JSON字段的处理能力让元数据扩展更灵活。如果只是个人用SQLite也完全够具体看你的分析场景。核心表我用的是五张表加一个视图表名职责关键字段cities城市主表city_id, city_name, province, lng, latstations站点/机场表station_id, station_name, city_id, type, lng, latroutes线路主表route_id, route_name, mode, via_stationsod_pairs城市对OD表od_id, origin_city, dest_city, distanceyearly_stats年度聚合表od_id, year, mode, frequency, travel_timeod_pairs 和 yearly_stats 是查询主力。od_pairs 保存的是“这个OD对是否存在、空间距离多少”yearly_stats 保存的是“这一个OD对在某个年份有多少班次”。两张表联查就能得到“任意年份任意城市对的交通供给强度”。视图方面我预留了一个 v_od_with_name把 city_id 关联成城市名方便直接导出给BI工具或者R/Python做可视化。提示城市对OD表里我坚持用 city_id 而不是 station_id 做主键是为了避免“北京南—上海虹桥”和“北京—上海”这种粒度混乱的问题。城市粒度天然统一了两种交通方式的可比性。3. 数据采集与清洗实操20年的数据怎么攒出来3.1 数据来源与采集策略CRAD覆盖2003到2022年这个时间跨度本身就决定了没有一个单一公开源能一口喂饱。我采用了多源交叉补全的方式航线数据以民航局每年公布的国内航班计划为基础结合航班时刻查询平台做交叉验证。2003到2010年的历史航班数据很多已从公开网站下线需要用Wayback Machine把这些早期航季的页面抓下来解析。高铁数据2008年之前国内没有高铁这条线的起点是2003年前期是普速为主严格说CRAD前几年数据主要是既有线提速列车2008年京津城际开通后才真正有“高铁”概念。2008年之后的铁路数据主要通过官方时刻表、铁路客户服务网站的历史页面、以及多个票务平台的历史查询记录来收集。地理与距离数据城市坐标用公开地理编码服务批量获取OD距离采用球面距离公式计算高铁线路里程则参考线路设计里程和实际轨道里程公报。这套多源策略说起来简单做起来最花时间的是“对齐”。每一个来源的字段命名方式都不同有的给机场三字码有的给中文名有的给城市名。我写了两个阶段的清洗脚本第一阶段做字段标准化第二阶段做实体对齐。3.2 清洗与去重的几个坑第一个坑是站名/机场名的同义不同写。北京首都机场在历史数据里有“北京首都”“首都机场”“PEK”三种写法高铁站则有“北京南”“北京南站”“北京南火车站”三种写法。清洗时必须建一张“别名映射表”把同义实体映射到统一ID否则聚合统计会莫名翻倍。第二个坑是航线与列车班次会定期调整。民航有冬春航季和夏秋航季一年两个航季航班时刻不同铁路每年也有多次调图。CRAD的“年度”统计口径是怎么定的我按“每年5月或10月的大调图时点”作为当年的快照时刻把该时点的班次数据作为当年基准。这样虽然损失了年内动态变化但换来的是跨年份可比性对长时序研究来说值得。第三个坑是停运线路的时效性。一条航线2020年还在2021年因为市场原因停航如果只收集“当前存在的航线”就会漏掉这些历史记录。CRAD的做法是每次都保留完整快照即使某OD在某年班次为0也保留行记录用 frequency0 表示“曾经有服务但当年无班次”。这就避免了“消失的航线”被静默删除的问题。注意清洗阶段最忌讳的就是“把数据洗成自己想要的结论”。我只做格式统一和实体对齐绝不做“看起来异常就删除”的操作。异常值在分析阶段处理清洗阶段只负责把事实准确转化为结构化记录。4. 建库建表与导入实战从CSV到可查询的数据库4.1 核心表结构SQL参考这里我把CRAD建库时的核心表结构简化后放出来你可以直接用也可以根据自己的分析场景修改。以下SQL在PostgreSQL 13以上版本测试通过。-- 城市表 CREATE TABLE cities ( city_id INTEGER PRIMARY KEY, city_name VARCHAR(100) NOT NULL, province VARCHAR(100), lng NUMERIC(9,6), lat NUMERIC(9,6) ); -- 站点/机场表 CREATE TABLE stations ( station_id INTEGER PRIMARY KEY, station_name VARCHAR(150) NOT NULL, city_id INTEGER REFERENCES cities(city_id), type VARCHAR(10) CHECK (type IN (rail, air)), lng NUMERIC(9,6), lat NUMERIC(9,6) ); -- 城市对OD表 CREATE TABLE od_pairs ( od_id INTEGER PRIMARY KEY, origin_city INTEGER REFERENCES cities(city_id), dest_city INTEGER REFERENCES cities(city_id), distance_km NUMERIC(10,2), UNIQUE (origin_city, dest_city) ); -- 年度聚合表 CREATE TABLE yearly_stats ( od_id INTEGER REFERENCES od_pairs(od_id), year INTEGER CHECK (year BETWEEN 2003 AND 2022), mode VARCHAR(10) CHECK (mode IN (rail, air)), frequency INTEGER, travel_min INTEGER, PRIMARY KEY (od_id, year, mode) ); -- 常规联查视图 CREATE VIEW v_od_with_name AS SELECT o.od_id, oc.city_name AS origin_city, dc.city_name AS dest_city, o.distance_km, y.year, y.mode, y.frequency, y.travel_min FROM od_pairs o JOIN cities oc ON o.origin_city oc.city_id JOIN cities dc ON o.dest_city dc.city_id JOIN yearly_stats y ON o.od_id y.od_id;这个结构里最值得琢磨的是 yearly_stats 用了组合主键 (od_id, year, mode)。为什么不用自增ID因为这张表本质上是“事实表”同一OD对同一年的不同交通方式各占一行天然唯一用组合主键既能防止重复导入又让查询直接走索引。4.2 数据导入流程与性能优化从原始CSV导入PostgreSQL我踩过的最大坑是逐条INSERT几百万行数据插到天荒地老。后来改用 COPY 命令速度提升了两个数量级。核心流程如下# 1. 先把清洗后的CSV放到服务器可访问目录 # 2. 用COPY批量导入 COPY cities(city_id, city_name, province, lng, lat) FROM /path/to/cities.csv DELIMITER , CSV HEADER; COPY yearly_stats(od_id, year, mode, frequency, travel_min) FROM /path/to/yearly_stats.csv DELIMITER , CSV HEADER;如果数据量实在太大还可以先导入到无索引的临时表再 INSERT INTO ... SELECT 转写到正式表最后统一建索引。这种方式比带索引导入快很多因为避免了每插入一行就更新一次B树。注意导入之前一定要先跑一次完整性检查重点看外键是否能对上。我曾因为CSV里混入了几行没有对应 city_id 的记录导致导入失败排查了半天。稳妥的办法是先导入维度表cities、stations、od_pairs再导入事实表yearly_stats最后用一条LEFT JOIN查空值。导入完成之后建议立刻做三件事给 yearly_stats 建索引、执行 ANALYZE 更新统计信息、跑一条“抽数对比”验证导入无误。索引方面od_id 是外键year 是维度字段两个都要建索引CREATE INDEX idx_yearly_od ON yearly_stats(od_id); CREATE INDEX idx_yearly_year ON yearly_stats(year);5. 典型应用场景CRAD能回答什么问题5.1 高铁开通后航线的“生存曲线”这套数据最经典的应用就是看高铁对航线的替代效应。拿京沪通道举例北京—上海航线在2003到2010年之间是国内最繁忙的航线之一但2011年京沪高铁开通后通道内航空班次并没有立刻暴跌因为高铁全程最快也要4小时48分对商务旅客吸引力有限。真正变化出现在2017年之后复兴号把京沪运行时间压到4小时18分加上准点率优势航空班次才开始明显下滑。用CRAD你可以直接跑这段查询把京沪通道20年的两种方式班次变化拉出来SELECT y.year, y.mode, y.frequency FROM yearly_stats y JOIN od_pairs o ON y.od_id o.od_id JOIN cities c1 ON o.origin_city c1.city_id JOIN cities c2 ON o.dest_city c2.city_id WHERE (c1.city_name 北京 AND c2.city_name 上海) OR (c1.city_name 上海 AND c2.city_name 北京) ORDER BY y.year, y.mode;结果会非常直观地呈现两条曲线的交叉点。再配合 distance_km 和 travel_min还可以算“单位里程耗时”这个指标去解释为什么某些线路高铁替代效应强、某些弱。1000公里以内高铁优势明显1500公里以上航空仍然坚挺这些结论都能从数据里自然浮现出来。5.2 城市对可达性与网络韧性分析CRAD不止能做“一条线”的分析它最大的价值是能升级成全网络的视角。我做过一个“任意两座城市之间的年度可达性变化”分析核心指标是如果我要从A城到B城在当天上午8点到下午6点之间出发最快的方式需要多久。这个指标需要对 yearly_stats 里的 frequency 和 travel_min 做组合计算。实际上直接用 CRAD 的OD表和年度班次表就能算出一个“班次供给指数”数值等于“该OD对全年总班次 ÷ 全年可服务小时数”反映出行的密集程度。网络韧性分析则是另一个方向假设某个枢纽城市比如武汉、郑州这种铁路枢纽的高铁站停运一周整个网络有多少城市对的连接会中断或绕行这种模拟需要把OD数据转成图结构跑复杂网络分析。CRAD的数据结构天然适合转化成这种图OD表就是边表年份就是时间戳用NetworkX或者igraph读入后直接可以算网络直径、连通分量、介数中心性。从我做过的试验看中国高铁网络的鲁棒性极高因为长三角、珠三角、成渝这几个城市群内部形成了多路径环网单一节点失效对全局连通性影响有限但会对途经该枢纽的长距离OD造成显著的时间惩罚。这种结论如果从零开始采集数据至少需要半年站在CRAD基础上一个下午就能跑完。5.3 与其他开放数据源联合分析CRAD单独用已经能回答不少问题但我实际使用中更喜欢把它和另外两类数据结合。一类是人口栅格数据如WorldPop或LandScan可以算“高铁网络覆盖了多少人口”“某条新线开通后覆盖人口增加了多少”另一类是夜间灯光数据用来做区域经济活力的代理变量然后探讨“高铁通达性提升是否伴随着经济活动的空间重组”。联合分析时要留意CRAD的OD粒度和栅格数据空间粒度之间的匹配。CRAD是城市级别的OD人口栅格是1公里级别处理方式是以城市点为圆心做缓冲区把缓冲区内的栅格值聚合到城市。这一步听上去简单实则容易踩坑城市辖区范围往往很大直接按行政区边界聚合比按圆形缓冲区更准确。6. 常见问题排查与避坑速查6.1 重复数据与主键冲突最典型的导入报错就是主键冲突。用COPY导入 yearly_stats 时如果CSV里同一个 (od_id, year, mode) 出现了两行直接导入会失败。解决方法有两个一是导入前用 DISTINCT 去重二是导入后定期跑一遍分组统计把重复项揪出来。我更推荐后者因为有些重复来自原始数据源本身直接剔除会丢信息应当先检查重复记录的具体内容再决定保留哪一条。6.2 航季与调图的口径问题每年有两个航季和多次铁路调图CRAD的年度快照不能反映年内全部变化。如果你研究的问题是“2020年疫情期间航线周度变化”CRAD的年度粒度就用不上需要回到原始班期数据。所以使用前务必明确粒度CRAD适合中长时序的年度趋势分析不适合高频短期波动研究。6.3 导入性能慢的排查思路如果COPY导入慢按优先级排查磁盘IO是否跑满、是否在目标表上有大量索引、CSV是否有大量空字段。最快的优化方式是先导入到无索引的临时表转写后再建索引。PostgreSQL配置方面把 maintenance_work_mem 调高一点创建索引会明显加快。6.4 坐标偏移与距离校验如果计算OD距离时出现“北京到上海300公里”这种离谱值多半是经纬度单位错了或者拿站点坐标和城市坐标混用了。CRAD的 distance_km 字段是城市坐标算出来的球面距离不是实际铁路里程。实际铁路里程通常比球面距离长10%到30%因为线路不可能直线。做时间可达性分析时用 travel_min 字段就够了做运输经济学分析时如果要算票价收入、单位里程成本务必用实际里程不要拿球面距离硬套。注意我见过不少人在论文里把球面距离当作铁路里程用这是很常见的坑。CRAD里 distance_km 明确定义为球面距离你有实际里程需求时可以拿 od_id 关联铁路线路表从线路设计里程表里匹配真实里程。7. 实操中我认为最值得记住的几点CRAD从想法到跑通前后经历了数据收集、清洗、建模、导入、验证五轮循环每一轮都在推翻之前的一些假设。我个人体会最深的是做长时序交通数据库稳定性比丰富性更重要。早期我也试图把所有机场、所有高铁站、所有车次都塞进一个表里后来发现这样只会把主键搞得臃肿、查询越写越复杂。拆成 cities、stations、od_pairs、yearly_stats 之后所有常见分析都变得顺手了。如果你也打算做类似的数据库最后分享几个小建议。第一保留原始数据快照不要只保留清洗后的结果否则后续口径调整时无法追溯。第二给数据文件加版本号CRAD的CSV文件名类似 crad_2022_v1.3.csv版本管理救命。第三别追求一次到位先拿一个年份跑通全流程再批量扩展到20年效率最高。这套数据后续我还想补进票价、旅行速度分位数和中转衔接数据把“最快路径”和“最少换乘”也做进去让这套库在交通规划里的适配面更宽广。