|
Australian MGA/AMG Zone Calculation from geographic (longitude/latitude) data
Friday June 08 2007 at 21:46
Someone asked on Oracle Technet recently about how to compute the Australian AMG/MGA (UTM) zone from longitude latitude data (geographicals) expressed in NAD84.
My solution is via a single SQL statement as follows (the first statement is just to show the SRIDs associated with the right projections.
DEFINE BASE_AGD66=81857
DEFINE BASE_AGD84=81858
DEFINE AGD6684=2
SET PAGESIZE 1000 LINESIZE 150
COLUMN CS_NAME FORMAT A25 WRAPPED
select srid,cs_name
from mdsys.cs_srs
where UPPER like ‘AMG ZONE‘
or UPPER like ‘MGA94 ZONE‘
order by srid
/
COLUMN AMG66Name FORMAT A20 WRAPPED
COLUMN AMG84Name FORMAT A20 WRAPPED
COLUMN MGA94Name FORMAT A20 WRAPPED
SELECT longitude,
latitude,
UTMZone,
AMG66SRID,
(SELECT cs_name FROM mdsys.cs_srs WHERE SRID = AMG66SRID)
AS AMG66Name,
mdsys.sdo_cs.transform(mdsys.sdo_geometry(2001,8265,MDSYS.SDO_POINT_TYPE(longitude,latitude,NULL),NULL,NULL),AMG66SRID)
AS AMG66Geom,
AMG84SRID,
(SELECT cs_name FROM mdsys.cs_srs WHERE SRID = AMG84SRID)
AS AMG84Name,
mdsys.sdo_cs.transform(mdsys.sdo_geometry(2001,8265,MDSYS.SDO_POINT_TYPE(longitude,latitude,NULL),NULL,NULL),AMG84SRID)
AS AMG84Geom,
MGA94SRID,
(SELECT cs_name FROM mdsys.cs_srs WHERE SRID = MGA94SRID) AS MGA94Name,
mdsys.sdo_cs.transform(mdsys.sdo_geometry(2001,8265,MDSYS.SDO_POINT_TYPE(longitude,latitude,NULL),NULL,NULL),MGA94SRID)
AS MGA94Geom
FROM (SELECT LEVEL + 95 as longitude,
dbms_random.value(-10,-45) as latitude,
31 + trunc((LEVEL + 95) / 6) as UTMZone,
&&BASE_AGD66. + 31 + ( trunc((LEVEL + 95) / 6) * &&AGD6684. ) as AMG66SRID,
&&BASE_AGD84. + 31 + ( trunc((LEVEL + 95) / 6) * &&AGD6684. ) as AMG84SRID,
82413 + 31 + trunc((LEVEL + 95) / 6) as MGA94SRID
FROM dual
CONNECT BY LEVEL < ( 168 – 96 + 1 ) /* Max Long is 168 and Min is 96 */
)
/
        
|
SpatialDB Advisor
Home
Comment