Ten things to know about using MapInfo Pro to populate your SQL tables

Shape2SQL is a great product for loading spatial data into Microsoft SQL Server. In fact, we use it all the time. However, it’s intended for ESRI shapefiles only. Chances are, if you work for a business, your spatial data will be in MapInfo tab format.

Easy Loader Dialog

If you are building up a data warehouse want to include spatial data in it, then you’ll want to look at MapInfo Pro for help staging the datasets. To load such data into a remote database server, MapInfo Pro gives you three methods. They are not completely interchangeable, so you’ll want to be aware of important distinctions.

Methods Available With MapInfo Pro (via its command menu)

  • Tools / EasyLoader
  • File / Save Copy As
  • Table / Append Rows to Table

Comparisons of Easy Loader and File / Save Copy As Commands

Easy Loader will transfer invalid spatial objects (e.g., those having self intersections) as is, as invalid objects. It does not run MakeValid() on the SQL Server side for you. Not so with Save Copy As, which conveniently applies the MakeValid() spatial method to your data.

Both Save Copy As and Easy Loader create in the target SQL Server table a column named SP_Geometry for the spatial data. Regardless of the spatial data type, geography or geometry, you get the same column name of SP_Geometry. That could be confusing.

Server, which lets which lets you mix and match Projection Systems record by record. So if your data warehouse convention is always to use one system, say WGS 84, but a MapInfo table happens to be in NAD 83, you can transform it in the Save Copy As process. Easy Loader on the other hand transfers the table with the same system defined on it in MapInfo Pro. You’ll get no warning of the type. We’d recommend verifying the SRID on the database table using the STSrid property on the spatial column.

In MapInfo Pro, if you happen to use the projection-less system of Latitude/Longitude (as opposed to say Latitude/Longitude WGS 84), you’ll get in SQL Server a SRID value of zero, even on a geography data type. That could be confusing since geometry data types have an SRID of zero by default.

Easy Loader allows you append records to an existing spatial table in your database-provided the structures are identical. As the command name applies, Save Copy As always creates a new table in the database. Not surprisingly, non-spatial data types in MapInfo Pro don’t line up neatly with data types in SQL Server. Regardless of which method you use, expect to do some type conversions. Easy Loader lets you create indexes, spatial and non-spatial, on the SQL Server side as part of the upload process. Convenient yes, but that may or may not be a good thing, since you have no flexibility in the specifications of the index (e.g., grid density, include columns, and so forth).

Easy Loader Minimum Options

Both methods limit your SQL Server table name to 32 characters in length, as opposed to 128 in SQL Server 2012. This limitation is not at all obvious. If your table name is too long, you’ll get an error message of “failed to add to the MapInfo.MapInfo_MapCatalog, which is a SQL Server table created and used by MapInfo Pro (when you make a remote table “mappable” in MapInfo Pro). The error message itself says nothing about the name length restriction.

To the destination table in t he database, both automatically create a key column named MI_PRINX, of type int. Save Copy As causes it to have the identity property. With Easy Loader, you’ll need to manually set the option. If you don’t do this, you’ll throw an error on the upload. See Figure 2 for the minimum required options.

Table/Append Rows To Table

As the name suggests, this method can only be used to insert records into an existing remote table. It’s tricky to implement, though, because it’s not like a SQL command of insert into. Instead, the command literally appends the rows of a table to the rows of the target table. this means that you have to align the columns in MapInfo Pro table exactly with the columns of a MapInfo Pro side copy of the remote table to which you are adding records.

That’s doable, as long as you are careful to download from the remote table only the columns that have a match in the MapInfo table, while also keeping them in the same order as found in the MapInfo table. At least if you don’t keep the order the same, you can always use SQL Select command in MapInfo Pro to rearrange the column order in the MapInfo table.

The append operation allows you to insert records with invalid spatial objects to an existing spatial table — provided that your remote tables does not include computed columns that use spatial methods. For example, if the target table includes a computed column that uses the STArea() method on the spatial column, when you go to commit the changes to the remote table, you’ll get an ODBC error like the one shown in Figure 3.

ODBC Error

 MapInfo Pro enables you to easily transfer spatial data from the desktop into an enterprise level database systems, such as Microsoft SQL Server to name one, and from their ultimately to your master data hub. The simple task of uploading the data is just the beginning. You still need to do the hard work of standardizing data types and naming conventions, as well as setting up indexes-not to mention setting up a maintenance plan. In other words, as usual, the rules of data governance and quality management still apply even if you think you are simply transferring data from one system to another.

Author: 
Daniel Brasuk
Share