PostGIS学习笔记

时间：2024年7月26日

学习来源：https://postgis.net/docs/manual-dev/zh_Hans/

1 概述

PostGIS 是一种扩展功能，适用于 PostgreSQL 对象关系数据库系统，它允许将 GIS（地理信息系统）对象存储在数据库中。PostGIS 包含对基于 GiST 的 R-Tree 空间索引的支持，以及用于分析和处理 GIS 对象的功能。

2 数据管理

2.1 空间数据模型

2.1.1 OGC几何图形

开放地理空间联盟 (OGC) 定义了简单功能访问标准 (SFA)，以提供地理空间数据模型。它定义了几何的基本空间类型，以及操纵和转换几何值以执行空间分析任务的操作。 PostGIS 将 OGC 几何模型实现为 PostgreSQL 数据类型 geometry 和 geography。

几何图形在二维笛卡尔平面上对形状进行建模。形状的大小和位置由坐标指定。每个坐标都有 X 和 Y 坐标轴值，用于确定其在平面上的位置。形状由点和线构造，点由单个坐标定义，线由两个坐标定义。

坐标可能包含可选的Z和M纵坐标值。Z纵坐标通常用于表示高程。M纵坐标包含一个测量值，该值可能表示时间或距离。如果几何值中存在Z或M值，则必须为几何中的每个点定义这些值。如果几何具有 Z 或 M 纵坐标，则坐标尺寸为 3D;如果它同时具有 Z 和 M，则坐标尺寸为 4D。

几何值与 空间参考系统指示它所嵌入的坐标系。空间参考系由几何SRID编号标识。X轴和Y轴的单位由空间参考系确定。平面参考系统中，X 和 Y 坐标通常表示东向和北向，而在 大地测量测量系统中，它们表示经度和纬度。SRID 0 表示一个具有无单位的无限笛卡尔平面。

① 点 Point

点是表示坐标空间中单个位置的 0 维几何图形。

1
2
3

POINT (1 2)
POINT Z (1 2 3)
POINT ZM (1 2 3 4)

② 线串 LineString

线串是由连续的线段序列形成的一维线。每条线段由两个点定义，一条线段的终点形成下一条线段的起点。OGC 有效的线串具有零个或两个或多个点，但 PostGIS 也允许单点线串。线串可以与自身交叉（自相交）。如果起点和终点相同，则线串是闭合的。如果线串不自相交，则它是简单的。

1	LINESTRING (1 2, 3 4, 5 6)

(a) 和(c) 是简单的LINESTRING；(b) 和 (d) 不简单；(c) 是一个封闭的线性环。

③ 线性环 LinearRing

线性环是一个既封闭又简单的线串。第一个点和最后一个点必须相等，并且线不得自相交。

1	LINEARRING (0 0 0, 4 0 0, 4 4 0, 0 4 0, 0 0 0)

④ 多边形 Polygon

多边形是由外部边界(壳)和零个或多个内部边界(洞)分隔的二维平面区域。每个边界都是线性环。

1	POLYGON ((0 0 0,4 0 0,4 4 0,0 4 0,0 0 0),(1 1 0,2 1 0,2 2 0,1 2 0,1 1 0))

POLYGON以下条件是有效的：

多边形边界环（外壳环和内孔环）很简单（不交叉或自接触）。因此，多边形不能有切割线、尖峰或环。这意味着多边形孔必须表示为内环，而不是外环自接触（所谓的“倒孔”）。
边界环不交叉
边界环可以在点上接触，但只能作为切线（即不在一条线上）
内环包含在外环中
多边形内部连接简单（即环不得以将多边形分成多个部分的方式接触）

(h)和(i) 是有效的POLYGON。(j-m) 是无效的。(j)可以表示为有效的MULTIPOLYGON。

⑤ 多点 MultiPoint

多点是点的集合。

1	MULTIPOINT ( (0 0), (1 2) )

⑥ 多线 MultiLingString

多线是线串的集合，如果多线的每个元素都已闭合，则该多线闭合。

1	MULTILINESTRING ( (0 0,1 1,1 2), (2 3,3 2,5 4) )

(e) 和(f) 是简单的 MULTILINESTRING；(g)不简单。

其余形状略。

2.1.2 SQL/MM-曲线

略

2.1.3 WKT和WKB

略

2.2 几何数据类型

PostGIS 通过定义称为geometry的 PostgreSQL 数据类型来实现 OGC 简单要素模型。这允许将空间要素建模为用geometry类型的列定义的表行。

geometry数据类型是 不透明的，这意味着所有访问都是通过调用几何值的函数来完成的。函数允许创建几何对象、访问或更新所有内部字段以及计算新的几何值。

EWKB 和 EWKT 用于 PostGIS 数据对象的“规范形式”。对于输入，二进制数据的规范形式是 EWKB，对于文本数据，接受 EWKB 或 EWKT。这允许通过使用 ::geometry将 HEXEWKB 或 EWKT中的文本值转换为几何值来创建几何值。对于输出，二进制的规范形式是 EWKB，对于文本，规范形式是 HEXEWKB（十六进制编码的 EWKB）。

例如，此过程使用来自EWKT文本值的强制转换生成几何图形，并使用HEXWKB的规范格式输出它们：

SELECT 'SRID=4;POINT(0 0)'::geometry;
  geometry
  ----------------------------------------------------
  01010000200400000000000000000000000000000000000000

总结：

geometry数据类型在Postgre中的存储形式是一个16进制编码。例如：
- 01010000200400000000000000000000000000000000000000
WKT和EWKT是一个字符串。例如：
- LINESTRING(190131 224148,190871 228134)
- POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))
WKB和EWKB是一个二进制文件

2.3 地理数据类型

PostGIS geography数据类型为“地理”坐标（有时称为“大地”坐标，或lat/lon或lon/lat）表示的空间要素提供本机支持。地理坐标是以角度单位（度）表示的球坐标。

与几何数据类型类似，地理数据通过空间参考系统标识符 (SRID) 与空间参考系统相关联。可以使用在spatial_ref_sys 表中定义的任何大地测量（基于经/纬度）空间参考系统。

2.4 空间参考系统

空间参考系统 (SRS)（也称为坐标参考系统（CRS））定义了如何将几何图形引用到地球表面。

PostGIS（和许多空间系统）使用称为SRID的整数来引用空间参考系统。

2.4.1 spatial_ref_sys表

PostGIS使用的SPATIAL_REF_SYS表是一个符合 OGC 标准的数据库表，用于定义可用的空间参考系统。它包含数字 SRID 和坐标系的文本描述。

spatial_ref_sys表定义为：

CREATE TABLE spatial_ref_sys (
  srid       INTEGER NOT NULL PRIMARY KEY,
  auth_name  VARCHAR(256),
  auth_srid  INTEGER,
  srtext     VARCHAR(2048),
  proj4text  VARCHAR(2048)
)

**srid**：唯一标识数据库内空间参考系统(SRS)的整数代码。
**auth_name**：该参考系统引用的标准或标准机构的名称。
**auth_srid**：由auth_name中引用的权威机构定义的空间参考系统的ID。
**srtext**：已知空间参考系统的文本表示形式。

PROJCS["NAD83 / UTM Zone 10N",
  GEOGCS["NAD83",
        DATUM["North_American_Datum_1983",
          SPHEROID["GRS 1980",6378137,298.257222101]
        ],
        PRIMEM["Greenwich",0],
        UNIT["degree",0.0174532925199433]
  ],
  PROJECTION["Transverse_Mercator"],
  PARAMETER["latitude_of_origin",0],
  PARAMETER["central_meridian",-123],
  PARAMETER["scale_factor",0.9996],
  PARAMETER["false_easting",500000],
  PARAMETER["false_northing",0],
  UNIT["metre",1]
]

**proj4text**：PostGIS使用PROJ库提供坐标转换功能。

2.4.2 用户自定义空间参考系统

略

2.5 加载数据

2.5.1 使用SQL加载数据

如果空间数据可以转换为文本表示（如 WKT 或 WKB），那么使用SQL可能是将数据导入 PostGIS 的最简单方法。通过使用psql SQL 实用程序加载 SQL INSERT语句的文本文件，可以将数据批量加载到 PostGIS/PostgreSQL 中。

SQL加载文件（例如roads.sql ）可能看起来像这样：

BEGIN;
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (1,'LINESTRING(191232 243118,191108 243242)','Jeff Rd');
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (2,'LINESTRING(189141 244158,189265 244817)','Geordie Rd');
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (3,'LINESTRING(192783 228138,192612 229814)','Paul St');
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (4,'LINESTRING(189412 252431,189631 259122)','Graeme Ave');
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (5,'LINESTRING(190131 224148,190871 228134)','Phil Tce');
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (6,'LINESTRING(198231 263418,198213 268322)','Dave Cres');
COMMIT;

SQL文件可以使用psql方法加载到PostgreSQL中:

1	psql -d [database] -f roads.sql

2.5.2 使用Shapefile加载器

略

3 空间查询

暂略

4 常用函数

完整手册：https://postgis.net/docs/manual-dev/zh_Hans/reference.html#PostGIS_Types

4.1 数据类型

box2d — 表示二维边界框的类型。
box3d — 表示三维边界框的类型。
geometry — 表示具有平面坐标系的空间要素的类型。
geometry_dump — 用于描述复杂几何形状部分的复合类型。
geography — 使用大地（椭球）坐标系表示空间特征的类型。

4.2 几何输入

这些函数从各种文本或二进制格式创建几何对象。

4.2.1 WKT文本

① ST_GeomFromText

从 OGC 已知的文本表示（WKT）构造 PostGIS ST_Geometry 对象。

1 2	geometry ST_GeomFromText(text WKT); geometry ST_GeomFromText(text WKT, integer srid);

示例：

SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)');

------------------
010200000003000000E44...

SELECT ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))');

------------------
010300000001000000050000006285...

② ST_PolygonFromText

使用给定的 SRID 从 WKT 创建几何图形。如果未给出 SRID，则默认为 0。如果 WKT 不是多边形，则返回 null。

1 2	geometry ST_PolygonFromText(text WKT); geometry ST_PolygonFromText(text WKT, integer srid);

示例：

SELECT ST_PolygonFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))');
st_polygonfromtext
------------------
010300000001000000050000006...

SELECT ST_PolygonFromText('POINT(1 2)') IS NULL as point_is_notpoly;

point_is_not_poly
------------------
t

其余略

4.2.2 WKB二进制文件

① ST_GeogFromWKB

从已知的二进制几何表示 (WKB) 或扩展的已知的二进制 (EWKB) 创建地理实例。

1	geography ST_GeogFromWKB(bytea wkb);

示例：

SELECT ST_AsText(
ST_GeogFromWKB(E'\\001\\002\\000\\000\\000\\002\\000\\000\\000\\037\\205\\353Q\\270~\\\\\\300\\323Mb\\020X\\231C@\\020X9\\264\\310~\\\\\\300)\\\\\\217\\302\\365\\230C@')
);

st_astext
------------------------------------------------------
 LINESTRING(-113.98 39.198,-113.981 39.195)
(1 row)

② ST_GeomFromWKB

从已知的二进制几何表示 (WKB) 和可选的 SRID 创建几何实例。

1 2	geometry ST_GeomFromWKB(bytea geom); geometry ST_GeomFromWKB(bytea geom, integer srid);

示例：

SELECT ST_AsEWKT(ST_GeomFromWKB(E'\\001\\002\\000\\000\\000\\002\\000\\000\\000\\037\\205\\353Q\\270~\\\\\\300\\323Mb\\020X\\231C@\\020X9\\264\\310~\\\\\\300)\\\\\\217\\302\\365\\230C@',4326)
);

st_asewkt
------------------------------------------------------
 SRID=4326;LINESTRING(-113.98 39.198,-113.981 39.195)
(1 row)

SELECT
  ST_AsText(
        ST_GeomFromWKB(
          ST_AsEWKB('POINT(2 5)'::geometry)
        )
  );
 st_astext
------------
 POINT(2 5)
(1 row)

4.2.3 其他格式

ST_GeomFromGeoJSON

将几何图形的 geojson 表示形式作为输入并输出 PostGIS 几何对象。

geometry ST_GeomFromGeoJSON(text geomjson);

geometry ST_GeomFromGeoJSON(json geomjson);

geometry ST_GeomFromGeoJSON(jsonb geomjson);

注：GeoJSON是一种用于编码各种地理数据结构的数据。GeoJSON对象可以表示几何、特征或特征集合。GeoJSON支持以下几何类型：点、线、面、多点、多线、多面和几何集合。

例如：

{
  "type": "Feature",
  "geometry": {
    "type": "Point",
    "coordinates": [125.6, 10.1]
  },
  "properties": {
    "name": "Dinagat Islands"
  }
}

{
  "type": "Feature",
  "geometry": {
    "type": "LineString",
    "coordinates": [
      [102.0, 0.0],
      [103.0, 1.0],
      [104.0, 0.0],
      [105.0, 1.0],
    ]
  },
  "properties": {
    "name": "Dinagat Islands"
  }
}

示例：

SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}') As wkt;

wkt
-----
0101000020E61000009279E40F061E48C0F2B0506B9A1F3440

4.3 几何输出

这些函数将几何对象转换为各种文本或二进制格式。

4.3.1 WKT文本

ST_AsText

返回几何/地理的 OGC 已知文本 (WKT) 表示形式。可选的 maxdecimaldigits 参数可用于限制输出坐标中小数点后的位数（默认为 15）。

text ST_AsText(geometry g1);

text ST_AsText(geometry g1, integer maxdecimaldigits = 15);

text ST_AsText(geography g1);

text ST_AsText(geography g1, integer maxdecimaldigits = 15);

逆变换为ST_GeomFromText。

示例：

SELECT ST_AsText('01030000000100000005000000000000000000
000000000000000000000000000000000000000000000000
F03F000000000000F03F000000000000F03F000000000000F03
F000000000000000000000000000000000000000000000000');

st_astext
--------------------------------
POLYGON((0 0,0 1,1 1,1 0,0 0))

4.3.2 WKB二进制文件

略

4.3.3 其他格式

ST_AsGeoJSON

返回一个几何体作为GeoJSON中的 “geometry” 对象，或者返回一行作为GeoJSON中的 “feature”对象。

text ST_AsGeoJSON(record feature, text geom_column="", integer maxdecimaldigits=9, boolean pretty_bool=false, text id_column='');

text ST_AsGeoJSON(geometry geom, integer maxdecimaldigits=9, integer options=8);

text ST_AsGeoJSON(geography geog, integer maxdecimaldigits=9, integer options=0);

示例：

SELECT st_asgeojson('0102000020E610000008000000E361B66CDA565D400C9966B0CA8D434062B48CAFD8565D40B34AF635C68D43402CEE2A10D8565D40AFDCC181C48D4340277840D1D7565D40AA91C9D5C38D43407AF62772D7565D40B8F69EE1C28D43401EE1FB02D7565D403BC84E0AC28D43402993E785D6565D406B5B0F54C18D43409C267FCED4565D40F48CDFA0BF8D4340') as geojson;

输出：

{
  "type":"LineString",
  "coordinates":[
    [117.357081583,39.107748079],
    [117.356975448,39.107611413],
    [117.356937448,39.107559414],
    [117.356922448,39.107538913],
    [117.356899776,39.107509806],
    [117.35687327,39.107484139],
    [117.356843449,39.107462413],
    [117.356738686,39.107410535]
  ]
}

4.4 几何构造

ST_LineFromMultiPoint — 从多点几何创建线串。

1	geometry ST_LineFromMultiPoint(geometry aMultiPoint);

示例：

SELECT ST_AsEWKT(ST_LineFromMultiPoint('MULTIPOINT(1 2 3, 4 5 6, 7 8 9)')  ));

--result--
LINESTRING(1 2 3,4 5 6,7 8 9)

ST_MakePoint — 创建 2D、3DZ 或 4D 点。

geometry ST_MakePoint(float x, float y);

geometry ST_MakePoint(float x, float y, float z);

geometry ST_MakePoint(float x, float y, float z, float m);

ST_Point — 创建具有 X、Y 和 SRID 值的点。

1
2
3

geometry ST_Point(float x, float y);

geometry ST_Point(float x, float y, integer srid=unknown);

这是 ST_MakePoint 的 SQL-MM 等效项，仅采用 X 和 Y。对于大地坐标，X是经度， Y是纬度。

示例：几何

1	SELECT ST_Point( -71.104, 42.315);

示例：地理，（使用 :: 转换语法）

1	SELECT ST_Point( -71.104, 42.315, 4326)::geography;

ST_MakeLine — 从Point, MultiPoint,或LineString geometries创建LineString。

geometry ST_MakeLine(geometry geom1, geometry geom2);

geometry ST_MakeLine(geometry[] geoms_array);

geometry ST_MakeLine(geometry set geoms);

示例：

SELECT ST_AsText( ST_MakeLine(ST_Point(1,2), ST_Point(3,4)) );

st_astext
---------------------
LINESTRING(1 2,3 4)

ST_MakePolygon — 从壳和可选的孔列表创建多边形。

1
2
3

geometry ST_MakePolygon(geometry linestring);

geometry ST_MakePolygon(geometry outerlinestring, geometry[] interiorlinestrings);

ST_Collect — 从一组几何图形创建 GeometryCollection 或 Multi* 几何图形。

geometry ST_Collect(geometry g1, geometry g2);

geometry ST_Collect(geometry[] g1_array);

geometry ST_Collect(geometry set g1field);

示例：合并2D点。

SELECT ST_AsText( ST_Collect( ST_GeomFromText('POINT(1 2)'),
        ST_GeomFromText('POINT(-2 3)') ));

st_astext
----------
MULTIPOINT((1 2),(-2 3))

合并两条线段（数组形式）：

SELECT ST_AsText(  ST_Collect(
                ARRAY[ ST_GeomFromText('LINESTRING(1 2, 3 4)'),
                        ST_GeomFromText('LINESTRING(3 4, 4 5)') ] )) As wktcollect;

--wkt collect --
MULTILINESTRING((1 2,3 4),(3 4,4 5))