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

[Jorge Arévalo]

Hello,

2009/7/10 Tamas Szekeres <szekerest at gmail.com>:
> Jorge,
>
> I'm a bit hesitant to think using a server cursor would be the best approach
> here. Could you explain why is this needed?

Yes. As now I'm working basically with regularly tiled coverages, an
operation like SELECT rast FROM table gives me all the tiles. These
tiles don't overlap, and its size can be the size of a block read for
IReadBlock method. So, if I have a cursor, I can fetch the tile that
the IRasterBlock needs as one of the rows of the resultset without
having all the tiles loaded in memory at same time.

Ok, this isn't a generic approach. I should perform a spatial query
for rows intersecting the bounding box of the requested block, as
Frank said. In case of regularly tiled coverage, the result of this
query will be one row. And maybe it's faster to perform only one query
and fetch all the rows from a cursor, instead of a lot of spatial
queries for this particular case.

Maybe is it a bad approach?


> I my understanding each IReadBlock would trigger a normal "SELECT" without
> the cursor, this will give you all the data at once, just like FETCH ALL.
> This select would in fact be a spatial select according to the extent of the
> block required by the client.
>
Ok, correct me if I'm wrong, please: Each IReadBlock will query for a
part of the raster. As we can set the band block size when creating
the Dataset (passing parameters to constructor), we can force the
blocks to be the same size of our tiles (rows). So, we have to
transform the 2D coordinate system of IReadBlock (nXBlockOff,
nYBlockOff) to the 1D system of result set. Then, if we call
IReadBlock(0, 0, buffer), this will be the first row of the resultset.
IReadBlock(0,1, buffer) will be the second row, and so on. We simply
have to read the matching band of each row (tile) in each IReadBlock
petition. I think that, in this particular case, it's faster than a
spatial query.

If we accept that, the question is:
- Should I fetch the data in the Dataset (FETCH ALL) and simply get
each row by calling PQgetvalue over the resultset?
- Should I fetch the data only in IReadBlock method, getting the row I need?

Am I talking nosense? Could be a good approach?

Many thanks

Best regards
Jorge


> Best regards,
>
> Tamas
>
>
>
>
> 2009/7/10 Jorge Arévalo <jorge.arevalo at gmail.com>
>>
>> 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?
>> 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
>>
>> Thanks in advance.
>>
>> Best regards
>> Jorge
>> _______________________________________________
>> gdal-dev mailing list
>> gdal-dev at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>
>