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 0px 0px 0px;font:13.0px 'Lucida Grande'">
</p><p style="margin:0px 0px 0px 0px;font:13.0px '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>
<p></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><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 href="mailto:micha@arava.co.il" target="_blank">micha@arava.co.il</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<u></u>
<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 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 href="mailto:grass-user@lists.osgeo.org" target="_blank">grass-user@lists.osgeo.org</a>
<a 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 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 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>