CRAD数据库：打通高铁与航线的20年交通时空数据

做交通数据分析的朋友应该都有过这种体验：想研究高铁对民航的冲击，翻遍全网找不到一份能直接用的长时序航班与列车对照数据；想算城市之间的出行可达性，要么自己爬去哪儿、12306，要么手搓正则从PDF班表里抠信息。累，且不可复现。CRAD（China 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也完全够，具体看你的分析场景。

核心表我用的是五张表加一个视图：

od_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，也保留行记录，用 frequency=0 表示“曾经有服务但当年无班次”。这就避免了“消失的航线”被静默删除的问题。

注意：清洗阶段最忌讳的就是“把数据洗成自己想要的结论”。我只做格式统一和实体对齐，绝不做“看起来异常就删除”的操作。异常值在分析阶段处理，清洗阶段只负责把事实准确转化为结构化记录。

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年，效率最高。这套数据后续我还想补进票价、旅行速度分位数和中转衔接数据，把“最快路径”和“最少换乘”也做进去，让这套库在交通规划里的适配面更宽广。