Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Notes:
-- A) Statements #1-13 can be ignored since they just create sample data. Skip to #14.
-- B) Sometimes db<>fiddle throws an error: "Run Failed". Best guess is: it times-out
--    due to the slow query. Try running it again. It usually works on the 2nd try.
-- C) The sample data query source: (statements #5, #11, #12)
--    @Davos Jun 14, 2022 https://stackoverflow.com/a/72621332/5576771 
-- D) The sample data is fake data. My real data is more complex. For example, the
--    polygons aren't squares, they're irregular road right-of-way property polygons.
--    So we wouldn't do the "intersect" calculation using simple math based on XY
--    coordinates. We would likely need to use SDO_GEOMETRY spatial functions since
--    they'd be able to handle complex polygons with ease. That's why I used 
--    SDO_ANYINTERACT() in my query (in statement #14).

--#1
--Create sample polygon data.
CREATE TABLE ply (objectid     NUMBER(*,0), 
                  query_source NVARCHAR2(255),
                  shape        SDO_GEOMETRY);
--#2
CREATE UNIQUE INDEX ply_objectid ON ply (objectid);
--#3
INSERT INTO user_sdo_geom_metadata (table_name,column_name,diminfo,srid) 
VALUES (
    'ply',
    'SHAPE',
    sdo_dim_array(
        sdo_dim_element(NULL, 0, 100, 0.001), 
        sdo_dim_element(NULL, 0, 100, 0.001)
    ),
    26917
    );
1 rows affected

--#4
CREATE INDEX ply_shape ON ply (shape) INDEXTYPE IS mdsys.spatial_index_v2;
--#5
insert into ply select * from (
select
    cast(rownum as number(38,0)) as objectid,
    'Query - Polygons' as query_source,
    sdo_geometry('polygon(('||startpoint_x||' '||startpoint_y||', '||endpoint_x||' '||endpoint_y||', '||endpoint_x||' '||(endpoint_y+1)||', '||startpoint_x||' '||(startpoint_y+1)||', '||startpoint_x||' '||startpoint_y||'))',26917) as shape
from 
    (
    with dimension as (
        select 0 as point from dual
            union all 
        select level
        from dual
        connect by level <= 70
    ), points as (
        select 
            a.point as startpoint, 
            b.point as endpoint,
            c.point as fixed
        from dimension a
        cross join dimension b
        cross join dimension c
        where b.point - a.point = 1
    )
    select
        startpoint_x  +  2.25 as startpoint_x,
        startpoint_y  +  2.25 as startpoint_y,
        endpoint_x    +  2.25 as endpoint_x,
        endpoint_y    +  2.25 as endpoint_y
    from
        (
        select
            startpoint as startpoint_x,
            fixed      as startpoint_y,
            endpoint   as endpoint_x,
            fixed      as endpoint_y
        from points
            union all
        select
            fixed      as startpoint_x,
            startpoint as startpoint_y,
            fixed      as endpoint_x,
            endpoint   as endpoint_y
        from points
        )
    )
where
    startpoint_y = endpoint_y
);
4970 rows affected

--#6
--Show the first 10 rows to get an idea of what the polygon data looks like.
select objectid, 
       query_source,
       sdo_util.to_wktgeometry(shape)
from ply
fetch first 10 rows only


OBJECTID
QUERY_SOURCE
SDO_UTIL.TO_WKTGEOMETRY(SHAPE)


1
Query - Polygons
POLYGON ((4.25 5.25, 5.25 5.25, 5.25 6.25, 4.25 6.25, 4.25 5.25))

2
Query - Polygons
POLYGON ((4.25 6.25, 5.25 6.25, 5.25 7.25, 4.25 7.25, 4.25 6.25))

3
Query - Polygons
POLYGON ((4.25 7.25, 5.25 7.25, 5.25 8.25, 4.25 8.25, 4.25 7.25))

4
Query - Polygons
POLYGON ((4.25 8.25, 5.25 8.25, 5.25 9.25, 4.25 9.25, 4.25 8.25))

5
Query - Polygons
POLYGON ((4.25 9.25, 5.25 9.25, 5.25 10.25, 4.25 10.25, 4.25 9.25))

6
Query - Polygons
POLYGON ((4.25 10.25, 5.25 10.25, 5.25 11.25, 4.25 11.25, 4.25 10.25))

7
Query - Polygons
POLYGON ((4.25 11.25, 5.25 11.25, 5.25 12.25, 4.25 12.25, 4.25 11.25))

8
Query - Polygons
POLYGON ((4.25 12.25, 5.25 12.25, 5.25 13.25, 4.25 13.25, 4.25 12.25))

9
Query - Polygons
POLYGON ((4.25 13.25, 5.25 13.25, 5.25 14.25, 4.25 14.25, 4.25 13.25))

10
Query - Polygons
POLYGON ((4.25 14.25, 5.25 14.25, 5.25 15.25, 4.25 15.25, 4.25 14.25))


--#7
--Create sample point data.
CREATE TABLE pnt (objectid     NUMBER(*,0), 
                  query_source NVARCHAR2(255),
                  shape        SDO_GEOMETRY);
--#8
CREATE UNIQUE INDEX pnt_objectid ON pnt (objectid);
--#9
INSERT INTO user_sdo_geom_metadata (table_name,column_name,diminfo,srid) 
VALUES (
    'PNT',
    'SHAPE',
    sdo_dim_array(
        sdo_dim_element(NULL, 0, 100, 0.001), 
        sdo_dim_element(NULL, 0, 100, 0.001)
    ),
    26917
    );
1 rows affected

--#10
CREATE INDEX pnt_shape ON pnt (shape) INDEXTYPE IS mdsys.spatial_index_v2;
--#11
--First batch of points
insert into pnt select * from (
with dimension as (
    select 0 as point from dual
      union all 
    select level
    from dual
    connect by level <= 40
), points as (
    select 
      a.point as startpoint, 
      b.point as endpoint,
      c.point as fixed
    from dimension a
    cross join dimension b
    cross join dimension c
    where b.point - a.point = 1
)
select
    cast(rownum as number(38,0)) as objectid,
    'Query - Points 1' as query_source,
    sdo_geometry('point('||x||' '||y||')',26917) as shape
from
    (
    select startpoint        as x, fixed          as y from points union all
    select fixed             as x, startpoint     as y from points
    )
);
3280 rows affected

--#12
--Second batch of points. 
--The purpose of this second query is to create additional points so that there are 
--multiple points per polygon in some cases.
insert into pnt select * from (
with dimension as (
    select 0 as point from dual
      union all 
    select level
    from dual
    connect by level <= 10
), points as (
    select 
      a.point as startpoint, 
      b.point as endpoint,
      c.point as fixed
    from dimension a
    cross join dimension b
    cross join dimension c
    where b.point - a.point = 1
)
select
    cast(rownum + 10000 as number(38,0)) as objectid,
    'Query - Points 2' as query_source,
    sdo_geometry('point('||x||' '||y||')',26917) as shape
from
    (
    select endpoint+0.5      as x, fixed   +0.5   as y from points union all
    select fixed   +0.5      as x, endpoint+0.5   as y from points 
    )
);
220 rows affected

--#13
--Show the first 10 rows to get an idea of what the point data looks like.
select objectid, 
       query_source,
       sdo_util.to_wktgeometry(shape)
from pnt
fetch first 10 rows only


OBJECTID
QUERY_SOURCE
SDO_UTIL.TO_WKTGEOMETRY(SHAPE)


1
Query - Points 1
POINT (2.0 3.0)

2
Query - Points 1
POINT (2.0 4.0)

3
Query - Points 1
POINT (2.0 5.0)

4
Query - Points 1
POINT (2.0 6.0)

5
Query - Points 1
POINT (2.0 7.0)

6
Query - Points 1
POINT (2.0 8.0)

7
Query - Points 1
POINT (2.0 9.0)

8
Query - Points 1
POINT (2.0 10.0)

9
Query - Points 1
POINT (2.0 11.0)

10
Query - Points 1
POINT (2.0 12.0)


--#14
--The main query.
EXPLAIN PLAN FOR (
  SELECT objectid 
    FROM (SELECT ply.objectid,
                 row_number() over(partition by ply.objectid order by null) rn
            FROM ply                                       --ORDER BY NULL is intentional.
      CROSS JOIN pnt                                       --It doesn't matter what polygon row per OBJECTID gets selected.
           WHERE sdo_anyinteract(ply.shape, pnt.shape) = 'TRUE'
         )
  WHERE rn = 1
  )
--#15
select * from table(dbms_xplan.display)

--An easier-to-read version of the explain plan:  
-- --------------------------------------------------------------------------------------------------------
-- | Id  | Operation                          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-- --------------------------------------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT                   |           |  1457 | 37882 |       | 12851   (1)| 00:00:01 |
-- |*  1 |  VIEW                              |           |  1457 | 37882 |       | 12851   (1)| 00:00:01 |
-- |*  2 |   WINDOW SORT PUSHED RANK          |           |  1457 |    10M|    11M| 12851   (1)| 00:00:01 |
-- |   3 |    NESTED LOOPS                    |           |  1457 |    10M|       | 10522   (1)| 00:00:01 |
-- |   4 |     TABLE ACCESS FULL              | PNT       |  3500 |    12M|       |    11   (0)| 00:00:01 |
-- |   5 |     TABLE ACCESS BY INDEX ROWID    | PLY       |     1 |  3848 |       | 10522   (1)| 00:00:01 |
-- |*  6 |      DOMAIN INDEX (SEL: 0.010000 %)| PLY_SHAPE |       |       |       |     3   (0)| 00:00:01 |
-- --------------------------------------------------------------------------------------------------------
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--    1 - filter("RN"=1)
--    2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PLY"."OBJECTID" ORDER BY  NULL )<=1)
--    6 - access("MDSYS"."SDO_ANYINTERACT"("PLY"."SHAPE","PNT"."SHAPE")='TRUE')
-- Note
-- -----
--    - dynamic statistics used: dynamic sampling (level=2)


PLAN_TABLE_OUTPUT


Plan hash value: 3021517553

 

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

| Id  | Operation                          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                   |           |  1389 | 36114 |       | 12742   (1)| 00:00:01 |

|*  1 |  VIEW                              |           |  1389 | 36114 |       | 12742   (1)| 00:00:01 |

|*  2 |   WINDOW SORT PUSHED RANK          |           |  1389 |    10M|    10M| 12742   (1)| 00:00:01 |

|   3 |    NESTED LOOPS                    |           |  1389 |    10M|       | 10522   (1)| 00:00:01 |

|   4 |     TABLE ACCESS FULL              | PNT       |  3500 |    12M|       |    11   (0)| 00:00:01 |

|   5 |     TABLE ACCESS BY INDEX ROWID    | PLY       |     1 |  3848 |       | 10522   (1)| 00:00:01 |

|*  6 |      DOMAIN INDEX (SEL: 0.010000 %)| PLY_SHAPE |       |       |       |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("RN"=1)

   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PLY"."OBJECTID" ORDER BY  NULL )<=1)

   6 - access("MDSYS"."SDO_ANYINTERACT"("PLY"."SHAPE","PNT"."SHAPE")='TRUE')

 

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

OBJECTID	QUERY_SOURCE	SDO_UTIL.TO_WKTGEOMETRY(SHAPE)
1	Query - Polygons	POLYGON ((4.25 5.25, 5.25 5.25, 5.25 6.25, 4.25 6.25, 4.25 5.25))
2	Query - Polygons	POLYGON ((4.25 6.25, 5.25 6.25, 5.25 7.25, 4.25 7.25, 4.25 6.25))
3	Query - Polygons	POLYGON ((4.25 7.25, 5.25 7.25, 5.25 8.25, 4.25 8.25, 4.25 7.25))
4	Query - Polygons	POLYGON ((4.25 8.25, 5.25 8.25, 5.25 9.25, 4.25 9.25, 4.25 8.25))
5	Query - Polygons	POLYGON ((4.25 9.25, 5.25 9.25, 5.25 10.25, 4.25 10.25, 4.25 9.25))
6	Query - Polygons	POLYGON ((4.25 10.25, 5.25 10.25, 5.25 11.25, 4.25 11.25, 4.25 10.25))
7	Query - Polygons	POLYGON ((4.25 11.25, 5.25 11.25, 5.25 12.25, 4.25 12.25, 4.25 11.25))
8	Query - Polygons	POLYGON ((4.25 12.25, 5.25 12.25, 5.25 13.25, 4.25 13.25, 4.25 12.25))
9	Query - Polygons	POLYGON ((4.25 13.25, 5.25 13.25, 5.25 14.25, 4.25 14.25, 4.25 13.25))
10	Query - Polygons	POLYGON ((4.25 14.25, 5.25 14.25, 5.25 15.25, 4.25 15.25, 4.25 14.25))

OBJECTID	QUERY_SOURCE	SDO_UTIL.TO_WKTGEOMETRY(SHAPE)
1	Query - Points 1	POINT (2.0 3.0)
2	Query - Points 1	POINT (2.0 4.0)
3	Query - Points 1	POINT (2.0 5.0)
4	Query - Points 1	POINT (2.0 6.0)
5	Query - Points 1	POINT (2.0 7.0)
6	Query - Points 1	POINT (2.0 8.0)
7	Query - Points 1	POINT (2.0 9.0)
8	Query - Points 1	POINT (2.0 10.0)
9	Query - Points 1	POINT (2.0 11.0)
10	Query - Points 1	POINT (2.0 12.0)