Oracle Coordinate System Support

Introduction

Properly setting a coordinate system in the data is essential for good data management practices. With the Oracle Spatial Database, you can set the coordinate system so the data can be properly read and written. This article will cover setting the coordinate system. For information on other operations you can do with Oracle, please see Tutorial: Getting Started with Oracle.

Writing to Oracle

When writing to Oracle with FME, there are three ways to set the coordinate system on the data. 

Method 1: Set the Coordinate System on the Reader

This is the simplest method.  Most data sources you encounter in FME are likely to have a coordinate system set on them by the readers, so they will need no modification. If the source data has no coordinate system defined on it, you can supply one by setting the coordinate system override on the reader.

Then, when writing, FME is able to map most of its coordinate systems to an Oracle SRID. However, there is currently no warning if the coordinate system cannot be mapped to an SRID.

Method 2: Set SRID in the Output Oracle Writer FeatureType Properties

Then, any data that reaches the writer is tagged with an SRID when it gets written.

Method 3: Set SRID on a per-feature Basis

Set the attribute oracle_srid to the desired SRID (e.g., create it with an AttributeManager transformer). This setting will override the SRID set on the feature type.

Reading from Oracle

When reading from Oracle, FME will attempt to map the SRID to an FME coordinate system. If FME cannot map the SRID, the coordinate system will not be set on the feature, and you may need to override the reader coordinate system, as described in step 1 above.

However, to override the reader, you will need to find out what the equivalent FME coordinate system is. The names can be quite different. Often, you need to examine the Oracle WKTEXT field associated with the SRID to see if it is, in fact, the same as the FME coordinate system OGC_WKT. Often, the SRID is based on the EPSG number, so this might be a good place to start. You can find the FME coordinate system parameters by going to the 'Tools > Browse Coordinate Systems', clicking on the coordinate system of interest, and then going to 'Properties' for that definition.

In Oracle, you will need to check the contents of the MDSYS.CS_SYS or MDSYS.SDO_CS_SRS table depending on your version of Oracle (for example, to check the definition for SRID 8192):

SELECT CS_NAME, SRID, AUTH_SRID, AUTH_NAME, WKTEXT, CS_BOUNDS FROM MDSYS.SDO_CS_SRS WHERE SRID = 8192;

For example:

  FME CS_NAME: LL84
  DESC_NM: WGS84 Lat/Long's, Degrees, -180 ==> +180
  OGC WKT Description:
  GEOGCS["WGS84 Lat/Long's, Degrees, -180 ==> +180",
  DATUM["WGS_1984",
  SPHEROID["World Geodetic System of 1984",6378137,298.257223563],
  AUTHORITY["EPSG","6326"]],
  PRIMEM["Greenwich",0],
  UNIT["degree",0.0174532925199433]]

The equivalent Oracle coordinate system is:

  ORACLE CS_NAME: Longitude / Latitude    
  SRID: 8192
  WKTEXT:
  GEOGCS [ "Longitude / Latitude (WGS 84)",
  DATUM ["WGS 84", SPHEROID ["WGS 84", 6378137, 298.257223563]],
  PRIMEM [ "Greenwich", 0.000000 ],
  UNIT ["Decimal Degree", 0.01745329251994330]]

Thus, you can use SRID 8192 to represent LL84. The CS_NAME is definitely a clue, but you can only be sure if you examine the WKT parameters in detail. Note that SRID 8307 and 4326 are equivalent to 8192, so there is typically some repetition in the CS_SYS or SDO_CS_SRS coordinate systems table.