I am trying to use cursors so that I can do it for all records at once.In the below code I select a list couple of polygons which I have restricted using rownum. Now for each polygon the sdo_relate figures out the adjacent polygons. What I want to do next is for the primary polygon which is overlapped by other polygons, clip of those overlap parts from that primary polygon and save that in a new table.Would appreciate if someone could point on where I am wrong.Thank you.
Declare Cursor c1 is select store_id ,client_id,geometry from TMCS.TMCS_All_Stores_TA where CLIENT_ID= 1 and rownum <4; Type C1_TAB_TYPE is table of c1%ROWTYPE; c1_list c1_TAB_TYPE; -- DBMS_OUTPUT.PUT_LINE('2012 Population--> '||c1_list(1).store_id); Begin For r1 in c1 Loop DBMS_OUTPUT.PUT_LINE('The primary --> '||r1.store_id); select store_id ,client_id,geometry BULK COLLECT INTO c1_list from ( select store_id, store_number ,client_id,geometry from TMCS.TMCS_ALL_STORES_TA where client_id=1 and SDO_RELATE(Geometry, r1.geometry,'mask=anyinteract') = 'TRUE' and store_id != r1.store_id ); For i in c1_list.First..c1_list.Last loop DBMS_OUTPUT.PUT_LINE('The Stores are --> '||c1_list(i).store_id); end loop; For i in c1_list.First..c1_list.Last loop Insert into TMCS.Temp_store_newTA select store_id ,store_number,client_id,SDO_geom.sdo_difference(geometry,c1_list(i).geometry ,0.005) from TMCS.TMCS_ALL_STORES_TA a where a.client_id= c1_list(i).client_id; End Loop; End Loop; End;
I don't full understand your requirements. It would help if you could give a bit more background information about what you want to achieve. Assuming you want one row in temp_store_newta for each store, then the following should do it (untested).
declare l_union sdo_geometry; l_remainder sdo_geometry; begin for r1 in ( select rowid, store_id, client_id, geometry from tmcs.tmcs_all_stores_ta where client_id = 1 and rownum < 4) loop dbms_output.put_line('The primary --> ' || r1.store_id); select sdo_aggr_union(sdoaggrtype(geometry, 0.005)) into l_union from tmcs.tmcs_all_stores_ta where client_id = 1 and sdo_anyinteract(geometry, r1.geometry) = 'TRUE' and rowid != r1.rowid; l_remainder := sdo_geom.sdo_difference(r1.geometry, l_union, 0.005); if l_remainder is not null then dbms_output.put_line('Inserting the remainder for ' || r1.store_id); insert into tmcs.temp_store_newta values (r1.store_id, r1.store_number, r1.client_id, l_remainder); else dbms_output.put_line('Nothing left after punching out all the other geometries'); end if; end loop r1; commit; end; /
So I select a list of store_ids from one table with client_id =1 . then I want to identify which all polygons overlap with each other and which I do using sdo_relate. Now I want to remove the overlaps and hence I use the sdo_difference. Finally what I want is no polygons overlap with each other at all.Hope I was clear in explaining what I am trying to do.
It works. But not in the format I wanted I suppose. This is how I want the output to look like.https://drive.google.com/file/d/0B2lN2UTtzOi8SG8zbGYzWkQzckk/edit?usp=sharing
I just want the shaded part.