Postgresql—第4章使用PostGIS:数据管理和查询

2020/02/11 19:40
阅读数 3.1K

文章地址: http://postgis.net/docs/using_postgis_dbmanagement.html

第4章使用PostGIS:数据管理和查询

目录

4.1。GIS对象

4.1.1。OpenGIS WKB和WKT

4.1.2。PostGIS EWKB,EWKT和规范形式

4.1.3。SQL-MM第3部分

4.2。PostGIS地理类型

4.2.1。地理基础

4.2.2。何时使用Geometry数据类型而不是Geometry数据类型

4.2.3。地理高级常见问题解答

4.3。使用OpenGIS标准

4.3.1。SPATIAL_REF_SYS表和空间参考系统

4.3.2。GEOMETRY_COLUMNS视图

4.3.3。创建空间表

4.3.4。在geometry_columns中手动注册几何列

4.3.5。确保OpenGIS符合几何

4.3.6。尺寸扩展9交叉模型(DE-9IM)

4.4。加载GIS(矢量)数据

4.4.1。使用SQL加载数据

4.4.2。shp2pgsql:使用ESRI Shapefile加载程序

4.5。检索GIS数据

4.5.1。使用SQL检索数据

4.5.2。使用自卸车

4.6。建筑指标

4.6.1。GiST索引

4.6.2。BRIN指数

4.6.3。SP-GiST索引

4.6.4。使用索引

4.7。复杂查询

4.7.1。利用索引

4.7.2。空间SQL的示例

4.1。GIS对象

PostGIS支持的GIS对象是OpenGIS联盟(OGC)定义的“简单功能”的超集。PostGIS支持OGC“ SQL的简单功能”规范中指定的所有对象和功能。

PostGIS通过支持3DZ,3DM和4D坐标扩展了该标准。

4.1.1。OpenGIS WKB和WKT

OpenGIS规范定义了两种表达空间对象的标准方式:已知文本(WKT)形式和已知二进制(WKB)形式。WKT和WKB都包含有关对象类型和形成对象的坐标的信息。

要素的空间对象的文本表示(WKT)的示例如下:

  • 要点(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,1 2,1 1))

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

  • 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(点(2 3),线条(2 3,3 4))

OpenGIS规范还要求空间对象的内部存储格式必须包括空间参考系统标识符(SRID)。创建用于插入数据库的空间对象时,需要SRID。

这些格式的输入/输出可通过以下接口使用:

bytea WKB = ST_AsBinary(geometry); 文字WKT = ST_AsText(geometry); geometry = ST_GeomFromWKB(bytea WKB,SRID); geometry = ST_GeometryFromText(text WKT,SRID);

例如,用于创建和插入OGC空间对象的有效插入语句为:

INSERT INTO geotable(the_geom,the_name) 值(ST_GeomFromText('POINT(-126.4 45.32)',312),'A Place');

4.1.2。PostGIS EWKB,EWKT和规范形式

OGC格式仅支持2D几何形状,并且关联的SRID *从不*嵌入在输入/输出表示中。

当前,PostGIS扩展格式是OGC的一种超集(每个有效的WKB / WKT都是有效的EWKB / EWKT),但是将来可能会有所不同,特别是如果OGC推出的新格式与我们的扩展名冲突。因此,您不应该依赖此功能!

PostGIS EWKB / EWKT添加了3DM,3DZ,4D坐标支持和嵌入式SRID信息。

要素的扩展空间对象的文本表示(EWKT)的示例如下。

  • POINT(0 0 0)-XYZ

  • SRID = 32632; POINT(0 0)-具有SRID的XY

  • POINTM(0 0 0)-XYM

  • POINT(0 0 0 0)-XYZM

  • SRID = 4326; MULTIPOINTM(0 0 0,1 2 1)-具有SRID的XYM

  • 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),CICCULARSTRING(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 9,9 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); 文字EWKT = ST_AsEWKT(geometry); geometry = ST_GeomFromEWKB(bytea EWKB); geometry = ST_GeomFromEWKT(text EWKT);

例如,用于创建和插入PostGIS空间对象的有效插入语句为:

INSERT INTO geotable(the_geom,the_name) 值(ST_GeomFromEWKT('SRID = 312; POINTM(-126.4 45.32 15)'),'A Place')

PostgreSQL类型的“规范形式”是通过简单查询(没有任何函数调用)获得的表示形式,并且可以保证通过简单的插入,更新或复制就可以接受这些表示形式。对于PostGIS“几何”类型,这些类型是:

-输出 -二进制:EWKB ASCII:HEXEWKB(十六进制形式的EWKB) -输入 -二进制:EWKB ASCII:HEXEWKB | EWKT 

例如,以下语句在标准ascii输入/输出过程中读取EWKT并返回HEXEWKB:

=#选择'SRID = 4; POINT(0 0)':: geometry; 几何 -------------------------------------------------- - 01010000200400000000000000000000000000000000000000000000 (1列)

4.1.3。SQL-MM第3部分

SQL多媒体应用程序空间规范通过定义许多圆形插值曲线,扩展了SQL规范的简单功能。

SQL-MM定义包括3DM,3DZ和4D坐标,但不允许嵌入SRID信息。

尚不完全支持“知名文本”扩展。一些简单的弯曲几何的示例如下所示:

  • 循环发送(0 0,1 1,1 0)

    循环发送(0 0,4 0,4 4,0 4,0 0)

    CIRCULARSTRING是基本曲线类型,类似于线性世界中的LINESTRING。单个线段需要三个点,即起点和终点(第一个和第三个点)以及弧上的任何其他点。例外是封闭圆,其起点和终点相同。在这种情况下,第二点必须是圆弧的中心,即圆的另一侧。要将弧链接在一起,就像在LINESTRING中一样,上一个弧的最后一个点将成为下一个弧的第一个点。这意味着有效的圆形字符串必须具有大于1的奇数个点。

  • 复合曲线(圆形(0 0,1 1,1 0),(1 0,0 1))

    复合曲线是具有弯曲(圆形)段和线性段的单个连续曲线。这意味着,除了具有格式正确的组件之外,每个组件的端点(最后一个组件除外)必须与后续组件的起点重合。

  • 曲线多边形(循环(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))

    曲线多边形就像一个多边形,具有一个外环和零个或多个内环。区别在于,环可以采用圆线,线性线或复合线的形式。

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

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

    MULTICURVE是曲线的集合,可以包括线性弦,圆形弦或复合弦。

  • 多表面(曲线聚合物(圆形(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)))

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

[注意]  

SQL-MM实现中的所有浮点比较均以指定的公差(当前为1E-8)执行。

4.2。PostGIS地理类型

地理类型为“地理”坐标(有时称为“大地坐标”或“纬度/经度”或“纬度/经度”)上表示的空间要素提供了原生支持。地理坐标是以角度单位(度)表示的球坐标。

PostGIS几何类型的基础是平面。平面上两点之间的最短路径是一条直线。这意味着可以使用笛卡尔数学和直线矢量来计算几何形状(面积,距离,长度,交点等)。

PostGIS地理类型的基础是一个球体。球面上两点之间的最短路径是大圆弧。这意味着必须使用更复杂的数学方法在球体上计算地形(区域,距离,长度,交点等)。为了获得更准确的测量结果,计算必须考虑到实际的球体形状。

因为基础数学要复杂得多,所以为地理类型定义的功能要比为几何类型定义的功能少。随着时间的流逝,随着新算法的添加,地理类型的功能将会扩展。

它使用称为的数据类型geography。GEOS函数均不支持该geography 类型。作为一种解决方法,可以在几何类型和地理类型之间来回转换。

在PostGIS 2.2之前,地理类型仅支持WGS 84 long lat(SRID:4326)。对于PostGIS 2.2及更高版本,spatial_ref_sys可以使用表中定义的任何基于长/纬度的空间参考系统。您甚至可以添加自己的自定义球状空间参考系统,因为地理类型不限于地球

无论使用哪种空间参照系,测量返回的单位(ST_DistanceST_LengthST_PerimeterST_Area)以及用于输入ST_DWithin的单位均为米。

地理位置类型使用PostgreSQL typmod定义格式,因此可以在一个步骤中添加带有地理位置字段的表。支持所有标准OGC格式(曲线除外)。

4.2.1。地理基础

地理类型不支持曲线,TINS或POLYHEDRALSURFACE,但是支持其他几何类型。如果标准几何类型数据是SRID 4326,则将自动广播到地理。您还可以使用EWKT和EWKB约定插入数据。

  • 要点:未指定srid时,使用2D点地理创建表格的默认默认值为4326 WGS 84 long lat:

    创建表ptgeogwgs(gid序列主键,geog地理(POINT));

    要点:在NAD83 longlat中使用2D点地理创建表格:

    创建表ptgeognad83(gid序列主键,geog geography(POINT,4269));

    创建具有z坐标点的表并明确指定srid

    创建表ptzgeogwgs84(gid序列主键,geog地理(POINTZ,4326));
  • 划线

    创建表lgeog(gid序列主键,geog地理(LINESTRING));
  • 多边形

    --polygon NAD 1927 long lat 创建表lgeognad27(gid序列主键,geog地理(POLYGON,4267));
  • 多点

  • 多行

  • 多聚脂

  • 几何采集

地理字段在geography_columns系统视图中注册。

现在,检查“ geography_columns”视图,看看表已列出。

您可以使用CREATE TABLE语法使用GEOGRAPHY列创建一个新表。

 

创建表global_points( id串行主键, 名称VARCHAR(64), 地理位置GEOGRAPHY(POINT,4326) );

 

请注意,位置列的类型为GEOGRAPHY,并且地理类型支持两个可选的修饰符:类型修饰符,用于限制列中允许的形状和尺寸的类型;SRID修饰符,用于将坐标参考标识符限制为特定数字。

类型修饰符的允许值为:POINT,LINESTRING,POLYGON,MULTIPOINT,MULTILINESTRING,MULTIPOLYGON。修改器还通过后缀Z,M和ZM支持尺寸限制。因此,例如,“ LINESTRINGM”的修饰符将只允许使用三个维度的线串,并将第三个维度视为度量。同样,“ POINTZM”将需要四维数据。

如果未指定SRID,则SRID将默认为4326 WGS 84 long / lat,将使用WGS84进行所有计算。

创建表后,您可以在GEOGRAPHY_COLUMNS表中看到它:

-查看元数据视图的内容 SELECT * FROM geography_columns;

您可以像使用GEOMETRY列一样将数据插入表中:

-将一些数据添加到测试表中 插入到global_points(名称,位置)值('Town','SRID = 4326; POINT(-110 30)'); 插入到global_points(名称,位置)值('Forest','SRID = 4326; POINT(-109 29)'); INSERT INTO global_points(名称,位置)VALUES('London','SRID = 4326; POINT(0 49)');

创建索引的方式与GEOMETRY相同。PostGIS将注意,列类型为GEOGRAPHY,并创建适当的基于球面的索引,而不是用于GEOMETRY的常规平面索引。

-用球形索引对测试台进行索引 使用GIST创建位置索引global_points_gix ON global_points(location);

 

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

-显示距离查询和注释,伦敦超出了1000km的公差范围 从global_points中选择名称WHERE ST_DWithin(位置,'SRID = 4326; POINT(-110 29)':: geography,1000000);

 

通过计算从西雅图飞往伦敦的飞机(LINESTRING(-122.33 47.606,0.0 51.5))与雷克雅未克(POINT(-21.96 64.15))的距离,可以看到GEOGRAPHY的作用。

-使用GEOGRAPHY进行距离计算(122.2km) SELECT ST_Distance('LINESTRING(-122.33 47.606,0.0 51.5)':: geography,'POINT(-21.96 64.15)':: geography);

 

-使用GEOMETRY(13.3“度”)进行距离计算 SELECT ST_Distance('LINESTRING(-122.33 47.606,0.0 51.5)':: geometry,'POINT(-21.96 64.15)':: geometry);

 

测试不同的lon / lat项目。spatial_ref_sys允许使用表中列出的任何长纬度空间参照系。

 

-NAD 83 lon / lat SELECT'SRID = 4269; POINT(-123 34)':: geography; 地理 -------------------------------------------------- - 0101000020AD1000000000000000C05EC00000000000004140 (1列)

 

-NAD27 lon / lat SELECT'SRID = 4267; POINT(-123 34)':: geography; 地理 -------------------------------------------------- - 0101000020AB1000000000000000C05EC00000000000004140 (1列)

 

-NAD83 UTM区域仪表,由于其基于仪表的投影而产生误差 SELECT'SRID = 26910; POINT(-123 34)':: geography; 错误:地理区域仅支持lon / lat坐标系。 第1行:SELECT'SRID = 26910; POINT(-123 34)':: geography;

GEOGRAPHY类型可计算雷克雅未克与西雅图和伦敦之间的大圆弧飞行路径之间的球面真正的最短距离。

Great Circle映射器 GEOMETRY类型可计算雷克雅未克与在世界平面地图上绘制的从西雅图到伦敦的直线路径之间的无意义的笛卡尔距离。结果的名义单位可能被称为“度”,但结果并不对应于点之间的任何真实角度差,因此即使将其称为“度”也是不准确的。

4.2.2。何时使用Geometry数据类型而不是Geometry数据类型

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

您选择的类型应取决于您正在构建的应用程序的预期工作区域。您的数据将遍及全球还是整个大洲地区,还是位于州,县或市的本地数据?

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

  • 如果您的数据是全球性的或覆盖整个大陆地区,则可能会发现GEOGRAPHY允许您构建系统而不必担心投影细节。您以经度/纬度存储数据,并使用在GEOGRAPHY上定义的功能。

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

请参阅第14.11节“ PostGIS功能支持列表”,以比较地理支持和几何支持。有关地理功能的简要列表和说明,请参见 第14.4节“ PostGIS地理支持功能”

4.2.3。地理高级常见问题解答

4.2.3.1。您是在球体还是球体上计算?

4.2.3.2。日期线和极点呢?

4.2.3.3。您可以加工的最长电弧是什么?

4.2.3.4。为什么计算欧洲/俄罗斯的区域/在此处插入大的地理区域这么慢?

4.2.3.1。

您是在球体还是球体上计算?

 

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

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

4.2.3.2。

日期线和极点呢?

 

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

4.2.3.3。

您可以加工的最长电弧是什么?

 

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

4.2.3.4。

为什么计算欧洲/俄罗斯的区域/在此处插入大的地理区域这么慢?

 

因为多边形是如此的庞大!大区域是不好的,这有两个原因:边界很大,因此无论您运行哪种查询,索引都倾向于拉动特征;顶点数量巨大,并且测试(距离,约束)必须遍历顶点列表至少一次,有时甚至遍历N次(其中N是其他候选要素中的顶点数量)。

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

4.3。使用OpenGIS标准

OpenGIS“ SQL的简单功能规范”定义了标准的GIS对象类型,操作它们所需的功能以及一组元数据表。为了确保元数据保持一致,将通过OpenGIS定义的特殊过程执行创建和删除空间列之类的操作。

有两个OpenGIS元数据表: SPATIAL_REF_SYS和 GEOMETRY_COLUMNS。该 SPATIAL_REF_SYS表包含空间数据库中使用的坐标系统的数字ID和文本说明。

4.3.1。SPATIAL_REF_SYS表和空间参考系统

spacear_ref_sys表是包括PostGIS且符合OGC的数据库表,该表列出了3000多个已知的空间参考系统 以及在它们之间进行转换/重新投影所需的详细信息。

尽管PostGISspatial_ref_sys表包含可以由proj库处理的3000多种更常用的空间参考系统定义,但它不包含人类已知的所有定义,如果您熟悉proj4构造,则可以定义自己的自定义投影。请记住,大多数空间参照系是区域性的,在其预期范围之外使用时没有意义。

http://spatialreference.org/是查找核心集中未定义的空间参照系的绝佳资源。

一些更常用的空间参照系是:4326-WGS 84长纬度, 4269-NAD 83长纬度, 3395-WGS 84世界墨卡托, 2163-美国国家地图集相等面积,每个NAD 83,WGS 84的空间参照系UTM区域-UTM区域是最理想的测量区域之一,但仅覆盖6度区域。

各种美国州平面空间参考系统(基于米或英尺)-每个美国州通常存在一个或两个。大多数仪表都在核心集中,但是您需要从spatialreference.org中提取许多基于英尺的仪表或由ESRI创建的仪表

有关确定您感兴趣的区域使用哪个UTM区域的详细信息,请查看utmzone PostGIS plpgsql helper函数

SPATIAL_REF_SYS表的定义如下:

创建表spatial_ref_sys( srid INTEGER NOT NULL PRIMARY KEY, auth_name VARCHAR(256), auth_srid INTEGER, srtext VARCHAR(2048), proj4text VARCHAR(2048) )

SPATIAL_REF_SYS列如下:

SRID

一个整数值,用于唯一标识数据库中的空间参考系统(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”, 螺旋体[“ GRS 1980”,6378137,298.257222101] ], PRIMEM [“格林威治”,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] ]

有关EPSG投影代码及其对应的WKT表示形式的列表,请参见http://www.opengeospatial.org/。有关WKT的一般讨论,请参见http://www.opengeospatial.org/standards上的OpenGIS“坐标转换服务实现规范” 。有关欧洲石油调查组织(EPSG)及其空间参考系统数据库的信息,请参见http://www.epsg.org

PROJ4TEXT

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

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

有关更多信息,请参见Proj4网站,网址为http://trac.osgeo.org/proj/。该spatial_ref_sys.sql文件包含 所有EPSG投影的SRTEXTPROJ4TEXT定义。

4.3.2。GEOMETRY_COLUMNS视图

GEOMETRY_COLUMNS是从数据库系统目录中读取的视图。其结构如下:

\ d geometry_columns
 查看“ public.geometry_columns” 专栏 类型 修饰符 ------------------- + ------------------------ + ----- ------ f_table_catalog | 字符变化(256)| f_table_schema | 字符变化(256)| f_table_name | 字符变化(256)| f_geometry_column | 字符变化(256)| coord_dimension | 整数| srid | 整数| 类型 角色变化(30)|

列的含义是:

F_TABLE_CATALOG,F_TABLE_SCHEMA,F_TABLE_NAME

包含几何列的特征表的完全限定名称。请注意,术语“目录”和“模式”是类似于Oracle的。没有PostgreSQL的“目录”类似物,因此该列留为空白-对于“模式”,使用PostgreSQL架构名称(public默认设置)。

F_GEOMETRY_COLUMN

特征表中几何列的名称。

COORD_DIMENSION

列的空间尺寸(2、3或4维)。

SRID

该表中用于坐标几何的空间参照系的ID。它是对的外键引用SPATIAL_REF_SYS

类型

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

[注意]  

该属性(可能)不是OpenGIS规范的一部分,但是对于确保类型同质性是必需的。

4.3.3。创建空间表

只需一步就可以创建包含空间数据的表格。如下例所示,该示例使用WGS84长纬度中的2D线串几何列创建道路表

创建表格道路(ID序列,ROAD_NAME文本,geom几何形状(LINESTRING,4326));

我们可以使用标准ALTER TABLE命令添加其他列,就像在下一个示例中添加3D线串的操作一样。

ALTER TABLE道路添加COLUMN geom2几何形状(LINESTRINGZ,4326);

4.3.4。在geometry_columns中手动注册几何列

您可能需要的两种情况是SQL视图和批量插入。对于批量插入的情况,您可以通过约束列或执行alter table来更正geometry_columns表中的配准。对于视图,可以使用CAST操作公开。注意,如果您的列是基于typmod的,则创建过程将正确注册它,因此无需执行任何操作。同样,没有对几何应用空间功能的视图将与基础表几何列注册相同。

-假设您有一个这样创建的视图 创建视图public.vwmytablemercator AS SELECT gid,ST_Transform(geom,3395)作为geom,f_name 来自public.mytable; -使其正确注册 -您需要铸造几何 - 删除视图public.vwmytablemercator; 创建视图public.vwmytablemercator AS SELECT gid,ST_Transform(geom,3395):: geometry(Geometry,3395)作为geom,f_name 来自public.mytable; -如果您确定几何类型肯定是2D多边形,则可以 删除视图public.vwmytablemercator; 创建视图public.vwmytablemercator AS SELECT gid,ST_Transform(geom,3395):: geometry(Polygon,3395)as geom,f_name 来自public.mytable;
-假设您通过批量插入创建了派生表 选择poi.gid,poi.geom,citybounds.city_name INTO myschema.my_special_pois 从poi INNER JOIN ST_Intersects上的citybounds(citybounds.geom,poi.geom); -在新表上创建2D索引 创建索引idx_myschema_myspecialpois_geom_gist 在myschema.my_special_pois上使用gist(geom); -如果您的点是3D点或3M点, -那么您可能想要创建nd索引而不是2D索引 创建索引my_special_pois_geom_gist_nd 在my_special_pois上使用gist(geom gist_geometry_ops_nd); -手动在geometry_columns中注册此新表的geometry列。 -请注意,它还将表的基础结构更改为 -使列基于typmod。 选择populate_geometry_columns('myschema.my_special_pois':: regclass); -如果您出于某种原因使用PostGIS 2.0 -您需要基于约束的定义行为 -(例如继承表的情况,其中所有子表都不具有相同的类型和srid) -将可选的use_typmod参数设置为false 选择populate_geometry_columns('myschema.my_special_pois':: regclass,false); 

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

创建表pois_ny(gid SERIAL PRIMARY KEY,poi_name文本,cat文本,geom geometry(POINT,4326)); SELECT AddGeometryColumn('pois_ny','geom_2160',2160,'POINT',2,false);

如果我们在psql中运行

\ d pois_ny;

我们发现它们的定义不同-一种是typmod,一种是约束

 表“ public.pois_ny” 专栏 类型 修饰符 ----------- + ----------------------- + -------------- ---------------------------------------- gid | 整数| 不为null的默认值nextval('pois_ny_gid_seq':: regclass) poi_name | 文字| 猫| 角色变化(20)| geom | 几何(Point,4326)| geom_2160 | 几何| 索引: “ pois_ny_pkey”主键,btree(gid) 检查约束: “ enforce_dims_geom_2160”检查(st_ndims(geom_2160)= 2) “ enforce_geotype_geom_2160”检查(geometrytype(geom_2160)='POINT':: text OR geom_2160为NULL) “ enforce_srid_geom_2160”检查(st_srid(geom_2160)= 2160)

在geometry_columns中,它们都正确注册

选择f_table_name,f_geometry_column,srid,类型 从geometry_columns 在哪里f_table_name ='pois_ny';
f_table_name | f_geometry_column | srid | 类型 ------------- + ------------------- + ------ + ------- pois_ny | geom | 4326 | 点 pois_ny | geom_2160 | 2160 | 点

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

创建视图vw_pois_ny_parks AS 选择 * 来自pois_ny 哪里cat ='park'; 选择f_table_name,f_geometry_column,srid,类型 从geometry_columns 在哪里f_table_name ='vw_pois_ny_parks';

基于typmod的geom视图列正确注册,但基于约束的列未正确注册。

 f_table_name | f_geometry_column | srid | 类型 ------------------ + ------------------- + ------ + ---- ------ vw_pois_ny_parks | geom | 4326 | 点 vw_pois_ny_parks | geom_2160 | 0 | 几何

这在PostGIS的将来版本中可能会发生变化,但是现在要强制基于约束的视图列正确注册,我们需要这样做:

DROP VIEW vw_pois_ny_parks; 创建视图vw_pois_ny_parks AS SELECT gid,poi_name,cat, 几何, geom_2160 :: geometry(POINT,2160)为geom_2160 来自pois_ny 其中cat ='park'; 选择f_table_name,f_geometry_column,srid,类型 从geometry_columns 在哪里f_table_name ='vw_pois_ny_parks';
 f_table_name | f_geometry_column | srid | 类型 ------------------ + ------------------- + ------ + ---- --- vw_pois_ny_parks | geom | 4326 | 点 vw_pois_ny_parks | geom_2160 | 2160 | 点

4.3.5。确保OpenGIS符合几何

PostGIS符合开放地理空间联盟(OGC)的OpenGIS规范。因此,许多PostGIS方法要求或更准确地假设要操作的几何既简单又有效。例如,计算在多边形外部定义了孔的多边形的面积,或者从非简单边界线构造多边形是没有意义的。

根据OGC规范,简单 几何是没有异常几何点(例如自相交或自相切[MULTI]POINT, [MULTI]LINESTRING)的几何,主要指0或1维几何(即)。另一方面,几何有效性主要是指二维几何(即[MULTI]POLYGON),它定义了表征有效多边形的一组断言。每个几何类别的描述都包含特定条件,这些条件进一步详述了几何简单性和有效性。

作为一个零维几何对象,A POINT容易继承。

MULTIPOINT如果没有两个坐标相等(坐标值相同),则s很简单POINT

LINESTRING简单,如果它不通过相同的POINT两次(除端点,在这种情况下它被称为一个线性环和另外考虑关闭)。

(一种)

(b)

(C)

(d)

(a)和 (c)是简单 LINESTRINGs,(b) 和(d)不是。

MULTILINESTRING简单 仅当所有元素为简单和任何两个元件之间的唯一的交叉点处出现POINTS中的值为在两个元件的边界。

(e)

(F)

(G)

(e)和 (f)是简单 MULTILINESTRINGs,(g) 不是。

根据定义,a POLYGON总是 很简单。如果边界中没有两个环(由外环和内环组成)交叉,则是有效的。a的边界POLYGON可以在a处相交, POINT但仅以切线相交(即不在一条线上)。A POLYGON可能没有切割线或尖刺,并且内圈必须完全包含在外圈内。

(H)

(一世)

(j)

(k)

(l)

(米)

(h)和 (i)是有效 POLYGONs,(jm) 不能表示为单个POLYGONs,但是 (j)(m) 可以表示为有效MULTIPOLYGON

一个MULTIPOLYGON有效的 ,当且仅当它的所有元素是有效的,并没有两个元素的内部相交。任何两个元素的边界都可以接触,但只能接触有限的POINTs。

(n)

(o)

(p)

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

GEOS库实现的大多数功能都基于这样的假设,即您的几何形状是有效的(如OpenGIS简单要素规范所指定)。要检查几何的简单性或有效性,可以使用ST_IsSimple()和 ST_IsValid()

-通常情况下,检查没有意义 -用于线性特征的有效性,因为它将始终返回TRUE。 -但是在此示例中,PostGIS扩展了OGC IsValid的定义 -如果LineString的顶点少于2个* distinct *,则返回false。 gisdb =#选择 ST_IsValid('LINESTRING(0 0,1 1)'), ST_IsValid('LINESTRING(0 0,0 0,0 0)'); st_isvalid | st_isvalid ------------ + ----------- t | F

默认情况下,PostGIS不会对几何输入应用此有效性检查,因为对于复杂几何(尤其是多边形)进行有效性测试需要大量的CPU时间。如果您不信任数据源,则可以通过添加检查约束来对表手动执行这种检查:

ALTER TABLE mytable 添加CONSTRAINT geometry_valid_check 检查(ST_IsValid(the_geom));

如果您遇到任何奇怪的错误消息,例如“ GEOS Intersection()抛出错误!”。当使用有效的输入几何调用PostGIS函数时,您可能在PostGIS或它使用的一个库中发现了错误,应联系PostGIS开发人员。如果PostGIS函数为有效输入返回了无效的几何体,则同样如此。

[注意]  

严格符合要求的OGC几何形状不能具有Z或M值。该 ST_IsValid()函数不会考虑更高的尺寸几何无效!调用AddGeometryColumn()将添加一个约束检查几何尺寸,因此在此处指定2就足够了。

4.3.6。尺寸扩展9交叉模型(DE-9IM)

有时,典型的空间谓词(ST_IntersectsST_Contains, ST_CrossesST_Touches,...)本身不足以充分提供所需的空间过滤器。

例如,考虑代表道路网络的线性数据集。GIS分析师的任务可能是确定彼此交叉的所有路段,而不是在某个点而是一条线上,这可能会使某些业务规则无效。在这种情况下,ST_Crosses不能充分提供必要的空间滤波器,因为对于线性要素ST_Crossestrue仅返回它们在某个点相交的位置。

一两步解决办法可能是先进行实际的交集(ST_Intersection对路段在空间相交(的)ST_Intersects),然后比较路口的 ST_GeometryType与“ LINESTRING”(正确处理案件的回报 GEOMETRYCOLLECTION第 [MULTI]POINTS, [MULTI]LINESTRINGs和等等。)。

确实可能需要更优雅/更快的解决方案。

 

第二个[理论]示例可能是GIS分析人员试图定位与湖上边界相交的所有码头或码头的一条线上,而码头只有一端在岸上。换句话说,码头在湖中,但不完全在湖中,与湖的边界在一条直线上相交,并且码头的端点既在湖中又在湖的边界内。分析人员可能需要使用空间谓词的组合来隔离所寻求的特征:

因此,输入Dimensionally Extended 9交叉模型,简称DE-9IM。

4.3.6.1。理论

根据适用于SQLOpenGIS简单功能实现规范,“比较两个几何的基本方法是对两个几何的内部,边界和外部之间的相交进行成对测试,并基于这两个几何之间的关系进行分类在结果“交集”矩阵中的条目上。”

边界

几何的边界是下一个较低维度的一组几何。对于POINT尺寸为0的s,边界是空集。a的边界LINESTRING是两个端点。对于POLYGONs,边界是组成外环和内环的线。

内部

几何图形的内部是移除边界时所保留的几何图形的那些点。对于 POINTs,内部就是 POINT本身。a的内部是LINESTRING端点之间的一 组实点。对于POLYGONs,内部是多边形内部的面积表面。

外观

几何的外部是宇宙,是一个曲面,而不是在几何的内部或边界上。

给定几何a,其中 I(a)B(a)和 E(a)是a的Interior, BoundaryExterior,矩阵的数学表示为:

  内部 边界 外观
内部 暗(I(a)∩I(b)) 昏暗(I(a)∩B(b)) 暗(I(a)∩E(b))
边界 昏暗(B(a)∩I(b)) 昏暗(B(a)∩B(b)) 暗(B(a)∩E(b))
外观 昏暗(E(a)∩I(b)) 暗(E(a)∩B(b)) 暗(E(a)∩E(b))

其中dim(a)ST_Dimension指定 的a的尺寸, 但具有以下范围: {0,1,2,T,F,*}

  • 0 =>点

  • 1 =>行

  • 2 =>面积

  • T => {0,1,2}

  • F =>空集

  • * =>不在乎

在视觉上,对于两个重叠的多边形几何,看起来像:

 

 

  内部 边界 外观
内部

昏暗(...)= 2

dim(...)= 1

昏暗(...)= 2

边界

dim(...)= 1

昏暗(...)= 0

dim(...)= 1

外观

昏暗(...)= 2

dim(...)= 1

昏暗(...)= 2

从左至右和从上至下读取,尺寸矩阵表示为“ 212101212 ”。

因此,表示我们在一条线上相交的两条线的第一个示例的相关矩阵为:' 1 * 1 *** 1 ** '

-确定在一条线上交叉的路段 选择一个id 从道路a,道路b 在哪里a.id!= b.id AND a.geom && b.geom AND ST_Relate(a.geom,b.geom,'1 * 1 *** 1 **');

表示部分在湖岸线上的码头的第二个示例的相关矩阵为' 102101FF2 '

-识别湖岸上的部分码头 选择a.lake_id,b.wharf_id 从湖泊a,码头b a.geom && b.geom在哪里 AND ST_Relate(a.geom,b.geom,'102101FF2');

有关更多信息或阅读,请参阅:

4.4。加载GIS(矢量)数据

创建空间表后,就可以将GIS数据上传到数据库了。当前,有两种方法可以将数据获取到PostGIS / PostgreSQL数据库中:使用格式化的SQL语句或使用Shape文件加载器/转储器。

4.4.1。使用SQL加载数据

如果可以将数据转换为文本表示形式,则使用格式化的SQL可能是将数据导入PostGIS的最简单方法。与Oracle和其他SQL数据库一样,可以通过将装满SQL“ INSERT”语句的大型文本文件管道传输到SQL终端监视器来批量加载数据。

数据上传文件(roads.sql例如)可能如下所示:

开始; 插入道路(road_id,roads_geom,road_name) 值(1,'LINESTRING(191232 243118,191108 243242)','Jeff Rd'); 插入道路(road_id,roads_geom,road_name) 值(2,'LINESTRING(189141 244158,189265 244817)','Geordie Rd'); 插入道路(road_id,roads_geom,road_name) 值(3,'LINESTRING(192783 228138,192612 229814)','Paul St'); 插入道路(road_id,roads_geom,road_name) 值(4,'LINESTRING(189412 252431,189631 259122)','Graeme Ave'); 插入道路(road_id,roads_geom,road_name) 值(5,'LINESTRING(190131 224148,190871 228134)','Phil Tce'); 插入道路(road_id,roads_geom,road_name) 值(6,'LINESTRING(198231 263418,198213 268322)','Dave Cres'); 承诺;

使用“ psql” SQL终端监视器,可以非常轻松地将数据文件通过管道传递到PostgreSQL:

psql -d [数据库] -f roads.sql

4.4.2。shp2pgsql:使用ESRI Shapefile加载程序

所述shp2pgsql数据加载器转换ESRI Shape文件到SQL适于插入一个的PostGIS / PostgreSQL数据库或者在几何或地理格式。加载程序具有几种操作模式,这些模式通过命令行标志来区分:

除了shp2pgsql命令行加载器之外,还有一个shp2pgsql-gui图形界面,其中大多数选项都可以作为命令行加载器使用,但是对于一次性的非脚本加载或如果您是PostGIS的新手,则可能更易于使用。也可以将其配置为PgAdminIII的插件。

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

 

-C

创建一个新表并从shapefile中填充它。这是默认模式。

-一种

将Shape文件中的数据追加到数据库表中。请注意,要使用此选项加载多个文件,文件必须具有相同的属性和相同的数据类型。

-d

在使用Shape文件中的数据创建新表之前,请删除数据库表。

-p

仅生成表创建SQL代码,而不添加任何实际数据。如果需要将表创建和数据加载步骤完全分开,可以使用此方法。

 

-?

显示帮助屏幕。

-D

对输出数据使用PostgreSQL“转储”格式。可以与-a,-c和-d结合使用。与默认的“插入” SQL格式相比,加载速度要快得多。将此用于非常大的数据集。

-s [<FROM_SRID>:] <SRID>

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

-k

保留标识符的大小写(列,架构和属性)。请注意,Shapefile中的属性全部为大写。

-一世

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

-一世

在几何列上创建一个GiST索引。

-米

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

列名DBFFIELD1 AVERYLONGCOLUMNNAME DBFFIELD2

 

-S

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

-t <维度>

强制输出几何具有指定的尺寸。使用以下字符串表示尺寸:2D,3DZ,3DM,4D。

如果输入的尺寸小于指定的尺寸,则输出将使用零填充这些尺寸。如果输入具有指定的更多尺寸,则将去除不需要的尺寸。

-w

输出WKT格式,而不是WKB。请注意,由于精度损失,这可能导致坐标漂移。

-e

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

-W <编码>

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

-N <策略>

NULL几何处理策略(插入*,跳过,中止)

-n

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

-G

在WGS84长纬度(SRID = 4326)中使用地理类型而不是几何(需要lon / lat数据)

-T <表空间>

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

-X <表空间>

为新表的索引指定表空间。这适用于主键索引,如果还使用-I,则适用于GIST空间索引。

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

#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.5。检索GIS数据

可以使用SQL或Shape文件加载器/转储器从数据库中提取数据。在有关SQL的部分中,我们将讨论一些可用于对空间表进行比较和查询的运算符。

4.5.1。使用SQL检索数据

从数据库中提取数据的最直接方法是使用SQL选择查询来减少返回的RECORDS和COLUMNS的数量,并将结果列转储到可解析的文本文件中:

db =#SELECT road_id,ST_AsText(road_geom)AS geom,road_name FROM道路; road_id | geom | road_name -------- + ----------------------------------------- + ----------- 1 | LINESTRING(191232 243118,191108 243242)| 杰夫路 2 | LINESTRING(189141 244158,189265 244817)| 乔迪路 3 | LINESTRING(192783 228138,192612 229814)| 保罗·圣 4 | LINESTRING(189412 252431,189631 259122)| 格雷姆大街 5 | LINESTRING(190131 224148,190871 228134)| 菲尔·特斯 6 | LINESTRING(198231 263418,198213 268322)| 戴夫·克里斯 7 | LINESTRING(218421 284121,224123 241231)| 克里斯·韦 (6列)

但是,有时需要某种限制来减少返回的字段数。对于基于属性的限制,只需对非空间表使用与通常相同的SQL语法。在空间限制的情况下,以下操作符可用/有用:

ST_相交

此函数告诉两个几何是否共享任何空间。

=

这测试两个几何形状在几何上是否相同。例如,如果'POLYGON((0 0,1 1,1 0,0 0))'与'POLYGON((0 0,1 1,1 0,0 0))'相同(是)。

注意:在PostGIS 2.4之前,这仅比较了几何形状的盒子。

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

选择road_id,road_name 从公路 WHERE roads_geom ='SRID = 312; LINESTRING(191232 243118,191108 243242)':: geometry;

上面的查询将从“ ROADS_GEOM”表返回单个记录,其中几何等于该值。

要检查某些道路是否在多边形定义的区域内通过:

选择road_id,road_name 从公路 在哪里ST_Intersects(roads_geom,'SRID = 312; POLYGON((...))');

最常见的空间查询可能是“基于框架”的查询,由客户端软件(如数据浏览器和Web映射器)使用,以获取“地图框架”值的数据以供显示。

使用“ &&”运算符时,可以将BOX3D指定为比较特征,也可以指定GEOMETRY。但是,当您指定GEOMETRY时,其边界框将用于比较。

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

SELECT ST_AsText(roads_geom)AS几何 从公路 哪里 roads_geom && ST_MakeEnvelope(191232,243117,191232,243119,312);

请注意,使用SRID 312来指定信封的投影。

4.5.2。使用自卸车

pgsql2shp表自卸直接连接到数据库和转换表(可能由一个查询所定义的)为一定的形状的文件。基本语法为:

pgsql2shp [<选项>] <数据库> [<模式>。] <表>
pgsql2shp [<选项>] <数据库> <查询>

命令行选项是:

-f <文件名>

将输出写入特定的文件名。

-h <主机>

要连接的数据库主机。

-p <端口>

数据库主机上要连接的端口。

-P <密码>

连接到数据库时使用的密码。

-u <用户>

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

-g <几何列>

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

-b

使用二进制游标。这将使操作更快,但是如果表中的任何NON-geometry属性都缺少转换为文本的功能,则将无法使用。

-r

原始模式。不要删除该gid字段,也不要转义列名。

-米 filename

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

4.6。建筑指标

索引使使用空间数据库处理大型数据集成为可能。如果不建立索引,则对功能的任何搜索都需要对数据库中的每个记录进行“顺序扫描”。索引通过将数据组织到搜索树中来加快搜索速度,可以快速遍历搜索树以查找特定记录。PostgreSQL默认支持三种索引:B树索引,SP-GiST和GiST索引。

  • B树用于可沿一个轴排序的数据;例如数字,字母,日期。空间数据可以沿着空间填充曲线,Z阶曲线或希尔伯特曲线进行排序。但是,此表示不允许加快常见操作。

  • GiST(通用搜索树)索引将数据分为“一侧的事物”,“重叠的事物”,“内部的事物”,并且可以用于包括GIS数据在内的各种数据类型。PostGIS使用在GiST之上实现的R-Tree索引来索引GIS数据。

4.6.1。GiST索引

GiST代表“通用搜索树”,是索引的一种通用形式。除GIS索引外,GiST还用于加快对不适合常规B树索引的各种不规则数据结构(整数数组,光谱数据等)的搜索。

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

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

创建索引[indexname] ON [tablename] using GIST([geometryfield]); 

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

创建索引[索引名]开启[表名]使用GIST([geometryfield] gist_geometry_ops_nd);

建立空间索引是一项需要大量计算的工作。它还会在创建表时阻止对表的写访问,因此在生产系统上,您可能希望以较慢的CONCURRENTLY感知方式执行该操作:

使用GIST([geometryfield])在[tablename]上同时创建索引[indexname]; 

构建索引后,有时强制PostgreSQL收集表统计信息(用于优化查询计划)会有所帮助:

VACUUM ANALYZE [table_name] [(column_name)];

4.6.2。BRIN指数

BRIN代表“块范围索引”,是PostgreSQL 9.5中引入的一种通用索引形式。BRIN是一种有损索引,它的主要用途是在读写性能方面提供一个折衷方案。它的主要目标是处理非常大的表,其中某些列与其在表中的物理位置具有某些自然相关性。除了GIS索引编制之外,BRIN还用于加快对各种规则或不规则数据结构(整数,数组等)的搜索。

一旦GIS数据表超过几千行,您将希望建立一个索引来加速数据的空间搜索(除非所有搜索都基于属性,在这种情况下,您将希望在数据库上建立普通索引)。属性字段)。只要GiST索引的大小不超过数据库可用的RAM数量,并且只要您能够负担得起存储大小和写入工作量的损失,GiST索引就可以真正发挥作用。否则,可以将BRIN指数视为替代方案。

BRIN索引的想法是只存储包含在一组表块(称为范围)中的所有行中包含的所有几何图形的绑定框。显然,这种索引方法仅在以物理方式对数据进行排序的情况下才有效,而对于块范围而言,生成的绑定框将是互斥的。所得的索引确实很小,但是在许多情况下,效率不如GiST索引。

建立BRIN索引的强度要比建立GiST索引的强度低。建立BRIN索引比GiST索引所需的时间少十倍是很常见的。由于BRIN索引只为一个或多个表块存储一个绑定框,因此这种索引消耗的磁盘空间最多少一千倍是很常见的。

您可以选择一个范围内要汇总的块数。如果减少此数字,索引将更大,但可能有助于获得更好的性能。

在“ geometry”列上建立BRIN索引的语法如下:

使用BRIN([geometryfield])将CREATE INDEX [indexname] ON [tablename]打开; 

上面的语法将始终建立2D索引。要获得3D维度索引,您可以使用以下语法创建一个

使用BRIN在[表名]上创建索引[索引名]到[表名]([geometryfield] brin_geometry_inclusion_ops_3d);

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

使用BRIN在[表名]上创建索引[索引名]到[表名]([geometryfield] brin_geometry_inclusion_ops_4d);

以上这些语法将使用范围内的默认数字或块,即128。要指定要在该范围内汇总的块数,可以使用此语法创建一个

CREATE INDEX [indexname] ON [tablename]使用BRIN([geometryfield])WITH(pages_per_range = [number]); 

另外,请记住,BRIN索引只会为大量的行存储一个索引值。如果您的表存储的几何尺寸混合在一起,则结果索引的性能可能很差。您可以通过选择存储的几何尺寸最少的运算符类别来避免这种性能下降

BRIN索引还支持“地理位置”数据类型。在“地理位置”列上建立BRIN索引的语法如下:

CREATE INDEX [indexname] ON [tablename]使用BRIN([geographyfield]); 

上面的语法将始终为球体上的地理空间对象建立2D索引。

目前,仅仅是“列入支持”在这里认为,这意味着只是&&~和 @运营商可用于2D情况下(无论是“几何”和“地理志”),只是在&&& 运营商可用于三维几何形状。目前不支持kNN搜索。

4.6.3。SP-GiST索引

SP-GiST代表“空间分区的通用搜索树”,它是索引的一种通用形式,它支持分区的搜索树,例如四叉树,kd树和基数树(重试)。这些数据结构的共同特征是,它们将搜索空间重复地划分为不必具有相同大小的分区。除GIS索引外,SP-GiST还用于加速对多种数据的搜索,例如电话路由,ip路由,子字符串搜索等。

与GiST索引一样,SP-GiST索引有损,因为它们存储了包围空间对象的边界框。SP-GiST索引可以视为GiST索引的替代方法。性能测试表明,当存在许多重叠的对象(即所谓的“意大利面条数据”)时,SP-GiST索引特别有用。

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

使用SPGIST([geometryfield])在CREATE INDEX [indexname] ON [tablename]上; 

上面的语法将建立一个二维索引。可以使用3D运算符类为几何类型创建3维索引:

使用SPGIST([geometryfield] spgist_geometry_ops_3d)将CREATE INDEX [indexname] ON [tablename]打开;

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

使用SPGIST([geometryfield])在[tablename]上同时创建索引[indexname]; 

构建索引后,有时强制PostgreSQL收集表统计信息(用于优化查询计划)会有所帮助:

VACUUM ANALYZE [table_name] [(column_name)];

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

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

  • &/&,〜==,@ >>和<< @,用于3维索引。

目前不支持kNN搜索。

4.6.4。使用索引

通常,索引会无形地加快数据访问速度:建立索引后,查询计划者会透明地决定何时使用索引信息来加快查询计划。不幸的是,PostgreSQL查询计划程序有时不能很好地优化GiST索引的使用,因此有时应该使用空间索引的搜索可能会对整个表进行顺序扫描。

如果发现您的空间索引没有被使用(或属性索引),则可以执行以下操作:

  • 首先,阅读查询计划并检查您的查询是否实际尝试计算您需要的东西。失控的JOIN条件(被遗忘或错误的表)可能会意外地使您的所有表多次出现。要获取查询计划,请在查询前面添加EXPLAIN关键字。

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

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

  • 如果清理不起作用,则可以通过将set enable_seqscan设置为off来临时强制计划器使用索引信息 命令。这样,您可以检查计划程序是否完全能够为您的查询生成索引加速的查询计划。您只应将此命令用于调试:通常而言,计划者比何时使用索引更了解。运行查询后,请不要忘记重新设置 ENABLE_SEQSCAN查询,这样其他查询将照常使用计划程序。

  • 如果将enable_seqscan设置为off;帮助您的查询运行,您的Postgres可能不适合您的硬件。如果您发现计划者关于顺序扫描和索引扫描的开销不正确,请尝试减小random_page_costpostgresql.conf中的值或将set random_page_cost设置为1.1;否则,请执行以下步骤。。该参数的默认值为4,请尝试将其设置为1(在SSD上)或2(在快速磁盘上)。减小该值可使计划者更倾向于使用索引扫描。

  • 如果将enable_seqscan设置为off;不能帮助您的查询,有可能您使用的是Postgres尚无法解开的结构。带有内联选择的子查询就是一个示例-您需要将其重写为可以优化LATERAL JOIN的表单计划者。

4.7。复杂查询

空间数据库功能的存在正在数据库内部执行查询,而查询通常需要桌面GIS功能。有效地使用PostGIS需要知道哪些空间功能可用,并确保适当的索引可用以提供良好的性能。这些示例中使用的SRID 312仅用于演示。您应该使用在spatial_ref_sys表中列出的REAL SRID,并使用与数据投影相匹配的REAL SRID。如果您的数据未指定空间参考系统,则应仔细考虑为什么没有这样做,也许应该这样做。

如果您的原因是因为要建模的对象没有定义地理空间参考系统,例如分子的内部结构或尚未建成的游乐园的平面图,那么就可以了。但是,如果已经计划了游乐园的位置,那么,只要确保游乐部分不会侵入已经存在的结构,就可以对该位置使用合适的平面坐标系。

即使在您计划火星远征运输人类在核屠杀的事件的情况下,你想绘制出火星星球改造中,你可以使用非尘世的坐标系,如火星2000 进行一次并将其插入spatial_ref_sys表格中。尽管此火星坐标系是非平面的(以球面度为单位),但您可以将其与地理类型一起使用,以长度和接近度单位以米为单位,而不是度数。

4.7.1。利用索引

在构造查询时,重要的是要记住,只有基于边界框的运算符(例如&&)才能利用GiST空间索引。诸如之类的功能 ST_Distance()无法使用索引来优化其操作。例如,在大型表上,以下查询将非常慢:

选择the_geom 从geom_table WHERE ST_Distance(the_geom,'SRID = 312; POINT(100000 200000)')<100

该查询正在选择geom_table中所有在点(100000,200000)的100个单位内的几何。这会很慢,因为它正在计算表中每个点与指定点之间的距离,即。ST_Distance()对表格中的每一行进行一次计算。我们可以通过使用单步索引加速函数ST_DWithin减少所需的距离计算次数来避免这种情况:

选择the_geom 从geom_table WHERE ST_DWithin(the_geom,'SRID = 312; POINT(100000 200000)',100) 

该查询选择相同的几何形状,但是它以更有效的方式进行。假设the_geom上有一个GiST索引,查询计划者将认识到可以在计算ST_Distance() 函数结果之前使用该索引减少行数。请注意,ST_MakeEnvelope&&操作中使用的几何是一个以原始点为中心的200单位的方形框-这是我们的“查询框”。&&运算符使用索引将结果集快速缩减为仅具有边界框与“查询框”重叠的那些几何。假设我们的查询框比整个几何表的范围小得多,这将大大减少需要执行的距离计算的数量。

4.7.2。空间SQL的示例

本节中的示例将使用两个表,即线性道路表和多边形市政边界表。该表的表定义为bc_roads

专栏 类型 描述 ------------ + ------------------- + ----------------- - gid | 整数| 唯一身份 名称| 字符变化| 道路名称 the_geom | 几何| 位置几何(线串)

该表的表定义为bc_municipality :

专栏 类型 描述 ----------- + ------------------- + ------------------ -- gid | 整数| 唯一身份 代码 整数| 唯一身份 名称| 字符变化| 城镇名称 the_geom | 几何| 位置几何(多边形)

4.7.2.1。所有道路的总长度是多少(以公里为单位)?

4.7.2.2。乔治王子城有多少公顷?

4.7.2.3。按地区划分,全省最大的直辖市是什么?

4.7.2.4。每个市政当局完全包含的道路长度是多少?

4.7.2.5。用乔治王子市内的所有道路创建一个新表。

4.7.2.6。维多利亚州“道格拉斯街”的公里长是多少?

4.7.2.7。有洞的最大的自治市多边形是多少?

4.7.2.1。

所有道路的总长度是多少(以公里为单位)?

 

您可以使用非常简单的SQL来回答这个问题:

SELECT sum(ST_Length(the_geom))/ 1000 AS km_roads FROM bc_roads; km_roads ------------------ 70842.1243039643 (1列)

4.7.2.2。

乔治王子城有多少公顷?

 

此查询将属性条件(在自治市名称上)与空间计算(对该区域)结合在一起:

选择 ST_Area(the_geom)/ 10000 AS公顷 来自bc_municipality WHERE name ='PRINCE GEORGE'; 公顷 ------------------ 32657.9103824927 (1列)

4.7.2.3。

按地区划分,全省最大的直辖市是什么?

 

该查询将空间度量引入查询条件。有几种方法可以解决此问题,但最有效的方法如下:

选择 名称, ST_Area(the_geom)/ 10000 AS公顷 从 bc_municipality 按公顷排序DESC LIMIT 1; 名称| 公顷 --------------- + ----------------- TUMBLER RIDGE | 155020.02556131 (1列)

请注意,为了回答此查询,我们必须计算每个多边形的面积。如果我们经常这样做,则可以在表中添加一个Area列,这样我们就可以为性能单独编制索引。通过将结果按降序排列,并使用PostgreSQL的“ LIMIT”命令,我们可以轻松选择最大值,而无需使用诸如max()之类的聚合函数。

4.7.2.4。

每个市政当局完全包含的道路长度是多少?

 

这是“空间联接”的一个示例,因为我们将来自两个表的数据(进行联接)聚集在一起,但是使用空间交互条件(“包含”)作为联接条件,而不是通常的关系方法来联接公用密钥:

选择 m。名称, sum(ST_Length(r.the_geom))/ 1000作为roads_km 从 bc_roads AS r, bc_municipality AS m 哪里 ST_Contains(m.the_geom,r.the_geom) 按名称分组 ORDER BY roads_km; 名称| roads_km ---------------------------- + ------------------ 调查 1539.47553551242 温哥华| 1450.33093486576 兰利区| 833.793392535662 布纳比| 773.769091404338 乔治王子| 694.37554369147 ...

该查询需要一段时间,因为表中的每条道路都汇总为最终结果(对于我们的特定示例表,大约为25万条道路)。对于较小的覆盖图(几百个记录中的几千个),响应速度可能非常快。

4.7.2.5。

用乔治王子市内的所有道路创建一个新表。

 

这是“叠加”的一个示例,它包含两个表并输出一个新表,该表由空间修剪或剪切的结果组成。与上面演示的“空间连接”不同,此查询实际上创建了新的几何。覆盖层就像涡轮增压的空间连接,对于更精确的分析工作很有用:

将表pg_roads创建为 选择 ST_Intersection(r.the_geom,m.the_geom)AS交集_geom, ST_Length(r.the_geom)AS rd_orig_length, * 从 bc_roads AS r, bc_municipality AS m 哪里 m.name ='PRINCE GEORGE' AND ST_Intersects(r.the_geom,m.the_geom);

4.7.2.6。

维多利亚州“道格拉斯街”的公里长是多少?

 
选择 sum(ST_Length(r.the_geom))/ 1000 AS公里 从 bc_roads r, bc_municipality m 哪里 r.name ='道格拉斯街' AND m.name ='维多利亚' AND ST_Intersects(m.the_geom,r.the_geom); 公里 ------------------ 4.89151904172838 (1列)

4.7.2.7。

有洞的最大的自治市多边形是多少?

 
SELECT gid,名称,ST_Area(the_geom)AS区域 来自bc_municipality WHERE ST_NRings(the_geom)> 1 ORDER BY区域DESC LIMIT 1; gid | 名称| 区域 ----- + -------------- + ------------------ 12 | SPALLUMCHEEN | 257374619.430216 (1列)
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部