Ah, I see. Yeah, that would be trickier. You could save off the geometry blob and the GEOSPreparedGeometry object in a structure passed in to sqlite3_create_function and accessed via sqlite3_user_data and memcmp each geometry blob with the previous one to see if you need to regenerate the prepared geometry, but then you'd have to clean it up yourself somehow afterwards, and the cost of alloc, memcpy calls and memcmp calls would have to be cheaper than generating the prepared geometry.
It does seem like SQLite would benefit from somewhat to declare a function as invarient, so if it had constant inputs, its output would be treated as a constant input itself when fed into another function. Peter ----- Original Message ----- > From: Pepijn Van Eeckhoudt <pep...@vaneeckhoudt.net> > To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database > <sqlite-users@sqlite.org> > Cc: > Sent: Friday, September 6, 2013 4:47 PM > Subject: Re: [sqlite] How to use aux_data effectively? > > Peter, > > Thanks for the suggestion but that's not the part I'm trying to optimise > at the moment. It could be useful to use auxdata there as well to avoid > reparsing the text of course. What I would really like to achieve is that the > GEOSPreparedGeometry can be cached to speed up the geometry calculations. > > Pepijn > > On 07 Sep 2013, at 00:58, Peter Aronson <pbaron...@att.net> wrote: > >> Actually, as it turns out, you can get the result you want by having the > GeomFromText function use auxdata to store the geometry blob generated from > the > WKT string, since it's a constant. Then all the GeomFromText has to do is > to return the Geometry blob when sqlite3_get_auxdata returns non-NULL. >> >> Peter >> >> ----- Original Message ----- >>> From: Pepijn Van Eeckhoudt <pep...@vaneeckhoudt.net> >>> To: sqlite-users@sqlite.org >>> Cc: >>> Sent: Friday, September 6, 2013 8:38 AM >>> Subject: [sqlite] How to use aux_data effectively? >>> >>> Hi, >>> >>> In the extension I'm developing > (https://bitbucket.org/luciad/libgpkg) >>> I'm currently adding support for queries like: >>> select Distance( >>> GeomFromText('Point(13.457 3)'), >>> geometry >>> ) from table; >>> >>> GeomFromText takes a string and outputs a geometry blob >>> Distance takes two geometry blobs and returns a double >>> >>> In order to speed up the distance function I was wondering if I could >>> use aux_data to cache the parsed version of the first parameter since >>> this remains the same for every call. So far I haven't been able to > get >>> this to work though, aux_data is always NULL, no matter what I try. >>> >>> My hunch is that this is because the first parameter is the result of a >>> function call which could in theory return different values for each > row >>> even if the input parameters are constant. Is that correct? >>> >>> Are there any other ways to kind of memoize the GeomFromText function >>> (or the parameters to distance) besides aux_data? >>> >>> Thanks, >>> >>> Pepijn >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
