PostGIS地理信息分析 - 实时数仓Hologres

PostGIS是数据库PostgreSQL的空间扩展，PostGIS可以提供空间对象、空间索引、空间操作函数和空间操作符等空间信息服务功能。本文为您介绍PostGIS在Hologres中的使用方法。

使用限制

不支持将Geometry、Geography两种数据类型作为主键（Primary Key）约束。
不支持PostGIS索引。
Hologres从 V1.3版本开始，大部分函数已通过Hologres自研引擎HQE支持，提供更好的性能；如果实例版本低于V1.3，将会使用PQE引擎支持，但性能稍弱。

安装PostGIS

在使用PostGIS之前，需要Superuser在DB内执行以下语句安装扩展包才可以正常使用。一个DB只需执行一次即可，如果创建新的DB，还需要再次执行如下语句。

-- 加载PostGIS插件
create extension if not exists postgis;

说明如需卸载extension请执行如下命令。

DROP extension  postgis;

在安装完成后，您可以执行如下SQL语句查看当前PostGIS版本。Hologres中目前使用的PostGIS版本是3.0.0。

select postgis_full_version();

创建并查询包含空间数据类型的表

目前在Hologres中使用PostGIS，支持两类空间数据类型，即几何类型（Geometry Type）和地理类型（Geography Type）。

在实际使用过程中，几何类型（Geometry Type）是使用较频繁的类型，如下内容将以几何类型为例，指导您创建包含Geometry数据类型的表并进行典型空间查询。更多关于地理类型（Geography Type）的参数和使用说明，请参见PostGIS Geography Type。

创建包含Geometry数据类型的表

说明创建包含几何类型的表时，您可以指定创建的几何体类型。包括Point、MultiPoint、Linestring、MultiLinestring、Polygon、MultiPolygon等。
- 创建不指定几何类型的表。
```
CREATE TABLE holo_gis_1 ( id int, geom geometry, PRIMARY KEY (id)) ;
```
  以上示例中，创建了包含几何类型的表，但是该表中不指定具体的几何类型。
- 创建指定几何体类型和SRID的表。
```
CREATE TABLE holo_gis_2 ( id int, geom geometry(point, 4326), PRIMARY KEY (id)) ;
```
  以上示例中，Geometry类型指定为Point，SRID为4326，SRID不指定默认为0。更多关于SRID释义，请参见PostGIS官方文档。
向表中插入数据
您可以通过如下方式向表中插入数据，其中，关于空间函数的使用说明，请参见空间函数。关于SRID释义，请参见PostGIS官方文档。
- 不指定SRID值。
```
insert into holo_gis_1 values (1, ST_GeomFromText('point(116 39)'));
```
- 指定SRID值。
```
insert into holo_gis_2 values (1, ST_GeomFromText('point(116 39)', 4326));
```

执行数据查询

您可以在创建表并插入数据后，执行矩形范围查询和多边形相交判定两种典型场景的查询。如下示例中，更多关于空间函数的使用说明，请参见空间函数。

矩形范围查询

不指定SRID值。

select st_astext(geom) from holo_gis_1
where ST_Contains(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), geom);

指定SRID值。

select st_astext(geom) from holo_gis_2 
where ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), 4326), geom);

多边形相交判定（在内部或在边界上）

不指定SRID值。

select st_astext(geom) from holo_gis_1
where ST_Contains(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), geom);

指定SRID值。

select st_astext(geom) from holo_gis_2 
where ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), 4326), geom);

空间函数

PostGIS为您提供了一些空间函数，每种函数可以将一种数据类型的值转换为另一种类型。空间函数说明具体见下表，关于函数语法，部分参数说明如下：

geom：表格中涉及的geom均表示一个GEOMETRY类型的值，或一个计算结果为GEOMETRY类型的表达式。
precision：表格中涉及的precision均表示一个INTEGER类型的值。坐标系geom将使用指定的精度1~20显示。如果未指定精度，则默认值为15。
index：表格中涉及的index为索引，均表示一个INTEGER类型的值。
srid：表格中涉及srid均表示一个INTEGER类型的值，它是空间参考标识符SRID。

更多关于空间函数的说明，请参见PostGIS官方文档。

Geometry Constructors函数


函数	函数语法	返回类型	说明	支持的引擎
ST_LineFromMultiPoint	ST_LineFromMultiPoint(geom)	GEOMETRY	ST_LineFromMultiPoint 返回输入多点几何体中的线串。点的顺序将保留。返回的几何体的空间参考系统标识符（SRID）与输入几何体的相同。	V1.3版本HQE支持。
ST_MakeEnvelope	ST_MakeEnvelope(xmin, ymin, xmax, ymax)ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid)	GEOMETRY子类型的EMR集群POINT、LINESTRING或者POLYGON。	ST_MakeEnvelope 包络返回一个几何，如下所示：如果输入坐标指定一个点，则返回的几何体是一个点。如果输入坐标指定一条线，则返回的几何为线串。否则，返回的几何为面，其中输入坐标指定框的左下角和右上角。如果提供，返回的几何体的空间参考系统标识符（SRID）值将设置为输入 SRID 值。	V1.3版本HQE支持。
ST_MakeLine	ST_MakeLine(geom1, geom2)	子类型 LINESTRING的GEOMETRY。	ST_MakeLine从输入几何体创建线串。	V1.3版本HQE支持。
ST_MakePoint	ST_MakePoint(x, y)	子类型POINT的 GEOMETRY。	ST_MakePoint返回其坐标值为输入值的点几何体。	V1.3版本HQE支持。
ST_Point	ST_Point(x, y)	子类型POINT的 GEOMETRY。	ST_Point从输入坐标值返回点几何体。	V1.3版本HQE支持。
ST_Polygon	ST_Polygon(linestring, srid)	子类型POLYGON的GEOMETRY。	ST_Polygon返回一个多边形几何体，其外部环形是输入线串，其值是空间参考系统标识符（SRID）的输入值。	V1.3版本HQE支持。

Geometry Accessors函数


函数	函数语法	返回类型	说明	支持的引擎
GeometryType	GeometryType(geom)	VARCHAR	GeometryType以字符串形式返回输入几何体的子类型。	V1.3版本HQE支持。
ST_Boundary	ST_Boundary(geom)	GEOMETRY	ST_Boundary返回输入几何体的边界，如下所示：如果输入几何为空（即不包含点），则按原样返回。如果输入几何是点或非空多点，则返回空几何集合。如果输入是线串或多线串，则返回包含边界上所有点的多点，多点可能为空）。如果输入是一个没有任何内环的面，则返回一个表示其边界的闭合线串。如果输入是具有内环的面，或者是多面，则返回多线串。多线串包含面积几何中所有环的所有边界作为闭合线串。	V1.3版本HQE支持。
ST_Dimension	ST_Dimension(geom)	INTEGER	ST_Dimension 返回输入几何体的固有维度。固有维度是几何体中定义的子类型的维度值。	V1.3版本HQE支持。
ST_Envelope	ST_Envelope(geom)	GEOMETRY	ST_Envelope 返回输入几何体的最小边界框，如下所示：如果输入几何体为空，则返回的几何体是输入几何体的副本。如果输入几何体的最小边界框退化为一个点，则返回的几何体是一个点。如果输入几何体的最小边界框是一维的，则返回两点线串。如果上述条件都不成立，则函数将返回一个顺时针方向的多边形，其顶点为最小边界框的角。返回的几何体的空间参考系统标识符（SRID）与输入几何体的相同。	V1.3版本HQE支持。
ST_ExteriorRing	ST_ExteriorRing(geom)	子类型 LINESTRING的GEOMETRY。	ST_ExteriorRing返回一个表示输入面外环的闭合线串。	V1.3版本HQE支持。
ST_GeometryN	ST_GeometryN(geom, index)	GEOMETRY	ST_GeometryN返回由输入几何体的输入索引指向的几何体，如下所示：当输入是点、线串或多边形时，如果索引等于一 (1)，则按原样返回几何体；如果索引不是一 (1)，则返回 null。如果输入是多点、多线串、多边形或几何体集合，则返回由输入索引指向的点、线串、多边形或几何体集合。索引是从 1 开始的。结果的空间参考系统标识符 (SRID) 与输入几何体的相同。	V1.3版本HQE支持。
ST_GeometryType	ST_GeometryType(geom)	VARCHAR	ST_GeometryType以字符串形式返回输入几何体的子类型。	V1.3版本HQE支持。
ST_InteriorRingN	ST_InteriorRingN(geom, index)	子类型LINESTRING的GEOMETRY。	ST_InteriorRingN返回与索引位置处输入多边形的内环相对应的闭合线串。	V1.3版本HQE支持。
ST_IsClosed	ST_IsClosed(geom)	BOOLEAN	如果输入几何体已闭合，则 ST_IsClosed 返回 true。以下规则定义闭合的几何体：输入的几何体是一个点或一个多点。输入几何体是一个线串，并且该线串的起点和终点是重合的。输入几何体是一个非空的多线串，并且其所有线串均已闭合。输入几何体是一个非空多边形，所有多边形的环都是非空的，并且所有环的起点和终点都是重合的。输入几何体是一个非空的多边形集合，并且其所有多边形均已闭合。输入几何体是一个非空几何体集合，并且其所有组件均已闭合。	V1.3版本HQE支持。
ST_IsCollection	ST_IsCollection(geom)	BOOLEAN	如果输入几何体为下列子类型之一，则 ST_IsCollection返回true：GEOMETRYCOLLECTION、MULTIPOINT、MULTILINESTRING或者MULTIPOLYGON。	V1.3版本HQE支持。
ST_IsEmpty	ST_IsEmpty(geom)	BOOLEAN	如果输入几何体是空的，则ST_IsEmpty返回true。如果一个几何体不包含点，则该几何体为空。	V1.3版本HQE支持。
ST_IspolyGonCW	ST_IsPolygonCCW(geom)	BOOLEAN	如果输入多边形或多多边形是逆时针的，则ST_IspolyGonCW返回true。如果输入几何是点、线串、多点或多线串，则返回true。对于几何体集合，如果集合中的所有几何体均为逆时针方向，ST_IspolygonCW将返回true。	V1.3版本HQE支持。
ST_IsSimple	ST_IsSimple(geom)	BOOLEAN	如果输入几何体是简单的，则ST_IsSimple返回 true。	V1.3版本HQE支持。
ST_NPoints	ST_NPoints(geom)	INTEGER	ST_NPoints 返回输入几何体中的点数。	V1.3版本HQE支持。
ST_NRings	ST_NRings(geom)	INTEGER	ST_NRings 返回输入几何体中的环形数。	V1.3版本HQE支持。
ST_NumGeometries	ST_NumGeometries(geom)	INTEGER	ST_NumGeometries返回输入几何体中的几何体数。	V1.3版本HQE支持。
ST_NumInteriorRings	ST_NumInteriorRings(geom)	INTEGER	ST_NumInteriorRings返回输入多边形几何体中的环形数。	V1.3版本HQE支持。
ST_NumPoints	ST_NumPoints(geom)	INTEGER	ST_NumPoints返回输入几何体中的点数。	V1.3版本HQE支持。
ST_PointN	ST_PointN(geom, index)	子类型POINT的 GEOMETRY。	ST_PointN返回由索引值指定的线串中的点。负索引值从线串的末尾开始倒计数，因此-1是最后一个点。	V1.3版本HQE支持。
ST_Points	ST_Points(geom)	子类型MULTIPOINT的 GEOMETRY。	ST_Points返回包含输入几何中所有非空点的多点几何。ST_Points不会移除输入中重复的点，包括环形几何的起点和终点。	V1.3版本HQE支持。
ST_StartPoint	ST_StartPoint(geom)	GEOMETRY	ST_StartPoint返回输入线串的第一个点。结果的空间参考系统标识符（SRID）值与输入几何体的相同。	V1.3版本HQE支持。
ST_X	ST_X(point)	DOUBLE	ST_X返回输入点的第一个坐标。	V1.3版本HQE支持。
ST_Y	ST_Y(point)	DOUBLE	ST_Y返回输入点的第二个坐标。	V1.3版本HQE支持。

Geometry Editors函数


函数	函数语法	返回类型	说明	支持的引擎
ST_AddPoint	ST_AddPoint(geom1, geom2)	GEOMETRY	ST_AddPoint返回一个线串几何体，它与添加了点的输入几何体相同。	V1.3版本HQE支持。
ST_Multi	ST_Multi(geom)	GEOMETRY带子类型MULTIPOINT、MULTILINESTRING、MULTIPOLYGON或者GEOMETRYCOLLECTION。	ST_multi将几何转换为相应的多类型。如果输入几何已经是多类型或几何集合，则返回其副本。如果输入几何体是点、线串或多边形，则分别返回包含输入几何体的多点、多线串或多边形。	V1.3版本HQE支持。
ST_RemovePoint	ST_RemovePoint(geom, index)	GEOMETRY	ST_RemovePoint返回一个线串几何体，该几何体已删除输入几何体在索引位置的点。索引是从零开始的。结果的空间参考系统标识符 (SRID) 与输入几何体的相同。	V1.3版本HQE支持。
ST_Reverse	ST_Reverse(geom)	GEOMETRY	ST_Reverse可反转线性和面积几何的折点顺序。对于点或多点几何，将返回原始几何的副本。对于几何体集合，ST_Reverse将反转集合中每个几何体的折点顺序。	V1.3版本HQE支持。
ST_setPoint	ST_SetPoint(geom1, index, geom2)	GEOMETRY	ST_setPoint返回与索引指定的输入线串位置相对于输入线串位置的更新坐标的线串。新坐标是输入点的坐标。	V1.3版本HQE支持。

Geometry Validation函数


函数	函数语法	返回类型	说明	支持的引擎
ST_IsValid	ST_IsValid(geom)	BOOLEAN	如果输入几何体有效，则ST_IsVALID返回true。说明通过PQE支持。	PQE

Spatial Reference System函数


函数	函数语法	返回类型	说明	支持的引擎
ST_SetSRID	ST_SetSRID(geom, srid)	GEOMETRY	ST_SetSRID返回一个与输入几何体相同的几何体，只不过使用空间参考系统标识符（SRID）的输入值进行了更新。	V1.3版本HQE支持。
ST_SRID	ST_SRID(geom)	INTEGER	ST_SRID返回输入几何体的空间参考系统标识符（SRID）。	V1.3版本HQE支持。

Geometry Input函数


函数	函数语法	返回类型	说明	支持的引擎
ST_GeomFromText	ST_GeomFromText(wkt_string) ST_GeomFromText(wkt_string, srid)	GEOMETRY。	ST_GeomFromText从输入几何体的已知文本（WKT）表示形式构造几何体对象。说明通过PQE支持。	PQE

Geometry Output函数


函数	函数语法	返回类型	说明	支持的引擎
ST_AsBinary	ST_AsBinary(geom)	BYTEA	ST_AsBinary 使用 ASCII 十六进制字符（0—9，A—F）返回输入几何体的十六进制已知二进制 (WKB) 表示形式。	V1.3版本HQE支持。
ST_AsEWKB	ST_AsEWKB(geom)	BYTEA	ST_AsEWKB 使用 ASCII 十六进制字符（0—9，A—F）返回输入几何体的扩展的已知二进制 (EWKB) 表示形式。	V1.3版本HQE支持。
ST_AsEWKT	ST_AsEWKT(geom)	VARCHAR	ST_AsEWKT 返回输入几何体的扩展的已知文本 (EWKT) 表示形式。	V1.3版本HQE支持。
ST_AsGeoJSON	ST_AsGeoJSON(geom)ST_AsGeoJSON(geom, precision)	VARCHAR	ST_AsGeoJSON 返回输入几何体的 GeoJSON 表示形式。有关 GeoJSON 的更多信息，请参阅 Wikipedia 中的 GeoJSON。	V1.3版本HQE支持。
ST_AsText	ST_AsText(geom)ST_AsText(geom, precision)	VARCHAR	ST_AsText 返回输入几何体的已知文本 (WKT) 表示形式。	V1.3版本HQE支持。

Spatial Relationships函数


函数	函数语法	返回类型	说明	支持的引擎
ST_Contains	ST_Contains(geom1, geom2)	BOOLEAN	如果第一个输入几何体包含第二个输入几何体，则 ST_Contains 返回 true。如果 B 中的每个点均为 A 中的一个点，并且其内部有非空相交区域，则几何体 A 包含几何体 B。ST_Contains(A, B) 与 ST_Within(B, A) 等效。说明通过PQE支持。	PQE
ST_ContainsProperly	ST_ContainsProperly(geom1, geom2)	BOOLEAN	如果两个输入几何都是非空的，并且第二个几何的所有点都是第一个几何的内部点，则 ST_ContainsProperly 返回 true。说明通过PQE支持。	PQE
ST_CoveredBy	ST_CoveredBy(geom1, geom2)	BOOLEAN	如果第一个输入几何体被第二个输入几何体覆盖，则 ST_CoveredBy 返回 true。如果几何体 A 和几何体 B 都是非空的，并且 A 中的每个点均为 B 中的一个点，则前者被后者覆盖。ST_CoveredBy(A, B) 与 ST_Covers(B, A) 等效。说明通过PQE支持。	PQE
ST_Covers	ST_Covers(geom1, geom2)	BOOLEAN	如果第一个输入几何体被第二个输入几何体覆盖，则 ST_Covers 返回 true。如果几何体 A 和几何体 B 都是非空的，并且 B 中的每个点均为 A 中的一个点，则前者覆盖了后者。ST_Covers(A, B) 与 ST_CoveredBy(B, A) 等效。说明通过PQE支持。	PQE
ST_Crosses	ST_Crosses(geom1, geom2)	BOOLEAN	如果两个输入几何体相互交叉，则 ST_Crosses返回 true。	不涉及
ST_Disjoint	ST_Disjoint(geom1, geom2)	BOOLEAN	如果两个输入几何体没有共同点，则 ST_Disjoint 返回 true。	不涉及
ST_DWithin	ST_DWithin(geom1, geom2, threshold)	BOOLEAN	如果两个输入几何体值之间的欧氏距离不大于阈值，则 ST_DWithin 返回 true。说明通过PQE支持。	PQE
ST_Equals	ST_Equals(geom1, geom2)	BOOLEAN	如果输入几何体在几何上相等，则 ST_Equals 返回 true。如果几何体具有相等的点集且其内部具有非空相交区域，则将几何体视为在几何上相等。	PQE
ST_Intersects	ST_Intersects(geom1, geom2)	BOOLEAN	如果两个输入几何体至少有一个共同点，则 ST_Intersects 返回 true。说明通过PQE支持。	PQE
ST_Touches	ST_Touches(geom1, geom2)	BOOLEAN	如果两个输入几何体接触，则 ST_Touches 返回 true。如果两个几何体是非空的、相交并且没有共同的内部点，则它们是接触的。	PQE
ST_Within	ST_Within(geom1, geom2)	BOOLEAN	如果第一个输入几何体在第二个输入几何体中，则 ST_Within 返回 true。例如，如果几何体 A 中的每个点均为几何体 B 中的一个点，并且其内部有非空相交区域，则几何体 A 在几何体 B 中。ST_Within(A, B) 与 ST_Contains(B, A) 等效。说明通过PQE支持。	PQE

Measurement函数


函数	函数语法	返回类型	说明	支持的引擎
ST_Angle	ST_Angle(geom1, geom2, geom3) ST_Angle(geom1, geom2, geom3, geom4)	DOUBLE	ST_Angle返回顺时针方向测量的点之间的角度（返回值以弧度为单位且在 [0, 2π) 范围内）。例如：如果输入三个点，则测量P1P2P3三个点顺时针组成的角度。如果输入四个点，则测量有向线P1P2和P3P4顺时针形成的角度。如果输入为两条线平行（即，P1等于P2，或P3等于P4），则返回 null。	PQE
ST_Area	ST_Area(geom)	DOUBLE	ST_Area 返回输入几何体的笛卡尔面积。面积单位与用于表示输入几何体坐标的单位相同。对于点、线串、多点和多线串，此函数返回 0。对于几何体集合，它返回集合中几何体的面积之和。	V1.3版本HQE支持。
ST_Azimuth	ST_Azimuth(point1, point2)	DOUBLE	ST_Azimuth返回两个输入点的基于北向的笛卡尔方位。	V1.3版本HQE支持。
ST_Distance	ST_Distance(geom1, geom2)	DOUBLE	ST_Distance返回两个输入几何体值之间的欧氏距离。	V1.3版本HQE支持。
ST_Length	ST_Length(geom)	DOUBLE	ST_Length返回输入线性几何体的笛卡尔长度。长度单位与用于表示输入几何体坐标的单位相同。对于点、多点和平面几何体，此函数返回零 (0)。当输入为几何体集合时，此函数返回集合中的几何体长度之和。	V1.3版本HQE支持。
ST_Perimeter	ST_Perimeter(geom)	DOUBLE	ST_Perimeter返回输入平面几何体的笛卡尔周长（边界长度）。周长单位与用于表示输入几何体坐标的单位相同。对于点、多点和线性几何体，此函数返回零 (0)。当输入为几何体集合时，此函数返回集合中的几何体周长之和。	V1.3版本HQE支持。

Overlay函数


函数	函数语法	返回类型	说明	支持的引擎
ST_Intersection	ST_Intersection(geom1, geom2)	GEOMETRY。	返回几何之间的交集。	V1.3版本HQE支持。

Geometry Processing函数


函数	函数语法	返回类型	说明	支持的引擎
ST_Buffer	ST_Buffer(geography,float8)	GEOMETRY	用于指定与围绕其计算缓冲区的几何图形实例的距离。说明通过PQE支持。	PQE
ST_Convexhull	ST_ConvexHull(geom)	GEOMETRY	ST_convexhull 返回一个几何，该几何表示输入几何中包含的非空点的凸壳。	V1.3版本HQE支持。
ST_Simplify	ST_Simplify(geom, tolerance)	GEOMETRY	ST_Simplify中使用带有给定容差的 Ramer-Douglas-Peucker 算法返回输入几何的简化副本。输入几何的拓扑可能不会保留。有关该算法的详细信息，请参阅拉默-道格拉斯-普克算法在维基百科。	V1.3版本HQE支持。

Bounding Box函数


函数	函数语法	返回类型	说明	支持的引擎
ST_XMax	ST_XMax(geom)	DOUBLE	ST_XMax返回输入几何体的最大的第一个坐标。说明通过PQE支持。	PQE
ST_XMin	ST_XMin(geom)	DOUBLE	ST_XMin返回输入几何体的最小的第一个坐标。说明通过PQE支持。	PQE
ST_YMax	ST_YMax(geom)	DOUBLE	ST_YMax返回输入几何体的最大的第二个坐标。说明通过PQE支持。	PQE
ST_YMin	ST_YMin(geom)	DOUBLE	ST_YMin返回输入几何体的最小的第二个坐标。说明通过PQE支持。	PQE

Linear Referencing函数


函数	函数语法	返回类型	说明	支持的引擎
ST_LineInterpolatePoint	ST_LineInterpolatePoint(geom, fraction)	子类型POINT的GEOMETRY。	ST_LineInterpolatePoint为插值点，即返回一个距离线起点为小数距离的点。	V1.3版本HQE支持。

空间函数最佳实践

阿里云为您提供了空间函数使用方法的最佳实践，详情请参见使用空间函数查询数据方法。