云数据库 PostgreSQL postgis

这里说明 postgis 插件的简介及使用方法。

一、概述

postgis 是空间地理信息插件，提供几何类型、空间函数和空间索引，支持距离计算、范围查询、空间关系判断、几何运算、坐标转换等能力，并提供拓扑、栅格、三维几何等子扩展，适用于地图、位置服务、地理信息系统等场景。

二、支持版本

注意：说明：

注意：你可在控制台实例详情页查看当前实例的内核版本，或执行 SHOW tencentdb_version; 查询。

三、插件简介

postgis 由一个主扩展和多个可选子扩展组成：

四、环境准备

在目标数据库中执行以下语句创建主扩展：

postgres=> CREATE EXTENSION postgis;CREATE EXTENSION

注意：说明：

注意：postgis 无需预加载，创建后即可使用空间类型和函数。子扩展按需创建，用法见 子扩展 章节。

五、创建空间表与插入数据

geometry(Point, 4326) 表示 WGS84坐标系（经纬度）下的点类型。以下示例创建空间表并插入城市坐标：

postgres=> CREATE TABLE places ( id int, name text, geom geometry(Point, 4326));CREATE TABLEpostgres=> INSERT INTO places VALUES (1, 'Beijing', ST_GeomFromText('POINT(116.4 39.9)', 4326)), (2, 'Shanghai', ST_GeomFromText('POINT(121.5 31.2)', 4326)), (3, 'Guangzhou', ST_GeomFromText('POINT(113.3 23.1)', 4326));INSERT 0 3postgres=> SELECT id, name, ST_AsText(geom) AS geom FROM places ORDER BY id; id | name | geom—-+———–+——————- 1 | Beijing | POINT(116.4 39.9) 2 | Shanghai | POINT(121.5 31.2) 3 | Guangzhou | POINT(113.3 23.1)(3 rows)

注意：说明：

注意：ST_GeomFromText 用于从 WKT（Well-Known Text）文本构造几何对象，ST_AsText 将几何对象转换为 WKT 文本。

七、球面距离

使用 ST_DistanceSphere 计算两点之间的球面距离（单位：米）：

postgres=> SELECT round(ST_DistanceSphere( (SELECT geom FROM places WHERE name='Beijing'), (SELECT geom FROM places WHERE name='Shanghai'))::numeric, 0) AS distance_m; distance_m———— 1071287(1 row)

注意：说明：

注意：北京到上海的球面距离约为1071公里。

八、范围内查询

使用 ST_DWithin 查询指定范围内的对象。以下示例查找距离北京1100公里以内的城市：

postgres=> SELECT name FROM placesWHERE ST_DWithin(geom::geography, (SELECT geom FROM places WHERE name='Beijing')::geography, 1100000) AND name <> 'Beijing'; name———- Shanghai(1 row)

注意：说明：

注意：ST_DWithin 的第三个参数为距离（单位：米）。::geography 将几何类型转换为地理类型，使其按球面距离计算。

十、创建空间索引

为空间列创建 GiST 索引，加速空间查询：

postgres=> CREATE INDEX places_geom_idx ON places USING gist (geom);CREATE INDEX

十一、最近邻查询

使用 <-> 操作符按距离排序，查询最近的相邻对象。先补充更多城市数据：

postgres=> INSERT INTO places VALUES (4, 'Shenzhen', ST_GeomFromText('POINT(114.1 22.5)', 4326)), (5, 'Hangzhou', ST_GeomFromText('POINT(120.2 30.3)', 4326)), (6, 'Chengdu', ST_GeomFromText('POINT(104.1 30.6)', 4326)), (7, 'Wuhan', ST_GeomFromText('POINT(114.3 30.6)', 4326)), (8, 'Xian', ST_GeomFromText('POINT(108.9 34.3)', 4326));INSERT 0 5postgres=> SELECT name, round(ST_DistanceSphere(geom, (SELECT geom FROM places WHERE name='Shanghai'))::numeric, 0) AS dist_mFROM places WHERE name <> 'Shanghai'ORDER BY geom <-> (SELECT geom FROM places WHERE name='Shanghai') LIMIT 3; name | dist_m———-+——— Hangzhou | 159523 Wuhan | 690078 Beijing | 1071287(3 rows)

注意：说明：

注意：ORDER BY geom <-> … 使用空间索引进行最近邻查询，返回距离上海最近的3个城市，其中杭州距离约159公里。

十二、空间关系判断

使用 ST_Contains 判断几何对象是否包含另一个对象。以下示例判断城市是否位于华东区域（约东经115 – 125、北纬25 – 40）内：

postgres=> SELECT ST_Contains( ST_GeomFromText('POLYGON((115 25, 125 25, 125 40, 115 40, 115 25))', 4326), (SELECT geom FROM places WHERE name='Shanghai')) AS shanghai_in_area; shanghai_in_area—————— t(1 row)postgres=> SELECT ST_Contains( ST_GeomFromText('POLYGON((115 25, 125 25, 125 40, 115 40, 115 25))', 4326), (SELECT geom FROM places WHERE name='Chengdu')) AS chengdu_in_area; chengdu_in_area—————– f(1 row)

注意：说明：

注意：上海位于华东区域多边形内返回 t，成都位于区域外返回 f。

十四、线长度与面面积

ST_Length 配合 geography 类型计算线路的实际长度（单位：米）：

postgres=> SELECT round(ST_Length(ST_GeomFromText('LINESTRING(116.4 39.9, 117.2 39.1, 118.8 37.4, 120.3 36.1, 121.5 31.2)', 4326)::geography)::numeric, 0) AS line_length_m; line_length_m————— 1098957(1 row)

注意：说明：

注意：上述京沪沿线折线的球面长度约为1099公里。

ST_Area 配合 geography 类型计算多边形的实际面积（单位：平方米）：

postgres=> SELECT round(ST_Area(ST_GeomFromText('POLYGON((115 25, 125 25, 125 40, 115 40, 115 25))', 4326)::geography)::numeric, 0) AS area_m2; area_m2—————- 1559310606085(1 row)

注意：说明：

注意：上述华东区域多边形的面积约为156万平方公里。

十五、缓冲区与质心

ST_Buffer 生成几何对象周边的缓冲区：

postgres=> SELECT ST_GeometryType(ST_Buffer(ST_GeomFromText('POINT(116.4 39.9)', 4326), 5)) AS buffer_type, ST_NPoints(ST_Buffer(ST_GeomFromText('POINT(116.4 39.9)', 4326), 5)) AS num_points; buffer_type | num_points—————+———— ST_Polygon | 33(1 row)

注意：说明：

注意：ST_Buffer 以点为中心、5度为半径生成多边形缓冲区（33个顶点）。

ST_Centroid 计算几何对象的质心：

postgres=> SELECT ST_AsText(ST_Centroid(ST_GeomFromText('POLYGON((115 25, 125 25, 125 40, 115 40, 115 25))', 4326))) AS centroid; centroid————— POINT(120 32.5)(1 row)

十六、坐标转换

ST_Transform 在不同坐标系之间转换坐标。以下示例将 WGS84（4326）坐标转换为 Web Mercator（3857）：

postgres=> SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(116.4 39.9)', 4326), 3857)) AS web_mercator; web_mercator——————————————— POINT(12957588.728337044 4851421.175183359)(1 row)

十七、GeoJSON 输出

ST_AsGeoJSON 将几何对象输出为 GeoJSON 格式，便于与前端地图组件交互：

postgres=> SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(116.4 39.9)', 4326)) AS geojson; geojson——————————————- {"type":"Point","coordinates":[116.4,39.9]}(1 row)

十九、postgis_sfcgal（三维几何）

提供三维几何运算能力，如挤出、体积计算、三维相交判断：

postgres=> CREATE EXTENSION postgis_sfcgal;CREATE EXTENSIONpostgres=> SELECT round(ST_Volume(ST_MakeSolid(ST_Extrude(ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 0))'), 0, 0, 1)))::numeric, 2) AS volume; volume——– 0.50(1 row)postgres=> SELECT ST_3DIntersects( ST_GeomFromText('LINESTRING Z(0 0 0, 0 0 1)'), ST_GeomFromText('LINESTRING Z(0 0 0.5, 0 0 2)')) AS intersects_3d; intersects_3d————— t(1 row)

注意：说明：

注意：ST_Extrude 将平面三角形沿 Z 轴挤出1个单位，ST_Volume 计算挤出后的三维实体体积（面积0.5 × 高度1 = 0.50）；ST_3DIntersects 判断两条三维线段是否相交。

20、postgis_topology（拓扑）

提供拓扑数据管理能力，用于维护共享边界的空间数据（如行政区、地籍）。以下示例创建拓扑并向表添加拓扑几何列：

postgres=> CREATE EXTENSION postgis_topology;CREATE EXTENSIONpostgres=> SELECT CreateTopology('demo_topo', 4326); createtopology—————- 1(1 row)postgres=> CREATE TABLE land_parcels (id int, name text);CREATE TABLEpostgres=> SELECT AddTopoGeometryColumn('demo_topo', 'public', 'land_parcels', 'topo_geom', 'polygon'); addtopogeometrycolumn———————- 1(1 row)postgres=> SELECT * FROM topology.topology WHERE name = 'demo_topo'; id | name | srid | precision | hasz—-+———–+——+———–+—— 1 | demo_topo | 4326 | 0 | f(1 row)

注意：说明：

注意：CreateTopology 创建一个独立的拓扑（生成同名 schema，内含 node、edge、face 等表），AddTopoGeometryColumn 将拓扑管理的几何列添加到业务表。清理拓扑时使用 DropTopology('demo_topo')。

21、postgis_raster（栅格）

提供栅格数据类型与分析能力。以下示例将面几何转换为栅格并查询栅格属性：

postgres=> CREATE EXTENSION postgis_raster;CREATE EXTENSIONpostgres=> CREATE TABLE rasters (id int, rast raster);CREATE TABLEpostgres=> INSERT INTO rasters VALUES (1, ST_AsRaster(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'), 100, 100, '8BUI', 128));INSERT 0 1postgres=> SELECT id, ST_Width(rast) AS width, ST_Height(rast) AS height, ST_NumBands(rast) AS bands FROM rasters; id | width | height | bands—-+——-+——–+——- 1 | 100 | 100 | 1(1 row)postgres=> SELECT ST_Value(rast, 1, 50, 50) AS pixel_value FROM rasters WHERE id = 1; pixel_value————- 128(1 row)postgres=> SELECT ST_Intersects(rast, ST_GeomFromText('POINT(5 5)')) AS point_intersects FROM rasters WHERE id = 1; point_intersects—————— t(1 row)

注意：说明：

注意：ST_AsRaster 将10 × 10的面转换为100 × 100像素的8位无符号整型栅格（初始值128），ST_Value 取指定像素的值，ST_Intersects 判断栅格与几何是否相交。

23、Q：创建扩展后需要加载其他子扩展吗？

A：postgis 提供多个可选子扩展（如 postgis_topology、postgis_raster、postgis_sfcgal），按需创建即可。基础的点线面几何、距离计算、空间索引等功能包含在主扩展 postgis 中。

24、Q：删除 postgis 时提示有依赖对象？

A：请先删除依赖 postgis 的子扩展和对象（如 DROP EXTENSION postgis_sfcgal CASCADE;），再删除主扩展；创建过拓扑的，需先调用 DropTopology 清理拓扑。

25、相关参考

postgis 产品文档