hi list,
how can i get a list of all different occurences in a column of an attributetable.
Marco
On Fri, 2008-03-07 at 11:37 +0100, Marco Lechner wrote:
hi list,
how can i get a list of all different occurences in a column of an
attributetable.
Marco,
in a linux-box another way would be to combine db.select with the "sort"
command.
Example (commands in quotes):
I have a table TrainingSamples which looks like:
"v.info -c TrainingSamples"
Displaying column types/names for database connection of layer 1:
INTEGER|cat
INTEGER|clccode_le
TEXT|label
"db.selct TrainingSamples"
1|3|vegetation
2|334|Burned
3|334|Burned
[...]
45|332|non_veg
46|2|agro
And I want to get only the number of different labels (in column
"label")
"db.select TrainingSamples | cut -d "|" -f3 | sort -u"
agro
Burned
label
minext
non_veg
non_vegetation
urban
vegetation
water
---
* cut -d "|" splits an output line in more fields separated by the "|"
character
* -f3 (belongs to the cut command as well) and prints only the 3rd field
* sort -u does the actual job. It prints only once a name/ number no
matter how many times it appears.
how can i get a list of all different occurences in a column of an
attributetable.
v.db.select map=mapname column=columnname | uniq (for vector with a connected attribute table)
db.select map=mapname column=columnname | uniq (for tables unattached to a vector)
~ Eric.
On Fri, 2008-03-07 at 13:12 +0100, Nikos Alexandris wrote:
On Fri, 2008-03-07 at 11:37 +0100, Marco Lechner wrote:
> hi list,
>
> how can i get a list of all different occurences in a column of an
> attributetable.Marco,
in a linux-box another way would be to combine db.select with the "sort"
command.Example (commands in quotes):
I have a table TrainingSamples which looks like:
"v.info -c TrainingSamples"
Displaying column types/names for database connection of layer 1:
INTEGER|cat
INTEGER|clccode_le
TEXT|label"db.selct TrainingSamples"
1|3|vegetation
2|334|Burned
3|334|Burned[...]
45|332|non_veg
46|2|agroAnd I want to get only the number of different labels (in column
"label")"db.select TrainingSamples | cut -d "|" -f3 | sort -u"
agro
Burned
label
minext
non_veg
non_vegetation
urban
vegetation
water
To kick-out the "label" from being printed... (!)
db.select TrainingSamples | cut -d "|" -f3 | sort -u | grep -v label
On Fri, 2008-03-07 at 07:17 -0500, Patton, Eric wrote:
>how can i get a list of all different occurences in a column of an
>attributetable.v.db.select map=mapname column=columnname | uniq (for vector with a connected attribute table)
It doesn't work for me. What am I doing wrong?
v.db.select map=TrainingSamples column=clccode_le | uniq -u
clccode_le
3
3
332
3
334
2
334
5
3
5
131
131
332
131
1
332
3
332
1
332
2
db.select map=mapname column=columnname | uniq (for tables unattached to a vector)
Do the "map" and "column" parameters exist for db.select?
~ Eric.
Nikos
On Fri, 2008-03-07 at 07:17 -0500, Patton, Eric wrote:
how can i get a list of all different occurences in a column of an
attributetable.v.db.select map=mapname column=columnname | uniq (for vector with a connected attribute table)
It doesn't work for me. What am I doing wrong?
Omit the -u flag from uniq.
Do the "map" and "column" parameters exist for db.select?
Whoops; my mistake. No, those parameters don't exist for db.select.
db.select uses table= instead of map=.
There also isn't a column parameter, so you'd have to use some SQL
commands to get the column you need.
Note you can omit the column heading name in the output in both db.select and v.db.select
by using the -c flag:
db.select -c table=Matthew_2005013_Navigation | head -10
1|1860029|636162.13|5156960.89
2|186002910|636194.64|5157012.42
3|186002920|636229.43|5157064.57
4|186002930|636263.7|5157116.26
5|186002940|636297.62|5157169.61
...
~ Eric.
db.select table=TABLE
~ Eric.
Nikos
On Fri, 2008-03-07 at 08:55 -0500, Patton, Eric wrote:
On Fri, 2008-03-07 at 07:17 -0500, Patton, Eric wrote:
>>>how can i get a list of all different occurences in a column of an
>>>attributetable.
>>
>> v.db.select map=mapname column=columnname | uniq (for vector with a connected attribute table)
>>>It doesn't work for me. What am I doing wrong?
Omit the -u flag from uniq.
Hmmm? I tried but... :
v.db.select map=TrainingSamples column=clccode_le | uniq
clccode_le
3
334
3
332
334
3
334
332
334
2
334
5
3
5
131
1
131
332
131
1
332
3
332
1
332
2
332
2
>Do the "map" and "column" parameters exist for db.select?
Whoops; my mistake. No, those parameters don't exist for db.select.
db.select uses table= instead of map=.There also isn't a column parameter, so you'd have to use some SQL
commands to get the column you need.Note you can omit the column heading name in the output in both db.select and v.db.select
by using the -c flag:
Ahh... ok! thanks Eric!
db.select -c table=Matthew_2005013_Navigation | head -10
1|1860029|636162.13|5156960.89
2|186002910|636194.64|5157012.42
3|186002920|636229.43|5157064.57
4|186002930|636263.7|5157116.26
5|186002940|636297.62|5157169.61
...~ Eric.
db.select table=TABLE
>
> ~ Eric.Nikos
On Fri, Mar 07, 2008 at 03:35:17PM +0100, we recorded a bogon-computron collision of the <nikos.alexandris@felis.uni-freiburg.de> flavor, containing:
On Fri, 2008-03-07 at 08:55 -0500, Patton, Eric wrote:
> On Fri, 2008-03-07 at 07:17 -0500, Patton, Eric wrote:
> >>>how can i get a list of all different occurences in a column of an
> >>>attributetable.
> >>
> >> v.db.select map=mapname column=columnname | uniq (for vector with a connected attribute table)
> >>
>
> >It doesn't work for me. What am I doing wrong?
>
> Omit the -u flag from uniq.Hmmm? I tried but... :
v.db.select map=TrainingSamples column=clccode_le | uniq
clccode_le
3
334
3
332
334
[...]
"uniq" only prints unique lines if duplicated lines are adjacent --- so
the input to uniq has to be sorted first. The earlier example using
"sort -u" is the way to go when the input list isn't sorted, as in this
case.
--
Tom Russo KM5VY SAR502 DM64ux http://www.swcp.com/~russo/
Tijeras, NM QRPL#1592 K2#398 SOC#236 AHTB#1 http://kevan.org/brain.cgi?DDTNM
"And, isn't sanity really just a one-trick pony anyway? I mean all you get is
one trick, rational thinking, but when you're good and crazy, oooh, oooh,
oooh, the sky is the limit!" --- The Tick
On Fri, 2008-03-07 at 10:20 -0700, Tom Russo wrote:
On Fri, Mar 07, 2008 at 03:35:17PM +0100, we recorded a bogon-computron collision of the <nikos.alexandris@felis.uni-freiburg.de> flavor, containing:
[...]
So we end up with (in my example using the table "TrainingSamples" and
the column "clccode_le"):
v.db.select -c map=TrainingSamples column=clccode_le | sort -u
Still,
I think it's necessary to give a multiplatform solution (meaning a
solution which works for wingrass as well). So an SQL query should be it
as far as I understand.
Thank you all and sorriezzz if I insist too much ;-p
Cheers,
Nikos
piping to "cut" and "sort", does the job as i wanted - thanks (just didn't know about the unique-switch in sort. Great!
| cut -d "|" -f3 | sort -u"
Marco
Nikos Alexandris schrieb:
On Fri, 2008-03-07 at 11:37 +0100, Marco Lechner wrote:
hi list,
how can i get a list of all different occurences in a column of an attributetable.
Marco,
in a linux-box another way would be to combine db.select with the "sort"
command.Example (commands in quotes):
I have a table TrainingSamples which looks like:
"v.info -c TrainingSamples"
Displaying column types/names for database connection of layer 1:
INTEGER|cat
INTEGER|clccode_le
TEXT|label"db.selct TrainingSamples"
1|3|vegetation
2|334|Burned
3|334|Burned[...]
45|332|non_veg
46|2|agroAnd I want to get only the number of different labels (in column
"label")"db.select TrainingSamples | cut -d "|" -f3 | sort -u"
agro
Burned
label
minext
non_veg
non_vegetation
urban
vegetation
water---
* cut -d "|" splits an output line in more fields separated by the "|"
character
* -f3 (belongs to the cut command as well) and prints only the 3rd field
* sort -u does the actual job. It prints only once a name/ number no
matter how many times it appears.
On 07/03/08 19:42, Nikos Alexandris wrote:
On Fri, 2008-03-07 at 10:20 -0700, Tom Russo wrote:
On Fri, Mar 07, 2008 at 03:35:17PM +0100, we recorded a bogon-computron collision of the <nikos.alexandris@felis.uni-freiburg.de> flavor, containing:
[...]
So we end up with (in my example using the table "TrainingSamples" and
the column "clccode_le"):v.db.select -c map=TrainingSamples column=clccode_le | sort -u
Still,
I think it's necessary to give a multiplatform solution (meaning a
solution which works for wingrass as well). So an SQL query should be it
as far as I understand.
If you use a real SQL backend:
echo "select distinct(ColumnName) from TableName" | db.select
If you want to count the amount of occurences:
echo "select distinct(ColumnName) as ame, count(*) from TableName group by name" | db.select
Moritz