Oracle Coordinate System Support

Writing to Oracle

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

1. The simplest method is to set an FME coordinate system on the reader, if one does not already exist. Most data sources you encounter in FME are likely to have a coordinate system set on them by the readers and so 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.

2. Another method is to set the SRID in the output Oracle Writer FeatureType Properties. Then, any data that reaches the writer is tagged with an SRID when it gets written.

3. You can also set the SRID on a per-feature basis, by setting 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 so often you need to examine the Oracle WKTEXT field associated with the SRID to see if it is in fact is 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 'Workbench > 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.