Changeset: 67179644de6f for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=67179644de6f
Modified Files:
sql/backends/monet5/UDF/80_ssdb.sql
sql/backends/monet5/UDF/ssdb.c
sql/backends/monet5/UDF/ssdb.mal
Branch: ssdb
Log Message:
almost done with cooking observations (without computing polygons)
diffs (truncated from 499 to 300 lines):
diff --git a/sql/backends/monet5/UDF/80_ssdb.sql
b/sql/backends/monet5/UDF/80_ssdb.sql
--- a/sql/backends/monet5/UDF/80_ssdb.sql
+++ b/sql/backends/monet5/UDF/80_ssdb.sql
@@ -20,6 +20,23 @@ All Rights Reserved.
-- This file defines the signatures of the UDFs needed to run the SS-DB
-- benchmark on MonetDB
+-- Cooks one pixels value (tin_pixs.val) for each image in the input table
+-- 'tin_pixs' according to the description in the SS-DB paper.
+-- tin_img: name of the input table for image meta data, with columns
+-- (imageid int, time int, cyclem int)
+-- tin_pix: name of the input table for image pixel values, with columns
+-- (imageid int, x int, y int, val int)
+-- tout_obs: name of the output table for observations, with columns
+-- (obsid INT, imageid INT, time INT, cyclem INT, averageDist REAL,
+-- pixelSum BIGINT, centerx INT, centery INT,
+-- boxxstart INT, boxystart INT, boxxend INT, boxyend INT)
+-- tout_plg: name of the output table for polygons, with columns
+-- (obsid INT, ord INT, x INT, y INT)
+-- width: width of all images, assuming they are the same
+-- height: height of all images, assuming they are the same
+CREATE PROCEDURE cooking (tin_img CHAR(128), tin_pix CHAR(128), tout_obs
CHAR(128), tout_plg CHAR(128), width INT, height INT)
+EXTERNAL NAME ssdb.cooking;
+
-- Determines for each polygon in the input table 'tin' if it intersects with
-- the bounding box by [(xstart, ystart), (xstart+xlen, ystart+ylen)
-- The ID of the intersecting polygons are stored in the output table 'tout'
@@ -29,13 +46,6 @@ CREATE PROCEDURE intersects (
tin CHAR(128), tout CHAR(128), xstart INT, ystart INT, xlen INT, ylen
INT)
EXTERNAL NAME ssdb.intersects;
--- Cooks one pixels value (tin.val) for each image in the input table 'tin'
--- according to the description in the SS-DB paper.
--- tin: name of the input table, with columns (imageid int, x int, y int, val
int)
--- tout: name of the output table, with columns (obsid INT, imageid INT, time
INT, cyclem INT, averageDist REAL, pixelSum BIGINT, centerx INT, centery INT,
boxxstart INT, boxystart INT, boxxend INT, boxyend INT)
-CREATE PROCEDURE cooking (tin CHAR(128), tout CHAR(128), threshold int)
-EXTERNAL NAME ssdb.cooking;
-
-- Regrid each image in the input table 'tin' according to the definition of
Q3.
-- tin: name of the input table, with columns (imageid int, x int, y int, val
int)
-- tout: name of the output table, with columns (imageid int, x int, y int,
avgval real)
diff --git a/sql/backends/monet5/UDF/ssdb.c b/sql/backends/monet5/UDF/ssdb.c
--- a/sql/backends/monet5/UDF/ssdb.c
+++ b/sql/backends/monet5/UDF/ssdb.c
@@ -23,11 +23,18 @@
#include "sql_semantic.h"
#include "ssdb.h"
+#define MIN(a, b) ((a) < (b) ? (a) : (b))
+#define MAX(a, b) ((a) > (b) ? (a) : (b))
+
static sql_table *
-_bind_table(mvc *sql, char *tname)
+_bind_table(mvc *sql, char *sname, char *tname)
{
- sql_table *t = mvc_bind_table(sql, cur_schema(sql), tname);
-
+ sql_table *t = NULL;
+
+ if(sname)
+ t = mvc_bind_table(sql, mvc_bind_schema(sql, sname), tname);
+ if(!t)
+ mvc_bind_table(sql, cur_schema(sql), tname);
if(!t)
t = mvc_bind_table(sql, mvc_bind_schema(sql, "sys"), tname);
if(!t)
@@ -63,93 +70,335 @@ SSDBintersects(Client cntxt, MalBlkPtr m
str
SSDBcooking(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
-{ /* cooking(tin:str, tout:str, threshold:int):void */
+{ /* cooking(tin_img:str, tin_pix:str, tout_obs:str, tout_plg:str):void */
mvc *sql = NULL;
- sql_table *tin = NULL, *tout = NULL;
- /* tin ((imageid int, x int, y int, val int) */
- BAT *imageid_in = NULL, *x = NULL, *y = NULL, *val = NULL;
- /* tout (obsid INT, imageid INT, time INT, cyclem INT,
+ sql_table *tin_img = NULL, *tin_pix = NULL, *tout_obs = NULL, *tout_plg
= NULL;
+ /* tin_img (imageid int, time int, cyclem int) */
+ BAT *tin_img_imgid = NULL, *tin_img_time NULL, *tin_img_cyclem = NULL;
+ /* tin_pix (imageid int, x int, y int, val int) */
+ BAT *tin_pix_imgid = NULL, *tin_pix_x NULL, *tin_pix_y = NULL,
*tin_pix_val = NULL;
+ /* tout_obs (obsid INT, imageid INT, time INT, cyclem INT,
* averagedist REAL, pixelsum BIGINT, centerx INT,
* centery INT, boxxstart INT, boxystart INT, boxxend INT,
* boxyend INT) */
- BAT *obsid = NULL, *imageid_out = NULL, *time = NULL, *cyclem = NULL,
- *averagedist = NULL, *pixelsum = NULL, *centerx = NULL,
- *centery = NULL, *boxxstart = NULL, *boxystart = NULL, *boxxend
= NULL,
- *boxyend = NULL;
+ BAT *tout_obs_obsid = NULL, *tout_obs_imgid = NULL,
+ *tout_obs_time = NULL, *tout_obs_cyclem = NULL,
+ *tout_obs_avgdist = NULL, *tout_obs_pixsum = NULL,
+ *tout_obs_centerx = NULL, *tout_obs_centery = NULL,
+ *tout_obs_boxxstart = NULL, *tout_obs_boxystart = NULL,
+ *tout_obs_boxxend = NULL, *tout_obs_boxyend = NULL;
+ /* tout_plg (obsid INT, ord INT, x INT, y INT) */
+ BAT *tout_plg_obsid = NULL, *tout_plg_ord = NULL,
+ *tout_plg_x = NULL, *tout_plg_y = NULL;
+
+ BUN i = 0, idx = 0, nr_pixs = 0, nr_obs = 0;
+ int nr_imgs = 0, imgid = 0;
+ int *grpid = NULL, *obsid = NULL, cur_obsid = 0;
+ /* pointers to the actual values of the columns */
+ int *tin_pix_imgid_t = NULL, *tin_pix_x_t = NULL, *tin_pix_y_t = NULL,
+ *tin_pix_val_t = NULL;
+ int *tout_obs_obsid_t = NULL, *tout_obs_imgid_t = NULL,
+ *tout_obs_time_t = NULL, *tout_obs_cyclem_t = NULL,
+ *tout_obs_pixsum_t = NULL,
+ *tout_obs_centerx_t = NULL, *tout_obs_centery_t = NULL,
+ *tout_obs_boxxstart_t = NULL, *tout_obs_boxystart_t = NULL,
+ *tout_obs_boxxend_t = NULL, *tout_obs_boxyend_t = NULL;
+ dbl *tout_obs_avgdist_t = NULL;
+ /* intermediate sums */
+ lng *wtdSumX = NULL, *wtdSumY = NULL; /* weighted sum X,Y */
+ dbl *distSum = NULL;
+
str msg = getSQLContext(cntxt, mb, &sql, NULL);
- str tin_name = *(str *) getArgReference(stk, pci, 0 + pci->retc);
- str tout_name = *(str *) getArgReference(stk, pci, 1 + pci->retc);
- int threshold = *(int *) getArgReference(stk, pci, 2 + pci->retc);
+ str tin_img_name = *(str *) getArgReference(stk, pci, pci->retc + 0);
+ str tin_pix_name = *(str *) getArgReference(stk, pci, pci->retc + 1);
+ str tout_obs_name = *(str *) getArgReference(stk, pci, pci->retc + 2);
+ str tout_plg_name = *(str *) getArgReference(stk, pci, pci->retc + 3);
+ int width = *(int *) getArgReference(stk, pci, pci->retc + 4);
+ int height = *(int *) getArgReference(stk, pci, pci->retc + 5);
if (msg)
return msg;
- if (pci->argc - pci->retc != 3)
- return sql_message("COOKING(): 3 parameters expected, got %d",
pci->argc - pci->retc);
- if (!tin_name)
- return sql_message("COOKING(): missing name of the input
table");
- if (!tout_name)
- return sql_message("COOKING(): missing name of the output
table");
- if (threshold <= 0)
- return sql_message("COOKING(): 'threshold' must be a positive
integer");
+ if (pci->argc - pci->retc != 6)
+ return sql_message("COOKING(): 6 parameters expected, got %d",
pci->argc - pci->retc);
+ if (!tin_img_name || !tin_pix_name)
+ return sql_message("COOKING(): missing name(s) of the input
table(s)");
+ if (!tout_obs_name || !tout_plg_name)
+ return sql_message("COOKING(): missing name(s) of the output
table(s)");
- if(!(tin = _bind_table(sql, tin_name)))
- return sql_message("42S02!COOKING(): no such table '%s'",
tin_name);
- if(!(tout = _bind_table(sql, tout_name)))
- return sql_message("42S02!COOKING(): no such table '%s'",
tout_name);
+ if(!(tin_img = _bind_table(sql, "tmp", tin_img_name)))
+ return sql_message("42S02!COOKING(): no such table '%s'",
tin_img_name);
+ if(!(tin_pix = _bind_table(sql, "tmp", tin_pix_name)))
+ return sql_message("42S02!COOKING(): no such table '%s'",
tin_pix_name);
+ if(!(tout_obs = _bind_table(sql, NULL, tout_obs_name)))
+ return sql_message("42S02!COOKING(): no such table '%s'",
tout_obs_name);
+ if(!(tout_plg = _bind_table(sql, NULL, tout_plg_name)))
+ return sql_message("42S02!COOKING(): no such table '%s'",
tout_plg_name);
- /* get the BATs of all columns of the input table */
- if(!(imageid_in = _bind_bat(sql, tin, "imageid", TYPE_int, RDONLY)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "imageid");
- if(!(x = _bind_bat(sql, tin, "x", TYPE_int, RDONLY)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "x");
- if(!(y = _bind_bat(sql, tin, "y", TYPE_int, RDONLY)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "y");
- if(!(val = _bind_bat(sql, tin, "val", TYPE_int, RDONLY)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "val");
- /* get the BATs of all columns of the output table */
- if(!(obsid = _bind_bat(sql, tout, "obsid", TYPE_int, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "obsid");
- if(!(imageid_out = _bind_bat(sql, tout, "imageid", TYPE_int, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "imageid");
- if(!(time = _bind_bat(sql, tout, "time", TYPE_int, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "time");
- if(!(cyclem = _bind_bat(sql, tout, "cyclem", TYPE_int, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "cyclem");
- if(!(averagedist = _bind_bat(sql, tout, "averagedist", TYPE_dbl,
RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "averagedist");
- if(!(pixelsum = _bind_bat(sql, tout, "pixelsum", TYPE_lng, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "pixelsum");
- if(!(centerx = _bind_bat(sql, tout, "centerx", TYPE_int, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "centerx");
- if(!(centery = _bind_bat(sql, tout, "centery", TYPE_int, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "centery");
- if(!(boxxstart = _bind_bat(sql, tout, "boxxstart", TYPE_int, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "boxxstart");
- if(!(boxystart = _bind_bat(sql, tout, "boxystart", TYPE_int, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "boxystart");
- if(!(boxxend = _bind_bat(sql, tout, "boxxend", TYPE_int, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "boxxend");
- if(!(boxyend = _bind_bat(sql, tout, "boxyend", TYPE_int, RD_INS)))
- return sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "boxyend");
+ /* get the BATs of all columns of the input table 'tin_img' */
+ /* RD_INS means we want the insert BATs, since all input tables are
+ * declared as TEMP */
+ if(!(tin_img_imgid = _bind_bat(sql, tin_img, "imageid", TYPE_int,
RD_INS))) {
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "imageid");
+ goto CLEANUP_RETURN;
+ }
+ if(!(tin_img_time = _bind_bat(sql, tin_img, "time", TYPE_int, RD_INS)))
{
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "time");
+ goto CLEANUP_RETURN;
+ }
+ if(!(tin_img_cyclem = _bind_bat(sql, tin_img, "cyclem", TYPE_int,
RD_INS))) {
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "cyclem");
+ goto CLEANUP_RETURN;
+ }
+ /* get the BATs of all columns of the input table 'tin_pix' */
+ if(!(tin_pix_imgid = _bind_bat(sql, tin_pix, "imageid", TYPE_int,
RD_INS))) {
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "imageid");
+ goto CLEANUP_RETURN;
+ }
+ if(!(tin_pix_x = _bind_bat(sql, tin_pix, "x", TYPE_int, RD_INS))) {
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "x");
+ goto CLEANUP_RETURN;
+ }
+ if(!(tin_pix_y = _bind_bat(sql, tin_pix, "y", TYPE_int, RD_INS))) {
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "y");
+ goto CLEANUP_RETURN;
+ }
+ if(!(tin_pix_val = _bind_bat(sql, tin_pix, "val", TYPE_int, RD_INS))) {
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tin_name, "val");
+ goto CLEANUP_RETURN;
+ }
+ /* get the BATs of all columns of the output table 'tout_plg' */
+ /*
+ if(!(tout_plg_obsid = _bind_bat(sql, tout_plg, "obsid", TYPE_int,
RD_INS))) {
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "obsid");
+ goto CLEANUP_RETURN;
+ }
+ if(!(tout_plg_ord = _bind_bat(sql, tout_plg, "ord", TYPE_int, RD_INS)))
{
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "ord");
+ goto CLEANUP_RETURN;
+ }
+ if(!(tout_plg_x = _bind_bat(sql, tout_plg, "x", TYPE_int, RD_INS))) {
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "x");
+ goto CLEANUP_RETURN;
+ }
+ if(!(tout_plg_y = _bind_bat(sql, tout_plg, "y", TYPE_int, RD_INS))) {
+ msg = sql_message("42S02!COOKING(): no such column '%s.%s'",
tout_name, "y");
+ goto CLEANUP_RETURN;
+ }
+ */
+ tin_pix_imgid_t = (int*)Tloc(tin_pix_imgid, BUNfirst(tin_pix_imgid));
+ tin_pix_x_t = (int*)Tloc(tin_pix_x, BUNfirst(tin_pix_x));
+ tin_pix_y_t = (int*)Tloc(tin_pix_y, BUNfirst(tin_pix_y));
+ tin_pix_val_t = (int*)Tloc(tin_pix_val, BUNfirst(tin_pix_val));
- BBPunfix(imageid_in->batCacheid);
- BBPunfix(x->batCacheid);
- BBPunfix(y->batCacheid);
- BBPunfix(val->batCacheid);
- BBPunfix(obsid->batCacheid);
- BBPunfix(imageid_out->batCacheid);
- BBPunfix(time->batCacheid);
- BBPunfix(cyclem->batCacheid);
- BBPunfix(averagedist->batCacheid);
- BBPunfix(pixelsum->batCacheid);
- BBPunfix(centerx->batCacheid);
- BBPunfix(centery->batCacheid);
- BBPunfix(boxxstart->batCacheid);
- BBPunfix(boxystart->batCacheid);
- BBPunfix(boxxend->batCacheid);
- BBPunfix(boxyend->batCacheid);
- return MAL_SUCCEED;
+ nr_pixs = BATcount(tin_pix_imgid);
+ if(!(grpid = GDKmalloc(nr_pixs * sizeof(int)))) {
+ msg = sql_message("COOKING(): GDKmalloc 'grpid' failed");
+ goto CLEANUP_RETURN;
+ }
+ if(!(obsid = GDKmalloc(nr_pixs * sizeof(int)))) {
+ msg = sql_message("COOKING(): GDKmalloc 'obsid' failed");
+ goto CLEANUP_RETURN;
+ }
+
+ /* First pass over all input pixels, to: */
+ for (idx = 0, imgid = tin_pix_imgid_t[0], nr_imgs = 1;
+ idx < nr_pixs; idx++) {
+ /* 1. Compute a temporary group ID for each pixel, which is its
+ * position in the image */
+ grpid[idx] = tin_pix_y_t[idx] * width + tin_pix_x_t[idx];
+
+ assert(tin_pix_imgid_t[idx] >= imgid);
+ if(tin_pix_imgid_t[idx] != imgid){
+ imgid = tin_pix_imgid_t[idx];
+ /* 2. compute the actual number of images */
+ nr_imgs++;
+ }
+ }
+
+ /* Second pass over all input pixels, to group the pixels into
+ * observations and assign them a unique obsid */
+ for (idx = 0; idx < nr_pixs; idx++) {
+ int x_prev, y_prev, x = tin_pix_x_t[idx], y = tin_pix_y_t[idx],
+ min = grpid[idx];
+ /* search back for the smallest 'grpid' value of my neighbours
*/
+ for(i = idx-1, x_prev = tin_pix_x_t[i], y_prev = tin_pix_y_t[i];
+ /* current or previous row */
+ i >= 0 && (y_prev == y || y_prev == y-1);
_______________________________________________
checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
