Chapter 4. 数据管理

Table of Contents
4.1. 空间数据模型
4.1.1. OGC几何图形
4.1.2. SQL/MM第3部分-曲线
4.1.3. WKT和WKB
4.2. 几何数据类型
4.2.1. PostGIS EWKB和EWKT
4.3. 地理数据类型
4.3.1. 创建地理表
4.3.2. 使用地理表
4.3.3. 何时使用地理数据类型
4.3.4. 地理高级常见问题解答
4.4. 几何图形验证
4.4.1. 简单几何图形
4.4.2. 有效的几何图形
4.4.3. 有效性管理
4.5. 空间参考系
4.5.1. SPATIAL_REF_SYS表
4.5.2. 用户定义的空间参考系统
4.6. 空间表
4.6.1. 创建空间表
4.6.2. 几何图形_列视图
4.6.3. 手动注册几何图形列
4.7. 加载空间数据
4.7.1. 使用SQL加载数据
4.7.2. 使用Shapefile加载器
4.8. 提取空间数据
4.8.1. 使用SQL提取数据
4.8.2. 使用Shapefile Dumper
4.9. 空间索引
4.9.1. 主旨指数
4.9.2. Brin指数
4.9.3. SP-GIST指数
4.9.4. 调整索引使用率

4.1. 空间数据模型

4.1.1. OGC几何图形

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

几何学是一种 摘要 键入。几何值属于其 混凝土 代表各种类型和尺寸的几何形状的子类型。其中包括 原子性 类型 LineString LinearRing 多边形 ,以及 征集 类型 MultiPoint MultiLineString MultiPolygon GeometryCollection 。这个 简单功能访问.第1部分:通用体系结构v1.2.1 为结构添加子类型 PolyhedralSurface 三角形 TIN

几何图形在2维笛卡尔平面中对形状建模。多面体曲面、三角形和三角网类型也可以表示三维空间中的形状。形状的大小和位置由其 坐标 。每个坐标都有一个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模型将其称为 信封 。这是包含几何体坐标的二维或三维长方体。它是表示几何图形在坐标空间中的范围以及检查两个几何图形是否相互作用的有效方法。

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

OGC几何模型定义了每种几何类型的有效性规则。这些规则确保几何体值表示真实情况(例如,可以指定一个位于壳外部的洞的多边形,但这在几何上没有意义,因此是无效的)。PostGIS还允许存储和操作无效的几何值。这允许在需要时检测和修复它们。看见 Section 4.4, “几何图形验证”

4.1.1.1. 点

点是表示坐标空间中的单个位置的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是一种既封闭又简单的线串。第一个点和最后一个点必须相等,并且直线不能自相交。

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

4.1.1.4. 多边形

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

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是几何图形的异类(混合)集合。

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 ((0 0, 0 9, 9 0, 0 0))

4.1.1.11. TIN

TIN是非重叠的集合 三角形 %s表示一个 不规则三角网

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-3SQL多媒体-空间 标准(SQL/MM)扩展了OGC SFA以定义包含圆弧曲线的几何子类型。SQL/MM类型支持3DM、3DZ和4D坐标。

[Note]

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

4.1.2.1. CircularString

CircularString是基本的曲线类型,类似于线性世界中的LineString。单个圆弧段由三个点指定:起点和终点(第一个和第三个)以及圆弧上的某个其他点。要指定闭合圆,起点和终点相同,并且中点是圆直径(即圆弧的中心)上的相反点。在圆弧序列中,前一个圆弧的终点是下一个圆弧的起点,就像线串的线段一样。这意味着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

CurvePolygon就像一个多边形,有一个外环和零个或多个内环。不同之处在于,环可以是CircularString或CompoundCurve,也可以是线串。

从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,其壳由包含CircularString和LineString的CompoundCurve定义,以及一个孔由CircularString定义

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

多重曲线是一组曲线,其中可以包括LineStrings、CircularStrings或CompoundCurve。

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表示的示例如下:

  • 点(0 0)

  • 点Z(0 0 0)

  • ZM点(0 0 0)

  • 点为空

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

  • 线路设置为空

  • 多边形((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,12,1 1))

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

  • 多点Z((0 0 0),(1 2 3))

  • 多点为空

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

  • 几何测量集合为空

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:点(1 1)

    WKB:010100000000000000000000F03F000000000000F03

  • 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通过定义名为的PostgreSQL数据类型实现OGC简单要素模型 geometry 。它使用内部类型代码表示所有几何子类型(请参见 GeometryTypeST_GeometryType )。这允许将空间要素建模为使用类型为的列定义的表行 geometry

这个 geometry 数据类型为 不透明 这意味着所有访问都是通过对几何值调用函数来完成的。函数允许创建几何对象、访问或更新所有内部字段以及计算新的几何值。PostGIS支持OGC中指定的所有功能 简单功能访问.第2部分:SQL选项 (SFS)规范,以及许多其他规范。看见 Chapter 8, 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。这允许通过使用将HEXEWKB或EWKT中的文本值转换为几何值来创建几何值 ::geometry 。对于输出,二进制的规范形式是EWKB,而文本的规范形式是HEXEWKB(十六进制编码的EWKB)。

例如,此语句通过从EWKT文本值强制转换来创建几何图形,并使用HEXEWKB的规范形式输出该几何图形:

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

PostGIS EWKT输出与OGC WKT有几点不同:

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

    OGC:点Z(1 2 3)

    EWKT:点(1 2 3)

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

    OGC:点M(1 2 3)

    EWKT:POINTM(1 2 3)

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

    OGC:点ZM(1 2 3 4)

    EWKT:点(1 2 3 4)

EWKT避免过度指定维度和OGC/ISO格式可能出现的不一致,例如:

  • ZM点(1 1)

  • ZM点(1 1 1)

  • 点(1 1 1)

[Caution]

PostGIS扩展格式目前是OGC格式的超集,因此每个有效的OGC WKB/WKT也是有效的EWKB/EWKT。然而,如果OGC以与PosGIS定义冲突的方式扩展一种格式,这在未来可能会有所不同。因此,您不应该依赖这种兼容性!

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

  • 点(0 0 0)--XYZ

  • SRID=32632;点(0 0)--带SRID的XY

  • POINTM(00 0)--XYM

  • 点(0 0 0)--XYZM

  • SRID=4326;MULTIPOINTM(0 0 0,1 21)--带SRID的XYM

  • 多行((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 4 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)) )

  • 三角形((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 数据类型为在“地理”坐标(有时称为“大地”坐标,或“经度/经度”或“经度/经度”)上表示的空间要素提供原生支持。地理坐标是以角度单位(度)表示的球面坐标。

PostGIS几何数据类型的基础是平面。平面上两点之间的最短路径是一条直线。这意味着几何上的函数(面积、距离、长度、交点等)是使用直线向量和笛卡尔数学来计算的。这使得它们更容易实现和更快地执行,但也使它们对地球球面上的数据不准确。

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

因为基础数学更复杂,所以为地理类型定义的函数比为几何类型定义的函数要少。随着时间的推移,随着新算法的加入,地理类型的能力将会扩大。作为一种解决办法,可以在几何类型和地理类型之间来回转换。

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

对于所有空间参考系,由测量函数返回的单位(例如 ST_DistanceST_LengthST_PerimeterST_Area )和关于距离的争论 ST_DWithin 以米为单位。

4.3.1. 创建地理表

您可以使用创建一个表来存储地理数据 创建表格 具有以下类型列的SQL语句 geography 。以下样例创建一个表,其中包含存储WGS84大地坐标系(SRID 4326)中的二维线串的地理列:

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

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

  • 空间类型修饰符限制列中允许的造型和尺寸标注的类型。空间类型允许的值为:POINT、LINESTRING、POLYGON、MULTPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION。地理类型不支持曲线、三角网或多角曲线。修改器通过添加后缀Z、M和ZM来支持坐标维度限制。例如,修饰符‘LINESTRINGM’只允许具有三个维度的线串,并将第三个维度视为测量。同样,‘POINTZM’需要四维(XYZM)数据。

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

创建包含地理列的表格的示例:

  • 使用默认SRID 4326(WGS84经度/纬度)创建具有二维点地理位置的表格:

    CREATE TABLE ptgeogwgs(gid serial PRIMARY KEY, geog geography(POINT) );
  • 在NAD83 Longlat中创建包含二维点地理信息的表格:

    CREATE TABLE ptgeognad83(gid serial PRIMARY KEY, geog geography(POINT,4269) );
  • 创建具有3D(XYZ)点和显式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经度)创建具有2D多边形地理位置的表格:

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

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

SELECT * FROM geography_columns;

创建空间索引与创建几何图形列的工作原理相同。PostGIS将注意到列类型是地理类型,并创建适当的基于球体的索引,而不是通常用于几何图形的平面索引。

-- 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.051.5))到雷克雅未克(点(-21.9664.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时间来执行。

您选择的数据类型应该由您正在构建的应用程序的预期工作区确定。您的数据将覆盖全球或大片大陆地区,还是某个州、县或市的本地数据?

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

  • 如果您的数据是全球性的或覆盖大陆地区,您可能会发现地理位置允许您构建一个系统,而不必担心预测细节。您以经度/纬度存储数据,并使用已在地理上定义的函数。

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

请参阅 Section 15.11, “PostGIS Function Support Matrix” 用于比较地理与几何支持的内容。有关地理函数的简短列表和说明,请参阅 Section 15.4, “PostGIS Geography Support Functions”

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

4.3.4.1. 你算的是球体还是椭球体?
4.3.4.2. 日期线和电线杆呢?
4.3.4.3. 你能处理的最长弧线是多少?
4.3.4.4. 为什么计算欧洲/俄罗斯/在这里插入大的地理区域的面积会这么慢?

4.3.4.1.

你算的是球体还是椭球体?

默认情况下,所有距离和面积计算都在椭球体上完成。您应该会发现,局部区域的计算结果将与良好的局部投影中的局部平面结果相匹配。在更大的区域上,球面计算将比在投影平面上进行的任何计算更准确。

所有地理函数都可以选择使用球体计算,方法是将最后一个布尔参数设置为‘False’。这将在一定程度上加快计算速度,特别是在几何体非常简单的情况下。

4.3.4.2.

日期线和电线杆呢?

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

4.3.4.3.

你能处理的最长弧线是多少?

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

4.3.4.4.

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

因为这个多边形太大了!大区域不好有两个原因:它们的边界很大,所以无论运行什么查询,索引都会拉出特征;顶点数量很大,测试(距离、包容)必须至少遍历顶点列表一次,有时必须遍历N次(N是另一个候选特征中的顶点数)。

与几何学一样,我们建议,当您有非常大的多边形,但在较小的区域进行查询时,您可以将几何数据“反规格化”成较小的块,以便索引可以有效地子查询对象的各个部分,这样查询就不必每次都取出整个对象。请咨询 ST_Subdivide 功能文档。仅仅因为你可以将整个欧洲存储在一个多边形中,并不意味着你应该。

4.4. 几何图形验证

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

4.4.1. 简单几何图形

A 简单 几何体是指没有不规则几何点的几何体,例如自交或自切。

A POINT 与生俱来 简单 作为0维几何图形对象。

MULTIPOINT %s是 简单 如果没有两个坐标( POINT S)相等(具有相同的坐标值)。

A LINESTRING 简单 如果它没有两次通过相同的点,除了端点。如果简单线串的端点相同,则称为 关着的不营业的 并被称为线性环。

(A) (C) 都很简单 LINESTRING s. (B) (D) 并不简单。 (C) 是一个闭线性环。

(A)

(B)

(C)

(D)

A MULTILINESTRING 简单 仅当它的所有元素都是单的,并且任意两个元素之间的唯一交集出现在两个元素的边界上的点上。

(E) (F) 都很简单 MULTILINESTRING s. (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. 有效的几何图形

几何有效性主要适用于2维几何图形( POLYGON S和 MULTIPOLYGON S)。有效性由允许多边形几何体明确建模平面区域的规则定义。

A POLYGON 有效 如果:

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

  2. 边界环不交叉

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

  4. 内环包含在外环中

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

(H) (I) 是有效的 POLYGON s. (J-m) 是无效的。 (J) 可以表示为有效的 MULTIPOLYGON

(H)

(I)

(J)

(K)

(L)

(M)

A MULTIPOLYGON 有效 如果:

  1. 其要素 POLYGON %s有效

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

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

(N) 是有效的 MULTIPOLYGON (O) (P) 是无效的。

(N)

(O)

(P)

这些规则意味着有效的面几何也是 简单

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

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 cross()抛出错误!”),您应该首先确认函数参数有效。如果它们不是,则考虑使用以下技术之一来确保您正在处理的数据是有效的。

[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 是空间函数的一个例子,该函数 会吗? 允许无效输入!)

默认情况下,PostGIS在加载几何体时不会检查有效性,因为对于复杂的几何体,有效性测试可能会占用大量的CPU时间。如果不信任数据源,可以通过添加检查约束对表强制执行有效性检查:

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

4.5. 空间参考系

A 空间参考系 (SRS)(也称为坐标参考系(CRS))定义几何图形如何参考地球表面上的位置。有三种类型的SR:

  • A 大地测量学 SRS使用直接映射到地球表面的角坐标(经度和纬度)。

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

  • A 本地 SRS是不以地球表面为基准的笛卡尔坐标系。在PostGIS中,这由SRID值0指定。

有许多不同的空间参考系统在使用中。欧洲石油勘探组对常见的SRS进行了标准化 EPSG数据库 。为方便起见,PostGIS(和许多其他空间系统)使用称为SRID的整数标识符来引用SRS定义。

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

处理几何对的空间函数(例如 覆盖层 关系 函数)要求输入几何图形在相同的空间参考系中(具有相同的SRID)。可以使用将几何数据转换为不同的空间参考系 ST_Transform 。从函数返回的几何图形具有与输入几何图形相同的SRS。

4.5.1. SPATIAL_REF_SYS表

这个 SPATIAL_REF_SYS PostGIS使用的表是与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)
)

这些列包括:

格栅

唯一标识 空间参考系 (SRS)在数据库中。

auth_name

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

auth_srid

引用的管理局定义的空间参考系统的ID auth_name 。对于EPSG,这是EPSG代码。

源文本

空间参考系的众所周知的文本表示法。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标准 坐标参考系的知名文本表示法

项目4Text

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

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

有关详细信息,请参阅 项目网站 。这个 spatial_ref_sys.sql 文件同时包含两者 srtextproj4text 所有EPSG预测的定义。

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

  • 如果 auth_nameauth_srid 存在(非空),则根据这些条目使用项目SR(如果存在)。

  • 如果 srtext 如果可能,请使用它创建一个SRS。

  • 如果 proj4text 如果可能,请使用它创建一个SRS。

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

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

用于查找核心集中未定义的空间参考系的资源是 http://spatialreference.org/

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

美国各州使用州平面空间参考系统(基于米或英尺)-通常每个州存在一个或两个。大多数基于仪表的文档位于核心集合中,但许多基于英尺的文档或ESRI创建的文档将需要从 spatialreference.org

您甚至可以定义非地球坐标系,例如 火星2000 该火星坐标系是非平面的(单位为椭球度),但您可以将其与 geography 键入以获取以米为单位的长度和邻近度,而不是度。

以下是使用未指定的SRID和以美国为中心的Lambert Conform投影的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语句 geometry 。下面的示例创建一个表,其中包含存储BC-Albers坐标系(SRID 3005)中的二维(XY)线串的几何图形列:

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

这个 geometry 类型支持两个可选的 类型修饰符

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

  • 这个 SRID修改器 限制 空间参考系 将SRID设置为特定号码。如果省略,则SRID默认为0。

使用几何图形列创建表格的示例:

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

    CREATE TABLE geoms(gid serial PRIMARY KEY, geom geometry );
  • 使用默认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创建具有四维(XYZM)线条几何图形的表:

    CREATE TABLE lines(gid serial PRIMARY KEY, geom geometry(LINESTRINGZM) );
  • 使用SRID 4267(NAD 1927经度)创建具有二维多边形几何图形的表格:

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

一个表中可以有多个几何图形列。这可以在创建表时指定,也可以使用 ALTER TABLE SQL语句。此示例添加一个可以包含3D线条字符串的列:

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

4.6.2. 几何图形_列视图

《OGC》 针对SQL的简单功能规范 定义 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表格目录、f表格模式、f表格名称

包含几何图形列的要素表的完全限定名称。没有类似于“CATALOG”的PostgreSQL,因此该列保留为空。对于“SCHEMA”,使用PostgreSQL模式名称( public 是默认设置)。

f_geometry_column

要素表中几何图形列的名称。

coord_dimension

柱的坐标尺寸(2、3或4)。

格栅

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

类型

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

4.6.3. 手动注册几何图形列

您可能需要这样做的两种情况是SQL视图和批量插入。对于大容量插入,您可以通过约束列或执行ALTER TABLE来更正GEOMETRY_COLUMNS表中的注册。对于视图,您可以使用强制转换操作来公开。请注意,如果您的列是基于类型模式的,则创建过程将正确注册它,因此不需要执行任何操作。此外,未对几何图形应用空间函数的视图将与基础表几何图形列注册相同。

-- 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中,类型模式列也是如此。在本例中,我们使用tymod定义了一个列,并使用约束定义了另一个列。

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';

基于tymod的geom view列可以正确注册,但基于约束的geom view列不能正确注册。

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语句或使用shapefile加载器。

4.7.1. 使用SQL加载数据

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

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文件加载到PostgreSQL中 psql

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

-k

保留标识符大小写(列、模式和属性)。请注意,shapefile中的属性均为大写。

-我

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

-我

在几何图形列上创建一个Gist索引。

-m

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

COLUMNNAME DBFFIELD1
AVERYLONGCOLUMNNAME DBFFIELD2

-S

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

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

空几何图形处理策略(插入*、跳过、中止)

-n

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

-G

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

-T <tablespace>

指定新表的表空间。除非还使用-X参数,否则索引仍将使用默认表空间。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 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 Dumper

这个 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(通用搜索树) 索引将数据分解为“一侧的事物”、“重叠的事物”和“内部的事物”,可用于包括地理信息系统数据在内的各种数据类型。PostGIS使用在GIST之上实现的R-Tree索引来索引空间数据。GIST是最常用、用途最广的空间索引方法,具有很好的查询性能。

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

  • SP-GIST(空间分割广义搜索树) 是一种支持分区搜索树(如四叉树、k-d树和基数树(Try))的通用索引方法。

空间索引只存储几何图形的边界框。空间查询使用索引作为 主过滤器 以快速确定可能匹配查询条件的一组几何图形。大多数空间查询都需要 二次过滤器 它使用空间谓词函数来测试更具体的空间条件。有关使用空间谓词排队的更多信息,请参见 Section 5.2, “使用空间索引”

另请参阅 PostGIS专题讨论会关于空间索引的部分 ,以及 PostgreSQL手册

4.9.1. 主旨指数

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中引入的一种通用索引方法。布林是一个 Lossy 索引方法,这意味着需要进行二次检查以确认记录与给定的搜索条件匹配(所有提供的空间索引都是这种情况)。它提供了更快的索引创建速度和更小的索引大小,并具有合理的读取性能。它的主要目的是支持在与表中的物理位置相关的列上对非常大的表进行索引。除了空间索引,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;

对象上构建Brin索引的语法 geometry 列为:

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

上面的语法构建了一个2D索引。要构建3D索引,请使用以下语法:

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索引仅存储大量行的一个索引项。如果您的表存储具有混合数量的维度的几何图形,则结果索引的性能可能会很差。您可以通过选择存储几何的维度数最少的运算符类来避免此性能损失

这个 geography Brin索引支持数据类型。在地理列上构建Brin索引的语法为:

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

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

目前,只提供“包含支持”,这意味着只有 &&~@ 运算符可用于2D情况(用于两种情况 geometrygeography ),并且仅仅是 &&& 用于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] ); 

上面的语法将构建一个2维索引。可以使用3D运算符类创建几何图形类型的三维索引:

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 作为一份非高峰期的定期工作。

  • 如果清理无济于事,您可以使用以下命令临时强制规划器使用索引信息 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(对于固态硬盘)或2.0(对于快速磁盘)。减小该值会使规划器更有可能使用索引扫描。

  • 如果 SET ENABLE_SEQSCAN TO OFF; 对您的查询没有帮助,查询可能正在使用Postgres规划器尚不能优化的SQL结构。有可能以规划者能够处理的方式重写查询。例如,带有内联SELECT的子查询可能不会生成有效的计划,但可能会使用横向联接重写。

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