From a8fa877d41572bfd76b76a9e64488b88df36e6e2 Mon Sep 17 00:00:00 2001
From: David Zwarg <dzwarg@azavea.com>
Date: Wed, 8 Feb 2012 17:23:14 +0000
Subject: [PATCH] Added ST_StdDev4ma raster neighborhood processing function.
 #1318

git-svn-id: http://svn.osgeo.org/postgis/trunk@9101 b70326c6-7e19-0410-871a-916f4a2858ee
---
 raster/rt_pg/rtpostgis.sql.in.c               | 18 +++++------
 .../regress/rt_mapalgebrafctngb_userfunc.sql  | 32 +++++++++++++++++++
 .../rt_mapalgebrafctngb_userfunc_expected     |  2 ++
 3 files changed, 42 insertions(+), 10 deletions(-)

diff --git a/raster/rt_pg/rtpostgis.sql.in.c b/raster/rt_pg/rtpostgis.sql.in.c
index f166d87ad..a69b46221 100644
--- a/raster/rt_pg/rtpostgis.sql.in.c
+++ b/raster/rt_pg/rtpostgis.sql.in.c
@@ -2076,16 +2076,14 @@ CREATE OR REPLACE FUNCTION st_hillshade(rast raster, band integer, pixeltype tex
     LANGUAGE 'SQL' STABLE;
 
 CREATE OR REPLACE FUNCTION st_distinct4ma(matrix float[][], nodatamode TEXT, VARIADIC args TEXT[])
-    RETURNS float
-    AS
-    $$
-    DECLARE
-        _count integer;
-    BEGIN
-        RETURN (SELECT COUNT(DISTINCT unnest) FROM unnest(matrix));
-    END;
-    $$
-    LANGUAGE 'plpgsql' IMMUTABLE;
+    RETURNS float AS
+    $$ SELECT COUNT(DISTINCT unnest)::float FROM unnest($1) $$
+    LANGUAGE 'sql' IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION st_stddev4ma(matrix float[][], nodatamode TEXT, VARIADIC args TEXT[])
+    RETURNS float AS
+    $$ SELECT stddev(unnest) FROM unnest($1) $$
+    LANGUAGE 'sql' IMMUTABLE;
 
 
 -----------------------------------------------------------------------
diff --git a/raster/test/regress/rt_mapalgebrafctngb_userfunc.sql b/raster/test/regress/rt_mapalgebrafctngb_userfunc.sql
index 9a01380d9..da0c5ffe2 100644
--- a/raster/test/regress/rt_mapalgebrafctngb_userfunc.sql
+++ b/raster/test/regress/rt_mapalgebrafctngb_userfunc.sql
@@ -237,3 +237,35 @@ SELECT
       ST_TestRasterNgb(3, 3, 1), 1, 1, 5
     ), 3, 3, 7
   ) AS rast;
+
+-- test st_stddev4ma, all one value
+SELECT
+  ST_Value(rast, 2, 2) = 10,
+  ST_Value(
+    ST_MapAlgebraFctNgb(rast, 1, NULL, 1, 1, 'st_stddev4ma(float[][], text, text[])'::regprocedure, '1', NULL), 2, 2
+  ) = 0
+  FROM ST_TestRasterNgb(3, 3, 10) AS rast;
+
+-- test st_stddev4ma, all different values
+SELECT
+  ST_Value(rast, 2, 2) = 5,
+  round(ST_Value(
+    ST_MapAlgebraFctNgb(rast, 1, NULL, 1, 1, 'st_stddev4ma(float[][], text, text[])'::regprocedure, '1', NULL), 2, 2
+  ) * 1000000) = 2738613
+  FROM ST_SetValue(
+    ST_SetValue(
+      ST_SetValue(
+        ST_SetValue(
+          ST_SetValue(
+            ST_SetValue(
+              ST_SetValue(
+                ST_SetValue(
+                  ST_TestRasterNgb(3, 3, 1), 2, 1, 2
+                ), 3, 1, 3
+              ), 1, 2, 4
+            ), 2, 2, 5
+          ), 3, 2, 6
+        ), 1, 3, 7
+      ), 2, 3, 8
+    ), 3, 3, 9
+  ) AS rast;
diff --git a/raster/test/regress/rt_mapalgebrafctngb_userfunc_expected b/raster/test/regress/rt_mapalgebrafctngb_userfunc_expected
index 9510b3118..43d833328 100644
--- a/raster/test/regress/rt_mapalgebrafctngb_userfunc_expected
+++ b/raster/test/regress/rt_mapalgebrafctngb_userfunc_expected
@@ -31,3 +31,5 @@ t
 t
 t|t
 t|t
+t|t
+t|t
-- 
2.40.0