Implementing the BEST VicGrid Projection in Oracle 10gR2
There is a Lambert Conformal Conic projection used by Computer Aided Dispatch (CAD) in Victoria, Australia that is called BEST VicGrid.
It is not available as part of the standard Oracle install (most Australian projections in are provided to Oracle by Michael Stoke’s Navigate company in Sydney), and so has to be created. Here is how to create and test it.
I have given the new projection the SRID 82476.
DELETE FROM SDO_COORD_REF_SYSTEM
WHERE srid = 82476;
COMMIT;
SELECT *
FROM cs_srs
WHERE srid = 82476;
Prompt Now insert our Best VicGrid....
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 (
82476, /* SRID */
'BEST VICGRID94 Conformal Projection (Australia GDA94)', /* COORD_REF_SYS_NAME */
'PROJECTED', /* COORD_REF_SYS_KIND */
4400, /* COORD_SYS_ID */
NULL, /* DATUM_ID -- 'tis NULL because is projected See select * from sdo_coord_ref_sys where GEOG_CRS_DATUM_ID = 20005;*/
20005, /* GEOG_CRS_DATUM_ID */
2000012, /* SOURCE_GEOG_SRID */
NULL, /* PROJECTION_CONV_ID - Same as VICGRID94 ie NULL */
NULL, /* CMPD_HORIZ_SRID */
NULL, /* CMPD_VERT_SRID */
'SpatialDB Advisor', /* INFORMATION_SOURCE */
null, /* DATA_SOURCE */
'FALSE', /* IS_LEGACY */
NULL, /* LEGACY_CODE */
'PROJCS["BEST VICGRID94 Conformal Projection (Australia GDA94)",GEOGCS [ "Geodetic Datum of Australia 1994", DATUM ["GDA 94", SPHEROID ["GRS 80", 6378137.000000, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Lambert Conformal Conic"], PARAMETER ["Standard_Parallel_1", -18.000000], PARAMETER ["Standard_Parallel_2", -36.000000],PARAMETER ["Central_Meridian", 135.000000], PARAMETER ["Latitude_Of_Origin", -27.000000], PARAMETER ["False_Easting", 2500000.00000], PARAMETER ["False_Northing", 2500000.00000], UNIT ["Meter", 1.000000000000]]',
/* LEGACY_WKTEXT */
NULL, /* LEGACY_CS_BOUNDS */
'FALSE', /* EPSG record for the coordinate reference system is NOT completely defined */
'TRUE') /* SUPPORTS_SDO_GEOMETRY has to be TRUE for PROJECTED CS */
/
COMMIT;
Now let’s conduct some tests. For this I will compare Oracle against the only GIS I own, Manifold GIS.
Prompt Conduct tests....
-- What projections are used in the test?
SELECT CS_NAME,SRID,AUTH_SRID,AUTH_NAME,WKTEXT
FROM cs_srs
WHERE srid IN (82472,82473,82476,81938,82469);
CS_NAME SRID AUTH_SRID AUTH_NAME WKTEXT
------------------------------------------------- --------- ----------- ------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AMG Zone 56 (AGD 66) 81938 81938 Oracle PROJCS["AMG Zone 56 (AGD 66)", GEOGCS [ "Australian Geodetic 1966", DATUM ["Australian Geodetic 1966", SPHEROID ["Australian", 6378160, 298.25]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Transverse Mercator"], PARAMETER ["Scale_Factor", 0.999600], PARAMETER ["Central_Meridian", 153.000000], PARAMETER ["False_Easting", 500000.000000], PARAMETER ["False_Northing", 10000000.000000], UNIT ["Meter", 1.000000000000]]
MGA94 Zone 56 82469 82469 Navigate PROJCS["MGA94 Zone 56", GEOGCS [ "Geodetic Datum of Australia 1994", DATUM ["GDA 94", SPHEROID ["GRS 80", 6378137.000000, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Transverse Mercator"], PARAMETER ["Scale_Factor", 0.999600], PARAMETER ["Central_Meridian", 153.000000], PARAMETER ["False_Easting", 500000.000000], PARAMETER ["False_Northing", 10000000.000000], UNIT ["Meter", 1.000000000000]]
VICGRID66 82472 82472 Navigate PROJCS["VICGRID66 Conformal Projection (Australia AGD66)", GEOGCS [ "AGD 66 VIC NSW", DATUM ["AGD 66 VIC NSW", SPHEROID ["Australian", 6378160, 298.25],-119.35,-48.3,139.48,-.42,-.26,-.44,-.61], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Lambert Conformal Conic"], PARAMETER ["Standard_Parallel_1", -36.000000], PARAMETER ["Standard_Parallel_2", -38.000000], PARAMETER ["Central_Meridian", 145.000000], PARAMETER ["Latitude_Of_Origin", -37.000000], PARAMETER ["False_Easting", 2500000.00000], PARAMETER ["False_Northing", 4500000.00000], UNIT ["Meter", 1.000000000000]]
VICGRID94 82473 82473 Navigate PROJCS["VICGRID94 Conformal Projection (Australia GDA94)", GEOGCS [ "Geodetic Datum of Australia 1994", DATUM ["GDA 94", SPHEROID ["GRS 80", 6378137.000000, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Lambert Conformal Conic"], PARAMETER ["Standard_Parallel_1", -36.000000], PARAMETER ["Standard_Parallel_2", -38.000000], PARAMETER ["Central_Meridian", 145.000000], PARAMETER ["Latitude_Of_Origin", -37.000000], PARAMETER ["False_Easting", 2500000.00000], PARAMETER ["False_Northing", 2500000.00000], UNIT ["Meter", 1.000000000000]]
BEST VICGRID94 Conformal Projection (Australia GDA94) 82476 82476 SpatialDB Advisor PROJCS["BEST VICGRID94 Conformal Projection (Australia GDA94)",GEOGCS [ "Geodetic Datum of Australia 1994", DATUM ["GDA 94", SPHEROID ["GRS 80", 6378137.000000, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Lambert Conformal Conic"], PARAMETER ["Standard_Parallel_1", -18.000000], PARAMETER ["Standard_Parallel_2", -36.000000],PARAMETER ["Central_Meridian", 135.000000], PARAMETER ["Latitude_Of_Origin", -27.000000], PARAMETER ["False_Easting", 2500000.00000], PARAMETER ["False_Northing", 2500000.00000], UNIT ["Meter", 1.000000000000]]
5 rows selected
Prompt Test Oracle against Manifold GIS ...
Test Oracle against Manifold GIS ...
/* Here is the custom preset projection used in Manifold GIS
<xml>
<preset>
<name>BEST VICGRID94</name>
<category>National Grids*Australia</category>
<system>Lambert Conformal Conic</system>
<datum>Australian Geocentric 1994 (GDA94)</datum>
<centerLat>-27.0</centerLat>
<centerLon>135.0</centerLon>
<firstStdLat>-18.0</firstStdLat>
<secondStdLat>-36.0</secondStdLat>
<falseEasting>2500000.0</falseEasting>
<falseNorthing>2500000.0</falseNorthing>
</preset>
</xml>
*/
SELECT round(a.geom.sdo_point.x,6) as OX,
round(a.geom.sdo_point.y,6) as OY,
round(a.manifoldx,6) as Mx,
round(a.manifoldy,6) as My
FROM (SELECT SDO_CS.TRANSFORM(MDSYS.SDO_GEOMETRY(2001,82469,SDO_POINT_TYPE(300000,7800000,NULL),NULL,NULL), 82476) as geom,
4172735.1951413983 as ManifoldX,
3173070.9409930659 as ManifoldY
FROM DUAL
) a;
OX OY MX MY
---------------------- ---------------------- ---------------------- ----------------------
4172735.195141 3173070.940993 4172735.195141 3173070.940993
1 rows selected
I hope this article is of use to the odd Victorian Oracle user!