[GRASSLIST:9455] Re: merging db tables

[Markus Neteler]

On Tue, Dec 13, 2005 at 03:46:12PM -0600, Kirk R. Wythers wrote:
> I have a text file that I want to "merge" with an existing db table  
> (there is a common column in both the vector table and the ascii  
> file). 

This is a table "join", right?

> Is there some kind of merge command that would work within  
> grass, or should I export the vector, merge, then reimport the merged  
> vector with v.in.ascii?
> 
> Any suggestions would be much appreciated.

You can do that directly in the database (not supported
by DBF driver). So postgreql, sqlite and mysql (maybe the latter
interface doesn't work) support such joins.

For experiments, here you can download the spearfish
soils map legend as sqlite file:

 http://mpa.itc.it/grasstutor/examples_menu.phtml
 -> Spearfish soils legend: 
     [TXT with explanations]
     [CSV legend table]
     [SQL file legend table]
     [SQLite DB only legend table]
   x [SQLite DB Spearfish soils + Legend tables]

Here how to do the join of the long soil names into the soil
map:

g.copy vect=soils,spearfish_soils

#Legend of 'soils' map imported via CSV from:
#  http://mpa.itc.it/grasstutor/examples.phtml
#  -> soils_legend.csv
#using the 'sqlitebrowser'.

GRASS 6.1.cvs (spearfish60):~ > db.connect -p
driver:sqlite
database:$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db
schema:(null)
group:(null)

GRASS 6.1.cvs (spearfish60):~ > db.tables -p
spearfish_soils
soils_legend
test

GRASS 6.1.cvs (spearfish60):~ > g.list vect
----------------------------------------------
Vorhandene vector Dateien im Mapset sqlite:
  myfields  mypoints3D  spearfish_soils  test
Vorhandene vector Dateien im Mapset PERMANENT:
...

# Check columns in legend table:
db.describe -c soils_legend

# Check columns in map table:
v.info -c spearfish_soils
v.db.select spearfish_soils

# open sqlite database, quit then:
sqlitebrowser `v.db.connect -g spearfish_soils | cut -d' ' -f4`

# Now join the legend long names into "soils" map table:

sqlite3 `v.db.connect -g spearfish_soils | cut -d' ' -f4`
sqlite> .schema

# try first before updating. We do LEFT OUTER JOIN to
# keep rows where a longname is missing:
sqlite> SELECT spearfish_soils.cat, spearfish_soils.label, soils_legend.longname
        FROM spearfish_soils LEFT OUTER JOIN soils_legend
        ON soils_legend.shortname=spearfish_soils.label;
1|Aab|
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
...
53|WaA|Weber loam, 0 to 2
54|Wb|Winetti cobbly loam
56||

# ... looks reasonable. Now update table of soils map:
# The query is much more simple as UPDATE only updates
# existing values:
sqlite> UPDATE spearfish_soils SET longname=(SELECT longname
        FROM soils_legend WHERE soils_legend.shortname=spearfish_soils.label);

sqlite> SELECT * FROM spearfish_soils;
1|Aab|
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
...
# works

sqlite> .quit

# verification
v.db.select spearfish_soils


cheers

 markus