Re: [GRASS-user] Reclassify a vector adding a column
On Thu, Jun 18, 2020 at 12:29 PM Margherita Di Leo 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 grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Reclassify a vector adding a column
Super! Thank you both! On Wed, Jun 17, 2020 at 9:12 PM Micha Silver wrote: > > On 17/06/2020 19:38, Margherita Di Leo wrote: > > Dear Micha, > > On Wed, Jun 17, 2020 at 4:05 PM Micha Silver 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 WHEN '' THEN '' WHEN '' >> THEN ''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-665918https://orcid.org/-0002-1128-1325 > > -- Margherita Di Leo ___ grass-user mailing list grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Reclassify a vector adding a column
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 On Behalf Of Margherita Di Leo Sent: onsdag 17. juni 2020 18:39 To: Micha Silver Cc: GRASS user list Subject: Re: [GRASS-user] Reclassify a vector adding a column Dear Micha, On Wed, Jun 17, 2020 at 4:05 PM Micha Silver mailto:tsvi...@gmail.com>> 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 WHEN '' THEN '' WHEN '' THEN ''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 grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Reclassify a vector adding a column
Dear Micha, On Wed, Jun 17, 2020 at 4:05 PM Micha Silver 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 WHEN '' THEN '' WHEN '' > THEN ''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 grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Reclassify a vector adding a column
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 < stefan.blumentr...@nina.no> 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 *On Behalf Of > *Margherita > Di Leo > *Sent:* onsdag 17. juni 2020 10:35 > *To:* GRASS user list > *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 grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Reclassify a vector adding a column
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 On Behalf Of Margherita Di Leo Sent: onsdag 17. juni 2020 10:35 To: GRASS user list 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 grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Reclassify a vector adding a column
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 grass-user@lists.osgeo.org 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 grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
[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 grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
