[mapserver-users] logging GDAL queries

[Lucena, Ivan]

Sebastian,

I have this little piece of code in Python that shows how 18 levels of pyramids would look like:

rows = 271423
cols = 190325
bxs = 256
bys = 256
t = 0
for i in range(0,18,1):
     c = int( round( cols / 2 ** i ) )
     r = int( round( rows / 2 ** i ) )
     if ( c <= ( bxs / 2 ) ) and ( r <= ( bys / 2 ) ):
         w = c
         h = r
     else:
         w = bxs
         h = bys
     bc = int( max(1, c / w ) )
     br = int( max(1, r / h ) )
     t += bc * br
     print i, c, r, bc, br, t

And here is the output:

0 190325 271423 743 1060 787580
1 95162 135711 371 530 984210
2 47581 67855 185 265 1033235
3 23790 33927 92 132 1045379
4 11895 16963 46 66 1048415
5 5947 8481 23 33 1049174
6 2973 4240 11 16 1049350
7 1486 2120 5 8 1049390
8 743 1060 2 4 1049398
9 371 530 1 2 1049400
10 185 265 1 1 1049401
11 92 132 1 1 1049402
** What you don't have: **
12 46 66 1 1 1049403
13 23 33 1 1 1049404
14 11 16 1 1 1049405
15 5 8 1 1 1049406
16 2 4 1 1 1049407
17 1 2 1 1 1049408

Where:

i  = level
c  = size of the level in columns
r  = size of the level in rows
bc = number of blocks per column
br = number of blocks per row
t  = total number of blocks

You said that “it performs very well at zoom level under 12” and performs poorly with zoom level 
between 13 and 17.

The problem is that you don't have those levels built into your GeoRaster object.

So, before doing any change to the system settings, could you please try to increase the number of 
pyramids to 17?

Building the pyramids is a internal process on the server. Even if you use gdaladdo, that will just 
call a function from the GeoRaster PL/SQL extension.

It wouldn't take as long as before because it will add up to the existing levels.

The PL/SQL call should looks like that:

declare
   gr sdo_georaster;
begin
   select georaster into gr from fluvd04q200pj where id = 1 for update;
   sdo_geor.generatePyramid(gr, 'rlevel=17 resampling=NN');
   update fluvd04q200pj set georaster = gr where id = 1;
   commit;
end;
/

And the gdaladdo command should look like that:

gdaladdo georaster:geoserver,geoserver,MFPRODUK_11G,fluvd04q200pj,georaster,id=1 2 4 8 16 32 64 128 
256 512 1024 2048 4096 8192 16384 32768 65536

Best regards,

Ivan


Sebastian E. Ovide wrote:
> Ivan,
> 
> 
> 
>     Yes!
> 
>     and
> 
>     No!
> 
>     That query creates a *cursor* to go through the whole raster,
>     meaning, all the rows on the RasterDataTable that satisfy that query
>     in the order stipulated by it.
> 
>     It does require a some memory but it looks like you got it. 
> 
> 
> mmm... that makes me little bit nervous... I am testing it with a small 
> map. Its is only UK with 5mx5m pixel.... Just wondering how this 
> approach would scale to larger maps... US is 40 times bigger than UK... 
> and they have more accurated maps.... and more than a single layer.... 
> Just wondering how many manual configuration I'll be forced to do.... 
> (Oracle created Spatial extention exactly for this reason... so the 
> developer do not to think about handling blobs for him self anymore... 
> reducing costs and risks)
>  
> 
> 
>     By the way, how long does it take to produce a geotiff file? Have
>     loaded that image on QuantumGIS using the oracle_raster plugin? Hoes
>     does it perform?
> 
> 
> it is not possible to create a TIFF as it would be too big (max size is 
> 4GB)... and UK would be arounf 16GB (300MB in Oracle as I'm using deflate)
> 
> I am testing Oracle MapViewer on the same table and it HAS the same 
> problem.... The strange thing is that using MapBuilder (Oracle software 
> to prepare the maps to use with MapViewer) the maps are displayed very 
> fast at ANY zoom level !... therefore there is a way to do it fast (as 
> MapBuilder does...)
>  
> 
> 
>         I was expecting to find in the logs some query that reads a
>         subset of the whole image (just the tile/metatile that MapServer
>         is serving) for example using SDO_GEOR.getRasterSubset..
> 
> 
>     That would be very slow. The GDAL driver access the BLOB directly.
> 
>  
> not sure about that... well I guess that at least it releases some work 
> from the Oracle server...  Anyway, as I have a spatial index, why don't 
> just query only the right blobs?
> 
> 
>     I have more questions:
> 
>     Are you using Mapserver FastCGI with a recent version?
> 
> 
> nop... I have not tried that yet... I'll try on Monday... I have tried 
> as cgi and recently as mod_python.
>  
> 
>     If you do, them the process will remains in memory, the GDALDataset
>     associated with that GeoRaster will be keet, the *cursor* you remain
>     alive and all the zooms and pans are going to work faster. Like for
>     QGIS. Unless, of course, Mapserver is feeling the need to re-project
>     or produce a new overview tiles on the fly. Can you see that on the log?
> 
>     Regards,
> 
>     Ivan
> 
> 
> regards
> 
> 
> -- 
> Sebastian E. Ovide
> 
> 
>