7 Replies Latest reply: Aug 3, 2012 2:17 PM by cosmopolitan RSS

    Compound polygon with mutiple lines and circular arcs.

    cosmopolitan
      I am looking to create a compound polygon with multiple lines and arcs using the geodetic co-ordinate system...in an effort to resemble an irregular shape like a US state for example. I did look at the compound polygon shown in the link below which shows a compound polygon that resembles an ice cream cone but i need to have something complex.

      http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_objrelschema.htm#i1005726

      In the example below, i don't understand exactly what SDO_ELEM_INFO = (1,1005,2, 1,2,1, 5,2,2)... means how is it calculated?

      Also this example just had four co-ordinates, SDO_ORDINATE_ARRAY(6,10, 10,1, 14,10, 10,14, 6,10)

      what if i have more lines and circular arcs...can anyone help give a complex example of inserting a compound polygon with multiple lines and arcs and help understand what each and every attribute represents in the example?

      Thanks in advance
      Ajay
        • 1. Re: Compound polygon with mutiple lines and circular arcs.
          _jum
          Here a little example - ice cream cone ;-)
          Hope this helps to understand the design of cplx. polygon
          --  cplx. Polygon ice cream cone with square hole
          --    arc         <5,0> <0,5> <-5,0> 
          --    line       (<-5,0>) <0,-5> <5,0> 
          --    square hole <-1,-1> <-1,1> <1,1> <1,-1>
          --    A=Pi*r²/2 (half circle) + 10*5/2    (triangle) - 2*2 (square) = 60.27
          --    l=Pi*r    (half circle) + 2*SQRT(50)(triangle) + 4*2 (square) = 37.85
          
          WITH 
            l1 AS (SELECT SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,2, 5,2,1, 11,2003,1 ),SDO_ORDINATE_ARRAY(5,0, 0,5, -5,0, 0,-5, 5,0, -1,-1, -1,1, 1,1, 1,-1, -1,-1)) geom FROM dual)
          SELECT
            SDO_GEOM.SDO_LENGTH(geom,0.001) len,
            SDO_GEOM.SDO_AREA(geom,0.001)  area,
            SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom,0.001) valid
            FROM l1;
          
          len            area          valid
          --------------------------------------------
          37,85009889168 60,26990817  TRUE
          • 2. Re: Compound polygon with mutiple lines and circular arcs.
            Paul Dziemiela
            Hi Ajay the Cosmopolitan,

            Jum's example seems great to me. You can also find examples in the 11g Oracle Spatial book around page 79. Note that you can get the 10g version of the book for less than $3 on Amazon and I've seen it there for under a dollar sometimes.

            My comment is if you plan to utilize compound features in real geodetic space, well then you cannot spatially index the data. You'll need to move into projected space. If you create a geodetic feature - say Jum's icecream cone - and try to index it, you will get
            ORA-13035: Invalid data (arcs in geodetic data) in the SDO_GEOMETRY object
            Circular arcs are not supported in geodetic coordinate systems and I don't really get the feeling they ever will be (anyone know otherwise?).

            Cheers,
            Paul
            CREATE TABLE foo AS
            SELECT 
            1 AS objectid,
            SDO_GEOMETRY(2003,4269,NULL,SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,2,5,2,1,11,2003,1),SDO_ORDINATE_ARRAY(5,0,0,5,-5,0,0,-5,5,0,-1,-1,-1,1,1,1,1,-1,-1,-1)) AS shape 
            FROM dual;
            
            INSERT INTO user_sdo_geom_metadata
            VALUES(
            'FOO','SHAPE',
            MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,.05),MDSYS.SDO_DIM_ELEMENT('Y',-90,90,.05)),
            4269);
            COMMIT;
            
            CREATE INDEX foo_spx ON foo(shape) 
            INDEXTYPE IS MDSYS.SPATIAL_INDEX;
            • 3. Re: Compound polygon with mutiple lines and circular arcs.
              cosmopolitan
              OK. is there a way i can use circular arcs. basically what i have right now is an ice cream cone with all the geodetic co-ordinates. How do i convert this into the Projected Co-ordinate System.

              For ex i tried the following query to insert into the Db
              INSERT INTO LOCATION (RECORD_ID,LOC_GEO)
              VALUES(10376677,
              SDO_GEOMETRY(
              2003,
              8307,
              NULL,
              SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2),
              SDO_ORDINATE_ARRAY(39.48893,-84.20268,39.35855,-84.12802,39.48869,-84.05266, 39.54672,-84.12752,39.48893,-84.20268)
              ));

              But it failed and gave the same error as you said "ORA-13035: Invalid data (arcs in geodetic data) in the SDO_GEOMETRY object"

              How can i fix this and insert this ice cream cone into projected co-ordinates system, so it can be spatially indexes as well.?
              • 4. Re: Compound polygon with mutiple lines and circular arcs.
                cosmopolitan
                OK. is there a way i can use circular arcs. basically what i have right now is an ice cream cone with all the geodetic co-ordinates. How do i convert this into the Projected Co-ordinate System.

                For ex i tried the following query to insert into the Db
                INSERT INTO LOCATION (RECORD_ID,LOC_GEO)
                VALUES(10376677,
                SDO_GEOMETRY(
                2003,
                8307,
                NULL,
                SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2),
                SDO_ORDINATE_ARRAY(39.48893,-84.20268,39.35855,-84.12802,39.48869,-84.05266, 39.54672,-84.12752,39.48893,-84.20268)
                ));

                But it failed and gave the same error as you said "ORA-13035: Invalid data (arcs in geodetic data) in the SDO_GEOMETRY object"

                How can i fix this and insert this ice cream cone into projected co-ordinates system, so it can be spatially indexes as well.?
                • 5. Re: Compound polygon with mutiple lines and circular arcs.
                  cosmopolitan
                  OK. is there a way i can use circular arcs. basically what i have right now is an ice cream cone with all the geodetic co-ordinates. How do i convert this into the Projected Co-ordinate System.

                  For ex i tried the following query to insert into the Db
                  INSERT INTO LOCATION (RECORD_ID,LOC_GEO)
                  VALUES(10376677,
                  SDO_GEOMETRY(
                  2003,
                  8307,
                  NULL,
                  SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2),
                  SDO_ORDINATE_ARRAY(39.48893,-84.20268,39.35855,-84.12802,39.48869,-84.05266, 39.54672,-84.12752,39.48893,-84.20268)
                  ));

                  But it failed and gave the same error as you said "ORA-13035: Invalid data (arcs in geodetic data) in the SDO_GEOMETRY object"

                  How can i fix this and insert this ice cream cone into projected co-ordinates system, so it can be spatially indexes as well.?
                  • 6. Re: Compound polygon with mutiple lines and circular arcs.
                    Paul Dziemiela
                    Hi Ajay,

                    I don't think you can which is why I mentioned it in the first place. Once you have your coordinates in a geodetic projection, you are kind of stuck. One one hand there is nothing wrong with the coordinates, but on the other hand you cannot index them.
                    I manage a lot of data in NAD83 and for me finding ANY arcs or compound geometry is anathema. However you seem to want to have the arcs so I am not sure this is possible. Note that you cannot straightforwardly transform the arc geodetic geometry to a projected cs as that produces ORA-13285: Geometry coordinate transformation error. You also cannot densify arc geodetic geometry with SDO_GEOM.SDO_ARC_DENSIFY, you just get back the input geometry. So I don't know offhand what to recommend. Perhaps someone else has an idea?

                    I'd be interested to know why you want to do this. What's the interest in arcs? Are you just looking to save on coordinates? How are you building these polygons?

                    Cheers,

                    Paul
                    • 7. Re: Compound polygon with mutiple lines and circular arcs.
                      cosmopolitan
                      Paul,
                      Thanks for the reply ..basically i wanted a complex shape like a US state. where there are arcs and lines all across for a more detailed and accurate geometry which is why i was looking at compound polygons. if i have to use lines instead it would be an approximation and not as accurate as circular arcs. is there a way to convert the geodetic co-ordinates to the projected co-ordinate system?
                      Ajay