[postgis-users] Postgis raster : storage performance ?

[Duncan Golicher]

Hello Laurent,

The -R option does not import the raster values themselves. Instead it
sets up references to the file within the database. This means that
you must run the import using the absolute path and that you must keep
the original file in the same place in your file system. No
compression is involved and the storage space needed = the original
file size (outside the database, so not part of the cluster) + the
metadata stored for each tile. The latter can increase the size of the
database notably if tile size is small for a large raster.

select pg_database_size()

to find the size of the database.

If you look at the size after a full import (leaving out the -R option
this time) you can compare the added storage size in the data base
with the raw file. Postgresql uses LZ compression, which can reduce
storage space depending on the original format of the file. So if you
are just looking to save disk space this could be useful. However it
can (surprisingly) take longer to access the information using PostGIS
queries involving rasters imported into the data base than from those
left outside and just registered within it.

So, as Tim pointed out, the real advantage of importing the rasters
(either held within or left outside the database) is to allow you to
use the full power of SQL to conduct efficient operations involving
complex raster/vector overlays on large rasters. Spatial indexing can
make operations more efficient computationally than they would be
using a desktop GIS, providing you find an appropriate tile size when
importing that allows the spatial index to cut down the number of
calculations needed. However using PostGIS will not usually speed up
simple point on raster overlays, as compared to the equivalent
operation run on a desktop GIS. It just makes extracting information
from rasters more convenient, assuming that you are using a spatial
database in order to take advantage of its other features.

I hope that helps,

Duncan




On Fri, Jan 11, 2013 at 10:07 AM, laurent
<lcelati at latitude-geosystems.com> wrote:
> Hello,
>
> 1/ Is there a real advantage to import raster into postgis db in storage
> purposes? Can you give me an indicator evaluating  the gain of file size
> compared to raw raster files?
>
> 2/ Is there a postgis raster tool for compressing raster cover in order to
> compress the size of the dbs?
>
> 3/ Could you explain to me the -R (register) postgis optional parameter ?
> What is the interest of this parameter?
>
>
> Thank you to throw light for me?
>
> Kind regards.
>
> ~IMAGE~
>
>
>
>
> --
> View this message in context: http://postgis.17.n6.nabble.com/Postgis-raster-storage-performance-tp5002222.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



-- 
Dr Duncan Golicher
Investigador Titular,
El Colegio de la Frontera Sur, Chiapas,Mexico
Mexico tel +52 1 967 137 94 20
Skype name duncangolicher

Publications: http://www.mendeley.com/profiles/duncan-golicher

Senior lecturer, Bournemouth University, UK
Centre for Conservation Ecology & Environmental Change
School of Applied Sciences
Christchurch House rm C218a
Bournemouth University
Fern Barrow
Poole (Dorset) BH12 5BB UK
Tel. +44 (0)1202 961682

For list of publications see Researcher ID:
http://www.researcherid.com/rid/B-4240-2009

dgolicher at bournemouth.ac.uk
dgoliche at ecosur.mx

Researcher ID:
http://www.researcherid.com/rid/B-4240-2009