SpatialDB Advisor

Current Oracle Spatial Blogs

Converting distances and units of measure in Oracle Locator

(read more..)

Generating multi-points from single point records in Oracle Spatial

(read more..)

Multi-Centroid Shootout

(read more..)

Oracle Locator vs Oracle Spatial: A Reflection on Oracle Licensing of the SDO_GEOM Package

(read more..)

Oracle Spatial Centroid Shootout

(read more..)

Oracle Spatial Forum - Melbourne April 2007

(read more..)

Oracle Spatial Mapping and Map Rendering Performance Tips

(read more..)

Oracle's SQL/MM Compliant Types

(read more..)

Spatial Pipelining

(read more..)

Split Sdo_Geometry Linestring at a known point

(read more..)

The significance of sdo_lb/sdo_ub in USER_SDO_GEOM_METDATA: Do I need it?

(read more..)

Tip #2: layer_gtypes for spatial indexes

(read more..)

Tip #3: Generating sample (test) point data

(read more..)

Tip #4: Loading Shapefiles (SHP) into Oracle Spatial

(read more..)

Tip #5: FAST REFRESHing of Oracle Materialized Views containing Sdo_Geometry columns

(read more..)

Tip #6: Unpacking USER_SDO_GEOM_METADATA's DIMINFO structure

(read more..)

Tip #7: Forcing Sdo_Geometry to contain only points, lines or areas

(read more..)

Tips and Tricks

(read more..)

Trick #1: Australian MGA/AMG Zone Calculation from geographic (longitude/latitude) data

(read more..)

Trick #2: Object Tables of Sdo_Geometry

(read more..)

Using Oracle's SDO_NN Operator - Some examples

(read more..)

RSS / Atom

Email me

Trick #1: 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 */
)
/

<<Tip #4: Loading Shapefiles (SHP) into Oracle Spatial >>Tip #5: FAST REFRESHing of Oracle Materialized Views containing Sdo_Geometry columns