How can I learn what MapInfo Pro knows about my spatial data?
The process of transferring spatial data from MapInfo Pro up to the SQL Server sometimes can result in unexpected changes to the spatial data. If you plan ahead, you can capture data helpful in identifying the records that underwent a transformation.
Descriptive characteristics of a spatial object such as the spherical area and perimeter length, number of nodes and polygons in it, the object type, it’s centroid, plus the dimensions of it’s minimum bounding rectangle-all this stuff is invaluable for before and after comparisons.
For a single polygon, it’s easy enough to get this data. Just double-click on an object in the mapper window of MapInfo Pro to bring up the object info window, like the one in Figure 1.
Suppose you have thousands or millions of data records? In this case, you need to query the entire table for the data. That’s easy enough to do, provided you know a bit about MapBasic (which is the programming language for MapInfo Pro. Here’s the query script you would want to run.
[My Primary Key],
Round(SphericalArea(obj, “sq m”), -1) “Area”,
Round(SphericalPerimeter(obj, “m”), -1) “Perimeter”,
ObjectInfo(obj, 1) “ObjectTypeID”,
ObjectInfo(obj, 20) “ObjectNodeCount”,
ObjectInfo(obj, 21) “ObjectPolygonCount”,
CentroidY(obj) “CentroidY” ,
FROM [My Table]
BROWSE * FROM MyQueryResults
The method for executing the script in MapInfo Pro is frankly a bit odd, but easy enough after you’ve done it once. Do the following:
- Open the MapBasic window (from the Options menu)
- Edit the Select command to single, and very long statement, all on one line.
- Copy and paste it into the MapBasic window.
- With your cursor at the end of the line, hit return. That will execute it. (Yes, you read that right!)
- Repeat for the Browse command.
You’ll get results like those in Figure 2:
If this approach seems to be a bit crude, that’s because it is. What you are really doing is running MapBasic commands manually inside MapInfo Pro. In fact, you have to resort to MapBasic because the ObjectInfo commands used in the script are not directly accessible from the function call dialogs in MapInfo Pro’s standard query dialogs.
The MapBasic window, while simple, is not your only option. You can execute the same query using the conventional SQL Select dialog in MapInfo Pro. Construct the query to resemble the one in Figure 2. Start with copying and pasting the column components of the Select script into the Select Columns input box of the SQL Select dialog window. (Pull down the Functions list and you can confirm the ObjectInfo functions are not accessible from the Functions dialog). Edit the From Tables and Into Table Named inputs. Add a Where Condition if you need one. Click OK to execute.
If you want to make the query reusable, use the Save Template command. Open it at another time for reuse using the Load Template command. FYI, the Save Template command creates a qry type file. The contents of the query template file are not the same as the Select statement that you pasted into the MapBasic window. Instead it looks like what’s shown in Figure 3. Not that it’s important but I thought you should know that you cannot simply save the original SQL language as is.
Finally, after executing the query, push the results up to SQL Server using either File/Save Copy As, or Table/Append Rows To Table if you already have a target.
While its not obvious, the query results also will contain a column for spatial objects in the table. You won’t see it in the browser window in MapInfo Pro. However, if you display the query results in a map window, you will. So when you export the result to SQL Server, you’ll end up with a spatial column in your SQL table. If you want to avoid that, then you’ll need to take a couple of extra steps to strip off the spatial column. (Hint: Save the table into MapInfo Pro; open it back up; use Table Maintenance to make the table un-mappable.)
About Coordinate Systems: In MapInfo Pro, all records in a table use the same system, unlike SQL Server, which lets you mix and match systems by record. So in my query, the TableInfo() function will always return the same name for all rows. The value will be in the format of the name of the system, such as “Latitude / Longitude (WGS 84)” instead of the SRID value (of 4326) that we are used to seeing in SQL Server. If you don’t recognize the name and need the SRID, PBBI provides a text file named MapInfoW.prj that includes the SRID. (Unfortunately, the system names used by MapInfo do not correspond to the names in the system table sys.spatial_reference_systems.)
When transferring spatial data from MapInfo Pro to SQL Server, especially if you have a lot of records, its usually a good practice to transfer descriptive data about the spatial objects themselves at the same time. That way, you can create an audit trail of changes to polygons. Incorporate this rule into your data governance practices and you’ll have to worry less about surprises and mistakes due to data quality problems.