[GRASS-user] Tutorial & help needed::join tables (which are stored in postgres) and export result

[Moritz Lennert]

wqual wrote:
> Dear list,
> I am still testing, what kind of database I should use for my work with 
> GRASS/QGIS. As my experiences with sqlite3 where not that positive (large 
> datasets took very much time to be read into sqlite), I started working with 
> postgres (and my first impression is that it's far more faster). dbf is 
> quick, but does not offer me the possibility to perform "table-join", which I 
> need every day. 
> I am not that experienced with pg, therefore, I would like to ask you folks 
> some questions about "daily GIS work with postgres in GRASS":
> 
> * How can I join pg-tables in GRASS, 

You don't have to do the work in GRASS (although you can use db.execute 
to send any SQL-query you like to your database backend. If you prefer 
to work with a graphical then you are free to do so.

BTW I find it easiest to just use views, instead of new tables, joining 
two existing tables. That way you reduce the number of actual tables to 
a strict minimum. And as a side-effect you don't erase a table when you 
erase the map.

You can then use v.db.connect to connect the map to any table or view 
you wish, as long as it has a column containing the category codes of 
the map.

Just watch out: when you erase a map, the table it is linked to is also 
erased.


> can I export a joined theme to make it 
>> permanent (for example, I would like to create a shapefile out of it which 
>> contains all information of the join)

When you use v.out.ogr to export a GRASS map to shape, it will create 
the corresponding dbf file on the basis of the table/view linked to the 
map. So, yes, you can.

> 
> * Does anybody has experience with pg-frontends like knoda and pg_admin? 
> Working with tables on the command-line is hard for people who used *other 
> programmes* before ;)

Sorry, whenever I tried, I found going back to psql much faster...

> 
> * what about large datasets: shapefiles with *qix-files seem to be loaded 
> faster compared the same theme in GRASS with its attributes in postgres

What do you mean by 'loaded' ? How are you loading the shapefiles ?

> 
> * tutorials available for this pg-stuff inside of GRASS?

Intros:
http://grass.itc.it/grass61/manuals/html61_user/databaseintro.html
http://grass.itc.it/grass61/manuals/html61_user/sql.html

Tutorial:
http://www.gdf-hannover.de/lit_html/grass60_v1.2_en/node49.html

Moritz