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

[Markus Metz]

On Tue, May 8, 2018 at 4:01 PM, Nikos Alexandris <nik at nikosalexandris.net>
wrote:
>
> 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

you need to include v.external/v.in.ogr in your timing in order to get real
timings for creating a raster MASK from a vector stored in a remote
database.

as a general rule of thumb, processing becomes faster if you create a local
copy of the data to be processed, particularly if these data need to be
accessed repeatedly.
>
>
> 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?

you could check the network connection speed with some data transfer,
ideally by transferring ordinary files/directories

Markus M

>
> - Would anyone have any recommendations/considerations on this approach?
>
> Thank you, Nikos
>
> _______________________________________________
> grass-user mailing list
> grass-user at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/grass-user
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/grass-user/attachments/20180508/2a08247d/attachment.html>