[第一篇] PostGIS：“我让PG更完美”

About PostGIS

PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial extension. PostGIS follows the OpenGIS "Simple Features Specification for SQL" and has been certified as compliant with the "Types and Functions" profile.

PostGIS development was started by Refractions Research as a project in open source spatial database technology. PostGIS is released under the GNU General Public License. PostGIS continues to be developed by a group of contributors led by a Project Steering Committee and new features continue to be added.

译文：

PostGIS 向 PostgreSQL 对象关系数据库添加了对地理对象的支持。实际上，PostGIS 在空间上启用了 PostgreSQL 服务器，使其可以用作地理信息系统 (GIS) 的后端空间数据库，就像 ESRI 的 SDE 或 Oracle 的 Spatial 扩展一样。 PostGIS 遵循 OpenGIS“SQL 的简单特征规范”，并已被认证为符合“类型和函数”配置文件。

PostGIS 的开发由 Refractions Research 作为开源空间数据库技术中的一个项目启动。 PostGIS 是在 GNU 通用公共许可证下发布的。 PostGIS 继续由项目指导委员会领导的一组贡献者开发，并继续添加新功能。

友情提示：本文篇幅较长，共分为5篇文章，几乎涵盖了PostGIS的所有内容，PostGIS作为PostgreSQL的重要插件，是目前国内外GIS行业的首选数据库的主要推动力。如果你感兴趣的话，可以先收藏，再慢慢学习~

DownLoad

在下面的网址中，你可以选择下载适合你的windows版本，如果你是用于生产环境，建议尽量不要选择最近的release版本。在下载安装PostGIS插件之前，你需要先具有PostgreSQL的运行环境。

在下面的网址中，我们在自己使用的PostGreSQL版本里面，选择PostGIS插件，因为这些是被PostGIS官方团队所验证和支持的。

http://download.osgeo.org/postgis/windows/

在这个网址，你可以选择适合你的其他版本安装包，也包括免安装的压缩包形式文件。

http://postgis.net/install/

安装

下载完成之后，进行安装；

PostGIS的安装很简单，一路下一步，但是需要注意的是，PostGIS插件需要和PostgreSQL安装在同一个目录(也就是PostGIS需要选择PostGreSQL的根目录进行安装)。

安装过程当中的空间数据库可以创建也可以不创建，这个根据自己的情况来看，如果你要创建空间数据库的话，就需要连接到当前的PostgreSQL。

准备PostGIS

在开始菜单栏找到PostGreSQL，点击里面的pgAdmin4;
点击左上角的Server，根据提示输入连接信息进行登录；
登录上之后，创建一个数据库实例，并新建数据库；
依次点击：数据库名-Extensions-右击-create-extension,输入：postgis;

使用PostGIS的前置知识

1. 常见的PostGIS支持的GIS对象

这些创建GIS对象的语法要熟悉，实际工作中很容易忘~

• POINT(0 0) //点
• LINESTRING(0 0,1 1,1 2) //线
• 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)) //多点
• 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)) //几何集合

2. SRID——存储空间对象的地理坐标系的编号，其输入输出可通过以下接口进行

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

示例

SELECT (ST_GeomFromText('POINT(-126.4 45.32)', 312), 'A Place');
输出如下：
(0101000020380100009A99999999995FC0295C8FC2F5A84640,"A Place")

3. 支持3D GIS对象的语法

PostGIS EWKB/EWKT增加了对3DM、3DZ、4D坐标的支持和嵌入式SRID信息。

• POINT(0 0 0) -- XYZ
• SRID=32632;POINT(0 0) -- XY with SRID
• POINTM(0 0 0) -- XYM
• POINT(0 0 0 0) -- XYZM
• SRID=4326;MULTIPOINTM(0 0 0,1 2 1) -- XYM with 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 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)) )

3DM、3DZ、4D之间的转换接口：

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

示例

 SELECT 'SRID=4;POINT(0 0)'::geometry;
 输出如下：
 01010000200400000000000000000000000000000000000000

4. 弯曲的几何实体

SQL多媒体应用空间规范扩展了SQL规范的简单功能，定义了一些圆弧插值曲线。 SQL-MM的定义包括3DM、3DZ和4D坐标，但不允许嵌入SRID信息。 Well-Known Text扩展还没有被完全支持。

下面是一些简单的曲线几何图形的例子。

• CIRCULARSTRING(0 0, 1 1, 1 0)
• CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0)
• COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1)) //类似于LINESTRING
• CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3, 3 1, 1 1))//复合曲线
• MULTICURVE((0 0, 5 5),CIRCULARSTRING(4 0, 4 4, 8 4)) //复合曲线

5. 创建一张空间表

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

6. 插入数据

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

7. 建立索引

Creating an index works the same as GEOMETRY. PostGIS will note that the column type is GEOGRAPHY and create an

appropriate sphere-based index instead of the usual planar index used for GEOMETRY.

CREATE INDEX global_points_gix ON global_points USING GIST ( location );

8. 查询与计算

//查询给位置1000公里之内的城镇
SELECT name FROM global_points WHERE ST_DWithin(location, 'SRID=4326;POINT(-110 29)'::
geography, 1000000);

// 计算从西雅图飞往伦敦的距离
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96
64.15)'::geography);
//计算点线之间的距离
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.96 64.15)
'::geometry);

9. spatial_ref_sys表

spatial_ref_sys表是一个包含PostGIS和OGC兼容的数据库表，它列出了3000多个已知的空间参考系系统以及在它们之间进行转换/投影所需的细节。虽然PostGIS的spatial_ref_sys表包含了3000多个比较常用的空间参考系统定义的定义，但它并不包含所有已知的定义，如果你熟悉proj4的结构，你可以定义你自己的投影。

10. SRID

一个整数值，用于唯一识别数据库中的空间参考系统（SRS）。

11. 创建一个空间数据表

//建表
CREATE TABLE ROADS (ID serial, ROAD_NAME text, geom geometry(LINESTRING,4326) );
//添加字段
ALTER TABLE roads ADD COLUMN geom2 geometry(LINESTRINGZ,4326);

12. 创建视图，目的是简化SQL语句

//创建视图
CREATE VIEW public.vwmytablemercator AS
SELECT gid, ST_Transform(geom, 3395) As geom, f_name
FROM public.mytable;
//删除视图
DROP VIEW public.vwmytablemercator;

13. 加载 GIS (Vector) 数据

INSERT INTO roads (id, geom, road_name)
VALUES (1,'SRID=4326;LINESTRING(191232 243118,191108 243242)'::geometry,'Jeff Rd');
INSERT INTO roads (id, geom, road_name)
VALUES (2,'SRID=4326;LINESTRING(189141 244158,189265 244817)','Geordie Rd');
INSERT INTO roads (id, geom, road_name)
VALUES (3,'SRID=4326;LINESTRING(192783 228138,192612 229814)','Paul St');
INSERT INTO roads (id, geom, road_name)
VALUES (4,'SRID=4326;LINESTRING(189412 252431,189631 259122)','Graeme Ave');
INSERT INTO roads (id, geom, road_name)
VALUES (5,'SRID=4326;LINESTRING(190131 224148,190871 228134)','Phil Tce');
INSERT INTO roads (id, geom, road_name)
VALUES (6,'SRID=4326;LINESTRING(198231 263418,198213 268322)','Dave Cres');
COMMIT;

14. 导入数据

14.1 使用psql

psql -d [database] -f roads.sql

14.2 使用shp2pgsql

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

14.3 Unix 命令

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

15. 检索数据

//全查
SELECT id, ST_AsText(geom) AS geom, road_name FROM roads;
//条件查询
SELECT id, road_name
FROM roads
WHERE geom='SRID=312;LINESTRING(191232 243118,191108 243242)'::geometry;
//相交查询
SELECT id, road_name
FROM roads
WHERE ST_Intersects(geom, 'SRID=312;POLYGON((...))');
//使用矩形框来查询框内是否含有目标实体
SELECT ST_AsText(geom) AS geom
FROM roads
WHERE
geom && ST_MakeEnvelope(191232, 243117,191232, 243119,312，4326);

16. pgsql2shp

//语法模板
pgsql2shp [<options>] <database> [<schema>.]<table>
pgsql2shp [<options>] <database> <query>

17. 建立索引

索引使得处理空间数据库大型数据集成为可能。

GiST Indexes

GiST是 "通用搜索树 "的缩写，是一种通用的索引形式。除了GIS索引之外，GiST还被用来加速加快对各种不规则数据结构（整数阵列、光谱数据等）的搜索，这些结构不适合使用普通的B-Tree 索引。

//2D索引
CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );
//n维索引
CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd);
//确保索引在更新或者建立时，依然可以对表进行写操作；
CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING GIST ( [geometryfield] );
//收集表的统计数据报告，以便优化查询SQL
VACUUM ANALYZE [table_name] [(column_name)];

BRIN Indexes

BRIN是 "块范围索引 "的意思，是PostgreSQL 9.5中引入的一种通用的索引形式。BRIN是是一种有损失的索引，它的主要用途是为读和写的性能提供一个折中。它的主要目标是处理非常大的表，其中一些列与它们在表中的物理位置有一些自然的关联。表内的物理位置有一些自然的关联。除了GIS索引之外，BRIN还被用来加快对各种规则或不规则数据结构的搜索速度 (整数、数组等)

//普通2D索引
CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] );
//3D索引
CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield]
brin_geometry_inclusion_ops_3d);
//4D索引
CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield]
brin_geometry_inclusion_ops_4d);
//修改默认的块大小
CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ) WITH (
pages_per_range = [number]);

SP-GiST Indexes

SP-GiST是 "空间分割的通用搜索树 "的缩写，是一种通用的索引形式，支持分割的搜索树，如四叉树、K-D树和弧度树（tries）。这些数据结构的共同特点是，它们重复地将搜索空间划分为不需要同等大小的分区。除了GIS索引之外，SP-GiST还被用来加快对许多种数据的搜索，如电话路由、IP路由、子串搜索等。与GiST索引一样，SP-GiST索引也是有损失的，因为它们存储的是包含空间对象的边界盒。对象。SP-GiST索引可以被看作是GiST索引的替代品。性能测试表明，SP-GiST 索引在有许多重叠对象的情况下特别有用，也就是所谓的 "面条数据"。一旦GIS数据表超过几千行，就可以使用SP-GiST索引来加快数据的空间搜索速度。

//2D索引
CREATE INDEX [indexname] ON [tablename] USING SPGIST ( [geometryfield] );
//3D索引
CREATE INDEX [indexname] ON [tablename] USING SPGIST ([geometryfield]
spgist_geometry_ops_3d);
//确保索引在更新或者建立时，依然可以对表进行写操作；
CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING SPGIST ( [geometryfield] );
//收集表的统计数据，以便优化查询SQL
VACUUM ANALYZE [table_name] [(column_name)];

关于空间索引的具体使用和操作符介绍，请大家参考这篇文章，这里就不具体一一展开了。

学以致用 | PostgreSQL之空间索引

那其实准确的来讲空间索引不是PostGreSQL提供的，而是由PostGIS提供的功能，这也正体现插件扩展的的意义就是存在于无形，却作用无处不在！

18. 复杂查询示例

//距离匹配查询
SELECT the_geom
FROM geom_table
WHERE ST_Distance(the_geom, 'SRID=312;POINT(100000 200000)')
//指定100的缓冲区的范围内查询
SELECT the_geom
FROM geom_table
WHERE ST_DWithin(the_geom, 'SRID=312;POINT(100000 200000)', 100)

19. raster2pgsql

//栅格数据转为sql文件
raster2pgsql raster_options_go_here raster_file someschema.sometable > out.sql
//实操示例，栅格数据入库
raster2pgsql -s 4326 -I -C -M *.tif -F -t 100x100 public.demelevation > elev.sql
psql -d gisdb -f elev.sql
//Unix下栅格数据入库的操作命令
raster2pgsql -s 4326 -I -C -M *.tif -F -t 100x100 public.demelevation | psql -d gisdb
//创建一个全视图、2和4级的概述表，使用复制模式插入
raster2pgsql -I -C -e -Y -F -s 26986 -t 128x128 -l 2,4 bostonaerials2008/*.jpg aerials. 
boston | psql -U postgres -d gisdb -h localhost -p 5432
// get a list of raster types supported:
raster2pgsql -G
//创建一张存储栅格数据的表
CREATE TABLE myrasters(rid serial primary key, rast raster);
//建立索引
CREATE INDEX myrasters_rast_st_convexhull_idx ON myrasters USING gist( ST_ConvexHull( 
rast) );

20. PostGIS Geometry/Geography/Box Data Types

box2d

box2d是一种空间数据类型，用于表示一个几何体或几何体集合的二维围合盒。例如例如，ST_Extent聚合函数返回一个box2d对象。该表示法包含xmin、ymin、xmax、ymax的值。这些是X和Y轴的最小值和最大值。和Y扩展的最小值和最大值。

box3d

box3d是一个postgis空间数据类型，用于表示一个几何体或几何体集合的三维包围盒。几何体的三维包围盒。例如，ST_3DExtent聚合函数返回一个box3d对象。该表示法包含xmin, ymin, zmin, xmax, ymax, zmax的值。这些是X、Y和Z的最小和最大 X、Y和Z轴的极限值。

geometry

geometry是PostGIS的一个基本空间数据类型，用于表示平面（欧几里得）坐标系中的一个特征。对几何体的所有空间操作都使用几何体所在的空间参考系统的单位。

geometry_dump

geometry_dump是一个包含字段的复合数据类型。

geom - 对一个几何体组件的引用

path[] - 一个一维的整数数组，定义了倾倒的几何体到geom组件的导航路径。路径数组从1开始（例如，path[1]是第一个元素。）它被ST_Dump*系列函数用作输出类型，将一个复杂的几何体分解成其组成部分。

geography

geography是一种空间数据类型，用于在大地坐标系中表示一个特征。大地坐标系的模型使用一个椭圆体对地球进行建模。通过考虑到椭圆体模型，对地理学类型的空间操作可以提供更准确的结果。

总结

这篇文章，我们主要对PostGIS 的背景、下载、安装，以及一些常用的前置知识进行了较为系统的介绍。

如果你对PostGIS有兴趣，希望可以帮助到你~

posted @ 2021-11-26 12:58 WongBynn 阅读(369) 评论(0) 编辑收藏举报

刷新页面返回顶部

WongBynn