Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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.