<html style="direction: ltr;">
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
<style type="text/css">body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;" bidimailui-charset-is-forced="true"
bgcolor="#FFFFFF" text="#000000">
<br>
<br>
<div class="moz-cite-prefix">On 02/28/2017 10:25 PM, Ken Mankoff
wrote:<br>
</div>
<blockquote cite="mid:m2poi2kqpx.fsf@gmail.com" type="cite">
<pre wrap="">Hi GRASS list,
I'm trying to work with a database and have what I assume is a fairly simple question. I usually work with rasters and am not sure how to update or subset a vector. Specifically, I have the following vector product:
v.info BTMP # output below is trimmed
+----------------------------------------------------------------------------+
| Name: BTMP |
| Timestamp (first layer): none |
|----------------------------------------------------------------------------|
| Map format: native |
|----------------------------------------------------------------------------|
| Type of map: vector (level: 2) |
| |
| Number of points: 0 Number of centroids: 3378 |
| Number of lines: 0 Number of boundaries: 9859 |
| Number of areas: 3946 Number of islands: 433 |
| |
| Map is 3D: No |
| Number of dblinks: 1 |
+----------------------------------------------------------------------------+
With contents like:
db.select table=BTMP | head
cat|area|termini_id
1|5000479.797168|2
2|27502638.884425|1
3|29502830.803293|2
4|14001343.432071|3
5|3000287.878301|4
6|45004318.174513|4
7|4500431.817451|5
8|1500143.93915|5
9|7000671.716036|6
And I'd like to subset this. I'd like the largest area for each group of "termini_id". I can select the subset I want with the following:
db.select sql="select cat,max(area),termini_id from BTMP GROUP BY termini_id" | head</pre>
</blockquote>
The way to do this is with a subquery in the sql statement:<br>
<pre wrap="">db.select sql="select b.cat,b.area,b.termini_id from BTMP AS b WHERE b.area=(SELECT max(area) FROM BTMP WHERE termini_id=b.termini_id) GROUP BY b.termini_id"</pre>
<br>
<br>
<blockquote cite="mid:m2poi2kqpx.fsf@gmail.com" type="cite">
<pre wrap="">
cat|max(area)|termini_id
2|27502638.884425|1
3|29502830.803293|2
4|14001343.432071|3
6|45004318.174513|4
7|4500431.817451|5
9|7000671.716036|6
11|10751031.563912|7
12|4750455.80731|8
15|80007676.75469|9
But that just prints it to the terminal (our I can output to a file). I'd like to either have a new column with 0's for the non-selected and 1s for the selected rows. I can then display the selected rows using d.vect and the WHERE keyword. Or alternatively a new vector which is a subset of this one, based on the selected rows. How do I do this? I tried to generate the new column:</pre>
</blockquote>
<br>
Now you need to grab the cat values from the result of the first
query, and use them in v.extract :<br>
<br>
<tt>v.extract input=BTMP output=BTMP_max cats=2,3,4,6,7,9,11,12,15<br>
<br>
</tt>
<blockquote cite="mid:m2poi2kqpx.fsf@gmail.com" type="cite">
<pre wrap="">
v.db.addcolumn map=BTMP column="max_basin INT"
db.execute sql="update BTMP set max_basin=1 where max(area) GROUP BY termini_id"
But get errors I'm not sure how to interpret:
DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "GROUP": syntax error
DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "GROUP": syntax error
Thanks for any hints to this specific problem or a general tutorial on working with vectors and DBs.
Thanks,
-k.
_______________________________________________
grass-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:grass-user@lists.osgeo.org">grass-user@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/grass-user">https://lists.osgeo.org/mailman/listinfo/grass-user</a></pre>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Micha Silver
cell: 052-3665918</pre>
</body>
</html>