By Ashish M Jain-Oracle on Feb 21, 2012
Some times there is a requirement to merge two or more boundaries/borders into one, such as a custom region/territory. For eg. you may need to merge California, Oregon and Washington which are the states that comprise the Western US region.
In order to achieve this, I assume that you already have the state boundaries for California, Oregon and Washington. If not, you can get it easily on otn, that includes the standard boundaries. in the world_sample schema. Once you have this information you can use the Oracle Spatial function SDO_AGGR_UNION. Hence, you can combine several standard boundaries into a new region.
Below is an way how you would merge the states mentioned above into a new custom Western US region.
INSERT INTO custom_area SELECT 'Western US', SDO_AGGR_UNION(SDOAGGRTYPE(o.geometry, 0.5)) FROM wom_area WHERE iso_country_code = 'USA' and feature_type = 909996 and name in ('CALIFORNIA', 'OREGON', 'WASHINGTON');
Here, the SDOAGGRTYPE is an object that specifies the geometry column from the spatial table wom_area and the dimensional array.