From 4c1f9a321cd816b7ce556783ae747b15a7987fc1 Mon Sep 17 00:00:00 2001 From: Bborie Park Date: Fri, 14 Dec 2012 20:27:46 +0000 Subject: [PATCH] regular_blocking constraint removed. regular_blocking column of raster_columns now looks for spatially_unique and coverage_tile constraints. Ticket #2150 git-svn-id: http://svn.osgeo.org/postgis/trunk@10841 b70326c6-7e19-0410-871a-916f4a2858ee --- NEWS | 20 +++-- raster/rt_pg/rtpostgis.sql.in.c | 75 ++++++++----------- raster/rt_pg/rtpostgis_drop.sql.in.c | 2 + raster/test/regress/check_raster_columns.sql | 31 +++++++- .../regress/check_raster_columns_expected | 8 ++ 5 files changed, 81 insertions(+), 55 deletions(-) diff --git a/NEWS b/NEWS index 9eaa65818..0c366e5d9 100644 --- a/NEWS +++ b/NEWS @@ -3,29 +3,33 @@ PostGIS 2.1.0 * Important / Breaking Changes * + - #1653, Removed srid parameter from ST_Resample(raster) and variants + with reference raster no longer apply reference raster's SRID. + - #2026, ST_Union(raster) now unions all bands of all rasters + - #2150, regular_blocking is no longer a constraint. column of same name + in raster_columns now checks for existance of spatially_unique + and coverage_tile constraints - ST_Intersects(raster, geometry) behaves in the same manner as ST_Intersects(geometry, raster). - - #2026, ST_Union(raster) now unions all bands of all rasters - point variant of ST_SetValue(raster) previously did not check SRID of input geometry and raster. - ST_Hillshade parameters azimuth and altitude are now in degrees instead of radians. - ST_Slope and ST_Aspect return pixel values in degrees instead of radians. - - #1653, Removed srid parameter from ST_Resample(raster) and variants - with reference raster no longer apply reference raster's SRID. * Deprecated signatures + - #2104, ST_World2RasterCoord, ST_World2RasterCoordX and + ST_World2RasterCoordY renamed to ST_WorldToRasterCoord, + ST_WorldToRasterCoordX and ST_WorldToRasterCoordY. + ST_Raster2WorldCoord, ST_Raster2WorldCoordX and + ST_Raster2WorldCoordY renamed to ST_RasterToWorldCoord, + ST_RasterToWorldCoordX and ST_RasterToWorldCoordY - ST_Estimated_Extent renamed to ST_EstimatedExtent - ST_MapAlgebraFctNgb and 1 and 2 raster variants of ST_MapAlgebraFct. Use ST_MapAlgebra instead - 1 and 2 raster variants of ST_MapAlgebraExpr. Use expression variants of ST_MapAlgebra instead - - #2104, ST_World2RasterCoord, ST_World2RasterCoordX and - ST_World2RasterCoordY renamed to ST_WorldToRasterCoord, - ST_WorldToRasterCoordX and ST_WorldToRasterCoordY. ST_Raster2WorldCoord, - ST_Raster2WorldCoordX and ST_Raster2WorldCoordY renamed to - ST_RasterToWorldCoord, ST_RasterToWorldCoordX and ST_RasterToWorldCoordY * New Features * diff --git a/raster/rt_pg/rtpostgis.sql.in.c b/raster/rt_pg/rtpostgis.sql.in.c index 42df2cc34..6c0ea61af 100644 --- a/raster/rt_pg/rtpostgis.sql.in.c +++ b/raster/rt_pg/rtpostgis.sql.in.c @@ -6552,15 +6552,19 @@ CREATE OR REPLACE FUNCTION _raster_constraint_info_spatially_unique(rastschema n RETURNS boolean AS $$ SELECT TRUE - FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s + FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s, pg_index idx, pg_operator op 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.contype = 'u'; + AND s.contype = 'x' + AND 0::smallint = ANY (s.conkey) + AND idx.indexrelid = s.conindid + AND pg_get_indexdef(idx.indexrelid, 1, true) LIKE '(' || quote_ident($3) || '::geometry)' + AND s.conexclop[1] = op.oid + AND op.oprname = '='; $$ LANGUAGE sql STABLE STRICT COST 100; @@ -6677,48 +6681,20 @@ CREATE OR REPLACE FUNCTION _raster_constraint_info_regular_blocking(rastschema n RETURNS boolean AS $$ DECLARE - cn text; - sql text; - rtn boolean; + covtile boolean; + spunique boolean; BEGIN - cn := 'enforce_regular_blocking_' || $3; - - sql := 'SELECT TRUE FROM pg_class c, pg_namespace n, pg_constraint s' - || ' WHERE n.nspname = ' || quote_literal($1) - || ' AND c.relname = ' || quote_literal($2) - || ' AND s.connamespace = n.oid AND s.conrelid = c.oid' - || ' AND s.conname = ' || quote_literal(cn); - EXECUTE sql INTO rtn; - RETURN rtn; - END; - $$ LANGUAGE 'plpgsql' STABLE STRICT - COST 100; + -- check existance of constraints + -- coverage tile constraint + covtile := COALESCE(_raster_constraint_info_coverage_tile($1, $2, $3), FALSE); -CREATE OR REPLACE FUNCTION _add_raster_constraint_regular_blocking(rastschema name, rasttable name, rastcolumn name) - RETURNS boolean AS $$ - DECLARE - fqtn text; - cn name; - sql text; - BEGIN - - RAISE INFO 'The regular_blocking constraint is just a flag indicating that the column "%" is regularly blocked. It is up to the end-user to ensure that the column is truely regularly blocked.', quote_ident($3); + -- spatially unique constraint + spunique := COALESCE(_raster_constraint_info_spatially_unique($1, $2, $3), FALSE); - fqtn := ''; - IF length($1) > 0 THEN - fqtn := quote_ident($1) || '.'; - END IF; - fqtn := fqtn || quote_ident($2); - - cn := 'enforce_regular_blocking_' || $3; - - sql := 'ALTER TABLE ' || fqtn - || ' ADD CONSTRAINT ' || quote_ident(cn) - || ' CHECK (TRUE)'; - RETURN _add_raster_constraint(cn, sql); + RETURN (covtile AND spunique); END; - $$ LANGUAGE 'plpgsql' VOLATILE STRICT - COST 100; + $$ LANGUAGE 'plpgsql' STABLE STRICT + COST 100; CREATE OR REPLACE FUNCTION _drop_raster_constraint_regular_blocking(rastschema name, rasttable name, rastcolumn name) RETURNS boolean AS @@ -7114,8 +7090,12 @@ CREATE OR REPLACE FUNCTION AddRasterConstraints ( RAISE NOTICE 'Adding alignment constraint'; rtn := _add_raster_constraint_alignment(schema, $2, $3); WHEN kw IN ('regular_blocking', 'regularblocking') THEN - RAISE NOTICE 'Adding regular blocking constraint'; - rtn := _add_raster_constraint_regular_blocking(schema, $2, $3); + RAISE NOTICE 'Adding coverage tile constraint required for regular blocking'; + rtn := _add_raster_constraint_coverage_tile(schema, $2, $3); + IF rtn IS NOT FALSE THEN + RAISE NOTICE 'Adding spatially unique constraint required for regular blocking'; + rtn := _add_raster_constraint_spatially_unique(schema, $2, $3); + END IF; WHEN kw IN ('num_bands', 'numbands') THEN RAISE NOTICE 'Adding number of bands constraint'; rtn := _add_raster_constraint_num_bands(schema, $2, $3); @@ -7354,8 +7334,15 @@ CREATE OR REPLACE FUNCTION DropRasterConstraints ( RAISE NOTICE 'Dropping alignment constraint'; rtn := _drop_raster_constraint_alignment(schema, $2, $3); WHEN kw IN ('regular_blocking', 'regularblocking') THEN - RAISE NOTICE 'Dropping regular blocking constraint'; rtn := _drop_raster_constraint_regular_blocking(schema, $2, $3); + + RAISE NOTICE 'Dropping coverage tile constraint required for regular blocking'; + rtn := _drop_raster_constraint_coverage_tile(schema, $2, $3); + + IF rtn IS NOT FALSE THEN + RAISE NOTICE 'Dropping spatially unique constraint required for regular blocking'; + rtn := _drop_raster_constraint_spatially_unique(schema, $2, $3); + END IF; WHEN kw IN ('num_bands', 'numbands') THEN RAISE NOTICE 'Dropping number of bands constraint'; rtn := _drop_raster_constraint_num_bands(schema, $2, $3); diff --git a/raster/rt_pg/rtpostgis_drop.sql.in.c b/raster/rt_pg/rtpostgis_drop.sql.in.c index e0cae36e1..ac1bfaaac 100644 --- a/raster/rt_pg/rtpostgis_drop.sql.in.c +++ b/raster/rt_pg/rtpostgis_drop.sql.in.c @@ -481,3 +481,5 @@ DROP FUNCTION IF EXISTS st_tile(raster, integer[], integer, integer); DROP FUNCTION IF EXISTS st_tile(raster, integer, integer, integer); DROP FUNCTION IF EXISTS st_tile(raster, integer, integer); +-- function no longer exists +DROP FUNCTION IF EXISTS _add_raster_constraint_regular_blocking(name, name, name); diff --git a/raster/test/regress/check_raster_columns.sql b/raster/test/regress/check_raster_columns.sql index 68e79258d..3184ec3e0 100644 --- a/raster/test/regress/check_raster_columns.sql +++ b/raster/test/regress/check_raster_columns.sql @@ -16,9 +16,11 @@ SET client_min_messages TO warning; ----------------------------------------------------------------------- -- Check table exists -SELECT c.relname FROM pg_class c, pg_views v - WHERE c.relname = v.viewname - AND v.viewname = 'raster_columns'; +SELECT + c.relname +FROM pg_class c, pg_views v +WHERE c.relname = v.viewname + AND v.viewname = 'raster_columns'; ----------------------------------------------------------------------- --- Test AddRasterConstraints and DropRasterConstraints @@ -81,5 +83,28 @@ SELECT r_table_name, r_raster_column, srid, scale_x, scale_y, blocksize_x, block SELECT DropRasterConstraints(current_schema(), 'test_raster_columns', 'rast'::name, 'scale'::text); SELECT r_table_name, r_raster_column, srid, scale_x, scale_y, blocksize_x, blocksize_y, same_alignment, regular_blocking, num_bands, pixel_types, nodata_values, ST_AsEWKT(extent) FROM raster_columns WHERE r_table_name = 'test_raster_columns'; +SELECT DropRasterConstraints(current_schema(), 'test_raster_columns', 'rast'::name); +DELETE FROM test_raster_columns; + +-- regular_blocking + +SELECT make_test_raster(1, 3, 3, 0, 0); +SELECT make_test_raster(2, 3, 3, 3, 0); +SELECT make_test_raster(3, 3, 3, 0, 3); +SELECT make_test_raster(4, 3, 3, 3, 3); + +SELECT AddRasterConstraints(current_schema(), 'test_raster_columns', 'rast'::name); +SELECT AddRasterConstraints(current_schema(), 'test_raster_columns', 'rast'::name, 'regular_blocking'); +SELECT r_table_name, r_raster_column, srid, scale_x, scale_y, blocksize_x, blocksize_y, same_alignment, regular_blocking, num_bands, pixel_types, nodata_values, ST_AsEWKT(extent) FROM raster_columns WHERE r_table_name = 'test_raster_columns'; + +-- spatially unique, this should fail +SELECT make_test_raster(0, 3, 3, 0, 0); + +-- coverage tile, this should fail +SELECT make_test_raster(0, 3, 3, 1, 0); + +SELECT DropRasterConstraints(current_schema(), 'test_raster_columns', 'rast'::name, 'regular_blocking'); +SELECT r_table_name, r_raster_column, srid, scale_x, scale_y, blocksize_x, blocksize_y, same_alignment, regular_blocking, num_bands, pixel_types, nodata_values, ST_AsEWKT(extent) FROM raster_columns WHERE r_table_name = 'test_raster_columns'; + DROP FUNCTION make_test_raster(integer, integer, integer, double precision, double precision, double precision, double precision, double precision, double precision); DROP TABLE IF EXISTS test_raster_columns; diff --git a/raster/test/regress/check_raster_columns_expected b/raster/test/regress/check_raster_columns_expected index 067854ff4..6bc4b0659 100644 --- a/raster/test/regress/check_raster_columns_expected +++ b/raster/test/regress/check_raster_columns_expected @@ -11,3 +11,11 @@ t test_raster_columns|rast|0|1|1|2|2|t|f|1||{0}|MULTIPOLYGON(((0 0,0 -2,-2 -2,-2 0,0 0)),((3 1,3 -1,1 -1,1 0,0 0,0 2,1 2,1 3,2 3,2 4,4 4,4 2,3 2,3 1))) t test_raster_columns|rast|0|||2|2|t|f|1||{0}|MULTIPOLYGON(((0 0,0 -2,-2 -2,-2 0,0 0)),((3 1,3 -1,1 -1,1 0,0 0,0 2,1 2,1 3,2 3,2 4,4 4,4 2,3 2,3 1))) +t +t +t +test_raster_columns|rast|0|1|1|3|3|t|t|1|{8BUI}|{0}|POLYGON((3 0,0 0,0 3,0 6,3 6,6 6,6 3,6 0,3 0)) +ERROR: conflicting key value violates exclusion constraint "enforce_spatially_unique_rast" +ERROR: new row for relation "test_raster_columns" violates check constraint "enforce_coverage_tile_rast" +t +test_raster_columns|rast|0|1|1|3|3|t|f|1|{8BUI}|{0}|POLYGON((3 0,0 0,0 3,0 6,3 6,6 6,6 3,6 0,3 0)) -- 2.40.0