Oracle support for GDA2020

Australia is moving from the GDA94 Datum to the new GDA2020 datum. To support this, you can run the following script in an Oracle database versions 11.2, 12.1 and 12.2.to support the new data. 

These scripts must be run as MDSYS.You may need to unlock the MDSYS account to use it, and you might also need to change the password - both of which are fine. It's a good idea to lock the account after running these scripts too.

Legal note: I take no responsibility for these scripts. I've not tested them, I have not used them, I don't guarantee you that they work in any way.... they are here just for your amusement, perusal and to give you some guidelines on using such scripts...

First, if your running in 11.2.0.4 you will need to run this script:

insert into "MDSYS"."SDO_COORD_OPS" values
(17359,'SA Lambert (EPSG OP 17359)','CONVERSION',NULL,NULL,NULL,NULL,9802,NULL,NULL,
'South Australia Department for Environment and Heritage, Spatial Information Committee (SICOM); http://www.environment.sa.gov.au/mapland/sicom/sicom/lambert.html',
'OGP',1,'FALSE',NULL,1,1,1);

Then to update the SDR's... run these scripts....

update
sdo_datums
set
shift_x = 0.06155,
shift_y = -0.01087,
shift_z = -0.04019,
rotate_x = 0.0394924,
rotate_y = 0.0327221,
rotate_z = 0.0328979,
scale_adjust = -0.009994
where
datum_id = 6283;

INSERT INTO SDO_DATUMS(DATUM_ID,DATUM_NAME,DATUM_TYPE,ELLIPSOID_ID,PRIME_MERIDIAN_ID,INFORMATION_SOURCE,
DATA_SOURCE,IS_LEGACY)
VALUES
(1168,'Geocentric Datum of Australia 2020','GEODETIC',7019,8901,'Geodesy Section, Geoscience Australia.',
'IOGP','FALSE' );

commit;

INSERT INTO SDO_COORD_REF_SYSTEM
(SRID,COORD_REF_SYS_NAME,COORD_REF_SYS_KIND,COORD_SYS_ID,DATUM_ID,GEOG_CRS_DATUM_ID,
INFORMATION_SOURCE,DATA_SOURCE,IS_LEGACY,IS_VALID,SUPPORTS_SDO_GEOMETRY)
VALUES
(7843,'GDA2020','GEOGRAPHIC3D',6423,1168,1168,'Geodesy Section, Geoscience Australia.','IOGP','FALSE',
'TRUE','TRUE');

INSERT INTO SDO_COORD_REF_SYSTEM
(SRID,COORD_REF_SYS_NAME,COORD_REF_SYS_KIND,COORD_SYS_ID,DATUM_ID,GEOG_CRS_DATUM_ID,
INFORMATION_SOURCE,DATA_SOURCE,IS_LEGACY,IS_VALID,SUPPORTS_SDO_GEOMETRY)
VALUES
(7844,'GDA2020','GEOGRAPHIC2D',6422,1168,1168,'Geodesy Section, Geoscience Australia.','IOGP','FALSE',
'TRUE','TRUE');

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)
VALUES
(7852,'GDA2020 / MGA zone 52','PROJECTED',4400,NULL,1168,7844,17352,NULL,NULL,'Geodesy Section, Geoscience Australia.',
'IOGP','FALSE',NULL,NULL,NULL);

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)
VALUES
(7853,'GDA2020 / MGA zone 53','PROJECTED',4400,NULL,1168,7844,17353,NULL,NULL,'Geodesy Section, Geoscience Australia.',
'IOGP','FALSE',NULL,NULL,NULL);

call sdo_cs.update_wkts_for_epsg_datum(datum_id => 6283);

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)
VALUES
(7854,'GDA2020 / MGA zone 54','PROJECTED',4400,NULL,1168,7844,17354,NULL,NULL,'Geodesy Section, Geoscience Australia.',
'IOGP','FALSE',NULL,NULL,NULL);

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)
VALUES
(8059,'GDA2020 / SA Lambert','PROJECTED',4400,NULL,1168,7844,17359,NULL,NULL,'Development Division, South Australia Department of Planning,
Transport and Infrastructure.','IOGP','FALSE',NULL,NULL,NULL);

commit;

and to test the scripts...

-- Test:
select sdo_cs.transform(sdo_geometry(3001, 4939, sdo_point_type(133.88551329, -23.67012389, 603.3466), null, null), 7843) from dual;
select sdo_cs.transform(sdo_geometry(3001, 4939, sdo_point_type(129.8102423, -17.78524706, 450.404), null, null), 7843) from dual;

select srid, wktext from cs_srs where srid in (7852, 7853) order by srid;

select sdo_cs.transform(sdo_geometry(3001, 7852, sdo_point_type(500000, 10000000, null), null, null), 7844) from dual;
select sdo_cs.transform(sdo_geometry(3001, 7853, sdo_point_type(500000, 10000000, null), null, null), 7844) from dual;