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

[Jorge Arévalo]

Hello,

2009/7/10 Frank Warmerdam <warmerdam at pobox.com>:
> 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.

Ok, I'm working with this approach, actually.

>
>> 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.

If I understand, I should perform a spatial query to get the blocks
that intersect with the requested block. In case of regular_blocking,
only one matchs: the requested block itself. I think that's the
approach.


>
> 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.

Ok. Added to TODO list. And I have two small more questions:

- In the IReadBlock method of several RasterBands I saw CPLAssert(
nBlockXOff == 0 ); This ensure that you always read a band starting on
the most left block. I think that this is necessary with untiled
rasters. Am I right?

- What's the minimum set of methods of RasterBand that should be
implemented? IReadBlock, GDALColorInterp, GDALColorTable...

Thanks again
Best regards
Jorge