UPDATE 2010-08-08: I’ve rewritten this post, with more interesting stuff.
Pierre Racine wrote an excellent tutorial on PostGIS WKT Raster working with raster and vector data. As I’m involved in the development of WKT Raster extension, I’d like to know about similar solutions. The closest one is Oracle GeoRaster [PDF], in the sense is a raster-on-database solution.
I thought: “Could I repit the Pierre’s tutorial with Oracle GeoRaster? would it be easier, harder?”. Now, my impressions:
- My machine: Intel Core 2 Duo 3 GHz, 3 GB RAM
- My Operating System: Windows XP SP3
- My Oracle version: Oracle Database 11g Release 1 Enterprise Edition (22.214.171.124.0)
First step: Install Oracle Database
Problem: After installing the software in my machine, it takes a loooooong time to start. Ok, Oracle Database Enterprise is a really powerful software, and is thought for lots of things PostGIS WKT Raster isn’t. It’s only for people simply looking for a raster-in-database solution, this one could be as using a sledgehammer to crack a nut. Only first impression.
And WKT Raster?: The installation of PostgreSQL + PostGIS + WKT Raster is much lighter than the installation of Oracle Database 11g Enterprise. But Oracle Database has more components. We can’t compare both things. The point is, if you only need a raster-on-database solution, WKT Raster is a lighter one.
Now, we’re going to create a new user. We execute a command shell and open SQL*Plus as sysdba (using sys user):
sqlplus sys/password as sysdba
Once identified, execute:
create user jorge identified by jorgejorgedefault tablespace users quota unlimited on users temporary tablespace temp; grant create session to jorge;
When we create a new user, a new schema is automatically created, to store all the stuff generated by the new user. In this case, the schema is created in the users tablespace. We need to ensure the new user has the create session privilege granted, to start session in the Oracle backend.
Next step, grant the new user enough privileges to create and manage tables. The easy way:
grant all privileges to jorge
Now, let’s see how to load shapefiles into Oracle Database.
LOAD SHAPEFILES INTO ORACLE DATABASE USING ORACLE SPATIAL
Problem: To import shapefiles into an Oracle Database, you must convert them to SDO format using Oracle Shapefile Converter.
And PostGIS?: The ESRI Shapefile Format [PDF] is the format accepted by the PostGIS loader. So, you have to transform your data to this format if you want to use it, just like Oracle’s case. In both cases, instead of official loader you can use ogr2ogr to directly load the data into the database.
Download Oracle Shapefile Converter here. The last version is from 2005, and the examples of the README file were made using Oracle 9, but still work. I converted a shapefile of the Ibiza island (in Spain) to SDO format. Here, the line:
The shapefile is in UTM system, SRID 23030. You can find the x and y bounds here. As result, shp2sdo generates 2 files:
- SQL file: With the Ibiza’s table and the metadata
- CTL file: With the data and control sentences for SQL*Loader
Time to load the SDO file in database. We use SQL*Loader:
We now have a new table created: IBIZA_COVERAGE:
Now, let’s work with raster data
LOAD RASTER DATA INTO ORACLE DATABASE USING ORACLE GEORASTER
We’ll need raster data. I’ve downloaded GeoTIFF files from Spain and Canary Island, by selecting them in the map. 6 files: srtm_35_04.tif, srtm_35_05.tif, srtm_36_04.tif, srtm_36_05.tif, srtm_33_07.tif, srtm_34_07.tif.
Problem: The GeoRaster loader does not support source raster files in BSQ interleaving, and it might raise memory errors if the files are too big (how much is “too big”?). To avoid this problems, the Oracle GeoRaster developers guide recommends reformat and reblock the images using gdal_translate:
gdal_translate -of GTiff -co “TFW=YES” -co “INTERLEAVE=PIXEL” -co “TILED=YES” image.tif image_new.tif
This line creates a stripped image_new.tif file based in the original image.tif, but with pixel interleaving. After applying this filter to the six images, they’re ready for loading in Oracle GeoRaster.
And WKT Raster? The storage used for bands in WKT Raster is BSQ, but you don’t have to take care of the band interleaving of the raster you want to load. As the loader is based in GDAL, GDAL takes care of this.
Problem: We need to first create the tables to store the raster data. These data are stored as a relationship between two tables. One table stores the metadata, and another one stores the raster data itself. And you need two tables for each raster you want to store. The software doesn’t provide an automatic tool to perform the create-needed-tables-and-load-data operation in one step.
And WKT Raster? The loader creates the needed tables, the indexes (if specified in command line options) and store the data in them. To be honest, the loader generates a SQL file that you can load on Postgres, and makes all the stuff. Anyway, is easier.
Let’s create the needed tables to store the downloaded GeoTIFF images of Spain:
CREATE TABLE spain_images (image_id NUMBER PRIMARY KEY, image_description VARCHAR2(50), image SDO_GEORASTER); CREATE TABLE spain_images_rdt OF SDO_RASTER (PRIMARY KEY (rasterID, pyramidLevel, bandBlockNumber, rowBlockNumber, columnBlockNumber)) TABLESPACE users LOB(rasterBlock) STORE AS SECUREFILE lobseg (NOCACHE);
Two important things to remark here:
- We’re using LOB secure files storage schema. See Oracle Database SecureFiles and Large Objects Developer’s Guide and Oracle Database SQL Language Reference for further information on this topic.
- When we create the primary key for this table, a B-Tree index over data table is automatically created. PostGIS WKT Raster creates a GiST index for the same purpose.
Once tables have been created, we have three options for loading the raster data:
- Use the PL/SQL loader
- Use the Java loader
- Use the Java-based GeoRaster visualizator.
Problem: The two last options require to download and install the Oracle Database 11g examples. About 500MB to download. Again, this using a sledgehammer to crack a nut impression.
And WKT Raster? When you install the WKT Raster extension on a PostGIS-enabled database, you have all you need. Even some test code. Again, Oracle Database Enterprise is much bigger. So, you can’t compare both solutions in this sense.
Anoter important issue: One important advantage of WKT Raster over Oracle GeoRaster is the georeferencing approach. In case of non-rectangular raster coverages, this is particularly clear. Better to see with this two images:
That basically means that Oracle GeoRaster requires one raster data table for each raster data file loaded, if these raster files are from an irregular raster coverage. PostGIS WKT Raster allows only one table for all kinds of raster coverages. Regular or not. You can get further information in the WKT Raster FAQ
Load raster data using PL/SQL loader
The first step is to grant permissions to our user and to MDSYS for reading the file we want to load, using a Java-based program.
call dbms_java.grant_permission(‘JORGE’, ‘SYS:java.io.FilePermission’, ‘C:orcl_tutimage_new.tif’, ‘read’); call dbms_java.grant_permission(‘MDSYS’, ‘SYS:java.io.FilePermission’, ‘C:orcl_tutimage_new.tif’, ‘read’);
We repit the same operation with the rest of the files. Then, we can load the images, using a little PL/SQL code. Here, a real executed piece of code:
DECLARE geor SDO_GEORASTER; BEGIN -- Initialize an empty GeoRaster object into which the external image -- is to be imported. INSERT INTO spain_images values( 1, 'Spain_TIFF_1', sdo_geor.init('spain_images_rdt') ); -- Import the TIFF image. SELECT image INTO geor FROM spain_images WHERE image_id = 1 FOR UPDATE; sdo_geor.importFrom(geor, 'blocksize=(256,256)', 'TIFF', 'file', 'C:orcl_tutsrtm_35_04_new.tif'); UPDATE spain_images SET image = geor WHERE image_id = 1; END;
You have to repit the same insert-select-update cycle for all the images you want to load. Is striking you first have to insert an empty value in the data table, and then call a PL/SQL procedure to store the data in it.
And WKT Raster? The raster formats allowed for loading are the ones allowed for your version of GDAL. You can check the GDAL accepted raster formats here. Anyway, for sure you can use more than six formats with a default installation of GDAL.
Now, you have all your images loaded into database. We can use the validategeoraster procedure to ensure the information is correctly stored:
Load raster data using Java loader
If you want to use the Java loader, you need to download the database examples from Oracle site. Take care of downloading the file that matchs your database version. Once installed, you’ll see a new md/demo folder inside your %ORACLE_HOME% directory ($ORACLE_HOME in Linux. This variable is set during Oracle Database installation). In that folder, there’s a lot of examples of all the technologies present in Oracle Database Enterprise. We are interested in the Georaster folder.
Inside this Georaster folder, there are two more folders:
- plsql: Some pieces of PL/SQL code to create GeoRaster tables, delete them, modify them, insert values, etc. Simple examples, resembling the code above.
- java: here you hava the image loader, the image exporter and the image viewer. There’s a README file, explaining how to use them.
To execute the java code, we first have to add some jar files to the CLASSPATH enviroment variable:
CLASSPATH=%CLASSPATH%;%ORACLE_HOME%/ord/jlib/jai_codec.jar;%ORACLE_HOME%/ord/jlib/jai_core.jar; %ORACLE_HOME%/jdbc/lib/ojdbc5.jar;%ORACLE_HOME%/rdbms/jlib/xdb.jar;%ORACLE_HOME%/lib/xmlparserv2.jar; %ORACLE_HOME%/lib/xmlcomp.jar;%ORACLE_HOME%/lib/xschema.jar;%ORACLE_HOME%/jlib/jewt4.jar; %ORACLE_HOME%/md/jlib/sdoapi.jar;%ORACLE_HOME%/md/jlib/sdoutl.jar; %ORACLE_HOME%/md/demo/georaster/java/georaster_tools.jar
Additionally, we’ll add xtiff-jai.jar and geotiff-jai.jar, because we want to load and export GeoTIFF images. We download these files, put them into a folder, and add the path to that folder to the CLASSPATH enviroment variable.
Problem: In the README file, the JDBC driver is referred as ojdbc14.jar, and old version. But the driver available with this installation of Oracle is the newer ojdbc5.jar. An error caused by copy&paste of an older version of README, I guess.
Now, we can execute the loader with one of the files:
java oracle.spatial.georaster.tools.GeoRasterLoader localhost orcl 1521 jorge mypassword thin 32 T images image "blocking=true,blockSize=(256,256),geotiff=true,srid=4326" "srtm_34_05_new.tif,1,rdt1,srtm_34_05_new.tfw,4326"
Remember we blocked the image with gdal_translate. And the SRID of the files can be fetched with gdalinfo. The image formats accepted are the same than with PL/SQL loader plus JPEG and JP2.
Problem: This happened when loading with PL/SQL, but in this case, I find it even less intuitive. If you want to add raster data, you first have to create an empty entry in data table, and then provide the name of this table and the id of the new record to the Java loader (in the example, the raster data table is rdt1, and the id is 1).
And WKT Raster? As said, the whole process of create needed tables and insert data, can be performed in one step with WKT Raster. Much simpler.
So, before calling the loader, you’ll have to execute an INSERT sentence like this:
INSERT INTO images values( 1, sdo_geor.init('image', 1) );
The important thing here is you can provide an id to sdo_geor.init procedure, and this will be the id you’ll have to use with the new image when calling the loader. If you don’t provide an id number as second argument of sdo_geor.init procedure, the system will choose the first free one. Write it down, to use it when calling the loader. Not very clear, in my opinion.
Minor problem: There is a parameter, the eighth one, that has always the same value: T (interpreted as true, according with documentation). I suppose it’ll be used in future releases, or it will be deleted.
Load raster data using GeoRaster visualizator
The GeoRaster visualizator is a program written in Java that allows to load, view and export GeoRaster data. You can execute the visualizator by running the script startGeoRasterViewer.bat (or startGeoRasterViewer.sh if you’re in a UNIX enviroment).
Problem: The Windows script (startGeoRasterViewer.bat) has 1 error. I had to fix it manually: At the beggining of the script, there’s a goto sentence that drives the code to an unexsistent tag (gotHome). The correct tag is moreCheck2.
Now, we can execute the script file and load the GeoRaster viewer. As we can see, the images can be loaded from a disk file or retrieved from database.
We are interesting in fetching images from database, so, we provide our credentials. The viewer loads the GeoRaster data found in our schema. Here, a screenshot
In the menu bar, the third button from the right is the loading button. If we push it, a dialog will ask us for needed data, as host name, port number, database name, user name, password (yes, again). It will ask for the table and column name where we want to load our data. And for the raster id and the raster data table…
Problem: As happened when using java loader, you first have to create a new entry in raster data table by calling sdo_geor.init PL/SQL procedure, and be careful with raster id. Not very intuitive.
VIEW RASTER DATA FROM ORACLE GEORASTER
To view the raster data loaded into the database, you can use GeoRaster viewer. Of course you can use any other tools that allow connection with Oracle, but I want to know which tools are provided during a common installation of Oracle Database.
Problem: There are two things I don’t like about GeoRaster viewer:
- In README file states that Oracle GeoRaster provides, among others, the following feature: “supporting box selection on the GeoRaster object in the image panel, and allowing user to export the selected window to the image file in the local file system”. Well, I can’t find the way of doing it. It’s possible, when you export data, to specify init and end row and column, but not to directly select parts of the image in the viewer.
- There’s a None option in the menu Image that seems to be a kind of undo option, but I’m not sure. A bit confusing option. See the screenshot:
Apart from this, the viewer is an acceptably useful tool for doing some basic operations on rasters
And WKT Raster? WKT Raster doesn’t have a GUI, or a visualizator distributed packed with the code. Using GDAL WKT Raster driver (basic version right now, read only. It’s in my TODO list) you can connect WKT Raster with any program that uses GDAL for raster reading, but honestly, I haven’t tested it. It’s planned to work in this point.
EXPORT RASTER DATA FROM ORACLE GEORASTER
Again, we have three ways of exporting data from Oracle GeoRaster:
- Using PL/SQL exporter
- Using Java-based exporter
- Using Java GeoRaster viewer
Export raster data using PL/SQL exporter
As in the importing option, the first step is, as sysdba, grant permissions to our user, and to MDSYS. Write permissions, this time:
call dbms_java.grant_permission(‘JORGE’, ‘SYS:java.io.FilePermission’, ‘C:orcl_tutexportfichero.tif’, ‘write’); call dbms_java.grant_permission(‘MDSYS’, ‘SYS:java.io.FilePermission’, ‘C:orcl_tutexportfichero.tif’, ‘write’);
Now, a bit of real PL/SQL again:
DECLARE geor SDO_GEORASTER; BEGIN
SELECT image INTO geor FROM spain_images WHERE image_id = 1;
-- Export the whole GeoRaster object into a GeoTIFF file. sdo_geor.exportTo(geor,NULL, 'GeoTIFF', 'file', 'C:orcl_tutexportimage_exported_by_plsql'); END;
Problem: Formats accepted for image exporting using this method are only TIFF, BMP, GeoTiff or PNG. If you want JPEG or GIF, you have to use the Java exporter.
And WKT Raster? As for reading, the formats allowed for writting are GDAL-dependent. You can use gdal_translate for exporting data, but as I’ve said, the GDAL WKT Raster driver is still a basic one. Needs more work and testing. Apart from this, there are JPEG and TIFF exporters planned, internally builded, not GDAL dependent.
Executing this code, we get…
… yes, a memory error.
Problem: In the Oracle GeoRaster developers guide, we read that “The maximum amount of GeoRaster data that can be exported in a single operation is 67 megabytes”. Our raster data are greater than this (files of 75MB). But the most strange thing is during the import operation we didn’t have that problem.
And WKT Raster? The export size limits are determined by the file format itself, and the operating system/file system capabilities. This is, again, a GDAL issue. WKT Raster itself doesn’t impose a limitation. Further information here.
Ok, we can export only a fragment of the data. Using the viewer, we get init and end row and column, to see a significative part of our files. Let’s try it again, this time with the cropArea option:
DECLARE geor SDO_GEORASTER; BEGIN
SELECT image INTO geor FROM spain_images WHERE image_id = 1;
-- Export the whole GeoRaster object into a GeoTIFF file. sdo_geor.exportTo(geor, ’cropArea=(2024,951,2831,1663)’, 'GeoTIFF', 'file', 'C:orcl_tutexportimage_exported_by_plsql’); END;
The file name without extension. Now the export operation finishes. Here, the result:
I personally think this is a severe limitation. Are 67 MB too much megabytes for Oracle GeoRaster?, why?
Export raster data using Java-based exporter
The Java exporter works in a similar way than the Java importer.
Problem: We have to provide some parameters, like raster data table and raster id, that could be not very intuitive. We have to remember the raster data table name and the raster id we used when loading the data if we want to export the data loaded from the original files.
And WKT Raster? As said, for data exporting, you use gdal_translate. Not well tested yet. But the needed options are clearly explained here
Minor problem: In the README file, by the way, there is a minor typo when explaining the exporter calling method. There’s a repeated parameter in parameter list. Not very important, but it should be fixed in next releases.
Then, the instruction to export one of the raster previously imported is:
java oracle.spatial.georaster.tools.GeoRasterExporter localhost 1521 user password thin 32 T images image "1,rdt1" "GEOTIFF" "export_file.tif"
This time, the file name with extension, not like in the PL/SQL example. The formats accepted are the same than with the Java importer: TIFF, GeoTIFF, JPEG, BMP, GIF, PNG and JP2. After executing this line, we get…
… yes, a memory error again.
Problem: The raster is too big to be exported. The maximum size is, again, 67 MB. And we didn’t have this problem while loading with the Java importer.
But this time, we have another solution, to get the whole raster exported, instead of using the cropArea method: the Java VM -Xmx option.
Then, if we increase the maximum heap size for our Java VM, we can get the whole raster data. I think it shouldn’t be necessary. I didn’t find these limitations working with GDAL and PostGIS WKT Raster.
Export raster data using Java GeoRaster viewer
Finally, we can export our data by using the Java GeoRaster Viewer. In the screenshot, the menu options available for doing this operation are marked in red
Then, we provide some basic parameters (see screenshot)
Problem: If we choose GeoTIFF as format, we don’t need a World File. The program should inform about this, because while importing with the GeoRaster viewer, we have such option.
Ok, we accept the options and then…
Big problem: Yes, memory problem again. But the thing is the program doesn’t inform us about this problem. Actually, says the data were exported correctly! And the file has not been exported. I think this is an important problem, and must be fixed. See screenshot
In case of selecting any different format from GeoTIFF, and tell the viewer to generate a World File, it’s correctly generated, but the image file, as we’ve seen, doesn’t. The parameters of the World File are generated using scientific notation, but are correct.
Of course, we can execute the GeoRaster viewer using -Xmx parameter, or export only an area of the raster, but I think this is not a good solution.
With Oracle GeoRaster, you can have a basic raster-in-database support, but I’m surprised to find bugs, important limitations and non-intuitive tools in an extension of a commercial software like this. Problems that, in most cases, WKT Raster has solved, or never suffered from them.
The Oracle GeoRaster project seems to be something left since 2007. Next days, I’m going to delve into the tool, comparing its functionality with PostGIS WKT Raster, following the Pierre’s tutorial