16.5. Lesson: 几何构造

在本节中，我们将更深入地研究如何在SQL中构造简单的几何图形。在现实中，您可能会使用像QGIS这样的GIS来使用它们的数字化工具创建复杂的几何图形；然而，了解它们是如何表示的，对于编写查询和了解数据库是如何组装的很方便。

The goal of this lesson: 以更好地了解如何在PostgreSQL/PostGIS中直接创建空间实体。

16.5.1. 创建线串

回到我们的 address 数据库，让我们让我们的Streets表与其他表相匹配；即，在GEOMETRY_COLUMNS表中有一个几何约束、一个索引和一个条目。

16.5.2. Try Yourself 适度

  • 修改 streets 表，以使其具有ST_LineString型几何图形列。

  • 不要忘记对几何列表进行随附的更新！

  • 还可以添加约束以防止添加任何不是LINESTRINGS或NULL的几何图形。

  • 在新几何列上创建空间索引

回答

alter table streets add column the_geom geometry;
alter table streets add constraint streets_geom_point_chk check
     (st_geometrytype(the_geom) = 'ST_LineString'::text OR the_geom IS NULL);
insert into geometry_columns values ('','public','streets','the_geom',2,4326,
     'LINESTRING');
create index streets_geo_idx
  on streets
  using gist
  (the_geom);

现在，让我们在Streets表中插入一条线串。在本例中，我们将更新现有的街道记录：

update streets
set the_geom = 'SRID=4326;LINESTRING(20 -33, 21 -34, 24 -33)'
where streets.id=2;

请在QGIS中查看结果。(您可能需要在“Layers”面板中右键单击Streets层，然后选择“Zoom to Layer Expert”。)

现在创建更多的Streets条目--一些在QGIS中，一些在命令行中。

16.5.3. 创建多边形

创建多边形也同样简单。需要记住的一点是，根据定义，多边形至少有四个顶点，其中最后一个和第一个顶点位于同一位置：

insert into cities (name, the_geom)
values ('Tokyo', 'SRID=4326;POLYGON((10 -10, 5 -32, 30 -27, 10 -10))');

备注

多边形需要用双括号括起其坐标列表；这是为了允许您添加具有多个未连接区域的复杂多边形。例如

insert into cities (name, the_geom)
values ('Tokyo Outer Wards',
        'SRID=4326;POLYGON((20 10, 20 20, 35 20, 20 10),
                           (-10 -30, -5 0, -15 -15, -10 -30))'
        );

如果您遵循此步骤，则可以通过将Cities数据集加载到QGIS中、打开其属性表并选择新条目来检查它执行了什么操作。请注意，这两个新多边形的行为方式类似于一个多边形。

16.5.4. 练习：将城市与人联系起来

在本练习中，您应该执行以下操作：

  • 从您的People表中删除所有数据。

  • 向引用Cities表的主键的People添加外键列。

  • 使用QGIS来捕获一些城市。

  • 使用SQL插入一些新的人物记录，确保每个记录都有关联的街道和城市。

您更新的People架构应该如下所示：

\d people

Table "public.people"
   Column   |         Type          |                      Modifiers
 -----------+-----------------------+--------------------------------------------
  id        | integer               | not null
            |                       | default nextval('people_id_seq'::regclass)
  name      | character varying(50) |
  house_no  | integer               | not null
  street_id | integer               | not null
  phone_no  | character varying     |
  the_geom  | geometry              |
  city_id   | integer               | not null
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(the_geom) =
                       'ST_Point'::text OR the_geom IS NULL)
Foreign-key constraints:
  "people_city_id_fkey" FOREIGN KEY (city_id) REFERENCES cities(id)
  "people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

回答

delete from people;
alter table people add column city_id int not null references cities(id);

(在QGIS中捕获城市)

insert into people (name,house_no, street_id, phone_no, city_id, the_geom)
   values ('Faulty Towers',
           34,
           3,
           '072 812 31 28',
           1,
           'SRID=4326;POINT(33 33)');

insert into people (name,house_no, street_id, phone_no, city_id, the_geom)
   values ('IP Knightly',
           32,
           1,
           '071 812 31 28',
           1,F
           'SRID=4326;POINT(32 -34)');

insert into people (name,house_no, street_id, phone_no, city_id, the_geom)
   values ('Rusty Bedsprings',
           39,
           1,
           '071 822 31 28',
           1,
           'SRID=4326;POINT(34 -34)');

如果您收到以下错误消息：

ERROR:  insert or update on table "people" violates foreign key constraint
        "people_city_id_fkey"
DETAIL: Key (city_id)=(1) is not present in table "cities".

这意味着，在尝试为Cities表创建多边形时，您一定已经删除了其中的一些，然后重新开始。只需检查城市表中的条目并使用 id 它是存在的。

16.5.5. 查看我们的架构

到目前为止，我们的模式应该如下所示：

../../../_images/final_schema.png

16.5.6. Try Yourself 硬的

通过计算该城市所有地址的最小凸包并计算该区域周围的缓冲区来创建城市边界。

16.5.7. 访问子对象

使用SFS-Model函数，您可以使用多种选项来访问SFS几何图形的子对象。如果要选择表myPolygonTable中每个多边形几何体的第一个顶点，则必须通过以下方式执行此操作：

  • 将多边形边界转换为线串：

    select st_boundary(geometry) from myPolygonTable;

  • 选择生成的线串的第一个顶点：

    select st_startpoint(myGeometry)
from (
    select st_boundary(geometry) as myGeometry
    from myPolygonTable) as foo;

16.5.8. 数据处理

PostGIS支持所有符合OGC SFS/MM标准的功能。所有这些功能都以 ST_ 。

16.5.9. 剪裁

若要剪裁数据的子部分，可以使用 ST_INTERSECT() 功能。若要避免空几何图形，请使用：

where not st_isempty(st_intersection(a.the_geom, b.the_geom))
../../../_images/qgis_001.png 
select st_intersection(a.the_geom, b.the_geom), b.*
from clip as a, road_lines as b
where not st_isempty(st_intersection(st_setsrid(a.the_geom,32734),
  b.the_geom));
../../../_images/qgis_002.png

16.5.10. 从其他几何图形构建几何图形

从给定点表中，您需要生成线串。点的顺序由它们的 id 。另一种排序方法可以是时间戳，例如当您使用GPS接收器捕获路点时获得的时间戳。

../../../_images/qgis_006.png

若要从名为‘Points’的新点图层创建线串，可以运行以下命令：

select ST_LineFromMultiPoint(st_collect(the_geom)), 1 as id
from (
  select the_geom
  from points
  order by id
) as foo;

要想在不创建新层的情况下查看它是如何工作的，你也可以在‘People’层上运行这个命令，当然这样做在现实世界中没有什么意义。

../../../_images/qgis_007.png

16.5.11. 几何体清理

有关此主题的更多信息，请参阅 this blog entry 。

16.5.12. 表之间的差异

要检测具有相同结构的两个表之间的差异，可以使用PostgreSQL关键字 EXCEPT ：

select * from table_a
except
select * from table_b;

因此，您将从TABLE_a中获得没有存储在TABLE_b中的所有记录。

16.5.13. 表空间

您可以通过创建表空间来定义Postgres应将其数据存储在磁盘上的哪个位置：

CREATE TABLESPACE homespace LOCATION '/home/pg';

创建数据库时，您可以指定要使用的表空间，例如：：

createdb --tablespace=homespace t4a

16.5.14. In Conclusion

您已经学习了如何使用PostGIS语句创建更复杂的几何图形。请记住，这主要是为了在通过GIS前端使用启用地理功能的数据库时提高您的默示知识。您通常不需要手动输入这些语句，但在使用GIS时，对它们的结构有一个大致的了解将会对您有所帮助，特别是当您遇到其他情况下看起来很隐晦的错误时。