[postgis-users] rasdaman performance (was: WKTRaster : gdal2wktraster.py cannot read AIG/Arc/Info Binary Grid)

Sebastian E. Ovide sebastian.ovide at gmail.com
Thu Jul 22 06:02:38 PDT 2010


thanks for that Peter

On Thu, Jul 22, 2010 at 9:35 AM, Peter Baumann <
p.baumann at jacobs-university.de> wrote:

>  Sebastian,
>
> as it will take some time until we complete our performance evaluation,
> here a quick response taken from the log file on my notebook. I have run a
> few rasql queries (while all other demon stuff plus my desktop apps were
> running as well):
>
> cold access to 3 images of 256x211 8bit greyscale:
> --- snip -------------
> [2010-07-22 10:11:35] request from 127.0.0.1
> Request: executeQquery 'select mr from mr'...parsing...checking
> semantics...optimizing (level 3)...No optimization library found
> evaluating...ok, result type 'set <marray <char, [0:255,0:210]>>', 3
> element(s).
> [2010-07-22 10:11:35] request completed; *request time=0.049s*
> --- snip -------------
>
> same, hot access:
> --- snip -------------
> [2010-07-22 10:16:27] request completed; *request time=0.007s*
> --- snip -------------
>
> accessing a 2D slice 512x512 (pixel type: unsigned short) from a *3D
> weather data cube* 16384x144x73:
> --- snip -------------
> [2010-07-22 10:25:20] request from 127.0.0.1
> Request: executeQquery 'select m[0:511,0:511,0] from
> ten_metre_U_wind_component as m'...parsing...checking semantics...optimizing
> (level 3)...No optimization library found
> evaluating...ok, result type 'set <marray <ushort, [0:511,0:143]>>', 1
> element(s).
> [2010-07-22 10:25:21] request completed; *request time=0.290s*
> --- snip -------------
>
> accessing a 3D subcube 100x100x73 from same cube:
> --- snip -------------
> [2010-07-22 10:26:49] request from 127.0.0.1
> Request: executeQquery 'select m[0:99,0:99,*:*] from
> ten_metre_U_wind_component as m'...parsing...checking semantics...optimizing
> (level 3)...No optimi
> zation library found
> evaluating...ok, result type 'set <marray <ushort, [0:99,0:99,0:72]>>', 1
> element(s).
> [2010-07-22 10:26:49] request completed; *request time=0.293s*
> --- snip -------------
>
> extracting a 1024x73 x/t slice from same cube:
> --- snip -------------
> [2010-07-22 10:28:27] request from 127.0.0.1
> Request: executeQquery 'select m[0,0:1023,*:*] from
> ten_metre_U_wind_component as m'...parsing...checking semantics...optimizing
> (level 3)...No optimization library found
> evaluating...ok, result type 'set <marray <ushort, [0:143,0:72]>>', 1
> element(s).
> [2010-07-22 10:28:28] request completed; *request time=0.289s*
> --- snip -------------
>
> As you see, slicing in all 3 dimensions delivers roughly the same times
> (although tiling has not been optimized).
>
> laptop specs:
> CPU: Intel(R) Core(TM)2 Duo CPU     T8300  @ 2.40GHz
> Mem:   2GB (swap off)
> Linux floridita 2.6.32-23-generic #37-Ubuntu SMP Fri Jun 11 07:54:58 UTC
> 2010 i686 GNU/Linux
> rasdaman running without optimization package (cf log).
>
> Of course not a scientific test (that will follow), but it may give an
> idea.
>
> cheers,
> Peter
>
>
>
> On 07/21/2010 11:00 AM, Peter Baumann wrote:
>
> Sebastian,
>
> it's a XEON. Your question comes at the right time, we are in the process
> of compiling performance data this summer. I will soon post detail info
> here.
>
> Loading 100k x 150k pixels is no problem. You load piecemeal, and the
> system will align with its internal tiling + maintain the image pyramid.
>
> 10 years back we have loaded the whole ortho image mosaic of Bavaria into
> the database (server was a desktop PC of about 1000 EUR), that was 600 GB
> compressed (losslessly). At that time, import of an 230 MB image took about
> 2min (array placement, re-tiling of input, index maintenance, compression,
> and this for all pyramid levels). Read access, OTOH, was about 150ms. A few
> years later the Thuringia Mapping Agency, again with a desktop PC as a
> server, verified that access times are stable under a load of about 15
> concurrent users challenging the system with WMS type interactive browsing.
>
> For another mapping agency we have set up a database containing ortho
> image, 30+ thematic raster layers, DEM. Access was via WMS, the rasdaman
> system delivered a single image for display (Javascript was not what it is
> today...). This included on-the-fly classification of the DEM and styling
> the thematic layers.
>
> So we have already done complete raster servers for several German States.
>
> BTW, standards: We have established the OGC Web Coverage Processing Service
> (WCPS) standard which lifts the rasdaman concept of a raster querfy language
> to an OGC standard. Being editor of currently 9 geo raster service specs in
> OGC we also pursue reference implementations on the basis of rasdaman. See
> www.earthlook.org for demos.
>
> -Peter
>
> PS: Should data really exceed disk capacity (hard to believe today) then we
> have a PhD thesis work which has implemented transparent tape cabinetaccess.
>
>
>
>
> On 07/21/2010 09:37 AM, Sebastian E. Ovide wrote:
>
>
>
> On Tue, Jul 20, 2010 at 5:08 PM, Peter Baumann <
> p.baumann at jacobs-university.de> wrote:
>
>>  On 07/20/2010 05:47 PM, Pierre Racine wrote:
>>
>>  Since we have this bug with big rasters,
>>
>>
>>  proven, any-size, ... ;-)
>>
>
> what do you mean ? were you able to load a raster of 100kx150k ?
>
>
>>
>> Who is proven? Well, running a dozen-TB seamless mosaic on PostgreSQL for
>> many years, having online-demos available since years, etc... you decide, in
>> comparison.
>>
>>
> Peter, how big is your cluster to support dozen of TB ? and how fast are
> the queries ? (for example a simple lookup)
>
>
>> -Peter
>>
>>
>>
>>  I would suggest you split your raster with something like GDAL
>> gdal_retile.py and then import your tiles into a single WKT Raster table
>> following the instruction provided in our tutorial. Each tile will be stored
>> in a column cell of type RASTER similar to the PostGIS GEOMETRY type.
>>
>>
>>
>> As I said previously you can then query the raster in SQL like this:
>>
>>
>>
>> SELECT ST_Value(rast, ST_Geomfromtext('Point(-78.1 58.1)', 4326))
>>
>> FROM srtm_tiled_100x100
>>
>> WHERE ST_Intersects(rast::geometry, ST_Geomfromtext('Point(-78.1 58.1)',
>> 4326)) AND whatever you want.
>>
>>
>>
>> Basically WKT Raster is the first true SQL interface with which is is
>> simple to do such things… It's proven, any-size, cloud-scalable, and open
>> source J Maybe not that prooven. But who is?
>>
>>
>>
>> There is also rasdaman but I don’t think you can use SQL. It would be nice
>> if you could compare both… I can’t find time for this. Jorge has started
>> comparing PostGIS WKT Raster with Oracle Georaster though.
>>
>>
>>
>> Pierre
>>
>>
>>
>>
>>
>> *From:* postgis-users-bounces at postgis.refractions.net [
>> mailto:postgis-users-bounces at postgis.refractions.net<postgis-users-bounces at postgis.refractions.net>]
>> *On Behalf Of *Sebastian E. Ovide
>> *Sent:* 20 juillet 2010 10:46
>> *To:* PostGIS Users Discussion
>> *Subject:* Re: [postgis-users] WKTRaster : gdal2wktraster.py cannot read
>> AIG/Arc/Info Binary Grid
>>
>>
>>
>> yes... with png worked... but it was a different png (a smaller one)...
>>
>> so I've converted the ESRI into a png and tried to import it... and it
>> didn't work neither...
>>
>> so this is the situation:
>> A have huge rusters (from 150kx150k).. In Oracle I would just load it (the
>> huge raster) in a single row of a GeoRaster table and then Oracle GeoRaster
>> would split it in small tiles and store one tile per line of another table
>> (Raster Data Table)... then I can run a query similar to this one: SELECT
>> getcellvalue(t.rastercolumn,x,y) from GeoRasterTable t where t.id=1; and
>> GeoRaster will query automatically the spatial indexes and the Raster Data
>> Table and it will find the right tile etc...
>>
>> I do not know how WKTRaster works.... If I cannot import a such big image,
>> of course I can split it in smaller georeferenced tiles... (how?)... but....
>> My main question is: after that, how will the table look like ? how can I do
>> the same query (where id=1 or where name="UK" etc...) ?
>>
>>  On Tue, Jul 20, 2010 at 2:31 PM, Pierre Racine <
>> Pierre.Racine at sbf.ulaval.ca> wrote:
>>
>> Wait. You first said the png was working. Now it’s not? Did you try
>> gdal_translate with the ESRI grid? For sure I haven’t test yet with such big
>> rasters. Is this the result of a merge or all your original raster are all
>> this size? The point is that with WKT Raster you don’t have to merge your
>> raster first into a gigantic raster in order to get it store in a unique
>> table like with Oracle Spatial.
>>
>>
>>
>> Pierre
>>
>>
>>
>> *From:* postgis-users-bounces at postgis.refractions.net [mailto:
>> postgis-users-bounces at postgis.refractions.net] *On Behalf Of *Sebastian
>> E. Ovide
>> *Sent:* 20 juillet 2010 05:51
>> *To:* PostGIS Users Discussion
>> *Subject:* Re: [postgis-users] WKTRaster : gdal2wktraster.py cannot read
>> AIG/Arc/Info Binary Grid
>>
>>
>>
>> Hi Pierre,
>>
>> Does gdal2wktraster.py have any limitation on the maximum number of
>> columnsxrows ?
>>
>> in my case, my raster is  107759 x 168633...
>>
>> gdal works well:
>>
>> sebas at SeansPC:~/rasters$ gdal_translate -of PNG raster/ test.png
>> Input file size is 107759, 168633
>> 0...10...20...30...40...50...60...70...80...90...100 - done.
>>
>>
>> sebas at SeansPC:~/rasters$ python gdal2wktraster.py -r raster/ -t
>> sebastable -o ok.sql
>> gdal2wktraster.py:695: DeprecationWarning: 'H' format requires 0 <= number
>> <= 65535
>>   hexwkb += wkblify('H', xsize)
>> gdal2wktraster.py:696: DeprecationWarning: 'H' format requires 0 <= number
>> <= 65535
>>   hexwkb += wkblify('H', ysize)
>> gdal2wktraster.py:727: DeprecationWarning: integer argument expected, got
>> float
>>   hexwkb += wkblify(pt2fmt(pixtype), nodata)
>> Traceback (most recent call last):
>>   File "gdal2wktraster.py", line 1013, in <module>
>>     main()
>>   File "gdal2wktraster.py", line 976, in main
>>     wkblify_raster(opts, filename, i)
>>   File "gdal2wktraster.py", line 921, in wkblify_raster
>>     summary = wkblify_raster_level(options, ds, options.overview_level,
>> band_range, infile, i)
>>   File "gdal2wktraster.py", line 888, in wkblify_raster_level
>>     hexwkb += wkblify_band(options, band, level, xoff, yoff,
>> read_block_size, block_size, infile, b)
>>   File "gdal2wktraster.py", line 777, in wkblify_band
>>     target_block_size[0], target_block_size[1])
>>   File "/usr/lib/python2.6/dist-packages/osgeo/gdal.py", line 895, in
>> ReadAsArray
>>     buf_xsize, buf_ysize, buf_obj )
>>   File "/usr/lib/python2.6/dist-packages/osgeo/gdal_array.py", line 228,
>> in BandReadAsArray
>>     ar = numpy.empty([buf_ysize,buf_xsize], dtype = typecode)
>> MemoryError
>>
>>
>> sebas at SeansPC:~/rasters$ python gdal2wktraster.py -r test.png  -t
>> sebastable -o ok.sql
>> gdal2wktraster.py:695: DeprecationWarning: 'H' format requires 0 <= number
>> <= 65535
>>   hexwkb += wkblify('H', xsize)
>> gdal2wktraster.py:696: DeprecationWarning: 'H' format requires 0 <= number
>> <= 65535
>>   hexwkb += wkblify('H', ysize)
>> gdal2wktraster.py:727: DeprecationWarning: integer argument expected, got
>> float
>>   hexwkb += wkblify(pt2fmt(pixtype), nodata)
>> Traceback (most recent call last):
>>   File "gdal2wktraster.py", line 1013, in <module>
>>     main()
>>   File "gdal2wktraster.py", line 976, in main
>>     wkblify_raster(opts, filename, i)
>>   File "gdal2wktraster.py", line 921, in wkblify_raster
>>     summary = wkblify_raster_level(options, ds, options.overview_level,
>> band_range, infile, i)
>>   File "gdal2wktraster.py", line 888, in wkblify_raster_level
>>     hexwkb += wkblify_band(options, band, level, xoff, yoff,
>> read_block_size, block_size, infile, b)
>>   File "gdal2wktraster.py", line 777, in wkblify_band
>>     target_block_size[0], target_block_size[1])
>>   File "/usr/lib/python2.6/dist-packages/osgeo/gdal.py", line 895, in
>> ReadAsArray
>>     buf_xsize, buf_ysize, buf_obj )
>>   File "/usr/lib/python2.6/dist-packages/osgeo/gdal_array.py", line 228,
>> in BandReadAsArray
>>     ar = numpy.empty([buf_ysize,buf_xsize], dtype = typecode)
>> MemoryError
>>
>>
>>
>>  On Mon, Jul 19, 2010 at 5:56 PM, Pierre Racine <
>> Pierre.Racine at sbf.ulaval.ca> wrote:
>>
>> Hi Sebastian,
>>
>>
>>
>> I can convert ESRI Grid file to .sql without problem using
>> gdal2wktraster.py and the same parameters as you. I can do both integer and
>> floating point rasters.
>>
>>
>>
>> Maybe this is a GDAL problem. Try to convert it using gdal_translate (to
>> tiff for example). This would be a better test than just gdalinfo.
>>
>>
>>
>> Could you provide us with a file sample?
>>
>>
>>
>> Pierre
>>
>>
>>
>> *From:* postgis-users-bounces at postgis.refractions.net [mailto:
>> postgis-users-bounces at postgis.refractions.net] *On Behalf Of *Sebastian
>> E. Ovide
>> *Sent:* 19 juillet 2010 12:28
>> *To:* PostGIS Users Discussion
>> *Subject:* [postgis-users] WKTRaster : gdal2wktraster.py cannot read
>> AIG/Arc/Info Binary Grid
>>
>>
>>
>> Hi All,
>>
>> trying to create a SQL with gdal2wktraster.py. It works on PNG but it
>> doesn't on AIG files...
>>
>> Note: As Gdal works fine.
>>
>> C:\Program Files\PostgreSQL\8.4\bin>gdalinfo c:\tmp\raster
>> Driver: AIG/Arc/Info Binary Grid
>> Files: c:\tmp\raster
>>        c:\tmp\raster\dblbnd.adf
>>        c:\tmp\raster\hdr.adf
>>        c:\tmp\raster\metadata.xml
>>        c:\tmp\raster\prj.adf
>>        c:\tmp\raster\sta.adf
>>        c:\tmp\raster\vat.adf
>>        c:\tmp\raster\w001000.adf
>>        c:\tmp\raster\w001000x.adf
>>        c:\tmp\raster\w001001.adf
>>        c:\tmp\raster\w001001x.adf
>>        c:\tmp\raster\z001001.adf
>>        c:\tmp\raster\z001001x.adf
>>        c:\tmp\raster\z001002.adf
>>        c:\tmp\raster\z001002x.adf
>>        c:\tmp\raster\z001003.adf
>>        c:\tmp\raster\z001003x.adf
>>        c:\tmp\raster\z001004.adf
>>        c:\tmp\raster\z001004x.adf
>>        c:\tmp\raster\z001005.adf
>>        c:\tmp\raster\z001005x.adf
>>        c:\tmp\raster\z001006.adf
>>        c:\tmp\raster\z001006x.adf
>>        c:\tmp\raster\z001007.adf
>>        c:\tmp\raster\z001007x.adf
>>        c:\tmp\raster\z001008.adf
>>        c:\tmp\raster\z001008x.adf
>>        c:\tmp\raster\z001009.adf
>>        c:\tmp\raster\z001009x.adf
>>        c:\tmp\raster\z001010.adf
>>        c:\tmp\raster\z001010x.adf
>>        c:\tmp\raster\z001011.adf
>>        c:\tmp\raster\z001011x.adf
>>        c:\tmp\raster\z001012.adf
>>        c:\tmp\raster\z001012x.adf
>>        c:\tmp\raster\z001013.adf
>>        c:\tmp\raster\z001013x.adf
>>        c:\tmp\raster\z001014.adf
>>        c:\tmp\raster\z001014x.adf
>>        c:\tmp\raster\z001015.adf
>>        c:\tmp\raster\z001015x.adf
>> Size is 107759, 168633
>> Coordinate System is:
>> PROJCS["unnamed",
>>     GEOGCS["Unknown datum based upon the Airy 1830 ellipsoid",
>>         DATUM["Not_specified_based_on_Airy_1830_ellipsoid",
>>             SPHEROID["Airy 1830",6377563.396,299.3249646,
>>                 AUTHORITY["EPSG","7001"]],
>>             AUTHORITY["EPSG","6001"]],
>>         PRIMEM["Greenwich",0,
>>             AUTHORITY["EPSG","8901"]],
>>         UNIT["degree",0.01745329251994328,
>>             AUTHORITY["EPSG","9122"]],
>>         AUTHORITY["EPSG","4001"]],
>>     PROJECTION["Transverse_Mercator"],
>>     PARAMETER["latitude_of_origin",49],
>>     PARAMETER["central_meridian",-2],
>>     PARAMETER["scale_factor",0.9996012717],
>>     PARAMETER["false_easting",400000],
>>     PARAMETER["false_northing",-100000],
>>     UNIT["METERS",1]]
>> Origin = (128110.000000000000000,813270.000000000000000)
>> Pixel Size = (5.000000000000000,-5.000000000000000)
>> Corner Coordinates:
>> Upper Left  (  128110.000,  813270.000) (  6d29'37.32"W, 57d 7'47.53"N)
>> Lower Left  (  128110.000,  -29895.000) (  5d45'40.00"W, 49d34'10.24"N)
>> Upper Right (  666905.000,  813270.000) (  2d24'41.72"E, 57d 7'58.04"N)
>> Lower Right (  666905.000,  -29895.000) (  1d41'32.29"E, 49d34'18.23"N)
>> Center      (  397507.500,  391687.500) (  2d 2'15.04"W, 53d25'18.19"N)
>> Band 1 Block=256x4 Type=Byte, ColorInterp=Undefined
>>   Min=1.000 Max=4.000
>>   NoData Value=255
>>
>>
>> C:\Program Files\PostgreSQL\8.4\bin>python gdal2wktraster.py -r
>> c:\tmp\raster -t sebastable -o c:\tmp\sebas.sql
>> gdal2wktraster.py:644: DeprecationWarning: 'H' format requires 0 <= number
>> <= 65535
>>   hexstr = binascii.hexlify(struct.pack(fmt_little, data)).upper()
>> gdal2wktraster.py:644: DeprecationWarning: integer argument expected, got
>> float
>>   hexstr = binascii.hexlify(struct.pack(fmt_little, data)).upper()
>> ERROR 2: Multiplication overflow : 107759 * 168633 * 1
>> Traceback (most recent call last):
>>   File "gdal2wktraster.py", line 1013, in <module>
>>     main()
>>   File "gdal2wktraster.py", line 976, in main
>>     wkblify_raster(opts, filename, i)
>>   File "gdal2wktraster.py", line 921, in wkblify_raster
>>     summary = wkblify_raster_level(options, ds, options.overview_level,
>> band_range, infile, i)
>>   File "gdal2wktraster.py", line 888, in wkblify_raster_level
>>     hexwkb += wkblify_band(options, band, level, xoff, yoff,
>> read_block_size, block_size, infile, b)
>>   File "gdal2wktraster.py", line 777, in wkblify_band
>>     target_block_size[0], target_block_size[1])
>>   File "C:\OSGeo4W\apps\gdal-16\pymod\osgeo\gdal.py", line 835, in
>> ReadAsArray
>>     buf_xsize, buf_ysize, buf_obj )
>>   File "C:\OSGeo4W\apps\gdal-16\pymod\osgeo\gdal_array.py", line 140, in
>> BandReadAsArray
>>     ar = numpy.reshape(ar, [buf_ysize,buf_xsize])
>>   File
>> "C:\OSGeo4W\apps\Python25\lib\site-packages\numpy\core\fromnumeric.py", line
>> 116, in reshape
>>     return reshape(newshape, order=order)
>> ValueError: total size of new array must be unchanged
>>
>> Any ideas ?
>> --
>> Sebastian E. Ovide
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>>
>> --
>> Sebastian E. Ovide
>>
>> skype: sebastian.ovide
>>
>> +353 (0) 87 6340149
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>>
>> --
>> Sebastian E. Ovide
>>
>>
>>
>>
>>   --
>> Dr. Peter Baumann
>>  - Professor of Computer Science, Jacobs University Bremen
>>    www.faculty.jacobs-university.de/pbaumann
>>    mail: p.baumann at jacobs-university.de
>>    tel: +49-421-200-3178, fax: +49-421-200-493178
>>  - Executive Director, rasdaman GmbH Bremen (HRB 147737)
>>    www.rasdaman.com, mail: baumann at rasdaman.com
>>    tel: 0800-rasdaman, fax: 0800-rasdafax, mobile: +49-173-5837882
>> "Si forte in alienas manus oberraverit hec peregrina epistola incertis ventis dimissa, sed Deo commendata, precamur ut ei reddatur cui soli destinata, nec preripiat quisquam non sibi parata." (mail disclaimer, AD 10xx)
>>
>>
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
>
> --
> Sebastian E. Ovide
>
>
>
>
> --
> Dr. Peter Baumann
>  - Professor of Computer Science, Jacobs University Bremen
>    www.faculty.jacobs-university.de/pbaumann
>    mail: p.baumann at jacobs-university.de
>    tel: +49-421-200-3178, fax: +49-421-200-493178
>  - Executive Director, rasdaman GmbH Bremen (HRB 147737)
>    www.rasdaman.com, mail: baumann at rasdaman.com
>    tel: 0800-rasdaman, fax: 0800-rasdafax, mobile: +49-173-5837882
> "Si forte in alienas manus oberraverit hec peregrina epistola incertis ventis dimissa, sed Deo commendata, precamur ut ei reddatur cui soli destinata, nec preripiat quisquam non sibi parata." (mail disclaimer, AD 10xx)
>
>
>
>
>
> --
> Dr. Peter Baumann
>  - Professor of Computer Science, Jacobs University Bremen
>    www.faculty.jacobs-university.de/pbaumann
>    mail: p.baumann at jacobs-university.de
>    tel: +49-421-200-3178, fax: +49-421-200-493178
>  - Executive Director, rasdaman GmbH Bremen (HRB 147737)
>    www.rasdaman.com, mail: baumann at rasdaman.com
>    tel: 0800-rasdaman, fax: 0800-rasdafax, mobile: +49-173-5837882
> "Si forte in alienas manus oberraverit hec peregrina epistola incertis ventis dimissa, sed Deo commendata, precamur ut ei reddatur cui soli destinata, nec preripiat quisquam non sibi parata." (mail disclaimer, AD 10xx)
>
>
>
>


-- 
Sebastian E. Ovide
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100722/6370d169/attachment.html>


More information about the postgis-users mailing list