Re: [sqlite] How to use aux_data effectively?
On 07 Sep 2013, at 02:03, Peter Aronson wrote: > 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. That's definitely an option; I'm already using sqlite3_user_data to store a bunch of function pointers that abstract the differences between geopackage, spatialite3 and spatialite4. It's all still pretty crude, but it's starting to get where I want it to be. Adding a fixed size LRU geometry cache to this seems like the way forward, which is essentially what spatialite has done as well. Fixed size LRU avoids the need to have cleanup routines. I would expect the memcmp to not be slower than creating the prepared geometry. Creating that object requires two converting from blob to GEOSGeometry to GEOSPreparedGeometry. I have a hard time imagining that this would be faster than one or two memcmps. Anyway, I'll experiment with this approach and profile it to see if it's worth it. Thanks for the ideas and advice. Pepijn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use aux_data effectively?
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
> To: Peter Aronson ; General Discussion of SQLite Database
>
> 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 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
>>> 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
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 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
>> 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
Re: [sqlite] How to use aux_data effectively?
On 06 Sep 2013, at 17:51, Simon Davies wrote:
>> Are there any other ways to kind of memoize the GeomFromText function
>> (or the parameters to distance) besides aux_data?
>
> select Distance( constGeom, geometry ) from table, (select
> GeomFromText('Point(13.457 3)') as constGeom );
I just tried this out, but unfortunately it doesn't make a difference. I guess
this boils down to the same thing as what I was doing before. table and the sub
select get joined and the function is invoked for every row again.
Or I could be doing something in wrong in my code of course. Snippet copied
below. With a simple query this prints 'Recreate' for each row.
Pepijn
disclaimer: this is just quick and dirty proof of concept code to see if this
would work or not.
static void ST_Contains(sqlite3_context *context, int nbArgs, sqlite3_value
**args) {
GEOS_START(context);
const GEOSPreparedGeometry *pg1 = sqlite3_get_auxdata(context, 0);
if (pg1 == NULL) {
printf("Recreate\n");
GEOSGeometry *g1 = GEOS_GET_GEOM( args, 0 );
if (g1 == NULL) {
sqlite3_result_error(context, error_message(&error), -1);
return;
}
pg1 = GEOSPrepare_r(GEOS_HANDLE, g1);
if (pg1 == NULL) {
sqlite3_result_error(context, error_message(&error), -1);
return;
}
} else {
printf("Reuse\n");
}
GEOSGeometry *g2 = GEOS_GET_GEOM( args, 1 );
if (g2 == NULL) {
sqlite3_result_error(context, error_message(&error), -1);
return;
}
char result = GEOSPreparedContains_r(GEOS_HANDLE, pg1, g2);
if (result == 2) {
geom_get_geos_error(&error);
sqlite3_result_error(context, error_message(&error), -1);
} else {
sqlite3_result_int(context, result);
}
GEOS_FREE_GEOM( g2 );
sqlite3_set_auxdata(context, 0, pg1, NULL);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use aux_data effectively?
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
> 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
Re: [sqlite] How to use aux_data effectively?
On 6 September 2013 16:38, Pepijn Van Eeckhoudt wrote:
> 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?
select Distance( constGeom, geometry ) from table, (select
GeomFromText('Point(13.457 3)') as constGeom );
>
> Thanks,
>
> Pepijn
>
Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use aux_data effectively?
On Fri, Sep 6, 2013 at 5:51 PM, Simon Davies
wrote:
> On 6 September 2013 16:38, Pepijn Van Eeckhoudt
> wrote:
> > 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?
>
> select Distance( constGeom, geometry ) from table, (select
> GeomFromText('Point(13.457 3)') as constGeom );
>
Clever. Thanks for that.
Or make your Distance function implementation do the GeomFromText
implicitly, with a bit of duck-typing.
You can test the argument's type, if blob use it as-is (hoping it's a
"geometry" blob in all cases), and if text, do the GeomFromText()
implicitly.
Since it's back to being a (text) constant, you can use aux_data again. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use aux_data effectively?
On 06 Sep 2013, at 17:58, Dominique Devienne wrote:
>> select Distance( constGeom, geometry ) from table, (select
>> GeomFromText('Point(13.457 3)') as constGeom );
>
> Clever. Thanks for that.
Simple and elegant. Thanks for the idea, I'll give it a try.
> Or make your Distance function implementation do the GeomFromText
> implicitly, with a bit of duck-typing.
The semantics of Distance (or better ST_Distance) are defined in the ISO SQL/MM
part 3 and OGC Simple Feature/SQL specs. I would prefer to avoid non-specified
behaviour.
Pepijn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use aux_data effectively?
On Fri, Sep 6, 2013 at 6:14 PM, Pepijn Van Eeckhoudt <
pep...@vaneeckhoudt.net> wrote:
> On 06 Sep 2013, at 17:58, Dominique Devienne wrote:
>
> >> select Distance( constGeom, geometry ) from table, (select
> >> GeomFromText('Point(13.457 3)') as constGeom );
> >
> > Clever. Thanks for that.
>
> Simple and elegant. Thanks for the idea, I'll give it a try.
>
> > Or make your Distance function implementation do the GeomFromText
> > implicitly, with a bit of duck-typing.
>
> The semantics of Distance (or better ST_Distance) are defined in the ISO
> SQL/MM part 3 and OGC Simple Feature/SQL specs. I would prefer to avoid
> non-specified behaviour
>
Well, given the fact that SQLite does not have user-defined-types, the spec
is already abused by using a blob for the "geometry" type. And that's more
natural a query with the duck-typing IMHO, rather than joining with a
scalar query. And aux-data use in Distance would make it just as efficient.
But I get your point too. My $0.02 :) --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use aux_data effectively?
On Fri, Sep 6, 2013 at 5:40 PM, Richard Hipp wrote: > On Fri, Sep 6, 2013 at 11:38 AM, Pepijn Van Eeckhoudt < > pep...@vaneeckhoudt.net> wrote: > > > 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? > > > > Correct. Would be nice to be able to mark a function as "deterministic" and allow aux-data for such function results when called with constant arguments. In fact, such deterministic function calls with constant arguments could even be evaluated at "parse time", with their results used directly (inlined) in the VDBE, in theory. Just speculations from someone who does not understand SQLite's implementation. :) --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use aux_data effectively?
On Fri, Sep 6, 2013 at 11:38 AM, Pepijn Van Eeckhoudt <
pep...@vaneeckhoudt.net> wrote:
> 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?
>
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
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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
