Reclassify a vector adding a column

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Reclassify a vector adding a column

margherita
Hi,

I need to reclassify a vector in the following way: based on a column of type string I have to create a new column of type string based on certain rules that I give upon the first column, and I thought it would be really handy if I could do it like  CASE WHEN... THEN ...
I have tried to feed a similar rules file to v.reclass but this statement is not recognised. How can I translate it? Furthermore, I would like to be able to just add a reclass column rather than creating a new vector file, is that possible? How?

Thank you in advance

Kind regards,

--
Margherita Di Leo

_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: Reclassify a vector adding a column

Zoltan Szecsei
Hi Margherita,
I offer my suggestion below as someone who uses varied (and any) product needed to get a job done quickly and correctly.

If you are able to open your vector dataset in QGIS (by simply dragging and dropping it into the 'Layer' panel), then take a look at the "Field Calculator" - it is the button with an Abacus for an icon.

Using this Field Calculator will make the job extremely simple.

Good luck and kind regards,
Zoltan


On 2020/06/17 10:34, Margherita Di Leo wrote:
Hi,

I need to reclassify a vector in the following way: based on a column of type string I have to create a new column of type string based on certain rules that I give upon the first column, and I thought it would be really handy if I could do it like  CASE WHEN... THEN ...
I have tried to feed a similar rules file to v.reclass but this statement is not recognised. How can I translate it? Furthermore, I would like to be able to just add a reclass column rather than creating a new vector file, is that possible? How?

Thank you in advance

Kind regards,

--
Margherita Di Leo

_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user

-- 

===============================================
Zoltan Szecsei GPrGISc 0031
Geograph (Pty) Ltd.
GIS and Photogrammetric Services

P.O. Box 7, Muizenberg 7950, South Africa.

Mobile: +27-83-6004028  (Best option: WhatsApp)
India:	+91 83418 01616
Qatar:  +974 5083 2722   www.geograph.co.za
===============================================

_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
SBL
Reply | Threaded
Open this post in threaded view
|

Re: Reclassify a vector adding a column

SBL
In reply to this post by margherita

Ciao Madi,

 

You could use db.execute and do something like:

db.execute sql="ALTER TABLE vectormap ADD COLUMN reclass_column TEXT;

UPDATE vectormap SET reclass_column = CASE

     WHEN old_column = ' old value 1' THEN ' new value 1'

     WHEN old_column = ' old value 2' THEN ' new value 2'

     WHEN old_column = ' old value 3' THEN ' new value 3'

     ELSE ' new value 4'

END; "

 

Maybe worth adding as an (validated) example to the manual of db.execute (https://grass.osgeo.org/grass78/manuals/db.execute.html)…

 

Cheers

Stefan

 

P.S.: I did not alidate the SQL syntax, but it should be roughly along those lines…

 

From: grass-user <[hidden email]> On Behalf Of Margherita Di Leo
Sent: onsdag 17. juni 2020 10:35
To: GRASS user list <[hidden email]>
Subject: [GRASS-user] Reclassify a vector adding a column

 

Hi,

 

I need to reclassify a vector in the following way: based on a column of type string I have to create a new column of type string based on certain rules that I give upon the first column, and I thought it would be really handy if I could do it like  CASE WHEN... THEN ...

I have tried to feed a similar rules file to v.reclass but this statement is not recognised. How can I translate it? Furthermore, I would like to be able to just add a reclass column rather than creating a new vector file, is that possible? How?

 

Thank you in advance

 

Kind regards,


--

Margherita Di Leo


_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: Reclassify a vector adding a column

margherita
Ciao Stefan,

thank you, I tried and it didn't give me any error but didn't update the column either...

On Wed, Jun 17, 2020 at 12:16 PM Stefan Blumentrath <[hidden email]> wrote:

Ciao Madi,

 

You could use db.execute and do something like:

db.execute sql="ALTER TABLE vectormap ADD COLUMN reclass_column TEXT;

UPDATE vectormap SET reclass_column = CASE

     WHEN old_column = ' old value 1' THEN ' new value 1'

     WHEN old_column = ' old value 2' THEN ' new value 2'

     WHEN old_column = ' old value 3' THEN ' new value 3'

     ELSE ' new value 4'

END; "

 

Maybe worth adding as an (validated) example to the manual of db.execute (https://grass.osgeo.org/grass78/manuals/db.execute.html)

 

Cheers

Stefan

 

P.S.: I did not alidate the SQL syntax, but it should be roughly along those lines…

 

From: grass-user <[hidden email]> On Behalf Of Margherita Di Leo
Sent: onsdag 17. juni 2020 10:35
To: GRASS user list <[hidden email]>
Subject: [GRASS-user] Reclassify a vector adding a column

 

Hi,

 

I need to reclassify a vector in the following way: based on a column of type string I have to create a new column of type string based on certain rules that I give upon the first column, and I thought it would be really handy if I could do it like  CASE WHEN... THEN ...

I have tried to feed a similar rules file to v.reclass but this statement is not recognised. How can I translate it? Furthermore, I would like to be able to just add a reclass column rather than creating a new vector file, is that possible? How?

 

Thank you in advance

 

Kind regards,


--

Margherita Di Leo



--
Margherita Di Leo

_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: Reclassify a vector adding a column

margherita
Dear Micha,

On Wed, Jun 17, 2020 at 4:05 PM Micha Silver <[hidden email]> wrote:

On 17/06/2020 16:14, Margherita Di Leo wrote:
Ciao Stefan,

thank you, I tried and it didn't give me any error but didn't update the column either...



The sqlite CASE statement should be:

CASE <old_column> WHEN '<old_val1>' THEN '<new_val1>' WHEN '<old_val2>' THEN '<new_val2>'....END;


Here's an example that worked for me. I have a "myroads" vector with column 'TYPE'


# Add new column for road width as text

v.db.addcolumn myroads column="width TEXT"

# Run update with CASE

db.execute sql="UPDATE myroads SET width=CASE TYPE WHEN 'Main' THEN 'Wide' WHEN 'Local' THEN 'Narrow' ELSE 'Unknown' END;"


Best, Micha

This worked! Thank you so much! I have one last question. Does it also support an IS LIKE condition, and if yes, how can I express it?

Thanks!


--
Margherita Di Leo

_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
SBL
Reply | Threaded
Open this post in threaded view
|

Re: Reclassify a vector adding a column

SBL

Here is the documentation for the LIKE operator in SQLite:

https://www.sqlitetutorial.net/sqlite-like/

 

It should be possible to use it in a “searched case statement”:

https://www.sqlitetutorial.net/sqlite-case/

 

So the syntax would be:

db.execute sql="UPDATE myroads SET width=CASE WHEN TYPE LIKE 'M%' THEN 'Wide' WHEN TYPE LIKE 'Loc%' THEN 'Narrow' ELSE 'Unknown' END;"

 

Cheers

Stefan

 

From: grass-user <[hidden email]> On Behalf Of Margherita Di Leo
Sent: onsdag 17. juni 2020 18:39
To: Micha Silver <[hidden email]>
Cc: GRASS user list <[hidden email]>
Subject: Re: [GRASS-user] Reclassify a vector adding a column

 

Dear Micha,

 

On Wed, Jun 17, 2020 at 4:05 PM Micha Silver <[hidden email]> wrote:

 

On 17/06/2020 16:14, Margherita Di Leo wrote:

Ciao Stefan,

 

thank you, I tried and it didn't give me any error but didn't update the column either...

 

 

 

The sqlite CASE statement should be:

CASE <old_column> WHEN '<old_val1>' THEN '<new_val1>' WHEN '<old_val2>' THEN '<new_val2>'....END;

 

Here's an example that worked for me. I have a "myroads" vector with column 'TYPE'

 

# Add new column for road width as text

v.db.addcolumn myroads column="width TEXT"

# Run update with CASE

db.execute sql="UPDATE myroads SET width=CASE TYPE WHEN 'Main' THEN 'Wide' WHEN 'Local' THEN 'Narrow' ELSE 'Unknown' END;"

 

Best, Micha

This worked! Thank you so much! I have one last question. Does it also support an IS LIKE condition, and if yes, how can I express it?

 

Thanks!



--

Margherita Di Leo


_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: Reclassify a vector adding a column

margherita
In reply to this post by margherita
Super! Thank you both!

On Wed, Jun 17, 2020 at 9:12 PM Micha Silver <[hidden email]> wrote:

On 17/06/2020 19:38, Margherita Di Leo wrote:
Dear Micha,

On Wed, Jun 17, 2020 at 4:05 PM Micha Silver <[hidden email]> wrote:

On 17/06/2020 16:14, Margherita Di Leo wrote:
Ciao Stefan,

thank you, I tried and it didn't give me any error but didn't update the column either...



The sqlite CASE statement should be:

CASE <old_column> WHEN '<old_val1>' THEN '<new_val1>' WHEN '<old_val2>' THEN '<new_val2>'....END;


Here's an example that worked for me. I have a "myroads" vector with column 'TYPE'


# Add new column for road width as text

v.db.addcolumn myroads column="width TEXT"

# Run update with CASE

db.execute sql="UPDATE myroads SET width=CASE TYPE WHEN 'Main' THEN 'Wide' WHEN 'Local' THEN 'Narrow' ELSE 'Unknown' END;"


Best, Micha

This worked! Thank you so much! I have one last question. Does it also support an IS LIKE condition, and if yes, how can I express it?


Yes, sqlite supports LIKE, for example:


micha@tp480:scripts$ db.select sql='SELECT TYPE,LENGTH FROM roads WHERE TYPE LIKE "%Ma%";'      
TYPE|LENGTH
Main|1.542641
Main|4.346567
Main|1.382557
Main|1.120805
Main|1.117633
Main|0.13777
....


Thanks!


--
Margherita Di Leo
-- 
Micha Silver
Ben Gurion Univ.
Sde Boker, Remote Sensing Lab
cell: +972-523-665918
https://orcid.org/0000-0002-1128-1325


--
Margherita Di Leo

_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: Reclassify a vector adding a column

Markus Neteler
On Thu, Jun 18, 2020 at 12:29 PM Margherita Di Leo <[hidden email]> wrote:
...
>> This worked! Thank you so much! I have one last question. Does it also support an IS LIKE condition, and if yes, how can I express it?

It does - this is also documented to some extent here (please add more
examples):

https://grass.osgeo.org/grass78/manuals/sql.html

Best,
Markus



--
Markus Neteler, PhD
https://www.mundialis.de - free data with free software
https://grass.osgeo.org
https://courses.neteler.org/blog
_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user