From e887b76b2e3e0ff7d55b61a23b1aada97a379d1f Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Sun, 11 Dec 2011 15:45:07 +0000 Subject: [PATCH] Revisions to ST_Union for raster: get rid of dependency on rastexpr (and get rid of type), put in regress tests, get rid of many ST_Union permutations and limit to st_union(rast), st_union(rast,p_expression), st_union(rast,band_num), st_union(rast,band_num,p_expression). Note for drop I'm not dropping those other versions in case people have installed them from scripts folder or are currently using them. just dropping the ones I'm replacing. git-svn-id: http://svn.osgeo.org/postgis/trunk@8351 b70326c6-7e19-0410-871a-916f4a2858ee --- raster/rt_pg/rtpostgis.sql.in.c | 243 +++++++------------------- raster/rt_pg/rtpostgis_drop.sql.in.c | 8 + raster/test/regress/Makefile.in | 1 + raster/test/regress/rt_union.sql | 16 ++ raster/test/regress/rt_union_expected | 12 ++ 5 files changed, 101 insertions(+), 179 deletions(-) create mode 100644 raster/test/regress/rt_union.sql create mode 100644 raster/test/regress/rt_union_expected diff --git a/raster/rt_pg/rtpostgis.sql.in.c b/raster/rt_pg/rtpostgis.sql.in.c index 07624ebf3..10098ea37 100644 --- a/raster/rt_pg/rtpostgis.sql.in.c +++ b/raster/rt_pg/rtpostgis.sql.in.c @@ -3231,25 +3231,8 @@ CREATE OR REPLACE FUNCTION st_intersection(rast raster, band integer, geom geome ----------------------------------------------------------------------- -- st_union aggregate ----------------------------------------------------------------------- -CREATE TYPE rastexpr AS ( - rast raster, - f_expression text, - f_nodata1expr text, - f_nodata2expr text, - f_nodatanodataval double precision -); - -- Main state function -CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 raster, - rast2 raster, - p_expression text, - p_nodata1expr text, - p_nodata2expr text, - p_nodatanodataval double precision, - t_expression text, - t_nodata1expr text, - t_nodata2expr text, - t_nodatanodataval double precision) +CREATE OR REPLACE FUNCTION _ST_MapAlgebra4UnionState(rast1 raster, rast2 raster, p_expression text, p_nodata1expr text, p_nodata2expr text, p_nodatanodataval double precision,t_expression text,t_nodata1expr text, t_nodata2expr text,t_nodatanodataval double precision) RETURNS raster AS $$ DECLARE @@ -3259,7 +3242,10 @@ CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 raster, -- With the new ST_MapAlgebraExpr we must split the main expression in three expressions: expression, nodata1expr, nodata2expr and a nodatanodataval -- ST_MapAlgebraExpr(rast1 raster, band1 integer, rast2 raster, band2 integer, expression text, pixeltype text, extentexpr text, nodata1expr text, nodata2expr text, nodatanodatadaval double precision) -- We must make sure that when NULL is passed as the first raster to ST_MapAlgebraExpr, ST_MapAlgebraExpr resolve the nodata1expr + -- Note: rast2 is always a single band raster since it is the accumulated raster thus far + -- There we always set that to band 1 regardless of what band num is requested IF upper(p_expression) = 'LAST' THEN + --RAISE NOTICE 'last asked for '; RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'rast2'::text, NULL::text, 'UNION'::text, 'rast2'::text, 'rast1'::text, NULL::double precision); ELSIF upper(p_expression) = 'FIRST' THEN RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'rast1'::text, NULL::text, 'UNION'::text, 'rast2'::text, 'rast1'::text, NULL::double precision); @@ -3272,12 +3258,17 @@ CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 raster, ELSIF upper(p_expression) = 'SUM' THEN RETURN ST_MapAlgebraExpr(rast1, 1, rast2, 1, 'rast1 + rast2'::text, NULL::text, 'UNION'::text, 'rast2'::text, 'rast1'::text, NULL::double precision); ELSIF upper(p_expression) = 'RANGE' THEN + -- have no idea what this is t_raster = ST_MapAlgebraExpr(rast1, 2, rast2, 1, 'LEAST(rast1, rast2)'::text, NULL::text, 'UNION'::text, 'rast2'::text, 'rast1'::text, NULL::double precision); - p_raster := MapAlgebra4UnionState(rast1, rast2, 'MAX'::text, NULL::text, NULL::text, NULL::double precision, NULL::text, NULL::text, NULL::text, NULL::double precision); + p_raster := _ST_MapAlgebra4UnionState(rast1, rast2, 'MAX'::text, NULL::text, NULL::text, NULL::double precision, NULL::text, NULL::text, NULL::text, NULL::double precision); RETURN ST_AddBand(p_raster, t_raster, 1, 2); ELSIF upper(p_expression) = 'MEAN' THEN + -- looks like t_raster is used to keep track of accumulated count + -- and p_raster is there to keep track of accumulated sum and final state function + -- would then do a final map to divide them. This one is currently broken because + -- have not reworked it so it can do without a final function t_raster = ST_MapAlgebraExpr(rast1, 2, rast2, 1, 'rast1 + 1'::text, NULL::text, 'UNION'::text, '1'::text, 'rast1'::text, 0::double precision); - p_raster := MapAlgebra4UnionState(rast1, rast2, 'SUM'::text, NULL::text, NULL::text, NULL::double precision, NULL::text, NULL::text, NULL::text, NULL::double precision); + p_raster := _ST_MapAlgebra4UnionState(rast1, rast2, 'SUM'::text, NULL::text, NULL::text, NULL::double precision, NULL::text, NULL::text, NULL::text, NULL::double precision); RETURN ST_AddBand(p_raster, t_raster, 1, 2); ELSE IF t_expression NOTNULL AND t_expression != '' THEN @@ -3291,184 +3282,78 @@ CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 raster, $$ LANGUAGE 'plpgsql'; --- Final function with three expression -CREATE OR REPLACE FUNCTION MapAlgebra4UnionFinal3(rast rastexpr) - RETURNS raster AS - $$ - DECLARE - BEGIN - RETURN ST_MapAlgebraExpr(rast.rast, 1, rast.rast, 2, rast.f_expression, NULL::text, 'UNION'::text, rast.f_nodata1expr, rast.f_nodata2expr, rast.f_nodatanodataval); - END; - $$ - LANGUAGE 'plpgsql'; +-- State function when there is a primary expression, band number and no alternative nodata expressions +CREATE OR REPLACE FUNCTION _ST_MapAlgebra4UnionState(rast1 raster,rast2 raster,bandnum integer, p_expression text) + RETURNS raster + AS $$ + SELECT _ST_MapAlgebra4UnionState($1, ST_Band($2,$3), $4, NULL, NULL, NULL, NULL, NULL, NULL, NULL) + $$ LANGUAGE 'SQL'; +-- State function when there is no expressions but allows specifying band +CREATE OR REPLACE FUNCTION _ST_MapAlgebra4UnionState(rast1 raster,rast2 raster, bandnum integer) + RETURNS raster + AS $$ + SELECT _ST_MapAlgebra4UnionState($1,ST_Band($2,$3), 'LAST', NULL, NULL, NULL, NULL, NULL, NULL, NULL) + $$ LANGUAGE 'SQL'; + +-- State function when there is no expressions and assumes band 1 +CREATE OR REPLACE FUNCTION _ST_MapAlgebra4UnionState(rast1 raster,rast2 raster) + RETURNS raster + AS $$ + SELECT _ST_MapAlgebra4UnionState($1,$2, 'LAST', NULL, NULL, NULL, NULL, NULL, NULL, NULL) + $$ LANGUAGE 'SQL'; + +-- State function when there isan expressions and assumes band 1 +CREATE OR REPLACE FUNCTION _ST_MapAlgebra4UnionState(rast1 raster,rast2 raster, p_expression text) + RETURNS raster + AS $$ + SELECT _ST_MapAlgebra4UnionState($1,$2, $3, NULL, NULL, NULL, NULL, NULL, NULL, NULL) + $$ LANGUAGE 'SQL'; + -- Final function with only the primary expression -CREATE OR REPLACE FUNCTION MapAlgebra4UnionFinal1(rast rastexpr) +CREATE OR REPLACE FUNCTION _ST_MapAlgebra4UnionFinal1(rast raster) RETURNS raster AS $$ DECLARE BEGIN - IF upper(rast.f_expression) = 'RANGE' THEN - RETURN ST_MapAlgebraExpr(rast.rast, 1, rast.rast, 2, 'rast1 - rast2'::text, NULL::text, 'UNION'::text, NULL::text, NULL::text, NULL::double precision); - ELSEIF upper(rast.f_expression) = 'MEAN' THEN - RETURN ST_MapAlgebraExpr(rast.rast, 1, rast.rast, 2, 'CASE WHEN rast2 > 0 THEN rast1 / rast2::float8 ELSE NULL END'::text, NULL::text, 'UNION'::text, NULL::text, NULL::text, NULL::double precision); + -- NOTE: I have to sacrifice RANGE. Sorry RANGE. Any 2 banded raster is going to be treated + -- as a MEAN + IF ST_NumBands(rast) = 2 THEN + RETURN ST_MapAlgebraExpr(rast, 1, rast, 2, 'CASE WHEN rast2 > 0 THEN rast1 / rast2::float8 ELSE NULL END'::text, NULL::text, 'UNION'::text, NULL::text, NULL::text, NULL::double precision); ELSE - RETURN rast.rast; + RETURN rast; END IF; END; $$ LANGUAGE 'plpgsql'; - - --- Main state function removing the final expression -CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 rastexpr, - rast2 raster, - p_expression text, - p_nodata1expr text, - p_nodata2expr text, - p_nodatanodataval double precision, - t_expression text, - t_nodata1expr text, - t_nodata2expr text, - t_nodatanodataval double precision, - f_expression text, - f_nodata1expr text, - f_nodata2expr text, - f_nodatanodataval double precision) - RETURNS rastexpr - AS $$ - SELECT (MapAlgebra4UnionState(($1).rast, $2, $3, $4, $5, $6, $7, $8, $9, $10), $11, $12, $13, $14)::rastexpr - $$ LANGUAGE 'SQL'; - --- State function when there is no alternative nodata expressions -CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 rastexpr, - rast2 raster, - p_expression text, - t_expression text, - f_expression text) - RETURNS rastexpr - AS $$ - SELECT (MapAlgebra4UnionState(($1).rast, $2, $3, NULL, NULL, NULL, $4, NULL, NULL, NULL), $5, NULL, NULL, NULL)::rastexpr - $$ LANGUAGE 'SQL'; - --- State function when there is no final expression -CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 rastexpr, - rast2 raster, - p_expression text, - p_nodata1expr text, - p_nodata2expr text, - p_nodatanodataval double precision, - t_expression text, - t_nodata1expr text, - t_nodata2expr text, - t_nodatanodataval double precision) - RETURNS rastexpr - AS $$ - SELECT (MapAlgebra4UnionState(($1).rast, $2, $3, $4, $5, $6, $7, $8, $9, $10), NULL, NULL, NULL, NULL)::rastexpr - $$ LANGUAGE 'SQL'; - --- State function when there is no alternative nodata and final expression -CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 rastexpr, - rast2 raster, - p_expression text, - t_expression text) - RETURNS rastexpr - AS $$ - SELECT (MapAlgebra4UnionState(($1).rast, $2, $3, NULL, NULL, NULL, $4, NULL, NULL, NULL), NULL, NULL, NULL, NULL)::rastexpr - $$ LANGUAGE 'SQL'; - --- State function when there is no temporary and final expressions -CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 rastexpr, - rast2 raster, - p_expression text, - p_nodata1expr text, - p_nodata2expr text, - p_nodatanodataval double precision) - RETURNS rastexpr - AS $$ - SELECT (MapAlgebra4UnionState(($1).rast, $2, $3, $4, $5, $6, NULL, NULL, NULL, NULL), NULL, NULL, NULL, NULL)::rastexpr - $$ LANGUAGE 'SQL'; - --- State function when there is only a primary expression without alternative nodata expressions -CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 rastexpr, - rast2 raster, - p_expression text) - RETURNS rastexpr - AS $$ - SELECT (MapAlgebra4UnionState(($1).rast, $2, $3, NULL, NULL, NULL, NULL, NULL, NULL, NULL), $3, NULL, NULL, NULL)::rastexpr - $$ LANGUAGE 'SQL'; - --- State function when there is no expressions -CREATE OR REPLACE FUNCTION MapAlgebra4UnionState(rast1 rastexpr, - rast2 raster) - RETURNS rastexpr - AS $$ - SELECT (MapAlgebra4UnionState(($1).rast, $2, 'LAST', NULL, NULL, NULL, NULL, NULL, NULL, NULL), NULL, NULL, NULL, NULL)::rastexpr - $$ LANGUAGE 'SQL'; - ------------------------------------------------------------------------ --- ST_Union AGGREGATE --- Variant with all the parameters --- raster - set of raster to union --- text - primary raster expression --- text - primary raster nodata1expr --- text - primary raster nodata2expr --- double precision - primary raster nodatanodataval --- text - temporary raster expression --- text - temporary raster nodata1expr --- text - temporary raster nodata2expr --- double precision - temporary raster nodatanodataval --- text - final raster expression --- text - final raster nodata1expr --- text - final raster nodata2expr --- double precision - final raster nodatanodataval ------------------------------------------------------------------------ -CREATE AGGREGATE ST_Union(raster, text, text, text, double precision, text, text, text, double precision, text, text, text, double precision) ( - SFUNC = MapAlgebra4UnionState, - STYPE = rastexpr, - FINALFUNC = MapAlgebra4UnionFinal3 -); - --- Variant with primary, temporary and final expression without nodata alternative expressions -CREATE AGGREGATE ST_Union(raster, text, text, text) ( - SFUNC = MapAlgebra4UnionState, - STYPE = rastexpr, - FINALFUNC = MapAlgebra4UnionFinal3 -); - --- Variant without final expressions -CREATE AGGREGATE ST_Union(raster, text, text, text, double precision, text, text, text, double precision) ( - SFUNC = MapAlgebra4UnionState, - STYPE = rastexpr, - FINALFUNC = MapAlgebra4UnionFinal1 -); - --- Variant with primary and temporary expression but witout alternative nodata and final expressions -CREATE AGGREGATE ST_Union(raster, text, text) ( - SFUNC = MapAlgebra4UnionState, - STYPE = rastexpr, - FINALFUNC = MapAlgebra4UnionFinal1 + +-- Variant with primary expression defaulting to 'LAST' and working on first band +CREATE AGGREGATE ST_Union(raster) ( + SFUNC = _ST_MapAlgebra4UnionState, + STYPE = raster, + FINALFUNC = _ST_MapAlgebra4UnionFinal1 ); --- Variant with full primary and alternative nodata expressions but without temporary and final expressions -CREATE AGGREGATE ST_Union(raster, text, text, text, double precision) ( - SFUNC = MapAlgebra4UnionState, - STYPE = rastexpr, - FINALFUNC = MapAlgebra4UnionFinal1 +-- Variant with primary expression defaulting to 'LAST' and working on specified band +CREATE AGGREGATE ST_Union(raster, integer) ( + SFUNC = _ST_MapAlgebra4UnionState, + STYPE = raster, + FINALFUNC = _ST_MapAlgebra4UnionFinal1 ); -- Variant with simple primary expressions but without alternative nodata, temporary and final expressions +-- and working on first band +-- supports LAST, MIN,MAX,MEAN,FIRST,SUM CREATE AGGREGATE ST_Union(raster, text) ( - SFUNC = MapAlgebra4UnionState, - STYPE = rastexpr, - FINALFUNC = MapAlgebra4UnionFinal1 + SFUNC = _ST_MapAlgebra4UnionState, + STYPE = raster, + FINALFUNC = _ST_MapAlgebra4UnionFinal1 ); --- Variant with primary expression defaulting to 'LAST' -CREATE AGGREGATE ST_Union(raster) ( - SFUNC = MapAlgebra4UnionState, - STYPE = rastexpr, - FINALFUNC = MapAlgebra4UnionFinal1 +CREATE AGGREGATE ST_Union(raster, integer, text) ( + SFUNC = _ST_MapAlgebra4UnionState, + STYPE = raster, + FINALFUNC = _ST_MapAlgebra4UnionFinal1 ); ------------------------------------------------------------------- diff --git a/raster/rt_pg/rtpostgis_drop.sql.in.c b/raster/rt_pg/rtpostgis_drop.sql.in.c index 2b4f375fd..ec71e3dcc 100644 --- a/raster/rt_pg/rtpostgis_drop.sql.in.c +++ b/raster/rt_pg/rtpostgis_drop.sql.in.c @@ -173,6 +173,14 @@ DROP FUNCTION IF EXISTS st_bandpixeltype(raster); -- signature changed DROP FUNCTION IF EXISTS st_bandpixeltype(raster, integer); + +-- signature changed and some functions dropped -- +-- Note: I am only including the surviving variants +-- since some people may be using the dead ones which are in scripts +-- and we do not have a replace for those +DROP AGGREGATE IF EXISTS ST_Union(raster); +DROP AGGREGATE IF EXISTS ST_Union(raster, integer, text); + -- function no longer exists DROP FUNCTION IF EXISTS st_value(raster, integer, integer, integer); DROP FUNCTION IF EXISTS st_value(raster, integer, integer); diff --git a/raster/test/regress/Makefile.in b/raster/test/regress/Makefile.in index 1fce1e272..4fb095802 100644 --- a/raster/test/regress/Makefile.in +++ b/raster/test/regress/Makefile.in @@ -48,6 +48,7 @@ TEST_FUNC = \ rt_astiff.sql \ rt_asjpeg.sql \ rt_aspng.sql \ + rt_union.sql \ $(NULL) TEST_PROPS = \ diff --git a/raster/test/regress/rt_union.sql b/raster/test/regress/rt_union.sql new file mode 100644 index 000000000..3a63c6a8f --- /dev/null +++ b/raster/test/regress/rt_union.sql @@ -0,0 +1,16 @@ +SELECT '#1 ' as run, ST_AsText((gval).geom), (gval).val::text + FROM (SELECT ST_DumpAsPolygons(ST_Union(rast) ) As gval + FROM ( + SELECT i As rid, ST_AddBand( + ST_MakeEmptyRaster(10, 10, 10*i, 10*i, 2, 2, 0, 0, ST_SRID(ST_Point(0,0) )), + '8BUI'::text, 10*i, 0) As rast + FROM generate_series(0,10) As i ) As foo ) As foofoo ORDER BY (gval).val; + +SELECT '#2 ' As run, ST_AsText((gval).geom), (gval).val::text + FROM (SELECT ST_DumpAsPolygons(ST_Union(rast,'MEAN') ) As gval + FROM ( + SELECT i As rid, ST_AddBand( + ST_MakeEmptyRaster(10, 10, 10*i, 10*i, 2, 2, 0, 0, ST_SRID(ST_Point(0,0) )), + '8BUI'::text, 10*i, 0) As rast + FROM generate_series(0,10) As i ) As foo ) As foofoo + ORDER BY (gval).val LIMIT 2; diff --git a/raster/test/regress/rt_union_expected b/raster/test/regress/rt_union_expected new file mode 100644 index 000000000..fd18af417 --- /dev/null +++ b/raster/test/regress/rt_union_expected @@ -0,0 +1,12 @@ +#1 |POLYGON((10 10,10 30,20 30,20 20,28 20,30 20,30 10,10 10))|10 +#1 |POLYGON((20 20,20 40,30 40,30 30,38 30,40 30,40 20,20 20))|20 +#1 |POLYGON((30 30,30 50,40 50,40 40,48 40,50 40,50 30,30 30))|30 +#1 |POLYGON((40 40,40 60,50 60,50 50,58 50,60 50,60 40,40 40))|40 +#1 |POLYGON((50 50,50 70,60 70,60 60,68 60,70 60,70 50,50 50))|50 +#1 |POLYGON((60 60,60 80,70 80,70 70,78 70,80 70,80 60,60 60))|60 +#1 |POLYGON((70 70,70 90,80 90,80 80,88 80,90 80,90 70,70 70))|70 +#1 |POLYGON((80 80,80 100,90 100,90 90,98 90,100 90,100 80,80 80))|80 +#1 |POLYGON((90 90,90 110,100 110,100 100,108 100,110 100,110 90,90 90))|90 +#1 |POLYGON((100 100,100 120,120 120,120 100,100 100))|100 +#2 |POLYGON((10 10,10 30,20 30,20 20,28 20,30 20,30 10,10 10))|10 +#2 |POLYGON((20 20,20 30,30 30,30 20,20 20))|15 -- 2.40.0