This discussion is archived
9 Replies Latest reply: Nov 21, 2013 10:47 PM by _jum RSS

To check for overlapping polygons in Oracle

NathanT Newbie
Currently Being Moderated

I have a table 1 which has a fields like store number and geometry and it just has one record in it.Now I want to check for this store number in table 2 and identify if there are any polygons overlapping to this particular store number polygon and insert them into the table 3.

 

 

Below is sql block I am trying to use.

 

 

    Insert into table 3

   

    select a.store_id,a.store_number,a.client_id,a.geometry

    from  table 2 a,table 1 b

    where a.client_id=b.client_id

    and SDO_RELATE(a.Geometry, (select Geometry from table 1  where store_id    

    = 34746),'mask=anyinteract') = 'TRUE';

 

 

 

 

But it just returns 1 field in the new table for any store id I select.

  • 1. Re: To check for overlapping polygons in Oracle
    _jum Journeyer
    Currently Being Moderated

    The code seems right to me, if only the tablenames would be like table_1 and table_2 (without blank).

    You check not only for ANYINTERACT, but for a.client_id=b.client_id too. What habens if You only SELECT:

     

    SELECT a.store_id,
          a.store_number,
          a.client_id,
          a.geometry
      FROM table_2 a, table_1 b
    WHERE    a.client_id = b.client_id
         -- AND sdo_relate (a.geometry, (SELECT geometry FROM table_1 WHERE store_id = 34746),  'mask=anyinteract') = 'TRUE';

    :

  • 2. Re: To check for overlapping polygons in Oracle
    NathanT Newbie
    Currently Being Moderated

    Well before this select statement is run I am having issues with the spatial index for the table.Here is the code for creating the spatial index.

     

    Delete  from user_sdo_geom_metadata where table_name = '<tablename>' and COLUMN_NAME = 'GEOMETRY';

     

     

    Insert into user_sdo_geom_metadata VALUES(<tablename>'', 'GEOMETRY',SDO_DIM_ARRAY(SDO_DIM_ELEMENT ('x', -180, 180, 0.05), SDO_DIM_ELEMENT ('y', -90, 90, 0.05)),8307);

     

     

    Commit;

     

     

    Create Index <tablename>'_GIDX on <tablename>'(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX parallel 25;

  • 3. Re: To check for overlapping polygons in Oracle
    Siva Ravada Expert
    Currently Being Moderated

    What error are you getting for create index ?

    And try it without the parallel clause to see if it works.

     

     

    siva

  • 4. Re: To check for overlapping polygons in Oracle
    NathanT Newbie
    Currently Being Moderated

    FOLLOWING IS THE ERROR:

     

    Insert into user_sdo_geom_metadata VALUES('TMCS.Temp_Nathan_Step1', 'GEOMETRY',SDO_DIM_ARRAY(SDO_DIM_ELEMENT ('x', -180, 180, 0.05), SDO_DIM_ELEMENT ('y', -90, 90, 0.05)),8307)

    Error at line 5

    ORA-13223: duplicate entry for TMCS.Temp_Nathan_Step1.GEOMETRY in SDO_GEOM_METADATA

    ORA-06512: at "MDSYS.MD", line 1723

    ORA-06512: at "MDSYS.MDERR", line 17

    ORA-06512: at "MDSYS.SDO_GEOM_TRIG_INS1", line 48

    ORA-04088: error during execution of trigger 'MDSYS.SDO_GEOM_TRIG_INS1'

  • 5. Re: To check for overlapping polygons in Oracle
    _jum Journeyer
    Currently Being Moderated

    This error comes because there is already an entry in the USER_SDO_GEOM_METADATA for table TMCS.Temp_Nathan_Step1

    In your second post you showed the solution:

    Delete  from user_sdo_geom_metadata where table_name = '<tablename>' and COLUMN_NAME = 'GEOMETRY';
    

    So before INSERT a second time in the metadata view, better DELETE a possible entry:

    DELETE FROM user_sdo_geom_metadata
    WHERE table_name = 'TMCS.TEMP_NATHAN_STEP1'
       AND column_name = 'GEOMETRY';
    

    Please note:

    1) the metadata view is user specific, so a schema name before the table name could cause problems

    2) the geometry TABLE_NAME and the geometry COLUMN_NAME in the metadata view are stored in capitals

  • 6. Re: To check for overlapping polygons in Oracle
    NathanT Newbie
    Currently Being Moderated

    If you check the code above I did delete the geometry from user_sdo_geom_metadata

     

    Delete  from user_sdo_geom_metadata where table_name = 'TMCS.Temp_Nathan_Step1' and COLUMN_NAME = 'GEOMETRY';
    Insert into user_sdo_geom_metadata VALUES('TMCS.Temp_Nathan_Step1', 'GEOMETRY',SDO_DIM_ARRAY(SDO_DIM_ELEMENT ('x', -180, 180, 0.05), SDO_DIM_ELEMENT ('y', -90, 90, 0.05)),8307);
    Commit;
    Create Index TMCS.Temp_Nathan_Step1_GIDX on TMCS.Temp_Nathan_Step1(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX parallel 25;
  • 7. Re: To check for overlapping polygons in Oracle
    Siva Ravada Expert
    Currently Being Moderated

    Several issues with the metadata entry.

    The table_name should be just the table_name without the schema prefix.

     

    And when you are deleting from the metadata, all the names should be upper case.

     

    So first delete the incorrect entry (use all upper case):

    1. Delete  from user_sdo_geom_metadata where table_name = 'TMCS.TEMP_NATHAN_STEP1' and COLUMN_NAME = 'GEOMETRY'

    Then insert using the right syntax:

     

    1. Insert into user_sdo_geom_metadata VALUES('TEMP_NATHAN_STEP1', 'GEOMETRY',SDO_DIM_ARRAY(SDO_DIM_ELEMENT ('x', -180, 180, 0.05), SDO_DIM_ELEMENT ('y', -90, 90, 0.05)),8307); 
  • 8. Re: To check for overlapping polygons in Oracle
    NathanT Newbie
    Currently Being Moderated

    Now I get this answer :

     

     

    Create Index TMCS.Temp_Nathan_Step1_GIDX on TMCS.Temp_Nathan_Step1(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX parallel 25

    Error at line 5

    ORA-00955: name is already used by an existing object

  • 9. Re: To check for overlapping polygons in Oracle
    _jum Journeyer
    Currently Being Moderated

    Simply DROP the INDEX before CREATE the new one:

    DROP INDEX TMCS.Temp_Nathan_Step1_GIDX;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points