[GRASS-user] To import from or to link to data stored in a PostgreSQL data base?

[Stefan Blumentrath]

Hei Nikos,
What about using gdal_rasterize and then r.external output=MASK?

Cheers
Stefan
________________________________
From: grass-user <grass-user-bounces at lists.osgeo.org> on behalf of Nikos Alexandris <nik at nikosalexandris.net>
Sent: Tuesday, May 8, 2018 4:01:14 PM
To: GRASS-GIS user mailing list
Cc: Giacomo.DELLI at ext.ec.europa.eu
Subject: [GRASS-user] To import from or to link to data stored in a PostgreSQL data base?

Dears,

the following concerns an update of an existing workflow, part of which
is GRASS GIS, that makes use of a large PostgreSQL data base which does
not reside locally.


The original data set consists of tens of thousands of (overlapping)
polygons. The data are required solely to build raster MASKs.
So, importing the whole of it, is an overkill. Instead, options, already
working, are to split all records in single tables or views. Then access
these via GRASS to perform some analytics.


First instructions of the workflow are:

- read a (external) vector map
- set the computational region
- build a raster mask.


Building a MASK using a pseudo vector map that links to an
external table, stored in a PostgreSQL data base, is times slower than
importing the vetor of interest in GRASS GIS and then building a MASK
using the "native" GRASS GIS vector map.


Giacomo timed different options, using `v.external` as well as importing
the data using `v.in.ogr`. Specifically,

- building a MASK using one pseudo vector map (without and with a
  spatial-index), takes about 9 minutes (real time).

time r.mask vector=test_nogeoindex --o

real 8m40.306s
user 5m14.225s
sys 0m56.378s

and

time r.mask vector=test_geoindex --o

real 8m46.096s
user 5m15.693s
sys 0m56.346s

- building a MASK using a native GRASS GIS vector map, imported via a
  table or a view, takes about 0.4 seconds.

real 0m0.373s
user 0m0.191s
sys 0m0.111s

and

real 0m0.350s
user 0m0.179s
sys 0m0.115s


For the latter, building a view is way faster than a table (half a
minute for more than 20000 views, while it would take approximately an
hour to build single tables).

The trade-off appears to be space vs time. If data are imported, more
disk space is required. If data are not imported, and `v.external` is
used, then `r.mask` takes too much time to build a raster MASK.

- Is it acceptable for `r.mask` to take so long in building a MASK based
  on an external vector map stored in a PostgreSQL data base?

- Is network connection a limiting factor here, since the PG data base
  is not local?

- Would anyone have any recommendations/considerations on this approach?

Thank you, Nikos
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/grass-user/attachments/20180508/e31b2fbe/attachment.html>