Announcing: FDO Toolbox 1.0 beta 2

Here it is! The 2nd beta of FDO Toolbox 1.0

This contains many new changes from the 1st beta, some of the notable features include:

Unified data store editor

All the of schema and spatial context management functionality is now merged into a single user interface

As you can see from the above screenshot, instead of trying to win the losing battle against the WinForms PropertyGrid, all schema element editors are now specialized user interfaces.

Because we have now unified everything, we have all the necessary information needed to create full XML configuration documents. Therefore, exporting to XML will now export the full XML configuration document (Spatial Contexts, Logical Schemas and Physical Mapping).

Thus the unified data store editor doubles up as a visual schema override editor. I'll elaborate on this in a future post.

Just like the original schema editor, this functions also in a disconnected mode, free of any restrictions imposed by FDO provider capabilities.

The unified data store editor is not fully implemented yet. Some features are missing or not fully tested, such as:

• Object Property support
• Association Property support
• Class inheritance support
• Logical-Physical mapping configuration

Despite these missing features, the unified data store editor should fulfill most common data modeling scenarios.

SQLite-driven join operations

The old join engine has been replaced by one backed by SQLite. This not only simplifies the whole implementation, but performs much faster since we delegate all the complex join logic to SQLite.

In addition, join operations now support filters on both the left and right sides of the join.

More express support for RDBMS providers

The beauty of most FDO RDBMS providers deriving from the same core, is that the same concept of re-use can be applied as the user interface level as well. As such supporting additional FDO providers based on the GenericRdbms core is a piece of cake.

The express module now has support for creating the following data stores

• MySQL
• PostgreSQL/PostGIS (via the new OSGeo.PostgreSQL provider)

Also included is support for connecting via the commercial Autodesk FDO providers (as these are also based on GenericRdbms):

• SQL Server (using Autodesk.SqlServer provider)
• Oracle (using Autodesk.Oracle provider, experimental)

These commands are only available if you have the aforementioned providers already registered in your providers.xml file.

Bulk Copy UI enhancements

The process of creating bulk copy tasks is now faster than ever.

Add All Connections option

I have discovered from my common usage patterns in creating bulk copies is that I want to add all connections that I currently have open in the Object Explorer. So I've added an option to Add All Connections so you no longer have to painstakingly add each connection individually.

Auto-map properties option

Another thing I discovered, especially when bulk copying to freshly created data stores is that most of the time, I will be mapping properties to identically named properties in the target feature class (and have them created if they don't exist). As such, I've implemented an Auto-map context menu option, which will map each un-mapped property to a property of the same name, with the create if not exists option set to true. This saves lots of time if your source feature class has 50+ properties.

Other changes

Rounding out the other changes:

FDO updated to 3.5.0 RC2

FDO has been updated to the latest RC2 version. Most notable thing here is the ArcSDE provider, which finally implements FDO RFC23. This implementation dramatically cuts down the time needed to perform a schema walk on an ArcSDE data store.

Here's how long it took to schema walk the Utah gov't SGID public database with a pre-RC2 provider:

Here's how long it takes to schema walk the same database with the RC2 provider:

An 18x improvement! No need to take a coffee break waiting for the schema to fully load.

Friendlier connection names

When you drag and drop a supported file into the Object Explorer, it will normally create a connection name of the form: [Provider]_[Number]

Now the naming algorithm is more refined so that for file-based providers, it will create a connection name in the form of: [Provider]_[FileName]

Miscellaneous Fixes and Changes

• Fix "create class of same name" option not working when target schema already has classes
• Disable configuration section in the Generic Connect UI for providers that don't support configuration
• Reader fixes for decimal properties
• Make the Bulk Copy UI more flexible and resizable
• Prevent connection removal on active Bulk Copy and Join UIs
• Handle connection renaming on active Bulk Copy and Join UIs
• Prevent connection removal if open tasks depend on it
• Bulk Copy process no longer tries to execute a delete on a non-existent target class

Download

Report Issues/Enhancements

How to: auto-invoke an AJAX viewer command on startup

I've recently put up a new code sample to illustrate how to automatically invoke a specific AJAX viewer command on startup.

This uses the Initial Task Pane URL property of the Web Layout to load our autostart page, which then uses the AJAX viewer JavaScript API to locate the named command and execute it if found.

This will work with any command type, you just need to know the name (not the label) of the command.

An interesting find

This is the root directory of the Autodesk MapGuide Enterprise 2011 install image



Let's take a look inside...


Either this was included to satisfy open source licensing requirements (IANAL) OR it makes building MGE-compatible extensions much easier

What do you think?

SQLite: Is there anything you CAN'T do?

One of my key areas of focus in preparation for the next beta release of FDO Toolbox 1.0, was to bring the Join implementation up to par. Since all my efforts thus bar has been improving the Bulk Copy functionality, joins have been somewhat left out in the cold in terms of functionality and my attention.

As it stands, the primary problem with the current join implementation is one of performance. Without going into detail the current implementation is one that uses nested loops. This, being a O(m*n) operation will get slower as the size of the data you're working with increases.

In thinking about the different join algorithms available to tackle this problem, I came to the conclusion that implementing what is essentially a core DBMS component is just too much effort, so being the lazy programmer that I am, I've updated the join implementation to delegating the join functionality a DBMS which day-by-day continues to impress me with its amazing performance and capabilities: SQLite

So now, the updated implementation can be described as follows:

• Create a temporary SQLite database with table structure identical to our "left" and "right" sources.
  
• Pump the data from our "left" source into this temp SQLite database
• Pump the data from our "right" source into this temp SQLite database
• Create a view in our temp SQLite that encapsulates a join between these "left" and "right" tables
• Do some metadata hacks to make this view be recognised as a FDO feature class.
  
• Pump the data from this view out to our target data store.
  

How much faster is this approach? I've done some tests by joining the Sheboygan Parcel SHP file (only the bare ID/Geometry attributes, approx. 17600 records) with the corresponding Parcel data in an Access Database (approx. 16100 records):

• Old approach: 16 minutes
• SQLite approach: 21 seconds!

That's a 45x performance improvement! Now it is true that this is only a single data point :-), but if a single data point can indicate such a monumental improvement, then I can comfortably say that you will get similar results with different data sources.

I truly <3 SQLite!

Announcing FDO Toolbox 1.0 beta 1

Unlike commercial software, the 1.0 version number in the open source world usually signifies that that software is "complete", has all major features, and is considered reliable enough for general release.

In the nearly 2 years of development, I have come to the belief that FDO Toolbox fulfills this criteria and thus is ready for the v1.0 designation. This is the first of many beta releases, which will round out some of the remaining items to be completed.

This first beta release introduces the following new features:

• Bulk copy now supports on-the-fly schema modifications. If copying to non-existent classes and/or properties, these will be created before copying begins, but only if the provider in question supports such capabilities.
• Express support for creating SQL Server Spatial data stores.
• Support for executing arbitrary SQL statements for providers that support SQL commands.
• New menu commands to open the Log and Session directories
• FdoUtil.exe now supports a -log parameter for the RunTask and BulkCopy commands, allowing error logs to be written to a file name of your choice.
  

Download

Report Issues/Enhancements

Using FDO Schema Overrides

One of the more esoteric features of the FDO API is the ability to apply schema overrides via an XML configuration file.

Unfortunately this feature is not really well documented, so this blog post will hopefully make it much easier to understand how to use this feature.

What are Schema Overrides?

Schema Overrides allow you to define or override the following aspects of a FDO data source or MapGuide Feature Source:

• The spatial contexts for this data source
• The logical schema (schema/class/properties) of this data source
• The mapping of this logical schema to the underlying physical storage (class -> table, property -> column)

This information is specified in a XML-based configuration file

How are Schema Overrides used?

For FDO providers like ODBC, there is no actual concept of:

• Geometry properties
• Spatial Contexts

In this case, schema overrides can be used to "declare" point geometry properties (based on X/Y/Z columns) and what spatial context these points represent. The ODBC feature source table editor in MapGuide Studio/Maestro is a specialized editor of this kind of schema overrides.

Another use, which I think is not quite known is to override how FDO interprets the tables and views in your database, especially views.

A practical example

One of the common needs in any presentation of data is the ability to have one set of data linked to another set of data. In MapGuide, Feature Joins can be used to achieve this effect.

But the performance of Feature Joins can range anywhere between fair and excruciatingly slow (especially when themes are involved), and as such I generally do any of the following:

1. Pre-join the related bits of data before loading it into MapGuide
2. If both sets of data come from the same database, do the join at the database level and encapsulate the result in a view.

Our example covers point #2.

Consider the following SQL Server database:

























Suppose we want to have a view (called VParcels) that represents the Parcel tables and any related records from the ParcelData table. You would normally do a join similar to this:




















Now if we look at this database through FDO Toolbox, you'll now see that our view is represented as a feature class:

















But we have a problem, if we make a layer off of this we can't select any objects on it. Look at the ID property:




















It isn't an identity property. If we create a layer from this feature class we won't be able to select objects on it because identity properties are the mechanism by which MapGuide knows which particular objects have been selected. In fact, 90%-95% of most problems where layers aren't selectable can be boiled down to a violation of this one basic rule:

No identity properties = unselectable layers

So now we know the problem, how can we fix it? We could index this view, by applying a unique clustered index (the only valid index that can be applied to a SQL server view, other DBMSes may be different), FDO (for SQL Server) will interpret the index as the identity properties.

But a unique clustered index requires that the view only use inner joins which may be a problem especially if you want to show parcels that have no related records (ie. a left outer join).

Enter FDO Schema Overrides to the rescue.

FDO Toolbox 0.9.6 introduced a new command that allows you to dump the default schema mapping to an XML file. What we will do is dump a default XML configuration, and then do the following:

• Edit this configuration file
• Test this configuration in FDO Toolbox
• Loading this configuration file to MapGuide
• Testing this configuration in MapGuide

So it goes without saying, you'll need FDO Toolbox v0.9.6 or later to do this.

If you right click our SQL Server connection, you will see a new Dump Schema Mapping command on the context menu. This command will create an XML configuration file containing the following information:

• All the spatial contexts in this data store
• The entire FDO logical schema
• The logical to physical schema mapping

If you open this file it will look something like this (outline view):











All XML configuration files have the same structure:

• They all start and end with the fdo:DataStore element
• All spatial contexts are indicated with the gml:DerivedCRS element
• The xs:schema elements indicate the FDO logical schema
• Finally the SchemaMapping elements indicates how the logical schema maps to the physical schema. These elements are provider specific
  

Look at the logical schema section and you'll notice a pattern:

• Feature Classes are denoted by the xs:complexType elements
• Identity Properties of the Feature Class are denoted by the xs:element element declared directly above the matching xs:complexType element.

If you look closely there is no xs:element declaration for the xs:complexType of our view:

So let's override this default logical schema by declaring an identity property for our view. The xml fragment follows this pattern:

<xs:element name="CLASS_NAME" type="CLASS_NAMEType" abstract="false" substitutiongroup="gml:_Feature">
<xs:key name="CLASS_NAMEKey">
<xs:selector xpath=".//CLASS_NAME" />
<xs:field xpath="IDENTITY_PROPERTY_NAME" />
</xs:key>
</xs:element>

Where CLASS_NAME is the FDO feature class name and IDENTITY_PROPERTY_NAME is the name of the identity property in the FDO feature class. We'll add one such fragment for VParcels:



















Now let's save it and load up another SQL Server connection to the same database. You'll notice that the express dialog has a field allowing you to specify an XML configuration file. We'll use our saved configuration file.














Now connect and take a look at VParcels from the configured connection:























We now have identity properties for our view! Assuming we have an existing SQL Server feature source (without a configuration). Let's run a validation test on it, you'll get something like this:


















Notice that MapGuide also can't get the identity properties for VParcels (because it too uses FDO). Now let's edit the configuration of this feature source. In the feature source editor for Maestro, click Edit Configuration Document and we'll get an XML editor, paste the contents of our XML configuration file in here and save it.




















Now let's try validating again:


















That problem is gone. We have now successfully applied a schema override! MapGuide can now read the identity properties from our view. Just to doubly make sure, let's see if we can make a selectable layer now. I'll make one themed by whether there are related records (black = has related record, red = no related record). Remember that our view is based off of a left outer join so there may be parcels that have no related records.






















Now let's house it in a map (switch on selectability) and preview it.



















We now have a selectable view-based layer! Now having selectable views comes with a data modeling caveat:

We are only declaring these properties/columns to be unique to MapGuide. You have to ensure at the DBMS level (via constraints or other means) that the actual columns declared will be unique, otherwise selecting a feature may not return the correct set of attributes that you were expecting!

Hopefully this better explains how you can use schema overrides to change how FDO/MapGuide looks at your data store. I'll eventually have some kind of visual editor for FDO Toolbox so you don't have to do any painstaking XML editing by hand.

On a side note, as I've demonstrated with this post FDO Toolbox is a very useful tool for diagnosing problems with MapGuide Feature Sources and other problems that may not be readily apparent through looking at error logs. Almost every time I do a (windows) deployment of MapGuide (Open or Enterprise) I'll always install a copy of FDO Toolbox (and Maestro) as well, because having something on hand to be able to look at your data is very useful.

FDO Toolbox v0.9.6

This is a quick bugfix release with the following changes:

• Bulk copies to SQL Server now use regular FDO insert again. For best results set the ForceWKB option to true when copying data to SQL Server.
• Much improved performance when loading a bulk copy definition file for execution, if a bulk copy definition copies data between connections with large schemas, this performance gain should be readily apparent.
• A new ListBcpTasks command for FdoInfo.exe which lists all copy tasks in a bulk copy definition.
• A new Dump Schema Mapping command which will dump a default xml schema override configuration file ready for editing.

Download

Report Bugs / Enhancements