[GRASS-user] Grass SQLite driver & math funtions

GRASS GRASS user
,
1

Dear list, dear Markus,

···

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

On Wed, Sep 25, 2013 at 11:42 AM, Enrico Gallo <enrico.gallo@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@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

2

On Thu, Sep 26, 2013 at 2:18 PM, Enrico Gallo <enrico.gallo@gmail.com> wrote:

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?

this example works only in grass7, otherwise you have to write your
python code to work directly with the sqlite table.

{{{
from grass.pygrass.modules.shortcuts import general as g
from grass.pygrass.vector import VectorTopo

# copy the vector map
g.copy(vect=['railroads', 'foo'], overwrite=True)

# define the column
cols = [('power', 'double precision'),
        ('distance', 'double precision'),
        ('level', 'double precision')]

# instantiate the vector map
foo = VectorTopo('foo')
foo.open('rw')

# add the new columns
for cname, ctype in cols:
    foo.table.columns.add(cname, ctype)

# add some values
foo.table.execute('UPDATE foo SET power=80')
foo.table.execute('UPDATE foo SET distance=100')
foo.table.conn.commit()

#------------------------------------------------
# work around
import math

for geo in foo:
    geo.attrs['level'] = geo.attrs['power'] - math.log10(geo.attrs['distance'])

}}}

Note that this is far to be efficient...

If you compile sqlite including the support for the extra functions,
you simply need to substitute the workaroud with:

{{{
foo.table.execute('UPDATE foo SET level=power - log10(distance)')
foo.table.conn.commit()
}}}

Best regards

Pietro

3

2013/9/26 Pietro <peter.zamb@gmail.com>

On Thu, Sep 26, 2013 at 2:18 PM, Enrico Gallo <enrico.gallo@gmail.com>
wrote:
> 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?

this example works only in grass7, otherwise you have to write your
python code to work directly with the sqlite table.

[...]

#------------------------------------------------
# work around
import math

for geo in foo:
    geo.attrs['level'] = geo.attrs['power'] -
math.log10(geo.attrs['distance'])

}}}

Note that this is far to be efficient...

As I have to manage table with 1E5 - 1E6 rows, I will try some python code
using pysqlite or APSW

thank you in any case for suggestions and workaround

best regards,

Enrico