Re: [GRASS-dev] Re: [GRASS-user] v.db.join script
On 19/10/10 15:23, Micha Silver wrote: Moritz Lennert wrote: On 13/10/10 22:23, Micha Silver wrote: One line from the v.db.join script uses grep and cut to get the column names and the next line gets the column types like so: db.describe -c bike_rides2 | grep '^Column' | cut -d ':' -f 3 INTEGER CHARACTER INTEGER CHARACTER CHARACTER CHARACTER CHARACTER So the column size is actually ignored. Next, in the script the above output is used by v.db.addcol to create the new columns in the joined vector. So all new character columns are created as a single char and the actual length is never used. Questions: Is the db.describe output the same for all db drivers? Any suggestions how to fix this as a script? Why not use an SQL join, i.e. something like the following ? 1) CREATE TABLE temp AS (SELECT * FROM $maptable JOIN $otable ON $column=$ocolumn) 2) rename table $maptable to something else 3) rename table temp to $maptable 4) if this works, remove the original $maptable Interesting. So your suggestion is to run the above sql commands thru db.execute to create a new attribute table for an existing vector? Yes. You could put them all into a temporary text file then send this file as one transaction to db.execute. Moritz ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-dev] Re: [GRASS-user] v.db.join script
Moritz Lennert wrote: On 13/10/10 22:23, Micha Silver wrote: One line from the v.db.join script uses grep and cut to get the column names and the next line gets the column types like so: db.describe -c bike_rides2 | grep '^Column' | cut -d ':' -f 3 INTEGER CHARACTER INTEGER CHARACTER CHARACTER CHARACTER CHARACTER So the column size is actually ignored. Next, in the script the above output is used by v.db.addcol to create the new columns in the joined vector. So all new character columns are created as a single char and the actual length is never used. Questions: Is the db.describe output the same for all db drivers? Any suggestions how to fix this as a script? Why not use an SQL join, i.e. something like the following ? 1) CREATE TABLE temp AS (SELECT * FROM $maptable JOIN $otable ON $column=$ocolumn) 2) rename table $maptable to something else 3) rename table temp to $maptable 4) if this works, remove the original $maptable Interesting. So your suggestion is to run the above sql commands thru db.execute to create a new attribute table for an existing vector? Not tested, but might be a more elegant solution ? Moritz This mail was received via Mail-SeCure System. ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] v.db.join script
On Fri, Oct 15, 2010 at 2:08 PM, Micha Silver wrote: > Hi Jon: (Jon, sorry for my previous email, I missed this thread). > So we're pretty clear that this problem creeps up only on PostgreSQL and > MySQL based vector layers. I checked in the documentation and both databases > will create a *one* char wide column when the CHARACTER type is used without > any size. Sqlite uses their "dynamic typing " so any CHAR column can be any > size and the bug gets "hidden"... > > Here's a new v.db.join script that seems to overcome the width problem. I > tested *only* on a Postgresql based vector. It creates the full width > columns like in the original table. Would you mind testing on MySQL? To use > this scrpt, you'll have to drop it into your GRASS scripts directory > (probably /usr/lib/grass64/scripts ) and make sure it's executable. Then run > it as v.db.join2. @Micha: Since Jon tested it on MySQL successfully, please consider to submit it as patch to trac (please clean it up a bit then). thanks Markus ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-dev] Re: [GRASS-user] v.db.join script
On 13/10/10 22:23, Micha Silver wrote: One line from the v.db.join script uses grep and cut to get the column names and the next line gets the column types like so: db.describe -c bike_rides2 | grep '^Column' | cut -d ':' -f 3 INTEGER CHARACTER INTEGER CHARACTER CHARACTER CHARACTER CHARACTER So the column size is actually ignored. Next, in the script the above output is used by v.db.addcol to create the new columns in the joined vector. So all new character columns are created as a single char and the actual length is never used. Questions: Is the db.describe output the same for all db drivers? Any suggestions how to fix this as a script? Why not use an SQL join, i.e. something like the following ? 1) CREATE TABLE temp AS (SELECT * FROM $maptable JOIN $otable ON $column=$ocolumn) 2) rename table $maptable to something else 3) rename table temp to $maptable 4) if this works, remove the original $maptable Not tested, but might be a more elegant solution ? Moritz ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] v.db.join script
Hi Micha, I have tested v.db.join2 with mysql and it worked. No truncation! I am using GRASS 6.4.0 on macosx. I will think carefully about your workflow suggestion, many, many thanks, Jon > Hi Jon: > > So we're pretty clear that this problem creeps up only on PostgreSQL and > MySQL based vector layers. I checked in the documentation and both > databases will create a *one* char wide column when the CHARACTER type > is used without any size. Sqlite uses their "dynamic typing " so any > CHAR column can be any size and the bug gets "hidden"... > > Here's a new v.db.join script that seems to overcome the width problem. > I tested *only* on a Postgresql based vector. It creates the full width > columns like in the original table. Would you mind testing on MySQL? To > use this scrpt, you'll have to drop it into your GRASS scripts directory > (probably /usr/lib/grass64/scripts ) and make sure it's executable. Then > run it as v.db.join2. > > Additional note: If you're already using a spatial database, and you > have external data tables to join, wouldn't the best workflow be to > throw your new attributes into the database, and create a VIEW right in > MySQL?? > > Regards, > Micha > > On 10/12/2010 12:01 PM, Jon Eiriksson wrote: >> >> Hi, >> >> I have a truncation problem with v.db.join. This has been raised before >> - >> >> >> ([GRASS-user] Re: grass v.db.join >> >> Gary Nobles >> Fri, 12 Mar 2010 11:11:26 -0800) >> >> - but I have not seen a solution. I have tried my own data, the >> spearfish60 example data, and the example in Neteler and Mitasova's >> book. >> The new data columns are apparently defined as 1 character long, and the >> data become truncated accordingly, much against my intention. I use >> mysql. >> >> Can anyone point out a solution? I copy a session with the spearfish60 >> data below. >> >> Jon >> >> GRASS 6.4.0 (spearfish60):~ > g.copy vect=soils,mysoils >> Copy vector to current mapset as >> GRASS 6.4.0 (spearfish60):~ > db.select mysoils >> cat|label >> 1|Aab >> 2|Ba >> 3|Bb >> 4|BcB >> 5|BcC >> 6|BeE >> . >> . >> . >> GRASS 6.4.0 (spearfish60):~ > db.in.ogr soils_legend.csv >> out=soils_legend >> Imported table with 55 rows >> GRASS 6.4.0 (spearfish60):~ > db.select soils_legend >> id|shortname|longname >> 0|no data|no data >> 1|AaB|Alice fine sandy loam, 0 to 6 >> 2|Ba|Barnum silt loam >> 3|Bb|Barnum silt loam, channeled >> 4|BcB|Boneek silt loam, 2 to 6 >> 5|BcC|Boneek silt loam, 6 to 9 >> 6|BeE|Butche stony loam, 6 to 50 >> . >> . >> . >> GRASS 6.4.0 (spearfish60):~ > v.db.join mysoils col=label >> otable=soils_legend ocol=shortname >> GRASS 6.4.0 (spearfish60):~ > v.db.select mysoils >> cat|label|id|shortname|longname >> 1|Aab|1|A|A >> 2|Ba|2|B|B >> 3|Bb|3|B|B >> 4|BcB|4|B|B >> 5|BcC|5|B|B >> 6|BeE|6|B|B >> . >> . >> . >> GRASS 6.4.0 (spearfish60):~ > db.describe -c mysoils >> ncols: 5 >> nrows: 54 >> Column 1: cat:INTEGER:11 >> Column 2: label:CHARACTER:13 >> Column 3: id:CHARACTER:1 >> Column 4: shortname:CHARACTER:1 >> Column 5: longname:CHARACTER:1 >> GRASS 6.4.0 (spearfish60):~ > db.describe -c soils_legend >> ncols: 3 >> nrows: 55 >> Column 1: id:CHARACTER:255 >> Column 2: shortname:CHARACTER:255 >> Column 3: longname:CHARACTER:255 >> GRASS 6.4.0 (spearfish60):~ > >> == >> >> >> --- >> Jon Eiriksson, Research Professor, >> Earth Science Institute, University of Iceland, >> Askja, Sturlugata 7, >> IS-101 Reykjavik, Iceland. >> >> Tel +354 525 44 75 >> Fax +354 525 44 99 >> Email j...@hi.is >> Webhttp://www.raunvis.hi.is/~jeir/ >> --- >> >> >> >> >> ___ >> grass-user mailing list >> grass-user@lists.osgeo.org >> http://lists.osgeo.org/mailman/listinfo/grass-user >> >> This mail was received via Mail-SeCure System. >> >> > > > -- > Micha Silver > Arava Development Co. +972-52-3665918 > http://www.surfaces.co.il > > > ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] v.db.join script
Hi Jon: So we're pretty clear that this problem creeps up only on PostgreSQL and MySQL based vector layers. I checked in the documentation and both databases will create a *one* char wide column when the CHARACTER type is used without any size. Sqlite uses their "dynamic typing " so any CHAR column can be any size and the bug gets "hidden"... Here's a new v.db.join script that seems to overcome the width problem. I tested *only* on a Postgresql based vector. It creates the full width columns like in the original table. Would you mind testing on MySQL? To use this scrpt, you'll have to drop it into your GRASS scripts directory (probably /usr/lib/grass64/scripts ) and make sure it's executable. Then run it as v.db.join2. Additional note: If you're already using a spatial database, and you have external data tables to join, wouldn't the best workflow be to throw your new attributes into the database, and create a VIEW right in MySQL?? Regards, Micha On 10/12/2010 12:01 PM, Jon Eiriksson wrote: Hi, I have a truncation problem with v.db.join. This has been raised before - ([GRASS-user] Re: grass v.db.join Gary Nobles Fri, 12 Mar 2010 11:11:26 -0800) - but I have not seen a solution. I have tried my own data, the spearfish60 example data, and the example in Neteler and Mitasova's book. The new data columns are apparently defined as 1 character long, and the data become truncated accordingly, much against my intention. I use mysql. Can anyone point out a solution? I copy a session with the spearfish60 data below. Jon GRASS 6.4.0 (spearfish60):~ > g.copy vect=soils,mysoils Copy vector to current mapset as GRASS 6.4.0 (spearfish60):~ > db.select mysoils cat|label 1|Aab 2|Ba 3|Bb 4|BcB 5|BcC 6|BeE . . . GRASS 6.4.0 (spearfish60):~ > db.in.ogr soils_legend.csv out=soils_legend Imported table with 55 rows GRASS 6.4.0 (spearfish60):~ > db.select soils_legend id|shortname|longname 0|no data|no data 1|AaB|Alice fine sandy loam, 0 to 6 2|Ba|Barnum silt loam 3|Bb|Barnum silt loam, channeled 4|BcB|Boneek silt loam, 2 to 6 5|BcC|Boneek silt loam, 6 to 9 6|BeE|Butche stony loam, 6 to 50 . . . GRASS 6.4.0 (spearfish60):~ > v.db.join mysoils col=label otable=soils_legend ocol=shortname GRASS 6.4.0 (spearfish60):~ > v.db.select mysoils cat|label|id|shortname|longname 1|Aab|1|A|A 2|Ba|2|B|B 3|Bb|3|B|B 4|BcB|4|B|B 5|BcC|5|B|B 6|BeE|6|B|B . . . GRASS 6.4.0 (spearfish60):~ > db.describe -c mysoils ncols: 5 nrows: 54 Column 1: cat:INTEGER:11 Column 2: label:CHARACTER:13 Column 3: id:CHARACTER:1 Column 4: shortname:CHARACTER:1 Column 5: longname:CHARACTER:1 GRASS 6.4.0 (spearfish60):~ > db.describe -c soils_legend ncols: 3 nrows: 55 Column 1: id:CHARACTER:255 Column 2: shortname:CHARACTER:255 Column 3: longname:CHARACTER:255 GRASS 6.4.0 (spearfish60):~ > == --- Jon Eiriksson, Research Professor, Earth Science Institute, University of Iceland, Askja, Sturlugata 7, IS-101 Reykjavik, Iceland. Tel +354 525 44 75 Fax +354 525 44 99 Email j...@hi.is Webhttp://www.raunvis.hi.is/~jeir/ --- ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user This mail was received via Mail-SeCure System. -- Micha Silver Arava Development Co. +972-52-3665918 http://www.surfaces.co.il #!/bin/sh # # MODULE: v.db.join # AUTHOR(S):Markus Neteler # PURPOSE: Join a table to a map table # COPYRIGHT:(C) 2007-2008 by Markus Neteler and the GRASS Development Team # # This program is free software under the GNU General Public # License (>=v2). Read the file COPYING that comes with GRASS # for details. # # #%Module #% description: Allows to join a table to a vector map table. #% keywords: vector, database, attribute table #%End #%option #% key: map #% type: string #% key_desc : name #% gisprompt: old,vector,vector #% description: Vector map to which to join other table #% required : yes #%end #%option #% key: layer #% type: integer #% description: Layer where to join #% answer: 1 #% required : no #% gisprompt: old_layer,layer,layer #%end #%option #% key: column #% type: string #% description: Join column in map table #% required : yes #% gisprompt: old_dbcolumn,dbcolumn,dbcolumn #%end #%option #% key: otable #% type: string #% description: Other table name #% required : yes #% gisprompt: old_dbtable,dbtable,dbtable #%end #%option #% key: ocolumn #% type: string #% description: Join column in other table #% required : yes #% gisprompt: old_dbcolumn,dbcolumn,dbcolumn #%end if [ -z "$GISBASE" ] ; then echo "You must be i
Re: [GRASS-user] v.db.join script
Jon Eiriksson wrote: > I apologise for possible double-posting - but it seems that I did not > send the email copied below to the correct address. If anyone has > experienced the v.d.b.join column definition/truncation problem > desacribed belowl and has some advice, I would be very grateful to > hear about it. > > Best regards, Jon > > == > Hi, > > I have a truncation problem with v.db.join. This has been raised > before - > > > ([GRASS-user] Re: grass v.db.join > > Gary Nobles > Fri, 12 Mar 2010 11:11:26 -0800) > > - but I have not seen a solution. I have tried my own data, the > spearfish60 example data, and the example in Neteler and Mitasova's > book. > The new data columns are apparently defined as 1 character long, and the > data become truncated accordingly, much against my intention. I use > mysql. > > Can anyone point out a solution? I copy a session with the spearfish60 > data below. Hi Jon! Not a direct solution but an alternative: Is mysql a requirement? What about using sqlite? I think I have never seen this problem while using/ playing around with sqlite. Nikos [...] ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] v.db.join script
Thank you for the response, I ran the same data with sqlite, and this is what I get with db.describe -c: [sqlite db] GRASS 6.4.0 (spearfish60):~ > db.describe -c mysoils ncols: 5 nrows: 54 Column 1: cat:INTEGER:20 Column 2: label:CHARACTER:13 Column 3: id:CHARACTER:1 Column 4: shortname:CHARACTER:1 Column 5: longname:CHARACTER:1 GRASS 6.4.0 (spearfish60):~ > db.describe -c soils_legend ncols: 3 nrows: 55 Column 1: id:CHARACTER:1 Column 2: shortname:CHARACTER:1 Column 3: longname:CHARACTER:1 GRASS 6.4.0 (spearfish60):~ > - and there is no truncation of data after v.db.join, while [mysql db] GRASS 6.4.0 (spearfish60):~ > db.describe -c mysoils ncols: 5 nrows: 54 Column 1: cat:INTEGER:11 Column 2: label:CHARACTER:13 Column 3: id:CHARACTER:1 Column 4: shortname:CHARACTER:1 Column 5: longname:CHARACTER:1 GRASS 6.4.0 (spearfish60):~ > db.describe -c soils_legend ncols: 3 nrows: 55 Column 1: id:CHARACTER:255 Column 2: shortname:CHARACTER:255 Column 3: longname:CHARACTER:255 GRASS 6.4.0 (spearfish60):~ > - truncates the data in joined columns I do not have a suggestion for an improved script - not up to that yet. The mysql db seems to be more strict as to the column variable length definition. As you point out, the definition is ignored and data carried over differently in sqlite and mysql. Hope that someone can take this up. Thanks, Jon On 13.10.2010, at 20:23, Micha Silver wrote: On 10/12/2010 12:01 PM, Jon Eiriksson wrote: Hi, I have a truncation problem with v.db.join. This has been raised before - ([GRASS-user] Re: grass v.db.join Gary Nobles Fri, 12 Mar 2010 11:11:26 -0800) - but I have not seen a solution. I have tried my own data, the spearfish60 example data, and the example in Neteler and Mitasova's book. The new data columns are apparently defined as 1 character long, and the data become truncated accordingly, much against my intention. I use mysql. I can see why this is happening. But I'm not sure what the correct solution might be. v.db.join is a wrapper around db.describe and v.db.addcol. When I query an sqlite database connection here's what the output looks like: db.describe -c bike_rides2 ncols: 8 nrows: 17 Column 1: cat:INTEGER:20 Column 2: name:CHARACTER:80 Column 3: number:INTEGER:20 Column 4: comment:CHARACTER:80 Column 5: descriptio:CHARACTER:80 Column 6: source:CHARACTER:80 Column 7: url:CHARACTER:80 The output format is obviously: "Column num:column name:column type:column size". One line from the v.db.join script uses grep and cut to get the column names and the next line gets the column types like so: db.describe -c bike_rides2 | grep '^Column' | cut -d ':' -f 3 INTEGER CHARACTER INTEGER CHARACTER CHARACTER CHARACTER CHARACTER So the column size is actually ignored. Next, in the script the above output is used by v.db.addcol to create the new columns in the joined vector. So all new character columns are created as a single char and the actual length is never used. Questions: Is the db.describe output the same for all db drivers? Any suggestions how to fix this as a script? Or better, just convert to python? Thanks, Micha -- Micha Silver Arava Development Co. +972-52-3665918 http://www.surfaces.co.il --- Jon Eiriksson, Research Professor, Earth Science Institute, University of Iceland, Askja, Sturlugata 7, IS-101 Reykjavik, Iceland. Tel +354 525 44 75 Fax +354 525 44 99 Email j...@hi.is Webhttp://www.raunvis.hi.is/~jeir/ --- ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] v.db.join script
On 10/12/2010 12:01 PM, Jon Eiriksson wrote: Hi, I have a truncation problem with v.db.join. This has been raised before - ([GRASS-user] Re: grass v.db.join Gary Nobles Fri, 12 Mar 2010 11:11:26 -0800) - but I have not seen a solution. I have tried my own data, the spearfish60 example data, and the example in Neteler and Mitasova's book. The new data columns are apparently defined as 1 character long, and the data become truncated accordingly, much against my intention. I use mysql. I can see why this is happening. But I'm not sure what the correct solution might be. v.db.join is a wrapper around db.describe and v.db.addcol. When I query an sqlite database connection here's what the output looks like: db.describe -c bike_rides2 ncols: 8 nrows: 17 Column 1: cat:INTEGER:20 Column 2: name:CHARACTER:80 Column 3: number:INTEGER:20 Column 4: comment:CHARACTER:80 Column 5: descriptio:CHARACTER:80 Column 6: source:CHARACTER:80 Column 7: url:CHARACTER:80 The output format is obviously: "Column num:column name:column type:column size". One line from the v.db.join script uses grep and cut to get the column names and the next line gets the column types like so: db.describe -c bike_rides2 | grep '^Column' | cut -d ':' -f 3 INTEGER CHARACTER INTEGER CHARACTER CHARACTER CHARACTER CHARACTER So the column size is actually ignored. Next, in the script the above output is used by v.db.addcol to create the new columns in the joined vector. So all new character columns are created as a single char and the actual length is never used. Questions: Is the db.describe output the same for all db drivers? Any suggestions how to fix this as a script? Or better, just convert to python? Thanks, Micha -- Micha Silver Arava Development Co. +972-52-3665918 http://www.surfaces.co.il ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
[GRASS-user] v.db.join script
Hi, I apologise for possible double-posting - but it seems that I did not send the email copied below to the correct address. If anyone has experienced the v.d.b.join column definition/truncation problem desacribed belowl and has some advice, I would be very grateful to hear about it. Best regards, Jon == Hi, I have a truncation problem with v.db.join. This has been raised before - ([GRASS-user] Re: grass v.db.join Gary Nobles Fri, 12 Mar 2010 11:11:26 -0800) - but I have not seen a solution. I have tried my own data, the spearfish60 example data, and the example in Neteler and Mitasova's book. The new data columns are apparently defined as 1 character long, and the data become truncated accordingly, much against my intention. I use mysql. Can anyone point out a solution? I copy a session with the spearfish60 data below. Jon GRASS 6.4.0 (spearfish60):~ > g.copy vect=soils,mysoils Copy vector to current mapset as GRASS 6.4.0 (spearfish60):~ > db.select mysoils cat|label 1|Aab 2|Ba 3|Bb 4|BcB 5|BcC 6|BeE . . . GRASS 6.4.0 (spearfish60):~ > db.in.ogr soils_legend.csv out=soils_legend Imported table with 55 rows GRASS 6.4.0 (spearfish60):~ > db.select soils_legend id|shortname|longname 0|no data|no data 1|AaB|Alice fine sandy loam, 0 to 6 2|Ba|Barnum silt loam 3|Bb|Barnum silt loam, channeled 4|BcB|Boneek silt loam, 2 to 6 5|BcC|Boneek silt loam, 6 to 9 6|BeE|Butche stony loam, 6 to 50 . . . GRASS 6.4.0 (spearfish60):~ > v.db.join mysoils col=label otable=soils_legend ocol=shortname GRASS 6.4.0 (spearfish60):~ > v.db.select mysoils cat|label|id|shortname|longname 1|Aab|1|A|A 2|Ba|2|B|B 3|Bb|3|B|B 4|BcB|4|B|B 5|BcC|5|B|B 6|BeE|6|B|B . . . GRASS 6.4.0 (spearfish60):~ > db.describe -c mysoils ncols: 5 nrows: 54 Column 1: cat:INTEGER:11 Column 2: label:CHARACTER:13 Column 3: id:CHARACTER:1 Column 4: shortname:CHARACTER:1 Column 5: longname:CHARACTER:1 GRASS 6.4.0 (spearfish60):~ > db.describe -c soils_legend ncols: 3 nrows: 55 Column 1: id:CHARACTER:255 Column 2: shortname:CHARACTER:255 Column 3: longname:CHARACTER:255 GRASS 6.4.0 (spearfish60):~ > == --- Jon Eiriksson, Research Professor, Earth Science Institute, University of Iceland, Askja, Sturlugata 7, IS-101 Reykjavik, Iceland. Tel +354 525 44 75 Fax +354 525 44 99 Email j...@hi.is Webhttp://www.raunvis.hi.is/~jeir/ --- ___ grass-user mailing list grass-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-user
