GDAL OUT-DB RASTER SUPPORT

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

GDAL OUT-DB RASTER SUPPORT

Osahon Oduware
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: GDAL OUT-DB RASTER SUPPORT

Regina Obe-2

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:[hidden email]]
Sent: Friday, April 28, 2017 10:13 AM
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>; [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: GDAL OUT-DB RASTER SUPPORT

Osahon Oduware
Hi Regina,

I would try your suggestion. Thanks a lot for your help.

On Fri, Apr 28, 2017 at 4:07 PM, Regina Obe <[hidden email]> 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:[hidden email]]
Sent: Friday, April 28, 2017 10:13 AM
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>; [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: GDAL OUT-DB RASTER SUPPORT

Osahon Oduware
In reply to this post by Regina Obe-2
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 <[hidden email]> 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:[hidden email]]
Sent: Friday, April 28, 2017 10:13 AM
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>; [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: GDAL OUT-DB RASTER SUPPORT

Osahon Oduware
Hi Regina,

Your explanation has given me a better understanding of how things work and I greatly appreciate the effort you and your team have put into this. Thanks a lot for the information.

On Thu, May 4, 2017 at 5:48 AM, Regina Obe <[hidden email]> 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:[hidden email]]
Sent: Wednesday, May 03, 2017 12:02 PM
To: Regina Obe <[hidden email]>
Cc: PostGIS Users Discussion <[hidden email]>; Pierre Racine <[hidden email]>
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 <[hidden email]> 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:[hidden email]]
Sent: Friday, April 28, 2017 10:13 AM
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>; [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: GDAL OUT-DB RASTER SUPPORT

Osahon Oduware
In reply to this post by 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 <[hidden email]> 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:[hidden email]]
Sent: Wednesday, May 03, 2017 12:02 PM
To: Regina Obe <[hidden email]>
Cc: PostGIS Users Discussion <[hidden email]>; Pierre Racine <[hidden email]>
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 <[hidden email]> 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:[hidden email]]
Sent: Friday, April 28, 2017 10:13 AM
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>; [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: GDAL OUT-DB RASTER SUPPORT

Osahon Oduware
Hi Regina,

Thanks a lot for your help.

On Thu, May 4, 2017 at 1:59 PM, Regina Obe <[hidden email]> wrote:

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:[hidden email]]
Sent: Thursday, May 04, 2017 8:23 AM
To: Regina Obe <[hidden email]>
Cc: PostGIS Users Discussion <[hidden email]>; Pierre Racine <[hidden email]>; PostGIS Development Discussion <[hidden email]>


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 <[hidden email]> 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:[hidden email]]
Sent: Wednesday, May 03, 2017 12:02 PM
To: Regina Obe <[hidden email]>
Cc: PostGIS Users Discussion <[hidden email]>; Pierre Racine <[hidden email]>
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 <[hidden email]> 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:[hidden email]]
Sent: Friday, April 28, 2017 10:13 AM
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>; [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: GDAL OUT-DB RASTER SUPPORT

Osahon Oduware
In reply to this post by Osahon Oduware
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 <[hidden email]> 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:[hidden email]]
Sent: Wednesday, May 03, 2017 12:02 PM
To: Regina Obe <[hidden email]>
Cc: PostGIS Users Discussion <[hidden email]>; Pierre Racine <[hidden email]>
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 <[hidden email]> 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:[hidden email]]
Sent: Friday, April 28, 2017 10:13 AM
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>; [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users