By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table poly (
id int
, poly SDO_GEOMETRY
)
create table point (
id int
, x number
, y number
)
insert into poly
/*Growing squares centered in (0,0)*/
select
level
, sdo_geometry(
2003
, null, null
, SDO_ELEM_INFO_ARRAY(1, 1003, 3)
, SDO_ORDINATE_ARRAY(-level, -level, level, level)
)
from dual
connect by level < 6
5 rows affected
insert into point
/*List of points (0, y)*/
select
level
, 0
, level
from dual
connect by level < 8
7 rows affected
select
p.*
, pl.id as contained_in
, pl.poly.get_wkt() as contained_in_wkt
from point p
left join poly pl
/*You may construct point on-the-fly from its coordinates*/
on SDO_CONTAINS(pl.poly, sdo_geometry(
2001
, null
, sdo_point_type(p.x, p.y, null)
, null
, null
)) = 'TRUE'
where 1 = 1
order by
p.id
, pl.id
| ID | X | Y | CONTAINED_IN | CONTAINED_IN_WKT |
|---|---|---|---|---|
| 1 | 0 | 1 | 2 | POLYGON ((-2.0 -2.0, 2.0 -2.0, 2.0 2.0, -2.0 2.0, -2.0 -2.0)) |
| 1 | 0 | 1 | 3 | POLYGON ((-3.0 -3.0, 3.0 -3.0, 3.0 3.0, -3.0 3.0, -3.0 -3.0)) |
| 1 | 0 | 1 | 4 | POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0)) |
| 1 | 0 | 1 | 5 | POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0)) |
| 2 | 0 | 2 | 3 | POLYGON ((-3.0 -3.0, 3.0 -3.0, 3.0 3.0, -3.0 3.0, -3.0 -3.0)) |
| 2 | 0 | 2 | 4 | POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0)) |
| 2 | 0 | 2 | 5 | POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0)) |
| 3 | 0 | 3 | 4 | POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0)) |
| 3 | 0 | 3 | 5 | POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0)) |
| 4 | 0 | 4 | 5 | POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0)) |
| 5 | 0 | 5 | null | null |
| 6 | 0 | 6 | null | null |
| 7 | 0 | 7 | null | null |
/*Object type to access instances as a single field*/
create type ts_sdo as object (
shape sdo_geometry
)
/*Table of SDO_GEOMETRY*/
create type tt_sdo as varray (1048576) of ts_sdo
select
p.shape.get_wkt() as point_wkt
, pl.id as contained_in
, pl.poly.get_wkt() as contained_in_wkt
from table(
/*Then we construct collection from list of points*/
tt_sdo(
/*Each member is constructed from SDO_GEOMETRY*/
ts_sdo(sdo_geometry('POINT(0 1)'))
, ts_sdo(sdo_geometry(
2001
, null
, sdo_point_type(0, 10, 0)
, null
, null
))
)) p
left join poly pl
on SDO_CONTAINS(pl.poly, p.shape) = 'TRUE'
where 1 = 1
order by
pl.id
| POINT_WKT | CONTAINED_IN | CONTAINED_IN_WKT |
|---|---|---|
| POINT (0.0 1.0) | 2 | POLYGON ((-2.0 -2.0, 2.0 -2.0, 2.0 2.0, -2.0 2.0, -2.0 -2.0)) |
| POINT (0.0 1.0) | 3 | POLYGON ((-3.0 -3.0, 3.0 -3.0, 3.0 3.0, -3.0 3.0, -3.0 -3.0)) |
| POINT (0.0 1.0) | 4 | POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0)) |
| POINT (0.0 1.0) | 5 | POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0)) |
| POINT (0.0 10.0) | null | null |