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 <so...@permanent> to current mapset as <mysoils> >> 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 <soils_legend> 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 [email protected] >> Web http://www.raunvis.hi.is/~jeir/ >> ------------------------------------------------------------------- >> >> >> >> >> _______________________________________________ >> grass-user mailing list >> [email protected] >> 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 [email protected] http://lists.osgeo.org/mailman/listinfo/grass-user
