|
Extract Polygons from Compound Polygon
Thursday November 12 2009 at 22:03
I was asked the other day:
In thinning the data we sometimes end up with collection features (2004 gtype), and I need to pull out the relevant geometry type (usually polygon) from the collection, because of course SDE is not happy with 2004 gtypes.
I have covered this before, but often people email me asking for single function solutions based on a specific release.
This particular customer is running 10gR2 so a custom, standalone, function is a lot easier than the one in my free PL/SQL packages as those packages have to handle 8i, 9i etc which don’t have some of the sdo_util functions at 10gR2.
For 10gR2 and above users, this is what you need as a standalone function for extracting polygons (2003 or 2007) from a compound (2004) object.
Create Or Replace Function ExtractPolygon( p_geometry IN MDSYS.SDO_GEOMETRY )
RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC
IS
v_element number;
v_elements number;
v_geometry mdsys.sdo_geometry;
v_extract_shape mdsys.sdo_geometry;
Begin
IF ( MOD(p_geometry.sdo_gtype,10) <> 4 ) Then
RETURN p_geometry;
END IF;
v_elements := mdsys.sdo_util.GetNumElem(p_geometry);
FOR v_element IN 1..v_elements LOOP
v_extract_shape := mdsys.sdo_util.Extract(p_geometry,v_element,0); -- Extract element with all sub-elements
IF ( v_extract_shape.Get_Gtype() = 3 ) Then
IF ( v_geometry is null ) Then
v_geometry := v_extract_shape;
ELSE
v_geometry := MDSYS.SDO_UTIL.APPEND(v_geometry,v_extract_shape);
END IF;
END IF;
END LOOP;
RETURN( v_geometry );
END ExtractPolygon;
Let’s test it with a compound sdo_geometry object.
select extractpolygon( mdsys.sdo_geometry (2004, null, null, mdsys.sdo_elem_info_array (1,1,1, 3,2,1, 7,1003,1),
mdsys.sdo_ordinate_array (10,5, 10,10, 20,10, 10,105, 15,105, 20,110, 10,110, 10,105))) as poly
from dual;
POLY
-----------------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(10,105,15,105,20,110,10,110,10,105))
1 rows selected
I hope this helps someone.
        
|
SpatialDB Advisor
Home
Comment [4]
Thanks so much, this is exactly what I needed. Worked perfect.
— Tom Wayson · 4 December 2010, 04:30 · #
Tom,
Pleased to hear about your success.
If you have any other successes with my code, please let me know.
If you don’t, let me know why.
If you have an idea for something extra, let me know.
I am the chief programmer for GeoRaptor: try it (though wait a few days as a new release
is about to go out).
regards
Simon
— Simon Greener · 4 December 2010, 21:42 · #
i have hundreds of polygons and hundreds of rows (1 polygon = 1 row), then i have latitude and longitude, but in unknow polygon, how to find it in my table of polygons? exist a function that returns a polygon from latlong? is the right way 1 row by 1 geometry?
— Samir De Falco · 27 April 2011, 05:59 · #
Samir,
Let’s assume you have a table called GEODPOLY2D containing your polygons and another table GEODPOINT2D containing your points as two columns of numbers called lon and lat.
Here is an SQL statement that will associate them:
select a.id as polyId, b.id as pointId from geodPoly2d a, geodpoint2d b where sdo_contains(a.geom,sdo_geometry(2001,a.geom.sdo_srid,mdsys.sdo_point_type(b.lon,b.lat,null),null,null)) = 'TRUE';Hope this is what you were after.
regards
Simon
— Simon Greener · 27 April 2011, 16:06 · #