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

oracle spatial any interact function

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

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

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.

1 person found this helpful
• 3. Re: oracle spatial any interact function

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

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