Adding a Spatial Primary Key to a DBMS Table - MapInfo_Pro - 2023

After establishing a data source connection to your DBMS you must add a geometry reference to the tables you wish to work with on a map in MapInfo Pro. Each table that you want to work with must contain geometry, such as regions, or X,Y point data, with correctly named columns and a unique primary key column that is indexed and defined with a specific name.

If you attempt to work with a DBMS table that does not contain a spatial primary key column and does not have geometry and X,Y columns correctly labeled, then you may see the following error:

To have MapInfo Pro prepare a table for you:

1. Open the table in MapInfo Pro, on the HOME tab, in the File group, from the Open list, click Table and then locate and select the table in the Open dialog box.
2. on the HOME tab, in the File group, click Save Copy As and save a copy back to the DBMS.

   This saves a copy of the table with a unique primary key column that is indexed and in the correct format. This process also asks you for your spatial geometry column or your X,Y columns in the table and then renames these columns, so that MapInfo Pro can work with them.

You are now ready to begin working with the data in MapInfo Pro in tabular form (in a Browser window). For details on what changes are made to column names, see Adding a Spatial Primary Key Manually.

If you want to convert your table to the MapInfo Pro native .TAB format to work with it, then use EasyLoader, which will also rename your geometry or point columns and add a spatial primary key column to the table.

See also: MapInfo EasyLoader

Adding a Spatial Primary Key Manually

To have full control over renaming geometry or point columns and add a spatial primary key column to one or more tables, you can do this manually. If you choose to update your tables yourself, then you must make the following changes:

Geometry Data Tables

If you are working with a spatial table containing geometry (such as regions), the geometry column must be named SP_GEOMETRY. The following table is a summary of what is required for geometry data tables (note that Microsoft Access does not support geometry data, only point data):

• If working with a database table, then MapInfo Pro finds the correct primary index column (this column must be a numeric type).
• If working with a database view, then it must have a column named MI_PRINX that is unique and numeric.

X,Y Point Data Tables

If you are working with a spatial table containing X,Y data (such as Latitude and Longitude), the X and Y columns must be named MI_SQL_X and MI_SQL_Y. The table must also have a unique primary key column that is indexed and named MI_SQL_REC_NUM.

To use different names for these columns, specify the name of the X, Y and spatial object columns in the MapCatalog.

SQL Server with SpatialWare Tables

If you are working with SQL Server with SpatialWare installed, the column naming convention is different from other spatial DBMS tables. You must use SW_MEMBER for the unique primary key column that is indexed, and SW_GEOMETRY for the geometry column.

If your table contains X,Y data, then the column names must be MI_SQL_X and MI_SQL_Y.