[GRASS-user] v.db.join script

Fri Oct 15 08:08:24 EDT 2010

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

-------------- next part --------------
#!/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