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.
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 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@hi.is
Web http://www.raunvis.hi.is/~jeir/
-------------------------------------------------------------------
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.
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.
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 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@hi.is
Web http://www.raunvis.hi.is/~jeir/
-------------------------------------------------------------------
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 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@hi.is
Web http://www.raunvis.hi.is/~jeir/
-------------------------------------------------------------------
On Fri, Oct 15, 2010 at 2:08 PM, Micha Silver <micha@arava.co.il> 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).