<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html style="direction: ltr;">
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
<title></title>
<style>body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="UTF-8" text="#000000"
bgcolor="#ffffff">
On 01/05/2012 10:49 AM, Bulent Arikan wrote:
<blockquote
cite="mid:CAA5wL0i6CtY2x5goeQ9pe-VC7BZQ1WhXEfi9qtzLJm-DQ0iGYw@mail.gmail.com"
type="cite">Hi,
<div><br>
</div>
<div>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. </div>
<div><br>
</div>
<div>Now, I have been trying to get 'db.execute' to work. One
suggestion was that I use a loop for automated updating (<span
style="border-collapse: collapse; color: rgb(34, 34, 34);
font-family: arial,sans-serif; font-size: 13px;">I am assuming
that I have to type this in the Terminal</span>):</div>
<div><br>
</div>
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;">>
while read l; do \<br>
col=`echo $l | cut -d= -f1`; val=`echo $l | cut -d= -f2`;
\<br>
sqlite3 sqlite.db "UPDATE centroid SET ${col}=${val} ;"; \<br>
> done < univar.txt</span></div>
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;"><br>
</span></div>
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;"><br>
</span></div>
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;">I came
up with a statement (below) by looking at the manual in
'r.univar' to create an SQL command file:</span></div>
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;"><br>
</span></div>
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;">
<p style="margin: 0px; font: 13px 'Lucida Grande';">
</p>
<p style="margin: 0px; font: 13px 'Lucida Grande';">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";"}' </p>
</span></div>
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;"><br>
</span></div>
</blockquote>
<br>
The above will create a list of update statements, one for each row
in the runivar.txt file. You can't feed db.execute a list of
statements, only one at a time. So you'll have to dump the output of
the above command into an intermediary file, then use the db.execute
"input" parameter to specify that file of SQL commands. <br>
<br>
It might be worth noting that if you have many UPDATE's, then doing
them one by one with an individual call to the database each time
might take a bit of time. In this case, going back to sqlite, you
could wrap all the UPDATE statements between a BEGIN...COMMIT
clause, then feed that file to db.execute (or directly to sqlite),
and it will open one connection, and push all the updates at once.
For many 1000's of rows this would probably be much faster.<br>
<br>
<blockquote
cite="mid:CAA5wL0i6CtY2x5goeQ9pe-VC7BZQ1WhXEfi9qtzLJm-DQ0iGYw@mail.gmail.com"
type="cite">
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;">that can
be used in 'db.execute' but I am getting a syntax error:</span></div>
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;"><br>
</span></div>
<div>DBMI-SQLite driver error:</div>
<div>Error in sqlite3_prepare():</div>
<div>near "sed": syntax error</div>
<div>ERROR: Error while executing: 'sed -e '1d' ............</div>
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;"><br>
</span></div>
<div><span style="border-collapse: collapse; color: rgb(34, 34,
34); font-family: arial,sans-serif; font-size: 13px;"> </span></div>
<div>What seems to be the problem? </div>
<div>Thank you for your time.</div>
<div><br>
</div>
<div>Bulent<br>
<br>
<div class="gmail_quote">On Wed, Jan 4, 2012 at 9:16 AM, Micha
Silver <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:micha@arava.co.il" target="_blank">micha@arava.co.il</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt
0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left: 1ex;">
<div style="direction: ltr;" bgcolor="#ffffff"
text="#000000">
<div> On 01/03/2012 10:44 PM, Bulent Arikan wrote:
<blockquote type="cite">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!
<div> <br>
</div>
</blockquote>
<br>
<br>
</div>
Yes, v.rast.stats is definitely the way to go if you want
raster univariate statistics pushed into a polygon vector.
<div><br>
<br>
<br>
<blockquote type="cite">
<div>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' </div>
<div>(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.</div>
<div><br>
</div>
</blockquote>
<br>
</div>
I do as follows:<br>
eval `g.gisenv`<br>
(This creates the environment variables for GISDBASE,
etc.)<br>
Now:<br>
db.connect driv=sqlite
database=$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db<br>
<br>
Next:<br>
g.copy vect=old,new<br>
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.<br>
Now you can do:<br>
<br>
v.db.addcol <new_vector> col="..., ..." .<br>
<br>
BTW, I think that v.rast.stats will automatically create
the needed columns .<br>
<br>
Cheers,<br>
Micha
<div>
<div><br>
<br>
<blockquote type="cite">
<div>Thank you again,</div>
<div><br>
</div>
<div>Bulent<br>
<br>
<div class="gmail_quote">On Tue, Jan 3, 2012 at
9:13 PM, Micha Silver <span dir="ltr"><<a
moz-do-not-send="true"
href="mailto:micha@arava.co.il"
target="_blank">micha@arava.co.il</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:
0pt 0pt 0pt 0.8ex; border-left: 1px solid
rgb(204, 204, 204); padding-left: 1ex;">
<div style="direction: ltr;" bgcolor="#ffffff"
text="#000000">
<div> On 01/03/2012 04:50 PM, Bulent Arikan
wrote:
<blockquote type="cite">Dear List,
<div><br>
</div>
<div>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. </div>
<div><br>
</div>
<div>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?</div>
<div><br>
</div>
</blockquote>
<br>
</div>
I'm not sure about a shorter way, but here's
an option:<br>
First setup your mapset to save attributes
to sqlite. <br>
> v.db.connect centroid driver=sqlite
database=.... <br>
<br>
Now add the columns which will accept
r.univar values to the sqlite table:<br>
> 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"<br>
<br>
Run r.univar on your raster, putting results
into a text file<br>
> r.univar your_rast -g > univar.txt<br>
<br>
Now do this loop to update values for the
centroid:<br>
> while read l; do \<br>
col=`echo $l | cut -d= -f1`; val=`echo
$l | cut -d= -f2`; \<br>
sqlite3 sqlite.db "UPDATE centroid SET
${col}=${val} ;"; \<br>
> done < univar.txt<br>
<br>
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).<br>
<br>
HTH, Micha<br>
<br>
<br>
<blockquote type="cite">
<div>
<div>Thank you for your time.<br
clear="all">
<div><br>
</div>
<div>GRASS 6.5 svn on Snow Leopard</div>
<div><br>
</div>
<div><br>
</div>
-- <br>
BÜLENT<br>
</div>
<br>
</div>
This mail was received via Mail-SeCure
System.<br>
<pre><fieldset></fieldset>
_______________________________________________
grass-user mailing list
<a moz-do-not-send="true" href="mailto:grass-user@lists.osgeo.org" target="_blank">grass-user@lists.osgeo.org</a>
<a moz-do-not-send="true" href="http://lists.osgeo.org/mailman/listinfo/grass-user" target="_blank">http://lists.osgeo.org/mailman/listinfo/grass-user</a>
This mail was received via Mail-SeCure System.
</pre>
<span><font color="#888888"> </font></span></blockquote>
<span><font color="#888888">
<p><br>
</p>
<br>
<pre cols="72">--
Micha Silver
GIS Consultant, Arava Development Co.
<a moz-do-not-send="true" href="http://www.surfaces.co.il" target="_blank">http://www.surfaces.co.il</a></pre>
</font></span></div>
</blockquote>
</div>
<br>
<br clear="all">
<div><br>
</div>
-- <br>
BÜLENT ARIKAN, PhD
<div>Senior Research Fellow</div>
<div>Research Center for Anatolian Civilizations<br>
Koç University<br>
İstiklal Caddesi No: 181 Merkez Han</div>
<div>Beyoğlu - ISTANBUL</div>
<div>TURKEY<br>
34433</div>
<div><a moz-do-not-send="true"
href="tel:%28%2B%2090%29%20212-393-6036"
value="+902123936036" target="_blank">(+ 90)
212-393-6036</a></div>
<br>
</div>
<br>
This mail was received via Mail-SeCure System.<br>
</blockquote>
<p><br>
</p>
</div>
</div>
</div>
</blockquote>
</div>
<br>
<br clear="all">
<div><br>
</div>
<br>
<br>
</div>
<br>
This mail was received via Mail-SeCure System.<br>
</blockquote>
<p><br>
</p>
</body>
</html>