[GRASS-user] v.db.join script

[Jón Eiríksson]

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 <soils at 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   jeir at hi.is
>> Web    http://www.raunvis.hi.is/~jeir/
>> -------------------------------------------------------------------
>>
>>
>>
>>
>> _______________________________________________
>> grass-user mailing list
>> grass-user at 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
>
>
>