Resolve working with Oracle Spatial Tables

product affected: MapInfo Pro™

Issue

Cause

Resolution

UPDATED: September 19, 2017
Oracle Spatial is an implementation of a spatial database from Oracle Corporation. You can install it in addition to the MapInfo Professional ODBC Connectivity component. Although it has some similarities to the previous Oracle SDO relational implementation, it is significantly different. Oracle Spatial maintains the Oracle SDO implementation via a relational schema. However, MapInfo Professional does not support the Oracle SDO relational schema via the Oracle Call Interface (OCI). MapInfo Professional does support simultaneous connections to Oracle Spatial through the 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 Professional's Collection, which only allows one REGION, one polyline and one multipoint. Therefore, conversion from MapInfo Professional object to Oracle objects and the conversion from Oracle objects to MapInfo Professional objects may not be a one-to-one translation.
The following table shows the relationship between MapInfo Professional objects and Oracle objects.
Oracle Spatial ObjectsMapInfo Professional Objects
MULTIPOINTMultipoint
COLLECTIONCollection
Point (cluster)Multipoint
Line StringPolyline
Multiple Line String 
PolygonREGION
Multiple Polygons 
The table indicates how Oracle Spatial objects are translated into MapInfo Professional objects. All point elements in an Oracle COLLECTION will be translated into one multipoint in a MapInfo Professional Collection; all Oracle Line objects (including single and multiple) will be translated into one MapInfo Professional polyline; and all Oracle Polygons (including single and multiple) will be translated into one MapInfo Professional REGION. Therefore, when an Oracle COLLECTION is modified and then saved back into Oracle Server by using MapInfo Professional, the original structure of the Oracle COLLECTION object may be changed if it is more complicated than the MapInfo Professional Collection.
If you are connecting to an Oracle Spatial database,Oracle Spatial is a new spatial schema number 13. It provides support for points, lines, and polygon spatial types.
Oracle Spatial Requirements
To connect to Oracle Spatial within MapInfo Professional, you must have the Oracle Spatial or Oracle 9i and 9i release 2, or 10G client installed. See your Oracle documentation for detailed information.
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 Professional 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, thePrimary 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. From the File menu, click Open and select an Oracle database from the Files of Type drop-down list. Click OK to display 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 menu option enables you to remove 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 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.

MapInfo Professional does not Maintain the Curve Type in Oracle Data
When reading Oracle tables, MapInfo Professional changes records containing geometries that do not directly translate to MapInfo Professional geometry types. This happens when working with lines and polygon boundaries containing segments that are curve types or circles. MapInfo Professional does not support the curve type, so it converts these to polylines. MapInfo Professional 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 Professional 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 Professional 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.
Note: When executing the MapBasic Commit statement, the prompt displays only when it contains the Interactive keyword.
Converting Unsupported Geometries in Oracle
Some times when you are creating a Map in MapInfo Professional 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.
Note: If you have created a map you do not want to lose but cannot save to the DBMS of your choice due to unsupported geometries, then use the Save As menu option and save the map without the unsupported geometries.
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$
Note: The initialization parameter OS_AUTHENT_PREFIX is case-sensitive in some operating systems. Refer to your operating system specific Oracle documentation for more information about this initialization parameter.
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.