Coordinate Systems in Oracle

From Fmepedia

Created by: Dean Hintz

Q) How do I set the Coordinate System in Oracle?

A) You have 2 choices, you can set the FME coordinate system, OR you can set the appropriate SRID in oracle_srid. Ideally its simplest if you set one, not both, to avoid confusion. However, you may need to set both in the cases where FME does not recognize the SRID number.

Using the FME Coordinate System Setting

The Oracle writer was recently updated to handle FME coordinate systems, so in most cases this is the easiest approach. Note that FME coordinate system doesn't understand srids directly. For example, you can set the destination coordinate system to LL84, which results in a srid of 4326 once the data is written. In essence, FME automatically converts the FME coord sys to the appropriate srid for oracle. But setting FME coord sys to 4326 wouldnt be valid. Also note that in the case where srid = epsg number, you still need to select the appropriate epsg coordinate system from FME's coord sys table, you can't just enter the epsg number. For example, 'EPSG:4326' is a valid FME coordinate system setting, but '4326' is not.

Setting the SRID:

Occasionally, FME will not be able to map from FME coordinate system to SRID automatically. If the SRID is known then you can set it directly on your output oracle_srid. This is fine for a simple data load.

However, if you need to do any reprojection, then you will need to find out what the equivalent FME coordinate system is and set it so FME knows how to reference your data. The names can be quite different so often you need to examine the Oracle WKTEXT field associated with the SRID to see if it 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:

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

To check the definition for 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 repitition in the CS_SYS or SDO_CS_SRS coordinate systems table.