9 Replies Latest reply: Nov 22, 2013 12:47 AM by _jum RSS

    To check for overlapping polygons in Oracle

    NathanT

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          Simply DROP the INDEX before CREATE the new one:

                          DROP INDEX TMCS.Temp_Nathan_Step1_GIDX;