Free Union, Intersection, Xor and Difference Functions for Oracle Locator – Part 3

Here as some test on data other than just polygons.

Union of two points

``` SELECT SC4O.ST_CoordinateRounder(SC4O.ST_Union(g1,g2,1),1) AS geo_union,
SC4O.ST_CoordinateRounder(sdo_geom.sdo_union(g1,g2,0.05),1) AS Oracle
FROM (SELECT SDO_GEOMETRY(2001, 32639, SDO_POINT_TYPE(548810.44489, 3956383.07564,NULL),NULL,NULL) g1,
SDO_GEOMETRY(2001, 32639,SDO_POINT_TYPE(548766.398, 3956415.329,NULL), NULL, NULL) g2
FROM dual);
-- Results
GEO_UNION
------------------------------------------------------------------------------------------------------------------------------------
ORACLE
------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2005,32639,NULL,SDO_ELEM_INFO_ARRAY(1,1,2),SDO_ORDINATE_ARRAY(548766.4,3956415.3,548810.4,3956383.1))
SDO_GEOMETRY(2005,32639,NULL,SDO_ELEM_INFO_ARRAY(1,1,1,3,1,1),SDO_ORDINATE_ARRAY(548810.4,3956383.1,548766.4,3956415.3))
```

These are two representations of the same thing.

``` SELECT SC4O.ST_CoordinateRounder(SC4O.ST_Union(g1,g2,1),1) AS jtsgeom,
SC4O.ST_CoordinateRounder(sdo_geom.sdo_union(g2,g1,0.05),1) AS oraGeom,
SDO_GEOM.Relate(SC4O.ST_CoordinateRounder(SC4O.ST_Union(g1,g2,1),1),
'DETERMINE',
geom.roundOrdinates(sdo_geom.sdo_union(g2,g1,0.05),0.05),
1) AS compare
FROM (SELECT SDO_GEOMETRY(2001, 32639, SDO_POINT_TYPE(548810.44489, 3956383.07564,NULL),NULL,NULL) g1,
SDO_GEOMETRY(2001, 32639, SDO_POINT_TYPE(548766.398, 3956415.329,NULL), NULL, NULL) g2
FROM dual);
-- Results
JTSGEOM                                                                                                            ORAGEOM                                                                                                                  COMPARE
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -----
SDO_GEOMETRY(2005,32639,NULL,SDO_ELEM_INFO_ARRAY(1,1,2),SDO_ORDINATE_ARRAY(548766.4,3956415.3,548810.4,3956383.1)) SDO_GEOMETRY(2005,32639,NULL,SDO_ELEM_INFO_ARRAY(1,1,1,3,1,1),SDO_ORDINATE_ARRAY(548766.4,3956415.3,548810.4,3956383.1)) EQUAL
```

Now if we replace SDO_GEOM.RELATE with SC4O.ST_RELATE we do not get equals. The reason for this is that the SDO_Geometry to JTS Geometry converter supplied by GeoTools does not handle the sdo_elem_info_array – (1,1,1,3,1,1) – produced by SDO_UNION.

``` SELECT SC4O.ST_Relate(a.jtsgeom,
'EQUALS',
sdo_geometry(a.orageom.sdo_gtype,
a.orageom.sdo_srid,
a.orageom.sdo_point,
a.jtsgeom.sdo_elem_info,
a.orageom.sdo_ordinates),
1) AS compare
FROM (SELECT SC4O.ST_CoordinateRounder(SC4O.ST_Union(g1,g2,1),1) AS jtsgeom,
geom.roundOrdinates(sdo_geom.sdo_union(g2,g1,0.05),0.05) AS orageom
FROM (SELECT SDO_GEOMETRY(2001, 32639, SDO_POINT_TYPE(548810.44489, 3956383.07564,NULL),NULL,NULL) g1,
SDO_GEOMETRY(2001, 32639, SDO_POINT_TYPE(548766.398, 3956415.329,NULL), NULL, NULL) g2
FROM dual)
) a;
-- Results
COMPARE
-------
EQUALS
```

Intersection of a point and a line

``` SELECT SC4O.ST_CoordinateRounder(SC4O.ST_Intersection(g1,g2,1),1) AS GEO_UNION,
SC4O.ST_CoordinateRounder(sdo_geom.sdo_intersection(g1,g2,0.05),1) AS Oracle
FROM (SELECT SDO_GEOMETRY(2002, 32639, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(548766.398, 3956415.329, 548866.753, 3956341.844, 548845.366, 3956342.941)) g1,
SDO_GEOMETRY(2001, 32639, SDO_POINT_TYPE(548766.398, 3956415.329,NULL), NULL, NULL) g2
FROM dual);
-- Results
GEO_UNION
--------------------------------------------------------------------------------------
ORACLE
-----------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001,32639,SDO_POINT_TYPE(548766.4,3956415.3,NULL),NULL,NULL)
SDO_GEOMETRY(2001,32639,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(548766.4,3956415.3))
```

Which look like ….

Intersection of Two crossing lines

``` SELECT SC4O.ST_CoordinateRounder(SC4O.ST_Intersection(g1,g2,1),1) AS GEO_UNION,
SC4O.ST_CoordinateRounder(sdo_geom.sdo_intersection(g1,g2,0.05),1) AS Oracle
FROM (SELECT SDO_GEOMETRY(2002, 32639, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(548938.421,3956363.864,548823.852,3956379.758,548818.010,3956381.297,548812.139,3956382.844,548683.715,3956400.404)) g1,
SDO_GEOMETRY(2002, 32639, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(548766.398,3956415.329,548866.753,3956341.844,548845.366,3956342.941)) g2
FROM dual);
-- Results
GEO_UNION
--------------------------------------------------------------------------------------
ORACLE
-----------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001,32639,SDO_POINT_TYPE(548810.4,3956383.1,NULL),NULL,NULL)
SDO_GEOMETRY(2001,32639,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(548810.4,3956383.1))
```

Which look like ….

Intersection of a line and a polygon

``` SELECT SC4O.ST_CoordinateRounder(SC4O.ST_Intersection(g1,g2,1),1) AS GEO_UNION,
SC4O.ST_CoordinateRounder(sdo_geom.sdo_intersection(g1,g2,0.05),1) AS Oracle
FROM (SELECT SDO_GEOMETRY(2003, 32639, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(548862.366, 3956401.619, 548793.269, 3956409.845, 548785.043, 3956369.812, 548850.302, 3956361.587, 548862.366, 3956401.619)) g1,
SDO_GEOMETRY(2002, 32639, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(548766.398, 3956415.329, 548866.753, 3956341.844, 548845.366, 3956342.941)) g2
FROM dual);
-- Results
GEO_UNION
------------------------------------------------------------------------------------------------------------------------------------
ORACLE
------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2002,32639,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(548790.7,3956397.5,548837.6,3956363.2))
SDO_GEOMETRY(2002,32639,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(548790.7,3956397.5,548837.6,3956363.2))
```

Which look like ….

Intersection of a Point and polygon

``` SELECT SC4O.ST_CoordinateRounder(SC4O.ST_Intersection(g1,g2,1),1) AS GEO_UNION,
SC4O.ST_CoordinateRounder(sdo_geom.sdo_intersection(g1,g2,0.05),1) AS Oracle
FROM (SELECT SDO_GEOMETRY (2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY (13,106)) g1,
SDO_GEOMETRY (2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY (10,105, 15,105, 20,110, 10,110, 10,105)) g2
FROM dual);
-- Results
GEO_UNION
--------------------------------------------------------------------------
ORACLE
---------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(13,106,NULL),NULL,NULL)
SDO_GEOMETRY(2001,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(13,106))
```

Which look like ….

NOTE 1: JTS doesn’t currently support geodetic computations. If you wish to use this package with geodetic data 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 are provided in Part 4 of this series of articles.