[GRASS-user] simple way to join some attribute data w/o geometries

[Moritz Lennert]

On Tue, January 8, 2008 12:11, Markus Neteler wrote:
> The simple steps are
> - install SQLite (it does not need setup but works out of
>   the box)
> - set the DBMI settings with db.connect (see manual page
>   for copy-paste example)

add one step here: db.in.ogr

> - use v.db.join
> - use g.copy or db.copy to easily transform from DBF (or whatever
>   to SQLite). These two modules do all the work.

But I agree with Benjamin that while dbf is still the default data
backend, we should allow users to use it.

I don't think that rewriting v.db.join to include the capacity of joining
dbf files is that difficult (using a combination of v.db.select and
v.db.update instead of the sql join currently implemented).

So, I would probably go down the route of db.in.ogr + a rewrite of
v.db.join, which essentially means replacing the following line:

echo "UPDATE $maptable SET $col=(SELECT $col
        FROM $GIS_OPT_otable WHERE
$GIS_OPT_otable.$GIS_OPT_ocolumn=$maptable.$GIS_OPT_column);" |
db.execute
.

with something like (completely untested):

for key in `v.db.select $GIS_OPT_MAP col=$GIS_OPT_column | sort -n -u`
  do
     v.db.update $maptable col=$col value=`echo "SELECT $col
        FROM $GIS_OPT_otable WHERE $GIS_OPT_otable.$GIS_OPT_ocolumn=$key"
| db.select table=$GIS_OPT_otable database=$GIS_OPT_odatabase`
where="$maptable.$GIS_OPT_column=$key"
  done



where $GIS_OPT_odatabase = new command line parameter to introduce which
would allow to join two tables in two different databases.
If the key could be a string column, the sort -n is not correct, so you
probably have to cater to different possibilities (or program a 'select
distinct()' for the dbf driver).
You probably have to check for multiple lines being returned by the SELECT
statement if the new table has multiple entries per key, or you just do
this with a sort statement.

Moritz





Moritz