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

[ryagz]

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.

Thanks,
Ryagz





--
View this message in context: http://osgeo-org.1560.n6.nabble.com/PGRasterDataset-taking-too-much-time-to-Open-Dataset-tp5044549.html
Sent from the GDAL - Dev mailing list archive at Nabble.com.