Changeset: 91ef81c41215 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=91ef81c41215
Modified Files:
sql/test/sciql/Tests/teleios_noa_bsm.sql
Branch: sciql
Log Message:
partial SciQL queries for the connect-nearby-fires filter, need more thinking
on how to add the bridge between two groups
diffs (152 lines):
diff --git a/sql/test/sciql/Tests/teleios_noa_bsm.sql
b/sql/test/sciql/Tests/teleios_noa_bsm.sql
--- a/sql/test/sciql/Tests/teleios_noa_bsm.sql
+++ b/sql/test/sciql/Tests/teleios_noa_bsm.sql
@@ -9,17 +9,17 @@ SET size_y = $img_hei;
CREATE ARRAY landsat5_img1 (x INT DIMENSION[size_x], y INT DIMENSION[size_y],
b3 INT, b4 INT, b7 INT);
CREATE ARRAY landsat5_img2 (x INT DIMENSION[size_x], y INT DIMENSION[size_y],
b3 INT, b4 INT, b7 INT);
INSERT INTO landsat5_img1 (
- SELECT img1.x, img1.y, img1.intensity AS b3,
- img2.intensity AS b4, img3.intensity AS b7
- FROM rs.image1 AS img1, rs.image2 AS img2, rs.image3 AS img3
- WHERE img1.x = img2.x AND img1.x = img3.x
- AND img1.y = img2.y AND img1.y = img3.y);
+ SELECT img1.x, img1.y, img1.intensity AS b3,
+ img2.intensity AS b4, img3.intensity AS b7
+ FROM rs.image1 AS img1, rs.image2 AS img2, rs.image3 AS img3
+ WHERE img1.x = img2.x AND img1.x = img3.x
+ AND img1.y = img2.y AND img1.y = img3.y);
INSERT INTO landsat5_img2 (
- SELECT img4.x, img4.y, img4.intensity AS b3,
- img5.intensity AS b4, img6.intensity AS b7
- FROM rs.image4 AS img4, rs.image5 AS img5, rs.image6 AS img6
- WHERE img4.x = img5.x AND img4.x = img6.x
- AND img4.y = img5.y AND img4.y = img6.y);
+ SELECT img4.x, img4.y, img4.intensity AS b3,
+ img5.intensity AS b4, img6.intensity AS b7
+ FROM rs.image4 AS img4, rs.image5 AS img5, rs.image6 AS img6
+ WHERE img4.x = img5.x AND img4.x = img6.x
+ AND img4.y = img5.y AND img4.y = img6.y);
-- BSM classification (landsatFirePredicate()) using one image
CREATE ARRAY fire1 (x INT DIMENSION[size_x], y INT DIMENSION[size_y], f INT
DEFAULT 0);
@@ -93,14 +93,14 @@ BEGIN
FROM (
SELECT MAX(gid) - MIN(gid) AS res
FROM fire_eliminated
- GROUP BY fe[x][y], fe[x+1][y], fe[x][y+1], fe[x-1][y],
fe[x][y-1]
+ GROUP BY fe[x][y], fe[x+1][y], fe[x][y+1], fe[x-1][y], fe[x][y-1]
) AS updates;
END WHILE;
---- Eliminate any groups that have few members (<10 pixels)
CREATE TABLE to_eliminate (gid INT) AS
SELECT gid FROM fire_eliminated
- WHERE gid > 0
+ WHERE gid > 0
GROUP BY gid HAVING COUNT(gid) < 10;
UPDATE fire_eliminated SET gid = NULL WHERE gid IN (SELECT * FROM
to_eliminate);
@@ -134,14 +134,100 @@ BEGIN
---- Eliminate any groups that have few members (<10 pixels)
CREATE TABLE to_eliminate (gid INT) AS
SELECT gid FROM fire_eliminated
- WHERE gid > 0
+ WHERE gid > 0
GROUP BY gid HAVING COUNT(gid) < 10;
UPDATE fire_eliminated SET gid = NULL WHERE gid IN (SELECT * FROM
to_eliminate);
END;
-- BSM connect nearby fires filter
----- Union fires which are less that 3 pixels apart (using 8- CONNECTED)
+---- Union fires which are less that 3 pixels apart (using 8-CONNECTED)
---- Add fire bridge between them
--- TODO
+---- This impl. is most probably slower,
+---- but it only(?) uses SciQL features implemented
+CREATE PROCEDURE connect_nearby_fires1()
+BEGIN
+ DECLARE merge_more INT, update_more INT;
+ SET merge_more = 1;
+ SET update_more = 1;
+
+ -- Denote pairs of nearby fire groups that
+ -- should be merged from gid_from into gid_into
+ CREATE TABLE nearby_groups (gid_from INT, gid_into INT);
+
+ -- This process needs to be repeated, because it can happen that one pixel is
+ -- the _only_ connecting point for >2 fire groups
+ WHILE merge_more > 0
+ -- Find pairs of nearby fire groups by checking all 8 neighbours of each
+ -- non-fire pixel to see if the neighbours denoting more than one fire
+ -- groups.
+ INSERT INTO nearby_groups (
+ SELECT DISTINCT MIN(gid), MAX(gid) FROM fire_eliminated AS fe
+ GROUP BY fe[x-2:x+3][y-2:y+3] -- 5x5 window => max distance within the
window = 3
+ HAVING gid IS NULL -- this is a non-fire pixel
+ AND MIN(gid) IS NOT NULL -- this group has at least one fire pixel
+ AND MIN(gid) <> MAX(gid) -- this group has >1 fire groups
+ );
+
+ SELECT COUNT(*) INTO merge_more FROM nearby_groups;
+
+ -- Merge fire groups by overwriting gid of the group with a smaller ID
with
+ -- the gid of the other group
+ -- This process needs to be repeated to deal with recursive merging,
e.g.,
+ -- (gid_from, gid_into) = {(2, 4), (4, 5)}
+ WHILE update_more > 0
+ INSERT INTO fire_eliminated (
+ SELECT [x], [y], gid_into FROM fire_eliminated, nearby_groups
+ WHERE gid = gid_from);
+
+ SELECT COUNT(gid) INTO update_more FROM fire_eliminated, nearby_groups
+ WHERE gid = gid_from;
+ END WHILE;
+
+ DELETE FROM nearby_groups WHERE gid_from IS NOT NULL;
+ END WHILE;
+
+ -- TODO: add bridge!
+END;
+
+---- This impl. is most probably faster,
+---- but it uses unimplemented SciQL features
+CREATE PROCEDURE connect_nearby_fires2()
+BEGIN
+ -- Denote neighbours in the outer circle of a 5x5 window aroudn a non-fire
pixel
+ -- Only gid-s in the outer circle are sufficient, since small fire groups (in
+ -- this case, contains only on fire pixel) have been eliminated by the
+ -- clump&eliminate filter
+ CREATE TABLE nearby_groups (
+ n1 INT, n2 INT, n3 INT, n4 INT, n5 INT, n6 INT, n7 INT, n8 INT,
+ n9 INT, n10 INT, n11 INT, n12 INT, n13 INT, n14 INT, n15 INT, n16 INT,
+ gid_into INT);
+
+ -- This way, we find all GIDs that need to be merged in one go
+ INSERT INTO nearby_groups (
+ SELECT fe[x-2][y-2].gid, fe[x-1][y-2].gid, fe[x][y-2].gid,
fe[x+1][y-2].gid, fe[x+2][y-2].gid,
+ fe[x-2][y-1].gid,
fe[x+2][y-1].gid,
+ fe[x-2][y].gid,
fe[x+2][y].gid,
+ fe[x-2][y+1].gid,
fe[x+2][y+1].gid,
+ fe[x-2][y+2].gid, fe[x-1][y+2].gid, fe[x][y+2].gid,
fe[x+1][y+2].gid, fe[x+2][y+2].gid,
+ MAX(gid)
+ FROM fire_eliminated AS fe
+ GROUP BY fe[x-1:x+2][y-1:y+2]
+ HAVING gid IS NULL -- this is a non-fire pixel
+ AND MIN(gid) IS NOT NULL -- this group has at least one fire pixel
+ AND MIN(gid) <> MAX(gid) -- this group has >1 fire groups
+ );
+
+ -- We overwrite all GIDs that should be merged in one go
+ INSERT INTO fire_eliminated (
+ SELECT [x], [y], gid_into FROM fire_eliminated, nearby_groups
+ WHERE gid IS NOT NULL AND gid < gid_into
+ AND (gid = n1 OR gid = n2 OR gid = n3 OR gid = n4 OR
+ gid = n5 OR gid = n6 OR gid = n7 OR gid = n8 OR
+ gid = n9 OR gid = n10 OR gid = n11 OR gid = n12 OR
+ gid = n13 OR gid = n14 OR gid = n15 OR gid = n16)
+ );
+
+ -- TODO: add bridge!
+END;
_______________________________________________
checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list