企业官网建设流程全解析

空间数据库这个方向，不少人一开始是绕道走的。早期做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在这个领域基本是综合最优解。就算你之前用过Oracle Spatial，转PostgreSQL也不会太痛苦，这是我要在后面详细说的一个重点。

2. 搭建一套可用的空间数据库环境

2.1 版本选择与安装

PostgreSQL的版本更新挺快的，我接触过的大多数生产环境用的是14、15、16这几个版本。新版本在性能、索引机制、查询优化器上都有改进，建议直接用较新的稳定版，不要用太老的版本，否则很多SQL写法和扩展版本会受限。

安装方式要看你用的系统。Windows下直接下载安装包，一路下一步就行。不过Windows下要注意，安装过程中会让你设置超级用户postgres的密码，这个密码一定别用太简单的，因为后面所有数据库连接都需要它。Linux下推荐用系统包管理器安装，比如在Ubuntu下：

sudo apt update sudo apt install postgresql postgresql-contrib

Red 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_Transform：

SELECT 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语法差异对照

语法差异是迁移过程中最容易遇到问题的地方。我整理了一个高频对照表，两个数据库都跑过，保证准确。

这里面最坑的是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 THEN，PostgreSQL也支持类似写法，但具体异常名称和函数细节会有区别。迁移之前要先想清楚：是继续在数据库里写业务逻辑，还是把逻辑搬到应用层。我现在倾向于后者，数据库尽量只做数据存取和简单约束，复杂业务逻辑放在应用里，迁移和扩展都更容易。

第二个是二进制数据。Oracle有BLOB，PostgreSQL对应的是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写一个周边查询。把这条链路走通，你就已经超过大部分刚接触空间数据库的人了。后续再遇到具体问题，翻官方文档也有底了，因为你已经知道整个体系是怎么运转的。