[postgis-users] GDAL OUT-DB RASTER SUPPORT

Thu May 4 05:59:00 PDT 2017

I experimented with that and I don't think it helps that much so I abandoned that idea.

Reason being I think the meta data check it does on the raster because it's missing the information in raster_columns is very expensive out-weights the spatial index benefit.

Only way which I haven't tried yet but might work is to replace the  raster_columns view with an entry that is pretty much a copy of your original table entry. 

I suppose you can experiment with that 

1)      Renaming your raster_columns view to something like zz_raster_columns

2)      SELECT * INTO raster_columns FROM zz_raster_columns;

Updating the entry for your view with all the meta data it needs

3)      For the index, you'd need to look at the ST_Resize function and change it from STABLE to IMMUTABLE

I would instead create a new function which is wrapper around the core function used by ST_Resize so  it doesn't get replaced during upgrade.  Something like

CREATE OR REPLACE FUNCTION UPGIS_Materialize(rast raster) RETURNS raster AS

$$

BEGIN

RETURN _ST_gdalwarp(

                                                $1,

                                                'NearestNeighbour', 0.125,

                                                NULL,

                                                NULL, NULL,

                                                NULL, NULL,

                                                NULL, NULL,

                                                ST_Width($1), ST_Height($1)

                                );

END;

$$

language plpgsql IMMUTABLE strict COST 1000;

Anyway I haven't had a chance to test this out so wouldn't be surprised if you run into errors.

Thanks,

Regina

From: Osahon Oduware [mailto:osahon.gis at gmail.com] 
Sent: Thursday, May 04, 2017 8:23 AM
To: Regina Obe <lr at pcorp.us>
Cc: PostGIS Users Discussion <postgis-users at lists.osgeo.org>; Pierre Racine <pierre.racine at sbf.ulaval.ca>; PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
Subject: Re: GDAL OUT-DB RASTER SUPPORT

Hi Regina,

When I tried to create the index using the syntax you provided:

CREATE INDEX idx_outdb_table_mat_rast  ON outdb_table USING gist(st_convexhull(ST_Resize (rast,0.8,0.8)) );

I get the following error:

ERROR:  functions in index expression must be marked IMMUTABLE

How do I resolve this error?

On Thu, May 4, 2017 at 5:48 AM, Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

Osahon,

I have some ideas, but nothing I can suggest that is pluggable without changing PostGIS and GDAL.

The reason why it's slow is a couple

1)      ST_Resize is generally slower than reading cause it's doing some stuff to the raster.  The new function I am thinking of would do nothing aside from reading in the bytes of the raster.

2)      When you use a view, the data in raster_columns view for the view is incomplete, doesn't have the meta data that GDAL relies on, so it then tries to compute it with a query which since the rast column is a black box to it, also requires rerunning ST_Resize or whatever by querying the view which compounds the slowness.

To remedy this issue in 2.4, I'm planning to have a side line table like raster_column_additional so users can manually enter things like views with the right params and then the raster_columns view would use this information instead if of recomputing if there is an entry in raster_column_additional.

Plan to throw in some helper functions to auto-compute for views.

This part isn't needed for making out-db readable and speedily readable, but would solve some other issues people have complained about.

3)      Since the ST_Resize view uses  a function and GDAL just sees the view as another table, you'd need a spatial index on the functional bits something like:

CREATE INDEX idx_outdb_table_mat_rast  ON outdb_table USING gist(st_convexhull(ST_Resize (rast,0.8,0.8)) );

This wouldn't be necessary at all if we revised GDAL library.

4)       for it to be able to use it seamlessly, unless you did as I mentioned, limit your view to just a small area.

The fix I had in mind for this as I mentioned in another email is to create a wrapper function (that is not as expensive as ST_Resize that will return a non-outdb raster), 

and revise GDAL so the query will still use the raw out-db raster for computing the meta data it needs and also using the spatial index on the out-db so no additional index is needed and no additional cost of running the function is incurred.

All the above I think should make using out-db raster as transparent and fast as in-db and viewable in QGIS.

HINT HINT – If any raster folks are interested in funding this kind of work, let me know.  We will then be able to prioritize it for PostGIS 2.4 and next GDAL release.

Thanks,

Regina

From: Osahon Oduware [mailto:osahon.gis at gmail.com <mailto:osahon.gis at gmail.com> ] 
Sent: Wednesday, May 03, 2017 12:02 PM
To: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >
Cc: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >; Pierre Racine <pierre.racine at sbf.ulaval.ca <mailto:pierre.racine at sbf.ulaval.ca> >
Subject: Re: GDAL OUT-DB RASTER SUPPORT

Hi Regina,

I followed the process you described to create a view for the out-db rasters and dragged it to the map. I was able to view the out-db rasters this way. However, it takes very long before the raster is displayed on the map (in QGIS) as compared to the time taken to display an in-db raster.

Is there anything that can be done to improve the speed?

NB: indexes were created with the -I flag of the raster2pgsql tool

On Fri, Apr 28, 2017 at 4:07 PM, Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

Osahon,

It depends what you are trying to show and do.

1)      For example if you create overviews on your out-db, those will show, so then it would only be when you zoom in that you hit the out-db that you'd get black.

2)      If you are looking to inspect a slice of out-db with QGIS, you can create a view that has just the piece you want to show and drag it to your map.

You'd need to apply a function that forces that piece into in-db.  Not all raster functions do that and sadly we haven't been good about documenting ones that do vs. ones that don't.

I know for sure that ST_Resize would force a materialized in-db and that might actually be a good one to use anyway since it would lower the res allowing you to see more.

So something like

-- do this with db manager or pgAdmin

CREATE OR REPLACE VIEW vw_outdb_sample AS

SELECT rid, ST_Resize(rast,0.8, 0.8) AS rast

FROM some_outdb_table

WHERE ST_Intersects(rast,some_geometry);

Then drag this view to your Map.

Hope that helps,

Regina

From: Osahon Oduware [mailto:osahon.gis at gmail.com <mailto:osahon.gis at gmail.com> ] 
Sent: Friday, April 28, 2017 10:13 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Cc: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >; pierre.racine at sbf.ulaval.ca <mailto:pierre.racine at sbf.ulaval.ca> 
Subject: GDAL OUT-DB RASTER SUPPORT

Hi All,

I am getting a black image when I try to display PostGIS out-db rasters. I discovered that it has to do with GDAL support for out-db rasters as stated in the link below for a similar issue (over 1 year back):
https://hub.qgis.org/issues/12133

The last comment by Jürgen Fischer states "... only black images. So it's a dependency issue (GDAL complains ERROR 1: This raster has outdb storage. This feature isn't still available)."

I have also been told in the link below that "out-db support is not implemented yet in the GDAL PostGISRaster driver.":
https://lists.osgeo.org/pipermail/gdal-dev/2017-April/046512.html

I would like to know how to solve this issue of displaying postgis out-db rasters in QGIS?

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170504/d2284c79/attachment.html>