[GRASS-user] Grass SQLite driver & math funtions

[Enrico Gallo]

Dear list, dear Markus,


2013/9/26 Markus Neteler <neteler at osgeo.org>

> On Wed, Sep 25, 2013 at 11:42 AM, Enrico Gallo <enrico.gallo at gmail.com>
> wrote:
> > Dear list,
> > I am struggling with the use of simple math functions in SQL expression,
> > using SQLIte and db.execute.
> > I am writing a script for multiplatform end-users, so nor compiling
> SQLite
> > math contrib library nor piping data to bc using command line seem to me
> > feasable solutions.
> >
> > Do you have any suggestion?
>
> Could you post an example?
>


GRASS 6.4
from North Carolina data set
sqlite mapset

g.copy vect=railroads at PERMANENT,foo
v.db.addcol map=foo columns="power double precision,distance double
precision,level double precision"

v.db.update map=foo column=power value=80
v.db.update map=foo column=distance value=100

v.db.update map=foo column=level value=power-distance
#  OK

v.db.update map=foo column=level value=power-log10(distance)
DBMI-SQLite driver error:
Error in sqlite3_prepare():
no such function: log10
ERROR: Error while executing: 'UPDATE foo SET level=power-log10(distance)
'

same error for sqrt(), log(), etc

see also [1] (in Italian) with dbf driver; SQLite suggestion seems wrong


>
> > Including SQLite math functions in the standard binary GRASS GIS
> > distribuition could be a long term solution? I think this is the choice
> > SpatialLite did since 2.3 version.
>
> You mean
> http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html#math
>
> hence
> http://www.sqlite.org/contrib
> --> "extension-functions.c (50.96 KB) contributed by Liam Healy on
> 2010-02-06 15:45:07
> Provide mathematical and string extension functions for SQL queries
> using the loadable extensions mechanism. Math: acos, asin, atan, atn2,
> atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin,
> tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt,
> square, ceil, floor, pi. String: replicate, charindex, leftstr,
> rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr,
> padc, strfilter. Aggregate: stdev, variance, mode, median,
> lower_quartile, upper_quartile.
> "
>
> If you refer to this file, then it is more related to (your) SQLite
> installation rather than GRASS itself since GRASS just calls SQLite.
>
> best,
> Markus
>

It's exactly what I meant

So there is not a way to use this kind of funtions with vector attributes
without
compile extra library and load it in SQLIte?
Any python workaround?

Users need to change database, switching to MySQL or PostgreSQL?


Many thanks,
Enrico


[1] http://listserv.unipr.it/pipermail/grass-italia/2012-April/005897.html
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/grass-user/attachments/20130926/06b7b909/attachment.html>