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

GRASS GRASS user
,
1

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, 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)

* 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 :wink:

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

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

Any comments / hints are greatly appreciated!

Best regards,
Wolfgang

2

Hey Wqual,

I am starting to work with PGSQL/POSTGIS with GRASS and learning as I
go. Welcome to the club.

First I would encourage you to check out the estensive documentation
online... This might be what you need... Here are the docs on how to
join tables

http://www.postgresql.org/docs/8.1/static/tutorial-join.html

How to do it in grass? Not sure, I'm learning the CLI to understand how
PG works. I'm no programer, I just learn the stuff I need to.

However, that said if GRASS can pass and exploit SQL commands then I am
sure this is possible. If it is not then getting some dicussion going on
the list is a great first step.

As for tutotirals, that's why I'm learning so I can create a hands on
tutorial... So perhaps we can get some people working together on this
using a common dataset.

Check out the http://grass.itc.it/community/sqlgrasslist.html for maybe
some more help. I have not ventured there yet so no idea if they can
help, but it makes sense.

Check out this SQL primer for grass 5.0
http://freegis.org/cgi-bin/viewcvs.cgi/~checkout~/grass/src.garden/grass
.postgresql/tutorial/index.html

As for front ends I have some experience with Pgadmin, but most books
from the library use the CLI so PGADMIN is on hold right now... But if
you are used to ORACLE/SDE combos it is verry similar, at least in the
way of layout and permissions.

Have not done becnh marking on file sizes and formats.

Hope some of this helps.

Cheers

-----Original Message-----
From: grassuser-bounces@grass.itc.it
[mailto:grassuser-bounces@grass.itc.it] On Behalf Of wqual
Sent: August 3, 2006 08:30
To: grassuser@grass.itc.it
Subject: [GRASS-user] Tutorial & help needed::join tables (which are
storedin postgres) and export result

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, 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)

* 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 :wink:

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

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

Any comments / hints are greatly appreciated!

Best regards,
Wolfgang

_______________________________________________
grassuser mailing list
grassuser@grass.itc.it
http://grass.itc.it/mailman/listinfo/grassuser

3

Quick reply, as I have not completely finished these documents --- but feel
free to browse them here:

http://casoilresource.lawr.ucdavis.edu/drupal/node/264

there are some sample postgis queries which illustrate the use of spatial
operations, attribute joins, and such.

I am hoping to have a working set of my notes on interacting with USDA-NCSS
soil survey (SSURGO and STATSGO) within PostGIS, GRASS, etc.

I'll post an update when I make some changes.

Cheers,

Dylan

On Thursday 03 August 2006 05:30, 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, 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)

* 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 :wink:

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

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

Any comments / hints are greatly appreciated!

Best regards,
Wolfgang

_______________________________________________
grassuser mailing list
grassuser@grass.itc.it
http://grass.itc.it/mailman/listinfo/grassuser

--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341

4

Is it possible for GRASS to Write to postgres/postgis... I seem to
remember something about read only and then you write to local grass.

Can you clarify?

Cheers

-----Original Message-----
From: grassuser-bounces@grass.itc.it
[mailto:grassuser-bounces@grass.itc.it] On Behalf Of Dylan Beaudette
Sent: August 3, 2006 14:03
To: grassuser@grass.itc.it
Cc: wqual
Subject: Re: [GRASS-user] Tutorial & help needed::join tables (which
arestored in postgres) and export result

Quick reply, as I have not completely finished these documents --- but
feel free to browse them here:

http://casoilresource.lawr.ucdavis.edu/drupal/node/264

there are some sample postgis queries which illustrate the use of
spatial operations, attribute joins, and such.

I am hoping to have a working set of my notes on interacting with
USDA-NCSS soil survey (SSURGO and STATSGO) within PostGIS, GRASS, etc.

I'll post an update when I make some changes.

Cheers,

Dylan

On Thursday 03 August 2006 05:30, 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, 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)

* 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 :wink:

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

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

Any comments / hints are greatly appreciated!

Best regards,
Wolfgang

_______________________________________________
grassuser mailing list
grassuser@grass.itc.it
http://grass.itc.it/mailman/listinfo/grassuser

--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group University of California at
Davis
530.754.7341

_______________________________________________
grassuser mailing list
grassuser@grass.itc.it
http://grass.itc.it/mailman/listinfo/grassuser

5

How about using OGR as the go-between:

v.out.ogr in=xxx dsn="PG ...."

ogr2ogr "PG ...." grass/location/mapset/vector/...

here is a prototype for exporting from postgis to shapefile, but obviously it
could go between any OGR supported format (i think)

ogr2ogr file.shp PG:"dbname=ssurgo_combined user=xxxx password=xxxx host=xxxx"
tablename

cheers,

Dylan

On Thursday 03 August 2006 11:08, Sampson, David wrote:

Is it possible for GRASS to Write to postgres/postgis... I seem to
remember something about read only and then you write to local grass.

Can you clarify?

Cheers

-----Original Message-----
From: grassuser-bounces@grass.itc.it
[mailto:grassuser-bounces@grass.itc.it] On Behalf Of Dylan Beaudette
Sent: August 3, 2006 14:03
To: grassuser@grass.itc.it
Cc: wqual
Subject: Re: [GRASS-user] Tutorial & help needed::join tables (which
arestored in postgres) and export result

Quick reply, as I have not completely finished these documents --- but
feel free to browse them here:

http://casoilresource.lawr.ucdavis.edu/drupal/node/264

there are some sample postgis queries which illustrate the use of
spatial operations, attribute joins, and such.

I am hoping to have a working set of my notes on interacting with
USDA-NCSS soil survey (SSURGO and STATSGO) within PostGIS, GRASS, etc.

I'll post an update when I make some changes.

Cheers,

Dylan

On Thursday 03 August 2006 05:30, 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, 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)
>
> * 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 :wink:
>
> * what about large datasets: shapefiles with *qix-files seem to be
> loaded faster compared the same theme in GRASS with its attributes in
> postgres
>
> * tutorials available for this pg-stuff inside of GRASS?
>
> Any comments / hints are greatly appreciated!
>
> Best regards,
> Wolfgang
>
> _______________________________________________
> grassuser mailing list
> grassuser@grass.itc.it
> http://grass.itc.it/mailman/listinfo/grassuser

--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group University of California at
Davis
530.754.7341

_______________________________________________
grassuser mailing list
grassuser@grass.itc.it
http://grass.itc.it/mailman/listinfo/grassuser

--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341

6

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 :wink:

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