【超详细】PostgreSQL+PostGIS 出租车轨迹数据处理与OD分析全流程（含踩坑总结｜可直接发CSDN）

---

文章标题

PostgreSQL + PostGIS 出租车轨迹数据管理、OD挖掘与空间可视化全流程实战（实验报告+踩坑总结）

关键词

PostGIS、PostgreSQL、出租车轨迹、OD分析、交通小区、QGIS可视化、空间索引、实验报告

分类

数据库 -> PostgreSQL
GIS/遥感 -> GIS开发
编程语言 -> SQL

---

正文（直接复制发布）

一、前言

本文基于高校《地理数据分析与建模》实验课程完整复现，从原始GPS数据入库、轨迹分段、OD生成、交通小区匹配、OD矩阵到QGIS可视化，全程可直接运行、所有报错一次性解决。适合GIS、交通工程、大数据、地信专业学生做实验、课程设计、毕业设计参考。

二、实验目的

1. 掌握出租车GPS原始数据清洗、导入、时空字段解译标准流程。
2. 学会使用窗口函数实现同一车辆、同一运营状态的连续轨迹分段。
3. 生成出租车载客订单OD表，包含起止时间、坐标、空间几何。
4. 实现订单OD点与交通小区（TAZ）空间匹配，统计小区间OD流量。
5. 掌握GiST空间索引创建方法，大幅提升空间查询效率。
6. 导出流量表并在Excel生成OD矩阵透视表。
7. 在QGIS完成轨迹、OD流、小区流量分级可视化。
8. 构建带时间戳的LineStringM时空轨迹。

三、实验环境

• 数据库：PostgreSQL 14 + PostGIS 3.x
• 数据管理工具：DBeaver
• 空间可视化工具：QGIS 3.x
• 数据：北京市出租车GPS打点数据、交通小区TAZ矢量数据
• 操作系统：Windows 10/11

四、数据说明

• 车辆编号：vehiclenum
• Unix时间戳：point_time_int
• 纬度lat、经度lon：整型，需 /100000.0 转为真实经纬度
• 运营状态status：
  • 10000000：载客
  • 0：空驶
  • 989681：停运

五、完整实验步骤（可直接复制SQL）

5.1 创建模式与数据表

CREATE SCHEMA IF NOT EXISTS taxi;

CREATE TABLE taxi.track_points (
    vehiclenum VARCHAR,
    point_time_int BIGINT,
    lat INTEGER,
    lon INTEGER,
    status INTEGER
);

CREATE EXTENSION IF NOT EXISTS postgis;

5.2 数据导入 + 时空字段解译

COPY taxi.track_points 
FROM '文件路径/20160621_new.txt' 
DELIMITER ',' CSV;

ALTER TABLE taxi.track_points ADD point_time TIMESTAMP WITH TIME ZONE;
ALTER TABLE taxi.track_points ADD geom geometry;

UPDATE taxi.track_points
SET 
    point_time = to_timestamp(point_time_int),
    geom = ST_SetSRID(ST_MakePoint(lon/100000.0, lat/100000.0), 4326);

5.3 连续轨迹分段 → 生成OD表（核心）

CREATE TABLE taxi.track_od AS
WITH merged_records AS (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY vehiclenum ORDER BY point_time)
    - ROW_NUMBER() OVER (PARTITION BY vehiclenum, status ORDER BY point_time) AS group_id
    FROM taxi.track_points
),
continuous_points AS (
    SELECT 
        vehiclenum, status, group_id,
        MIN(point_time) AS start_time,
        MAX(point_time) AS end_time,
        (ARRAY_AGG(lon ORDER BY point_time))[1] AS start_lon,
        (ARRAY_AGG(lat ORDER BY point_time))[1] AS start_lat,
        (ARRAY_AGG(lon ORDER BY point_time DESC))[1] AS end_lon,
        (ARRAY_AGG(lat ORDER BY point_time DESC))[1] AS end_lat
    FROM merged_records
    WHERE status = 10000000
    GROUP BY vehiclenum, status, group_id
)
SELECT 
    vehiclenum, start_time, end_time, 
    start_lon, start_lat, end_lon, end_lat, status,
    ST_SetSRID(ST_Point(start_lon/100000.0, start_lat/100000.0), 4326) AS start_point,
    ST_SetSRID(ST_Point(end_lon/100000.0, end_lat/100000.0), 4326) AS end_point,
    ST_SetSRID(ST_MakeLine(
        ST_Point(start_lon/100000.0, start_lat/100000.0),
        ST_Point(end_lon/100000.0, end_lat/100000.0)
    ), 4326) AS od_flow
FROM continuous_points;

5.4 创建空间索引（提速关键）

CREATE INDEX taz_idx ON taxi.tazbeijing USING gist(geom);
CREATE INDEX start_point_idx ON taxi.track_od USING gist(start_point);
CREATE INDEX end_point_idx ON taxi.track_od USING gist(end_point);

5.5 交通小区OD流量统计

SELECT 
    taz1.tazid AS staz, 
    taz2.tazid AS etaz, 
    COUNT(*) AS num
INTO taxi.taz_flow_count
FROM 
    taxi.tazbeijing taz1, 
    taxi.tazbeijing taz2, 
    taxi.track_od tax
WHERE 
    ST_Contains(taz1.geom, tax.start_point)
    AND 
    ST_Contains(taz2.geom, tax.end_point)
GROUP BY staz, etaz
ORDER BY staz, etaz;

5.6 时空轨迹构建

CREATE TABLE taxi.track_trajectory (
    vehiclenum VARCHAR, 
    trajectory GEOMETRY
);

INSERT INTO taxi.track_trajectory (vehiclenum, trajectory)
SELECT 
    vehiclenum,
    ST_MakeLine(ST_MakePointM(lon/100000.0, lat/100000.0, point_time_int) ORDER BY point_time_int)
FROM (
    SELECT DISTINCT ON (vehiclenum, point_time_int) 
        vehiclenum, point_time_int, lon, lat
    FROM taxi.track_points
    WHERE vehiclenum IN ('车辆1','车辆2')
) s1
GROUP BY vehiclenum;

5.7 QGIS可视化步骤

1. 加载交通小区 tazbeijing
2. 加载 start_point、end_point、od_flow
3. 按 status 分类渲染（0空载、10000000载客）
4. 以 tazid=97（天安门）做OD流量分级图

六、实验结果

1. 完成5111万条GPS数据入库。
2. 时间与几何字段解析正确。
3. 生成约34.6万条载客订单OD数据。
4. 完成交通小区OD匹配，生成标准流量表。
5. 空间索引使查询从13分钟降至16秒。
6. Excel透视表生成标准OD矩阵。
7. 轨迹、OD流、小区流量可视化正常。
8. 时空轨迹 LineStringM 构建并验证有效。

七、实验所有问题 + 解决方案（重点）

1. 报错：syntax error at or near “(”

原因：两个 ROW_NUMBER() 之间少了减号 -
解决：必须写 rn1 - rn2 AS group_id

2. 坐标错误：10^5 结果不对

原因：PostgreSQL 中 ^ 是异或运算
解决：统一使用 /100000.0

3. crosstab 函数不存在

原因：未启用 tablefunc
解决：CREATE EXTENSION IF NOT EXISTS tablefunc;

4. 类型不匹配 smallint vs int4

原因：staz 类型为 smallint
解决：定义改为 staz smallint

5. Excel 透视表看不到 etaz 字段

原因：导出了转置矩阵，不是三元组
解决：导出 staz,etaz,num 再做透视表

6. 空间查询极慢

原因：未建索引
解决：对 geom、start_point、end_point 建 GiST 索引

7. 表不存在 tazbeijing

原因：实际表名是 taz2010
解决：替换为真实表名

8. QGIS 无法加载轨迹

原因：LineStringM QGIS不支持
解决：ST_Force2D(trajectory) 转二维

9. 导出表无 etaz 字段

原因：crosstab 把 etaz 变成列名
解决：导出原始三元组表

八、实验总结

本次实验完整实现了出租车轨迹从原始数据→数据库→空间分析→OD挖掘→可视化全流程，覆盖：

• 时空数据建库
• 连续轨迹分段
• OD流生成
• 空间匹配与索引优化
• OD矩阵制作
• QGIS专题制图
• 时空轨迹构建