Here I post my thoughts, QGIS tips and tricks, updates on my QGIS-related work, etc.

PostGIS vs ArcSDE: raster load speed test (summary)

30.06.2009 10:14 ·  GIS  ·  postgis, wktraster, arcsde

Let’s summarise what was said in the posts about PostGIS and ArcSDE. The image loading speed tests showed that PostGIS was much slower than ArcSDE.

However, do not forget that WKTRatser is still in an early stage of development (version 0.1.6 at the time of testing) whereas ArcSDE has been around for years. Also, rasters are usually loaded into the database once, so the load speed test is of little practical value. It would be much more interesting to compare the performance of these products when processing rasters. Unfortunately, this is not possible for a number of reasons.

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

30.06.2009 10:14 ·  GIS  ·  postgis, wktraster, arcsde

ArcSDE was tested on the same machine using the same dataset (see PostGIS test). The discs were formatted before the test, and the system was restored from the snapshot. As I could not get ArcSDE to work with my self-compiled PostgreSQL 8.3.7, I tested it on its bundled PostgreSQL 8.3.0 + ArcGIS 9.3 SP1 (build 1850) + ArcSDE 9.3.

As in the PostGIS test, the database cluster was located on a separate 80 GB disc. For the purity of the experiment, the original image in MrSID format was converted to ERDAS IMAGINE using ArcGIS tools. Loading and all other operations were done using ArcGIS Python scripts, not ArcCatalog.

The conversion to the ERDAS IMAGINE format has been carried out with the following script:

import arcgisscripting
gp = arcgisscritpting.create()
gp.toolbox = "management"
gp.CopyRaster_management("N-38-45.sid", "N-38-45.img", "#", "0", "#", "NONE", "NONE", "#")

and it took 1202 s (~20 min), the resulting file has the same size as when using gdal_translate, i.e. ~4.7 Gb. Now let’s build pyramids

import arcgisscripting
gp = arcgisscritpting.create()
gp.toolbox = "management"

Building the pyramids took 451 s (~7 min.) Before loading the raster into the database, we need to create a RasterDataset for it:

import arcgisscripting
gp = arcgisscritpting.create()
gp.toolbox = "management"
gp.CreateRasterDataset_management("Database Connections/raster.sde", "N_38_45", "14.25", "8_BIT_UNSIGNED", "#", 3, "#", "PYRAMIDS -1 CUBIC", "128 128", "LZ77", "#")

This operation took exactly 2 seconds :-). The time is so small that it can be neglected. Now we can load the raster into the created dataset:

import arcgisscripting
gp = arcgisscritpting.create()
gp.toolbox = "management"
gp.workspace = "d:\raster"
gp.CreateRasterDataset_management("N-38-45.img","Database Connections/raster.sde","LAST","FIRST","0","#","NONE","0","NONE")

The raster loading process was quite fast, taking only 1337 s (~22 min). After loading the image, the database cluster grew from 42,495,444 bytes (~40.5 MB) to 6,934,776,988 bytes (~6.45 GB).

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)
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
Band 2 Block=64x64 Type=Byte, ColorInterp=Undefined
Description = Layer_2
Overviews: 21481x19618, 10741x9809, 5371x4905, 2686x2453, 1343x1227, 672x614, 336x307
Band 3 Block=64x64 Type=Byte, ColorInterp=Undefined
Description = Layer_3
Overviews: 21481x19618, 10741x9809, 5371x4905, 2686x2453, 1343x1227, 672x614, 336x307

Now we can prepare the image for loading into the database. The preparation consists of using the Python script (shipped with WKTRaster) to generate an SQL file with the image dump. -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 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.

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

22.06.2009 09:02 ·  GIS  ·  postgis, wktraster, arcsde

Before moving on to the actual testing, I will describe the process of configuring PostgreSQL + PostGIS + WKTRaster. Since PostgreSQL has been built from source, all the work normally performed by the installer (creating users, initialising database clusters, etc.) has to be done manually.

The compilation process was described in the previous post. Here I will assume that you are using Windows XP Pro; PostgreSQL, GEOS, Proj, PostGIS and WKTRaster are already compiled, and everything is in the c:\postgres directory.

Let’s go!


matplotlib for OSGeo4W

15.06.2009 08:24 ·  GIS  ·  osgeo4w, matplotlib

There is a Python extension called matplotlib. It is very convenient and functional, allowing you to easily create and display various graphs and charts. It generates high-quality images and supports adding captions, including various special characters. I used this extension when I needed to output a histogram in my plugin.

That’s why I decided to create a package for the OSGeo4W installer. I read the instructions, experimented on a virtual machine, and here is the result: matplotlib is now available via the OSGeo4W network installer.

PostGIS vs ArcSDE: raster load speed test (preparation)

09.06.2009 12:03 ·  GIS  ·  postgis, wktraster, arcsde

Recently, PostGIS has received support for raster data and the ability to load images directly into the database through the WKTRaster extension. This is one of the features that previously fell short compared to ArcSDE.

As soon as raster support became available, it was natural to want to compare PostGIS and ArcSDE. When I saw a forum topic about it, I immediately volunteered to help.

Today I spent most of the day preparing: I downloaded source archives, read installation instructions, and compiled all necessary components. There were a few pitfalls: first, PostgreSQL 8.3.7 refused to compile, saying that utf8_and_shift_jis_2004.o could not be built. After some investigation, I found that the following files are missing


More precisely, they are present, but not in the src directory where the compiler looks for them, but in a completely different directory. After moving these files to the correct directory, the compilation was successfully completed. I described the compilation process in detail in the previous post.

All other components were compiled without any issues, the only trouble was that the archive with the SVN version of PostGIS turned out to be “broken”, so I had to re-download it.

The test data set has been downloaded, and all the components have been built. Now I am waiting for the test instructions.

To be continued…

Compiling PostgreSQL, PostGIS and WKTRaster on Windows

06.06.2009 15:53 ·  GIS  ·  postgis, wktraster, howto

To some extent, PostGIS can be seen as a free, open-source alternative to ArcSDE, but until recently it lacked support for raster data. Lately, a WKTRaster project has been started to fill this gap and develop an extension to support raster data in PostGIS. Perhaps in the near future, PostGIS will be able to offer its users functionality comparable to ArcSDE.

There was an idea to compare the speed of loading a large raster in ArcSDE and PostGIS. Since there are no ready-to-use binary builds of WTKRaster, it is necessary to build and configure the PostgreSQL + PostGIS + WKTRaster bundle before performing any tests.

We will build the latest stable version of PostgreSQL (8.3.7 at the time of writing), the latest SVN version of PostGIS (1.4.0SVN at the time of writing), and the latest SVN version of WKTRaster (0.1.6SVN at the time of writing). This post is based on the official build instructions and blog posts by Mateusz Loskot.


About dancing and OSGeo4W

05.06.2009 16:15 ·  GIS, Notes  ·  osgeo4w, dance, bravo

It’s hot, although even during the day the temp doesn’t rise above +25°C. The heat is particularly noticeable when dancing: our classroom is very limited when it comes to ventilation and opening windows, so everyone is sweating within 5-10 minutes of starting the warm-up. On the other hand, it is the perfect time for stretching — muscles warm up quickly and lose heat more slowly. Ira is making the most of this: she has decided to devote the last month of this year’s course to stretching alone.

Earlier I wrote about work on translating the OSGeo4W network installer into Ukrainian. It’s time to report. Translations are done, new resource files are committed to SVN, installers are built, uploaded to the server and available for download. Download links can be found on the OSGeo4W home page.

Nice little things

27.05.2009 10:34 ·  GIS  ·  qgis, ftools, osgeo4w

After spending some time solving various issues, I have finally managed to build the OSGeo4W network installer from source. This is necessary to create a similar installer, but with resource files translated into Ukrainian (and possibly in Russian, if I have enough motivation and time). As for many people, English is still quite a serious obstacle, and besides, it is much more pleasant to communicate with the program in your native language.

Just half an hour ago, my patch for fTools was accepted. The changes are available as of revision f13f45e (SVN r10849). Yay!

First patch for fTools

26.05.2009 09:08 ·  GIS  ·  qgis, ftools

I have prepared and submitted my first patch for fTools. The patch allows to use only selected features in the following geoprocessing tools:

I also made changes to the Basic Statistics tool. Namely, I added the ability to work with selected features and added support for text fields (previously only numeric fields were supported).

Ticket in bugtracker #11774. I already got a reply from Carson (fTools author): the patch will be applied soon.