Pitney Bowes
MapInfo Pro™ Monthly Journal
MapInfo Professional 12.0 - working with spatial databases

man Some of you might already have noticed some of the improvements that we have made to MapInfo Professional 12.0 when it comes to working with spatial databases.

However there might also be a few things that you have overlooked. This article is in two parts, one is about the support for the SQLite and the second part is about the new interface for accessing data from a database system.

Support for SQLite

Admitted, this isn't entirely new to MapInfo Professional 12.0, as we actually started adding support for SQLite in one of the patch releases for MapInfo Professional 11.5. However, the capability was enhanced with the release of v12.0 and we've not yet covered this in this journal.

MapInfo Professional now lets you work with spatial data stored in a SQLite database. You might wonder what an SQLite database is and where this database normally is used. The SQLite database is an open source spatial database. For more info here is the SQLite Web site http://www.sqlite.org and an entry in Wikipedia http://en.wikipedia.org/wiki/SQLite. sql

For those of you who use Autodesk® products and need to share data with MapInfo Professional (and other MapInfo products) you might find SQLite support to be very handy. A number of Autodesk products support SQLite and this mutual support improves interoperability between our respective products.

How does it work?

An SQLite database is a single file, similar to a MS Access database. This makes it very useful for sharing data with people outside your organisation as you can just give them a single file with all your data.

You can access a SQLite database in two ways in MapInfo Professional:

  • Through File > Open... where you will find the SQLite database in the list of file types.

 open

  • In the File > Open dialog box, use the Open Database Connection button (top right corner) where you will see the "FDO (SQLite)" connection type.

 odbc

It doesn't matter which of these ways you choose. The end result will be the same: either a linked or a live DBMS table connected to a table in a SQLite database depending on your choices later on.

Oh, I almost forgot. The improvement we made in MapInfo Professional 12.0 is to let you create a new SQLite database from scratch when saving a copy of an existing table.

Just select "Save Copy As" like you normally would and select the table to make a copy of.

Now when you are asked where to store the tab file in the Create New Table dialog, select the file type SQLite Database (*.tab) from the Save as type list.

create

In the next dialog you have to pick the location and enter the name of the SQLite database in which you want the table created.

select

And there you go: A new SQLite database containing the one table that you just created.

I should probably mention that MapInfo Professional can save a copy of a table into any supported database system. It is however, only for SQLite and MS Access that we can create the database as well.

Improved dialog for opening tables from a database

Another improvement that I like a lot is our new dialog for opening tables from a database. In earlier versions of MapInfo Professional you were only able to open one table at a time from a database. Now with MapInfo Professional 12.0 you can open multiple tables at once.

Here is how it works.

You still access this feature through the File > Open dialog box. You can either click the Open Database Connection button in the upper right corner of the dialog or select an existing connection from the Files of Type list.

open

When you have chosen your connection or created a new connection to a database, the new dialog for opening tables will appear.

dbms

In this dialog you can select the tables that you want to open. You can also specify into which folder you want to create the TAB files that refer to these database tables. (As usual) these TAB files can later be used to reopen the data.

You can, in the lower part of the dialog, choose how to open the tables. That is, use linked tables or use live with or without cache. Which type you choose depends on your data and what work you want to perform on these tables. We will discuss these options in an article in an upcoming issue of The MapInfo Professional. Importantly, you are able to choose different settings for different tables.

Just below the list of tables, you can also see a summary of your current choices. In my example you can see that there is 12 tables listed, that I have selected to open 5 of these and that I have customized how to open one of the tables.

Above the list of tables you can access the Options dialog which will give you better control on what data to get from the database. You can control this through the Open DBMS Table Options dialog. In this dialog you can set which columns to get from the database and you can also specify which rows to download. You can for example specify that you only want to get data within the current map or you can specify a condition using one or more of the alphanumerical columns in the table.

options

Once you have set your options for the table, click the OK button to get back to the Select One or More Tables to Open dialog. Notice that you can only set the options for one table at a time.

Once you are back, you can see that the table that you just set some options on is now marked with an asterisk (*). When you hover the cursor over the table, you'll see a popup window showing you the options that you have chosen - in this case the query specified for downloading the data from the database.

tables

When you are happy with your settings, you can click OK and open the selected tables into your current session in MapInfo Professional.

Summary

MapInfo Professional v12.0 offers a new interface for opening data from database management systems. To be clear, this new functionality pertains to any of the supported database types.

Also covered in this article is the ability open, read, edit and save new tables in the SQLite open source database format.

Don't yet have MapInfo Professional v12.0?

A 30 day free trial for MapInfo Professional v12.0 is available for download in 10 languages!

Brazilian Portuguese http://web.pb.com/mipro12download_brazil/inquiry
Danish http://web.pb.com/mipro-dk/miprov12/miprov12
English http://web.pb.com/miprov12
Finnish http://web.pb.com/mipro-fi
French http://web.pb.com/mipro-fr
German http://web.pb.com/mipro-de
Japanese https://japan.mapinfo.com/location/evaluation
Russian http://web.pb.com/mipro-ru
Spanish http://web.pb.com/miprotrial
Swedish http://web.pb.com/mipro-se

Article by Peter Horsbøll Møller, Presales Consultant

When not writing articles for "The MapInfo Professional" journal, Peter helps Pitney Bowes Software customers to get the most from their software and is a prolific contributor to the mapinfo-l Google Group. When not working he is an aficionado of fine whiskey and good cigars.