【超详细】PostgreSQL+PostGIS 出租车轨迹数据处理与OD分析全流程

hyx_H

400人浏览 · 2026-06-01 09:19:51

hyx_H · 2026-06-01 09:19:51 发布

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

文章标题

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

关键词

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

分类

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

正文（直接复制发布）

一、前言

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

二、实验目的

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