PostgreSQL / PostGIS：创建地理要素

PostGIS详细教程可以参考官方文档：https://postgis.net/workshops/zh_Hans/postgis-intro/，并且官方文档提供了练习数据、教程、PPT版本教程。我这里参考QGIS文档中关于PostGIS的教程进行学习。

PostGIS 可以被认为是一组数据库内函数的集合，这些函数扩展了 PostgreSQL 的核心功能，以便它可以存储、检索、查询和操作空间数据。我们可以在psql中使用\df *point*查询与point有关的函数，使用\dT+ *Point*查看点数据类型。

除了PostGIS函数外，PostGIS扩展还EPSG定义的空间参考系（SRS）定义集合。这些在坐标参考系（CRS）转换等操作中使用。这些SRS被定义在spatial_ref_sys表中，可以查看该表的架构：\d spatial_ref_sys；还可以使用SELECT语句来查看特定的EPSG，例如查询常用的WGS 84坐标系。

SELECT * FROM spatial_ref_sys Where srid=4326;

SFS模型

如何在数据库中存储和表示地理要素，可以使用开放地理空间联盟（OGC）标准接口：Simple Feature for SQL (SFS) Model，也就是SFS模型。SFS模型是一种以非拓扑方式在数据库中存储地理空间数据方法，并定义了访问、操作和构造数据的函数，用于在SQL数据库中处理简单的空间特征数据。总的来说，SFS包括两部分内容，第一部分是描述简单要素的通用模型，定义了几何对象的基本类型，如点、线、面等；第二部分是描述第一部分模型在SQL中的实现，定义了空间SQL语句中的操作，如AsText、Intersects等。

除此之外，OGC还定义了其他多种标准接口：WMS（Web Map Service）、WFS（Web Feature Service）、WCS（Web Coverage Service）、WMTS（Web Map Tile Service）等。

（1）添加 geometry 字段

首先，修改完善之前创建的streets表和people表：

# 先完善streets表
UPDATE streets SET name='High street' WHERE id=1;
INSERT INTO streets(name) VALUES ('Main Road');
INSERT INTO streets(name) VALUES ('Low Street');
# 完善people表
insert into people (name, house_no, street_id, phone_no)
          values ('Joe Bloggs',3,2,'072 887 23 45');
insert into people (name,house_no, street_id, phone_no)
          values ('Jane Smith',55,3,'072 837 33 35');
insert into people (name,house_no, street_id, phone_no)
          values ('Roger Jones',33,1,'072 832 31 38');
insert into people (name,house_no, street_id, phone_no)
          values ('Sally Norman',83,1,'072 932 31 32');

接下来，为数据库pglearn添加postgis拓展：CREATE EXTENSION postgis;，然后向 people 表添加一个 point 字段：

ALTER TABLE people ADD COLUMN geom geometry;

（2）根据几何类型添加约束

geometry 字段类型并没有隐式地指定字段的几何类型，因此添加一个约束，指定几何类型只接受point类型或 null 值，这里的约束'POINT'要注意大小写，小写可能导致后续插入数据失败。

ALTER TABLE people
	ADD CONSTRAINT people_geom_point_chk
	CHECK(GeomeTryType(geom)='POINT'::text
	OR geom IS NULL);
# 删除约束
ALTER TABLE 表名
DROP CONSTRAINT 约束名;

（3）处理geometry_columns表

geometry_columns 表是 PostGIS 扩展的一部分，用于存储空间数据的元信息，这个表记录了每个空间表的详细信息，包括表名、列名、坐标系等。首先查看这个表，发现表里面已经具有了people这个表的相关信息，表中的coord_dimension等于2，表示X和Y两个纬度，但坐标系是0。

 select * from geometry_columns;

因此，修改坐标系为WGS84，我在这里使用UpdateGeometrySRID 函数（表在 public 模式中，可以省略了'public'），显示query string argument of EXECUTE is null，但是表中明明有这个字段。因此，我又使用了原生SQL语句修改，然后修改成功了。我认为可能是因为存在多个geometry_columns 文件，所以使用UpdateGeometrySRID 函数查询时没有查询到正确的表。

SELECT UpdateGeometrySRID('people', 'geom', 4326);

ALTER TABLE people
ALTER COLUMN geom TYPE geometry(Point, 4326)
USING ST_SetSRID(geom,4326);

（4）添加几何信息

使用 SQL 语句将几何记录添加到people表中，要注意ST_GeomFromText函数中不要出现多余的空格和符号等。

insert into people (name,house_no, street_id, phone_no, geom)
    values ('Fault Towers', 34, 3, '072 812 31 28',
            ST_GeomFromText('POINT(33 -33)', 4326)
           );

添加点后，可以使用QGIS连接到数据库查看点的位置。

（5）创建线

在 streets 表中插入一个 LINESTRING；创建多边形相似，需要将参数改为 POLYGON。

 sudo -u postgres psql
 \c pglearn
 # 添加列
 ALTER TABLE streets ADD COLUMN geom geometry;
 # 添加约束
 ALTER TABLE streets                         
 ADD CONSTRAINT streets_geom_line_chk
 CHECK(GeomeTryType(geom)='LINESTRING'::text
 OR geom IS NULL);
 # 修改约束的投影
 ALTER TABLE streets
 ALTER COLUMN geom TYPE geometry(LINESTRING, 4326)
 USING ST_SetSRID(geom,4326);
 #添加几何信息
 UPDATE streets
 SET geom = ST_LineFromText('LINESTRING(20 -33,21 -34,24 -33)',4326)
 WHERE streets.id=2;