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

[Benjamin Ducke]

Thanks all for your suggestions. This is getting to be
a very fruitful discussion.

I would agree with the proposed enhancements to v.db.join.
This seems straight-forward and coherent with GRASS usage
GIVEN THAT there is a DBF file as a departure point.

In many cases, however, users will simply start with a
CSV text file and converting that to DBF can be non-trivial
once the file exceeds 64K lines or so and the spreadsheet
application chokes on it.

So how about also extending v.in.ascii slightly to accept
input lines without coordinate pairs and create a plain
DBF table with user-defined field formats?

It should also not be too hard to add an option that
would guess the correct formats for the user.

In that case, pulling in attribute data from a text file
would amount to using v.in.ascii and v.db.join only, which
I think should not give anyone too many headaches.

Would that make any sense?

Benjamin


mlennert at geog-pc40.ulb.ac.be wrote:
> 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
> 
> 
> 

-- 
Benjamin Ducke, M.A.
Archäoinformatik
(Archaeoinformation Science)
Institut für Ur- und Frühgeschichte
(Inst. of Prehistoric and Historic Archaeology)
Christian-Albrechts-Universität zu Kiel
Johanna-Mestorf-Straße 2-6
D 24098 Kiel
Germany

Tel.: ++49 (0)431 880-3378 / -3379
Fax : ++49 (0)431 880-7300
www.uni-kiel.de/ufg