Wednesday, 29 July 2020

Back into the groove of MapGuide things

Getting back into the groove of MapGuide development to finally make some progress on that long arduous journey to MapGuide Open Source 4.0

First item on the books is a small improvement to WMS GetMap. Namely, I don't think you will need to whip out a dedicated WMS client to preview your WMS layers anymore after this, we can just generate an OpenLayers viewer for you straight from the mapagent so you can see for yourself!




Monday, 20 July 2020

Announcing: vscode-map-preview 0.5.5

This release adds partial support for the Mapbox SimpleStyle spec for GeoJSON features




Many thanks to @vohtaski for the PR to add this support.

The other new feature is a new opt-in configuration property to de-clutter vector feature labels. To illustrate, here's a point KML preview with de-cluttering disabled (current behavior)


And here's the same KML preview with de-cluttering enabled


The other points/markers will be visible as you zoom in where there will be more "breathing space" for OpenLayers to draw the extra labels.

This release also updates:

  • OpenLayers to 6.3.1
  • ol-layerswitcher to 3.5.0

Tuesday, 7 July 2020

Announcing: FDO Toolbox 1.5

Another release already? Yes indeed!

There were 2 reasons for this new release.

Firstly, the previous 1.4 release had a major oversight where the windows installer did not bundle a required dll needed for the new FdoCmd tool to work, so it was totally broken out of the box in both the 1.4 and 1.4.1 releases. This release now properly bundles the missing dll, making this tool finally operational for the first time for you all.

Secondly, this release include a new major feature that was originally slated to be part of the 1.4 release, but got shelved due to stability issues around the FDO .net wrapper that was resolved with the 1.4.1 release. And for this announcement post, I'll be talking about this feature in great detail.

That feature, is the long overdue integration of MgCoordinateSystem from the MapGuide API for coordinate system support. Here's how we use the MgCoordinateSystem integration in FDO Toolbox.

Transforming Features in Bulk Copy

Let''s start with the obvious place, bulk copying now supports optional transformation of geometry features. This is expressed in several different ways.

In the main bulk copy editor, when specifying a spatial context override with a different coordinate system WKT like so.


There is now a new flag that you can toggle to indicate that this override should be interpreted as an instruction to transform geometries for the source coordinate system WKT to the coordinate system indicated by the override WKT.



This method of enabling transformation may look a bit un-intuitive if you're accustomed to using tools like ogr2ogr where you state upfront what the source CS is and what target CS you want to transform to. The reason for this is due to the concept of spatial contexts in FDO. In FDO, coordinate systems are not set explicitly, they are inferred through its spatial context. Thus the UI to enable transformation stems from the constraint imposed by FDO's spatial contexts. We can't set a source or target CS, we have to override what is inferred by FDO.

When using the SDF/SQLite dump context menu command, the UI has an option to allow transforming the dumped features to the specified coordinate system.



When you click the Pick CS button, a dialog appears that you should be familiar with. It's the same coordinate system picker dialog from MapGuide Maestro and fulfills the same purpose here in FDO Toolbox as it does in Maestro.



Streamlining Various UI

Any UI that deals with spatial contexts now take advantage of the newly integrated coordinate system picker to help auto-fill most, if not, all of the fields in question.

For example, creating a spatial context now can use the coordinate system picker to auto-fill in most of the fields in the UI



Similarly, when creating a new RDBMS data store, you can use the coordinate system picker to pre-fill the sections about the coordinate system and extents



And from the previous bulk copy example, you can easily override a source spatial context by picking an existing coordinate system.



FdoCmd enhancements

Not only will you finally get a functional FdoCmd tool in this release, it has also been enhanced with various integrations with the new coordinate system functionality.
  • Any command that outputs geometry data will have new options to allow transforming it to a target coordinate system
  • Creating spatial contexts can use an existing coordinate system to fill in most of the required spatial context information.
  • The bulk copy commands has support for transformation and can use an existing coordinate system as the basis for specifying the WKT of any override spatial context
  • When listing spatial contexts, we'll now use the new coordinate system facilities to resolve/display the corresponding mentor/EPSG code for any coordinate system displayed
Also FdoCmd receives a whole series of new commands for interacting with the coordinate system facilities:
  • enumerate-cs-categories for listing all categories from the coordinate system catalog
  • enumerate-cs for listing all coordinate systems for a given category
  • find-cs-by-code for fetching and displaying details of a coordinate system from an input mentor CS code
  • find-cs-by-epsg for fetching and displaying details of a coordinate system from an input EPSG code
  • wkt-to-cs-code for obtaining a mentor CS code from an input coordinate system WKT
  • wkt-to-epsg for obtaining an EPSG code from an input coordinate system WKT
  • cs-code-to-wkt for the inverse of wkt-to-cs-code
  • epsg-to-wkt for the inverse of wkt-to-epsg
  • is-valid-wkt for validating coordinate system WKT strings
A small caveat

If you've used CS-MAP (or its MgCoordinateSytem wrapper in MapGuide), you'll know about its legendary support for nearly every coordinate system in existence on Planet Earth.

But that support comes at a price: The size of our installers/installation suffers as a result.

To support so many coordinate systems requires country-specific grid data files that are:
  • Huge
  • Difficult to compress due to their binary nature
So to avoid making this installer a 300MB file, and making it a 30MB file instead, this release of FDO Toolbox only bundles the core CS-MAP dictionary data files and omits all of the country-specific grid files. Such files are available for download as a separate zip file on the 1.5 release page.

If you need these grid files for your coordinate system transformation (most likely, you are transforming from one CS to another and it is failing because it is looking for a certain grid file), just download the zip file (from the releases page) and extract the contents into the Dictionaries directory of your FDO Toolbox installation.

Other changes

  • FDO Toolbox now ships with libpq.dll and libmysql.dll (dlls courtesy of the VS2015 build of GDAL/OGR from gisinternals) allowing for the MySQL and PostgreSQL providers to work out of the box without having to source these dlls yourself. King Oracle provider still requires you to source the Oracle 11g Instant Client binaries yourself.
  • When bulk copying, we no longer try to create spatial contexts for names that already exist on the target connection
  • More cases are handled when trying to convert an incompatible feature class when applying a schema to a target connection

In Closing

Barring bug fix releases to address any critical issues that show up after this release, I believe that FDO Toolbox 1.5 will be the last major release of FDO Toolbox I will be putting out for a while and the project will most likely return back to hibernation. I had restarted this journey a few months ago to address some long standing pain points that had built up and with the completion of this coordinate system integration in this release, I feel this journey is now complete ...

... Until another series of annoyances and pain points builds up to critical mass in 5/10 years time perhaps :)

Thursday, 11 June 2020

Announcing: FDO Toolbox 1.4.1

This release should dramatically improve stability of FDO Toolbox and the new FdoCmd CLI when working with SQLite data files.

In fixing the stability problem, it also finally gave me the ultimate insight into the proper usage patterns around the FDO .net API. I had made my concerns known over a decade earlier about the flakiness of the FDO .net wrapper and the various answers given didn't quite give me a solid set of "rules of thumb" to avoid.

What lit the light bulb was looking the FDO SQLite provider codebase once more. I noticed that the main connection class not only implements the main connection interface, it also implements several other interfaces for convenience. The other insight was that there are several places in the FDO Toolbox code that it would be possible that a reference to a capability or a property dictionary (off of the capability, or some other top-level connection property) would out-live the underlying connection which meant that when it came time for the .net GC to start cleaning up references, it would call the finalizers on these references which would then proceed to subtract the ref count on the underlying native pointer.

But because in the case of SQLite the connection implements several FDO interfaces, we may be subtracting a ref count of a pointer to a non-connection interface, but the underlying implementation is actually the connection itself, causing an access violation from tearing down the same connection more than once or tearing down something that is connected to the torn down connection.

I didn't have full evidence to confirm that the above was indeed the case, but it was a solid enough theory that was backed by my observations in running isolated snippets of C# code using the FDO API targeting the SQLite provider.

Here's an example of a crashing snippet:


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
using OSGeo.FDO.ClientServices;
using OSGeo.FDO.Commands;
using OSGeo.FDO.Commands.DataStore;
using OSGeo.FDO.Connections;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FdoCrash
{
    class Program
    {
        static void Main(string[] args)
        {
            var conn = FeatureAccessManager.GetConnectionManager().CreateConnection("OSGeo.SQLite");
            using (conn)
            {
                if (HasCommand(conn, CommandType.CommandType_CreateDataStore, "Creating data stores", out var _))
                {
                    using (var cmd = (ICreateDataStore)conn.CreateCommand(CommandType.CommandType_CreateDataStore))
                    {
                        var dict = cmd.DataStoreProperties;
                        foreach (string name in dict.PropertyNames)
                        {
                            Console.WriteLine("{0}", name);
                        }
                    }
                }
            }
        }

        static bool HasCommand(IConnection conn, CommandType cmd, string capDesc, out int? retCode)
        {
            retCode = null;
            if (Array.IndexOf<int>(conn.CommandCapabilities.Commands, (int)cmd) < 0)
            {
                //WriteError("This provider does not support " + capDesc);
                //retCode = (int)CommandStatus.E_FAIL_UNSUPPORTED_CAPABILITY;
                return false;
            }
            return true;
        }
    }
}


And here's the same snippet, refactored to the point it does not crash with System.AccessViolationException:



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
using OSGeo.FDO.ClientServices;
using OSGeo.FDO.Commands;
using OSGeo.FDO.Commands.DataStore;
using OSGeo.FDO.Connections;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FdoCrash
{
    class Program
    {
        static void Main(string[] args)
        {
            var conn = FeatureAccessManager.GetConnectionManager().CreateConnection("OSGeo.SQLite");
            using (conn)
            {
                if (HasCommand(conn, CommandType.CommandType_CreateDataStore, "Creating data stores", out var _))
                {
                    using (var cmd = (ICreateDataStore)conn.CreateCommand(CommandType.CommandType_CreateDataStore))
                    {
                        using (var dict = cmd.DataStoreProperties) //Dispose the property dictionary asap
                        {
                            foreach (string name in dict.PropertyNames)
                            {
                                Console.WriteLine("{0}", name);
                            }
                        }
                    }
                }
            }
        }

        static bool HasCommand(IConnection conn, CommandType cmd, string capDesc, out int? retCode)
        {
            retCode = null;
            using (var cmdCaps = conn.CommandCapabilities) //Dispose the command capabilities asap
            {
                if (Array.IndexOf<int>(cmdCaps.Commands, (int)cmd) < 0)
                {
                    //WriteError("This provider does not support " + capDesc);
                    //retCode = (int)CommandStatus.E_FAIL_UNSUPPORTED_CAPABILITY;
                    return false;
                }
            }
            return true;
        }
    }
}


And with this snippet, I have come to what I confidently feel should be the "best practices" to using the FDO .net API.

Firstly, dispose of any reference to a top-level connection property (or a sub-property that hangs off of that property) as soon as you are done with it. You must do whatever you can to eliminate the possibility of such references out-living the main connection if/when the .net GC goes to cleanup. The C# using keyword helps streamlining this a lot. Other FDO objects do not need such aggressive disposal (the key point is that they don't directly hang off of the main connection or one of its top-level properties), but you should probably do it anyways out of habit. A thing that may result in confusion (it's confused me for about a decade!) is that disposing a .net FDO object is not the same as disposing a FDO object in C++. In C++, disposing is actual deleting/deallocation of memory, in .net disposing is just subtracting the refcount from the underlying C++ pointer (a way to tell it we're done with the object on the .net side). And because nearly every FDO class/interface exposed to .net implements the IDisposable interface, you are encouraged to dispose early and often once you're done with them.

Secondly, avoid compound statements (some.property.PropertyOrMethod) on anything involving classes/interfaces from the FDO .net API. These are the rules for working with its C++ API (to prevent memory leaks from not housing intermediate parts of a compound statement in smart pointers) and we should probably follow the same rules on the .net side just to be safe.

With these 2 rules established, I did a sweep of the FDO Toolbox codebase to make sure these 2 rules were being adhered to and the end result was that our powershell test harness no longer crashes when involving the SQLite provider, which objectively gave me confidence that this issue was finally addressed.

And that's the little (and hopefully insightful) side-story to pad out this blog post :)

Download

Friday, 5 June 2020

Announcing: FDO Toolbox 1.4

After 5+ years of inactivity, the dam of pain points and personal frustrations finally burst and resulted in 2 months of solid enhancements and quality-of-life improvements, culminating in a new release of FDO Toolbox that I am pleased to finally announce.

Here's the significant changes of this release

FDO Toolbox is now 64-bit only

You should all be running a 64-bit version of Windows by now, so there's no real point trying to make a 32-bit version available.

Bulk Copy Enhancements

The Bulk Copy feature of FDO Toolbox has undergone many enhancements and quality-of-life improvements to make it ever more robust in getting spatial data out of one spatial data store and into another. This post covers all the significant changes.

New FdoCmd command-line tool

This release includes FdoCmd, a much more powerful and flexible command-line tool that replaces the existing FdoInfo.exe and FdoUtil.exe tools.

FDO 4.1

This release of FDO Toolbox ships with FDO 4.1 (r7973). This is pretty much equivalent to the FDO that ships with MapGuide Open Source 3.1.2 with extra PostgreSQL and MySQL provider enhancements made after the 3.1.2 release.

Improved file extension to provider inference

Most of you are probably accustomed to dragging and dropping a .sdf file or a .shp file into the Object Explorer of FDO Toolbox and it automatically creating a respective SDF or SHP FDO connection.

Unfortunately, the list of file extensions that work like this was a hard-coded list. Drag/drop a:
  • .geojson file
  • .csv file
  • .tab file
  • etc
And expect a FDO connection to be created? This was not possible until this release. For this release, we no longer hard-code a list of file extensions, we delegate that out to a new and external FileExtensionMappings.xml file. This file (which you can edit) defines all the file extensions a FDO connection can be created from and this file helps to service:
  • The file drag and drop functionality of the Object Explorer
  • The --from-file command line argument of any FdoCmd verb that requires FDO connection details
Removal of various cruft

5 years is a lot of time in the world of technology so naturally when coming back to this codebase, I took a look at things that were either obsolete or done real half-heartedly and gave them the axe. This includes:

1. Removing specialized connection UIs for Autodesk Oracle and SQL Server FDO providers. I have not touched an Autodesk Geospatial product in many years (where these providers are included), so whether this feature still works or not I do not know, nor do I want to invest in the resource to know (I'm long gone from the Autodesk reseller/partner game, so it's not like I have easy access to these products to find out). The existing open source SQL Server and Oracle providers are more than adequate for this task.

2. Removing specialized connection UI for the legacy PostGIS provider. If you don't know what this is, this was the FDO provider for PostGIS that was replaced by the more robust OSGeo.PostgreSQL provider around the FDO 3.5 timeframe. The legacy provider itself has long since been removed, but the UI for this was still present. Not anymore.

3. Removal of Sequential Process support. This feature (which I probably never documented, but you may see references to it in various UI menus) was an XML-based definition around wrapping calls to the old FdoInfo/FdoUtil CLI tools. Obviously, with the consolidation of these tools into FdoCmd, the Sequential Process support was broken and since it is much simpler to just write your own powershell wrapper around the FdoCmd tool, the choice to axe Sequential Process support was an easy one.

4. Removing all semblances of scripting and extensibility. My original ambitions for FDO Toolbox was for it to be a fully customizable and scriptable Windows GUI application. These ambitions were better realized in MapGuide Maestro, but were left half-baked in FDO Toolbox. Having the opportunity to revisit this codebase, I've come to the conclusion that FDO Toolbox doesn't need customization or an integrated scripting engine.

It is a tool with a singular set of purposes:
  • I want to peek/inspect some spatial data
  • I want to query some spatial data
  • I want to get data in/out of some spatial data source
And in this frame of reference, it was clear that adding in a half-baked IronPython scripting engine was overkill. Maestro has legitimate use cases for an integrated scripting engine, whereas for FDO Toolbox I struggle to find such use cases that cannot be satisfied with this release. In terms of scripting/automation, FdoCmd + powershell is already a combination that can address this niche. So as a result, this release of FDO Toolbox no longer include the scripting engine UI and has closed off the addin system. There just isn't a strong need for such features. 

This release also no longer includes API documentation for the FDO Toolbox core library. Coming back to this codebase, I've found that this library is really just a thin-wrapper on top of the FDO .net API and there isn't much value that the library adds on top. You are better off just using the FDO .net APIs directly or using the new FdoCmd + powershell combination.

In closing

This release has addressed all of my personal pain points and frustrations built up in the last 5 years since the last release of FDO Toolbox, and hopefully it does the same for you!

Thursday, 28 May 2020

Introducing FdoCmd, the missing CLI for FDO

Fixing long time annoyances is not the only standout feature of the upcoming 1.4 release of FDO Toolbox. This release will also introduce a new command-line tool that replaces the existing FdoInfo.exe and FdoUtil.exe tools and combines their functionality into a single, flexible, script-friendly CLI tool.

The original FdoInfo/FdoUtil tools were implemented with the goal of getting a basic command-line experience for FDO functionality without much thought into proper best practices for how CLI tools should behave and work in a shell scripting context.

In the many years since, having accumulated experience in using various CLI tools (in particular, the git command-line client) and .net libraries coming into existence like CommandLine, which makes creating .net CLI applications in the behavioral template of git and friends dead-simple, I saw the re-activation of FDO Toolbox development as an opportunity to do away with FdoInfo/FdoUtil and build a proper CLI tool that can act as a true command-line companion for FDO Toolbox.

And thus FdoCmd.exe was born.

To understand why FdoCmd.exe came to be, it probably helps to explain what my high-level goals were for FdoCmd.exe
  • It has to verb-based.
  • It has to be mostly self-documenting and its functionality easily discover-able.
  • Its output has to be in a form that allows for it to be easily capture/parsing in shell scripts. Since FDO Toolbox is a windows-only tool, we are primarily referring for FdoCmd.exe to be easily driven by Powershell script, powershell being the predominant shell scripting environment for Windows.
  • It has to have functional parity with the core features provided by its GUI counterpart. The normal things you do in FDO Toolbox (the windows application) should be also doable in FdoCmd.exe.
Each goal I'll be explaining in greater detail below.

Verb-based terminology

FdoCmd.exe is a verb-based CLI. What we mean by this is that the first argument you pass to FdoCmd.exe is the verb that you want to run followed by arguments specific to that particular verb. The most famous example of CLI tools that follow a verb-based pattern is the git command line client. 

When using the git command-line client, every command always starts with "git":
  • git add somefile.txt
  • git commit -m "some commit message"
  • git reset --hard
  • git merge origin/master
FdoCmd.exe follows this same pattern for all the functionality it provides.

Self-documenting and discoverability

The verb-based design contributes to the discover-ability of FdoCmd.exe. What do we mean by this? If you run FdoCmd.exe without arguments, you effectively get a "directory listing" of all supported verbs.



From here you know what verbs to use, and you can drill down further. Want to know what arguments you need for the list-schemas verb? Just run FdoCmd.exe list-schemas and the tool will tell you.


And for some verbs, we even provide some examples.



It is this discoverability that lends to the self-documenting nature of the tool. There's really no need to write a dedicated set of documentation for this tool when the tool can tell you itself how its meant to be used.

Shell-scripting friendliness

FdoCmd.exe was designed with the requirement that it should be easy to write shell scripts around the tool. On windows, the predominant shell scripting environment is Powershell, so the outputs of FdoCmd.exe is such that it can be easily parsed and processed in powershell code.

For example, listing the classes of a data store prints each class name line by line.


Which is easily captured as a string array when invoked within a powershell script

1
2
3
4
$classNames = & .\FdoCmd.exe list-classes --provider OSGeo.SHP --connect-params DefaultFileLocation D:\fdo-trunk\Providers\SHP\TestData\Sheboygan
foreach ($className in $className) {
    Write-Host "Doing something with: $className"
}

Where applicable, FdoCmd.exe provides CSV output for its verb, which when combined with the ConvertFrom-CSV cmdlet allows powershell to easily consume FdoCmd.exe output for particular verbs as an array of objects. For example, you can query out features as CSV (along with the usual querying features like filtering and selective output of properties)


Which easily allows for powershell scripts to read features as arrays of objects.

1
2
3
4
5
6
7
8
9
$records = & .\FdoCmd.exe query-features --class Parcels --from-file D:\fdo-trunk\Providers\SHP\TestData\Sheboygan\Parcels.shp --schema Default --filter "RNAME LIKE 'SCHMITT%' AND RYEAR > 1950" --properties FeatId RNAME RYEAR RBILAD RCITY RZIP --format CSV | ConvertFrom-CSV
foreach ($record in $records) {
   $fid = $item.FeatId
   $name = $item.RNAME
   $addr = $item.RBILAD
   $city = $item.RCITY
   $zip = $item.ZIP
   # Do stuff with these values
}

For convenience, querying features through FdoCmd also supports GeoJSON output for easy conversion of feature data to GeoJSON.

If you recall my previous post on splitting out a SHP file to individual GeoJSON files per feature, FdoCmd.exe has all the tools (pun intended) to now do this in a single powershell script!


1
2
3
4
5
6
7
8
9
$srcPath="C:\TestData\national-esri-fe2019\COM_ELB_region.shp"
$featIds = & .\FdoCmd.exe query-features --from-file $srcPath --class COM_ELB_region --properties FeatId --computed-properties FileName "concat(lower(Elect_div), '_new_0_', lower(State))" --format CSV | ConvertFrom-CSV
foreach ($item in $featIds) {
    $fid = $item.FeatId
    $fn = $item.FileName
    $outPath = "C:\TestData\electorates_geojson\$fn.geojson"
    & .\FdoCmd.exe query-features --from-file $srcPath --class COM_ELB_region --filter "FeatId = $fid" --format GeoJSON | Out-File -FilePath $outPath
    Write-Host "Saved: $outPath"
}

Functional parity with FDO Toolbox

Anything you normally do in FDO Toolbox, you should be able to do in FdoCmd.exe as well.
  • Creating/Destroying data stores
  • Introspecting required provider parameters to connect, create, destroy data stores.
  • FDO provider registration/unregistration
  • Walk the structure (schemas/classes) of any data store
  • Listing/creating/destroying spatial contexts
  • Exporting/Applying FDO schemas 
  • Querying features in a feature class
  • Run SQL commands on data stores where the provider supports SQL commands
  • Easily bulk copy features from a feature class to a target data store
  • Executing existing bulk copy / join definition files
Along with some convenience functionality that is exclusive to FdoCmd.exe:
  • Get a total count of features in any feature class
  • Get the extent of any feature class
  • CSV or GeoJSON output for feature/SQL query commands
Where to from here?

As far as I am concerned, when FDO Toolbox 1.4 is released (coming real soon!). The FdoCmd.exe tool that ships with it will be functionally complete.

The only thing that can be improved on at that point would be to port it over to C++ and built against the native FDO APIs (instead of the windows-only .net wrapper APIs), so it can be part of the FDO native binary set, whether it's on Windows or Linux. That way, even if there won't be FDO Toolbox for Linux, at least you'll have access to its functionally equivalent CLI tool.

Monday, 25 May 2020

Fixing and improving long-standing FDO Toolbox annoyances

After 5+ years of inactivity, the FDO Toolbox project has roared back to life.

What prompted this burst of development activity? Years upon years of eating my own dogfood in my day job. Every-time I needed to load spatial data? Whip out the toolbox. Every-time I needed to take a peek at some spatial data to see what I'm dealing with? Whip out the toolbox. But those many years of self-usage also revealed many annoyances I had with using the tool and it was a constant battle between "just fix the problem already" and "we can workaround it".

For the longest time, "we can workaround it" had always been the winner, but those annoyances and pain points have built up to a critical mass of annoyance where I've finally decided to nip these problems in the bud once and for all.

So for this post, I'm going to outline some common scenarios and how the upcoming 1.4 release of FDO Toolbox will make these scenarios much simpler to carry out.

Scenario: Connecting to an RDBMS server with lots of data stores

Ever connect to a SQL Server that has lots of databases within (eg. 50+) and the current connect UI forces you to wait for the FDO provider to enumerate all those databases to load into a combo-box where you then have to pick and sift through to find your database in question.


Or heaven forbid, one of these 50+ databases is slightly misconfigured security-wise, and blows up the data store enumeration process. The UI in its current form has no escape hatch. You literally cannot make a connection to this SQL Server through the UI provided as a result. Now the actual bug is no longer an issue because the upcoming release will include FDO 4.1, whose SQL Server provider has this fix, but this scenario is being used as an example of the failings of the current connect UI.

Most of the time, you probably already know the name of the database you want to connect to, so in the upcoming release, that combo-box is now editable, allowing you to bypass this whole enumerating all the databases by putting in the name of the database directly and going straight to connect



Scenario: Setting up a bulk copy involving lots of files

Say you made a blank SQL Server database, and you want to load in these data files

How would you do this in current FDO Toolbox? It would be as follows:
  1. Connect to the SQL server database
  2. Connect to your data files
  3. Create a new bulk copy
  4. For each data file, add a copy task and pick the source feature class to copy and its destination target
  5. Review all the copy task options. Did you not want to copy/map all the properties? Well, you'll have to un-map them one-by-one.
  6. This is probably a once off thing, but the UI doesn't allow you to execute this right away. You have to give the bulk copy a name and save it out to a bulk copy task.
  7. Then you can execute this by right clicking the task on the Tasks tree and executing it.
  8. Grab a coffee/tea while the bulk copy does its thing
Now depending on how many files you are trying to load in, step 4 and 5 of this process are the most tedious step of the lot whose tediousness scales up with the number of files you're trying to load in.

In the upcoming release, we're going to streamline this process by giving you a new UI do specify multiple copy tasks at once!



Here's a gif of how long it takes to set up a full bulk copy of multiple SHP files into SQL Server.



Could you imagine how long this would've taken in current FDO Toolbox? What's more, you don't even have to save the task to execute it. There's now an Execute button in the bulk copy editor UI for you to execute the bulk copy in-place!



Scenario: I want a quick peek at the data

I use SQL Server Management Studio a lot when working with SQL Server databases directly and one of the features I take for granted in this tool is the ability to quickly preview the first 1000 results of any table I select.

I yearned for such a feature when going back to FDO Toolbox to take a quick peek at some spatial data and being annoyance at the absence of such a feature. So in the upcoming release, I've implemented such a feature!



Scenario: Copying data with non-standard/foreign projections into SQL Server

A common dataset I use when testing out FDO Toolbox or MapGuide is the Parcels.shp that comes with the SHP FDO Provider test suite.

So imagine my surprise that when I set up a bulk copy to load this file into a blank SQL Server db, that the process completely fails!



The actual logged error in question being:



This error is thrown by the SQL Server provider and reveals a strange corner case with blank SQL Server databases. The key thing to note was that this database was created outside of FDO Toolbox (I created it through SQL Server Management Studio). When you create a fresh database in SQL Server through FDO Toolbox like so:


You don't actually get a fully blank database in SQL Server, if you look at it through alternate tools you'll see this little table called f_scinfo


What is this table? This table is used to store FDO spatial context information about geometry columns/properties. So when we start the bulk copy into this blank database, the FDO provider creates this table automatically, which leads to the error at hand.

The error is due to the fact that when creating a spatial context in SQL Server, it tries to resolve a matching entry in the sys.spatial_reference_systems system table. Failing that, it tries to do an in-memory lookup of its local copy of ExtendedCoordSys.txt if you have one. If no matching entry is found through either means, it throws the aforementioned error. The Parcels.shp file is actually in the (LL/EPSG:4326) projection, but the name and WKT presented from the SHP file does not resolve to a coordinate system that SQL Server knows about.

So with the problem identified, the upcoming release presents the solution. For created copy tasks, there is a new option node available.


That when right-clicked presents an option to show a new spatial context override UI where you can replace the CS name/wkt from the source with a name/wkt that is known to SQL Server. When bulk copy creates its spatial contexts, it will use the name/wkt from the override settings if specified.


If you've ever used the Coordinate System Override feature in MapGuide Feature Sources, this is effectively the same concept but used for bulk copying in FDO Toolbox: A way to "fix" problematic coordinate systems in the source.

Alternatively, if you don't want to create a spatial context from source during the bulk copy and want to reuse an existing spatial context already on the target connection instead, we now provide that option as well.


Right-clicking this node presents a list of available target spatial contexts to map to.

Scenario: Copying data out of Oracle (through the King.Oracle provider)

If you have an Oracle database, then you are probably familiar with the quirks that come with reading data out of it through the King.Oracle FDO provider.
  1. The weird tilde notation for feature class names (unless you set up the KingFdoClass table)
  2. Spatial contexts always starting with "OracleSrid"
If you ever want to bulk copy data out of Oracle through this provider, these quirks live on in the data stores you've copied to, which may complicate any application code you have that may be working against this data store as the king oracle-isms have seeped into the data store.

In the upcoming release, through the multiple copy task UI, you now have the ability to specify the name of the target feature class if creating one. No more tildes!


Similarly, on the new spatial context override UI already shown, you may nominate a new name for the spatial context to be created. No more "OracleSrid" either!

In closing

This is not the full summary of what will be coming in the upcoming 1.4 release, but these features and improvements are the ones I believe will have the most impact in making the things you currently use FDO Toolbox for that much easier.