Working with Oracle Spatial Tables - MapInfo_Pro - 2023

Oracle Spatial is an implementation of a spatial database from Oracle Corporation. MapInfo Pro supports simultaneous connections to Oracle Spatial through the Oracle Call Interface (OCI) and to other databases through ODBC.

Supported Object Types

The Multipoint and Collection object types are translated into the Spatial Objects Oracle MULTIPOINT and COLLECTION, respectively, via Oracle OCI and vice versa. However, Oracle's COLLECTION object is broader in scope than MapInfo Pro's Collection, which only allows one REGION, one polyline and one multipoint. Therefore, conversion from MapInfo Pro object to Oracle objects and the conversion from Oracle objects to MapInfo Pro objects may not be a one-to-one translation.

The following table shows the relationship between MapInfo Pro objects and Oracle objects.

The table indicates how Oracle Spatial objects are translated into MapInfo Pro objects. All point elements in an Oracle COLLECTION will be translated into one multipoint in a MapInfo Pro Collection; all Oracle Line objects (including single and multiple) will be translated into one MapInfo Pro polyline; and all Oracle Polygons (including single and multiple) will be translated into one MapInfo Pro REGION. Therefore, when an Oracle COLLECTION is modified and then saved back into Oracle Server by using MapInfo Pro, the original structure of the Oracle COLLECTION object may be changed if it is more complicated than the MapInfo Pro Collection.

Oracle Spatial Requirements

If you are connecting to an Oracle Spatial database, the spatial schema supports points, lines, and polygon spatial types.

Before connecting to Oracle Spatial from MapInfo Pro, ensure that you are working with a compatible version of Oracle. For a list of supported database versions, see the MapInfo Pro Install Guide.

Primary Key used for New Table Creation or When Saving a Remote Copy

An Unique key ensures that an entry does not match any other entry from a different record. If a record does not contain any value, no error is reported. A Primary key requires that every record contains a unique value in that field. By making these values Primary keys, we enforce that every record has a value in this field.

Primary Key Auto-Increment for Oracle Databases

The Primary auto-increment feature manages the Primary key value for you. When you add a new record to a remote Oracle table, MapInfo Pro locks the table, checks for the highest value of the Primary key of the table, increments it by one (1), and then puts that value in the Primary key field. SQL Server Spatial users do not require this feature as the server-side software manages the Primary key automatically.

Use the Auto Key check box in the Open DBMS Table Option dialog box to enable this feature. When you select the Auto Key check box, the Primary key field is not editable and the Unique key is automatically incremented. If you do not select this check box, the Primary key field remains editable.

To access this feature:

1. On the HOME tab, in the File group, on the Open list, click Database Table and select an Oracle database from the Files of Type drop-down list. Click OK to open the Open DBMS Table Options dialog box.
2. Select the Auto Key option to increment the Primary key of the selected table automatically for any new records.
3. Click OK to continue.

Oracle Object Map Verification Supporting Object/Map Validation

The Check Regions command (located on the SPATIAL tab, in the Edit group) removes region line segments that intersect each other. You can also think of this as nodes within a single polygon of a region where the polygon intersects itself, as when a node has more than 2 line segments emanating from it.

These cases can be broken down into two other instances:

• If a polygon has 2 looped sections, and the path traced by the nodes of the polygon follow a cursive figure 8 pattern, then we call this a Figure 8 polygon.
• If the same 2 looped polygon can trace its path as a cursive capital letter B, then we call this a Bow Tie polygon.

We think of the Figure 8 as worse than Bow Tie because the area of a Figure 8 is always incorrect, while the area of a Bow Tie can be correct and accurate. Both Figure 8's and Bow Ties are detected.

Creating Legends from/for a Live Table

You can retrieve unique styles from the table using the Create Legend wizard. You can retrieve styles from the map catalog, if performance is your primary concern, but if you prefer a more visually descriptive and appealing spatial representation of the unique map styles in your live table, you can create legends from a live table instead.

When are unique map styles available for a live table?

In general, remote databases have no consistent way of storing styles for geographic data. MapInfo Pro gives you two different ways of associating styles with geographic data for remote databases. The first way is to specify a common style for a database table in the map catalog, which is used for all records in that database table. The second way is to have an additional column for the database table that specifies a style for each record in that table. (MapInfo Pro uses the map catalog to keep track of the style column for a database table.) Unique map styles are available for a database table when it has this designated style column.

To see this functionality:

1. Open a remote DBMS connection and select a live table for display.
2. On the MAP tab, in the Content group, click Add to Map, and click Legend from the list to open the Create Legend - Choose Layers dialog box.
3. Select a layer to create a legend from and click Next to open the Create Legend - Legend Default Properties dialog box.
4. Click Next to open the Create Legend - Frame Properties dialog box.
5. In the Styles from box, there are three options. The first option, map catalog, provides the existing support and is still the default for live tables.
   • Select map catalog to retrieve the default styles for the selected legend frame from the map catalog. Use this option when you are concerned with performance. Retrieving map styles from live tables on a remote database can take a long time, but retrieving a default style from the map catalog can be significantly faster. Remember that styles in the map catalog may not be as visually descriptive as the other options. This is the default option for live tables, but is disabled for other types of tables.
   • Select unique map styles to retrieve all unique object styles for the live access table for a visually appealing legend. For extremely large tables, this option can take longer than the first option. This option is selected by default when the legend frame is not a live access table.
   • Select unique values in a column to retrieve the styles associated with the values saved in a particular column in the database table for a visually appealing legend. For extremely large tables, this option can take longer than the first option.
6. Click Save frame setting to Metadata to preserve the choice you made in this dialog box. When you re-open this dialog box the settings from the metadata are used as default values instead of the system set default values.
7. Click Finish.

See Troubleshooting your DBMS Access

MapInfo Pro does not Maintain the Curve Type in Oracle Data

When reading Oracle tables, MapInfo Pro changes records containing geometries that do not directly translate to MapInfo Pro geometry types. This happens when working with lines and polygon boundaries containing segments that are curve types or circles. MapInfo Pro does not support the curve type, so it converts these to polylines. MapInfo Pro also converts circles into regions. When saving these records back to the database, you may lose some topological information as a result. This is something to be aware of when sharing your Oracle data with other applications, such as Autodesk's Map3D.

MapInfo Pro shows a message before saving data back to the Oracle database when that data contains curved geometries. The message is as follows:

The Oracle table you are updating contains one or more geometry types that MapInfo Pro does not support, so the geometry type has been converted to a supported type. Saving these geometries may introduce topological errors into your database. Click Yes to continue, or No to discard changes that were made to unsupported geometries. Edits to attribute data save in either case.

Converting Unsupported Geometries in Oracle

Some times when you are creating a map in MapInfo Pro and you are storing the results in Oracle you create maps which use geometries that are not supported: Oracle does not support arcs, ellipses, rectangles, and rounded rectangles.

Oracle Geometry Conversion Behavior

If you try to save a map with unsupported spatial geometry types in Oracle, these are the results:

• Spatial Geometry Types with All Unsupported Objects: If you have created a map that might contain all of the unsupported objects and you are trying to save to Oracle, this message displays:

  Table has unsupported objects (rounded rectangles, rectangles,

  ellipses or arcs). Convert to regions and/or polylines?

  Click Yes to convert the unsupported objects to regions or polylines; you would select No to decline to convert the unsupported objects. If you decline, you cannot save the map you have created to the Oracle database. A confirmation message explains that the operation is canceled.

• Spatial Geometry types with Region Objects Only: If you have created a map that contains region objects only and you are trying to save to Oracle, this message displays:

  Table has unsupported objects (rounded rectangles, rectangles, or

  ellipses). Convert to regions?

  Click Yes to convert the unsupported objects to regions; you would select No to decline to convert the unsupported objects. If you decline, you cannot save the map you have created to the Oracle database.

• For Spatial Geometry types with Line Objects Only: If you have created a map that contains line objects only and you are trying to save to Oracle, this message displays:

  Arc is an unsupported object. Convert to polylines?

  Click Yes to convert the unsupported objects to polylines; you would select No to decline to convert the unsupported objects. If you decline, you cannot save the map you have created to the Oracle database.

Oracle Operating System Authentication and Configuration

When you choose OS authentication for a user, the user account is maintained by the Oracle database, but password administration and user authentication is performed by the Operating System (OS). With OS authentication, your database relies on the underlying operating system to restrict access to database accounts. A database password is not used for this type of login.

By default, Oracle allows operating-system-authenticated logins only over secure connections, which precludes using Oracle .NET and a shared server configuration. This default restriction prevents a remote user from impersonating another operating system user over a network connection.

Setting REMOTE_OS_AUTHENT to TRUE in the database initialization parameter file forces the RDBMS to accept the client operating system user name received over a non-secure connection and use it for account access.

Any change to this parameter takes effect the next time you start the instance and mount the database. Generally, user authentication through the host operating system offers faster and more convenient connection to Oracle without specifying a separate database user name or password. Also, user entries correspond in the database and operating system audit trails.

Setting Prefix for OS Authenticated User

Set the initialization parameter OS_AUTHENT_PREFIX, and use this prefix in the Oracle database user names. The OS_AUTHENT_PREFIX parameter defines a prefix that the Oracle database adds to the beginning of every user's operating system account name. When a user attempts to connect, Oracle compares the prefixed user name with the Oracle user names in the database.

For example, assume that OS_AUTHENT_PREFIX is set as follows:

OS_AUTHENT_PREFIX=OPS$

If a user named tsmith is to connect to an Oracle database installation and be authenticated by the Operating System, then the Oracle database checks whether a corresponding database user OPS$tsmith exists. If so, the user will connect. All references to a user authenticated by the Operating System must include the prefix, OPS$, as seen in the example OPS$tsmith.

The default value of this parameter is OPS$ for backward compatibility with previous versions of Oracle databases. However, you might prefer to set the prefix value to some other string or a null string (an empty set of double quotes: ""). Using a null string eliminates the addition of any prefix to operating system account names, so that Oracle user names exactly match operating system user names.

After you set OS_AUTHENT_PREFIX, it should remain the same for the life of a database. If you change the prefix, then any database user name that includes the old prefix cannot be used to establish a connection, unless you alter the user name to have it use password authentication.

Creating a User who is Authenticated by the Operating System

The following statement creates a user who is identified by Oracle and authenticated by the operating system or a network service. This example assumes that OS_AUTHENT_PREFIX = "".

CREATE USER scott IDENTIFIED EXTERNALLY;

Using CREATE USER <user name> IDENTIFIED EXTERNALLY, you create database accounts that must be authenticated by the operating system or network service. Oracle will then rely on this external login authentication when it provides that specific operating system user with access to the database resources of a specific user.

See the Oracle Database Advanced Security Administrator's Guide (http://docs.oracle.com/cd/B19306_01/network.102/b14268/toc.htm) for more information about external authentication.