Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [6]

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, 04: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, 15:06 · #

Excellent! Exactly what I needed. Thank you!

— Matt Allen · 21 June 2012, 21:53 · #

Hi Simon,

I met a problem, could you give me some advice?

this problem just like below:
I just want to use this operator to search all objects(point & polygon) within 5 miles of a point, however this operator just return all points within 5 miles of this point, not any polygons,
or if I use this operator to search all objects(point & polygon) within 5 miles of a polygon, however this operator just return all polygons within 5 miles of this polygon, not any points…

SQL just like below:

select hl_neighbors.geometry.GET_GTYPE() from hz_locations hl_neighbors, hz_locations hl_center where hl_center.location_id = 36076(a point)/36156(a polygon) and sdo_within_distance(hl_neighbors.geometry, hl_center.geometry, ‘distance=50 unit=mile’) = ‘TRUE’

If location_id=36076, all results are 1, if location_id=36156, all results are 3, actually these points and polygons are gather within 5 miles.

The index in this column just like below:

Index_Name Index_Type Table_Owner Table_Name Status Ityp_Owner Ityp_Name SDO_LAYER_GTYPE
HZ_LOCATIONS_N15 DOMAIN AR HZ_LOCATIONS VALID MDSYS SPATIAL_INDEX DEFAULT

I didn’t add the layer_gtype parameters, you could see this value is default, why the result identify the gtype automatically? I just want to search all type of object use this operator…

Thanks & Regards,
James

— James · 25 December 2012, 19:04 · #