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!

Simon

Am I to understand that this BEST vicGrid is not supposed to be the same as the “standard” VicGrid94 defined by the Government? I was wondering why you have centerLat, firstStdLat and secondStdLat that are significantly wider than the standard spec? The range also does not cover all of Victoria and is centered off Western Australia.

Perhaps I am the odd Victorian user…

James K.

— James Dec 11, 10:02 am #
James,

Correct, BEST VicGrid is not a Government defined grid.

The values of the projection were as provided to me by a customer in Victoria working in emergency services: I can only hope that I transcribed them correctly.

If I did then I can only assume that the non-Victorian targeted parameters (eg central meridian of 135 degrees) were because the company that created the projection wanted to provide dispatch in more than one state or the person defining the projection simply got it from someone else who got it from someone else…..

If you can find out that I have an error in translation, please let me know and I will correct it.

Simon

— Simon Dec 11, 10:37 am #
Simon

Thanks for the explanation.

My mistake saying it was centred over WA, I wasn’t concentrating. The values given do make sense if the aim is to provide a decent appoximation for most of eastern Aus rather than just Vic.

At least if they are an emergency services mob they aren’t just using a system based on utm zones, like the CFA do here in Vic. It took me too much effort to write a procedure to convert the coordinate supplied by the CFA for fires into a simple lat /long.

James

— James Dec 11, 02:47 pm #

<<Making Sdo Geometry Metadata Update Generic Code >>Performance of PL/SQL Functions using SQL or not

Name		Remember
E-mail
http://
Message
	Textile Help