Adding Spherical Mercator EPSG:3857

In this page I describe how to add the Google Projection Format Spherical Mercator, with the code 'EPSG:3857' (unofficial code 'EPSG:900913') the SRID table of Oracle Spatial. This is a conversation having with Siva, an Oracle Spatial Expert: see https://forums.oracle.com/forums/thread.jspa?threadID=952992&start=0&tstart=0 to see the entire posting

Related links are:

Optional preliminary steps

Adding Ellipsoid

INSERT INTO MDSYS.SDO_ELLIPSOIDS (
ELLIPSOID_ID,
ELLIPSOID_NAME,
SEMI_MAJOR_AXIS,
UOM_ID,
INV_FLATTENING,
SEMI_MINOR_AXIS,
INFORMATION_SOURCE,
DATA_SOURCE,
IS_LEGACY,
LEGACY_CODE)
VALUES (
7059,
'Popular Visualisation Sphere',
6378137,
9001,
1.0000E+12,
NULL,
NULL,
'EPSG',
'FALSE',
NULL);

returns sql errors

Adding Datum

INSERT INTO MDSYS.SDO_DATUMS (
DATUM_ID,
DATUM_NAME,
DATUM_TYPE,
ELLIPSOID_ID,
PRIME_MERIDIAN_ID,
INFORMATION_SOURCE,
DATA_SOURCE,
SHIFT_X,
SHIFT_Y,
SHIFT_Z,
ROTATE_X,
ROTATE_Y,
ROTATE_Z,
SCALE_ADJUST,
IS_LEGACY,
LEGACY_CODE)
VALUES (
6055,
'Popular Visualisation Datum',
'GEODETIC',
7059,
8901,
NULL,
'EPSG',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'FALSE',
NULL);

returns sql errors

Adding COORD_REF_SYSTEM

INSERT INTO MDSYS.SDO_COORD_REF_SYSTEM (
SRID,
COORD_REF_SYS_NAME,
COORD_REF_SYS_KIND,
COORD_SYS_ID,
DATUM_ID,
geog_crs_datum_id,
SOURCE_GEOG_SRID,
PROJECTION_CONV_ID,
CMPD_HORIZ_SRID,
CMPD_VERT_SRID,
INFORMATION_SOURCE,
DATA_SOURCE,
IS_LEGACY,
LEGACY_CODE,
LEGACY_WKTEXT,
LEGACY_CS_BOUNDS,
is_valid,
supports_sdo_geometry)
VALUES (
4055,
'Popular Visualisation CRS',
'GEOGRAPHIC2D',
6422,
6055,
6055,
NULL,
NULL,
NULL,
NULL,
NULL,
'EPSG',
'FALSE',
NULL,
NULL,
NULL,
'TRUE',
'TRUE');

returns sql errors

Adding sdo_coord_ops

INSERT INTO mdsys.sdo_coord_ops (
coord_op_id,
coord_op_name,
coord_op_type,
source_srid,
target_srid,
coord_tfm_version,
coord_op_variant,
coord_op_method_id,
UOM_ID_SOURCE_OFFSETS,
UOM_ID_TARGET_OFFSETS,
information_source,
data_source,
show_operation,
is_legacy,
legacy_code,
reverse_op,
is_implemented_forward,
is_implemented_reverse)
VALUES (
19847,
'Popular Visualisation Mercator',
'CONVERSION',
NULL,
NULL,
'',
NULL,
9804,
NULL,
NULL,
NULL,
NULL,
1,
'FALSE',
NULL,
1,
1,
1);

returns ORA-00001: unique constraint

Adding optional coordinate parameters

INSERT INTO MDSYS.SDO_COORD_OP_PARAM_VALS (
COORD_OP_ID,
COORD_OP_METHOD_ID,
PARAMETER_ID,
PARAMETER_VALUE,
PARAM_VALUE_FILE_REF,
UOM_ID)
VALUES (
19847,
9804,
8801, -- Latitude of natural origin
0,
NULL,
9102);

returns ORA-00001: unique constraint

Adding optional coordinate parameters

INSERT INTO MDSYS.SDO_COORD_OP_PARAM_VALS (
COORD_OP_ID,
COORD_OP_METHOD_ID,
PARAMETER_ID,
PARAMETER_VALUE,
PARAM_VALUE_FILE_REF,
UOM_ID)
VALUES (
19847,
9804,
8802, -- longitude of natural origin
0,
NULL,
9102);

returns ORA-00001: unique constraint

Adding optional coordinate parameters

INSERT INTO MDSYS.SDO_COORD_OP_PARAM_VALS (
COORD_OP_ID,
COORD_OP_METHOD_ID,
PARAMETER_ID,
PARAMETER_VALUE,
PARAM_VALUE_FILE_REF,
UOM_ID)
VALUES (
19847,
9804,
8805, -- scale factor at natural origin
1,
NULL,
9201);

returns ORA-00001: unique constraint

Adding optional coordinate parameters

INSERT INTO MDSYS.SDO_COORD_OP_PARAM_VALS (
COORD_OP_ID,
COORD_OP_METHOD_ID,
PARAMETER_ID,
PARAMETER_VALUE,
PARAM_VALUE_FILE_REF,
UOM_ID)
VALUES (
19847,
9804,
8806, -- false easting
0,
NULL,
9001);

returns ORA-00001: unique constraint

Adding optional coordinate parameters

INSERT INTO MDSYS.SDO_COORD_OP_PARAM_VALS (
COORD_OP_ID,
COORD_OP_METHOD_ID,
PARAMETER_ID,
PARAMETER_VALUE,
PARAM_VALUE_FILE_REF,
UOM_ID)
VALUES (
19847,
9804,
8807, -- false northing
0,
NULL,
9001);

returns ORA-00001: unique constraint

Mandatory steps

Adding coord_ref_system

INSERT INTO sdo_coord_ref_system (
srid,
coord_ref_sys_name,
coord_ref_sys_kind,
coord_sys_id,
datum_id,
geog_crs_datum_id,
source_geog_srid,
projection_conv_id,
cmpd_horiz_srid,
cmpd_vert_srid,
information_source,
data_source,
is_legacy,
legacy_code,
legacy_wktext,
legacy_cs_bounds,
is_valid,
supports_sdo_geometry)
VALUES (
3857,
'Popular Visualisation CRS / Mercator',
'PROJECTED',
4499,
NULL,
6055,
4055,
19847,
NULL,
NULL,
NULL,
NULL,
'FALSE',
NULL,
NULL,
NULL,
'TRUE',
'TRUE');

:!: This works

Adding transformation rules

  • create the tfm_plans, i.e. transformation rule. Note: This will result in an incorrect conversion since it ignores a datum shift

between the ellipsoid and the sphere. However the data will match up better on google maps first for wgs84 (8307)

CALL sdo_cs.create_pref_concatenated_op( 83073857, 'CONCATENATED OPERATION 8307 3857', TFM_PLAN(SDO_TFM_CHAIN(8307, 1000000000, 4055, 19847, 3857)), NULL);

4326 is the EPSG equivalent of 8307

CALL sdo_cs.create_pref_concatenated_op( 43263857, 'CONCATENATED_OPERATION_4326_3857', TFM_PLAN(SDO_TFM_CHAIN(4326, 1000000000, 4055, 19847, 3857)), NULL);

similarly for os bng (oracle srid 81989 or epsg 27700 it is

CALL sdo_cs.create_pref_concatenated_op( 819893857, 'CONCATENATED OPERATION 81989 3857', TFM_PLAN(SDO_TFM_CHAIN(81989, -19916, 2000021, 1000000000, 4055, 19847, 3857)), NULL);

27700 is the EPSG equivalent of 81989

CALL sdo_cs.create_pref_concatenated_op( 277003857, 'CONCATENATED_OPERATION_27700_3857', TFM_PLAN(SDO_TFM_CHAIN(27700, -19916, 4277, 1000000000, 4055, 19847, 3857)), NULL);
commit;

Create rule for 82344

SELECT MDSYS.SDO_CS.DETERMINE_CHAIN(NULL, NULL, 82344, 3857) FROM DUAL;
-- output
MDSYS.TFM_PLAN(MDSYS.SDO_TFM_CHAIN(82344,-1,2000002,-2,4055,-3,3857))

This tells that 82344 goes to 2000002 and then to 3857

than define the rule

CALL sdo_cs.create_pref_concatenated_op( 
  200023857, 'CONCAT OPERATION 2000002 3857', TFM_PLAN(SDO_TFM_CHAIN(2000002, 1000000000, 4055, 19847, 3857)), NULL);

Testing

Testing

  • How to test if the installed rules are working correct:
SELECT sdo_cs.transform(sdo_geometry(2001, 8307, sdo_point_type(10,10,NULL), NULL, NULL), 3857) FROM dual;
-- output without rule is:
SDO_GEOMETRY(2001, 3857, SDO_POINT_TYPE(1113194.91, 1111474.85, NULL), NULL, NULL)
-- output with the rules defined to not to the datum shift, the answer is
SDO_GEOMETRY(2001, 3857, SDO_POINT_TYPE(1113194.91, 1118889.97, NULL), NULL, NULL)

Rest of conversation

So your source SRID is 82344 and your target SRID is 3785, is this correct ?

If this is the case, you need to define a rule to go from 82344 to 3785 without doing the datum shift. The example I pointed out earlier only shows this for 8307 to 3785. So you can either define a rule for 82344 to 3785 or you can use this work around:

The first one is probably what you get:

SELECT sdo_cs.transform(sdo_geometry(2001, 82344, sdo_point_type(678225.9457,5144181.3145,NULL), NULL, NULL), 3785) FROM dual;

This is what you want: Go from 82344 to 8307, then go to 3785. This way, the rules you have added for Google Map projection will be used.

SELECT sdo_cs.transform(sdo_cs.transform(sdo_geometry(2001, 82344,
sdo_point_type(678225.9457,5144181.3145,NULL), NULL, NULL), 8307), 3785) FROM dual;
CALL sdo_cs.create_pref_concatenated_op( 83073785, 'CONCATENATED OPERATION 8307 3785', TFM_PLAN(SDO_TFM_CHAIN(8307, 1000000000, 4055, 19847, 3785)), NULL);

Yes, a rule will be defined by calling the create_pref_concatenated_op() method.

So if you want to define a rule for your case, do these steps:

SELECT MDSYS.SDO_CS.DETERMINE_CHAIN(NULL, NULL, 82344, 3785) FROM DUAL;
TFM_PLAN(SDO_TFM_CHAIN(82344, -1, 2000002, -2, 4055, -3, 3785))

This will tell you the default chain of operation.

This should show you that it goes from 82344 to 2000002 to 3785.

Here 2000002 is similar to 8307. So you need to create a rule that converts 2000002 to 3785 without doing the datum shift.

So you need to create the following rule:

CALL sdo_cs.create_pref_concatenated_op( 200023785, 'CONCAT OPERATION 2000002 3785', TFM_PLAN(SDO_TFM_CHAIN(2000002, 1000000000, 4055, 19847, 3785)), NULL);

After that, your transform will use this rule and the result should be what you expect.

SELECT sdo_cs.transform(sdo_geometry(2001, 82344, sdo_point_type(678225.9457,5144181.3145,NULL), NULL, NULL), 3785) FROM dual;

Before the rule:

SDO_GEOMETRY(2001, 3785, SDO_POINT_TYPE(1260099.58, 5818161.99, NULL), NULL, NULL)

After the rule:

SDO_GEOMETRY(2001, 3785, SDO_POINT_TYPE(1260099.58, 5849151.08, NULL), NULL, NULL)

Hope that helps.

siva

 
 

Personal Tools