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.
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? Thank you, Bulent On Thu, Jan 5, 2012 at 12:27 PM, Micha Silver <mi...@arava.co.il> wrote: > ** > On 01/05/2012 10:49 AM, Bulent Arikan wrote: > > 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";"}' > > > 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. > > 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. > > > 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 <mi...@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 <mi...@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@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 <%28%2B%2090%29%20212-393-6036> >> >> >> This mail was received via Mail-SeCure System. >> >> >> > > > > > > This mail was received via Mail-SeCure System. > > > -- 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
_______________________________________________ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user