[GRASS-user] Clarifying use of postgres/postgis

rshepard · August 14, 2020, 2:41pm

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.

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?

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.

A pointer to other docs would be great.

Regards,

Rich

Micha_Silver1 · August 15, 2020, 10:15am

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@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

rshepard · August 15, 2020, 12:50pm

On Sat, 15 Aug 2020, Micha Silver wrote:

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.

Micha,

Thanks for clarifying; I must have mis-understood what I read. I assumed the
geometry was kept by GRASS and didn't know why PostGIS was mentioned ... and
I don't recall just where I read all this.

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?

Ah, so. I don't.

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.

No, I want the attribute data in postgres so I need to learn to make that
the default.

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

My reason is keeping these data in the same format as other project data.

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.

Yes, I've been using SQlite as long as I have PostgreSQL.

Thanks,

Rich

dheerajchand · August 15, 2020, 1:22pm

‘’’

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.
‘’’

How would one configure that? Please assume that I am unfamiliar and uncomfortable with GRASS when explaining, but able to read Java and Python man pages with ease, also comfortable with PSQL and PostGIS.
Would we get all the speed benefits of PSQL, would it be used for computations whenever possible?
Is there a way to easily move from GRASS and PSQL geometry and topology models when doing this?

Sent from my iPhone

On Aug 15, 2020, at 7:50 AM, Rich Shepard rshepard@appl-ecosys.com wrote:

On Sat, 15 Aug 2020, Micha Silver wrote:

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.

Micha,

Thanks for clarifying; I must have mis-understood what I read. I assumed the
geometry was kept by GRASS and didn’t know why PostGIS was mentioned … and
I don’t recall just where I read all this.

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?

Ah, so. I don’t.

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.

No, I want the attribute data in postgres so I need to learn to make that
the default.

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

to allow fancy SQL queries on the database tables

huge, complex data tables or triggers

multiuser access to the attribute tables

My reason is keeping these data in the same format as other project data.

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.

Yes, I’ve been using SQlite as long as I have PostgreSQL.

Thanks,

Rich

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user

Micha_Silver1 · August 15, 2020, 2:10pm

···

On 15/08/2020 16:22, Dheeraj Chand wrote:

‘’’

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.
‘’’

How would one configure that? Please assume that I am unfamiliar and uncomfortable with GRASS when explaining, but able to read Java and Python man pages with ease, also comfortable with PSQL and PostGIS.

In that case you might want to first go thru some tutorials on using GRASS. We’re here to help if encounter anything that is unclear, or not working as you expected.

All GRASS commands can be called thru the GRASS-python bindings, so that might be easiest for you. But do go into the beginning tutorials first.

The GRASS module that sets the backend database connection is db.connect. Have a look at the man page:

https://grass.osgeo.org/grass78/manuals/db.connect.html

You would choose the driver parameter as “pg”, then set the database and schema as required. This comes after running db.login one time to save your DB auth credentials.

Would we get all the speed benefits of PSQL, would it be used for computations whenever possible?

Not sure how to answer here. GRASS in general sends SQL commands back to the DB backend for any undates. If, for example, you had a point vector of cities, with two columns “population” and “number_hospitals” in the attribute table, and you wanted to calculate number of hospitals per 1000 people, then you would construct a regular SQL query that would be sent to the backend. So I guess that the speed would be determined by PostgreSQL.

Is there a way to easily move from GRASS and PSQL geometry and topology models when doing this?

Sorry to repeat again: GRASS maintains all geometry (and topology) within its own internal vector data structure. NO PostGIS involved here. But You could easily export GRASS vectors to a PostGIS database using the v.out.postgis module.

https://grass.osgeo.org/grass78/manuals/v.out.postgis.html

To pull a PostGIS vector table into GRASS would require either v.in.ogr or v.import

Sent from my iPhone

On Aug 15, 2020, at 7:50 AM, Rich Shepard rshepard@appl-ecosys.com wrote:

On Sat, 15 Aug 2020, Micha Silver wrote:

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.

Micha,

Thanks for clarifying; I must have mis-understood what I read. I assumed the
geometry was kept by GRASS and didn’t know why PostGIS was mentioned … and
I don’t recall just where I read all this.

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?

Ah, so. I don’t.

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.

No, I want the attribute data in postgres so I need to learn to make that
the default.

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

to allow fancy SQL queries on the database tables

huge, complex data tables or triggers

multiuser access to the attribute tables

My reason is keeping these data in the same format as other project data.

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.

Yes, I’ve been using SQlite as long as I have PostgreSQL.

Thanks,

Rich

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user
_______________________________________________
grass-user mailing list
[grass-user@lists.osgeo.org](mailto:grass-user@lists.osgeo.org)
[https://lists.osgeo.org/mailman/listinfo/grass-user](https://lists.osgeo.org/mailman/listinfo/grass-user)

-- 
Micha Silver
Ben Gurion Univ.
Sde Boker, Remote Sensing Lab
cell: +972-523-665918
[https://orcid.org/0000-0002-1128-1325](https://orcid.org/0000-0002-1128-1325)

dheerajchand · August 16, 2020, 3:02am

I think that maybe my request was unclear, sorry.

I am okay and comfortable with GRASS on how to use it, but not on the man pages about how to edit the source code to patch/change it.

What I was really hoping for was to avoid having two spatial databases, one PostGIS and one GRASS, with the idea that GRASS would store everything in PostGIS with perhaps its only specific column for its own geometries.

I guess not!

···

On 15/08/2020 16:22, Dheeraj Chand wrote:

‘’’

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.
‘’’

How would one configure that? Please assume that I am unfamiliar and uncomfortable with GRASS when explaining, but able to read Java and Python man pages with ease, also comfortable with PSQL and PostGIS.

In that case you might want to first go thru some tutorials on using GRASS. We’re here to help if encounter anything that is unclear, or not working as you expected.
All GRASS commands can be called thru the GRASS-python bindings, so that might be easiest for you. But do go into the beginning tutorials first.
The GRASS module that sets the backend database connection is db.connect. Have a look at the man page:
https://grass.osgeo.org/grass78/manuals/db.connect.html
You would choose the driver parameter as “pg”, then set the database and schema as required. This comes after running db.login one time to save your DB auth credentials.

Would we get all the speed benefits of PSQL, would it be used for computations whenever possible?

Not sure how to answer here. GRASS in general sends SQL commands back to the DB backend for any undates. If, for example, you had a point vector of cities, with two columns “population” and “number_hospitals” in the attribute table, and you wanted to calculate number of hospitals per 1000 people, then you would construct a regular SQL query that would be sent to the backend. So I guess that the speed would be determined by PostgreSQL.

Is there a way to easily move from GRASS and PSQL geometry and topology models when doing this?

Sorry to repeat again: GRASS maintains all geometry (and topology) within its own internal vector data structure. NO PostGIS involved here. But You could easily export GRASS vectors to a PostGIS database using the v.out.postgis module.
https://grass.osgeo.org/grass78/manuals/v.out.postgis.html

To pull a PostGIS vector table into GRASS would require either v.in.ogr or v.import

Sent from my iPhone

On Aug 15, 2020, at 7:50 AM, Rich Shepard rshepard@appl-ecosys.com wrote:

On Sat, 15 Aug 2020, Micha Silver wrote:

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.

Micha,

Thanks for clarifying; I must have mis-understood what I read. I assumed the
geometry was kept by GRASS and didn’t know why PostGIS was mentioned … and
I don’t recall just where I read all this.

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?

Ah, so. I don’t.

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.

No, I want the attribute data in postgres so I need to learn to make that
the default.

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

to allow fancy SQL queries on the database tables

huge, complex data tables or triggers

multiuser access to the attribute tables

My reason is keeping these data in the same format as other project data.

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.

Yes, I’ve been using SQlite as long as I have PostgreSQL.

Thanks,

Rich

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user
_______________________________________________
grass-user mailing list
[grass-user@lists.osgeo.org](mailto:grass-user@lists.osgeo.org)
[https://lists.osgeo.org/mailman/listinfo/grass-user](https://lists.osgeo.org/mailman/listinfo/grass-user)

-- 
Micha Silver
Ben Gurion Univ.
Sde Boker, Remote Sensing Lab
cell: +972-523-665918
[https://orcid.org/0000-0002-1128-1325](https://orcid.org/0000-0002-1128-1325)

Blumentrath_Stefan1 · August 16, 2020, 8:36pm

Hi Dheeraj,

GRASS uses a special topological vector format, that – as Micha pointed out – requires for most operations that data are stored in native GRASS format and not PostGIS.

However, for points for example external vector data (e.g. in PostGIS) can be used in GRASS in those modules directly that support topology level 1. Here one would use v.external to link PostGIS data to GRASS.

g.search.modules keyword=level1

gives you a list of those modules.

A typical use case for such a setup (vector in PostGIS, raster in GRASS) I am familiar with are GPS tracking data in PostGIS that are matched with GRASS GIS raster data…

If you use PostGIS as the database backend in GRASS (instead of default sqlite), then you would have almost the setup you are looking for, just that GRASS does not add geometry columns but stores the geometry in the GRASS DB (and attributes in PG).

Cheers

Stefan

···

From: grass-user grass-user-bounces@lists.osgeo.org On Behalf Of Dheeraj Chand
Sent: søndag 16. august 2020 05:03
To: Micha Silver tsvibar@gmail.com
Cc: grass-user@lists.osgeo.org
Subject: Re: [GRASS-user] Clarifying use of postgres/postgis

I think that maybe my request was unclear, sorry.

I am okay and comfortable with GRASS on how to use it, but not on the man pages about how to edit the source code to patch/change it.

What I was really hoping for was to avoid having two spatial databases, one PostGIS and one GRASS, with the idea that GRASS would store everything in PostGIS with perhaps its only specific column for its own geometries.

I guess not!

On Aug 15, 2020, at 9:10 AM, Micha Silver <tsvibar@gmail.com> wrote:

On 15/08/2020 16:22, Dheeraj Chand wrote:

‘’’

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.

‘’’

How would one configure that? Please assume that I am unfamiliar and uncomfortable with GRASS when explaining, but able to read Java and Python man pages with ease, also comfortable with PSQL and PostGIS.

In that case you might want to first go thru some tutorials on using GRASS. We’re here to help if encounter anything that is unclear, or not working as you expected.

All GRASS commands can be called thru the GRASS-python bindings, so that might be easiest for you. But do go into the beginning tutorials first.

The GRASS module that sets the backend database connection is db.connect. Have a look at the man page:

https://grass.osgeo.org/grass78/manuals/db.connect.html

You would choose the driver parameter as “pg”, then set the database and schema as required. This comes after running db.login one time to save your DB auth credentials.

Would we get all the speed benefits of PSQL, would it be used for computations whenever possible?

Not sure how to answer here. GRASS in general sends SQL commands back to the DB backend for any undates. If, for example, you had a point vector of cities, with two columns “population” and “number_hospitals” in the attribute table, and you wanted to calculate number of hospitals per 1000 people, then you would construct a regular SQL query that would be sent to the backend. So I guess that the speed would be determined by PostgreSQL.

Is there a way to easily move from GRASS and PSQL geometry and topology models when doing this?

Sorry to repeat again: GRASS maintains all geometry (and topology) within its own internal vector data structure. NO PostGIS involved here. But You could easily export GRASS vectors to a PostGIS database using the v.out.postgis module.

https://grass.osgeo.org/grass78/manuals/v.out.postgis.html

To pull a PostGIS vector table into GRASS would require either v.in.ogr or v.import
Sent from my iPhone

On Aug 15, 2020, at 7:50 AM, Rich Shepard rshepard@appl-ecosys.com wrote:

On Sat, 15 Aug 2020, Micha Silver wrote:

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.

Micha,

Thanks for clarifying; I must have mis-understood what I read. I assumed the
geometry was kept by GRASS and didn’t know why PostGIS was mentioned … and
I don’t recall just where I read all this.

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?

Ah, so. I don’t.

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.

No, I want the attribute data in postgres so I need to learn to make that
the default.

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

to allow fancy SQL queries on the database tables

huge, complex data tables or triggers

multiuser access to the attribute tables

My reason is keeping these data in the same format as other project data.

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.

Yes, I’ve been using SQlite as long as I have PostgreSQL.

Thanks,

Rich

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user
_______________________________________________
grass-user mailing list
[grass-user@lists.osgeo.org](mailto:grass-user@lists.osgeo.org)
[https://lists.osgeo.org/mailman/listinfo/grass-user](https://lists.osgeo.org/mailman/listinfo/grass-user)
-- 
Micha Silver
Ben Gurion Univ.
Sde Boker, Remote Sensing Lab
cell: +972-523-665918
[https://orcid.org/0000-0002-1128-1325](https://orcid.org/0000-0002-1128-1325)

neteler · August 17, 2020, 4:06pm

On Sat, Aug 15, 2020 at 2:50 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Sat, 15 Aug 2020, Micha Silver wrote:

> 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.

Micha,

Thanks for clarifying; I must have mis-understood what I read. I assumed the
geometry was kept by GRASS and didn't know why PostGIS was mentioned ... and
I don't recall just where I read all this.

Maybe here?

https://grasswiki.osgeo.org/wiki/PostGIS#Direct_access_to_PostGIS_data_.28GRASS_7_only.29
Direct access to PostGIS data (GRASS 7 only)

Markus

rshepard · August 17, 2020, 4:13pm

On Mon, 17 Aug 2020, Markus Neteler wrote:

Maybe here?

https://grasswiki.osgeo.org/wiki/PostGIS#Direct_access_to_PostGIS_data_.28GRASS_7_only.29
Direct access to PostGIS data (GRASS 7 only)

Markus,

Perhaps. I don't recall.

Thanks,

Rich