'Re: [postgis-users] GDAL OUT-DB RASTER SUPPORT'

[prev in list] [next in list] [prev in thread] [next in thread] 

List: postgis-users
Subject: Re: [postgis-users] GDAL OUT-DB RASTER SUPPORT
From: Osahon Oduware <osahon.gis () gmail ! com>
Date: 2017-05-11 10:28:54
Message-ID: CAGpiUSi1bdj+JPjZdX5rbeJ5egXr8fc=3tv9Db1oZifBjBqH4w () mail ! gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

Hi Regina,

Regarding funding as mentioned in your mail below, I spoke with the C.E.O
of the GIS firm for whom I am undergoing this research and *he would like
to know how much funding would be required for these functionalities to be
implemented*.

The firm already have these functionalities working on ORACLE (spatial)
database, but I am undergoing this research so that a decision can be made
for a possible switch to PostgreSQL/PostGIS for future projects. A lot
depends on these functionalities, especially the Out-DB rasters being
viewed on QGIS with similar performance (in terms of speed) as currently
being achieved with ORACLE/GeoMedia. The PostgreSQL/PostGIS performance
with Vector data has been satisfactory so far.

I look forward to hearing from you.

On Thu, May 4, 2017 at 5:48 AM, Regina Obe <lr@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@gmail.com]
> *Sent:* Wednesday, May 03, 2017 12:02 PM
> *To:* Regina Obe <lr@pcorp.us>
> *Cc:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>; Pierre
> Racine <pierre.racine@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@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@gmail.com]
> *Sent:* Friday, April 28, 2017 10:13 AM
> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> *Cc:* Regina Obe <lr@pcorp.us>; pierre.racine@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?
>
>
>

[Attachment #5 (text/html)]

<div dir="ltr">Hi Regina,<div \
style="font-size:12.8px"> </div><div style="font-size:12.8px">Regarding funding as \
mentioned in your mail below, I spoke with the C.E.O of the GIS firm for whom I am \
undergoing this research and he would like to know how much funding would be \
required for these functionalities to be implemented.</div><div \
style="font-size:12.8px"> </div><div style="font-size:12.8px">The firm already \
have these functionalities working on ORACLE (spatial) database, but I am undergoing \
this research so that a decision can be made for a possible switch to \
PostgreSQL/PostGIS for future projects. A lot depends on these functionalities, \
especially the Out-DB rasters being viewed on QGIS with similar performance (in terms \
of speed) as currently being achieved with ORACLE/GeoMedia. The PostgreSQL/PostGIS \
performance with Vector data has been satisfactory so far.</div><div \
style="font-size:12.8px"> </div><div style="font-size:12.8px">I look forward to \
hearing from you. </div><div class="gmail_extra"> <div class="gmail_quote">On \
Thu, May 4, 2017 at 5:48 AM, Regina Obe &lt;<a \
href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>&gt; \
wrote: <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"><div lang="EN-US" link="blue" vlink="purple"><div \
class="m_7431731818429970095WordSection1">Osahon, \
I \
have some ideas, but nothing I can suggest that is pluggable without changing PostGIS \
and GDAL. \
The \
reason why it&#39;s slow is a couple \
1) \
ST_Resize \
is generally slower than reading cause it&#39;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&#39;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&#39;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&#39;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&#39;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&#39;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,ReginaFrom: Osahon Oduware \
[mailto:<a href="mailto:osahon.gis@gmail.com" \
target="_blank">osahon.gis@gmail.com</a>] Sent: Wednesday, May 03, 2017 \
12:02 PM To: Regina Obe &lt;<a href="mailto:lr@pcorp.us" \
target="_blank">lr@pcorp.us</a>&gt; Cc: PostGIS Users Discussion &lt;<a \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>&gt;; Pierre Racine &lt;<a \
href="mailto:pierre.racine@sbf.ulaval.ca" \
target="_blank">pierre.racine@sbf.ulaval.ca</a>&gt; Subject: Re: GDAL \
OUT-DB RASTER SUPPORT<div><div class="h5"> <div>Hi Regina,<div> </div><div>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.</div><div> </div><div>Is there anything that can be done to improve the \
speed?</div><div> </div><div> </div><div>NB: indexes were created with the -I flag of the \
raster2pgsql tool</div></div><div> <div>On Fri, Apr 28, 2017 at 4:07 PM, Regina Obe &lt;<a \
href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>&gt; \
wrote:<blockquote style="border:none;border-left:solid #cccccc \
1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in"><div><div>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&#39;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&#39;d \
need to apply a function that forces that piece into in-db. Not all raster \
functions do that and sadly we haven&#39;t been good about documenting ones that do \
vs. ones that don&#39;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 \

[Attachment #6 (text/plain)]

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 