Tuesday, February 21, 2012

Merging Geometry boundaries using Oracle spatial

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.

No comments:

Post a Comment