Chapter 4. 数据管理

Table of Contents

4.1. 空间数据模型

4.1.1. OGC几何图形

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

几何是一种抽象类型。几何值属于其具体子类型之一。子类型表示各种类型和各种维度的几何形状。其中包括基本类型, 线, 线性环, 以及集合类型多点, 多线, 多面几何对象集合简单 功能访问 - 第一部分:通用体系结构v1.2.1 为结构PolyhedralSurface, TriangleTIN添加了子类型。

几何图形在二维笛卡尔平面上对形状进行建模。PolyhedralSurface, Triangle,和 TIN 也可以表示三维空间中的形状。形状的大小和位置由坐标指定。每个坐标都有 X 和 Y 坐标轴值,用于确定其在平面上的位置。形状由点和线构造,点由单个坐标定义,线由两个坐标定义。

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

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

几何维度是几何类型的属性。点类型的维度为 0,线性类型的维度为 1, 多边形类型的维度为 2。集合具有最大元素维度的维度。

几何值可以为 。空值不包含顶点(对于原子几何类型)或不包含元素(对于集合)。

几何值的一个重要属性是它们的空间范围边界框,OGC模型将其称为包络。这是包含几何坐标的2维或3维框。它是在坐标空间中表示几何图形范围并检查两个几何图形是否相互作用的有效方法。

几何模型允许评估拓扑空间关系,如Section 5.1.1, “维度扩展九交模型(Dimensionally Extended 9-Intersection Model)”中所述。为了支持这一点,为每种几何类型定义了内部, 外部边界的概念。几何图形在拓扑上是封闭的,因此它们总是包含它们的边界。边界是比几何体本身小一维的几何体。

OGC几何模型为每种几何类型定义了有效性规则。这些规则确保几何值表示现实情况(例如,可以指定一个具有孔的多边形位于外壳之外,但从几何角度来看这是没有意义的,因此是无效的)。PostGIS 也允许存储和操作无效的几何值。这允许在需要时检测和修复它们。请参阅Section 4.4, “几何有效性验证”

4.1.1.1. 点(Point)

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

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

4.1.1.2. 线串(LineString)

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

LINESTRING (1 2, 3 4, 5 6)

4.1.1.3. 线性环(LinearRing)

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

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

4.1.1.4. 多边形(Polygon)

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

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))

4.1.1.5. 多点(MultiPoint)

多点是点的集合。

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

4.1.1.6. 多线(MultiLineString)

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

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

4.1.1.7. 多面(MultiPolygon)

多面是非重叠、不相邻多边形的集合。集合中的多边形只能在有限数量的点处接触。

MULTIPOLYGON (((1 5, 5 5, 5 1, 1 1, 1 5)), ((6 5, 9 1, 6 1, 6 5)))

4.1.1.8. 几何集合(GeometryCollection)

几何集合是几何的异构(混合)集合。

GEOMETRYCOLLECTION ( POINT(2 3), LINESTRING(2 3, 3 4))

4.1.1.9. 多面体曲面(PolyhedralSurface)

多面体曲面是共享一些边的斑块或刻面的连续集合。每个面片都是一个平面多边形。如果多边形坐标具有 Z 纵坐标,则表面是三维的。

POLYHEDRALSURFACE Z (
  ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
  ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)),
  ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
  ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
  ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),
  ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )

4.1.1.10. 三角形(Triangle)

三角形是由三个不同的非共线顶点定义的多边形。因为三角形是一个多边形,所以它由四个坐标指定,第一和第四个相等。

TRIANGLE ((0 0, 0 9, 9 0, 0 0))

4.1.1.11. TIN

TIN是一组不重叠的三角形,表示了一个三角不规则网络(Triangulated Irregular Network)的结构。

TIN Z ( ((0 0 0, 0 0 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 0 0 0)) )

4.1.2. SQL/MM第3部分-曲线

ISO/IEC 13249-3 SQL多媒体-空间标准 (SQL/MM)扩展了 OGC SFA 以定义包含弯曲几何图形的子类型。SQL/MM 类型支持 XYM、XYZ 和 XYZM。

[Note]

SQL-MM 实现中的所有浮点比较 按照指定的公差执行,目前为 1E-8。

4.1.2.1. 圆弧串(CircularString)

圆弧串是基本曲线类型,类似于线性世界中的线串。单个圆弧段由三个点指定:起点和终点(第一个和第三个)以及圆弧上的其他点。 要指定闭合圆,起点和终点相同,中点是圆直径上的相对点(即圆弧的中心)。在弧序列中,前一个弧的终点是下一个弧的起点,就像线串的线段一样。 这意味着圆弧串必须具有大于 1 的奇数个点。

CIRCULARSTRING(0 0, 1 1, 1 0)

CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0)

4.1.2.2. 复合曲线(CompoundCurve)

复合曲线是一条连续曲线,可以包含圆弧段和线性段。意味着除了具有格式良好的组件之外,每个组件(最后一个除外)的终点必须与后续组件的起点重合。

COMPOUNDCURVE( CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1))

4.1.2.3. 曲线多边形(CurvePolygon)

曲线多边形与多边形相似,因为它们有一个外环和零个或多个内环。区别在于多边形环是线串,而曲线多边形环是复合曲线或圆弧串。

从PostGIS 1.4开始,PostGIS 现在支持曲线多边形上的复合曲线。

CURVEPOLYGON(
  CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),
  (1 1, 3 3, 3 1, 1 1) )

示例:具有由圆弧串和线串组成的复合曲线定义的外壳的曲线多边形,以及由圆弧串定义的孔

CURVEPOLYGON(
  COMPOUNDCURVE( CIRCULARSTRING(0 0,2 0, 2 1, 2 3, 4 3),
                 (4 3, 4 5, 1 4, 0 0)),
  CIRCULARSTRING(1.7 1, 1.4 0.4, 1.6 0.4, 1.6 0.5, 1.7 1) )

4.1.2.4. 多曲线(MultiCurve)

多曲线是曲线的集合,可以包括线串、圆弧串或复合曲线。

MULTICURVE( (0 0, 5 5), CIRCULARSTRING(4 0, 4 4, 8 4))

4.1.2.5. 多曲面(MultiSurface)

多曲面是曲面的集合,可以是(线性的)多边形或曲线多边形。

MULTISURFACE(
  CURVEPOLYGON(
    CIRCULARSTRING( 0 0, 4 0, 4 4, 0 4, 0 0),
    (1 1, 3 3, 3 1, 1 1)),
  ((10 10, 14 12, 11 10, 10 10), (11 11, 11.5 11, 11 11.5, 11 11)))

4.1.3. WKT和WKB

OGC SFA规范定义了两种标准格式,用于表示供外部使用的几何值。已知文本(WKT)和已知二进制文件(WKB)。WKT和WKB都包含有关定义对象的类型和坐标的信息。

已知文本(WKT)提供空间数据的标准字符表示形式。以下是空间对象的 WKT 表示形式示例:

  • POINT(0 0)

  • POINT Z (0 0 0)

  • POINT ZM (0 0 0 0)

  • POINT EMPTY

  • LINESTRING(0 0,1 1,1 2)

  • LINESTRING EMPTY

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

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

  • MULTIPOINT Z ((0 0 0),(1 2 3))

  • MULTIPOINT EMPTY

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

  • MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))

  • GEOMETRYCOLLECTION(POINT(2 3),LINESTRING(2 3,3 4))

  • GEOMETRYCOLLECTION EMPTY

WKT输入和输出由函数ST_AsTextST_GeomFromText提供:

text WKT = ST_AsText(geometry);
geometry = ST_GeomFromText(text WKT, SRID);

例如,从WKT和SRID创建并插入空间对象的语句如下所示:

INSERT INTO geotable ( geom, name )
  VALUES ( ST_GeomFromText('POINT(-126.4 45.32)', 312), 'A Place');

已知二进制 (WKB) 是空间数据二进制数据(字节数组)的可移植且准确的表示形式。空间对象的 WKB 表示如下所示:

  • WKT: POINT(1 1)

    WKB: 0101000000000000000000F03F000000000000F03

  • WKT: LINESTRING (2 2, 9 9)

    WKB: 0102000000020000000000000000000040000000000000004000000000000022400000000000002240

WKB 的输入和输出由以下函数ST_AsBinaryST_GeomFromWKB提供:

bytea WKB = ST_AsBinary(geometry);
geometry = ST_GeomFromWKB(bytea WKB, SRID);

例如,用于创建和插入WKB的空间对象如下所示:

INSERT INTO geotable ( geom, name )
  VALUES ( ST_GeomFromWKB('\x0101000000000000000000f03f000000000000f03f', 312), 'A Place');

4.2. 几何数据类型

PostGIS 通过定义称为geometry的 PostgreSQL 数据类型来实现 OGC 简单要素模型。它使用内部类型代码表示所有几何子类型(请参阅 GeometryTypeST_GeometryType)。这允许将空间要素建模为用geometry类型的列定义的表行。

geometry数据类型是 不透明的,这意味着所有访问都是通过调用几何值的函数来完成的。 函数允许创建几何对象、访问或更新所有内部字段以及计算新的几何值。PostGIS 支持 OGC 简单功能访问 - 第二部分: SQL 选项(SFS) 规范中指定的所有功能以及许多其他功能。 请参阅Chapter 7, PostGIS 参考手册以获取完整的函数列表。

[Note]

PostGIS 遵循 SFA 标准,在空间函数前面加上前缀“ST_”。 这表示“空间和时间”,但标准时间部分尚未开发。 相反,它可以解释为“空间类型”。

SFA 标准指定空间对象包含空间参考系统标识符 (SRID)。创建插入数据库的空间对象时需要 SRID(可能默认为 0)。 请参阅 ST_SRIDSection 4.5, “空间参考系统”

为了使几何查询更加高效,PostGIS 定义了不同类型的空间索引。有关更多信息,请参见 Section 4.9, “空间索引”Section 5.2, “使用空间索引”

4.2.1. PostGIS EWKB 和 EWKT

OGC SFA 规范最初仅支持 2D 几何形状, 并且几何 SRID 不包含在输入/输出表示中。OGC SFA 规范 1.2.1(与 ISO 19125 标准一致) 增加了对 3D (ZYZ) 和测量(XYM 和 XYZM)坐标的支持, 但仍然不包括 SRID 值。

由于这些限制,PostGIS定义了扩展的EWKB和EWKT格式。它们提供 3D(XYZ 和 XYM)和 4D (XYZM) 坐标支持,并包括 SRID 信息。 包含所有几何信息允许 PostGIS 使用 EWKB 作为记录格式 (例如,在转储文件中)。

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

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

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

PostGIS EWKT 输出与 OGC WKT 有一些不同之处:

  • 对于 3DZ 几何形状,省略了 Z 限定符:

    OGC: POINT Z (1 2 3)

    EWKT: POINT (1 2 3)

  • 对于 3DM 几何形状,包括 M 限定符:

    OGC: POINT M (1 2 3)

    EWKT: POINTM (1 2 3)

  • 对于 4D 几何形状,省略了 ZM 限定符:

    OGC: POINT ZM (1 2 3 4)

    EWKT: POINT (1 2 3 4)

EWKT避免过度指定维度以及OGC/ISO格式可能发生的不一致,例如:

  • POINT ZM (1 1)

  • POINT ZM (1 1 1)

  • POINT (1 1 1 1)

[Caution]

PostGIS扩展格式向上兼容OGC格式,因此,每个有效的 OGC WKB/WKT 也是有效的 EWKB/EWKT。但是,将来这可能会有所不同,如果 OGC 以与 PosGIS 定义冲突的方式扩展格式。因此,您不应该依赖这种兼容性!

空间对象的 EWKT 文本表示的示例如下:

  • POINT(0 0 0) -- XYZ

  • SRID=32632;POINT(0 0) -- XY 与 SRID

  • POINTM(0 0 0) -- XYM

  • POINT(0 0 0 0) -- XYZM

  • SRID=4326;MULTIPOINTM(0 0 0,1 2 1) -- XYM 与 SRID

  • MULTILINESTRING((0 0 0,1 1 0,1 2 1),(2 3 1,3 2 1,5 4 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))

  • MULTIPOLYGON(((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)),((-1 -1 0,-1 -2 0,-2 -2 0,-2 -1 0,-1 -1 0)))

  • GEOMETRYCOLLECTIONM( POINTM(2 3 9), LINESTRINGM(2 3 4, 3 4 5) )

  • MULTICURVE( (0 0, 5 5), CIRCULARSTRING(4 0, 4 4, 8 4) )

  • POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)), ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)), ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)), ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )

  • TRIANGLE ((0 0, 0 10, 10 0, 0 0))

  • TIN( ((0 0 0, 0 0 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 0 0 0)) )

可以使用以下函数使用这些格式的输入和输出:

bytea EWKB = ST_AsEWKB(geometry);
text EWKT = ST_AsEWKT(geometry);
geometry = ST_GeomFromEWKB(bytea EWKB);
geometry = ST_GeomFromEWKT(text EWKT);

例如,使用 EWKT 创建和插入 PostGIS 空间对象的语句为:

INSERT INTO geotable ( geom, name )
  VALUES ( ST_GeomFromEWKT('SRID=312;POINTM(-126.4 45.32 15)'), 'A Place' )

4.3. 地理数据类型

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

PostGIS几何类型的基础是平面。平面上两点之间的最短路径是直线。几何函数(面积、距离、长度、交点等)是使用线性向量和笛卡尔平面计算的。这简化了实施并运行得更快,但对于地球球面以上的数据是不准确的。

PostGIS 地理数据类型基于球面模型。球体上两点之间的最短路径是一个大圆弧。地理上的函数(面积、距离、长度、交点等)是使用球体上的弧计算的。通过考虑世界的球体形状,函数可提供更准确的结果。

由于基础数学更复杂,因此为地理类型定义的函数少于为几何类型定义的函数。随着时间的推移,随着新算法的添加,地理类型的功能将扩展。作为一种解决方法,可以在几何和地理类型之间来回转换。

与几何数据类型类似,地理数据通过空间参考系统标识符 (SRID) 与空间参考系统相关联。可以使用在spatial_ref_sys 表中定义的任何大地测量(基于经/纬度)空间参考系统。(在 PostGIS 2.2 之前,地理类型仅支持 WGS 84 大地测量 (SRID:4326))。您可以添加自己的自定义大地测量空间参考系统,如Section 4.5.2, “用户自定义空间参考系统”中所述。

测量函数返回的单位(例如,ST_Distance, ST_Length, ST_Perimeter, ST_Area)与ST_DWithin参数中给出的距离之间的空间参考系统单位为米。

4.3.1. 创建地理表

您可以使用带有地理类型列的 CREATE TABLE SQL 语句创建一个表来存储地理数据。 以下示例创建一个表,其中包含存储 WGS84 大地坐标系 (SRID 4326) 中的 2D LineString 的地理列:

CREATE TABLE global_points (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    location geography(POINT,4326)
  );

地理类型支持两个可选的类型修饰符:

  • 空间类型修饰符用于调节列中允许的形状的类型和维度。根据值,空间类型可以是点、线串、面、多点、多线、多面、几何集合。地理类型不支持曲线、三角形或多面体曲面。通过将后置 Z、M 和 ZM 附加到类型修饰符,可以解决坐标维度的约束问题。 例如,“LINESTRINGM”只允许三维线串,第三个轴是M。 同样,“POINTZM”需要四维(XYZM)数据。

  • SRID 修饰符将空间参考系统 SRID 限制为特定数字。如果省略,SRID 默认为 4326(WGS84 大地测量),并且所有计算均使用 WGS84 执行。

以下是生成包含地理列的表的示例:

  • 生成一个具有二维点地理的表,默认情况下SRID为4326(WGS84 经度/纬度):

    CREATE TABLE ptgeogwgs(gid serial PRIMARY KEY, geog geography(POINT) );
  • 生成具有 NAD83 经纬度二维点地理的表:

    CREATE TABLE ptgeognad83(gid serial PRIMARY KEY, geog geography(POINT,4269) );
  • 创建一个包含 3D (XYZ) POINT 和默认 SRID 4326 的表:

    CREATE TABLE ptzgeogwgs84(gid serial PRIMARY KEY, geog geography(POINTZ,4326) );
  • 生成具有二维线串地理的表,SRID 默认值为 4326:

    CREATE TABLE lgeog(gid serial PRIMARY KEY, geog geography(LINESTRING) );
  • 使用 SRID 4267(NAD 1927 经纬度)创建包含二维多边形地理的表:

    CREATE TABLE lgeognad27(gid serial PRIMARY KEY, geog geography(POLYGON,4267) );

地理字段在geography_columns系统视图中注册。 您可以查询geography_columns视图并看到该表已列出:

SELECT * FROM geography_columns;

创建空间索引的工作方式与创建几何列相同。 PostGIS 将注意到列类型是 GEOGRAPHY,并创建适当的基于球体的索引,而不是用于 GEOMETRY 的常用平面索引。

-- Index the test table with a spherical index
CREATE INDEX global_points_gix ON global_points USING GIST ( location );

4.3.2. 使用地理表

您可以采用与几何相同的方式将数据插入地理表中。如果几何数据具有 SRID 4326,则几何数据将自动转换为地理类型。也可以使用 EWKT和EWKB 格式 以指定地理值。

-- Add some data into the test table
INSERT INTO global_points (name, location) VALUES ('Town', 'SRID=4326;POINT(-110 30)');
INSERT INTO global_points (name, location) VALUES ('Forest', 'SRID=4326;POINT(-109 29)');
INSERT INTO global_points (name, location) VALUES ('London', 'SRID=4326;POINT(0 49)');

spatial_ref_sys表中列出的任何大地测量(经/纬度)空间参考系统都可以指定为地理 SRID。如果使用非大地坐标系,则会产生错误。

-- NAD 83 lon/lat
SELECT 'SRID=4269;POINT(-123 34)'::geography;
                    geography
----------------------------------------------------
 0101000020AD1000000000000000C05EC00000000000004140
-- NAD27 lon/lat
SELECT 'SRID=4267;POINT(-123 34)'::geography;
                    geography
----------------------------------------------------
 0101000020AB1000000000000000C05EC00000000000004140
-- NAD83 UTM zone meters - gives an error since it is a meter-based planar projection
SELECT 'SRID=26910;POINT(-123 34)'::geography;

ERROR:  Only lon/lat coordinate systems are supported in geography.

查询和测量功能以米为单位。因此,距离参数以米为单位(面积为平方米)。

-- A distance query using a 1000km tolerance
SELECT name FROM global_points WHERE ST_DWithin(location, 'SRID=4326;POINT(-110 29)'::geography, 1000000);

通过计算一架从西雅图飞往伦敦的飞机(LINESTRING(-122.33 47.606, 0.0 51.5) 表示的大圆航线)飞行过程中距离雷克雅未克(POINT(-21.96 64.15))的距离,您可以看到地理学的作用(绘制路线)。

地理类型计算出雷克雅未克与西雅图和伦敦之间的大圆飞行路径之间的球体上的真实最短距离为 122.235 公里。

-- Distance calculation using GEOGRAPHY
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)'::geography);
   st_distance
-----------------
 122235.23815667

几何类型在平面世界地图上计算雷克雅未克之间的笛卡尔距离以及西雅图和伦敦之间的直线,这是没有意义的。计算结果的标称单位是“度”,但它与点之间的真实角度差并不对应,称其为“度”本身是不准确的。

-- Distance calculation using GEOMETRY
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.96 64.15)'::geometry);
      st_distance
--------------------
 13.342271221453624

4.3.3. 何时使用地理数据类型

地理数据类型允许您将数据存储在经度/纬度坐标中,但有代价:在地理上定义的函数比在几何上定义的函数少;定义的那些函数需要更多 CPU 时间来执行。

您选择的数据类型应由正在构建的应用程序的预期工作区域确定。您的数据是跨越全球还是大片大陆区域,还是州、县或直辖市的本地数据?

  • 如果数据包含在较小的区域中,您可能会发现,就可用的性能和功能而言,选择合适的投影并使用 GEOMETRY 是最佳解决方案。

  • 如果您的数据是整个地球或大陆,您将能够构建一个系统,而无需担心地理投影的细节。保存经度/纬度数据并使用地理中定义的函数。

  • 如果您不了解投影,并且不想了解它们,并且您准备接受地理中可用功能的限制,那么使用地理可能比使用几何更容易。只需将数据加载为经度/纬度,然后从那里开始。

有关地理和几何之间的支持比较,请参见Section 13.11, “PostGIS函数支持矩阵”。有关地理函数的简要列表和说明,请参见 Section 13.4, “PostGIS 地理支持函数”

4.3.4. 地理高级常见问题解答

4.3.4.1.

你是用球体还是椭球体计算?

默认情况下,所有距离和面积计算都在椭球体中执行。将局部区域的计算结果与投影良好的平面上的结果进行比较。对于大面积,椭球体计算比投影平面上的任何其他计算都更准确。

所有地理函数都可以选择使用球体计算,方法是将最终布尔参数设置为“FALSE”。这将在一定程度上加快计算速度,特别是对于几何形状非常简单的情况。

4.3.4.2.

日期变更线和极点呢?

所有计算都没有日期变更线或极点的概念。 由于坐标是球体(经度/纬度),因此从计算的角度来看,穿过日期变更线的形状与其他任何形状没有什么不同。

4.3.4.3.

您可以处理的最长圆弧是多少?

我们使用大圆弧作为两点之间的“插值线”。 这意味着任何两点实际上以两种方式连接起来,具体取决于您沿着大圆行进的方向。我们所有的代码都假设这些点是由沿着大圆的两条路径中“较短的”一条连接起来的。因此,弧度超过 180 度的形状将无法正确建模。

4.3.4.4.

为什么计算欧洲/俄罗斯/大地理区域的面积这么慢?

因为多边形大得离谱。大面积不好有两个原因 一个原因是边界框很大,因此无论您运行什么查询,索引都倾向于拉取特征。另一个原因是顶点计数很大,并且测试(距离、包含)函数必须至少遍历顶点一次,通常为 N(其中 N 是另一个要素的顶点计数)。

与 GEOMETRY 一样,我们建议您在具有非常大的多边形,但在小区域中执行查询时,将几何数据“非规范化”为较小的块,以便索引可以有效地子查询对象的各个部分,这样查询就不必每次都提取整个对象。请参阅ST_Subdivide 函数文档。仅仅因为您*可以*将整个欧洲存储在一个多边形中并不意味着您*应该*。

4.4. 几何有效性验证

PostGIS 符合开放地理空间联盟 (OGC) 的简单要素规范。 该标准定义了简单有效几何概念。 这些定义允许简单要素几何模型以一致且明确的方式表示空间对象,从而支持高效计算。 (注:OGC SF 和 SQL/MM 对简单和有效的定义相同。)

4.4.1. 简单几何

简单几何是指没有异常几何点(例如自交或自相切)的几何。

作为 0 维几何对象,POINT 本质上是简单

MULTIPOINT简单的,如果没有两个坐标(POINT)的坐标值相同(具有相同的坐标值)。

如果LINESTRING不两次穿过同一点(端点除外),则该 LINESTRING 很简单。如果简单线串的端点相同,则称为闭合线并称为线性环。

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

(a)

(b)

(c)

(d)

仅当MULTILINESTRING 的所有元素都很简单 并且任意两个元素之间的唯一交集发生在两个元素边界上的点时,MULTILINESTRING 才是简单的。

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

(e)

(f)

(g)

POLYGON是由线性环形成的,所以常见的多边形几何总是简单的。

测试几何图形是否简单,使用 ST_IsSimple函数:

SELECT
   ST_IsSimple('LINESTRING(0 0, 100 100)') AS straight,
   ST_IsSimple('LINESTRING(0 0, 100 100, 100 0, 0 100)') AS crossing;

 straight | crossing
----------+----------
 t        | f

一般来说,PostGIS 函数不要求几何参数很简单。简单性主要用作定义几何有效性的基础。这也是某些类型的空间数据模型的要求(例如,线性网络通常不允许线交叉)。使用 ST_UnaryUnion 可以使多点和线性几何变得简单。

4.4.2. 有效的几何形状

几何有效性主要适用于二维几何形状(POLYGONMULTIPOLYGON)。有效性是由允许多边形几何形状明确地模拟平面区域的规则定义的。

POLYGON以下条件是有效的:

  1. 多边形边界环(外壳环和内孔环)很简单(不交叉或自接触)。因此,多边形不能有切割线、尖峰或环。这意味着多边形孔必须表示为内环,而不是外环自接触(所谓的“倒孔”)。

  2. 边界环不交叉

  3. 边界环可以在点上接触,但只能作为切线(即不在一条线上)

  4. 内环包含在外环中

  5. 多边形内部连接简单(即环不得以将多边形分成多个部分的方式接触)

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

(h)

(i)

(j)

(k)

(l)

(m)

MULTIPOLYGON以下条件是有效的:

  1. 其元素 POLYGON有效

  2. 元素不重叠(即其内部不得相交)

  3. 元素仅在点处接触(即不沿线接触)

(n)是有效的MULTIPOLYGON(o)(p)无效。

(n)

(o)

(p)

这些规则意味着有效的多边形几何也很简单

对于线性几何,唯一的有效性规则是LINESTRING必须至少有两个点并且具有非零长度(或等效地,至少有两个不同的点。请注意,非简单(自相交)线是有效的。

SELECT
   ST_IsValid('LINESTRING(0 0, 1 1)') AS len_nonzero,
   ST_IsValid('LINESTRING(0 0, 0 0, 0 0)') AS len_zero,
   ST_IsValid('LINESTRING(10 10, 150 150, 180 50, 20 130)') AS self_int;

 len_nonzero | len_zero | self_int
-------------+----------+----------
 t           | f        | t

POINTMULTIPOINT 几何形状没有有效性规则。

4.4.3. 有效性管理

PostGIS允许创建和存储有效和无效的几何图形。这允许检测并标记或修复无效的几何图形。在某些情况下,OGC有效性规则比预期更严格(例如零长度线串和带有倒孔的多边形。)

PostGIS提供的许多功能都依赖于几何参数有效的假设。例如,计算在多边形外部定义有孔的多边形的面积,或者从非简单边界线构造多边形是没有意义的。假设有效的几何输入允许函数更有效地运行,因为它们不需要检查拓扑正确性。(值得注意的例外是零长度线和具有反转的多边形通常可以正确处理。)此外,如果输入有效,大多数 PostGIS 函数都会生成有效的几何输出。 这使得 PostGIS 功能可以安全地链接在一起。

如果在调用 PostGIS 函数时遇到意外错误消息 (例如“GEOS Intersection() 抛出了一个错误!”),应首先确认函数参数是否有效。如果无效,请考虑使用以下技术之一来验证您正在处理的数据。

[Note]

如果函数报告有效输入错误,那么您可能在 PostGIS 或其使用的库之一中发现错误,您应该将此报告给 PostGIS 项目。如果 PostGIS 函数为有效输入返回无效几何图形,情况也是如此。

要测试几何图形是否有效,请使用ST_IsValid函数。 执行以下操作:

SELECT ST_IsValid('POLYGON ((20 180, 180 180, 180 20, 20 20, 20 180))');
-----------------
 t

有关几何无效的性质和位置的信息由ST_IsValidDetail函数确定:

SELECT valid, reason, ST_AsText(location) AS location
    FROM ST_IsValidDetail('POLYGON ((20 20, 120 190, 50 190, 170 50, 20 20))') AS t;

 valid |      reason       |                  location
-------+-------------------+---------------------------------------------
 f     | Self-intersection | POINT(91.51162790697674 141.56976744186045)

在某些情况下,需要自动纠正无效的几何形状。 使用 ST_MakeValid 函数来执行此操作。 (ST_MakeValid 是空间函数的一种情况,确实允许无效输入!)

测试复杂几何的刚度需要花费大量CPU时间,因此默认情况下,PostGIS 在加载几何时不会验证几何。如果不信任数据源,则可以使用检查约束对表进行强制验证。执行以下操作:

ALTER TABLE mytable
  ADD CONSTRAINT geometry_valid_check
        CHECK (ST_IsValid(geom));

4.5. 空间参考系统

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

  • 地理(geodetic)SRS,使用角坐标(经度和纬度),直接映射到地球表面。

  • 投影(projected)SRS使用数学投影变换将球形地球的表面“压平”到平面上。 它以允许直接测量距离、面积和角度等量的方式分配位置坐标。 坐标系是笛卡尔坐标系,这意味着它具有定义的原点和两个垂直轴(通常面向北和东)。 每个投影的 SRS 使用规定的长度单位(通常是米或英尺)。投影的 SRS 的适用范围可能会受到限制,以避免变形并适合定义的坐标范围。

  • 本地(local)SRS是不参考地球表面的笛卡尔坐标系。PostGIS 指定 SRID 值为 0。

所使用的空间参考系统存在许多差异。一般空间参考系统在欧洲石油调查组的EPSG database中进行了标准化。为方便起见,PostGIS(和许多空间系统)使用称为SRID的整数来引用空间参考系统。

几何通过其SRID值与空间参考系相关联,可由ST_SRID访问。可以使用ST_SetSRID指定几何图形的 SRID 值。某些几何构造函数允许提供 SRID (如ST_PointST_MakeEnvelope)。EWKT格式支持带有前缀SRID=n;的SRID。

处理几何图形对(例如叠加关系函数)的空间函数要求输入几何图形位于同一空间参考系统中(具有相同的 SRID)。可以使用ST_TransformST_TransformPipeline将几何数据转换为不同的空间参考系统。从函数返回的几何图形具有与输入几何图形相同的SRS。

4.5.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

该参考系统引用的标准或标准机构的名称。例如,“EPSG”是有效的auth_name

auth_srid

auth_name中引用的权威机构定义的空间参考系统的ID。对于EPSG,这是EPSG代码。

srtext

已知空间参考系统的文本表示形式。WKT SRS表示的一个示例是:

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]
]

有关SRS WKT的详细信息,请参阅 OGC 标准参考系的已知文本表示

proj4text

PostGIS使用PROJ库提供坐标转换功能。proj4text列包含特定SRID的PROJ坐标定义字符串。例如:

+proj=utm +zone=10 +ellps=clrk66 +datum=NAD27 +units=m

欲了解更多信息,请访问PROJ 网站spatial_ref_sys.sql文件包含所有 EPSG 投影的srtextproj4text定义。

检索用于变换的空间参考系统定义时,PostGIS使用以下策略:

  • 如果 auth_nameauth_srid存在(非空),使用基于这些条目(如果存在)的 PROJ SRS。

  • 如果srtext存在,请使用它创建SRS(如果可能)。

  • 如果proj4text存在,请使用它创建SRS(如果可能)。

4.5.2. 用户自定义空间参考系统

PostGISspatial_ref_sys表包含由PROJ投影库处理的 3000 多个最常见的空间参考系统定义。但有很多坐标系它不包含。如果您具有有关空间参考系统的所需信息,则可以将 SRS 定义添加到表中。或者,如果您熟悉 PROJ 构造,则可以定义自己的自定义空间参考系统。请记住,大多数空间参考系统都是区域性的,在其预期范围之外使用时没有任何意义。

http://spatialreference.org/可用于查找不在 PostGIS 核心集中的空间参考系统

一些常用的空间参考系统包括:4326 - WGS 84 经纬度, 4269 - NAD 83 经纬度, 3395 - WGS 84 世界墨卡托投影, 2163 - 美国国家地图等面积投影以及 60 个 WGS84 UTM 区域。UTM 区域是最理想的测量区域之一,但仅覆盖 6 度区域。(要确定您感兴趣的区域使用哪个UTM区域,请参阅 utmzone PostGIS plpgsql帮助程序函数。)

美国各州使用国家平面空间参考系统(基于米或英尺)- 通常每个州有一个或两个。 大多数基于米的数据都在核心集中,但许多基于英尺的数据或 ESRI 创建的数据需要从spatialreference.org复制。

您甚至可以定义非基于地球的坐标系,比如 火星2000这个火星坐标系是非平面的(以球度为单位),但是您可以将其与 geography类型一起使用,以米而不是度为单位获得长度和邻近测量值。

以下是使用未分配的SRID和以美国为中心的兰伯特等角投影的PROJ定义加载自定义坐标系的示例:

INSERT INTO spatial_ref_sys (srid, proj4text)
VALUES ( 990000,
  '+proj=lcc  +lon_0=-95 +lat_0=25 +lat_1=25 +lat_2=25 +x_0=0 +y_0=0 +datum=WGS84 +units=m +no_defs'
);

4.6. 空间表

4.6.1. 创建空间表

您可以使用SQL语句中的CREATE TABLE语句创建一个用于存储几何数据的表,其中包括一个geometry类型的列。以下示例创建了一个带有几何列的表,该列存储BC-Albers坐标系统(SRID 3005)中的2D(XY)LineStrings:

CREATE TABLE roads (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    geom geometry(LINESTRING,3005)
  );

geometry类型对应于两个可选的类型修饰符

  • 空间类型修饰符 限制列中允许的形状和维度类型。 该值可以是任何受支持的几何子类型(例如 POINT、LINESTRING、POLYGON、MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION 等)。 该修改器通过添加后缀支持坐标维数限制:Z、M 和 ZM。 例如,修饰符“LINESTRINGM”仅允许具有三个维度的线串,并将第三个维度视为度量。 同样,“POINTZM”需要四维 (XYZM) 数据。

  • SRID 修饰符空间参考系统的 SRID 限制为特定的数值。如果省略,则默认值为 0。

创建具有几何列的表的示例:

  • 创建一个表,其中包含具有默认 SRID 的任何类型的几何图形:

    CREATE TABLE geoms(gid serial PRIMARY KEY, geom geometry );
  • 为 2D 点创建具有 SRID 默认值的表:

    CREATE TABLE pts(gid serial PRIMARY KEY, geom geometry(POINT) );
  • 创建一个包含 3D (XYZ) 点和显式 SRID 3005 的表:

    CREATE TABLE pts(gid serial PRIMARY KEY, geom geometry(POINTZ,3005) );
  • 使用默认 SRID 创建具有 4D (XYZM) LINESTRING 几何图形的表:

    CREATE TABLE lines(gid serial PRIMARY KEY, geom geometry(LINESTRINGZM) );
  • 使用 SRID 4267(NAD 1927 长纬度)创建包含 2D POLYGON 几何图形的表:

    CREATE TABLE polys(gid serial PRIMARY KEY, geom geometry(POLYGON,4267) );

一个表可以有多个几何列。这可以通过在创建表时指定它或使用ALTER TABLE语句添加它来实现。下面的示例演示如何添加列以存储 3D LineStrings:

ALTER TABLE roads ADD COLUMN geom2 geometry(LINESTRINGZ,4326);

4.6.2. GEOMETRY_COLUMNS 视图

适用于SQL的OGC简单要素规范定义了用于描述GEOMETRY_COLUMNS结构的元数据表。在 PostGIS 中,geometry_columns是从数据库的系统目录表中读取的视图。这可确保空间元数据信息始终与当前定义的表或视图一致。视图结构为:

\d geometry_columns
View "public.geometry_columns"
      Column       |          Type          | Modifiers
-------------------+------------------------+-----------
 f_table_catalog   | character varying(256) |
 f_table_schema    | character varying(256) |
 f_table_name      | character varying(256) |
 f_geometry_column | character varying(256) |
 coord_dimension   | integer                |
 srid              | integer                |
 type              | character varying(30)  |

这些列是:

f_table_catalog, f_table_schema, f_table_name

包含几何列的要素表的完全限定名称。PostgreSQL 中没有类似的“目录”,因此该列留空。对于“schema”,使用 PostgreSQL 模式名称(默认为public)。

f_geometry_column

要素表中几何列的名称。

coord_dimension

列的坐标维度(2、3 或 4)。

srid

用于此表中的坐标几何的空间参考系的 ID。它是对引用spatial_ref_sys表的外键引用(请参见Section 4.5.1, “SPATIAL_REF_SYS表”)。

type

空间对象的类型。 要将空间列限制为单一类型,请使用以下之一:POINT、LINESTRING、POLYGON、MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION或相应的 XYM 版本 POINTM、LINESTRINGM、POLYGONM、MULTIPOINTM、MULTILINESTRINGM、MULTIPOLYGONM、GEOMETRYCOLLECTIONM。 对于异构(混合类型)集合,您可以使用“GEOMETRY”作为类型。

4.6.3. 手动注册几何列

您可能需要这样做的两种情况是 SQL 视图和批量插入。对于批量插入情况,您可以通过约束列或执行更改表来更正 Geometry_columns 表中的注册。对于视图,您可以使用 CAST 操作来公开。 请注意,如果您的列是基于typmod的,创建过程将正确注册它,因此无需执行任何操作。 此外,未对几何应用空间函数的视图将与基础表几何列注册相同。

-- Lets say you have a view created like this
CREATE VIEW public.vwmytablemercator AS
        SELECT gid, ST_Transform(geom, 3395) As geom, f_name
        FROM public.mytable;

-- For it to register correctly
-- You need to cast the geometry
--
DROP VIEW public.vwmytablemercator;
CREATE VIEW  public.vwmytablemercator AS
        SELECT gid, ST_Transform(geom, 3395)::geometry(Geometry, 3395) As geom, f_name
        FROM public.mytable;

-- If you know the geometry type for sure is a 2D POLYGON then you could do
DROP VIEW public.vwmytablemercator;
CREATE VIEW  public.vwmytablemercator AS
        SELECT gid, ST_Transform(geom,3395)::geometry(Polygon, 3395) As geom, f_name
        FROM public.mytable;
--Lets say you created a derivative table by doing a bulk insert
SELECT poi.gid, poi.geom, citybounds.city_name
INTO myschema.my_special_pois
FROM poi INNER JOIN citybounds ON ST_Intersects(citybounds.geom, poi.geom);

-- Create 2D index on new table
CREATE INDEX idx_myschema_myspecialpois_geom_gist
  ON myschema.my_special_pois USING gist(geom);

-- If your points are 3D points or 3M points,
-- then you might want to create an nd index instead of a 2D index
CREATE INDEX my_special_pois_geom_gist_nd
        ON my_special_pois USING gist(geom gist_geometry_ops_nd);

-- To manually register this new table's geometry column in geometry_columns.
-- Note it will also change the underlying structure of the table to
-- to make the column typmod based.
SELECT populate_geometry_columns('myschema.my_special_pois'::regclass);

-- If you are using PostGIS 2.0 and for whatever reason, you
-- you need the constraint based definition behavior
-- (such as case of inherited tables where all children do not have the same type and srid)
-- set optional use_typmod argument to false
SELECT populate_geometry_columns('myschema.my_special_pois'::regclass, false); 

尽管仍然支持旧的基于约束的方法,但直接在视图中使用的基于约束的几何列将无法在 Geometry_columns 中正确注册,typmod 也是如此。在此示例中,我们使用typmod定义一个列,使用约束定义另一个列。

CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY, poi_name text, cat text, geom geometry(POINT,4326));
SELECT AddGeometryColumn('pois_ny', 'geom_2160', 2160, 'POINT', 2, false);

在 psql 中运行

\d pois_ny;

我们观察到它们的定义不同——一个是类型,一个是约束

Table "public.pois_ny"
  Column   |         Type          |                       Modifiers

-----------+-----------------------+------------------------------------------------------
 gid       | integer               | not null default nextval('pois_ny_gid_seq'::regclass)
 poi_name  | text                  |
 cat       | character varying(20) |
 geom      | geometry(Point,4326)  |
 geom_2160 | geometry              |
Indexes:
    "pois_ny_pkey" PRIMARY KEY, btree (gid)
Check constraints:
    "enforce_dims_geom_2160" CHECK (st_ndims(geom_2160) = 2)
    "enforce_geotype_geom_2160" CHECK (geometrytype(geom_2160) = 'POINT'::text
        OR geom_2160 IS NULL)
    "enforce_srid_geom_2160" CHECK (st_srid(geom_2160) = 2160)

在geometry_columns中,两者都已正确注册

SELECT f_table_name, f_geometry_column, srid, type
        FROM geometry_columns
        WHERE f_table_name = 'pois_ny';
f_table_name | f_geometry_column | srid | type
-------------+-------------------+------+-------
pois_ny      | geom              | 4326 | POINT
pois_ny      | geom_2160         | 2160 | POINT

但是 - 如果我们要创建这样的视图

CREATE VIEW vw_pois_ny_parks AS
SELECT *
  FROM pois_ny
  WHERE cat='park';

SELECT f_table_name, f_geometry_column, srid, type
        FROM geometry_columns
        WHERE f_table_name = 'vw_pois_ny_parks';

基于typmod的几何视图列正确注册, 但基于约束的约束没有。

f_table_name   | f_geometry_column | srid |   type
------------------+-------------------+------+----------
 vw_pois_ny_parks | geom              | 4326 | POINT
 vw_pois_ny_parks | geom_2160         |    0 | GEOMETRY

这可能会在未来版本的PostGIS中发生变化,但目前 要强制基于约束的视图列正确注册,您需要执行以下操作:

DROP VIEW vw_pois_ny_parks;
CREATE VIEW vw_pois_ny_parks AS
SELECT gid, poi_name, cat,
  geom,
  geom_2160::geometry(POINT,2160) As geom_2160
  FROM pois_ny
  WHERE cat = 'park';
SELECT f_table_name, f_geometry_column, srid, type
        FROM geometry_columns
        WHERE f_table_name = 'vw_pois_ny_parks';
f_table_name   | f_geometry_column | srid | type
------------------+-------------------+------+-------
 vw_pois_ny_parks | geom              | 4326 | POINT
 vw_pois_ny_parks | geom_2160         | 2160 | POINT

4.7. 加载空间数据

创建空间表后,即可上传空间数据到数据库。有两种内置方法可以将空间数据放入PostGIS/PostgreSQL 数据库:使用格式化的 SQL 语句或使用 形状文件加载器。

4.7.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中:

psql -d [database] -f roads.sql

4.7.2. 使用Shapefile加载器

shp2pgsql数据加载器将 Shapefile 转换为适合以几何或地理格式插入PostGIS/PostgreSQL 数据库的 SQL。加载程序有多种通过命令行标志选择的操作模式。

还有一个shp2pgsql-gui图形界面,其中大多数选项作为命令行加载程序。对于一次性非脚本加载或者您是 PostGIS 新手,这可能更容易使用。它还可以配置为 PgAdminIII 的插件。

(c|a|d|p)这些是相互排斥的选项:

-c

创建一个新表并从Shapefile文件填充它。这是默认模式

-a

将数据从Shapefile文件添加到数据库表中。如果使用此选项加载多个文件,则这些文件必须具有相同的属性和相同的数据类型。

-d

在创建包含 shapefile 中的数据的新表之前,请先删除数据库表。

-p

它只生成用于表创建的SQL代码,不添加任何实际数据。 如果要将表创建与数据加载完全分开,请使用此模式。

-?

显示帮助屏幕。

-D

对输出数据使用 PostgreSQL 转储格式。此模式与 -a、-c 和 -d 结合使用。 它的加载速度比默认的“插入”SQL格式快得多。通常将其用于大型数据集。

-s [<FROM_SRID>:]<SRID>

使用指定的 SRID 创建并填充几何表。(可选)指定输入shapefile文件使用给定的 FROM_SRID,在这种情况下,几何图形将被重新投影到目标 SRID。

-k

保留标识符的大小写(列、架构和属性)。请注意,Shapefile文件中的属性都是大写的。

-i

将所有整数强制为标准 32 位整数,不要创建 64 位大整数,即使DBF标头签名似乎可以保证这一点。

-I

在几何列上生成 GiST 索引。

-m

-m a_file_name指定一个文件,其中包含一组(长)列名到 10 个字符的 DBF 列名的映射。文件的内容是由空格分隔的两个名称的一行或多行,并且没有尾随或前导空格。 例如:

COLUMNNAME DBFFIELD1
AVERYLONGCOLUMNNAME DBFFIELD2

-S

生成简单的几何图形而不是多个几何图形。仅当所有几何图形实际上都是单一的时才会成功(即具有单个壳的 MULTIPOLYGON,或具有单个顶点的 MULTIPOINT)。

-t <dimensionality>

强制输出几何具有指定的维度。使用以下方法表示维度的字符串:2D、3DZ、3DM、4D。

如果输入的维度少于指定的维度,则输出将用零填充这些维度。 如果输入具有指定的更多维度,则不需要的维度将被删除。

-w

使输出格式为 WKT 而不是 WKB。 重要的是要注意,精度可能会降低,并且可能会出现坐标波动。

-e

单独执行每个语句,而不使用事务。当存在一些产生错误的不良几何形状时,这允许加载大多数好的数据。请注意,这不能与 -D 标志一起使用,因为“转储”格式始终使用事务。

-W <encoding>

指定输入数据(dbf 文件)的编码。 使用时,dbf 的所有属性都会从指定的编码转换为 UTF8。生成的 SQL 输出将包含SET CLIENT_ENCODING to UTF8命令,以便后端能够从 UTF8 重新转换为数据库配置为内部使用的任何编码。

-N <policy>

选择 NULL几何操作策略(插入*、跳过、中止)

-n

-n 仅导入 DBF 文件。如果您的数据没有相应的shapefile文件,它将自动切换到此模式 并仅加载 DBF。因此,仅当您设置了完整的 shapefile 并且只需要属性数据而不需要几何图形时,才需要设置此标志。

-G

地理类型不使用几何类型,而是使用 WGS84 经度和纬度 (SRID=4326)(需要经度纬度数据)

-T <tablespace>

指定新表的表空间。索引仍将使用 默认表空间,除非还使用 -X 参数。The PostgreSQL 文档对何时使用自定义表空间有很好的描述。

-X <tablespace>

指定新表上的索引要使用的表空间。 它适用于主键索引,如果 -I 一起使用,则也适用于 GiST 空间索引。

-Z

使用时,该标志将阻止生成ANALYZE语句。 如果没有-Z标志(默认行为),将生成ANALYZE语句。

使用加载程序创建输入文件并加载它的示例会话可能如下所示:

# shp2pgsql -c -D -s 4269 -i -I shaperoads.shp myschema.roadstable 
> roads.sql
# psql -d roadsdb -f roads.sql

转换和加载可以使用 UNIX 通道一步完成:

# shp2pgsql shaperoads.shp myschema.roadstable | psql -d roadsdb

4.8. 提取空间数据

可以使用 SQL 或 Shapefile 转储程序从数据库中提取空间数据。有关 SQL 的部分介绍了一些可用于对空间表进行比较和查询的函数。

4.8.1. 使用SQL提取数据

从数据库中提取空间数据的最直接方法是使用 SQL SELECT 查询来定义要提取的数据集并将结果列转储到可解析的文本文件中:

db=# SELECT road_id, ST_AsText(road_geom) AS geom, road_name FROM roads;

road_id | geom                                    | road_name
--------+-----------------------------------------+-----------
          1 | LINESTRING(191232 243118,191108 243242) | Jeff Rd
          2 | LINESTRING(189141 244158,189265 244817) | Geordie Rd
          3 | LINESTRING(192783 228138,192612 229814) | Paul St
          4 | LINESTRING(189412 252431,189631 259122) | Graeme Ave
          5 | LINESTRING(190131 224148,190871 228134) | Phil Tce
          6 | LINESTRING(198231 263418,198213 268322) | Dave Cres
          7 | LINESTRING(218421 284121,224123 241231) | Chris Way
(6 rows)

有时需要某种限制来减少返回的记录数量。对于基于属性的限制,请使用与非空间表相同的 SQL 语法。在空间限制的情况下,以下函数很有用:

ST_Intersects

此函数指示两个几何图形是否共享任何空间。

=

它测试两个几何形状在几何上是否相同。例如,如果“POLYGON((0 0,1 1,1 0,0 0))”与“POLYGON((0 0,1 1,1 0,0 0))”相同(确实如此)。

接下来,您可以在查询中使用这些运算符。请注意,在 SQL 命令行上指定几何图形和框时,必须显式将字符串表示形式转换为几何函数。312是一个虚构的空间参考系统,与我们的数据相匹配。例如:

SELECT road_id, road_name
  FROM roads
  WHERE roads_geom='SRID=312;LINESTRING(191232 243118,191108 243242)'::geometry;

上面的查询从“ROADS_GEOM”表中返回与该值等效的单个记录。

要检查道路是否穿过由多边形定义的面,请执行以下操作:

SELECT road_id, road_name
FROM roads
WHERE ST_Intersects(roads_geom, 'SRID=312;POLYGON((...))');

最常见的空间查询可能是“基于框架”的查询,由数据浏览器和网络制图器等客户端软件使用,以获取“地图框架”的数据进行显示。

使用“&&”运算符时,可以指定比较特征是 BOX3D 还是几何。 但是,如果指定几何图形,则其边界框用于比较。

使用框架的“BOX3D”对象,查询如下所示:

SELECT ST_AsText(roads_geom) AS geom
FROM roads
WHERE
  roads_geom && ST_MakeEnvelope(191232, 243117,191232, 243119,312);

请注意使用 SRID 312 来指定包络的投影。

4.8.2. 使用Shapefile文件转储程序

pgsql2shp表转储器连接到数据库并将表(可能由查询定义)转换为形状文件。 基本语法是:

pgsql2shp [<options
>] <database
> [<schema
>.]<table>
pgsql2shp [<options
>] <database
> <query>

命令行选项包括:

-f <filename>

将输出写入特定文件名。

-h <host>

要连接到的数据库的主机名。

-p <port>

要连接到的数据库的端口。

-P <password>

用于连接到数据库的密码。

-u <user>

要连接到数据库的用户名。

-g <geometry column>

对于具有多个几何列的表,写入形状文件时要使用的几何列。

-b

使用二进制游标。 这将使操作更快,但如果表中的任何非几何属性缺少文本转换,则该操作将不起作用。

-r

原始模式。不要删除gid字段或转义列名称。

-m filename

将标识符重新映射为十个字符名称。文件的内容是由单个空格分隔的两个符号行,并且没有尾随或前导空格:VERYLONGSYMBOL SHORTONE ANOTHERVERYLONGSYMBOL SHORTER 等。

4.9. 空间索引

空间索引使得使用空间数据库存储大型数据集成为可能。如果没有索引,搜索功能需要顺序扫描数据库中的每条记录。索引通过将数据组织成可快速遍历以查找匹配记录的结构来加快搜索速度。

通常用于属性数据的B树索引方法对于空间数据来说不是很有用,因为它只支持单个维度的数据存储和查询。诸如几何形状(具有 2 个或更多维度)之类的数据需要支持跨所有数据维度的范围查询的索引方法。PostgreSQL 在空间数据处理方面的主要优势之一是它提供了多种适用于多维数据的索引方法:GiST、BRIN 和 SP-GiST 索引。

  • GiST (广义搜索树) 索引将数据分解为“一侧的事物”、“重叠的事物”、“内部的事物”,并且可用于多种数据类型,包括 GIS 数据。PostGIS 使用在 GiST 之上实现的 R-Tree 索引来索引空间数据。 GiST是最常用、最通用的空间索引方法,提供非常好的查询性能。

  • BRIN (块范围索引)索引通过汇总表记录范围的空间范围进行操作。搜索是通过扫描范围来完成的。BRIN仅适用于某些类型的数据(空间排序、更新不频繁或不更新)。但它提供了更快的索引创建时间和更小的索引大小。

  • SP-GiST (空间分区广义搜索树)是一种通用索引方法,支持四叉树、k-d 树和基数树(tries)等分区搜索树。

空间索引仅存储几何图形的边界框。空间查询使用索引作为主要过滤器来快速确定一组可能与查询条件匹配的几何图形。大多数空间查询需要使用空间谓词函数的辅助过滤器 来测试更具体的空间条件。有关使用空间谓词进行查询的更多信息,请参阅Section 5.2, “使用空间索引”

另请参阅 PostGIS Workshop有关空间索引的部分以及PostgreSQL手册

4.9.1. GiST 索引

GiST 代表“广义搜索树”,是多维数据索引的通用形式。PostGIS 使用在 GiST 之上实现的 R-Tree 索引来索引空间数据。GiST是最常用、最通用的空间索引方法,提供非常好的查询性能。GiST 的其他实现用于加速对不适合普通 B 树索引的各种不规则数据结构(整数数组、光谱数据等)的搜索。有关详细信息,请参阅PostgreSQL手册

一旦空间数据表超过几千行,您将需要构建索引来加速数据的空间搜索(除非您的所有搜索都基于属性,在这种情况下,您将需要在空间数据表上构建普通索引) 属性字段)。

在“geometry”列上构建 GiST 索引的语法如下:

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] ); 

上述语法将始终构建 2D 索引。若要获取几何类型的 n 维索引,可以使用以下语法创建一个索引:

CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd);

构建空间索引是一项计算密集型工作。它还会在创建表时阻止对表的写入访问,因此在生产系统上,您可能希望以较慢的并发感知方式执行:

CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING GIST ( [geometryfield] ); 

建立索引后,让 PostgreSQL 不时收集表上的统计信息会很有帮助。它用于优化查询计划:

VACUUM ANALYZE [table_name] [(column_name)];

4.9.2. BRIN 索引

BRIN 代表“区块范围索引”。 它是 PostgreSQL 9.5 中引入的通用索引方法。BRIN 是一种有损索引方法,这意味着需要进行二次检查来确认记录与给定的搜索条件匹配(所有提供的空间索引都是这种情况)。它提供更快的索引创建速度和更小的索引大小,以及合理的读取性能。 其主要目的是支持在与其在表中的物理位置相关的列上对非常大的表建立索引。 除了空间索引之外,BRIN 还可以加快各种属性数据结构(整数、数组等)的搜索速度。有关详细信息,请参阅PostgreSQL手册

一旦空间表超过几千行,您将需要构建索引来加速数据的空间搜索。只要 GiST 索引的大小不超过数据库可用的 RAM 量,并且只要您能够承受索引存储大小以及写入时更新索引的成本,GiST 索引的性能就非常好。 否则,对于非常大的表,可以考虑将 BRIN 索引作为替代方案。

BRIN 索引存储包围一组连续的表块(称为块范围)中的行中包含的所有几何图形的边界框。使用索引执行查询时,将扫描块范围以查找与查询范围相交的块范围。仅当数据经过物理排序以使块范围的边界框具有最小重叠(并且理想情况下是互斥的)时,这才是有效的。 生成的索引非常小,但读取相同数据时的性能通常低于 GiST 索引。

构建 BRIN 索引比构建 GiST 索引占用的 CPU 资源要少得多。我们经常发现,对于相同数据,BRIN 索引的构建速度比 GiST 索引快十倍。由于 BRIN 索引只为每个表块范围存储一个边界框,因此通常使用的磁盘空间比 GiST 索引少一千倍。

您可以选择要在某个范围内汇总的块数。 如果减少这个数字,索引会更大,但可能会提供更好的性能。

为了使 BRIN 有效,表数据应按物理顺序存储,从而最大限度地减少块范围重叠量。数据可能已经适当排序(例如,如果它是从已按空间顺序排序的另一个数据集加载的)。否则,这可以通过按一维空间键对数据进行排序来完成。 一种方法是创建一个按几何值排序的新表(在最近的 PostGIS 版本中使用有效的希尔伯特曲线排序):

CREATE TABLE table_sorted AS
   SELECT * FROM table  ORDER BY geom;

或者,可以通过使用 GeoHash 作为(临时)索引并对该索引进行聚类来对数据进行适当排序:

CREATE INDEX idx_temp_geohash ON table
    USING btree (ST_GeoHash( ST_Transform( geom, 4326 ), 20));
CLUSTER table USING idx_temp_geohash;

geometry 列上构建 BRIN 索引的语法为:

CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geome_col] ); 

上述语法构建二维索引。若要生成三维索引,请使用以下语法:

CREATE INDEX [indexname] ON [tablename]
    USING BRIN ([geome_col] brin_geometry_inclusion_ops_3d);

您还可以使用 4D 运算符类获取 4D 维度索引:

CREATE INDEX [indexname] ON [tablename]
    USING BRIN ([geome_col] brin_geometry_inclusion_ops_4d);

在上面的命令中,范围内的块数使用默认值 128。 此语句用于指定聚合中某个区域中的块数

CREATE INDEX [indexname] ON [tablename]
    USING BRIN ( [geome_col] ) WITH (pages_per_range = [number]); 

请记住,BRIN 索引仅存储大量行的一个索引条目。如果您的表存储具有混合维数的几何图形,则生成的索引的性能可能会很差。您可以通过选择存储几何图形维数最少的运算符类来避免这种性能损失

BRIN 索引支持geography数据类型。在geography列上构建 BRIN 索引的语法是:

CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geog_col] ); 

上述语法为椭球体上的地理空间对象构建 2D 索引。

目前,仅提供“包容性支持”,这意味着只有&&~@运算符可用于 2D 情况(对于几何地理 ),而运算符 &&&只能用于 3D 几何图形。目前不支持 kNN 搜索。

BRIN 与其他索引类型的一个重要区别是数据库不会动态维护索引。对表中空间数据的更改只需附加到索引的末尾。这将导致索引搜索性能随着时间的推移而降低。 可以通过执行 VACUUM或使用特殊函数brin_summarize_new_values(regclass)来更新索引。 因此,BRIN 可能最适合用于只读或很少更改的数据。有关更多信息,请参阅手册

总结一下对空间数据使用 BRIN 的情况:

  • 索引构建时间非常快,索引大小非常小。

  • 索引查询时间比 GiST 慢,但完全可以接受。

  • 要求按空间顺序对表数据进行排序。

  • 需要手动维护索引。

  • 它最适合重叠很少或没有重叠(例如,点)且静态或不经常更改的大型表。

  • 对于返回相对大量数据记录的查询更有效。

4.9.3. SP-GiST 索引

SP-GiST代表“空间分区广义搜索树”,是多维数据类型索引的通用形式,支持分区搜索树,例如四叉树、k-d 树和基数树 (tries)。 这些数据结构的共同特征是它们将搜索空间重复划分为大小不必相等的分区。除了空间索引之外,SP-GiST 还用于加速多种数据的搜索,例如电话路由、ip 路由、子字符串搜索等。有关更多信息,请参阅PostgreSQL手册

与 GiST 索引的情况一样,SP-GiST索引是有损的,因为它们存储包围空间对象的边界框。SP-GiST 索引可以被视为 GiST 索引的替代方案。

一旦 GIS 数据表超过几千行,就可以使用 SP-GiST 索引来加速数据的空间搜索。 在“geometry”列上构建 SP-GiST 索引的语法如下:

CREATE INDEX [indexname] ON [tablename] USING SPGIST ( [geometryfield] ); 

上面的语句构建了一个二维索引。 几何类型的三维索引是使用三维运算符类生成的,如下所示:

CREATE INDEX [indexname] ON [tablename] USING SPGIST ([geometryfield] spgist_geometry_ops_3d);

构建空间索引是一项计算密集型操作。它还会在创建表时阻止对表的写入访问,因此在生产系统上,您可能希望以较慢的并发感知方式执行操作:

CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING SPGIST ( [geometryfield] ); 

建立索引后,让 PostgreSQL 不时收集表上的统计信息会很有帮助。它用于优化查询计划:

VACUUM ANALYZE [table_name] [(column_name)];

SP-GiST 索引可以加速涉及以下运算符的查询:

  • 对于二维索引,<<, &<, &>, >>, <<|, &<|, |&>, |>>, &&, @>, <@, 和 ~=

  • 对于三维索引,&/&, ~==, @>>,和<<@。

目前不支持 kNN 搜索。

4.9.4. 调优索引的使用

通常,索引会无形中加速数据访问:一旦建立索引,PostgreSQL查询规划器就会自动决定何时使用它来提高查询性能。但在某些情况下,规划器不会选择使用现有索引,因此查询最终会使用缓慢的顺序扫描而不是空间索引。

如果发现空间索引未被使用, 您可以执行以下几项操作:

  • 可以通过测试查询计划和查看查询来计算所需的内容。不正确的连接、忘记的表和不正确的表可能会导致多次意外的表记录查找。若要获取查询计划,请在查询开始时执行EXPLAIN

  • 确保收集有关表中值的数量和分布的统计信息,以便为查询规划者提供更好的信息来做出有关索引使用的决策。VACUUM ANALYZE将计算两者。

    无论如何,您应该定期清理数据库。许多 PostgreSQL DBA 定期将 VACUUM 作为非高峰 cron 作业运行。

  • 如果清理没有帮助,您可以使用命令SET ENABLE_SEQSCAN TO OFF;暂时强制规划器使用索引信息。 通过这种方式,您可以检查规划器是否能够为您的查询生成索引加速查询计划。您应该仅使用此命令进行调试;一般来说,规划者比你更了解何时使用索引。运行查询后,不要忘记运行SET ENABLE_SEQSCAN TO ON; 以便规划器能够正常运行其他查询。

  • 如果 SET ENABLE_SEQSCAN TO OFF; 帮助您的查询运行得更快,则您的 Postgres 可能未针对您的硬件进行调整。 如果您发现规划器对顺序扫描与索引扫描的成本有误,请尝试减小 RANDOM_PAGE_COST 值(位于 postgresql.conf中),或使用 SET RANDOM_PAGE_COST TO 1.1;RANDOM_PAGE_COST 的默认值为 4.0。 尝试将其设置为 1.1(对于 SSD)或 2.0(对于快速磁盘)。 减小该值使规划器更有可能使用索引扫描。

  • 如果SET ENABLE_SEQSCAN TO OFF;对您的查询没有帮助,该查询可能使用 Postgres 规划器尚无法优化的 SQL 构造。 可以以规划器能够处理的方式重写查询。例如,具有内联 SELECT 的子查询可能不会产生有效的计划,但可能会使用 LATERAL JOIN 进行重写。

有关更多信息,请参阅 PostgreSQL 手册的查询规划部分。