[Monetdb-developers] How to register new "bulk" function with SQL?
Hoi Niels (and other SQL experts), I implemented the "bulk" version of ogc constructor function point(x:dbl,y:dbl) :wkb i.e. point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb] in the geom module (see below). How do I get SQL to use this function, e.g., in a scenario like this: CREATE TABLE coords (x double, y double); CREATE TABLE points (p point); INSERT INTO points SELECT POINT(x,y) FROM coords; For now, EXPLAIN still shows the explicit MAL interation calling the original scalar function: [...] | _67 := bat.new(nil:oid,nil:wkb); | | barrier (_71,_72,_73) := bat.newIterator(_16); | | _75 := algebra.find(_24,_72); | | _77 := geom.point(_73,_75); | | bat.insert(_67,_72,_77); | | redo (_71,_72,_73) := bat.hasMoreElements(_16); | | exit (_71,_72,_73); | | _25:bat[:oid,:wkb] := _67; | [...] Thanks in advance! Stefan ps: Eventually, we should consider implemeting bulk versions of all scalar functions in geom in the same (or a similar) way --- assuming that this might improve the performance ... On Thu, Oct 22, 2009 at 04:15:30PM +0000, Stefan Manegold wrote:
Update of /cvsroot/monetdb/geom/src/monetdb5 In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv25812/geom/src/monetdb5
Modified Files: geom.mx Log Message:
implemented bulk version of ogc constructor function point(x:dbl,y:dbl) :wkb i.e. point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb]
Open question: How to make SQL use this bulk function instead of an explicit MAL iterator over the scalar function, e.g., in queries like INSERT INTO points SELECT POINT(x, y) FROM coords;
Index: geom.mx =================================================================== RCS file: /cvsroot/monetdb/geom/src/monetdb5/geom.mx,v retrieving revision 1.29 retrieving revision 1.30 diff -u -d -r1.29 -r1.30 --- geom.mx 26 Jun 2009 07:37:52 -0000 1.29 +++ geom.mx 22 Oct 2009 16:15:28 -0000 1.30 @@ -170,6 +170,10 @@ address wkbcreatepoint comment "Construct a point from two geometries";
+command point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb] +address wkbcreatepoint_bat +comment "Construct a point-BAT from two geometry-BATs"; + @+ ogc information methods @mal command X(g:wkb) :dbl @@ -1010,6 +1014,76 @@ }
geom_export str +wkbcreatepoint_bat(int *out, int *x,int *y); + +str +wkbcreatepoint_bat(int *out, int *ix,int *iy) +{ + BAT *bo = NULL, *bx = NULL, *by = NULL; + dbl *x = NULL, *y = NULL; + BUN i, o; + wkb *p = NULL; + + if ((bx = BATdescriptor(*ix)) == NULL) { + throw(MAL, "geom.point", RUNTIME_OBJECT_MISSING); + } + if ((by = BATdescriptor(*iy)) == NULL) { + BBPreleaseref(bx->batCacheid); + throw(MAL, "geom.point", RUNTIME_OBJECT_MISSING); + } + if (!BAThdense(bx) || !BAThdense(by) || bx->hseqbase != by->hseqbase || BATcount(bx) != BATcount(by)) { + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + throw(MAL, "geom.point", "both arguments must have dense and aligned heads"); + } + + if ((bo = BATnew(TYPE_void, ATOMindex("wkb"), BATcount(bx))) == NULL) { + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + throw(MAL, "geom.point", MAL_MALLOC_FAIL); + } + BATseqbase(bo, bx->hseqbase); + bo->hdense = TRUE; + bo->hsorted = TRUE; + bo->H->nonil = TRUE; + BATkey(bo, TRUE); + + x = (dbl*) Tloc(bx, BUNfirst(bx)); + y = (dbl*) Tloc(by, BUNfirst(bx)); + for (i = 0, o = BUNlast(bo); i < BATcount(bx); i++, o++) { + str err = NULL; + if ((err = wkbcreatepoint(&p, &x[i], &y[i])) != MAL_SUCCEED) { + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + BBPreleaseref(bo->batCacheid); + throw(MAL, "geom.point", err); + } + tfastins_nocheck(bo, o, p, Tsize(bo)); + GDKfree(p); + p = NULL; + } + + BATsetcount(bo, BATcount(bx)); + bo->batDirty = TRUE; + bo->tdense = FALSE; + bo->tsorted = FALSE; + bo->T->nonil = FALSE; + BATkey(BATmirror(bo), FALSE); + + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + BBPkeepref(*out = bo->batCacheid); + return MAL_SUCCEED; + + bunins_failed: + if (p) GDKfree(p); + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + BBPreleaseref(bo->batCacheid); + throw(MAL, "geom.point", "bunins failed"); +} + +geom_export str mbroverlaps( bit *out, mbr *b1, mbr *b2);
str
------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Monetdb-sql-checkins mailing list Monetdb-sql-checkins@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
The remap optimizer gives an overview and solution to this problem. It has currently some hardwired assumptions, e.g. it would search for bat bulk operations only. If you would rename geom.point into batgeom.point for the bulk version, you should have reached your goal. Stefan Manegold wrote:
Hoi Niels (and other SQL experts),
I implemented the "bulk" version of ogc constructor function point(x:dbl,y:dbl) :wkb i.e. point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb] in the geom module (see below).
How do I get SQL to use this function, e.g., in a scenario like this: CREATE TABLE coords (x double, y double); CREATE TABLE points (p point); INSERT INTO points SELECT POINT(x,y) FROM coords;
For now, EXPLAIN still shows the explicit MAL interation calling the original scalar function:
[...] | _67 := bat.new(nil:oid,nil:wkb); | | barrier (_71,_72,_73) := bat.newIterator(_16); | | _75 := algebra.find(_24,_72); | | _77 := geom.point(_73,_75); | | bat.insert(_67,_72,_77); | | redo (_71,_72,_73) := bat.hasMoreElements(_16); | | exit (_71,_72,_73); | | _25:bat[:oid,:wkb] := _67; | [...]
Thanks in advance!
Stefan
ps: Eventually, we should consider implemeting bulk versions of all scalar functions in geom in the same (or a similar) way --- assuming that this might improve the performance ...
On Thu, Oct 22, 2009 at 04:15:30PM +0000, Stefan Manegold wrote:
Update of /cvsroot/monetdb/geom/src/monetdb5 In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv25812/geom/src/monetdb5
Modified Files: geom.mx Log Message:
implemented bulk version of ogc constructor function point(x:dbl,y:dbl) :wkb i.e. point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb]
Open question: How to make SQL use this bulk function instead of an explicit MAL iterator over the scalar function, e.g., in queries like INSERT INTO points SELECT POINT(x, y) FROM coords;
Index: geom.mx =================================================================== RCS file: /cvsroot/monetdb/geom/src/monetdb5/geom.mx,v retrieving revision 1.29 retrieving revision 1.30 diff -u -d -r1.29 -r1.30 --- geom.mx 26 Jun 2009 07:37:52 -0000 1.29 +++ geom.mx 22 Oct 2009 16:15:28 -0000 1.30 @@ -170,6 +170,10 @@ address wkbcreatepoint comment "Construct a point from two geometries";
+command point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb] +address wkbcreatepoint_bat +comment "Construct a point-BAT from two geometry-BATs"; + @+ ogc information methods @mal command X(g:wkb) :dbl @@ -1010,6 +1014,76 @@ }
geom_export str +wkbcreatepoint_bat(int *out, int *x,int *y); + +str +wkbcreatepoint_bat(int *out, int *ix,int *iy) +{ + BAT *bo = NULL, *bx = NULL, *by = NULL; + dbl *x = NULL, *y = NULL; + BUN i, o; + wkb *p = NULL; + + if ((bx = BATdescriptor(*ix)) == NULL) { + throw(MAL, "geom.point", RUNTIME_OBJECT_MISSING); + } + if ((by = BATdescriptor(*iy)) == NULL) { + BBPreleaseref(bx->batCacheid); + throw(MAL, "geom.point", RUNTIME_OBJECT_MISSING); + } + if (!BAThdense(bx) || !BAThdense(by) || bx->hseqbase != by->hseqbase || BATcount(bx) != BATcount(by)) { + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + throw(MAL, "geom.point", "both arguments must have dense and aligned heads"); + } + + if ((bo = BATnew(TYPE_void, ATOMindex("wkb"), BATcount(bx))) == NULL) { + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + throw(MAL, "geom.point", MAL_MALLOC_FAIL); + } + BATseqbase(bo, bx->hseqbase); + bo->hdense = TRUE; + bo->hsorted = TRUE; + bo->H->nonil = TRUE; + BATkey(bo, TRUE); + + x = (dbl*) Tloc(bx, BUNfirst(bx)); + y = (dbl*) Tloc(by, BUNfirst(bx)); + for (i = 0, o = BUNlast(bo); i < BATcount(bx); i++, o++) { + str err = NULL; + if ((err = wkbcreatepoint(&p, &x[i], &y[i])) != MAL_SUCCEED) { + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + BBPreleaseref(bo->batCacheid); + throw(MAL, "geom.point", err); + } + tfastins_nocheck(bo, o, p, Tsize(bo)); + GDKfree(p); + p = NULL; + } + + BATsetcount(bo, BATcount(bx)); + bo->batDirty = TRUE; + bo->tdense = FALSE; + bo->tsorted = FALSE; + bo->T->nonil = FALSE; + BATkey(BATmirror(bo), FALSE); + + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + BBPkeepref(*out = bo->batCacheid); + return MAL_SUCCEED; + + bunins_failed: + if (p) GDKfree(p); + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + BBPreleaseref(bo->batCacheid); + throw(MAL, "geom.point", "bunins failed"); +} + +geom_export str mbroverlaps( bit *out, mbr *b1, mbr *b2);
str
------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Monetdb-sql-checkins mailing list Monetdb-sql-checkins@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
Thanks, Martin! the below snipped is now reduced to a single statement: | _25:bat[:oid,:wkb] := batgeom.point(_16,_24); Stefan On Thu, Oct 22, 2009 at 06:36:00PM +0200, Martin Kersten wrote:
The remap optimizer gives an overview and solution to this problem. It has currently some hardwired assumptions, e.g. it would search for bat bulk operations only. If you would rename geom.point into batgeom.point for the bulk version, you should have reached your goal.
Stefan Manegold wrote:
Hoi Niels (and other SQL experts),
I implemented the "bulk" version of ogc constructor function point(x:dbl,y:dbl) :wkb i.e. point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb] in the geom module (see below).
How do I get SQL to use this function, e.g., in a scenario like this: CREATE TABLE coords (x double, y double); CREATE TABLE points (p point); INSERT INTO points SELECT POINT(x,y) FROM coords;
For now, EXPLAIN still shows the explicit MAL interation calling the original scalar function:
[...] | _67 := bat.new(nil:oid,nil:wkb); | | barrier (_71,_72,_73) := bat.newIterator(_16); | | _75 := algebra.find(_24,_72); | | _77 := geom.point(_73,_75); | | bat.insert(_67,_72,_77); | | redo (_71,_72,_73) := bat.hasMoreElements(_16); | | exit (_71,_72,_73); | | _25:bat[:oid,:wkb] := _67; | [...]
Thanks in advance!
Stefan
ps: Eventually, we should consider implemeting bulk versions of all scalar functions in geom in the same (or a similar) way --- assuming that this might improve the performance ...
On Thu, Oct 22, 2009 at 04:15:30PM +0000, Stefan Manegold wrote:
Update of /cvsroot/monetdb/geom/src/monetdb5 In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv25812/geom/src/monetdb5
Modified Files: geom.mx Log Message:
implemented bulk version of ogc constructor function point(x:dbl,y:dbl) :wkb i.e. point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb]
Open question: How to make SQL use this bulk function instead of an explicit MAL iterator over the scalar function, e.g., in queries like INSERT INTO points SELECT POINT(x, y) FROM coords;
Index: geom.mx =================================================================== RCS file: /cvsroot/monetdb/geom/src/monetdb5/geom.mx,v retrieving revision 1.29 retrieving revision 1.30 diff -u -d -r1.29 -r1.30 --- geom.mx 26 Jun 2009 07:37:52 -0000 1.29 +++ geom.mx 22 Oct 2009 16:15:28 -0000 1.30 @@ -170,6 +170,10 @@ address wkbcreatepoint comment "Construct a point from two geometries";
+command point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb] +address wkbcreatepoint_bat +comment "Construct a point-BAT from two geometry-BATs"; + @+ ogc information methods @mal command X(g:wkb) :dbl @@ -1010,6 +1014,76 @@ }
geom_export str +wkbcreatepoint_bat(int *out, int *x,int *y); + +str +wkbcreatepoint_bat(int *out, int *ix,int *iy) +{ + BAT *bo = NULL, *bx = NULL, *by = NULL; + dbl *x = NULL, *y = NULL; + BUN i, o; + wkb *p = NULL; + + if ((bx = BATdescriptor(*ix)) == NULL) { + throw(MAL, "geom.point", RUNTIME_OBJECT_MISSING); + } + if ((by = BATdescriptor(*iy)) == NULL) { + BBPreleaseref(bx->batCacheid); + throw(MAL, "geom.point", RUNTIME_OBJECT_MISSING); + } + if (!BAThdense(bx) || !BAThdense(by) || bx->hseqbase != by->hseqbase || BATcount(bx) != BATcount(by)) { + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + throw(MAL, "geom.point", "both arguments must have dense and aligned heads"); + } + + if ((bo = BATnew(TYPE_void, ATOMindex("wkb"), BATcount(bx))) == NULL) { + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + throw(MAL, "geom.point", MAL_MALLOC_FAIL); + } + BATseqbase(bo, bx->hseqbase); + bo->hdense = TRUE; + bo->hsorted = TRUE; + bo->H->nonil = TRUE; + BATkey(bo, TRUE); + + x = (dbl*) Tloc(bx, BUNfirst(bx)); + y = (dbl*) Tloc(by, BUNfirst(bx)); + for (i = 0, o = BUNlast(bo); i < BATcount(bx); i++, o++) { + str err = NULL; + if ((err = wkbcreatepoint(&p, &x[i], &y[i])) != MAL_SUCCEED) { + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + BBPreleaseref(bo->batCacheid); + throw(MAL, "geom.point", err); + } + tfastins_nocheck(bo, o, p, Tsize(bo)); + GDKfree(p); + p = NULL; + } + + BATsetcount(bo, BATcount(bx)); + bo->batDirty = TRUE; + bo->tdense = FALSE; + bo->tsorted = FALSE; + bo->T->nonil = FALSE; + BATkey(BATmirror(bo), FALSE); + + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + BBPkeepref(*out = bo->batCacheid); + return MAL_SUCCEED; + + bunins_failed: + if (p) GDKfree(p); + BBPreleaseref(bx->batCacheid); + BBPreleaseref(by->batCacheid); + BBPreleaseref(bo->batCacheid); + throw(MAL, "geom.point", "bunins failed"); +} + +geom_export str mbroverlaps( bit *out, mbr *b1, mbr *b2);
str
------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Monetdb-sql-checkins mailing list Monetdb-sql-checkins@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Monetdb-developers mailing list Monetdb-developers@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
participants (2)
-
Martin Kersten -
Stefan Manegold