From d13a3a517587230dc117a5b0b42e9f6571c5dd22 Mon Sep 17 00:00:00 2001
From: Bborie Park <bkpark at ucdavis.edu>
Date: Fri, 14 Dec 2012 20:26:56 +0000
Subject: [PATCH] Addition of coverage_tile constraint for raster.  Ticket
 #2148

git-svn-id: http://svn.osgeo.org/postgis/trunk@10835 b70326c6-7e19-0410-871a-916f4a2858ee
---
 NEWS                            |  2 +
 raster/rt_pg/rtpostgis.sql.in.c | 78 +++++++++++++++++++++++++++++++++
 2 files changed, 80 insertions(+)

diff --git a/NEWS b/NEWS
index 6375a03db..d45a9d936 100644
--- a/NEWS
+++ b/NEWS
@@ -100,6 +100,8 @@ PostGIS 2.1.0
     and ST_SnapToGrid() no longer require an SRID
   - #2141, More verbose output when constraints fail to be added
     to a raster column
+  - #2069, Added parameters to ST_Tile(raster) to control padding of tiles
+  - #2148, Addition of coverage_tile constraint for raster
 
 * Fixes *
 
diff --git a/raster/rt_pg/rtpostgis.sql.in.c b/raster/rt_pg/rtpostgis.sql.in.c
index 12f268c05..396d0a4d6 100644
--- a/raster/rt_pg/rtpostgis.sql.in.c
+++ b/raster/rt_pg/rtpostgis.sql.in.c
@@ -6581,6 +6581,84 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_spatially_unique(rastschema n
 	LANGUAGE 'sql' VOLATILE STRICT
 	COST 100;
 
+CREATE OR REPLACE FUNCTION _raster_constraint_info_coverage_tile(rastschema name, rasttable name, rastcolumn name)
+	RETURNS boolean AS $$
+	SELECT
+		TRUE
+	FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+	WHERE n.nspname = $1
+		AND c.relname = $2
+		AND a.attname = $3
+		AND a.attrelid = c.oid
+		AND s.connamespace = n.oid
+		AND s.conrelid = c.oid
+		AND a.attnum = ANY (s.conkey)
+		AND s.consrc LIKE '%st_iscoveragetile(%';
+	$$ LANGUAGE sql STABLE STRICT
+  COST 100;
+
+CREATE OR REPLACE FUNCTION _add_raster_constraint_coverage_tile(rastschema name, rasttable name, rastcolumn name)
+	RETURNS boolean AS $$
+	DECLARE
+		fqtn text;
+		cn name;
+		sql text;
+
+		_scalex double precision;
+		_scaley double precision;
+		_skewx double precision;
+		_skewy double precision;
+		_tilewidth integer;
+		_tileheight integer;
+		_alignment boolean;
+
+		_covextent geometry;
+		_covrast raster;
+	BEGIN
+		fqtn := '';
+		IF length($1) > 0 THEN
+			fqtn := quote_ident($1) || '.';
+		END IF;
+		fqtn := fqtn || quote_ident($2);
+
+		cn := 'enforce_coverage_tile_' || $3;
+
+		-- metadata
+		BEGIN
+			sql := 'WITH foo AS (SELECT ST_Metadata(' || quote_ident($3) || ') AS meta, ST_ConvexHull(' || quote_ident($3) || ') AS hull FROM ' || fqtn || ') SELECT max((meta).scalex), max((meta).scaley), max((meta).skewx), max((meta).skewy), max((meta).width), max((meta).height), ST_Union(hull) FROM foo';
+			EXECUTE sql INTO _scalex, _scaley, _skewx, _skewy, _tilewidth, _tileheight, _covextent;
+		EXCEPTION WHEN OTHERS THEN
+		END;
+
+		-- rasterize extent
+		BEGIN
+			_covrast := ST_AsRaster(_covextent, _scalex, _scaley, '8BUI', 1, 0, NULL, NULL, _skewx, _skewy);
+			IF _covrast IS NULL THEN
+				RAISE NOTICE 'Unable to create coverage raster. Cannot add coverage tile constraint';
+				RETURN FALSE;
+			END IF;
+
+			-- remove band
+			_covrast := ST_MakeEmptyRaster(_covrast);
+		EXCEPTION WHEN OTHERS THEN
+			RAISE NOTICE 'Unable to create coverage raster. Cannot add coverage tile constraint';
+			RETURN FALSE;
+		END;
+
+		sql := 'ALTER TABLE ' || fqtn ||
+			' ADD CONSTRAINT ' || quote_ident(cn) ||
+			' CHECK (st_iscoveragetile(' || quote_ident($3) || ', ''' || _covrast || '''::raster, ' || _tilewidth || ', ' || _tileheight || '))';
+		RETURN _add_raster_constraint(cn, sql);
+	END;
+	$$ LANGUAGE 'plpgsql' VOLATILE STRICT
+	COST 100;
+
+CREATE OR REPLACE FUNCTION _drop_raster_constraint_coverage_tile(rastschema name, rasttable name, rastcolumn name)
+	RETURNS boolean AS
+	$$ SELECT _drop_raster_constraint($1, $2, 'enforce_coverage_tile_' || $3) $$
+	LANGUAGE 'sql' VOLATILE STRICT
+	COST 100;
+
 CREATE OR REPLACE FUNCTION _raster_constraint_info_regular_blocking(rastschema name, rasttable name, rastcolumn name)
 	RETURNS boolean
 	AS $$
-- 
2.40.0