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:[email protected]]
Sent: Thursday, May 04, 2017 8:23 AM
To: Regina Obe <[email protected]>
Cc: PostGIS Users Discussion <[email protected]>; Pierre Racine
<[email protected]>; PostGIS Development Discussion
<[email protected]>
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 <[email protected]
<mailto:[email protected]> > 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:[email protected] <mailto:[email protected]>
]
Sent: Wednesday, May 03, 2017 12:02 PM
To: Regina Obe <[email protected] <mailto:[email protected]> >
Cc: PostGIS Users Discussion <[email protected]
<mailto:[email protected]> >; Pierre Racine
<[email protected] <mailto:[email protected]> >
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 <[email protected]
<mailto:[email protected]> > 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:[email protected] <mailto:[email protected]>
]
Sent: Friday, April 28, 2017 10:13 AM
To: PostGIS Users Discussion <[email protected]
<mailto:[email protected]> >
Cc: Regina Obe <[email protected] <mailto:[email protected]> >;
[email protected] <mailto:[email protected]>
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?
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
