[GRASS-user] Clarifying use of postgres/postgis

[Micha Silver]

Hi Rich


On 8/14/20 5:41 PM, Rich Shepard wrote:
> I'm starting a large project and want all data (geometric and 
> attributes) to
> be stored in a new postgres database I just created: washington_state. 
> Both
> postgresql-12.2 and postgis-3.0.1 are installed.
>
To be clear from the start, GRASS *always* stores geometry in its own 
database structure. The attributes can be stored in a number of ways: 
back in GRASS 6.x days, attrib tables were kept in the old dbf format. 
Since 7.x, sqlite is the default file format for attrib tables. You can 
alternatively choose to save your attribute tables to PostgreSQL 
(db.connect...). But again, don't confuse - this is NOT PostGIS, and 
GRASS does not need/use PostGIS for geometry. GRASS geometry is always 
independent of any external geospatial format.



> All my previous work has used the default GRASS database so I've read the
> manual page, PostgreSQL DATABASE DRIVER, and I'm still unsure how to
> proceed. I'm the only postgres user so I access all my databases without
> entering a username or password.
>
> When I start a grass session, create a new location, and mapset then 
> want to
> import .shp or .gdb files how do I specify using the postgres/postgis
> database I just created? When do I use PostGIS with shp2pgsql?


It's the other way around: When you export GRASS map layers, you can, as 
you know, choose to save out to several formats: shp, Geopackage (the 
current default) or to PostGIS. PostGIS is the best choice when you need 
multiuser access to the geospatial data. But you point out that you're 
the only user, so why would you need the overhead of PostGIS?


When importing a shapefile or other vector data, only the attrib tables 
get saved to some database: sqlite by default, or PostgrSQL if you have 
configured for that backend. But the geometry is still kept in the GRASS 
vector format.


>
> Since I've used the db.* modules before I'm familiar with them. What I 
> need
> to learn is how to expand my grass experiences to using post* as the
> databases rather than the defaults.


To repeat, you can set the default for saving attribute tables to 
PostgreSQL, but do not try to save a GRASS layer to PostGIS in the same 
database! That will definitely lead to trouble. If you want/need a 
PostGIS instance for some reason independent of GRASS, then keep it 
totally separated from GRASS. i.e. at least in a separate schema or even 
separate database.


The main reasons for choosing PostgreSQL as your database backend would be

 1. to allow fancy SQL queries on the database tables
 2. huge, complex data tables or triggers
 3. multiuser access to the attribute tables

But keep in mind that the default sqlite database is quite powerful, and 
you would have to look very deeply to find a PostgreSQL feature that is 
missing in sqlite.


Best regards, Micha


>
> A pointer to other docs would be great.
>
> Regards,
>
> Rich
> _______________________________________________
> grass-user mailing list
> grass-user at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/grass-user

-- 
Micha Silver
Ben Gurion Univ.
Sde Boker, Remote Sensing Lab
cell: +972-523-665918