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
#!/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 in GRASS GIS to run this program." >&2
exit 1
fi
# save command line
if [ "$1" != "@ARGS_PARSED@" ] ; then
CMDLINE="`basename $0`"
for arg in "$@" ; do
CMDLINE="$CMDLINE \"$arg\""
done
export CMDLINE
exec g.parser "$0" "$@"
fi
PROG=`basename $0`
driver=`v.db.connect -g map="$GIS_OPT_MAP" fs=";" | grep "^$GIS_OPT_LAYER" |
cut -d';' -f5`
database=`v.db.connect -g map="$GIS_OPT_MAP" fs=";" | grep "^$GIS_OPT_LAYER" |
cut -d';' -f4`
if [ "$driver" = "dbf" ] ; then
g.message -e "JOIN is not supported for tables stored in DBF format."
exit 1
fi
maptable=`v.db.connect -g map="$GIS_OPT_MAP" fs=";" | grep "^$GIS_OPT_LAYER" |
cut -d';' -f2`
if [ -z "$maptable" ] ; then
g.message 'There is no table connected to this map! Cannot join any column.'
### cleanup
exit 1
fi
v.info --quiet -c "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" | \
cut -d'|' -f1,2 | grep "|${GIS_OPT_COLUMN}$" 2>&1 >/dev/null
if [ $? -ne 0 ] ; then
g.message -e "Column <$GIS_OPT_COLUMN> not found in table <$GIS_OPT_MAP> at
layer $GIS_OPT_LAYER"
exit 1
fi
# we use map DBMI settings
COLLIST=`db.describe -c driver="$driver" database="$database"
table="$GIS_OPT_OTABLE" | grep '^Column ' | cut -d':' -f2`
# heck, types may have white space
COLTYPES=`db.describe -c driver="$driver" database="$database"
table="$GIS_OPT_OTABLE" | grep '^Column ' | cut -d':' -f3 | tr -s ' ' '_'`
###########################
#### Addition by Micha ####
# Also grab column widths
COLWIDTHS=`db.describe -c driver="$driver" database="$database"
table="$GIS_OPT_OTABLE" | grep '^Column ' | cut -d':' -f4`
#### End ####
###########################
i=1
for col in $COLLIST ; do
###########################
#### Addition by Micha ###
# if the column is a CHAR type, use the COLWIDTH value to create a VARCHAR
column
COLTYPE=`echo $COLTYPES | cut -d' ' -f$i | tr -s '_' ' '`
COLWIDTH=`echo $COLWIDTHS | cut -d' ' -f$i`
if [ "$COLTYPE" = "CHARACTER" ]; then
v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col
VARCHAR($COLWIDTH)"
else
v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col $COLTYPE"
fi
# v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col `echo $COLTYPES
| cut -d' ' -f$i | tr -s '_' ' '`"
#### End ####
###########################
if [ $? -ne 0 ] ; then
g.message -e "Cannot continue."
exit 1
fi
echo "UPDATE $maptable SET $col=(SELECT $col
FROM $GIS_OPT_OTABLE WHERE
$GIS_OPT_OTABLE.$GIS_OPT_OCOLUMN=$maptable.$GIS_OPT_COLUMN);" | db.execute
database="${database}" driver=${driver}
i=`expr $i + 1`
done
# write cmd history:
v.support "${GIS_OPT_MAP}" cmdhist="${CMDLINE}"
exit 0
_______________________________________________
grass-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/grass-user
