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

  1. 创建包含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官方文档
  2. 向表中插入数据

    您可以通过如下方式向表中插入数据,其中,关于空间函数的使用说明,请参见空间函数。关于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));
  3. 执行数据查询

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

    • 矩形范围查询
      • 不指定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支持。

空间函数最佳实践

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