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
- Why the WKT extracted from 11.2.0.2.0 doesn't seem valid in 11.2.0.1.0?
- How I can adjust it to make it valid?
- 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"]]'
);