PostGIS vs ArcSDE: raster load speed test (part 2)

30.06.2009 09:25 ·  GIS  ·  postgis, wktraster, arcsde

Continue the testing saga.

Before describing the test and its results, a few words about the test platform.

The test itself was carried out according to Mateusz’s instructions. When the SQL representation of the raster was created, the resulting SQL file was written to the second disc, and before loading it into the database, the file was moved to the data partition of the first disc. After each stage of testing, all disks were defragmented using OS tools, and the machine rebooted.

This Landsat scene was used as a test image. Quite detailed information about the image can be found in the file N-38-45-45.met, which is located next to it, and I give a partially reduced output of gdalinfo below:

Driver: MrSID/Multi-resolution Seamless Image Database (MrSID)
Files: d:\wktraster_test\N-38-45.sid
Size is 42962, 39235
Origin = (193892.625000000000000,5543955.125000000000000)
Pixel Size = (14.250000000000000,-14.250000000000000)
IMAGE__INPUT_FILE_SIZE=5086292652.000000
IMAGE__TARGET_COMPRESSION_RATIO=29.999998
IMAGE__BITS_PER_SAMPLE=8
IMAGE__COMPRESSION_WEIGHT=2.000000
IMAGE__COMPRESSION_GAMMA=1.000000
IMAGE__COMPRESSION_BLOCK_SIZE=4096
Band 1 Block=1024x128 Type=Byte, ColorInterp=Red
Minimum=0.000, Maximum=242.000, Mean=101.223, StdDev=35.192
Overviews: 21481x19618, 10741x9809, 5371x4905, 2686x2453, 1343x1227, 672x614, 336x307, 168x154
Band 2 Block=1024x128 Type=Byte, ColorInterp=Green
Minimum=0.000, Maximum=242.000, Mean=126.427, StdDev=33.576
Overviews: 21481x19618, 10741x9809, 5371x4905, 2686x2453, 1343x1227, 672x614, 336x307, 168x154
Band 3 Block=1024x128 Type=Byte, ColorInterp=Blue
Minimum=0.000, Maximum=252.000, Mean=105.329, StdDev=29.924
Overviews: 21481x19618, 10741x9809, 5371x4905, 2686x2453, 1343x1227, 672x614, 336x307, 168x154

Before starting the test, a snapshot of the system was taken, and all disks were defragmented using OS tools. The source data (i.e., the raster) is located on the second partition of the 320 Gb disc.

Unfortunately, both GDAL and WKTRaster do not yet fully support the MrSID format, so the raster was converted to the ERDAS IMAGINE format (*.img) using the following command:

gdal_translate.exe -of HFA N-38-45.sid N-38-45.img

The conversion took 1160 s (~19 min), and the resulting file occupied ~4.7 Gb of disk space. This file was used in all subsequent operations. We have the image in the supported format, and now we need to build pyramids (overviews), which are not present in our file:

gdaladdo -r average N-38-45.img 2 4 8 16 32 64 128

This command took 1057 s (~17 min) to execute. Using gdalinfo, we make sure that the overviews have been created successfully

Band 1 Block=64x64 Type=Byte, ColorInterp=Undefined
Description = Layer_1
Overviews: 21481x19618, 10741x9809, 5371x4905, 2686x2453, 1343x1227, 672x614, 336x307
Metadata:
LAYER_TYPE=athematic
Band 2 Block=64x64 Type=Byte, ColorInterp=Undefined
Description = Layer_2
Overviews: 21481x19618, 10741x9809, 5371x4905, 2686x2453, 1343x1227, 672x614, 336x307
Metadata:
LAYER_TYPE=athematic
Band 3 Block=64x64 Type=Byte, ColorInterp=Undefined
Description = Layer_3
Overviews: 21481x19618, 10741x9809, 5371x4905, 2686x2453, 1343x1227, 672x614, 336x307
Metadata:
LAYER_TYPE=athematic

Now we can prepare the image for loading into the database. The preparation consists of using the Python script gdal2wktraster.py (shipped with WKTRaster) to generate an SQL file with the image dump.

gdal2wktraster.py -r N-38-45.img -t N_38_45_img_rb_128 -o N_38_45_img_rb_128.sql --index --srid 32638 -k -m 128x128 -O -M -v

This is quite a long process, so make sure you have some tea/coffee. On my test platform, it took 2904 s (~48 min). At the end, the script generates a report of the work done, showing how many tables will be created in the database after the script is loaded and how many tiles (blocks) will be in each table:

------------------------------------------------------------
Summary of GDAL to WKT Raster processing:
------------------------------------------------------------
Number of processed raster files: 1
List of generated tables (number of tiles):
1 N_38_45_img (103152)
2 o_2_N_38_45_img (25872)
3 o_4_N_38_45_img (6468)
4 o_8_N_38_45_img (1638)
5 o_16_N_38_45_img (420)
6 o_32_N_38_45_img (110)
7 o_64_N_38_45_img (30)
8 o_128_N_38_45_img (9)

The gdal2wktraster.py script produced the file N_38_45_img_rb_128.sql, which took up — hold on tight :-) — 13,564,596,564 bytes (~12.6 GB). There you go.

Since all the necessary preparations were made at the PostGIS configuration stage, the only remaining step is to load this monstrous script into the database.:

psql -f N_38_45_img_rb_128.sql -U postgres -d postgis

Loading the SQL script took 1952 s (32 min), and the database cluster grew from 38,748,476 bytes (~36.9 MB) to 7,283,127,972 bytes (~6.78 GB).

PostGIS testing is over, let’s move on to ArcSDE.

⮜ Prev
Next ⮞