[gdal-dev] PGRasterDataset taking too much time to Open Dataset

[Even Rouault]

Selon ryagz <raghavendra at vizexperts.com>:

> Hi,
>
> I am having a postgis raster table which has raster data about 40 GB, when i
> try to load the dataset its taking too much time (about 40 mins) in the
> function "GDALDataset* PostGISRasterDataset::Open(GDALOpenInfo* poOpenInfo)
> " .
>
> When i digged into the code i found out that in the function "GBool
> PostGISRasterDataset::SetRasterProperties(const char *
> pszValidConnectionString)"
> there is an sql query issued on the database to get raster properties
>
>     if (pszWhere == NULL) {
>         osCommand.Printf(
>             "select srid, nbband, st_xmin(geom) as xmin, st_xmax(geom) as
> xmax, "
>             "st_ymin(geom) as ymin, st_ymax(geom) as ymax from (select
> st_srid(%s) srid, "
>             "st_extent(%s::geometry) geom, max(ST_NumBands(rast)) nbband
> from %s.%s "
>             "group by st_srid(%s)) foo", pszColumn, pszColumn, pszSchema,
>             pszTable, pszColumn);
>     }
>
>     else {
>         osCommand.Printf(
>             "select srid, nbband, st_xmin(geom) as xmin, st_xmax(geom) as
> xmax, "
>             "st_ymin(geom) as ymin, st_ymax(geom) as ymax from (select
> st_srid(%s) srid, "
>             "st_extent(%s::geometry) geom, max(ST_NumBands(rast)) nbband
> from %s.%s "
>             "where %s group by st_srid(%s)) foo", pszColumn, pszColumn,
> pszSchema,
>             pszTable, pszWhere, pszColumn);
>     }
>
> This sql query is causing all the delay because its going to scan the entire
> table and get the raster properties. One idea what i would like to propose
> is if the user has already applied the function  AddRasterConstraints on the
> raster table then all the raster properties which are required will be
> readily available in raster_columns table which is present in public schema.
>
> So for the cases where RasterConstraints is already applied we could have
> the sql query some thing like this:
>
>     if (pszWhere == NULL) {
>         osCommand.Printf(
>             "select srid, nbband, st_xmin(geom) as xmin, st_xmax(geom) as
> xmax, "
>             "st_ymin(geom) as ymin, st_ymax(geom) as ymax from (select srid
> srid, "
>             "extent geom, num_bands nbband from raster_columns where
> r_table_schema = '%s' "
>             "and r_table_name = '%s') foo", pszSchema, pszTable);
>     }
>
>     else {
>
>         osCommand.Printf(
>             "select srid, nbband, st_xmin(geom) as xmin, st_xmax(geom) as
> xmax, "
>             "st_ymin(geom) as ymin, st_ymax(geom) as ymax from (select srid
> srid, "
>             "extent geom, num_bands nbband from raster_columns where
> r_table_schema = '%s' "
>             "and r_table_name = '%s' where %s) foo", pszSchema, pszTable,
> pszWhere);
>     }
>
> I have tested the above mentioned sql query with the same table of 40GB
> size, the Dataset open speed has drastically improved, Now Dataset Open
> happens in couple of seconds.
>
> Please let me know if this is better way to do the required operation. If
> so, is it possible to add it in GDAL tree and add to GDAL 1.10 release.

Could you check that it isn't already a known issue in the Trac database. I can
see a few PostgisRaster tickets in
http://trac.osgeo.org/gdal/report/1?asc=0&sort=ticket , but at first sight, I'm
not sure if your issue really intersects one of those. If not, please file a
ticket with all the above details and add "jorgearevalo" in CC. I'm not sure if
it will be fixed for 1.10 however.