I hate Outlook 2007. I don't like using it. Unfortunately, like many other public servants, I have little choice in the matter until I get home and open my macbook pro.

There are two big issues with it that I can't stand. The first is the rubbish RSS support that it has. I mean, RSS is pretty standard these days, right? So why, if you are subscribed to a few RSS feeds in Outlook, does it freeze the whole program every second or two? Because it's crap (well, actually because it's constantly synchronising with Internet Explorer). The only way to get around it is to turn off RSS. Not a good option for me - I follow many blogs that I take no shame in plugging, including Paul Ramsey, James Fee, Ray Camden and SlashGeo. I don't want to run a separate RSS reader because I like that it's integrated with my email, so I put up with it.

Second, I can't stand emails that are of a simple, textual nature, yet sent in HTML. Even if you have Outlook set up to compose in plain text, if someone sends you a HTML email and you hit reply, you will be composing in HTML. I'm an active member of a number of mailing lists, and find that HTML really clutters them up. It's much easier to keep track of a conversation if the entire thread is in plain text.

Outlook QuoteFix was a great solution with Outlook 2000, but unfortunately it doesn't work with Outlook 2007. However QuoteFix Macro does. I recommend it. To use it you have to turn on the option to view all messages you receive in plain text (which I like). But never fear, if you do get an email that is supposed to be viewed as HTML you can on a per-email basis by clicking on "This message was converted to plain text" at the top of the message, and choosing "View as HTML".

I put together a small Geotools based jar that Coldfusion can use to copy data from Oracle Spatial directly into PostGIS. Previously, we had needed to export the data as a shapefile, and use shp2pgsql to load the data into PostGIS.

That method had an ESRI imposed limitation, in that the length of shapefile attribute names can only be up to 10 characters long, whereas Oracle column names can be much longer, so they were truncated. No good!

Even though Geotools made it ridiculously easy to implement, it turns out I didn't even need to do it. I should have thought of ogr2ogr in the beginning! Of course, it supports Oracle Spatial and PostGIS, and its main function is to convert features from one data type to another. Anyways, here's how you would do it.

First you need to download ogr2ogr with the OCI driver. You can get it from FWTools but I found it easier to use the one distributed with OSGeo4W, from OSGeo, becuase it already includes the OCI driver.

The OCI driver uses a thick connection to your Oracle database, so you need to have the Oracle Instant Client or SQL Developer installed on the machine that you wish to connect from. This doesn't have to be the PostGIS machine or the Oracle machine - it can just be your normal desktop. Anyways, you need to copy the oci.dll that comes with your Instant Client installation to the "bin" directory of OSGeo4W.

Also, make sure you can connect to your Oracle database using the instant client. If you can't do that, you need to edit your TNSNAMES.ORA until you get it right.

Now you should be able to use ogr2ogr to connect to oracle using the name you gave it in your TNSNAMES.ORA, and transfer spatial tables to PostGIS, using this command (on one line):

Code:

It looks complicated, but it's really not.

Open source software wins again. Why does anyone still use shapefiles again?

An issue came up on the Geoserver users mailing list the other day, which I myself have had in the past. It goes like this:

Lets say you have a table a and table b that you want to publish through Geoserver. a belongs to schema GIS1 and b belongs to schema GIS2. Now, it would be nice to be able to set up one datastore that can connect to Oracle with a username that has access to both tables, and pool connections between them.

Unfortunately it doesn't quite work like that.

The reason being is that you must specify a schema in the datastore configuration for the Oracle plugin to know how to find the spatial metadata of a table, unless the table belongs to the user that you are connecting with. For example, you could try connecting as user GIS1. If GIS1 has access to GIS2.b, you will be able to see it in your list of available layers in the geoserver layer chooser. If you select it and try to use it, you will most likely get an exception from the Oracle driver, along the lines of:

ORA-00942: table or view does not exist

Why? Well there are two different ways a user can access spatial metadata for tables in Oracle.

1. USER_SDO_GEOM_METADATA - This is a special view that gives a user access to the spatial metadata for all of the spatially enabled tables that they own. It identifies geometry objects that the user owns using columns table_name and column_name. Geoserver will first try to use this view to obtain spatial metadata for your selected table. In our case, GIS1 does not own table b, so it won't come up in the list. So it tries the other technique.

2. ALL_SDO_GEOM_METADATA - This view is available to all users, and shows the spatial metadata for all spatial tables in the Oracle instance. That means that b will be in there, but ALL_SDO_GEOM_METADATA contains one vital extra column that it uses to tell tables apart; the schema_name. Because this view shows tables from all schemas, we need to be able to identify them!

Now, geoserver won't remember that b comes from a schema other than GIS1, which we are trying to access it with. So it will find the appropriate metadata in ALL_SDO_GEOM_METADATA, but it will only check for the table using table_name and column_name, which will not uniquely identify it, if there is another table called b in another schema!

That won't be a problem if b is the only spatially enabled table in the Oracle instance that is called b. What will be a problem is when Oracle tries to use the table. If you have no schema specified in your geoserver datastore, geoserver (well, geotools, actually) will test access to b like this:

SELECT MY_PK FROM B WHERE 0=1

That will result in the oracle error ORA-00942, above.

So how do we get around all this? I really want to access multiple schemas in geoserver but I only want to connect with one username so that I can pool connections across schemas!

JNDI.

Using JNDI you can set up a datasource in your application container (i.e. Tomcat) that can be referenced by multiple Geoserver datasources. You still need to hard-code a username and password into the connection config for Tomcat, but you can set up a Geoserver datasource for each schema that you want to access, which uses that connection pool, but that allows you to specify a schema name!

And voila, we have got multiple datasources in Geoserver that point to different schemas, using the same username and password for connecting, but we are actually pooling connections across datasources. And best of all Geoserver knows exactly where to find all your tables.

If you need help setting up connection pooling for Geoserver, they have a great guide in amongst the rest of their super documentation.

Tad Larsen wrote a great article for Directions Magazine yesterday, discussing  the two very distinct categories of GIS developers, that often get labelled the same thing: GIS Developer.

He mentions the GIS developer with a background in GIS, that has learned some aspects of programming, and the other kind, the developer with a background in Computing who has learned some aspects of GIS.

I fall into the second category, but get the feeling that there are many more who fall into the first. Either way, he makes a very valid point: There are very few GIS developers in this world that are experts in both fields. But realistically we are better off that way.

I liken it to going to a large supermarket to get your bread and vegetables. You will get bread and vegetables, but they'll be that little bit better if you get your bread from a bread shop and your vegetables from a grocer. What makes it interesting is if the bakery knows a bit about vegetables and then uses the grocer's knowledge expertise, resulting in a fantastic sandwich!

Thanks Tad, for a great article.

It was a showdown between the three different methods to compare geometry in Oracle today. My lesson for the day was what the subtle differences are between SDO_RELATE and SDO_GEOM.RELATE.

I had a left outer join to a table with a geometry attribute  in it. If there was no matching record in the table I was joining to, it would return null for the geometry:

SELECT a.id, b.name, b.geometry
FROM   voyage a
LEFT OUTER JOIN voyage_track b ON b.voyage_id = a.voyage_id

OK, simple stuff, but I wanted to only return voyage tracks that intersect with the MBR of a user-specified geometry. To make things difficult, I also needed to return any records that ended up with a null geometry. So in other words, I needed to filter out only the records that have a geometry, which don't intersect with our user-specified MBR.

We can rule one method out straight up: SDO_FILTER. Why? SDO_FILTER is fast but it compares the MBR of both geometry objects that you feed it. If I have a voyage track that goes from Tasmania to New Zealand the MBR is going to be quite large, but I just wanted to see if the voyage had passed through a particular place.

Next I tried SDO_GEOM.RELATE, using the 'ANYINTERACT' property:

SELECT a.id, b.name, b.geometry
FROM   voyage a
LEFT OUTER JOIN voyage_track b ON b.voyage_id = a.voyage_id
WHERE  (
    b.geometry IS NULL
) OR (
    SDO_GEOM.RELATE (
       b.geometry,       
       'ANYINTERACT',
       SDO_GEOMETRY(2003, 8307, NULL,SDO_ELEM_INFO_ARRAY(1, 1003, 3),
           SDO_ORDINATE_ARRAY(131.563, -65.246, 134.421, -62.388)
       ),
       0.000005
    ) = 'TRUE'
) 

One would have thought that this would be a reasonable way to approach the task. ERRRRR: fail. Why? Oracle, for some reason, doesn't always check what the left predicate of an OR statement evaluates to first, before trying the right predicate. I mean, think about this statement:

IF (a OR b)

Now, do you think it would be optimal to continue evaluating what 'b' is if you already knew 'a' to be true? Well guess what, Oracle does, well, sometimes. So even if my geometry was null, it was still trying to compare it with a real geometry object using SDO_GEOM.RELATE. This confused me somewhat!

ORA-13050: unable to construct spatial object

Looking further into the docs I find:

A null value (empty geometry) as an input parameter to a geometry function (for example, SDO_GEOM.RELATE) produces an error.

Enter SDO_RELATE. It turns out that Oracle have three functions that do more or less the same thing in three different ways, and yielding three different results. Oracle say:

Unlike with the SDO_GEOM.RELATE function, DISJOINT and DETERMINE masks are not allowed in the relationship mask with the SDO_RELATE operator. This is because SDO_RELATE uses the spatial index to find candidates that may interact, and the information to satisfy DISJOINT or DETERMINE is not present in the index.

OK, fine. So I gather that means SDO_GEOM.RELATE will accept two geometry objects that you feed it and will not work as efficient as SDO_RELATE it whereas SDO_RELATE is optimised to use indexes on existing tables to help out with the query. So I tried using SDO_RELATE and it started working:

SELECT a.id, b.name, b.geometry
FROM   voyage a
LEFT OUTER JOIN voyage_track b ON b.voyage_id = a.voyage_id
WHERE  (
    b.geometry IS NULL
) OR (
    SDO_RELATE (
       b.geometry,
       SDO_GEOMETRY(2003, 8307, NULL,SDO_ELEM_INFO_ARRAY(1, 1003, 3),
           SDO_ORDINATE_ARRAY(131.563, -65.246, 134.421, -62.388)
       ),
       'mask=anyinteract'
    ) = 'TRUE'
) 

Well, yippee, it actually returns null if there isn't any geometry, and for all other rows it returns only the ones with geometries that intersect.

Now, just to explore a bit further, I tried SDO_GEOM.RELATE again, and, uh... even though the docs contradict it... it works with a null value passed into it. I think I'll stick to SDO_RELATE for now though - it at least uses the spatial index on the voyage_track table.