VERIFIED SOLUTION i

How to use mapinfo_mapcatalog when storing spatialized tables in external database

Products affected: Mapinfo Professional™
 
From the MapInfo Pro 15.2 Help section:
 

Before you Begin Creating a Map Catalog Table

You will use EasyLoader to create a Map Catalog table in the database you wish to work with. EasyLoader installs with MapInfo Pro.
(The tool "DBMS_Catalog" no longer ships with MapInfo Professional 64-bit, as it's functionality is found in the EasyLoader tool.)

Note: EasyLoader is also available as a free download from the Pitney Bowes Inc. website. If you download a copy of EasyLoader, then you must ensure that you have the same version of EasyLoader as MapInfo Pro to use them together.
http://www.pbinsight.com/support/product-downloads/for/easyloader

Before you begin, you must alert your database administrator to the
following:

  • You require administrator privileges to read and write to the database server. Even if you only read tables on the server, you still require write access to have EasyLoader create and then have MapInfo Pro write to the Map Catalog table.
  • EasyLoader creates a MAPINFO schema at the server level while creating the Map Catalog table.
  • EasyLoader creates a MAPINFO user for the database.

Your database administrator may want to manually set up your database access, thus bypassing the need to use EasyLoader. If your database administrator wants to create the Map Catalog table manually.


Creating a Map Catalog in the DBMS

MapInfo Pro stores information about spatial tables and views in the database. It stores this information in a file called MAPINFO.MAPINFO_MAPCATALOG (the Map Catalog) as a special table on the DBMS you are accessing.

The MAPINFO_MAPCATALOG is a registry table for databases that stores metadata about spatial tables and views in the database. Using the tablename and ownername as the key, the MAPINFO_MAPCATALOGidentifies the spatial type, spatial columns (if there is more than one), projection, data bounds, and rendition information. The MAPINFO_MAPCATALOG is used by a number of MapInfo products, including MapInfo Pro, that access map data from databases.

There must be one catalog per database, which is required before mapping any tables on that database in MapInfo Pro.

Manually Creating a MapInfo_MapCatalog

To create a MAPINFO_MAPCATALOG manually:

  1. If the RDBMS requires owners and users, then create the user MAPINFO with the PASSWORD MAPINFO in the specific database where the mappable tables are located.
  2. Create the table MAPINFO_MAPCATALOG in the database.

     

    The Create Table statement must be equivalent to the following SQL Create Table statement:
    Create Table MAPINFO_MAPCATALOG(
    SPATIALTYPE
    	TABLENAME
    	OWNERNAME
    	SPATIALCOLUMN
    	DB_X_LL
    	DB_Y_LL
    	DB_X_UR
    	DB_Y_UR
    	VIEW_X_LL
    	VIEW_Y_LL
    	VIEW_X_UR
    	VIEW_Y_UR
    	COORDINATESYSTEM
    	SYMBOL
    	XCOLUMNNAME
    	YCOLUMNNAME
    	RENDITIONTYPE
    	RENDITIONCOLUMN
    	RENDITIONTABLE
    	NUMBER_ROWS
    Float,
    	Char(32),
    	Char(32),
    	Char(32),
    	Float,
    	Float,
    	Float,
    	Float,
    	Float,
    	Float,
    	Float,
    	Float,
    	Char(254),
    	Char(254),
    	Char(32),
    	Char(32),
    	Integer,
    	VarChar(32),
    	VarChar(32),
    	Integer
    )
     

    It is important that the structure of the table is exactly like this statement. The only substitution that can be made is for databases that support varchar or text data types; these data types can be substituted for the Char data type.

  3. Create a unique index on the TABLENAME and the OWNERNAME, so only one table for each owner can be made mappable.
  4. Grant Select privileges to all users on the MAPINFO_MAPCATALOG. This allows users to make tables mappable. The Update, Insert, and Delete privileges must be granted at the discretion of the database administrator.

Spatial Index Types

The spatial index type applies to the column that has the spatial information in the DBMS table. The spatial index provides a fast way for MapInfo Pro to access the spatial data in the table. The index types to choose from are.

Spatial Index TypeType Number

MapInfo MICODE schema (any database)

1

XY schema (any database)

4

Oracle Spatial Geometry

13

SpatialWare for SQL Server

14

Oracle Spatial Annotation Text

16

SQL Server Spatial (for geometry)

17

SQL Server Spatial (for geography)

18

PostGIS for PostgreSQL

19

SQL Server Spatial with M and Z values (for geometry)

20

SQL Server Spatial with M and Z values (for geography)

21

You use the XY Coordinates option when there is no index

Manually Making a Remote Table Mappable

For each spatial table in the remote database that you want to access in MapInfo Pro, you must add a row to the MAPINFO_MAPCATALOG table. This is carried out in MapInfo Pro when on the TABLE tab, in the Maintenance group, you click Maintenance, and Make DBMS Table Mappable.

If you do not use MapInfo Pro to manage the Map Catalog, you must manually add rows to the MAPINFO_MAPCATALOG table for each spatial table in the database that you want to geocode. Each entry must contain the following information about the table.

 
Column NameValues to AssignExample

SPATIALTYPE

SPATIALTYPE values are constructed from the spatial index type from the spatial index types table (see above) and the value that represents the table contents as described here:

  • 0 - points only
  • 1 – lines
  • 2 - regions
  • 3 - all geometries
  • 4 – text
4.2

TABLENAME

Name of the table.

Drainage

OWNERNAME

Owner name.

Georgetown

SPATIALCOLUMN

Name of the column, if any containing spatial features. The name is:

  • NO_COLUMN (for mappable tables using X,Y)
NO_COLUMN

DB_X_LL

X coordinate of the lower left corner of the layer's bounding rectangle, in units indicated by the COORDINATESYSTEM as defined by MapInfo Pro.

-360

DB_Y_LL

Lower left bounding Y value.

-90

DB_X_UR

Upper right bounding X value.

360

DB_Y_UR

Upper right bounding Y value.

90

VIEW_X_LL

X coordinate of the lower left corner of the view's bounding rectangle, in units indicated by the COORDINATESYSTEM as defined by MapInfo Pro.

-360

VIEW_Y_LL

Lower left bounding Y value.

-90

VIEW_X_UR

Upper right bounding X value.

360

VIEW_Y_UR

Upper right bounding Y value.

90

COORDINATESYSTEM

A string representing a MapInfo-supported coordinate system that specifies a map projection, coordinate units, etc.

For Example:
Earth Projection 1,33 (for NAD 83) or
Earth 
Projection 1,0

SYMBOL

A MapInfo Symbol clause (for a layer containing points)

Symbol 
(35,0,12)

XCOLUMNNAME

Specify the name of the column containing X coordinates.

NO_COLUMN

YCOLUMNNAME

Specify the name of the column containing Y coordinates.

NO_COLUMN

RENDITIONTYPE

Specify 1 if on, 0 if off.

1

RENDITIONCOLUMN

Specify the name of the rendition column.

MI_STYLE

RENDITIONTABLE

Specify the name of the rendition table.

This field is not used.

left empty

NUMBER_ROWS

Specify the number of rows in the table.

11
UPDATED:  August 2, 2017