Oracle Spatial add Google SRID conversion capability

oracle-spatial-google-projection.sql

------------------------------------------------
-- OPTIONAL 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

--- Rule creation
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
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)
-- 
-----------------------------------------------------------------------------------------
--- Source and additional material in:
--- https://www.inf.unibz.it/dis/wiki/doku.php?id=students:minnerebner:oracle:addingsrid
-----------------------------------------------------------------------------------------