Querying a list of geometry objects from Oracle based on list of x and y coordinates

We are using an Oracle database table with SDO_GEOMETRY objects. I would like to return the rows where the column with the geometry object contains a certain point (x, y coordinate).

I know I can query the table using a single point geometry like this:

[..]
WHERE SDO_CONTAINS(D.D_SHAPE, SDO_GEOMETRY(
      2001,
      NULL,
      SDO_POINT_TYPE(<myXCoordinate>, <myYCoordinate>, NULL),
      NULL,
      NULL)) = 'TRUE'

But let’s say I have a list of coordinates and I would like to have all geometry objects/rows where the object contains at least one coordinate from the list. How can I do so?

I know I could for example just implement an OR and do something like that:

WHERE SDO_CONTAINS(D.D_SHAPE, SDO_GEOMETRY(
        2001,
        NULL,
        SDO_POINT_TYPE(<myXCoordinate1>, <myYCoordinate1>, NULL),
        NULL,
        NULL)) = 'TRUE'
   OR SDO_CONTAINS(D.D_SHAPE, SDO_GEOMETRY(
        2001,
        NULL,
        SDO_POINT_TYPE(<myXCoordinate2>, <myYCoordinate2>, NULL),
        NULL,
        NULL)) = 'TRUE'

but that doesn’t seem to scale well when having a list with e.g. 1000 coordinates, right?

Also I might have a temporary table with x and y coordinates and would like to use a join. Not sure how I would do that here?

Answer

You may just join your “list” of points with the table of shapes using SDO_CONTAINS function.

If this list comes from some other table, you can apply join as it is (or by constructing SDO_GEOMETRY of type point in place). If you need to pass them “manually”, then there’s no option because I do not know handy rowset generation functions in Oracle (like select ... from values ... in other DBMSes). Some excercises may be done with, for example JSON, or you may use collection type as a source of records, which can be “typed in” manually or passed from outside.

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

insert into point
/*List of points (0, y)*/
select
  level
  , 0
  , level
from dual
connect by level < 8

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                                                         

And below is collection type example.

/*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                                                         

db<>fiddle here