Changeset: 5139e6355067 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5139e6355067
Added Files:
sql/test/sciql/Tests/leo_vista.sql
Branch: SciQL-2
Log Message:
Added a test file for calculating the Normalized Difference Vegetation Index
(NDVI) index.
diffs (128 lines):
diff --git a/sql/test/sciql/Tests/leo_vista.sql
b/sql/test/sciql/Tests/leo_vista.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/sciql/Tests/leo_vista.sql
@@ -0,0 +1,123 @@
+SET SCHEMA rs;
+
+-- configuration parameters --
+DECLARE window_size SMALLINT;
+
+DECLARE ndviThreshold DOUBLE;
+SET ndviThreshold = 0; -- what is the correct value ?
+
+
+-- loading data (images) --
+---- Asuming that the following files
+---- /net/lyon.ins.cwi.nl/export/scratch2/kostis/da/L7_B{3,4}.TIF
+---- are stored in /tmp
+CALL attach('/tmp/L7_B3.TIF');
+CALL attach('/tmp/L7_B4.TIF');
+
+CALL import(1);
+CALL import(2);
+---- Now the TIF images have been imported as the following:
+---- b3, b4 as rs.image1 and rs.image2
+
+DECLARE size_x SMALLINT, size_y SMALLINT;
+SET size_x = (SELECT MAX(x) + 1 FROM image1);
+SET size_y = (SELECT MAX(y) + 1 FROM image1);
+
+-- NDVI Calculation
+-- Below, we have four approaches of calculating the NDVI index using arrays
and tables
+-- Currently, the forth one is the fastest one.
+
+
+-- 1) Using two arrays
+CREATE ARRAY ndvi (x SMALLINT DIMENSION[size_x], y SMALLINT DIMENSION[size_y],
value REAL);
+
+INSERT INTO ndvi (
+SELECT b3.x, b3.y,
+ CASE
+ WHEN b4.intensity = 0 and b3.intensity = 0 THEN 0
+ ELSE CAST(b4.intensity - b3.intensity AS REAL) / (b4.intensity +
b3.intensity)
+ END AS value
+FROM image1 AS b3, image2 AS b4
+WHERE b3.x = b4.x AND b3.y = b4.y
+);
+
+
+-- 2) Using a single array
+CREATE ARRAY singlearray (x SMALLINT DIMENSION[size_x], y SMALLINT
DIMENSION[size_y], intensity_b3 REAL, intensity_b4 REAL);
+
+INSERT INTO singlearray (
+SELECT b3.x, b3.y, b3.intensity, b4.intensity
+FROM image1 AS b3, image2 AS b4
+WHERE b3.x = b4.x AND b3.y = b4.y
+);
+
+CREATE ARRAY singlendvi (x SMALLINT DIMENSION[size_x], y SMALLINT
DIMENSION[size_y], value REAL);
+
+INSERT INTO singlendvi (
+SELECT x, y,
+ CASE
+ WHEN intensity_b4 = 0 and intensity_b3 = 0 THEN 0
+ ELSE CAST(intensity_b4 - intensity_b3 AS REAL) / (intensity_b4 +
intensity_b3)
+ END AS value
+FROM singlearray
+);
+
+
+
+-- 3) Using two tables
+CREATE TABLE table1 (x SMALLINT, y SMALLINT, intensity REAL);
+
+INSERT INTO table1(SELECT x,y,intensity FROM image1);
+
+CREATE TABLE table2 (x SMALLINT, y SMALLINT, intensity REAL);
+
+INSERT INTO table2(SELECT x,y,intensity FROM image2);
+
+CREATE TABLE tablendvi (x SMALLINT, y SMALLINT, value REAL);
+
+INSERT INTO tablendvi (
+SELECT b3.x, b3.y,
+ CASE
+ WHEN b4.intensity = 0 and b3.intensity= 0 THEN 0
+ ELSE CAST(b4.intensity - b3.intensity AS REAL) / (b4.intensity +
b3.intensity)
+ END AS value
+FROM table1 AS b3, table2 AS b4
+WHERE b3.x = b4.x AND b3.y = b4.y
+);
+
+
+-- 4) Using a single table
+CREATE TABLE singletable (x SMALLINT, y SMALLINT, intensity_b3 REAL,
intensity_b4 REAL);
+
+INSERT INTO singletable(
+SELECT b3.x, b3.y, b3.intensity, b4.intensity
+FROM table1 AS b3, table2 AS b4
+WHERE b3.x = b4.x AND b3.y = b4.y
+);
+
+CREATE TABLE singletablendvi (x SMALLINT, y SMALLINT, value REAL);
+
+INSERT INTO singletablendvi (
+SELECT x, y,
+ CASE
+ WHEN intensity_b4 = 0 and intensity_b3 = 0 THEN 0
+ ELSE CAST(intensity_b4 - intensity_b3 AS REAL) / (intensity_b4 +
intensity_b3)
+ END AS value
+FROM singletable
+);
+
+
+---- clean up
+DROP ARRAY image1;
+DROP ARRAY image2;
+-- Am I supposed to delete these tuples myself?
+-- Deletion of the corresponding arrays is not propagated to a deletion of
these entries.
+DELETE FROM files WHERE (location LIKE '%/tmp/L7\_B_.TIF%');
+DROP ARRAY ndvi;
+DROP ARRAY singlearray;
+DROP ARRAY singlendvi;
+DROP TABLE table1;
+DROP TABLE table2;
+DROP TABLE tablendvi;
+DROP TABLE singletable;
+DROP TABLE singletablendvi;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
