1

I am working with an Oracle Standard database at version 11.2.0.1.0. I also have access to XE version 11.2.0.2.0.

The later version has both SRIDs 900913 (Google Mercator) and 3857 (official Web Mercator), but the earlier version does not. I read that SRSs are easily inserted into Oracle's mdsys.cs_srs table, so I selected the values for 900913 from the later DB and inserted into the earlier DB.

The insert reported success, but no matter how many times I update it the WKTEXT3D field in 11.2.0.1.0 remains null. If I try a coordinate transformation from a single point (both 2D and 3D) in 4326 (WGS84 Lat/Lon) to 900913 I am told "Parsing error on source or destination coordinate system WKT, or incompatible coordinate systems", then "Check the validity of the WKT for table entries, and check if the requested transformation is valid".

I can use the same transformation query (below) in 11.2.0.2.0 without issue.

Does anyone know

  1. Why the WKT extracted from 11.2.0.2.0 doesn't seem valid in 11.2.0.1.0?
  2. How I can adjust it to make it valid?
  3. If there is some fundamental issue in 11.2.0.1.0 that makes this transformation invalid?

Any input on these issues verrrrrry much appreciated.

Edit: Following MelitaKennedy's suggestion I started trying to track down the 'Popular Visualisation Pseudo-Mercator' projection in my 11.2.0.2.0 DB and I think I found it in MDSYS.SDO_COORD_OPS. However, it references a COORD_OP_METHOD_ID (MDSYS.SDO_COORD_OP_METHODS table) which defines several 'methods'. I assume that each of these methods relates to a function within Oracle, so maybe it simply isn't possible to create the entire CRS / Projection package in a pre-11.2.0.2.0 DB?

Transform Query:

  select
    sdo_cs.transform(
      sdo_geometry(
        2001,
        4326,
        null,
        sdo_elem_info_array(1,1,1),
        sdo_ordinate_array(-132.525665, 45.432552)
      ),
      sdo_dim_array(
          sdo_dim_element('LONG', -180.0, 180.0, 0.5),
          sdo_dim_element('LAT', -90, 90, 0.5)
      ),
    900913)
  from
    dual;

900913 Insert:

insert into mdsys.cs_srs (cs_name, srid, auth_srid, wktext, wktext3d)
  values (
    'Google Mercator',
    900913,
    900913,
    'PROJCS["Google Mercator", GEOGCS [ "WGS 84", DATUM ["World Geodetic System 1984 (EPSG ID 6326)", SPHEROID ["WGS 84 (EPSG ID 7030)", 6378137.0, 298.257223563]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.0174532925199433]], PROJECTION ["Popular Visualisation Pseudo-Mercator"], PARAMETER ["Latitude_of_origin", 0.0], PARAMETER ["Central_Meridian", 0.0], PARAMETER ["False_Easting", 0.0], PARAMETER ["False_Northing", 0.0], UNIT ["Meter", 1.0]]',
    'PROJCS["Google Mercator", GEOGCS["WGS 84", DATUM["World Geodetic System 1984", SPHEROID["WGS 84", 6378137.0, 298.257223563, AUTHORITY["EPSG", "7030"]], TOWGS84[0, 0, 0, 0, 0, 0, 0], AUTHORITY["EPSG", "6326"]], PRIMEM["Greenwich", 0.000000, AUTHORITY["EPSG","8901"]], UNIT["degree (supplier to define representation)", 0.0174532925199433, AUTHORITY["EPSG", "9122"]], AXIS["Lat", NORTH], AXIS["Long", EAST], AUTHORITY["EPSG", "4326"]], PROJECTION ["Popular Visualisation Pseudo-Mercator"], PARAMETER ["Latitude_of_origin", 0.0], PARAMETER ["Central_Meridian", 0.0], PARAMETER ["False_Easting", 0.0], PARAMETER ["False_Northing", 0.0], UNIT["metre", 1.0, AUTHORITY["EPSG", "9001"]], AXIS["X", EAST], AXIS["Y", NORTH], AUTHORITY["", "900913"]]'
  );
3
  • My guess is that PROJECTION ["Popular Visualisation Pseudo-Mercator"] is not a supported map projection in 11.2.0.1 and/or there are other tables that must be updated. Mar 1, 2012 at 1:35
  • @MelitaKennedy do you have any idea where supported projections are defined? The only Oracle table that comes close is 'SDO_PROJECTIONS_OLD_SNAPSHOT'
    – tomfumb
    Mar 1, 2012 at 1:46
  • 1
    Anyone having a similar problem might be interested in this link forums.oracle.com/forums/thread.jspa?threadID=2228982 where a transform is performed using a CRS that does exist in 11.2.0.1 is tweaked to give very similar (sub-millimetre) results to 3857 / 900913.
    – tomfumb
    Mar 1, 2012 at 19:55

1 Answer 1

3

check this out:

I collected the information to include the projection EPSG:3857 in Oracle Spatial

https://www.inf.unibz.it/dis/wiki/doku.php?id=students:minnerebner:oracle:addingsrid

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.