<!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" bgcolor="#ffffff"
text="#000000">
On 01/06/2012 02:31 PM, Bulent Arikan wrote:
<blockquote
cite="mid:CAA5wL0jcabvT8TseS8_HaPz2o9Dx=LbYf5MV-TZvFOOpURDTdw@mail.gmail.com"
type="cite">OK! That makes sense now. I have one last standing
issue about this. It seems that the second layer I created using
'v.db.addtable' (after setting the dbf to SQL and importing a
centroid –vector– map) has a column for CAT but it does not have a
number. I checked the book to make sure and it says it should have
a number and it does not have to be continuous from the first
layer.
<div>
<br>
</div>
<div>I tried assigning a CAT# using 'v.category' and
'v.db.connect' but I still do not see a number in CAT column. I
think I should have a number there so that my "...WHERE cat =
...." argument makes sense. Any ideas how to correct this?</div>
<div><br>
</div>
</blockquote>
<br>
Probably v.to.db will do it. Say you want a column cat_2 in layer2
then something like:<br>
v.db.addcolumn <centroids> lay=2 col="cat_2 integer, <other
columns...>"<br>
v.to.db <centroids> lay=2 column=cat_2 option=cat<br>
<br>
<blockquote
cite="mid:CAA5wL0jcabvT8TseS8_HaPz2o9Dx=LbYf5MV-TZvFOOpURDTdw@mail.gmail.com"
type="cite">
<div>Thank you,</div>
<div><br>
</div>
<div>Bulent <br>
<br>
<div class="gmail_quote">On Thu, Jan 5, 2012 at 12:27 PM, Micha
Silver <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:micha@arava.co.il">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;" text="#000000"
bgcolor="#ffffff">
<div class="im"> On 01/05/2012 10:49 AM, Bulent Arikan
wrote:
<blockquote 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>
</div>
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.
<div>
<div class="h5"><br>
<br>
<blockquote 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>
</div>
</div>
</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>(+ 90) 212-393-6036</div>
<br>
</div>
<br>
This mail was received via Mail-SeCure System.<br>
</blockquote>
<p><br>
</p>
<br>
<pre class="moz-signature" cols="72">--
Micha Silver
GIS Consultant, Arava Development Co.
<a class="moz-txt-link-freetext" href="http://www.surfaces.co.il">http://www.surfaces.co.il</a></pre>
</body>
</html>