[GRASS-user] PostgreSQL raster and vector import

patrick_s1 · February 3, 2016, 2:48pm

Dear list

I need some help on importing PostGIS raster with r.in.gdal and schema support under v.in.ogr.

RASTER:
"r.in.gdal -f" shows support: "PostGISRaster (rw): PostGIS Raster driver"
But I did not find any example on how to formulate the dsn/input.

VECTOR:
Unfortunately there is no hint in the manual of grass70 on how to work with schemas (there was one in 64).
I had a script in grass64 of following syntax, which is still working:

ISCHEMA=baselayer
v.in.ogr dsn="PG:dbname=psql_local active_schema=my_dataschema" layer=gws_buildings --overwrite

However, I would like to form this more general as a variable for all incoming data, which is not working:

INPATH="PG:dbname=psql_local active_schema=my_dataschema"
v.in.ogr in=$INPATH out=bldg_pt layer=gws_buildings --overwrite
=>ERROR: v.in.ogr: Sorry, <active_schema> is not a valid parameter

Also the approach described in the manual of grass64 does not work any longer

db.connect driver=pg database=psql_local schema=my_dataschema
v.in.ogr in=./ out=bldg_pt layer=gws_buildings --overwrite
=>ERROR: Unable to open data source <./>

Thanks for any help,
Patrick

patrick_s1 · February 4, 2016, 9:44am

Dear list

In addition to my mail yesterday (see below):

The use of PostGIS might be related to the conversation from octobre last year (http://osgeo-org.1560.x6.nabble.com/External-raster-from-PostGIS-td5231932.html), but it remains unclear to me how to import raster and is not referred to in the manual.

My current state seems close to the solution:
r.in.gdal in="PG:dbname=psql_local schema=myschema table=reference_map" out=region_ref -o --o #load into grass
r.external source="PG:dbname=psql_local schema=myschema table=reference_map" out=region_ref --o -o #link to grass

But both lead to "ERROR: North must be larger than South"

However, I can view the data in QGIS (loading though db-manager) and it appears to be ok.

Is Error it a problem of gdal, my data or the grass-modules? And how can I solve it?

Thanks for any help
Patrick

On 03.02.2016 15:48, patrick s. wrote:

Dear list

I need some help on importing PostGIS raster with r.in.gdal and schema support under v.in.ogr.

RASTER:
"r.in.gdal -f" shows support: "PostGISRaster (rw): PostGIS Raster driver"
But I did not find any example on how to formulate the dsn/input.

VECTOR:
Unfortunately there is no hint in the manual of grass70 on how to work with schemas (there was one in 64).
I had a script in grass64 of following syntax, which is still working:

ISCHEMA=baselayer
v.in.ogr dsn="PG:dbname=psql_local active_schema=my_dataschema" layer=gws_buildings --overwrite

However, I would like to form this more general as a variable for all incoming data, which is not working:

INPATH="PG:dbname=psql_local active_schema=my_dataschema"
v.in.ogr in=$INPATH out=bldg_pt layer=gws_buildings --overwrite
=>ERROR: v.in.ogr: Sorry, <active_schema> is not a valid parameter

Also the approach described in the manual of grass64 does not work any longer

db.connect driver=pg database=psql_local schema=my_dataschema
v.in.ogr in=./ out=bldg_pt layer=gws_buildings --overwrite
=>ERROR: Unable to open data source <./>

Thanks for any help,
Patrick

patrick_s1 · February 4, 2016, 10:11am

One finding concerning the variable-based pathdefinition to PostgreSQL with schema (thanks to a collegue).
Maybe of interest for other users.

My problems of Raster-import remains open...

Cheers,Patrick

#works
DBPATH="PG:dbname=psql_local"
v.in.ogr input=$DBPATH out=bldg_pt layer=${ISCHEMA}.gws_buildings --overwrite

#does not work
INPATH="PG:dbname=psql_local active_schema=baselayer_2000"
v.in.ogr input=$INPATH out=bldg_pt layer=gws_buildings --overwrite

#work
INPATH="PG:dbname=psql_local active_schema=$ISCHEMA"
v.in.ogr "input=$INPATH" out=bldg_pt layer=gws_buildings --overwrite #NOTE: "in" needs to be quoted or it will not work

On 04.02.2016 10:44, patrick s. wrote:

Dear list

In addition to my mail yesterday (see below):

The use of PostGIS might be related to the conversation from octobre last year (http://osgeo-org.1560.x6.nabble.com/External-raster-from-PostGIS-td5231932.html), but it remains unclear to me how to import raster and is not referred to in the manual.

My current state seems close to the solution:
r.in.gdal in="PG:dbname=psql_local schema=myschema table=reference_map" out=region_ref -o --o #load into grass
r.external source="PG:dbname=psql_local schema=myschema table=reference_map" out=region_ref --o -o #link to grass

But both lead to "ERROR: North must be larger than South"

However, I can view the data in QGIS (loading though db-manager) and it appears to be ok.

Is Error it a problem of gdal, my data or the grass-modules? And how can I solve it?

Thanks for any help
Patrick

On 03.02.2016 15:48, patrick s. wrote:

Dear list

I need some help on importing PostGIS raster with r.in.gdal and schema support under v.in.ogr.

RASTER:
"r.in.gdal -f" shows support: "PostGISRaster (rw): PostGIS Raster driver"
But I did not find any example on how to formulate the dsn/input.

VECTOR:
Unfortunately there is no hint in the manual of grass70 on how to work with schemas (there was one in 64).
I had a script in grass64 of following syntax, which is still working:

ISCHEMA=baselayer
v.in.ogr dsn="PG:dbname=psql_local active_schema=my_dataschema" layer=gws_buildings --overwrite

However, I would like to form this more general as a variable for all incoming data, which is not working:

INPATH="PG:dbname=psql_local active_schema=my_dataschema"
v.in.ogr in=$INPATH out=bldg_pt layer=gws_buildings --overwrite
=>ERROR: v.in.ogr: Sorry, <active_schema> is not a valid parameter

Also the approach described in the manual of grass64 does not work any longer

db.connect driver=pg database=psql_local schema=my_dataschema
v.in.ogr in=./ out=bldg_pt layer=gws_buildings --overwrite
=>ERROR: Unable to open data source <./>

Thanks for any help,
Patrick

Blumentrath_Stefan1 · February 9, 2016, 8:10pm

Hi Patrick,

Now I tested your approach and I can confirm your issue. It is likely a GRASS issue, I guess, as:
gdalinfo "PG:dbname=psql_local schema=myschema table=reference_map"
returns reasonable results.

You are probably hit by this: https://trac.osgeo.org/grass/ticket/798 as a PG raster consists of (many) subdatasets, which you can see in gdalinfo output...

You could try:
gdalbuildvrt ./reference_map.vrt "PG:dbname=psql_local schema=myschema table=reference_map"
r.in.gdal input=./reference_map.vrt output=region_ref -o --o
as GDAL VRTs are only "meta"-datasets and usually produced very fast.

However, if I were you, I would really reconsider my storage / workflow approach, as having the data in PG most likely does not yield in satisfying performance. Building a VRT from a reasonable sized raster dataset in PG takes ages, while it is built within a few seconds from a GeoTIFF or GRASS dataset.
Having it the other way around, linking a GRASS raster to PG (out-db storage) might be more appropriate. That would require building GDAL with GRASS support and PG against that GDAL version, so the driver becomes available for PG...

Or if you have to make the raster available over the internet, consider WCS...

Anyway, I hope you manage to achieve what you want!

Cheers
Stefan

-----Original Message-----
From: grass-user [mailto:grass-user-bounces@lists.osgeo.org] On Behalf Of patrick s.
Sent: 4. februar 2016 10:45
To: GRASS user list <grass-user@lists.osgeo.org>
Subject: Re: [GRASS-user] PostgreSQL raster and vector import

Dear list

In addition to my mail yesterday (see below):

The use of PostGIS might be related to the conversation from octobre last year (http://osgeo-org.1560.x6.nabble.com/External-raster-from-PostGIS-td5231932.html),
but it remains unclear to me how to import raster and is not referred to in the manual.

My current state seems close to the solution:
r.in.gdal in="PG:dbname=psql_local schema=myschema table=reference_map"
out=region_ref -o --o #load into grass
r.external source="PG:dbname=psql_local schema=myschema table=reference_map" out=region_ref --o -o #link to grass

But both lead to "ERROR: North must be larger than South"

However, I can view the data in QGIS (loading though db-manager) and it appears to be ok.

Is Error it a problem of gdal, my data or the grass-modules? And how can I solve it?

Thanks for any help
Patrick

On 03.02.2016 15:48, patrick s. wrote:

Dear list

I need some help on importing PostGIS raster with r.in.gdal and schema
support under v.in.ogr.

RASTER:
"r.in.gdal -f" shows support: "PostGISRaster (rw): PostGIS Raster driver"
But I did not find any example on how to formulate the dsn/input.

VECTOR:
Unfortunately there is no hint in the manual of grass70 on how to work
with schemas (there was one in 64).
I had a script in grass64 of following syntax, which is still working:

ISCHEMA=baselayer
v.in.ogr dsn="PG:dbname=psql_local active_schema=my_dataschema"
layer=gws_buildings --overwrite

However, I would like to form this more general as a variable for all
incoming data, which is not working:

INPATH="PG:dbname=psql_local active_schema=my_dataschema"
v.in.ogr in=$INPATH out=bldg_pt layer=gws_buildings --overwrite
=>ERROR: v.in.ogr: Sorry, <active_schema> is not a valid parameter

Also the approach described in the manual of grass64 does not work any
longer

db.connect driver=pg database=psql_local schema=my_dataschema v.in.ogr
in=./ out=bldg_pt layer=gws_buildings --overwrite
=>ERROR: Unable to open data source <./>

Thanks for any help,
Patrick

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

patrick_s1 · February 10, 2016, 5:01pm

Thanks for the confirmation, Stefan.

How can r.external be used for PostGIS than- if this concerns all PG-Rasters?

Concerning the workflow: good hint to use VRT and also to have WCT. Will need to check that.

My reason to use PG is to have a datastorage which can be connected to multiple software, without having to run these from within GRASS (R, QGIS, ArcGIS, own PostgreSQL functions, bash-scripts,....). Unfortunately GRASS-format is not supported by all software and PG has multiple additional features of interest (User control, Indexing, Stability, Comments, Schemas, Views,.....). Last not least I have some WebGIS-applications that make use of PG-views and allow a visualisation of different contents through simply changing the content of a view, i.e. the link to data.

Currently I load from db to GRASS, run all processes inside GRASS which depend on the GRASS-functions and save my data to db at the end. Works ok for vectors, even if I need transform them to topology for each process (v.external does not support of all functions to my knowledge and was very slow in the past). However my rasterdata needs to be stored in local folders and loaded from there which works fine, but appears to be a bit inconsistent with the rest of the workflow. So I thought it might be time to make use of the new PostGIS-RASTERS.

Happy on any comment. And sorry of this description is a bit confused.

Patrick

On 09.02.2016 21:10, Blumentrath, Stefan wrote:

Hi Patrick,

Now I tested your approach and I can confirm your issue. It is likely a GRASS issue, I guess, as:
gdalinfo "PG:dbname=psql_local schema=myschema table=reference_map"
returns reasonable results.

You are probably hit by this: https://trac.osgeo.org/grass/ticket/798 as a PG raster consists of (many) subdatasets, which you can see in gdalinfo output...

You could try:
gdalbuildvrt ./reference_map.vrt "PG:dbname=psql_local schema=myschema table=reference_map"
r.in.gdal input=./reference_map.vrt output=region_ref -o --o
as GDAL VRTs are only "meta"-datasets and usually produced very fast.

However, if I were you, I would really reconsider my storage / workflow approach, as having the data in PG most likely does not yield in satisfying performance. Building a VRT from a reasonable sized raster dataset in PG takes ages, while it is built within a few seconds from a GeoTIFF or GRASS dataset.
Having it the other way around, linking a GRASS raster to PG (out-db storage) might be more appropriate. That would require building GDAL with GRASS support and PG against that GDAL version, so the driver becomes available for PG...

Or if you have to make the raster available over the internet, consider WCS...

Anyway, I hope you manage to achieve what you want!

Cheers
Stefan

-----Original Message-----
From: grass-user [mailto:grass-user-bounces@lists.osgeo.org] On Behalf Of patrick s.
Sent: 4. februar 2016 10:45
To: GRASS user list <grass-user@lists.osgeo.org>
Subject: Re: [GRASS-user] PostgreSQL raster and vector import

Dear list

In addition to my mail yesterday (see below):

The use of PostGIS might be related to the conversation from octobre last year (http://osgeo-org.1560.x6.nabble.com/External-raster-from-PostGIS-td5231932.html),
but it remains unclear to me how to import raster and is not referred to in the manual.

My current state seems close to the solution:
r.in.gdal in="PG:dbname=psql_local schema=myschema table=reference_map"
out=region_ref -o --o #load into grass
r.external source="PG:dbname=psql_local schema=myschema table=reference_map" out=region_ref --o -o #link to grass

But both lead to "ERROR: North must be larger than South"

However, I can view the data in QGIS (loading though db-manager) and it appears to be ok.

Is Error it a problem of gdal, my data or the grass-modules? And how can I solve it?

Thanks for any help
Patrick

On 03.02.2016 15:48, patrick s. wrote:

Dear list

I need some help on importing PostGIS raster with r.in.gdal and schema
support under v.in.ogr.

RASTER:
"r.in.gdal -f" shows support: "PostGISRaster (rw): PostGIS Raster driver"
But I did not find any example on how to formulate the dsn/input.

VECTOR:
Unfortunately there is no hint in the manual of grass70 on how to work
with schemas (there was one in 64).
I had a script in grass64 of following syntax, which is still working:

ISCHEMA=baselayer
v.in.ogr dsn="PG:dbname=psql_local active_schema=my_dataschema"
layer=gws_buildings --overwrite

However, I would like to form this more general as a variable for all
incoming data, which is not working:

INPATH="PG:dbname=psql_local active_schema=my_dataschema"
v.in.ogr in=$INPATH out=bldg_pt layer=gws_buildings --overwrite
=>ERROR: v.in.ogr: Sorry, <active_schema> is not a valid parameter

Also the approach described in the manual of grass64 does not work any
longer

db.connect driver=pg database=psql_local schema=my_dataschema v.in.ogr
in=./ out=bldg_pt layer=gws_buildings --overwrite
=>ERROR: Unable to open data source <./>

Thanks for any help,
Patrick

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