Go to content Go to navigation and search

Home

Current Oracle Spatial Blogs

Search

RSS / Atom

Email me

textpattern

Australian MGA/AMG Zone Calculation from geographic (longitude/latitude) data

· Jun 8, 09:46 pm by Simon Greener

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 */
)
/
post this at del.icio.uspost this at Diggpost this at Technoratipost this at Newsvinepost this at Ma.gnoliapost this at Furlpost this at Blinklistpost this at Spurlpost this at Wistspost this at Simpypost this at Redditpost this at Farkpost this at Blogmarkspost this at Yahoo! my webpost this at Mr. Wongpost this at Windows Livepost this at Google Bookmarkspost this to Twitter
  Textile Help