Re: [GRASS-user] Grass SQLite driver math funtions
2013/9/26 Pietro peter.z...@gmail.com On Thu, Sep 26, 2013 at 2:18 PM, Enrico Gallo enrico.ga...@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 ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Grass SQLite driver math funtions
Dear list, dear Markus, 2013/9/26 Markus Neteler nete...@osgeo.org On Wed, Sep 25, 2013 at 11:42 AM, Enrico Gallo enrico.ga...@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 ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Grass SQLite driver math funtions
On Thu, Sep 26, 2013 at 2:18 PM, Enrico Gallo enrico.ga...@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
___
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user
[GRASS-user] Grass SQLite driver math funtions
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? 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. Thank you for your help Regards ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Grass SQLite driver math funtions
On Wed, Sep 25, 2013 at 11:42 AM, Enrico Gallo enrico.ga...@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? 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 ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
