利用 PostgreSQL/PostGIS 扩展模块对空间数据进行管理，通过触发器、函数等实现空间数据之间的空间关系约

要求

（1）掌握关系数据库常规触发器及函数的编
（2）理解空间 ER 中几何对象之间关
（3）掌握用 PostGIS 的触发器、函数等功能实现空间关系约束；
任务 1：关系数据库触发器
（1）以实验 1 创建“学生选课”数据库为例，通过触发器实现Student 表上的插入操作的结果提示，插入成功后告知“触发器名称、
触发器类型、触发器级别、触发操作”等信息（通过打印操作实现）。
（2）建立 Student 表操作（插入、更新、删除）日志记录，记录操作者、操作时间、操作类型、操作表名、操作前后值等信息至
audit_log
任务 2：空间数据库触发器实现关系约束
以实验 2 美国蓝湖空间数据库为基础，通过触发器或函数实现几何对象之间的空间约束

任务 1：关系数据库触发器

（1）以实验1创建“学生选课”数据库为例，通过触发器实现Student表上的插入操作的结果提示，插入成功后告知“触发器名称、触发器类型、触发器级别、触发操作”等信息（通过打印操作实现）。

创建触发器函数：

以Student表为例创建触发器：

验证触发器：

（2）建立Student表操作（插入、更新、删除）日志记录，记录操作者、操作时间、操作类型、操作表名、操作前后值等信息至audit_log表。

创建日志记录表

创建触发器函数

创建触发器

验证触发器

插入

日志

更新

日志

删除

日志

任务2：空间数据库触发器实现关系约束

1、思考“桥与河流”之间的跨越关系、“桥与路段”之间的链接关系、“湖泊与岛”之间的包含关系、“河流与湖泊”之间流入流出关系该如何保证？

“桥与河流”之间的跨越关系可以通过触发器判断河流与桥的几何属性之间是否为包含关系；

“桥与路段”之间的链接可以通过触发器判断桥与路段的几何属性之间是否为相接关系；

“湖泊与岛”之间的包含关系可以通过触发器判断湖泊与岛的几何属性之间是否为相接关系；

“河流与湖泊”之间流入流出关系可以通过触发器判断河流与湖泊的几何属性之间是否为相接关系；

2、利用触发器实现“建筑物与路段、池塘、湖泊”之间的不位于约束，并验证；

创建建筑物与池塘之间触发器函数

创建事前触发器

非法插入验证

合法插入验证

创建建筑物与湖泊之间触发器函数

创建事前触发器

非法插入验证

合法插入验证

创建建筑物与路段之间触发器函数

创建事前触发器

验证非法插入

验证合法插入

SQL代码

--一个简单的提示触发器--
CREATE OR REPLACE FUNCTION notify_trigger()
RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE 'Hi, I got % invoked for % % % ON %',TG_NAME,TG_LEVEL,TG_WHEN,TG_OP,TG_TABLE_NAME;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_insert_trigger
AFTER INSERT ON Student
FOR EACH ROW
EXECUTE PROCEDURE notify_trigger();

INSERT INTO Student
VALUES ('201215125','张三','男','18','IS');

--1创建日志记录表
CREATE TABLE audit_log(
username text, --who did the change
event_time_utc timestamp, --when the event was recorded
table_name text, --contains schena-qualified table name
operation text, --INSERT, UPDATE，DELETE or TRUNCATE
before_value json,  --the OLD tuple value
after_value json --the NEW tuple value
);

--2 创建触发器函数
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
DECLARE old_row json := NULL;
new_row json := NULL;
BEGIN
IF TG_OP IN('UPDATE','DELETE') THEN
old_row = row_to_json(OLD);
END IF;
IF TG_OP IN('INSERT', 'UPDATE') THEN
new_row = row_to_json(NEW) ;
END IF;
INSERT INTO audit_log VALUES(session_user,current_timestamp,TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME,TG_OP,old_row,new_row);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

--3 创建触发器
CREATE TRIGGER audit_log_trigger
AFTER INSERT OR UPDATE OR DELETE
ON student
FOR EACH ROW
EXECUTE PROCEDURE audit_trigger();

--4验证 更新 插入 删除 操作
--insert into student values('100','hh','0',22,'MS');
INSERT INTO Student
VALUES ('201215126','李四','男','19','IS');

UPDATE Student
SET Sage='19'
WHERE Sno='201215125';

DELETE FROM Student
WHERE Sno='201215126';

--5查看触发器操作结束
--select *
--from audit_log;


SELECT ST_Contains(streams.centerline,bridges.position)
FROM bridges,streams;
SELECT ST_Touches(road_segments.centerline,bridges.position)
FROM road_segments,bridges;
SELECT ST_Touches(islands.footpront,lakes.shore)
FROM island,lakes;

--1建筑物与池塘之间是不位于关系
CREATE OR REPLACE FUNCTION buildings_pond_contains()
RETURNS TRIGGER AS $$
DECLARE pond_cursor CURSOR for 
SELECT shores from ponds; --从池塘 表取出几何属性:
         tempPond geometry;
         stopFlag boolean := false;
BEGIN
IF TG_OP IN('INSERT', 'UPDATE') THEN
 OPEN pond_cursor;
LOOP
FETCH pond_cursor into tempPond ;
IF ST_Contains (tempPond ,New.position) = true THEN --判断建筑物与池塘是的包含关系
stopFlag := true;
CLOSE pond_cursor;
RAISE NOTICE 'The insert or update the geometry type is illegal!';
ELSE
RAISE NOTICE 'the next cursor...';
END IF;
EXIT WHEN stopFlag = true OR NOT FOUND;
END LOOP;
IF stopFlag = true THEN
RAISE NOTICE'% on rows in %.% won’t happen',TG_OP,TG_TABLE_SCHEMA,TG_TABLE_NAME;
RETURN NULL;
ELSE
CLOSE pond_cursor;
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;


--创建事前触发器
CREATE TRIGGER building_Insert_Update_trigger
BEFORE INSERT OR UPDATE
ON buildings
FOR EACH ROW
EXECUTE PROCEDURE buildings_pond_contains();


--插入验证
INSERT INTO buildings VALUES
	( 130                                     ,
		'{123 Main Street}'                   ,
		ST_GeomFromText('Point(23 42)', 32214),
		ST_Geomfromtext('Polygon((24 44,22 42,24 40,24 44))', 32214)
	)
;

INSERT INTO buildings VALUES
	(134                                      ,
		'{123 Main Street}'                   ,
		ST_GeomFromText('Point(64 33)', 32214),
		ST_Geomfromtext('Polygon((66 34,62 34,62 32,66 32,66 34))', 32214)
	)
;



select*
from buildings;

--select*
--from buildings;

delete
from buildings
where fid = 134;

--建筑物与水域之间是不位于关系
CREATE OR REPLACE FUNCTION buildings_lakes_contains()
RETURNS TRIGGER AS $$
DECLARE lakes_cursor CURSOR for 
select "shore" from "waters"; --从水域表取出几何属性:
         tempLakes geometry;
         stopFlag boolean := false;
BEGIN
IF TG_OP IN('INSERT', 'UPDATE') THEN
 OPEN lakes_cursor;
LOOP
FETCH lakes_cursor into templakes;
IF ST_Contains (templakes,New.position) = true THEN 判断建筑物与水域是否包含关系
stopFlag := true;
CLOSE lakes_cursor;
RAISE NOTICE 'The insert or update the geometry type is illegal!';
ELSE
RAISE NOTICE 'the next cursor...';
END IF;
EXIT WHEN stopFlag = true OR NOT FOUND;
END LOOP;
IF stopFlag = true THEN
RAISE NOTICE '% on rows in % .% won’t happen',TG_OP, TG_TABLE_SCHEMA, TG_TABLE_NAME;
RETURN NULL;
ELSE
CLOSE waters_cursor;
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;


--创建事前触发器
CREATE TRIGGER building_Insert_Update_trigger1
BEFORE INSERT OR UPDATE
ON buildings
FOR EACH ROW
EXECUTE PROCEDURE buildings_waters_contains();


--插入验证
INSERT INTO buildings VALUES
	( 120                                     ,
		'{123 Main Street}'                   ,
		ST_GeomFromText('Point(56 14)', 32214),
		ST_Geomfromtext('Polygon((52 18, 66 23, 73 9, 48 6,52 18))', 32214)
	)
;

INSERT INTO buildings VALUES
	( 124                                     ,
		'{123 Main Street}'                   ,
		ST_GeomFromText('Point(64 33)', 32214),
		ST_Geomfromtext('Polygon((66 34,62 34,62 32,66 32,66 34))', 32214)
	)
;



select*
from buildings;



--建筑物与道路之间是不位于关系
CREATE OR REPLACE FUNCTION buildings_road_segments_contains()
RETURNS TRIGGER AS $$
DECLARE road_segments_cursor CURSOR for 
SELECT centerline FROM road_segments; --从道路表取出几何属性:
         temproad_segments geometry;
         stopFlag boolean := false;
BEGIN
IF TG_OP IN('INSERT', 'UPDATE') THEN
 OPEN road_segments_cursor;
LOOP
FETCH road_segments_cursor into temproad_segments ;
IF ST_Contains (temproad_segments ,New.position) = true THEN 判断建筑物与道路是否包含关系
stopFlag := true;
CLOSE road_segments_cursor;
RAISE NOTICE 'The insert or update the geometry type is illegal!';
ELSE
RAISE NOTICE 'the next cursor...';
END IF;
EXIT WHEN stopFlag = true OR NOT FOUND;
END LOOP;
IF stopFlag = true THEN
RAISE NOTICE '% on rows in % .% won’t happen',TG_OP, TG_TABLE_SCHEMA, TG_TABLE_NAME;
RETURN NULL;
ELSE
CLOSE road_segments_cursor;
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;


--创建事前触发器
CREATE TRIGGER building_Insert_Update_trigger2
BEFORE INSERT OR UPDATE
ON buildings
FOR EACH ROW
EXECUTE PROCEDURE buildings_road_segments_contains();


--插入验证
INSERT INTO buildings VALUES
	( 147                                     ,
		'{123 Main Street}'                   ,
		ST_GeomFromText('Point(28 26)', 32214),
		ST_Geomfromtext('Polygon((26 27,30 27,30 25,26 25,26 27))', 32214)
	)
;

INSERT INTO buildings VALUES
	( 148                                     ,
		'{123 Main Street}'                   ,
		ST_GeomFromText('Point(64 33)', 32214),
		ST_Geomfromtext('Polygon((66 34,62 34,62 32,66 32,66 34))', 32214)
	)
;





select*
from buildings;

PostgreSQL

Manchan

GISer, a novice who is learning hard
博客内容遵循署名-非商业性使用-相同方式共享 4.0 国际 ( CC 4.0 BY-SA ) 协议
本文永久链接是： https://blog.manchan.top/post/di-li-kong-jian-shu-ju-ku-si/

Manchan's Blog

まん酱の记事与随笔

地理空间数据库：空间数据约束

要求

任务 1：关系数据库触发器

任务2：空间数据库触发器实现关系约束

SQL代码