[gdal-dev] Questions about SQL cursors in GDAL driver

[Frank Warmerdam]

Jorge Arévalo wrote:
> Hello,
> 
> Context: GDAL WKTRaster Driver. When creating Dataset, I declare a
> cursor to read all the rows (tiles) of a table with a raster column
> (DECLARE cursor CURSOR FOR SELECT * FROM TABLE). The RasterBand should
> read one of the rows' band
> 
> Question 1: The cursor is created during the Dataset creation, and
> stored in it. Should I start a transaction before? If yes, when should
> I close the cursor and end the transaction? When calling to Dataset's
> destructor?

Jorge,

Ideally you would issue the query when the resultset is first needed
which would presumably be when the first IReadBlock() call is made.
This would keep down overhead for lightweight open's (ie. for gdalinfo)
that don't really need the raster data.

Presumably the cursor would be closed in the dataset descructor.

> Question 2: What approach is better?
>      a) The Dataset fetchs all the rows (tiles) of the table just
> after declaring the cursor, and the RasterBand moves over the
> resultset using PQgetvalue and read one band of the read raster

>      b) The Dataset declare the cursor and the RasterBand fetchs the
> row that needs to read one of its bands

Hmm, thinking slightly deeper here, for the regular_blocking
case I believe you should be issuing a new query for each
IReadBlock() call.  I see this as the only way to ensure you
only read the rows/blocks that are requested by the application.

In particular, I am imagining IReadBlock() would issue a spatial
query for rows intersecting the bounding box of the requested
block for which there should only be one that matches.  I am
assuming there is no other way to get a particular block other
than a spatial query.

You will also want to use the trick used in the JPEG driver where
when you read a block in your RasterBand::IReadBlock() method that
you should shove the unneeded bands for that block into the block
cache to avoid having to refetch them from the database when the
other bands are finally requested.  You can treat this as an
optimization to do after you have the basics working.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent