PostGIS系列课程之空间约束（三）

延迟约束和完整的表级数据结构。

线状网络约束

从一个简单的道路网络开始。

CREATE TABLE roads (
  pk bigint PRIMARY KEY,
  name text NOT NULL,
  geom geometry(LineString, 3005) NOT NULL
    CONSTRAINT geom_no_zero_length CHECK (ST_Length(geom) > 0)
    CONSTRAINT geom_no_self_intersection CHECK (ST_IsSimple(geom))
);

复制

检查约束

我们在表定义中添加了几个简单的检查约束：

• 我们希望路段的长度不为零；和，
• 我们希望我们的路段没有自动交叉路口，变得“简单”。

毫无疑问，PostgreSQL可以轻松管理这些约束。

INSERT INTO roads VALUES (0, 'Main',
    'LINESTRING(0 0, 0 0)');
------------------------------
ERROR:  new row for relation "roads"
    violates check constraint "geom_no_zero_length"
DETAIL:  Failing row contains (0, Main,
    0102000020BD0B00000200000000000000000
    000000000000000000000000000...).
-----------------------------
INSERT INTO roads VALUES (1, 'Elm',
    'LINESTRING(0 0, 5 5, 5 3, 0 5)');
-----------------------------
ERROR:  new row for relation "roads"
    violates check constraint "geom_no_self_intersection"
DETAIL:  Failing row contains (1, Elm,
    0102000020BD0B00000400000000000000000
    000000000000000000000000000...).

复制

触发器约束

但是，大多数空间用户想要对线性网络数据实施的真正限制是连通性。特别是在以下情况下，他们会喜欢它：

• 它们的网络是“节点”的，因此每个段都在另一个段的起点或终点处结束，而不是在一个段的中间。
• 他们的网络是“连接的”，因此每个其他网段都可以访问每个网段。

这两个条件不仅需要了解要插入的段，还需要超越 CHECK
约束来触发。

CREATE OR REPLACE FUNCTION noded_road_trigger()
  RETURNS trigger AS
  $$
    DECLARE
      c bigint;
    BEGIN

      -- How many roads does the end point of this
      -- road touch at the ends? Touches is not true
      -- when end points touch middles.
      SELECT Count(*)
      INTO c
      FROM roads
      WHERE ST_Touches(roads.geom, NEW.geom);

      -- Have to touch at least one other segment in
      -- the network.
      IF c < 1 THEN
        RAISE EXCEPTION 'road % is not connected to the network', NEW.pk;
      END IF;

      RETURN NEW;
    END;
  $$
  LANGUAGE 'plpgsql';

-- Run for data additions and changes,
-- and allow deferal to end of transaction.
CREATE CONSTRAINT TRIGGER noded_road
  AFTER INSERT OR UPDATE ON roads DEFERRABLE
  FOR EACH ROW EXECUTE FUNCTION noded_road_trigger();

复制

从一个小的种子网络开始，如下所示：

INSERT INTO roads VALUES (2, 'Grand', 'LINESTRING(0 0, 2 2)');
INSERT INTO roads VALUES (3, 'Grand', 'LINESTRING(2 2, 4 2)');
INSERT INTO roads VALUES (4, 'Oak', 'LINESTRING(0 2, 2 2)');

复制

向网络添加两个新的细分：

INSERT INTO roads VALUES (5, 'Larch', 'LINESTRING(4 0, 6 0)');
-----------------------------
ERROR:  road 5 is not connected to the network
CONTEXT:  PL/pgSQL function noded_road_trigger() line 13 at RAISE
-----------------------------
INSERT INTO roads VALUES (6, 'Oak', 'LINESTRING(2 2, 4 0)');
-----------------------------
INSERT 0 1

复制

这虽然有趣的。一个失败了，另一个成功了，为什么？因为当它们都需要连接的时候, "Oak" 段 需要在插入"Larch" 段之前插入到表中才能判断它们连接有效↓。

延缓约束

为了判断连接性，必须同时将这两个部分都放在适当的位置。也许如果将它们放在事务块中，情况会好一些吗？

-- 删除数据进行新的测试
DELETE FROM roads WHERE pk = 6;
-- Does using a transaction block work?
BEGIN;
    INSERT INTO roads VALUES (5, 'Larch', 'LINESTRING(4 0, 6 0)');
    INSERT INTO roads VALUES (6, 'Oak', 'LINESTRING(2 2, 4 0)');
COMMIT;

复制

不行？！

ERROR:  road 5 is not connected to the network
CONTEXT:  PL/pgSQL function noded_road_trigger() line 13 at RAISE

复制

但是，当创建此约束触发器时，我们将其标记为 DEFERRABLE
。如果我们可以让数据库等到所有记录都加载后再检查约束，那么我们的插入语句就可以了。

延迟约束校验，使用 SET CONSTRAINTS 命令（在事物块开始前）。

BEGIN;
SET CONSTRAINTS noded_road DEFERRED;
INSERT INTO roads VALUES (5, 'Larch', 'LINESTRING(4 0, 6 0)');
INSERT INTO roads VALUES (6, 'Oak', 'LINESTRING(2 2, 4 0)');
COMMIT;

复制

成功！因此，我们可以确保连接线。

强制约束

但是我们还能确保连通性吗？如果每个网段都必须连接到另一个网段，则看起来是这样，但是实际上断开连接很容易：只需在一个延迟的事务中插入一组自连接的网段即可。

BEGIN;
SET CONSTRAINTS noded_road DEFERRED;
-- These aren't connected, do they fail on commit?
INSERT INTO roads VALUES (7, 'Sea', 'LINESTRING(6 1, 6 2)');
INSERT INTO roads VALUES (8, 'Isle', 'LINESTRING(6 2, 5 2)');
-- They do not! :(
COMMIT;
-- Clean up.
DELETE FROM roads WHERE pk IN (7, 8);

复制

哦不，这很容易在表中插入单独的节点段的孤“岛”。我们需要一个新的约束来检查所有段是否全局连接在一起。

表范围约束

我们已经使用``noded_road_trigger()` 保证了节点相连，因此我们只需要确保所有节点段都形成一个图，就可以使用 空间聚类 ，且公差为零。

CREATE OR REPLACE FUNCTION single_network_trigger()
  RETURNS trigger AS
  $$
    DECLARE
      c bigint;
    BEGIN

      -- All the segments are noded (touching)
      -- so clustering with zero distance will find
      -- all connected clusters.
      WITH clusters AS (
        SELECT ST_ClusterDBScan(geom, 0.0, 1) over () AS cluster
        FROM roads
      )
      SELECT Count(DISTINCT cluster)
      INTO c
      FROM clusters;

      -- If there's more than one cluster, we have
      -- separate islands in graph, and that's a no-no.
      IF c > 1 THEN
        RAISE EXCEPTION 'network is not fully connected';
      END IF;

      RETURN NEW;
    END;
  $$
  LANGUAGE 'plpgsql';

CREATE CONSTRAINT TRIGGER single_network
  AFTER INSERT OR UPDATE ON roads DEFERRABLE
  FOR EACH ROW EXECUTE FUNCTION single_network_trigger();

复制

现在，当尝试批量插入节点但未连接的线组时，系统捕获到异常了！

BEGIN;
-- 延迟约束起作用了
SET CONSTRAINTS ALL DEFERRED;
-- These aren't connected, do they fail on commit?
INSERT INTO roads VALUES (7, 'Sea', 'LINESTRING(6 1, 6 2)');
INSERT INTO roads VALUES (8, 'Isle', 'LINESTRING(6 2, 5 2)');
-- They do!
COMMIT;
-----------------------------
ERROR:  network is not fully connected
CONTEXT:  PL/pgSQL function single_network_trigger() line 15 at RAISE

复制

这种方法的缺点很明显：对于较大的表，在每次数据更新时检查群集的成本将对性能造成影响。但是，对于成千上万条记录中的较小表，它应该可以正常工作。

排除约束？

排除约束 由于PostGIS空间算子的实现方式，它对PostGIS并不是很有用。

排除约束严格限于执行使用索引运算符定义的条件，不幸的是，PostGIS运算符都使用“边界框逻辑”而不是“几何逻辑”。例如：

SELECT 'LINESTRING(0 0 10 10)'::geometry && 'POINT(5 4)';

复制

返回 TRUE , 因为它测试线串的边界框和点的边界框是否相交。

排除约束条件“仅允许不与现有几何图形相交的新几何图形”可能有用。但是无法使用PostGIS && 运算符来实现。