空间数据库这个方向不少人一开始是绕道走的。早期做GIS开发的人要么用文件型Shapefile硬扛要么丢给Oracle Spatial这种重家伙。直到PostgreSQL搭配PostGIS这套组合越来越成熟我才算真正找到了顺手的工作流。这篇内容不是官方文档翻译是我从零开始用PostgreSQL做空间数据存储和管理时的实操记录包括怎么选型、怎么落地、以及你一定会碰到的坑。如果你是做GIS开发、地图相关的后端服务或者刚接手一个和空间数据有关的项目正纠结用MySQL还是PostgreSQL甚至还在想着用文件存坐标那这篇内容应该对你有用。我会尽量把每个步骤背后的理由讲清楚不光是让你知道怎么做也让你知道为什么要这么做。1. 为什么拿PostgreSQL当空间数据库来用1.1 别把空间数据库想得太神秘很多人一听“空间数据库”这五个字觉得是个特别高级的东西。其实拆开来看它就是普通数据库多了两样东西空间数据类型和空间索引。空间数据类型让数据库能存储点、线、面这种几何信息而不是只存字符串和数字。空间索引则让数据库能快速回答“哪些点在这个范围里”、“哪条线和这条线相交”这类问题。那为什么偏偏是PostgreSQL我用过不少数据库做空间数据的存储MySQL也试过MongoDB也用过最后还是把核心的空间数据都搬到了PostgreSQL上。最直接的理由是PostGIS这个扩展。PostGIS给PostgreSQL加上了完整的地理信息处理能力包括几何类型、坐标转换、空间分析函数、栅格数据支持等等。它不是一个单独的数据库而是PostgreSQL的插件用的时候装上去不用也不影响其他功能。MySQL虽然也有空间扩展但功能上和PostGIS完全不是一个量级。举个最简单的例子在PostGIS里可以轻松计算两个多边形是否相交、相交面积多大还能做缓冲区分析、路径规划。在MySQL里想干这些事要么自己写算法要么就是性能差到让人崩溃。MongoDB虽然能存GeoJSON但空间查询能力和分析能力也远不如PostGIS特别是一旦涉及复杂的空间关系判断基本就是灾难。1.2 PostgreSQL凭什么是首选除了PostGIS这个王牌PostgreSQL本身也有很多值得说的东西。第一它对SQL标准的支持非常完整。数据库做得越久越能体会语法规范、功能完整意味着你写的SQL可以稳定跑很多年不会因为升级换了个花样就出问题。相比某些数据库的“方言”PostgreSQL的SQL写起来通透很多从Oracle迁过来的人会特别有感触。第二数据类型丰富。除了常规的数值、字符串、日期类型PostgreSQL还支持数组、JSON、JSONB、枚举等类型。这个在做空间数据的时候特别有用比如一个地块的JSON属性信息可以跟几何信息存在一行里不用单独搞一张表来存属性。第三扩展生态特别好。不只是PostGIS还有pgRouting做路径分析TimescaleDB做时序数据pgvector做向量检索。一套数据库可以把空间、属性、时序这些数据全部管起来省去了部署多个系统的麻烦。我自己在选型上的建议是如果项目的主要场景是和空间位置打交道并且有分析、计算的需求直接选PostgreSQL加PostGIS不用犹豫。如果只是存一些经纬度然后偶尔查查距离MySQL也能用但遇到复杂查询时扩展性会立刻成为问题。1.3 主流空间数据方案横评拿我自己做过的一些项目经验来对比一下常见的空间数据技术选型。方案空间数据支持功能丰富度学习成本综合开销适用场景PostgreSQL PostGIS强极高空间分析、坐标转换、栅格、拓扑全都有中等SQL基础GIS概念免费中大型空间应用、WebGIS、复杂分析MySQL空间扩展中基础只支持简单的空间关系函数低免费简单的POI存储和周边查询Oracle Spatial强高但上手复杂且生态封闭高授权费用高大型企业传统GIS系统MongoDB GeoJSON弱到中只有基础的空间查询能力低免费轻量应用文件数据库无完全靠自己实现高低临时性任务或小数据量这个表格一看就明白了PostgreSQL在这个领域基本是综合最优解。就算你之前用过Oracle Spatial转PostgreSQL也不会太痛苦这是我要在后面详细说的一个重点。2. 搭建一套可用的空间数据库环境2.1 版本选择与安装PostgreSQL的版本更新挺快的我接触过的大多数生产环境用的是14、15、16这几个版本。新版本在性能、索引机制、查询优化器上都有改进建议直接用较新的稳定版不要用太老的版本否则很多SQL写法和扩展版本会受限。安装方式要看你用的系统。Windows下直接下载安装包一路下一步就行。不过Windows下要注意安装过程中会让你设置超级用户postgres的密码这个密码一定别用太简单的因为后面所有数据库连接都需要它。Linux下推荐用系统包管理器安装比如在Ubuntu下sudo apt update sudo apt install postgresql postgresql-contribRed Hat系或CentOS系的系统用yum或dnf安装命令大概是sudo dnf install postgresql-server postgresql-contrib sudo postgresql-setup --initdb sudo systemctl start postgresql安装完成后PostgreSQL服务默认监听localhost的5432端口。不要急着修改监听地址初学者默认跑在本地就好别一上来就暴露到公网上安全问题不是闹着玩的。2.2 让PostgreSQL变成空间数据库的关键一步PostGIS装好PostgreSQL只是完成了基础部分要让它变成能处理空间数据的数据库必须安装PostGIS扩展。在Windows下安装PostgreSQL时可以选择捆绑安装Stack Builder里面有PostGIS组件。在Linux下用包管理器安装即可sudo apt install postgis postgresql-16-postgis-3注意版本要匹配比如你装的是PostgreSQL 16就要装对应的postgresql-16-postgis-3。版本不匹配会报找不到扩展的错这是新手最常见的坑之一。装完PostGIS之后还需要在具体数据库里启用它。注意PostGIS是按数据库粒度启用的不是装完扩展所有数据库自动具备空间能力。你得先建一个数据库然后执行连接postgres数据库创建空间数据库在空间数据库里启用PostGIS扩展。-- 创建空间数据库 CREATE DATABASE spatial_db; -- 连接到spatial_db执行 CREATE EXTENSION IF NOT EXISTS postgis;执行完这条SQL你的PostgreSQL才算真正变成了一个空间数据库。可以查一下验证SELECT PostGIS_Version();如果返回了版本号比如3.4.0之类的那就说明一切正常。2.3 验证环境能不能正常干活环境装好之后我一般会做一次快速的冒烟测试确认空间计算真的可用。先建一个简单的表然后插入点数据试试空间计算函数看看能不能跑通。CREATE TABLE test_points ( id SERIAL PRIMARY KEY, name VARCHAR(50), geom GEOMETRY(Point, 4326) ); INSERT INTO test_points (name, geom) VALUES (A点, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)), (B点, ST_SetSRID(ST_MakePoint(121.491, 31.233), 4326)); SELECT name, ST_AsText(geom) AS wkt, ST_Distance( geom::GEOGRAPHY, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)::GEOGRAPHY ) AS distance_meters FROM test_points;在这段测试SQL里我用了ST_MakePoint生成坐标点ST_SetSRID指定坐标系再通过ST_AsText把几何对象转成可读的文本最后用ST_Distance算两点间的距离。这里把geometry转成geography类型再计算距离得到的结果单位是米这一点非常关键。如果直接拿geometry类型的经纬度坐标去算算出来的结果是“度”并不是物理意义上的米这个坑我后面会专门讲。如果这条查询能跑出两条记录第一条距离是0第二条B点距离是1067公里左右说明环境已经完全OK了。3. PostGIS实操从建库到跑通第一个空间查询3.1 建库和启用扩展的最佳实践建空间数据库这件事看起来简单但有一个操作习惯值得养成不要把数据表直接建在public模式下。随着项目变大空间数据表、中间表、临时分析表混在一起后期维护非常痛苦。我通常的做法是创建一个专门的schema比如叫gis把所有空间相关的数据都放进去。CREATE SCHEMA IF NOT EXISTS gis; -- 需要在这个schema下建表时可以用完整限定名 CREATE TABLE gis.roads ( id BIGSERIAL PRIMARY KEY, road_name VARCHAR(100), geom GEOMETRY(LineString, 4326) );这样做的好处是数据层级清晰权限也更容易控制。比如可以单独把gis这个schema授权给某个业务账号其他schema不开放既方便管理又更安全。启用PostGIS扩展时也有一点要注意它需要超级用户或足够的权限。如果用的是普通用户可能会遇到“permission denied to create extension”这类错误。这时候要么用超级用户执行一次要么给这个用户加上适当的权限。3.2 把Shapefile数据导入PostgreSQL真实项目里肯定不会像测试那样手动一条条插入数据最常碰到的场景是把已有的Shapefile数据导入到PostgreSQL里。PostGIS安装完毕后会附带一个命令行工具叫shp2pgsql专门干这个事。Windows下这个工具的路径一般在PostgreSQL安装目录的bin文件夹里Linux下直接用就行。基本用法是这样shp2pgsql -s 4326 -I -W UTF-8 /path/to/your/file.shp gis.roads roads.sql然后生成了一个SQL文件把它导入数据库psql -U postgres -d spatial_db -f roads.sql这条命令里的几个参数值得细说。-s 4326表示Shapefile里的坐标是WGS84经纬度坐标系-I会在几何列上自动创建空间索引这个很重要忘了加后面查询会慢得让人怀疑人生-W UTF-8指定属性表的编码中文环境下特别关键不指定经常出现乱码尤其你的Shapefile是从国内测绘数据转出来的情况。还有一种方式是直接通过管道一条命令把数据灌进去我平时用得比较多shp2pgsql -s 4326 -I /path/to/layer.shp gis.building | psql -U postgres -d spatial_db这个命令不生成中间SQL文件直接把数据写进数据库。对于大文件来说更省磁盘空间而且不用担心生成的SQL文件太大导致导入失败。3.3 常用空间查询写法与坐标参考系的坑数据进去之后核心问题就是怎么查了。这里说一下我日常用得最多的几种空间查询写法。一是范围过滤。比如查一个点的周边500米范围内有哪些设施这种需求最常见SELECT name FROM gis.poi WHERE ST_DWithin( geom::GEOGRAPHY, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)::GEOGRAPHY, 500 );注意这个查询里用了ST_DWithin并且把geometry转换成了geography第三个参数500表示500米。ST_DWithin的优势是它可以利用空间索引快速筛选性能比先算距离再筛选的写法好很多。如果你写成ST_Distance(geom, target_geom) 500数据库会对每一行都做一次全量距离计算数据量一大就卡死。二是相交分析。比如有一个行政区划面要找出所有落在里面的地块SELECT b.parcel_id FROM gis.admin_boundary a JOIN gis.parcels b ON ST_Intersects(a.geom, b.geom) WHERE a.name 某区;ST_Intersects判断两个几何对象是否在空间上有交集它是空间连接操作里最常用的一个函数。在PostGIS的优化下这个操作会先通过空间索引圈定候选集然后再做精确计算性能非常有保障。三是计算面积和长度。用ST_Area和ST_Length可以计算面要素的面积和线要素的长度。这里有个我踩过好几次的坑如果是geometry类型且坐标系是4326算出来的面积单位是度不是平方米。要想得到平方米的结果得把坐标系转成投影坐标系或者转成geography类型SELECT name, ST_Area(geom::GEOGRAPHY) AS area_sqm FROM gis.parcels WHERE name 某地块;如果数据量很大每次都转换一次还是有开销的更优的做法是在建表时就直接把geom字段设计为geography类型或者额外存一列投影坐标根据不同的应用场景各取所需。四是坐标转换。从网上拿来的数据可能是4326的经纬度但很多地图底图要求用3857的Web墨卡托投影坐标这时候需要用ST_TransformSELECT ST_AsText(ST_Transform(geom, 3857)) FROM gis.roads WHERE id 1;ST_Transform和ST_SetSRID的区别要搞明白。ST_SetSRID只是给几何对象“贴标签”告诉数据库这个坐标是基于哪个坐标系的它不会改变坐标值。而ST_Transform是真正把坐标从一个坐标系换算到另一个坐标系坐标值会发生变化。这两者搞混是整个空间数据出错最常见的根源之一。3.4 空间查询性能的关键空间索引空间数据量一大性能好不好全看空间索引建得到不到位。没有空间索引的表每次空间查询都是全表扫描每条记录都要做一次昂贵的几何计算速度和龟爬差不多。有了空间索引数据库可以先用索引粗筛一遍只对少量候选记录做精确计算。PostGIS里的空间索引一般用GIST索引创建语句是CREATE INDEX idx_parcels_geom ON gis.parcels USING GIST(geom);在shp2pgsql导入时如果带了-I参数会在导入后自动创建这个索引所以导入时务必加上。如果表已经建好那就手动补上。有了索引之后还要注意SQL写法确保查询能真正利用到索引。用ST_DWithin、ST_Intersects这些函数时只要另一个参数是常量或参数数据库一般都能用上索引。但如果你写ST_Intersects(geom, ST_MakePoint(...))这种形式优化器也能处理。最怕的是先对坐标做计算再比较比如-- 这种写法用不上索引全表扫描慢 SELECT * FROM gis.poi WHERE ST_Distance(geom, target_geom) 100;虽然功能和ST_DWithin类似但因为语法形式不同数据库无法把它转为索引扫描。我一直跟团队强调能用ST_DWithin的场景绝不用ST_Distance加阈值判断。如果想确认查询有没有走索引用EXPLAIN ANALYZE看一下执行计划EXPLAIN ANALYZE SELECT name FROM gis.poi WHERE ST_DWithin( geom::GEOGRAPHY, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)::GEOGRAPHY, 500 );看执行计划里是不是有Index Scan using idx_poi_geom如果是Seq Scan就说明索引没生效需要回去检查SQL写法或索引是否创建成功。4. 从Oracle迁移过来的语法差异与踩坑实录4.1 数据类型的差异很多做GIS的团队是从Oracle Spatial转过来的因为Oracle的授权费用实在太高了。如果你也面临这个迁移下面的差异一定会用得上。Oracle里常用的NUMBER类型在PostgreSQL里对应的是NUMERIC或INTEGER、BIGINT。VARCHAR2在PostgreSQL里就是VARCHAR两者基本等价。Oracle的DATE类型默认包含时分秒PostgreSQL的DATE只包含日期如果想存时间得用TIMESTAMP这个一定要区分清楚。PostgreSQL还有Oracle没有的JSONB类型用起来非常方便。Oracle对JSON的支持比较晚性能也一般。从Oracle转过来的人第一次用JSONB字段普遍反馈是回不去了。Oracle的表常用SEQUENCE来实现自增主键通常是一个触发器加一个序列。PostgreSQL里可以用SERIAL或BIGSERIAL类型也可以直接用GENERATED AS IDENTITY。PostgreSQL 10以后推荐用GENERATED AS IDENTITY的方式更符合标准CREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100) );如果是从Oracle迁移过来Oracle的SEQUENCE.NEXTVAL可以映射成PostgreSQL里的NEXTVAL(sequence_name)双关语保持兼容。4.2 常用SQL语法差异对照语法差异是迁移过程中最容易遇到问题的地方。我整理了一个高频对照表两个数据库都跑过保证准确。功能Oracle写法PostgreSQL写法字符串拼接name || - || code或CONCAT(name, -, code)name || - || code或CONCAT(name, -, code)取前N条WHERE ROWNUM 10LIMIT 10分页OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY12cLIMIT 10 OFFSET 20当前时间SYSDATENOW()或CURRENT_TIMESTAMP空值排序默认空值最大默认空值最大但是可以NULLS FIRST/LAST控制字符串转数字TO_NUMBER(str)CAST(str AS NUMERIC)或str::NUMERIC去重统计COUNT(DISTINCT col)COUNT(DISTINCT col)字段自增SEQUENCE.NEXTVALSERIAL/IDENTITY插入不存在的记录更新MERGE INTO ...INSERT ... ON CONFLICT DO UPDATE条件逻辑DECODE(expr, val1, res1, res2)CASE WHEN ... THEN ... ELSE ... END这里面最坑的是ROWNUM和LIMIT的对应关系经常有人在迁移时直接把两者等价替换但Oracle的ROWNUM是在结果集生成时逐行赋予序号和LIMIT的语义有一些差别。一般简单场景等价没问题复杂的排序分页场景要特别小心建议用ROW_NUMBER() OVER (ORDER BY ...)把Oracle的写法整体重写一遍。还有一个使用频率很高的差异是隐式类型转换。Oracle在比较时会自动做很多隐式的类型转换PostgreSQL则严格很多。比如Oracle里直接写WHERE create_date 2024-01-01可能没问题但在PostgreSQL里如果create_date是TIMESTAMP类型这个字符串会被隐式转成日期导致的是精确匹配如果数据时间是2024-01-01 08:00:00这一条就查不出来。正确做法是用WHERE create_date 2024-01-01 AND create_date 2024-01-02。这是从一个老系统迁到PostgreSQL时最常见的业务bug来源。4.3 Oracle迁移到PostgreSQL值得注意的地方除了语法和数据类型的差异整个迁移过程还有很多不是SQL层面的坑我这里挑几个重点讲。第一个是存储过程的重写。Oracle的PL/SQL和PostgreSQL的PL/pgSQL虽然长得像但细节差异很大。比如Oracle里过程参数可以不带类型长度PostgreSQL里有些写法会不一样。异常处理的语法也不同Oracle是EXCEPTION WHEN OTHERS THENPostgreSQL也支持类似写法但具体异常名称和函数细节会有区别。迁移之前要先想清楚是继续在数据库里写业务逻辑还是把逻辑搬到应用层。我现在倾向于后者数据库尽量只做数据存取和简单约束复杂业务逻辑放在应用里迁移和扩展都更容易。第二个是二进制数据。Oracle有BLOBPostgreSQL对应的是BYTEA。看起来差不多但处理方式差别不小PostgreSQL的BYTEA对应用层更友好字符串转储时用\x十六进制格式。第三个是事务和锁。PostgreSQL的MVCC机制和Oracle有相似之处但锁粒度、死锁处理上的细节不同。大批量更新时两个数据库的行为表现都不一样最好先小规模测试再全量执行。第四个也是最容易忽视的编码问题。Oracle环境下很多老系统的字符集是ZHS16GBK导出的数据迁移到PostgreSQL通常UTF8后极容易出现中文乱码。迁移时的文本文件转码、CLIENT_ENCODING设置都要提前测好不要等迁移完才发现字段里全是问号。5. 日常维护与常见问题排查5.1 空间数据加载缓慢怎么排查用着用着有人会抱怨数据加载变慢了。空间数据的性能问题跟普通关系型数据的排查思路有相似点也有不一样的地方。第一步先看SQL执行计划。用EXPLAIN ANALYZE跑一下查询确认是不是发生了全表扫描有没有走空间索引。第二步检查索引是否失效。PostgreSQL一般不会主动把索引标记为不可用但如果表大量更新、删除、提交事务后没有及时清理索引的效率会下降。处理方式是执行VACUUM ANALYZE gis.parcels;VACUUM会清理已删除行的空间ANALYZE会更新统计信息让查询优化器作出更好的决策。这个命令在数据频繁更新的表上应该定期执行不是可有可无的事。第三步检查查询本身能不能改写。比如ST_Intersects(geom, geom)比在应用层做好裁剪后查两遍高效得多。尽量减少空间计算的行数让空间索引在第一时间把数据量降到足够小。第四步硬件层面。空间数据通常比普通业务数据占用更多存储因为几何对象要存整个坐标序列一个复杂的多边形可能有几万个坐标点。这时候内存够不够大、磁盘是不是SSD都会直接影响性能。像PostgreSQL的shared_buffers、work_mem这些参数也要根据机器配置适当调整。5.2 坐标系混乱的经典翻车场景坐标系问题在我接触过的所有空间数据项目里是出错率最高、排查难度最大的问题没有之一。这里列几个我真实遇到过的翻车场景。场景一数据是4326的经纬度但导入时忘记指定SRID结果数据库里存的SRID是0任何空间运算都得不到正确结果。这个很好排查执行SELECT ST_SRID(geom) FROM table LIMIT 1;看返回什么。场景二两个图层坐标SRID不同一个4326一个3857直接用ST_Intersects做叠加得到的结果完全错误。解决办法是先把两者统一到同一坐标系再做叠加SELECT a.name, b.name FROM gis.layer_a a JOIN gis.layer_b b ON ST_Intersects(a.geom, ST_Transform(b.geom, ST_SRID(a.geom)));场景三把带投影坐标的数据当经纬度用。有些数据的SRID标记为4547或4548国家大地坐标系坐标值看起来像是“几百米”这种尺度结果拿它当成经纬度画在地图上效果就是一团乱麻。这类数据的正确做法是先ST_Transform到4326再缩放到合理范围。场景四距离单位问题。4326坐标系下直接算两个点的ST_Distance结果是度一英里和一个经度的实际物理距离差别非常大。千万别用这个结果直接做展示或判断一定要转成GEOGRAPHY类型或者投影坐标系再算单位才是米。越是看起来经验丰富的人越容易在坐标系上栽跟头因为数据源头多样化一个不小心就是错位。建议所有入库数据都做一次SRID检查和统一校验把这个流程固化到ETL里能省掉后面大量的排查时间。5.3 关于备份恢复的几个建议空间数据库的数据量普遍都不小备份恢复的策略比普通业务库更需要注意。PostgreSQL最传统的备份工具是pg_dump对空间数据也适用。一个实例如下pg_dump -U postgres -F c -f spatial_db.dump spatial_db-F c表示输出为自定义格式这种格式支持压缩也支持恢复到指定表。恢复时用pg_restore -U postgres -d spatial_db spatial_db.dump但如果你有多个数据库要备份或者数据量达到几十GB甚至TB级别pg_dump的效率就不太行了。这时候我会用pg_basebackup做物理备份直接拷贝整个数据目录。物理备份的好处是恢复速度快但只能恢复到整个实例不能单独恢复某张表。对于空间数据我强烈建议在做完备份后定期尝试恢复到一台测试机上验证一下备份有效性。空间数据的表数量多、依赖关系复杂而且PostGIS相关元数据表也要一起备份否则恢复后的数据库执行空间查询时会提示找不到函数。实际恢复过程中还要确保目标库已经启用PostGIS扩展否则一堆依赖PostGIS的SQL全部报错。还有一个建议空间数据的几何字段很容易因为程序bug被写入脏数据比如自相交的多边形、重复的坐标点等。所以在备份策略之外最好定期做一些数据质量检查用ST_IsValid函数扫描一遍SELECT id, name FROM gis.parcels WHERE NOT ST_IsValid(geom) LIMIT 100;ST_IsValid在数据量大时会拖慢查询可以在非高峰时段跑一次全表检查把不合法数据修掉或者清理掉。别等到做空间分析出结果不对了才回头去查原始数据问题。我印象最深的一次是一个同事导入了几千个地块看起来一切正常但做面积统计时结果怎么都对不上。折腾了两个多小时才发现一部分几何对象有自相交问题导致面积计算重复计数。从那以后我的数据导入流程永远带一道ST_IsValid校验。最后分享一个我个人的使用体会PostgreSQL的空间数据能力学习曲线不算陡但涉及的知识点非常杂数据库本身要懂GIS原理要懂坐标系要知道甚至还要会一点点性能调优。入门阶段最忌讳的就是只装了个PostGIS能建表插数据就觉得完事了。实际项目中坐标系设置、空间索引创建、SQL写法优化每一项都会直接影响系统的成败。如果看完这篇内容你决定尝试用PostgreSQL做空间存储建议按这个顺序练手先建好环境和PostGIS然后用shp2pgsql导入一份公开的行政区域数据接着尝试用ST_Intersects做叠加分析再用ST_DWithin写一个周边查询。把这条链路走通你就已经超过大部分刚接触空间数据库的人了。后续再遇到具体问题翻官方文档也有底了因为你已经知道整个体系是怎么运转的。
PostgreSQL+PostGIS空间数据库实战：从环境搭建到性能优化
空间数据库这个方向不少人一开始是绕道走的。早期做GIS开发的人要么用文件型Shapefile硬扛要么丢给Oracle Spatial这种重家伙。直到PostgreSQL搭配PostGIS这套组合越来越成熟我才算真正找到了顺手的工作流。这篇内容不是官方文档翻译是我从零开始用PostgreSQL做空间数据存储和管理时的实操记录包括怎么选型、怎么落地、以及你一定会碰到的坑。如果你是做GIS开发、地图相关的后端服务或者刚接手一个和空间数据有关的项目正纠结用MySQL还是PostgreSQL甚至还在想着用文件存坐标那这篇内容应该对你有用。我会尽量把每个步骤背后的理由讲清楚不光是让你知道怎么做也让你知道为什么要这么做。1. 为什么拿PostgreSQL当空间数据库来用1.1 别把空间数据库想得太神秘很多人一听“空间数据库”这五个字觉得是个特别高级的东西。其实拆开来看它就是普通数据库多了两样东西空间数据类型和空间索引。空间数据类型让数据库能存储点、线、面这种几何信息而不是只存字符串和数字。空间索引则让数据库能快速回答“哪些点在这个范围里”、“哪条线和这条线相交”这类问题。那为什么偏偏是PostgreSQL我用过不少数据库做空间数据的存储MySQL也试过MongoDB也用过最后还是把核心的空间数据都搬到了PostgreSQL上。最直接的理由是PostGIS这个扩展。PostGIS给PostgreSQL加上了完整的地理信息处理能力包括几何类型、坐标转换、空间分析函数、栅格数据支持等等。它不是一个单独的数据库而是PostgreSQL的插件用的时候装上去不用也不影响其他功能。MySQL虽然也有空间扩展但功能上和PostGIS完全不是一个量级。举个最简单的例子在PostGIS里可以轻松计算两个多边形是否相交、相交面积多大还能做缓冲区分析、路径规划。在MySQL里想干这些事要么自己写算法要么就是性能差到让人崩溃。MongoDB虽然能存GeoJSON但空间查询能力和分析能力也远不如PostGIS特别是一旦涉及复杂的空间关系判断基本就是灾难。1.2 PostgreSQL凭什么是首选除了PostGIS这个王牌PostgreSQL本身也有很多值得说的东西。第一它对SQL标准的支持非常完整。数据库做得越久越能体会语法规范、功能完整意味着你写的SQL可以稳定跑很多年不会因为升级换了个花样就出问题。相比某些数据库的“方言”PostgreSQL的SQL写起来通透很多从Oracle迁过来的人会特别有感触。第二数据类型丰富。除了常规的数值、字符串、日期类型PostgreSQL还支持数组、JSON、JSONB、枚举等类型。这个在做空间数据的时候特别有用比如一个地块的JSON属性信息可以跟几何信息存在一行里不用单独搞一张表来存属性。第三扩展生态特别好。不只是PostGIS还有pgRouting做路径分析TimescaleDB做时序数据pgvector做向量检索。一套数据库可以把空间、属性、时序这些数据全部管起来省去了部署多个系统的麻烦。我自己在选型上的建议是如果项目的主要场景是和空间位置打交道并且有分析、计算的需求直接选PostgreSQL加PostGIS不用犹豫。如果只是存一些经纬度然后偶尔查查距离MySQL也能用但遇到复杂查询时扩展性会立刻成为问题。1.3 主流空间数据方案横评拿我自己做过的一些项目经验来对比一下常见的空间数据技术选型。方案空间数据支持功能丰富度学习成本综合开销适用场景PostgreSQL PostGIS强极高空间分析、坐标转换、栅格、拓扑全都有中等SQL基础GIS概念免费中大型空间应用、WebGIS、复杂分析MySQL空间扩展中基础只支持简单的空间关系函数低免费简单的POI存储和周边查询Oracle Spatial强高但上手复杂且生态封闭高授权费用高大型企业传统GIS系统MongoDB GeoJSON弱到中只有基础的空间查询能力低免费轻量应用文件数据库无完全靠自己实现高低临时性任务或小数据量这个表格一看就明白了PostgreSQL在这个领域基本是综合最优解。就算你之前用过Oracle Spatial转PostgreSQL也不会太痛苦这是我要在后面详细说的一个重点。2. 搭建一套可用的空间数据库环境2.1 版本选择与安装PostgreSQL的版本更新挺快的我接触过的大多数生产环境用的是14、15、16这几个版本。新版本在性能、索引机制、查询优化器上都有改进建议直接用较新的稳定版不要用太老的版本否则很多SQL写法和扩展版本会受限。安装方式要看你用的系统。Windows下直接下载安装包一路下一步就行。不过Windows下要注意安装过程中会让你设置超级用户postgres的密码这个密码一定别用太简单的因为后面所有数据库连接都需要它。Linux下推荐用系统包管理器安装比如在Ubuntu下sudo apt update sudo apt install postgresql postgresql-contribRed Hat系或CentOS系的系统用yum或dnf安装命令大概是sudo dnf install postgresql-server postgresql-contrib sudo postgresql-setup --initdb sudo systemctl start postgresql安装完成后PostgreSQL服务默认监听localhost的5432端口。不要急着修改监听地址初学者默认跑在本地就好别一上来就暴露到公网上安全问题不是闹着玩的。2.2 让PostgreSQL变成空间数据库的关键一步PostGIS装好PostgreSQL只是完成了基础部分要让它变成能处理空间数据的数据库必须安装PostGIS扩展。在Windows下安装PostgreSQL时可以选择捆绑安装Stack Builder里面有PostGIS组件。在Linux下用包管理器安装即可sudo apt install postgis postgresql-16-postgis-3注意版本要匹配比如你装的是PostgreSQL 16就要装对应的postgresql-16-postgis-3。版本不匹配会报找不到扩展的错这是新手最常见的坑之一。装完PostGIS之后还需要在具体数据库里启用它。注意PostGIS是按数据库粒度启用的不是装完扩展所有数据库自动具备空间能力。你得先建一个数据库然后执行连接postgres数据库创建空间数据库在空间数据库里启用PostGIS扩展。-- 创建空间数据库 CREATE DATABASE spatial_db; -- 连接到spatial_db执行 CREATE EXTENSION IF NOT EXISTS postgis;执行完这条SQL你的PostgreSQL才算真正变成了一个空间数据库。可以查一下验证SELECT PostGIS_Version();如果返回了版本号比如3.4.0之类的那就说明一切正常。2.3 验证环境能不能正常干活环境装好之后我一般会做一次快速的冒烟测试确认空间计算真的可用。先建一个简单的表然后插入点数据试试空间计算函数看看能不能跑通。CREATE TABLE test_points ( id SERIAL PRIMARY KEY, name VARCHAR(50), geom GEOMETRY(Point, 4326) ); INSERT INTO test_points (name, geom) VALUES (A点, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)), (B点, ST_SetSRID(ST_MakePoint(121.491, 31.233), 4326)); SELECT name, ST_AsText(geom) AS wkt, ST_Distance( geom::GEOGRAPHY, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)::GEOGRAPHY ) AS distance_meters FROM test_points;在这段测试SQL里我用了ST_MakePoint生成坐标点ST_SetSRID指定坐标系再通过ST_AsText把几何对象转成可读的文本最后用ST_Distance算两点间的距离。这里把geometry转成geography类型再计算距离得到的结果单位是米这一点非常关键。如果直接拿geometry类型的经纬度坐标去算算出来的结果是“度”并不是物理意义上的米这个坑我后面会专门讲。如果这条查询能跑出两条记录第一条距离是0第二条B点距离是1067公里左右说明环境已经完全OK了。3. PostGIS实操从建库到跑通第一个空间查询3.1 建库和启用扩展的最佳实践建空间数据库这件事看起来简单但有一个操作习惯值得养成不要把数据表直接建在public模式下。随着项目变大空间数据表、中间表、临时分析表混在一起后期维护非常痛苦。我通常的做法是创建一个专门的schema比如叫gis把所有空间相关的数据都放进去。CREATE SCHEMA IF NOT EXISTS gis; -- 需要在这个schema下建表时可以用完整限定名 CREATE TABLE gis.roads ( id BIGSERIAL PRIMARY KEY, road_name VARCHAR(100), geom GEOMETRY(LineString, 4326) );这样做的好处是数据层级清晰权限也更容易控制。比如可以单独把gis这个schema授权给某个业务账号其他schema不开放既方便管理又更安全。启用PostGIS扩展时也有一点要注意它需要超级用户或足够的权限。如果用的是普通用户可能会遇到“permission denied to create extension”这类错误。这时候要么用超级用户执行一次要么给这个用户加上适当的权限。3.2 把Shapefile数据导入PostgreSQL真实项目里肯定不会像测试那样手动一条条插入数据最常碰到的场景是把已有的Shapefile数据导入到PostgreSQL里。PostGIS安装完毕后会附带一个命令行工具叫shp2pgsql专门干这个事。Windows下这个工具的路径一般在PostgreSQL安装目录的bin文件夹里Linux下直接用就行。基本用法是这样shp2pgsql -s 4326 -I -W UTF-8 /path/to/your/file.shp gis.roads roads.sql然后生成了一个SQL文件把它导入数据库psql -U postgres -d spatial_db -f roads.sql这条命令里的几个参数值得细说。-s 4326表示Shapefile里的坐标是WGS84经纬度坐标系-I会在几何列上自动创建空间索引这个很重要忘了加后面查询会慢得让人怀疑人生-W UTF-8指定属性表的编码中文环境下特别关键不指定经常出现乱码尤其你的Shapefile是从国内测绘数据转出来的情况。还有一种方式是直接通过管道一条命令把数据灌进去我平时用得比较多shp2pgsql -s 4326 -I /path/to/layer.shp gis.building | psql -U postgres -d spatial_db这个命令不生成中间SQL文件直接把数据写进数据库。对于大文件来说更省磁盘空间而且不用担心生成的SQL文件太大导致导入失败。3.3 常用空间查询写法与坐标参考系的坑数据进去之后核心问题就是怎么查了。这里说一下我日常用得最多的几种空间查询写法。一是范围过滤。比如查一个点的周边500米范围内有哪些设施这种需求最常见SELECT name FROM gis.poi WHERE ST_DWithin( geom::GEOGRAPHY, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)::GEOGRAPHY, 500 );注意这个查询里用了ST_DWithin并且把geometry转换成了geography第三个参数500表示500米。ST_DWithin的优势是它可以利用空间索引快速筛选性能比先算距离再筛选的写法好很多。如果你写成ST_Distance(geom, target_geom) 500数据库会对每一行都做一次全量距离计算数据量一大就卡死。二是相交分析。比如有一个行政区划面要找出所有落在里面的地块SELECT b.parcel_id FROM gis.admin_boundary a JOIN gis.parcels b ON ST_Intersects(a.geom, b.geom) WHERE a.name 某区;ST_Intersects判断两个几何对象是否在空间上有交集它是空间连接操作里最常用的一个函数。在PostGIS的优化下这个操作会先通过空间索引圈定候选集然后再做精确计算性能非常有保障。三是计算面积和长度。用ST_Area和ST_Length可以计算面要素的面积和线要素的长度。这里有个我踩过好几次的坑如果是geometry类型且坐标系是4326算出来的面积单位是度不是平方米。要想得到平方米的结果得把坐标系转成投影坐标系或者转成geography类型SELECT name, ST_Area(geom::GEOGRAPHY) AS area_sqm FROM gis.parcels WHERE name 某地块;如果数据量很大每次都转换一次还是有开销的更优的做法是在建表时就直接把geom字段设计为geography类型或者额外存一列投影坐标根据不同的应用场景各取所需。四是坐标转换。从网上拿来的数据可能是4326的经纬度但很多地图底图要求用3857的Web墨卡托投影坐标这时候需要用ST_TransformSELECT ST_AsText(ST_Transform(geom, 3857)) FROM gis.roads WHERE id 1;ST_Transform和ST_SetSRID的区别要搞明白。ST_SetSRID只是给几何对象“贴标签”告诉数据库这个坐标是基于哪个坐标系的它不会改变坐标值。而ST_Transform是真正把坐标从一个坐标系换算到另一个坐标系坐标值会发生变化。这两者搞混是整个空间数据出错最常见的根源之一。3.4 空间查询性能的关键空间索引空间数据量一大性能好不好全看空间索引建得到不到位。没有空间索引的表每次空间查询都是全表扫描每条记录都要做一次昂贵的几何计算速度和龟爬差不多。有了空间索引数据库可以先用索引粗筛一遍只对少量候选记录做精确计算。PostGIS里的空间索引一般用GIST索引创建语句是CREATE INDEX idx_parcels_geom ON gis.parcels USING GIST(geom);在shp2pgsql导入时如果带了-I参数会在导入后自动创建这个索引所以导入时务必加上。如果表已经建好那就手动补上。有了索引之后还要注意SQL写法确保查询能真正利用到索引。用ST_DWithin、ST_Intersects这些函数时只要另一个参数是常量或参数数据库一般都能用上索引。但如果你写ST_Intersects(geom, ST_MakePoint(...))这种形式优化器也能处理。最怕的是先对坐标做计算再比较比如-- 这种写法用不上索引全表扫描慢 SELECT * FROM gis.poi WHERE ST_Distance(geom, target_geom) 100;虽然功能和ST_DWithin类似但因为语法形式不同数据库无法把它转为索引扫描。我一直跟团队强调能用ST_DWithin的场景绝不用ST_Distance加阈值判断。如果想确认查询有没有走索引用EXPLAIN ANALYZE看一下执行计划EXPLAIN ANALYZE SELECT name FROM gis.poi WHERE ST_DWithin( geom::GEOGRAPHY, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)::GEOGRAPHY, 500 );看执行计划里是不是有Index Scan using idx_poi_geom如果是Seq Scan就说明索引没生效需要回去检查SQL写法或索引是否创建成功。4. 从Oracle迁移过来的语法差异与踩坑实录4.1 数据类型的差异很多做GIS的团队是从Oracle Spatial转过来的因为Oracle的授权费用实在太高了。如果你也面临这个迁移下面的差异一定会用得上。Oracle里常用的NUMBER类型在PostgreSQL里对应的是NUMERIC或INTEGER、BIGINT。VARCHAR2在PostgreSQL里就是VARCHAR两者基本等价。Oracle的DATE类型默认包含时分秒PostgreSQL的DATE只包含日期如果想存时间得用TIMESTAMP这个一定要区分清楚。PostgreSQL还有Oracle没有的JSONB类型用起来非常方便。Oracle对JSON的支持比较晚性能也一般。从Oracle转过来的人第一次用JSONB字段普遍反馈是回不去了。Oracle的表常用SEQUENCE来实现自增主键通常是一个触发器加一个序列。PostgreSQL里可以用SERIAL或BIGSERIAL类型也可以直接用GENERATED AS IDENTITY。PostgreSQL 10以后推荐用GENERATED AS IDENTITY的方式更符合标准CREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100) );如果是从Oracle迁移过来Oracle的SEQUENCE.NEXTVAL可以映射成PostgreSQL里的NEXTVAL(sequence_name)双关语保持兼容。4.2 常用SQL语法差异对照语法差异是迁移过程中最容易遇到问题的地方。我整理了一个高频对照表两个数据库都跑过保证准确。功能Oracle写法PostgreSQL写法字符串拼接name || - || code或CONCAT(name, -, code)name || - || code或CONCAT(name, -, code)取前N条WHERE ROWNUM 10LIMIT 10分页OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY12cLIMIT 10 OFFSET 20当前时间SYSDATENOW()或CURRENT_TIMESTAMP空值排序默认空值最大默认空值最大但是可以NULLS FIRST/LAST控制字符串转数字TO_NUMBER(str)CAST(str AS NUMERIC)或str::NUMERIC去重统计COUNT(DISTINCT col)COUNT(DISTINCT col)字段自增SEQUENCE.NEXTVALSERIAL/IDENTITY插入不存在的记录更新MERGE INTO ...INSERT ... ON CONFLICT DO UPDATE条件逻辑DECODE(expr, val1, res1, res2)CASE WHEN ... THEN ... ELSE ... END这里面最坑的是ROWNUM和LIMIT的对应关系经常有人在迁移时直接把两者等价替换但Oracle的ROWNUM是在结果集生成时逐行赋予序号和LIMIT的语义有一些差别。一般简单场景等价没问题复杂的排序分页场景要特别小心建议用ROW_NUMBER() OVER (ORDER BY ...)把Oracle的写法整体重写一遍。还有一个使用频率很高的差异是隐式类型转换。Oracle在比较时会自动做很多隐式的类型转换PostgreSQL则严格很多。比如Oracle里直接写WHERE create_date 2024-01-01可能没问题但在PostgreSQL里如果create_date是TIMESTAMP类型这个字符串会被隐式转成日期导致的是精确匹配如果数据时间是2024-01-01 08:00:00这一条就查不出来。正确做法是用WHERE create_date 2024-01-01 AND create_date 2024-01-02。这是从一个老系统迁到PostgreSQL时最常见的业务bug来源。4.3 Oracle迁移到PostgreSQL值得注意的地方除了语法和数据类型的差异整个迁移过程还有很多不是SQL层面的坑我这里挑几个重点讲。第一个是存储过程的重写。Oracle的PL/SQL和PostgreSQL的PL/pgSQL虽然长得像但细节差异很大。比如Oracle里过程参数可以不带类型长度PostgreSQL里有些写法会不一样。异常处理的语法也不同Oracle是EXCEPTION WHEN OTHERS THENPostgreSQL也支持类似写法但具体异常名称和函数细节会有区别。迁移之前要先想清楚是继续在数据库里写业务逻辑还是把逻辑搬到应用层。我现在倾向于后者数据库尽量只做数据存取和简单约束复杂业务逻辑放在应用里迁移和扩展都更容易。第二个是二进制数据。Oracle有BLOBPostgreSQL对应的是BYTEA。看起来差不多但处理方式差别不小PostgreSQL的BYTEA对应用层更友好字符串转储时用\x十六进制格式。第三个是事务和锁。PostgreSQL的MVCC机制和Oracle有相似之处但锁粒度、死锁处理上的细节不同。大批量更新时两个数据库的行为表现都不一样最好先小规模测试再全量执行。第四个也是最容易忽视的编码问题。Oracle环境下很多老系统的字符集是ZHS16GBK导出的数据迁移到PostgreSQL通常UTF8后极容易出现中文乱码。迁移时的文本文件转码、CLIENT_ENCODING设置都要提前测好不要等迁移完才发现字段里全是问号。5. 日常维护与常见问题排查5.1 空间数据加载缓慢怎么排查用着用着有人会抱怨数据加载变慢了。空间数据的性能问题跟普通关系型数据的排查思路有相似点也有不一样的地方。第一步先看SQL执行计划。用EXPLAIN ANALYZE跑一下查询确认是不是发生了全表扫描有没有走空间索引。第二步检查索引是否失效。PostgreSQL一般不会主动把索引标记为不可用但如果表大量更新、删除、提交事务后没有及时清理索引的效率会下降。处理方式是执行VACUUM ANALYZE gis.parcels;VACUUM会清理已删除行的空间ANALYZE会更新统计信息让查询优化器作出更好的决策。这个命令在数据频繁更新的表上应该定期执行不是可有可无的事。第三步检查查询本身能不能改写。比如ST_Intersects(geom, geom)比在应用层做好裁剪后查两遍高效得多。尽量减少空间计算的行数让空间索引在第一时间把数据量降到足够小。第四步硬件层面。空间数据通常比普通业务数据占用更多存储因为几何对象要存整个坐标序列一个复杂的多边形可能有几万个坐标点。这时候内存够不够大、磁盘是不是SSD都会直接影响性能。像PostgreSQL的shared_buffers、work_mem这些参数也要根据机器配置适当调整。5.2 坐标系混乱的经典翻车场景坐标系问题在我接触过的所有空间数据项目里是出错率最高、排查难度最大的问题没有之一。这里列几个我真实遇到过的翻车场景。场景一数据是4326的经纬度但导入时忘记指定SRID结果数据库里存的SRID是0任何空间运算都得不到正确结果。这个很好排查执行SELECT ST_SRID(geom) FROM table LIMIT 1;看返回什么。场景二两个图层坐标SRID不同一个4326一个3857直接用ST_Intersects做叠加得到的结果完全错误。解决办法是先把两者统一到同一坐标系再做叠加SELECT a.name, b.name FROM gis.layer_a a JOIN gis.layer_b b ON ST_Intersects(a.geom, ST_Transform(b.geom, ST_SRID(a.geom)));场景三把带投影坐标的数据当经纬度用。有些数据的SRID标记为4547或4548国家大地坐标系坐标值看起来像是“几百米”这种尺度结果拿它当成经纬度画在地图上效果就是一团乱麻。这类数据的正确做法是先ST_Transform到4326再缩放到合理范围。场景四距离单位问题。4326坐标系下直接算两个点的ST_Distance结果是度一英里和一个经度的实际物理距离差别非常大。千万别用这个结果直接做展示或判断一定要转成GEOGRAPHY类型或者投影坐标系再算单位才是米。越是看起来经验丰富的人越容易在坐标系上栽跟头因为数据源头多样化一个不小心就是错位。建议所有入库数据都做一次SRID检查和统一校验把这个流程固化到ETL里能省掉后面大量的排查时间。5.3 关于备份恢复的几个建议空间数据库的数据量普遍都不小备份恢复的策略比普通业务库更需要注意。PostgreSQL最传统的备份工具是pg_dump对空间数据也适用。一个实例如下pg_dump -U postgres -F c -f spatial_db.dump spatial_db-F c表示输出为自定义格式这种格式支持压缩也支持恢复到指定表。恢复时用pg_restore -U postgres -d spatial_db spatial_db.dump但如果你有多个数据库要备份或者数据量达到几十GB甚至TB级别pg_dump的效率就不太行了。这时候我会用pg_basebackup做物理备份直接拷贝整个数据目录。物理备份的好处是恢复速度快但只能恢复到整个实例不能单独恢复某张表。对于空间数据我强烈建议在做完备份后定期尝试恢复到一台测试机上验证一下备份有效性。空间数据的表数量多、依赖关系复杂而且PostGIS相关元数据表也要一起备份否则恢复后的数据库执行空间查询时会提示找不到函数。实际恢复过程中还要确保目标库已经启用PostGIS扩展否则一堆依赖PostGIS的SQL全部报错。还有一个建议空间数据的几何字段很容易因为程序bug被写入脏数据比如自相交的多边形、重复的坐标点等。所以在备份策略之外最好定期做一些数据质量检查用ST_IsValid函数扫描一遍SELECT id, name FROM gis.parcels WHERE NOT ST_IsValid(geom) LIMIT 100;ST_IsValid在数据量大时会拖慢查询可以在非高峰时段跑一次全表检查把不合法数据修掉或者清理掉。别等到做空间分析出结果不对了才回头去查原始数据问题。我印象最深的一次是一个同事导入了几千个地块看起来一切正常但做面积统计时结果怎么都对不上。折腾了两个多小时才发现一部分几何对象有自相交问题导致面积计算重复计数。从那以后我的数据导入流程永远带一道ST_IsValid校验。最后分享一个我个人的使用体会PostgreSQL的空间数据能力学习曲线不算陡但涉及的知识点非常杂数据库本身要懂GIS原理要懂坐标系要知道甚至还要会一点点性能调优。入门阶段最忌讳的就是只装了个PostGIS能建表插数据就觉得完事了。实际项目中坐标系设置、空间索引创建、SQL写法优化每一项都会直接影响系统的成败。如果看完这篇内容你决定尝试用PostgreSQL做空间存储建议按这个顺序练手先建好环境和PostGIS然后用shp2pgsql导入一份公开的行政区域数据接着尝试用ST_Intersects做叠加分析再用ST_DWithin写一个周边查询。把这条链路走通你就已经超过大部分刚接触空间数据库的人了。后续再遇到具体问题翻官方文档也有底了因为你已经知道整个体系是怎么运转的。