PostgreSQL PostGIS 空间数据约束使用

背景

空间数据有一定的规范，例如SRID的规范。空间类型geometry包罗万象，除了能存储POINT，还能存储多边形，线段等。

这就带来一个有意思的烦恼，当我们业务不够规范时，你可以往GEOMETRY里面存储任意SRID的数据，存储任意的空间对象。

1、SRID错乱，可能导致一些查询，索引问题。

2、本身应该存POINT的，你存了一堆线段进去也可能带来一些不必要的查询麻烦。

3、本身应该是2维数据，存入了3维时，查询可能带来麻烦。

空间数据本身的净化（约束）是一项业务需求。

结合PostgreSQL提供的check约束功能，以及PostGIS提供的一些对象描述函数，可以实现空间约束。

原文

http://spatialdbadvisor.com/postgis_tips_tricks/127/how-to-apply-spatial-constraints-to-postgis-tables

例子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

《会议室预定系统实践(解放开发) - PostgreSQL tsrange(时间范围类型) + 排他约束》

digoal’s 大量PostgreSQL文章入口