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-tp5044548.html Sent from the GDAL - Dev mailing list archive at Nabble.com. _______________________________________________ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev