ORA-13226: Interface not supported without a spatial index.

I hate that error.  Someone would give me a new Oracle dump file.  I’d try to view the spatial data and boom - ORA-13226.  If I was lucky, there was only a couple of tables in it.  Chances are, there were hundreds of tables in it.  Sometimes I’d need to add the reference to the USER_SDO_GEOM_METADATA view, sometimes I wouldn’t.   Sometimes the index would already exist and just need to be rebuilt.

Regardless of the scenario, it would often involve a lot of SQL – or messing around to try and remember the SQL.  Every time this would pop up I would think to myself, “I really should write an app…”.  Well I finally did.

So, here is my announcement.  The RADE Spatial Indexer is just about ready for beta.  Initially this app does three things.

• It will list tables and views with geometry columns that are not part of the USER_SDO_GEOM_META data view and help you add them.
• It will list spatial tables without indexes and help you create them.
• It will list spatial tables with indexes and help you re-index them.

No more exporting queries to text files and using a macro to edit them to build your SQL for rebuilding those indexes.  I’ve tried to add some nice friendly helpers, such as a button that will suggest the spatial metadata settings for a table based on the existing meta data records.  The ability to mass create or rebuild indexes is also been a fantastic helper.

I’m recruiting brave individuals for a small closed beta program that will begin in about two weeks.  If you are interested in being a tester please e-mail indexer (at) webrade.com and let me know.  Alternately you can contact me with the contact me link on the blog here  Remember, this is a beta – so I really do not suggest you run this on a production, or important server.  That said, I have been using it in various states on my own ‘production’ development Oracle server.

As an incentive for helping me out.  If you submit feedback (be it a bug report or feature request) – I will set you up with a free license once we release 1.0.  I know you’re hooked and want to sign up.  I know it.  Just in case you need a little more, here is a few screen caps..

Look at how easy it could be to define metadata.  Click the suggest button and it will iterate through the metadata settings for other spatial tables in the schema.  Once you have one row defined, use the sync button to set all rows to the same values.  Click process and its done.

Within seconds, have hundreds of spatial indexes being re-created…Creating new indexes is almost as easy.

Over the years, this problem has sporadically appeared (probably once or twice a year).  In the Mapguide 6.5 server admin when creating a new data source the Autodesk Spatial Data Provider for Oracle Spatial doesn't appear as an option.  Every time this comes up I end up wracking my brain to remember what the solution is.  I know I've dealt with this many times - but I cannot remember the details.  Usually it's occurring on a customer's production server which is not a place I like to mess around.  This time, it was my development server - let the messing around begin!

In a nutshell this is a result of installing Mapguide server before the Oracle client is installed on the server.  The solution is to do a repair install of Mapguide Server (or a re-install if you prefer) once the Oracle client is installed.  Hopefully next year when this comes up again - writing this down will help me remember.  Worst case Google might pick this up and I'll find my own post when I search for it...

I had another problem with Mapguide Enterprise 2009 recently pertaining to objects not being selectable.  Unlike my previous post on errors caused by invalid FDO filter conditions while dynamically authoring layers, no errors were logged to the Mapguide server logs.  This problem is very likely specific to the enterprise version, as I was using the Autodesk FDO Provider for Oracle.

So a data connection was created pointing to the Oracle 10g schema.  This database contained a number of tables containing Lat/Long point geometry entities.  I created a layer pointing to the table in question.  I created a layer pointing to that data connection.  At this point the entities showed up as expected in the both the DWF and the AJAX viewers.  Once again, I could not select any of the entities.  I checked the server logs, no errors were reported.

In troubleshoot mode, I tried just about everything I could think of but nothing would work.  I simplified the theme, created new layers, removed all other layers from them map.  Eventually I created a new layer from another table.  Luckily, the entities on this layer could be selected.  It must be a problem with the underlying data.  On a hunch, I checked the table definitions - specifically looking at primary keys.  The selectable layer had a primary key defined, the problem layer did not.  (Good thing I cannot take credit for creating this source data =))

So, I created a primary key on the problem table, did a touch on the layer definition (opening the definition in Studio and saving it without any changes) and then left for twenty minutes.  I'm thinking there is some sort of caching going within the server, and I'm not sure how it works.  Immediately reloading the map after adding the primary key did not work - the entities were still not selectable.  When I came back - the items in the viewers were now selectable.

So long story short, if again you cannot select map entities using the Autodesk FDO Provider for Oracle ensure that the source table has a primary key defined.  Hopefully this saves someone some grief =)