[GRASS-user] SQL: generating numeric class numbers from class text labels?

[Markus Neteler]

Hi Moritz, all,

On Thu, Dec 5, 2019 at 9:22 AM Moritz Lennert
<mlennert at club.worldonline.be> wrote:
>
> On 4/12/19 19:58, Markus Neteler wrote:
> > Thanks for your answers.
> > In fact I need it in Python...
>
> Using SQL, you can do something like this (SQLite version):
>
> create table mytab (cat int, label varchar, labelint int);
>
> inserts...
>
> select * from mytab;
> 1|forest|
> 2|forest|
> 3|forest|
> 4|street|
> 5|street|
> 6|forest|
> 7|forest|
> 8|street|
> 9|grass|
> 10|grass|
>
>
> SELECT cat, label, rank() OVER win FROM mytab WINDOW win as (ORDER BY
> label);
> 1|forest|1
> 2|forest|1
> 3|forest|1
> 6|forest|1
> 7|forest|1
> 9|grass|6
> 10|grass|6
> 4|street|8
> 5|street|8
> 8|street|8
>
> Playing around with that should allow you to feed your table.

I tried this with NC:

g.copy vector=zipcodes_wake,myzipcodes_wake
db.select sql="SELECT cat, NAME, rank() OVER win as NAME_num FROM
myzipcodes_wake WINDOW win as (ORDER BY NAME);"
cat|NAME|NAME_num
22|ANGIER|1
31|ANGIER|1
16|APEX|3
23|APEX|3
30|APEX|3
34|APEX|3
29|CARY|7
35|CARY|7
36|CARY|7
38|CARY|7
19|CLAYTON|11
1|CREEDMOOR|12
...

Cool :-)

The question is now: how to turn this into an UPDATE statement (for
v.db.update or db.execute)?

thanks
Markus