4 Replies Latest reply: Aug 9, 2013 4:42 PM by AswinNaidu RSS

    oracle spatial any interact function

    AswinNaidu

      Hi all

       

      I have a question on oracle spatial. I have a line (a trajectory) and a polygon of type sdo_geometry, and i used "any interact" function to find if the line intersects the polygon. Now i need to find at which points the trajectory intersects the polygon. For example, the trajectory has 100 points and it enters the polygon at 20th and leaves at 70th point. Is there any way to find out these points?

       

      Thanks

      Aswin

        • 1. Re: oracle spatial any interact function
          Stefan Jager

          Hi Aswin,

           

          use SDO_GEOM.SDO_INTERSECTION. That will give you a line, and if your area is a simple area the line will have two points: the intersection points of the original line with the boundary of the polygon. Of course, if your polygons have holes the result will have more than two points.

           

          HTH,

          Stefan

          • 2. Re: oracle spatial any interact function
            Barbara Boehmer

            Please see the example below.  I just threw this together, so there may be a simpler way to extract the x and y coordinates if that is what you want.

             

            SCOTT@orcl12c_11gR2> create table polygon_tab

              2    (p_id         number,

              3      polygon_col  sdo_geometry)

              4  /

             

            Table created.

             

            SCOTT@orcl12c_11gR2> insert into polygon_tab (p_id, polygon_col)

              2  values

              3    (1,

              4      SDO_GEOMETRY

              5        (2003,

              6         NULL,

              7         NULL,

              8         SDO_ELEM_INFO_ARRAY (1, 1003, 3),

              9         SDO_ORDINATE_ARRAY (0,0, 4,4)))

            10  /

             

            1 row created.

             

            SCOTT@orcl12c_11gR2> create table line_tab

              2    (l_id       number,

              3      line_col   sdo_geometry)

              4  /

             

            Table created.

             

            SCOTT@orcl12c_11gR2> insert into line_tab (l_id, line_col)

              2  values

              3    (10,

              4      sdo_geometry

              5       (2002,

              6        null,

              7        null,

              8        sdo_elem_info_array (1,2,1),

              9        sdo_ordinate_array (2,5, 5,2)))

            10  /

             

            1 row created.

             

            SCOTT@orcl12c_11gR2> insert into line_tab (l_id, line_col)

              2  values

              3    (20,

              4      sdo_geometry

              5       (2002,

              6        null,

              7        null,

              8        sdo_elem_info_array (1,2,1),

              9        sdo_ordinate_array (3,5, 5,3)))

            10  /

             

            1 row created.

             

            SCOTT@orcl12c_11gR2> insert into line_tab (l_id, line_col)

              2  values

              3    (30,

              4      sdo_geometry

              5       (2002,

              6        null,

              7        null,

              8        sdo_elem_info_array (1,2,1),

              9        sdo_ordinate_array (10,25, 20,30, 25,25, 30,30)))

            10  /

             

            1 row created.

             

            SCOTT@orcl12c_11gR2> column relation format a18

            SCOTT@orcl12c_11gR2> column points   format a30

            SCOTT@orcl12c_11gR2> select p.p_id polygon,

              2          l.l_id line,

              3          sdo_geom.relate

              4            (p.polygon_col,

              5             'determine',

              6             l.line_col,

              7             0.005) relation,

              8          sdo_geom.sdo_intersection

              9            (p.polygon_col,

            10             l.line_col,

            11             0.005).sdo_ordinates points

            12  from   polygon_tab p,

            13          line_tab l

            14  order  by polygon, line

            15  /

             

               POLYGON       LINE RELATION           POINTS

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

                     1         10 OVERLAPBDYDISJOINT SDO_ORDINATE_ARRAY(3, 4, 4, 3)

                     1         20 TOUCH              SDO_ORDINATE_ARRAY(4, 4)

                     1         30 DISJOINT

             

            3 rows selected.

             

            SCOTT@orcl12c_11gR2> select polygon,

              2          line,

              3          relation,

              4          sum (decode (mod (rownum, 2), 1, t2.column_value )) x,

              5          sum (decode (mod (rownum, 2), 0, t2.column_value )) y

              6  from   (select p.p_id polygon,

              7              l.l_id line,

              8              sdo_geom.relate

              9                (p.polygon_col,

            10                 'determine',

            11                 l.line_col,

            12                 0.005) relation,

            13              sdo_geom.sdo_intersection

            14                (p.polygon_col,

            15                 l.line_col,

            16                 0.005).sdo_ordinates points

            17           from   polygon_tab p,

            18              line_tab l) t,

            19           table (t.points) t2

            20  group  by polygon, line, relation, ceil (rownum / 2)

            21  order  by polygon, line

            22  /

             

               POLYGON       LINE RELATION                    X          Y

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

                     1         10 OVERLAPBDYDISJOINT          3          4

                     1         10 OVERLAPBDYDISJOINT          4          3

                     1         20 TOUCH                       4          4

             

            3 rows selected.

            • 3. Re: oracle spatial any interact function
              AswinNaidu

              Hi Barbara

               

              Thanks for taking some time to put these examples together. I appreciate your help a lot. Actually your answer is also correct and more extensive.

               

              Thanks

              Aswin

              • 4. Re: oracle spatial any interact function
                AswinNaidu

                Hi

                 

                Both the answers above are working fine. These queries are giving me the segment of the line which is inside the polygon, but my line is a four dimensional geometry (lon, lat, altitude, time). These queries are interpolating the intersection points of the polygon & line, which is perfect, but only for lon & lat. The time is not interpolated & the altitude in always 0. Does anyone know why or how to do it?

                 

                For a better understanding of the problem i am pasting a sample code below:

                 

                create table polygon_tab (p_id number, p_shape  sdo_geometry);

                 

                insert into polygon_tab(p_id, p_shape) values (1,sdo_geometry(2003,8307,null, mdsys.sdo_elem_info_array(1,1003,1), mdsys.sdo_ordinate_array(-50, 60,  -50,20,-10, 20, -10, 60, -50, 60)));

                 

                create table line_tab   (l_id number,l_shape sdo_geometry);

                 

                insert into line_tab(l_id, l_shape)   values (1, sdo_geometry(4402, 8307, null,sdo_elem_info_array(1,2,1), sdo_ordinate_array(-77,38,0,700, -65,43, 200, 750, -51, 45, 370, 800, -48, 48, 380, 900,  -12, 52, 420, 950, -9,53, 370, 1000, -5,53, 330, 1050, 0,51,0,1100)));

                 

                --first query

                select sdo_geom.sdo_intersection(p.p_shape, l.l_shape, 0.005)   from polygon_tab p, line_tab l;

                 

                --second query

                select p.p_id polygon, l.l_id line, sdo_geom.relate (p.p_shape,'determine', l.l_shape, 0.005) relation,sdo_geom.sdo_intersection(p.p_shape,l.l_shape, 0.005).sdo_ordinates points from   polygon_tab p, line_tab l   order  by polygon, line

                 

                Also just a brief background.

                This line is a trajectory of a flight (lon, lat, altitude and time). I want to define a geographic area (box)and be able to tell if the flight has entered that box and if so, at what points, altitude and time it entered and also exited the box.

                 

                 

                Thank you in advance

                Aswin


                Thanks

                Aswin