This discussion is archived
4 Replies Latest reply: Aug 9, 2013 2:42 PM by AswinNaidu RSS

oracle spatial any interact function

AswinNaidu Newbie
Currently Being Moderated

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 Journeyer
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

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