[postgis-users] GDAL OUT-DB RASTER SUPPORT

[Osahon Oduware]

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> 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]
> *Sent:* Wednesday, May 03, 2017 12:02 PM
> *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>
> *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> 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]
> *Sent:* Friday, April 28, 2017 10:13 AM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Cc:* Regina Obe <lr at pcorp.us>; 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/8ee65617/attachment.html>