Geodetic Data in Oracle

From NGDCWiki

Jump to: navigation, search

see section 6.2 Geodetic Coordinate Support in Oracle Spatial User's Guide and Reference

  • No polygon element can have an area larger than one-half the surface of the earth.
  • The following polygons will fail validation:

large non-optimized rectangle

insert into geopoly values (1,
   MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
   MDSYS.SDO_ORDINATE_ARRAY(-179,-89, 179,-89, 179,89, -179,89, -179,-89)));

large optimized rectangle

insert into geopoly values (2,
    mdsys.sdo_geometry(2003,8307,null,mdsys.sdo_elem_info_array(1,1003,3),
   mdsys.sdo_ordinate_array(-179,-89, 179,89)));


  • Geodetic tables use meters for tolerances. 0.5m is the smallest usable tolerance. Specifying anything smaller will use 0.5m.
  • polygons that share more than a single point (e.g. have a common line boundary) will fail validation, but are usable by oracle.
  • Areas larger than 1/2 surface of earth need to be split and each part processed independently.
  • Dan Gerringer provides the following example using "union all" and sdo_aggr_centroid to find centroid of two polygons which share a common boundary:
select sdo_geometry (2003, 8307, null,
                    sdo_elem_info_array (1,1003,1),
                    sdo_ordinate_array (0,0, 1,0, 1,1, 0,1, 0,0)) geom
from dual
 union all
select sdo_geometry (2003, 8307, null,
                    sdo_elem_info_array (1,1003,1),
                    sdo_ordinate_array (1,0, 2,0, 2,1, 1,1, 1,0)) geom
from dual;


select sdo_aggr_centroid (sdoaggrtype (geom, 0.05))
from (
 select sdo_geometry (2003, 8307, null,
                      sdo_elem_info_array (1,1003,1),
                      sdo_ordinate_array (0,0, 1,0, 1,1, 0,1, 0,0)) geom
 from dual
   union all
 select sdo_geometry (2003, 8307, null,
                      sdo_elem_info_array (1,1003,1),
                      sdo_ordinate_array (1,0, 2,0, 2,1, 1,1, 1,0)) geom
 from dual);
Personal tools