Free Union, Intersection, Xor and Difference Functions for Oracle Locator – Part 4 Processing Geodetic data

As I have indicated in this series, the Java Topology Suite (JTS) collection of algorithms on which this implementation is based, currently does not support geodetic computations.

Simply put, if your data is longitude/latitude then the results of any computations will be inaccurate.

If you wish this package to work geodetic data and produce correct results then you should:

• Transform (sdo_cs.transform) your data to a suitable projected coordinate system, eg UTM;
• Call the relevant GEOPROCESS function;
• Transform the result back to you original geodetic SRID.

Worked examples of this will now be provided:

 select SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-79.230998,35.837119,-79.230480,35.836701,-79.230386,35.836838,-79.230884,35.837222,-79.230998,35.837119)) as g1,
         SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-79.230974,35.836828,-79.230802,35.836720,-79.230454,35.837183,-79.230664,35.837300,-79.230974,35.836828)) as g2
 from dual;

 -- What is SRID 8265?
 select cs_name 
   from cs_srs 
  where srid = 8265;
  
 CS_NAME
 -----------------------------
 Longitude / Latitude (NAD 83)
  
 -- What is a suitable UTM zone to use when processing this SRID's data?
 --
 select srid, central_meridian
   from (select srid, cs_name, to_number(replace(t.column_Value,'"Central_Meridian", ',null),'S999.000000') as central_meridian
           from cs_srs c,
                table(geom.tokenizer(c.wktext,'[]'))t
          where cs_name like 'UTM%NAD 83%' 
            and t.column_value like '"Central_Meridian%'
        ) cm,
        (select centroid.sdo_centroid(SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-79.230998,35.837119,-79.230480,35.836701,-79.230386,35.836838,-79.230884,35.837222,-79.230998,35.837119)),0.0000001) as point from geodpoly2d p) p
  where p.point.sdo_point.x between cm.central_meridian - 3 and cm.central_meridian + 3 ;
  
 SRID  CENTRAL_MERIDIAN
 ----- ----------------
 82247 -81
  
 -- Now execute a union (correctly)
 With twoPolys As (
 select SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-79.230998,35.837119,-79.230480,35.836701,-79.230386,35.836838,-79.230884,35.837222,-79.230998,35.837119)) as g1,
         SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-79.230974,35.836828,-79.230802,35.836720,-79.230454,35.837183,-79.230664,35.837300,-79.230974,35.836828)) as g2
 from dual 
 )
 select sdo_cs.transform(pinter,8265) as  geom8265
   from (select geoprocess.intersection(
                   sdo_cs.transform(g1,82247),
                   sdo_cs.transform(g2,82247),6) pinter
           from twoPolys
        );

Buffering is less complicated

 select sdo_cs.transform(geoprocess.buffer(sdo_cs.transform(g1,82247),10,2),8265) as buffGeom
   from (select SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-79.230998,35.837119,-79.230480,35.836701,-79.230386,35.836838,-79.230884,35.837222,-79.230998,35.837119)) as g1
           from dual 
        );

Finally, sdo_cs.transform() comes with a heavy setup cost. This is OK if you are only processing a single geometry or two but if you are processing a lot, such as buffering a whole table of geometry data, then those costs will create a significant performance penalty. When bulk processing data it is better to use sdo_cs.transform_layer() as the setup costs are incurred once, with the transformation parameters reused for each and every geometry processed.