[GRASS-user] Connecting table

Thu Jan 5 03:49:03 EST 2012

Hi,

I really would like to learn more about the SQL command-based operations in
GRASS. To summarize: I have created a new mapset where the driver is set to
SQL. I have created second layers for all of my centroids (the tables in
the second layers have a different name like "Db_runivar" ). I used
'v.db.addtable' to have GRASS create columns to upload the results from
extended statistics of r.univar text files (e.g., Db_runivar.txt) for each
centroid.

Now, I have been trying to get 'db.execute' to work. One suggestion was
that I use a loop for automated updating (I am assuming that I have to type
this in the Terminal):

> while read l; do \
    col=`echo $l | cut -d= -f1`; val=`echo $l | cut -d= -f2`; \
    sqlite3 sqlite.db "UPDATE centroid SET ${col}=${val} ;"; \
> done < univar.txt

I came up with a statement (below) by looking at the manual in 'r.univar'
to create an SQL command file:

sed -e '1d' Dogubayazit_runivar.txt | awk -F'l' '{print "UPDATE
Dogubayazit_runivar SET non_null_cells = "$2", null_cells = "$3", min =
"$4", max = "$5", range = "$6", mean = "$7", mean_of_abs = "$8", stddev =
"$9", variance = "$10", coeff_var = "$11", sum = "$12", sum_abs = "$13",
first_quart = "$14", median = "$15", third_quart = "$16", perc_90  = "$17"
WHERE cat = "$1";"}'

that can be used in 'db.execute' but I am getting a syntax error:

DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "sed": syntax error
ERROR: Error while executing: 'sed -e '1d' ............

What seems to be the problem?
Thank you for your time.

Bulent

On Wed, Jan 4, 2012 at 9:16 AM, Micha Silver <micha at arava.co.il> wrote:

> **
> On 01/03/2012 10:44 PM, Bulent Arikan wrote:
>
> Thank you! This seems to be an interesting solution to the problem
> especially because it involves changing the driver from DBF to SQLITE. I
> ended up using 'v.rast.stats', which adds columns for extended statistics
> and uploads values all at once. The only thing is: data are recorded as
> part of the same layer (i.e., Layer 1). So, I cheated!
>
>
>
> Yes, v.rast.stats is definitely the way to go if you want raster
> univariate statistics pushed into a polygon vector.
>
>
>
>  However, I started trying your method. I created a new mapset to use
> sqlite and copied some vector maps from a mapset where default driver is
> DBF. I defined SQLITE as the new driver in the new mapset using
> 'db.connect'
> (driver=sqlite, database='$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db', no
> flags checked). Then I wanted to use 'v.db.connect' for a vector map but I
> cannot select table name etc. I also tried just using 'v.db.connect' but I
> still cannot see anything under Table. So. I am confused about how to
> define a new driver in a mapset.
>
>
> I do as follows:
> eval `g.gisenv`
> (This creates the environment variables for GISDBASE, etc.)
> Now:
> db.connect driv=sqlite database=$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db
>
> Next:
> g.copy vect=old,new
> to create a new copy of the original vector. The original will still have
> its attrib table as dbf. THe new copy will have an sqlite based attribute
> table.
> Now you can do:
>
> v.db.addcol <new_vector> col="..., ..." .
>
> BTW, I think that v.rast.stats will automatically create the needed
> columns .
>
> Cheers,
> Micha
>
>
>  Thank you again,
>
>  Bulent
>
> On Tue, Jan 3, 2012 at 9:13 PM, Micha Silver <micha at arava.co.il> wrote:
>
>>  On 01/03/2012 04:50 PM, Bulent Arikan wrote:
>>
>> Dear List,
>>
>>  I rasterized a centroid and ran 'r.univar', whose extended stats are
>> saved as a text file. Then, I ran 'v.db.addtable' to create a second layer
>> in the attribute table of the centroid and I had columns added in this
>> second layer using 'v.db.addcol'. I want GRASS to upload values from the
>> text file but I am not sure how to perform this task.
>>
>>  Is there a shorter way of creating a second layer and uploading values
>> from the text file or what should I do next so that the columns in the
>> second layer will be populated using the text file I have?
>>
>>
>>  I'm not sure about a shorter way, but here's an option:
>> First setup your mapset to save attributes to sqlite.
>> > v.db.connect centroid driver=sqlite database=....
>>
>> Now add the columns which will accept r.univar values to the sqlite table:
>> > v.db.addcol centroid col="n double, null_cells double, cells double,
>> min double, max double, range double, mean double, mean_of_abs double,
>> stddev double, variance double, coeff_var double, sum double"
>>
>> Run r.univar on your raster, putting results into a text file
>> > r.univar your_rast -g  > univar.txt
>>
>> Now do this loop to update values for the centroid:
>> > while read l; do \
>>     col=`echo $l | cut -d= -f1`; val=`echo $l | cut -d= -f2`; \
>>     sqlite3 sqlite.db "UPDATE centroid SET ${col}=${val} ;"; \
>> > done < univar.txt
>>
>> Assuming you want to run this for several centroids/areas, you'll
>> probably want to add a WHERE clause to the UPDATE statement so as to put
>> values for only one certain row (centroid).
>>
>> HTH, Micha
>>
>>
>>  Thank you for your time.
>>
>>  GRASS 6.5 svn on Snow Leopard
>>
>>
>>  --
>> BÜLENT
>>
>>  This mail was received via Mail-SeCure System.
>>
>>
>> _______________________________________________
>> grass-user mailing listgrass-user at lists.osgeo.orghttp://lists.osgeo.org/mailman/listinfo/grass-user
>>
>> This mail was received via Mail-SeCure System.
>>
>>
>>
>>
>>
>> --
>> Micha Silver
>> GIS Consultant, Arava Development Co.http://www.surfaces.co.il
>>
>>
>
>
>  --
> BÜLENT ARIKAN, PhD
> Senior Research Fellow
> Research Center for Anatolian Civilizations
> Koç University
> İstiklal Caddesi No: 181 Merkez Han
> Beyoğlu - ISTANBUL
> TURKEY
> 34433
> (+ 90) 212-393-6036
>
>
> This mail was received via Mail-SeCure System.
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/grass-user/attachments/20120105/88737ae2/attachment.html