MapGuide tidbits: Fixing SQLite data files with no coordinate systems

From the "I lost several hours discovering this so you don't have to" department

Today, I was tasked with producing some KML files. The spatial data was in SQL Server and it needed to be married with some CSV files I had, but I wanted it to get into a more portable format so I can do the bulk of the styling work in MapGuide without having to touch the SQL Server again.

Due to some quirks in this SQL Server database, the SQLite file that were produced from an FDO Toolbox bulk copy did not have any spatial contexts in them. This turns out to be a big problem because MapGuide has to know what coordinate system your data is in for rendering and various outputs to work, especially if coordinate system transformation is required. You can fix and/or flub such a coordinate system using the Coordinate System override section of the Feature Source, but this only works if MapGuide/FDO can read a spatial context from the data store for you to stick in a replacement coordinate system in.

This actually broke the KML support in MapGuide I was intending to use because it needs to know if your data is LL84 or transformable to it and since the SQLite file had none, MapGuide didn't do anything with the data when I wanted a KML export of it. Rather than waste time re-exporting this data in another format, I wanted to see if such files can be "fixed" in-place and be made usable again.

Since SQLite files are actual relational(-ish) databases, you can open them any SQLite database viewer^ (there's a lot of them) to take a look at the contents. When you open such a file, you'll see various tables representing each of your feature classes and some special tables:

• fdo_columns
• geometry_columns
• spatial_ref_sys

fdo_columns contains the FDO property metadata for each column of each table. Without this table, the SQLite FDO provider will treat all columns as strings, even if your column stores numbers, dates, etc.

geometry_columns contains the metadata for the geometry columns.

spatial_ref_sys contains coordinate system information.

In the event that MapGuide reads 0 spatial contexts from your SQLite data store, it's because:

• There are no rows in spatial_ref_sys. Each row in this table is read as a spatial context
• Any rows in geometry_columns either have an srid of -1 or an srid with no match in spatial_ref_sys

So with this knowledge, to "fix" such files so MapGuide knows what coordinate system they're in:

• Insert a new row into spatial_ref_sys
• Update the srid column of all rows in geometry_columns to the srid of the row you inserted into spatial_ref_sys

Or if none of what I just said made sense, just run the commands in this gist in your SQLite database admin tool of choice. Once done, your SQLite database will have a spatial context which you can then put an override in your Feature Source so that MapGuide knows what coordinate system to interpret your data in.

^ Though FDO Toolbox can technically be used to admin SQLite databases, I am suggesting alternative tools as I am not sure whether the SQLite FDO provider will let you "fix" something that it has an already-open connection to, nor do I know if the SQL commands the fix this will work as intended when going through the SQLite FDO provider. Better to be safe than sorry.