5 Replies Latest reply: Jan 7, 2014 8:42 PM by Barbara Boehmer RSS

    Calculate Area proportional

    NathanT

      I am trying to calculate a proportional area for the intersecting polygon.

       

      SELECT a.bg_id, SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(a.geometry,b.geometry, 0.005), 0.005, 'unit=sq_mile') "overlap area" ,SDO_GEOM.SDO_AREA((a.geometry), 0.005, 'unit=sq_mile')"Total area"
      FROM table 1 a, table 2 b
      where b.STORE_number= '351029'
      AND SDO_Relate(a.geometry,b.geometry,'mask=anyinteract') = 'TRUE';
      

      I am trying to use cursor and then divide the two geometry to calculate the proportional area. Any suggestions on how I could be doing this.

        • 1. Re: Calculate Area proportional
          Barbara Boehmer

          SCOTT@orcl12c> CREATE TABLE table1

            2    (bg_id          VARCHAR2(8),

            3      geometry      MDSYS.SDO_GEOMETRY)

            4  /

           

          Table created.

           

          SCOTT@orcl12c> INSERT INTO table1 VALUES

            2    ('74073',

            3      SDO_GEOMETRY

            4        (2003, 2000, NULL,

            5         SDO_ELEM_INFO_ARRAY(1,1003,3),

            6         SDO_ORDINATE_ARRAY(4,6, 8,8)))

            7  /

           

          1 row created.

           

          SCOTT@orcl12c> CREATE TABLE table2

            2    (store_number  VARCHAR2(12),

            3      geometry      MDSYS.SDO_GEOMETRY)

            4  /

           

          Table created.

           

          SCOTT@orcl12c> INSERT INTO table2 VALUES

            2    ('351029',

            3      SDO_GEOMETRY

            4        (2003, 2000, NULL,

            5         SDO_ELEM_INFO_ARRAY(1,1003,3),

            6         SDO_ORDINATE_ARRAY(5,3, 7,7)))

            7  /

           

          1 row created.

           

          SCOTT@orcl12c> INSERT INTO user_sdo_geom_metadata

            2    (table_name, column_name, diminfo, srid)

            3  VALUES

            4    ('table1', 'geometry',

            5      SDO_DIM_ARRAY

            6        (SDO_DIM_ELEMENT ('X', 0, 20, 0.005),

            7         SDO_DIM_ELEMENT ('Y', 0, 20, 0.005)),

            8      2000)

            9  /

           

          1 row created.

           

          SCOTT@orcl12c> CREATE INDEX table1_idx ON table1 (geometry)

            2  INDEXTYPE IS MDSYS.SPATIAL_INDEX

            3  /

           

          Index created.

           

          SCOTT@orcl12c> SELECT a.bg_id,

            2          ROUND

            3            (SDO_GEOM.SDO_AREA

            4           (SDO_GEOM.SDO_INTERSECTION (a.geometry,b.geometry, 0.005),

            5            0.005, 'unit=sq_mile') /

            6             SDO_GEOM.SDO_AREA

            7           (a.geometry,

            8            0.005, 'unit=sq_mile')

            9             * 100, 2) || '%' "Proportional area"

          10  FROM   table1 a, table2 b

          11  where  b.STORE_number = '351029'

          12  AND    SDO_Relate (a.geometry, b.geometry, 'mask=anyinteract') = 'TRUE'

          13  /

           

          BG_ID    Proportional area

          -------- -----------------------------------------

          74073    25%

           

          1 row selected.

          • 2. Re: Calculate Area proportional
            NathanT

            The above solution works well for one store at a time.I have two tables both of them having polygon geometry. Lets consider Table 1 has bigger polygons which overlap over the smaller polygons in table 2.Now I know how to find how many polygons from table 2 come under a single polygon from table 1. What I want to do is to find the proportional area for those polygons from table 2 which get intersected with the polygon from table 1.When I say proportional area I mean how much

            (overlap polyon area/total area of that intersected polygon from table 2)*100.

            I want find this because it would help me in calculating another value with the right proportions.

            Below is the one which I tried using the cursor which doesnt work.

             

            
            
                 Declare
                 Cursor c1 is 
                 select store_number,geometry from stores  where CLIENT_ID= 1 and ORG_ID = 1 ;
                       
                 Type C1_TAB_TYPE is table of c1%ROWTYPE;      
                 c1_list c1_TAB_TYPE;
                   
                 Begin     
                           For r1 in c1 
                           
                              Loop
                              Select bg_id 
                               BULK COLLECT INTO c1_list from
                              (
                              SELECT bg_id, (SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(geometry,r1.geometry, 0.005), 0.005, 'unit=sq_mile') /SDO_GEOM.SDO_AREA((geometry), 0.005, 'unit=sq_mile'))"Proportional Area"
                 FROM DATA
                 where r1.store_number != r1.store_number
                 AND SDO_Relate(geometry,r1.geometry,'mask=anyinteract') = 'TRUE');
                 End loop;
                 End;
            
            • 3. Re: Calculate Area proportional
              Barbara Boehmer

              It would probably help to see some sample data and what results you want based on that data.  It would also be helpful if you provided that in the form of create table and insert statements and indicated how the tables are joined.  I have provided a couple of sample queries below.  The first query aggregates all of the data in table2 for comparison.  The second query groups the data in table2 by a column that could be used for joining or filtering.

               

               

              SCOTT@orcl12c> CREATE TABLE table1

                2    (bg_id          VARCHAR2(8),

                3      geometry      MDSYS.SDO_GEOMETRY)

                4  /

               

              Table created.

               

              SCOTT@orcl12c> INSERT INTO table1 VALUES

                2    ('74073',

                3      SDO_GEOMETRY

                4        (2003, 2000, NULL,

                5         SDO_ELEM_INFO_ARRAY(1,1003,3),

                6         SDO_ORDINATE_ARRAY(4,6, 8,8)))

                7  /

               

              1 row created.

               

              SCOTT@orcl12c> CREATE TABLE table2

                2    (store_number  VARCHAR2(12),

                3      geometry      MDSYS.SDO_GEOMETRY)

                4  /

               

              Table created.

               

              SCOTT@orcl12c> INSERT INTO table2 VALUES

                2    ('351029',

                3      SDO_GEOMETRY

                4        (2003, 2000, NULL,

                5         SDO_ELEM_INFO_ARRAY(1,1003,3),

                6         SDO_ORDINATE_ARRAY(5,3, 7,7)))

                7  /

               

              1 row created.

               

              SCOTT@orcl12c> INSERT INTO table2 VALUES

                2    ('351029',

                3      SDO_GEOMETRY

                4        (2003, 2000, NULL,

                5         SDO_ELEM_INFO_ARRAY(1,1003,3),

                6         SDO_ORDINATE_ARRAY(6,3, 8,7)))

                7  /

               

              1 row created.

               

              SCOTT@orcl12c> INSERT INTO user_sdo_geom_metadata

                2    (table_name, column_name, diminfo, srid)

                3  VALUES

                4    ('table1', 'geometry',

                5      SDO_DIM_ARRAY

                6        (SDO_DIM_ELEMENT ('X', 0, 20, 0.005),

                7         SDO_DIM_ELEMENT ('Y', 0, 20, 0.005)),

                8      2000)

                9  /

               

              1 row created.

               

              SCOTT@orcl12c> CREATE INDEX table1_idx ON table1 (geometry)

                2  INDEXTYPE IS MDSYS.SPATIAL_INDEX

                3  /

               

              Index created.

               

              SCOTT@orcl12c> SELECT a.bg_id,

                2          ROUND

                3            (SDO_GEOM.SDO_AREA

                4           (SDO_GEOM.SDO_INTERSECTION (a.geometry,b.geometry, 0.005),

                5            0.005, 'unit=sq_mile') /

                6             SDO_GEOM.SDO_AREA

                7           (a.geometry,

                8            0.005, 'unit=sq_mile')

                9             * 100, 2) || '%' "Proportional area"

              10  FROM   table1 a,

              11          (SELECT SDO_AGGR_UNION (SDOAGGRTYPE (geometry, 0.005)) geometry

              12           FROM   table2) b

              13  WHERE  SDO_Relate (a.geometry, b.geometry, 'mask=anyinteract') = 'TRUE'

              14  /

               

              BG_ID    Proportional area

              -------- -----------------------------------------

              74073    37.5%

               

              1 row selected.

               

              SCOTT@orcl12c> SELECT a.bg_id,

                2          ROUND

                3            (SDO_GEOM.SDO_AREA

                4           (SDO_GEOM.SDO_INTERSECTION (a.geometry,b.geometry, 0.005),

                5            0.005, 'unit=sq_mile') /

                6             SDO_GEOM.SDO_AREA

                7           (a.geometry,

                8            0.005, 'unit=sq_mile')

                9             * 100, 2) || '%' "Proportional area"

              10  FROM   table1 a,

              11          (SELECT store_number,

              12              SDO_AGGR_UNION (SDOAGGRTYPE (geometry, 0.005)) geometry

              13           FROM   table2

              14           GROUP  BY store_number) b

              15  WHERE  b.STORE_number = '351029'

              16  AND    SDO_Relate (a.geometry, b.geometry, 'mask=anyinteract') = 'TRUE'

              17  /

               

              BG_ID    Proportional area

              -------- -----------------------------------------

              74073    37.5%

               

              1 row selected.

              • 4. Re: Calculate Area proportional
                NathanT

                In the query below table b is the one which has all the stores in polygon format which interacts with polygons from table a for which i wanted to find out the proportional area. both of them have the geometry field and are spatially indexed too. Now the one here does it for one store at a time. All I want to do is to run to with cursor for the store number.

                 

                SELECT a.bg_id, (SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(a.geometry,b.geometry, 0.005), 0.005, 'unit=sq_mile') /SDO_GEOM.SDO_AREA((a.geometry), 0.005, 'unit=sq_mile'))"Proportional Area"

                FROM table a, table b

                where b.STORE_number= '351029'

                AND SDO_Relate(a.geometry,b.geometry,'mask=anyinteract') = 'TRUE';   

                • 5. Re: Calculate Area proportional
                  Barbara Boehmer

                  If you want to loop through a cursor of store_number values and pass them to the query above, then you can do that.  However, in general, if you can do something in SQL alone, instead of using PL/SQL, it is more efficient to use SQL alone.  If you want examples, then you need to provide create table and insert statements for sample data and desired results based on that data, as previously requested.