Top 5 Recent Articles

ARTICLES CATEGORIES

Intersecting two aggregated polygon layers with SC4O

Someone downloaded my Spatial Companion for Oracle (SC4O) package the other day, then asked asked the following question(s):

I have to calculate the intersection between two regions on a map. Each region is represented in the db as a >column in different tables. For now I have two columns of polygons one with 1500 entries and other with 600 entries. How can i do this in a faster way? Is it faster to join them in two resulting multipolygons and then do the intersection?

Now this can be done using the SC4O package by first unioning all the polygons in each sdo_geometry column into two multipolygon (probably) objects. (One for each sdo_geometry column.) This can be done using SC4O.ST_AggrUnionPolygons.

Once these two mutli-polygon objects have been created, use SC4O.ST_Intersection to create their intersection.

Standalone Example

 WITH polys1500 AS (
   SELECT mdsys.sdo_geometry(2003,82469,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(1,1,10,10)) AS geom FROM dual UNION ALL
   SELECT mdsys.sdo_geometry(2003,82469,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(5,5,15,15)) AS geom FROM dual
 ),
 polys900 AS (
   SELECT mdsys.sdo_geometry(2003,82469,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(0,0,2,2)) AS geom FROM dual UNION ALL
   SELECT mdsys.sdo_geometry(2003,82469,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(-1,-1,7,7)) AS geom FROM dual
 )
 SELECT codesys.SC4O.ST_Intersection(codesys.SC4O.ST_AggrUnionPolygons(CAST(COLLECT(p15.geom) AS mdsys.sdo_geometry_array),1,0.05),
                                     codesys.SC4O.ST_AggrUnionPolygons(CAST(COLLECT(p9.geom) AS mdsys.sdo_geometry_array),1,0.05),
                                     2) AS RESULT
   FROM polys1500 p15,
        polys900 p9;
 -- Result
 --
 RESULT
 -------------------------------------------------------------------------------------------------------
 SDO_GEOMETRY(2003,82469,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(7,1, 7,7, 1,7, 1,1, 7,1))

I hope this is of interest to any SC4O users out there.