A Noob in Oracle Land

The announcement that Oracle is supporting AMIs in the Amazon cloud came as a surprise to me. I had heard that there was a teaser version of Oracle out there for developers, but had not expected Oracle to jump on the cloud side, especially after Larry Ellison’s recent diatribe against cloud computing.

   “It’s complete gibberish. It’s insane. When is this idiocy going to stop?”

Just curious about this oracle of gibberish, I went on a tour of Oracle Land, the Kingdom of Ellison. This is no small undertaking for an enterprise as ambitious as Oracle. There are endless products and sub-products. The base of the pyramid is the database server, but after buying 50 or more companies in the last year or so, the borders of the empire extend way beyond RDBMS.

The venerable RDBMS has come a long way since IBMs E.F. Codd introduced the concept back in the 70s. I vaguely remember Oracle breaking into the PC world shortly after Turbo Pascal. There was a single DB product for the DOS IBM PC, and documentation consisted of a couple of grayish paperback manuals. Shortly after this, late 80s, a small vendor introduced GeoSQL to hook AutoCAD to the GIS world through Oracle. This was my first introduction to the potential of spatial databases and Oracle. The empire of Ellison has grown since then, and now documentation would fill a library as well as Ellisons bank account.

As an aside, we live in an interesting age at the dusk of the great technology innovators. The infamous industrialists of the previous era now exist only as shadowy figures in history texts, but the business innovators of technology are still walking among us, Larry Ellison, Bill Gates, Steve Jobs. The multi-billion personal fortunes are just now entering the charitable fund phase where our grand children will know their names in some impersonally institutional mode such as the Gates Foundation.

First stop in Oracle Land was a download of the free, as in free beer, teaser version, OracleXE.

  • Total data stored in XE is limited to 4GB
  • XE is limited to 1GB of RAM
  • XE is limited to 1 processor

Since my entire interest in Oracle is the spatial side, my next stop was Justin Lokitz’s helpful article on integration with Geoserver. Leading to this:

Fig 1 -http://localhost:80/geoserver/wms?service=WMS&request=GetMap&format=

Fig 2 – http://localhost:80/geoserver/wms/kml_reflect?layers=COUNTIES

Not a bad start. The Geoserver layer abstracts away the spatial guts of OracleXE. However, curiosity leads on. I found that OracleXE has some spatial components labelled ‘Locator’ as opposed to ‘Spatial’. Though only a subset of the extensive enterprise spatial version, geometry queries are possible. It took me a bit to find my way around.

Interestingly the open source world is generally more helpful in this respect. Although extensive, the forums of commercial software vendors are less friendly. For instance Paul Ramsey of Refraction fame is regularly present on the PostGIS forums, and Frank Warmerdam is always available to give a helping hand at the immensely useful www.gdal.org. But I doubt that I will ever run across a Larry Ellison post on the OracleXE forum. Many posts to commercial forums appear to languish unanswered, which is seldom the case in the OpenSource project forums I monitor.

It is worth noting that gdal’s ogr2ogr can be built with Oracle support on systems with Oracle Client libraries installed.

Oracle’s SDO_Geometry is present in a useful form letting users run geographic join queries like this:

   select c.COUNTY, c.STATE_ABRV, c.TOTPOP, c.POPPSQMI from states s, counties c where s.state = ‘California’ and sdo_anyinteract (c.geom, s.geom) = ‘TRUE’;

My next step was to look at SDO_Geometry in JDBC. Unfortunately Oracle’s JGeometry spatial library is not available for OracleXE, but the LGPL open source JTS library provides helpful OraReader and OraWriter classes. These encapsulate the SDO_GEOMETRY Struct translation to/from jts.geom.Geometry, where the rest of the JTS api can be applied.

logger.info(rsmd.getColumnName(i)+": "+rsmd.getColumnType(i));
st = (oracle.sql.STRUCT) rs.getObject(1);
//convert STRUCT into JGeometry not available in OracleXE
//JGeometry j_geom = JGeometry.load(st);

//JTS to the rescue
OraReader reader = new OraReader();
Geometry geom = reader.read(st);
Coordinate[] coords = geom.getCoordinates();

Next stop, Amazon AWS EC2. Here is a list of the public Oracle AMIs offered::
   Oracle Database 11g Release 1 Enterprise Edition – 64 Bit
   Oracle Database 11g Release 1 Enterprise Edition – 32 Bit
   Oracle Database 11g Release 1 Standard Edition/Standard Edition One – 32 Bit
   Oracle Database 10g Release 2 Express Edition – 32 Bit

The last in the list, OracleXE edition, is the one to experiment with, unless you have a spare Oracle license floating around.

Time to try it:
  C:\>ec2-run-instances ami-7acb2f13 -k gsg-keypair
  C:\>ec2-describe-instances i-??????

and login:

Use of this machine requires acceptance of
the following license agreements.
 1. Oracle Enterprise Linux


 2. Oracle Technology Developer License Terms


 Please enter the above URLs into your browser and review them.
To accept the agreements, enter 'y', otherwise enter 'n'.
Do you accept? [y/n]: y
Thank you.

You may now use this machine.
Welcome to Oracle Database on EC2!
This is the first time this EC2 instance has been started.

Please set the oracle operating system password.
Please specify the passwords for the following database administrative accounts:

SYS (Database Administrative Account) Password:

Now for the link to Apex on the new OracleXE instance:

Fig 3 – Oracle Apex running from an EC2 OracleXE instance

Looks like we have it.

Oracle is the Big Daddy of spatial GIS. It is also the “Mother of all DBA complexity.” Running a spatial app with oracle in the background is not trivial, but it is getting easier. The EC2 OracleXE AMI makes starting an Oracle server instance a matter of minutes. Although lacking some of the capability of its free and open source competition, OracleXE can be useful for the garden variety web enabled spatial app. For the developer with lots of experience in Oracle, OracleXE provides a low cost entry onto the performance/price escalator.

Next on the agenda is adding SDO_GEOMETRY data along with some kind of real spatial rendering, which means in my case getting a tomcat server running with Geoserver on the same OracleXE instance. Alternatively it might be worth a try at installing the OracleXE .rpm on an AMI with a GIS stack already available. And, it will be useful to recompile ogr with oracle db support.

Of course the real mix and match challenge will be OracleXE on an EC2 (real soon now) Windows instance with Java, Tomcat, Geoserver serving a Google Map control coupled to Google Earth, OpenLayers, VirtualEarth. But really EC2 Windows will probably come preconfigured with the new MS SQL Server 2008 and all the promised geospatial goodies including Linq potential.

After just a short trip into the Ellison Empire, I must admit I still like the no frills PostgreSQL/PostGIS better.

Comments are closed.