简介这是一份《时空数据库》主题的PPT教学文档共20页面向高校数据库课程学习者、研究者以及从事位置服务、交通监控等方向的技术人员。资源系统讲解了时空数据库的缘起与发展从无线定位与移动对象管理需求出发说明空间数据库与时态数据库从独立走向融合的过程随后界定时空对象、连续/离散时空变化等核心术语在主要研究内容部分重点介绍了时空数据建模的多种方式扩展现有概念模型、基于属性或位置建模等、面向过去/现在/未来的索引策略以及窗口查询、运动对象最近邻查询、TP查询和LB查询等典型查询方法最后归纳了时空数据库在交通控制、气象监测、移动计算中的应用场景。资源为1个pptx文件压缩包仅621KB轻量精炼。已有290人学习浏览适合作为教学课件或快速了解时空数据库技术体系的入门资料。1. 一句话说清时空数据库为什么加了经纬度索引还是慢我第一次把时空数据库做成方案PPT时文件名就叫《1时空数据库.pptx》。背后的痛点非常具体车联网的GPS轨迹表刚过千万行一段“查某辆车最近30天在某个坐标2公里内出现多少次”的SQL从几十毫秒一路恶化到几十秒。技术团队的第一反应通常是给经纬度字段建联合索引但B树在二维空间上无法同时完成“经度在区间A且纬度在区间B”的高效裁剪最终只能退化成全表扫描。时空数据库把“什么时间、在哪个位置”作为一等公民来处理通过空间索引和时间分区的组合让这类查询重新回到索引扫描的轨道上。它适合有轨迹、订单点、IoT定位历史、态势回放这类强时空属性的团队也适合正准备做技术选型但不想走弯路的人。2. 时空数据库选型前的技术底牌索引模型与这四种方案2.1 为什么B树拿二维坐标没办法传统关系型数据库的索引核心是B树它擅长处理一维有序数据。你可以通过复合索引(lng, lat, ts)把三个字段排成有序结构但查询要同时满足“经度在一个范围、纬度在另一个范围、时间在一个范围”时B树只能挑其中一个维度作为主顺序。假设索引按经度为主序那么查询会先从经度范围中取出一长串记录再逐个比对纬度和时间。当经纬度范围稍微放大一点这个“候选集合”就会变得非常大实际执行效果和全表扫描差距不大。这个问题的本质是二维空间没有全序关系。你可以在飞机上把所有点按经度排序但纬度方向的信息就丢了。反过来也一样。所以很多 MySQL 场景里“加了经纬度索引还是很慢”并不是索引失效而是索引结构本身就不适配二维裁剪。做时空数据库选型时第一件事就是记住我们需要的是专门的空间索引结构而不是把经纬度塞进 B树。2.2 空间索引的四种主流玩法目前业界常见的空间索引方案可以归为四类理解它们才能在选型时不踩坑。第一类是 R 树及其变种PostGIS 的 GiST 索引就是典型代表。它的思路是把相近的几何对象用一个最小外接矩形包起来索引节点上记录的是这些矩形的层级关系。查询时先快速判断哪些矩形和目标区域相交再下钻到具体对象。由于矩形相交测试很快它很适合“2公里内圈选”“多边形裁剪”这类真实几何关系判断。第二类是 GeoHash 编码MongoDB 和 Elasticsearch 这类系统常用它做地理检索。GeoHash 把经纬度交错编码成一串字符串两个字符串前缀相同的长度越长说明两个点靠近。它的好处是能把二维坐标降成一维直接利用字符串索引。但边界问题非常明显两个距离很近的点可能落在不同前缀的格子里后面会单独讲这个坑。第三类是空间填充曲线比如 Z-order、Hilbert 曲线。GeoMesa 在 HBase 上做时空索引就是这种思路目的是把二维坐标转成一维自然数按 rowkey 范围扫描。它比 GeoHash 更适合作分布式系统的底层支撑因为 HBase 本身只擅长按 rowkey 顺序扫描。第四类是网格聚合ClickHouse 这类分析型数据库常用。把所有点按固定的经纬度步长归入网格查询时先聚合网格统计再做二次分析。它牺牲了精确的几何判断但换来了极高的聚合速度和吞吐适合热力图这种不需要精确边界的场景。2.3 选型对比PostGIS / MongoDB / ClickHouse / Doris我刚做时空库选型时列过一张对比表后来在好几个项目里调整过现在保留的版本大致是下面这样方案核心索引模型擅长的事主要限制PostGISGiST / R树变种精确空间关系、丰富函数、生态成熟分布式能力弱写入需结合分区MongoDB2dsphere / GeoHash设备点写入、水平扩展复杂几何分析函数少ClickHouse分区 网格预聚合大规模聚合、轨迹统计复杂几何判定弱DorisST_* 函数 Bitmap 索引实时数仓内做空间检索几何分析能力有限我一般会这样判断如果数据量在千万到亿级且需要做距离、相交、缓冲区这些精确计算优先考虑 PostGIS。它的函数覆盖最全面很多空间分析可以在数据库内直接完成不用把数据导出来喂给算法。也就是说方案落地成本最低。如果设备点数据量很大而且业务主要就是“存下来、偶尔圈个范围看看”对精确空间关系要求不高MongoDB 的 2dsphere 就够用写入扩展也更省心。如果核心诉求是“实时统计全城热力分布”ClickHouse 反而更合适因为它本质上是把空间查询转换成了聚合查询走的是并行扫描和预聚合而不是传统索引。Doris 的情况类似如果你团队已经重度使用 Doris业务上也只需要按经纬度范围过滤和简单统计可以先用它的 ST 函数不必为了一个常规查询引入新的存储系统。我个人最推荐的中小型方案是 PostGIS。还有一个很现实的理由PostGIS 建立在 PostgreSQL 上序列、窗口函数、JSON 这些能力都能复用团队学习成本低出了问题能找到的案例和资料也最多。3. PostGIS落地轨迹存储从建表到两公里圈选的最小方案3.1 环境准备与GIS插件初始化本地验证时空数据库最快的方式是直接在 Docker 里跑一个带 PostGIS 的镜像。常见做法是拉取postgis/postgis镜像它已经把 PostgreSQL 和 PostGIS 插件打包好了启动后连插件都不用单独装。docker run -d --name pgis \ -e POSTGRES_USERgis \ -e POSTGRES_PASSWORDgis123 \ -e POSTGRES_DBspatial \ -p 5432:5432 \ postgis/postgis:latest启动完成后用任意 PostgreSQL 客户端连到spatial库执行下面这条命令确认插件可用CREATE EXTENSION IF NOT EXISTS postgis; SELECT postgis_version();第一条命令创建 PostGIS 扩展第二条命令会返回版本号比如 3.4 之类的。生产环境我建议锁定具体镜像 tag不要长期使用latest否则 PostgreSQL 大版本升级时可能带来兼容性意外。这个教训是很多线上翻车现场换来的。3.2 建一张带时空语义的表业务里最典型的一张表是轨迹点表至少包含设备标识、坐标点、采集时间三个字段。建表时我建议直接把坐标列定义为geography(Point, 4326)类型而不是传统的geometry。CREATE TABLE t_track ( id BIGSERIAL PRIMARY KEY, device_id VARCHAR(32) NOT NULL, point GEOGRAPHY(Point, 4326) NOT NULL, ts TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_t_track_geom ON t_track USING GIST (point); CREATE INDEX idx_t_track_ts ON t_track (ts);这里4326是 WGS84 经纬度坐标系GPS 采集的原始坐标就是它。使用geography类型的关键收益是所有距离函数的单位自动变为米不用每次查询都把度估算成米避免那类“以为在附近实际差了几十公里”的问题。代价是geography支持的空间函数数量比geometry少一些CPU 开销略高但对轨迹距离类应用完全值得。索引方面GIST索引服务空间查询btree索引服务时间范围查询。两条索引分开建查询时优化器可以分别用索引获取候选集再做合并效果通常比强行搞联合索引好。3.3 核心查询距离圈选、多边形裁剪、时间窗口最常写的查询是“在某个点2公里范围内最近30天有哪些记录”。SQL 大致是这样WITH target AS ( SELECT ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)::geography AS pt ) SELECT t.device_id, t.ts, ST_Distance(t.point, target.pt, true) AS dist_m FROM t_track t, target WHERE ST_DWithin(t.point, target.pt, 2000) AND t.ts now() - INTERVAL 30 days ORDER BY t.ts DESC LIMIT 100;CTE里构造目标点ST_MakePoint(longitude, latitude)注意参数顺序先经度后纬度。ST_DWithin是核心过滤条件第三个参数 2000 表示 2000 米。因为列已经是geography类型这个函数会命中前面的 GIST 索引不会在索引外面再做一次隐式类型转换。ST_Distance的第三个参数true表示使用球体近似计算距离结果和椭球模型相差在千分之几但计算量小一些高速公路、城市道路这种场景完全够用。如果你是做区域围栏查询比如查某个矩形区域内出现了哪些设备就用ST_Intersects配合ST_MakeEnvelopeSELECT device_id, ts FROM t_track WHERE ST_Intersects( point::geometry, ST_MakeEnvelope(116.0, 39.5, 117.0, 40.5, 4326) ) AND ts now() - INTERVAL 7 days;这里把point从geography转成了geometry因为ST_MakeEnvelope生成的是几何类型两者不能直接混用。如果这个查询很频繁另一种做法是建表时再加一个geometry列的副本专门服务多边形裁剪类操作空间占用会大一点但避免了反复类型转换和索引失效的风险。提示无论哪种查询写完先跑一遍EXPLAIN确认执行计划里出现Index Scan using idx_t_track_geom。如果看到Seq Scan说明索引没有用上先检查类型转换和函数包裹。4. 时空数据库避坑指南坐标系、GeoHash和查询计划的五处翻车点4.1 坐标系选错距离查询返回的是“度”不是“米”现象查询两个北京坐标点之间的距离ST_Distance返回0.015这样的数值。开发一看以为只有 1.5 厘米实际距离接近 1.5 公里完全对不上。原因如果列类型是geometry(Point, 4326)ST_Distance计算的是经纬度坐标下的直线距离单位是度而不是米。1 度经度在不同纬度对应的实际距离差别很大直接拿这个数值做业务判断会出大事。解决最省心的方案是列类型直接定义成geography就像第 3 章建表那样所有距离函数自动以米为单位。如果已经用了geometry列查询时可以把参数转成geography算距离但要注意不要用point::geography包住索引列去过滤否则索引会失效。可靠做法是先按度做粗过滤比如 2000 米近似 0.02 度再用geography精算距离。4.2 GeoHash当空间索引用边界格子必漏数据现象有人在 MongoDB 或后端代码里用 GeoHash 前缀做“附近的人”查询比如geohash LIKE wx4g0%结果发现两个明明相距只有 50 米的点一个落在wx4g0格子里另一个落在wx4g1格子里查询结果漏了一半。原因GeoHash 把经纬度交错编码本质是把地球切成一个个不同级别的格子。格子之间是硬边界两个距离很近的点如果恰好在边界两侧它们的字符串前缀就从某一位开始完全不同。只查一个前缀必然漏边界点。解决GeoHash 只适合做分片路由和预分区不适合做精确的空间过滤。生产里我一般用它决定数据落到哪个 HBase 分区或哪个 MySQL 分库最终的距离查询仍然要交给空间索引函数来处理PostGIS 的ST_DWithin或 MongoDB 的$geoWithin都可以。记住一条原则字符串前缀是索引的入口不是查询的终点。4.3 空间索引建了但查询还是慢先看时间字段现象某条查询用上了GIST索引但执行时间仍然在数秒以上。用EXPLAIN ANALYZE一看GIST索引扫描返回了 20 万行再逐个把ts过滤掉最后只剩 300 行Rows Removed by Filter非常刺眼。原因这是典型的“空间索引负责了筛选但时间条件没有索引可用”。PostgreSQL 的选择器先走GIST拿到一批候选记录然后对每一条做时间过滤。如果你圈定的空间范围很大候选集自然很大时间过滤就会变成短板。解决给ts建独立的 btree 索引并确保查询条件里时间范围写得足够紧。更彻底的做法是按时间做分区表比如按日或按月RANGE分区让查询直接从分区裁剪中跳过大部分数据。空间索引和时间分区是时空数据库的两条腿缺一条都跑不快。4.4 轨迹表越写越慢空间索引膨胀与分区方案现象上线两个月后会发现同样一条INSERT语句耗时从 0.5 毫秒涨到 5 毫秒表数据量翻了一倍但磁盘占用涨了三倍。原因GIST 索引的更新代价比 btree 索引高。每个新点插入时都要在 R 树中找到合适的叶子节点可能触发节点分裂。再加上 PostgreSQL 的 MVCC 机制频繁更新和删除会产生大量死元组索引块被不断膨胀但不会自动瘦身。解决第一轨迹数据只追加、不更新尽量用COPY或批量INSERT写入不要逐条 insert。第二按时间做分区表比如PARTITION BY RANGE (ts)每月的轨迹落到独立分区单个分区的索引体积可控。第三定期执行REINDEX INDEX CONCURRENTLY重建膨胀严重的空间索引这个操作不会锁表适合线上执行。4.5 本地快线上慢统计信息与内存参数现象同样的 SQL在测试环境 100 万行数据上跑得飞快到了生产库 1 亿行却慢到不可理喻甚至走了完全不同的执行计划。原因优化器依赖表的统计信息来做决策。如果生产库没有及时ANALYZE优化器可能认为某个表很小选择了嵌套循环而不是哈希关联或者错误地忽略了空间索引。另一种常见情况是work_mem设置太小排序和哈希操作落到临时文件性能断崖式下跌。解决先跑ANALYZE;再看执行计划是否变化。如果查询里涉及大范围排序尝试把work_mem调到 64MB 或更高注意这是每次操作的内存上限不要盲目调几百 MB。空间数据库的内存参数不是玄学但很多人就是栽在上面以为索引坏了实际只是规划器在信息不全的情况下选错了路。5. 进阶玩法轨迹压缩、网格聚合与相似度分析5.1 轨迹压缩用ST_SimplifyPreserveTopology省存储点表积累到一定规模后原始数据不能丢但分析用的轨迹线可以压缩。常见做法是把同一设备、同一时段内的点聚合成一条LineString再用简化函数抽稀。WITH line AS ( SELECT device_id, ST_MakeLine(point::geometry ORDER BY ts) AS geom FROM t_track WHERE ts 2024-01-01 AND ts 2024-01-02 GROUP BY device_id ) SELECT device_id, ST_SimplifyPreserveTopology(geom, 0.0001) FROM line;ST_MakeLine按时间排序后把点串成线注意point是geography这里要转成geometry才能使用。ST_SimplifyPreserveTopology的第二个参数是容差单位是度0.0001 大约对应 10 米左右。容差越大简化后保留的点越少形状细节丢得越多。这个函数比普通ST_Simplify更稳它不会简化出一个自相交的畸形轨迹对道路形状还原很重要。压缩结果建议单独存一张轨迹分析表原始点表继续保留。这样热力图和相似度分析可以读压缩表明细回放再走原表。5.2 热力图网格聚合把千万点变成千个格子全城热力图这类需求不关心单个点的精确位置只关心单位区域内有多少点。直接把原始点喂给前端几千万个点会把浏览器直接拖垮。正确做法是在库里先聚合。SELECT ST_SnapToGrid(point::geometry, 0.001) AS grid, date_trunc(hour, ts) AS hour_bucket, count(*) AS cnt FROM t_track WHERE ts now() - INTERVAL 1 day GROUP BY grid, hour_bucket ORDER BY cnt DESC;ST_SnapToGrid把每个点吸附到最近的网格中心第二个参数 0.001 度大约相当于 100 米左右具体看维度稍微有偏差但做热力粗粒度完全够用。与date_trunc(hour, ts)组合后输出就是某个小时、某个网格内的点数。前端拿到这份聚合数据后渲染热力图性能会比直接拉原始点好一个量级。如果业务上需要更准确的正方形网格可以先把坐标ST_Transform到 Web 墨卡托投影坐标系在投影坐标上按米做ST_SnapToGrid再转回流前端用的经纬度。这个细节才是热力图最终效果的分水岭。5.3 轨迹相似度分析从集合到几何距离比如判断两条送货轨迹是否“同路”或者识别异常绕路可以用豪斯多夫距离。它衡量的是两条轨迹线上任意点到另一条线的最大距离值越小越相似。SELECT a.device_id AS dev_a, b.device_id AS dev_b, ST_HausdorffDistance(a.geom, b.geom) AS hdist FROM t_track_line a JOIN t_track_line b ON a.device_id b.device_id WHERE a.day 2024-01-02 AND b.day 2024-01-02 ORDER BY hdist ASC LIMIT 20;t_track_line建议是上一节存下来的压缩轨迹表直接用原始点表聚合成 LineString 也可以但要控制每个设备的点数否则豪斯多夫距离的计算量会很大。这类几何距离对两端点位置比较敏感两条轨迹如果一条多走了个路口距离值就会变大。实际生产里我更常配合网格 ID 序列做 Jaccard 相似度计算更快也更稳定。5.4 超大规模场景预聚合层与并行扫描当轨迹表到了十亿行以上任何索引都救不了实时大范围聚合。我在一些大集群项目里看到的可靠做法是加一层预聚合表。CREATE TABLE t_track_agg_hour ( hour_bucket TIMESTAMPTZ NOT NULL, grid GEOMETRY(Point, 4326) NOT NULL, cnt BIGINT NOT NULL, PRIMARY KEY (hour_bucket, grid) );由定时任务每 5 分钟把新到的轨迹按小时和网格做一次增量统计写入这张表。业务查询先查聚合表秒级出结果用户下钻看明细时再查原始表并限制时间和空间范围。很多看起来“大数据量反而更快”的时空平台本质都是把精确查询和预聚合查询分开了而不是所有查询都硬抗原始表。注意预聚合表适合热力图、趋势统计这类可接受误差的上卷场景。涉及计费、合规、精确碰撞的业务必须走明细表不能拿聚合结果顶替。6. 用EXPLAIN ANALYZE验收时空查询一张百万级测试表的验证过程写完索引和 SQL 之后一定要先验证执行计划而不是直接上线。我常用一张 500 万行的测试表来做验收生成数据的 SQL 如下INSERT INTO t_track (device_id, point, ts) SELECT D || (g % 2000), ST_SetSRID( ST_MakePoint( 116.0 0.2 * (g % 1000) / 1000.0, 39.5 0.2 * (g % 900) / 1000.0 ), 4326 )::geography, now() - (g % 365) * INTERVAL 1 day FROM generate_series(1, 5000000) AS g;这里把 500 万个点均匀撒在北京五环附近约 0.2 度见方的区域时间分布在过去一年。g % 2000生成 2000 个模拟设备经纬度用g % 1000和g % 900控制在固定比例上。注意插入时最后要转成geography类型否则类型不匹配。然后分别跑三种场景的EXPLAIN ANALYZEEXPLAIN (ANALYZE, BUFFERS) SELECT device_id, ts FROM t_track WHERE ST_DWithin(point, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)::geography, 2000) AND ts now() - INTERVAL 30 days ORDER BY ts DESC LIMIT 100;重点看三项Execution Time是整体耗时Index Scan表示空间索引是否用上Rows Removed by Filter表示时间条件过滤掉了多少行。如果看到Seq Scan大概率是类型转换或函数包裹导致索引失效需要回到第 3 章检查表定义和 SQL 写法。我一般会做的对比是这个场景执行计划表现结论不建任何索引Seq Scan全表扫描必须建索引只建 GIST 索引Index Scan但 Rows Removed 很多时间条件缺索引GIST btree 双索引BitmapAnd过滤后行数少标准解法有一次线上查询从 3 秒优化到 50 毫秒靠的不是改 SQL而是清理了膨胀的索引并补上时间分区。工具比直觉可靠得多。任何做时空数据的人都应该把EXPLAIN ANALYZE当成第一反应而不是拿到慢查询先加索引。很多坑都是翻车之后才看执行计划浪费了大把时间希望帮到你。本文还有配套的精品资源点击获取