[GRASS-user] PostgreSQL raster and vector import

[patrick s.]

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 at lists.osgeo.org] On Behalf Of patrick s.
> Sent: 4. februar 2016 10:45
> To: GRASS user list <grass-user at 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 at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/grass-user