标签
PostgreSQL , PostGIS , 空间数据约束
背景
空间数据有一定的规范，例如SRID的规范。空间类型geometry包罗万象，除了能存储POINT，还能存储多边形，线段等。
这就带来一个有意思的烦恼，当我们业务不够规范时，你可以往GEOMETRY里面存储任意SRID的数据，存储任意的空间对象。
1、SRID错乱，可能导致一些查询，索引问题。
2、本身应该存POINT的，你存了一堆线段进去也可能带来一些不必要的查询麻烦。
3、本身应该是2维数据，存入了3维时，查询可能带来麻烦。
空间数据本身的净化（约束）是一项业务需求。
结合PostgreSQL提供的check约束功能，以及PostGIS提供的一些对象描述函数，可以实现空间约束。
原文
例子1
/* Note that the table now has the following structure.
CREATE TABLE simon.parcel
(
gid serial NOT NULL,
geom geometry,
CONSTRAINT parcel_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geom IS NULL),
CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 28355)
)
*/
-- 3. try and insert a POLYGON with wrong dimensionality
INSERT INTO simon.parcel(gid,geom) VALUES (1,ST_GeomFromEWKT('POLYGON ((100 0 -9,120 0 -9,120 20 -9,100 20 -9,100 0 -9))'));
ERROR: NEW ROW FOR relation "parcel" violates CHECK CONSTRAINT "enforce_dims_geom"
--
-- 4. Try and insert POLYGON with right dimensionality
INSERT INTO simon.parcel(gid,geom) VALUES (1,ST_PolygonFromText('POLYGON ((100 0,120 0,120 20,100 20,100 0))'));
ERROR: NEW ROW FOR relation "parcel" violates CHECK CONSTRAINT "enforce_srid_geom"
--
-- 5. Try and insert geometry with right SRID
INSERT INTO simon.parcel(gid,geom) VALUES (1,ST_PolygonFromText('POLYGON ((100 0,120 0,120 20,100 20,100 0))',28355));
Query returned successfully: 1 ROWS affected, 16 ms execution TIME.
--
-- 6. Insert Another POLYGON
INSERT INTO simon.parcel(gid,geom) VALUES (2,ST_PolygonFromText('POLYGON ((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',28355));
Query returned successfully: 1 ROWS affected, 16 ms execution TIME.
--
-- 7. Now try and insert a MULTIPOLYGON
INSERT INTO simon.parcel(gid,geom) VALUES (3,ST_MultiPolygonFromText('MULTIPOLYGON (((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5)),((50 5,50 7,70 7,70 5,50 5)))',28355));
ERROR: NEW ROW FOR relation "parcel" violates CHECK CONSTRAINT "enforce_geotype_geom"
--
-- How do we fix this if we want both POLYGON and MULTIPOLYGONS in our table?
-- We could do this back at the original call to AddGeometryColumn but here we will show how to do it post-factum.
--
-- 8. Modify the constraint directly
ALTER TABLE simon.parcel DROP CONSTRAINT enforce_geotype_geom;
Query returned successfully WITH no RESULT IN 15 ms.
--
ALTER TABLE simon.parcel
ADD CONSTRAINT enforce_geotype_geom
CHECK ((geometrytype(geom) = ANY (ARRAY['MULTIPOLYGON'::text, 'POLYGON'::text])) OR geom IS NULL);
Query returned successfully WITH no RESULT IN 31 ms.
--
-- 9. Try again
INSERT INTO simon.parcel(gid,geom) VALUES (3,ST_MultiPolygonFromText('MULTIPOLYGON (((0 0,20 0,20 20,0 20,0 0), (10 10,10 11,11 11,11 10,10 10), (5 5,5 7,7 7,7 5,5 5)), ((50 5,50 7,70 7,70 5,50 5)))',28355));
Query returned successfully: 1 ROWS affected, 32 ms execution TIME.
--
SELECT gid, ST_AsText(geom)
FROM simon.parcel;
例子2
CREATE TABLE simon.parcel
(
gid serial NOT NULL,
geom geometry,
centroid geometry,
CONSTRAINT parcel_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_centroid CHECK (st_ndims(centroid) = 2),
CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
CONSTRAINT enforce_geotype_centroid CHECK (geometrytype(centroid) = 'POINT'::text OR centroid IS NULL),
CONSTRAINT enforce_geotype_geom CHECK ((geometrytype(geom) = ANY (ARRAY['MULTIPOLYGON'::text, 'POLYGON'::text])) OR geom IS NULL),
CONSTRAINT enforce_srid_centroid CHECK (st_srid(centroid) = 28355),
CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 28355)
)
WITH (
OIDS=FALSE
);
-- 1 Add centroids to existing polygons
UPDATE simon.parcel SET centroid = ST_Centroid(geom);
Query returned successfully: 3 ROWS affected, 62 ms execution TIME.
--
-- 2. Now, apply centroid constraint
ALTER TABLE simon.parcel
ADD CONSTRAINT centroid_in_parcel
CHECK (centroid IS NOT NULL AND ST_Covers(geom,centroid) = TRUE);
ERROR: CHECK CONSTRAINT "centroid_in_parcel" IS violated BY SOME ROW
--
-- 3. Find which rows fail
SELECT gid, ST_Covers(geom,centroid)
FROM simon.parcel;
排他约束
排他约束也是空间约束之一，比如要求一个表里面，不允许空间对象相交。
https://www.postgresql.org/docs/10/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
