7 Replies Latest reply on Feb 27, 2013 3:22 PM by 993183

# Polygon Crop

Hello

I have been trying to crop an area by other area.
I've been trying different approaches but without success.
Any help would be appreciated.

I have two columns with severall 2D geometries (MDSYS.SDO_GEOMETRY(2003,8307,....)) on two different tables.
Each of these columns of geometries represent two main areas, A and B.
A has 1500 polygons and B has 330 polygons.
I want to crop A and limit it to the area of B.
B overlaps to A, is smaller than A and is completely inside A.

Ex: It is like if I had A as France and B as Paris. Where France and Paris were two columns on different tables each one composed by severall geometries like the ones above described.

I have spatial metadata and index created for both.

MDSYS.SDO_GEOM.SDO_INTERSECTION() RESULT: Too heavy. Can't get nothing but null values for the intersection.
MDSYS.SDO_GEOM.RELATE() with 'mask=anyinteract' RESULT: Faster. Also can't get nothing but null values for the intersection.
SDO_RELATE with 'anyinteract' RESULT: Can't make it run. Returns errors regarding the index etc.. By the
description that I read this looks like the most promising solution.

Regards
• ###### 1. Re: Polygon Crop
As I understand, you have to build the INTERSECTION of two AGGREGATS A/B with 1500/300 POLYGONS.
If this is correct, here is a very simple version/solution of this problem, on the basis of the brand new article from Simon Greener (thanks a lot!) at Intersecting two aggregated polygon layers with SC40
``````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, 12,12)) AS geom FROM dual
)
,
polys900 AS (
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 UNION ALL
SELECT mdsys.sdo_geometry(2003,82469,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array( 6, 0, 8,2)) AS geom FROM dual
)
SELECT SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(polys1500.geom, 0.005)) p1500un,
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE( polys900.geom, 0.005))  p900un,
SDO_GEOM.SDO_INTERSECTION( SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(polys1500.geom, 0.005)), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE( polys900.geom, 0.005)), 0.005) intr
FROM polys1500, polys900;``````
May be you can adapt the code.
• ###### 2. Re: Polygon Crop
The problem with this approach is that i have the two columns of 2DPolygon/Surface ("2003") one with 1500 and other with 300.
When doing the union i loose information about each one and I cannot loose that info.

I don't think that having two multipolygons composed by 1500 and 300 polygons with be much easier to process that two columns with 1500 and 300 entries each one of them as a polygon. The number of iterations would be the same.

Example:
Imagine that I have France as a column with 1500 entries each one a 2DPolygon/Surface on some table and Paris on another table with 300 entries.
I want the area where they overlap or, by other words, I want to limit the polygons of France to the area of Paris. The polygons that are completely inside Paris remain and the ones that only intersect are cropped to the area of Paris.
I do this because the way I divided france (the polygons that compose France column) is important and I need that info.
• ###### 3. Re: Polygon Crop
So you could do something like:
1. UNION the 300 polygons to a new one -> "Paris"
2. find the polygons from France that are completly in "Paris" (SDO_RELATE/SDO_INTERACT/SDO_CONTAINS/...)
->they remain unchanged
3. find the polygons from France that intersect"Paris" (SDO_RELATE/SDO_INTERACT/SDO_OVERLAPS/...)
4. INTERSECT each polygon from 3.) with "Paris"
Is this correct ?
• ###### 4. Re: Polygon Crop
I think I am having problems because the type of geometry I have for Paris is like this MDSYS.SDO_GEOMETRY(2007,8307,MDSYS.SDO_POINT_TYPE(...),MDSYS.SDO_ELEM_INFO_ARRAY(...)..) whereas 2007 i want 2003 and instead of MDSYS.SDO_POINT_TYPE() i should have NULL.
• ###### 5. Re: Polygon Crop
You have to use VALID geometries. Iit is no problem to set all SDO_GTYPE=2003 and SDO_POINT=NULL if the rest is correct. From where do these strange geometries come ?
• ###### 6. Re: Polygon Crop
Andre,

(Folks the chap's name is Andre.)

For me this is a form of cross-post - http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/306/intersecting-two-aggregated-polygon-layers-with-sc4o

Andrew, thanks for replying to my email to you about the same problem. I appreciated the feedback about my response to your question. Not!

Here are examples of the sort of information that I could have done with in your original private communication.
When doing the union i loose information about each one and I cannot loose that info.
I want to crop A and limit it to the area of B.
B overlaps to A, is smaller than A and is completely inside A.
Ex: It is like if I had A as France and B as Paris. Where France and Paris were two columns on different tables each one composed by severall geometries like the ones above described.

The solution you need should look somewhat like this:
``````With wpolys900 as (
SELECT SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE( polys900.geom, 0.005)) as uGeom
FROM polys900;
)
SELECT SDO_GEOM.SDO_INTERSECTION(a.geom,b.uGeom, 0.005) as rGeom
FROM polys1500 a, wpolys900 b
WHERE SDO_ANYINTERACT(a.geom,b.uGeom) = 'TRUE' /* Limits processing to area defined by table900 */;``````
Do not forget that this sort of polygon processing is most likely going to return polygon/multipolygon and compound 2004 geometries (point/line/polygon). Where a result is a 2004 SDO_GEOMETRY you will have to enact extract processing to extract the polygon elements from them. There are methods for this that have been dealt with on this forum before. I also deal with it on my website via a blog article that you may care to look for. Enact suitable searching on this before creating a new forum post that is separate to this one if you can't work out how to do it.
I think I am having problems because the type of geometry I have for Paris is like this MDSYS.SDO_GEOMETRY(2007,8307,MDSYS.SDO_POINT_TYPE(...),....
Why should these cause any processing issues? I cannot see why they should as Oracle can intersect a multipolygon by a polygon as easily as poly/poly.

If my tone is a bit sharp it is because we all spend a lot of time trying to help those who ask us for help. You need to provide better information with examples when emailing anyone or posting to the forum in order to minimise your impact on others and to get the fastest answer to your problem.

It is disrespectful to approach someone (eg myself via email) and not bother to answer them when they spend time trying to help. You are probably busy and frustrated, but please try and be more curteous to those whom you approach.

regards
Simon
• ###### 7. Re: Polygon Crop
_Jum

Thanks for your help, it seems like I had validated the geometry but i missed one important value.
As soon as I have new developments that need attention I will get back.

Simon I wrote you an email..