Oracle 23c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (id, geometry1, geometry2) AS
SELECT 1,
       SDO_GEOMETRY(
         'POLYGON ((-60.4757056 55.3541664, -60.4511908 55.3541664, -60.4465038 55.3541664, -60.4356551 55.3541664, -60.4329136 55.3541664, -60.4244905 55.3541664, -60.424006 55.3541664, -60.4117383 55.3541664, -60.4101404 55.3541664, -60.407924 55.3541664, -60.4043777 55.3541664, -60.3920481 55.3541664, -60.391244 55.3541664, -60.3518121 55.3541664, -60.3505029 55.3541664, -60.3468947 55.3541664, -60.3435134 55.3541664, -60.3216893 55.3541664, -60.3162255 55.3541664, -60.1931465 55.3541664, -60.1887886 55.3541664, -60.1870387 55.3541664, -60.1841416 55.3541664, -60.1831256 55.3541664, -60.1815229 55.3541664, -60.1810291 55.3541664, -60.1759252 55.3541664, -60.1732461 55.3541664, -60.1703932 55.3541664, -60.1689513 55.3541664, -60.1674771 55.3541664, -60.1621165 55.3541664, -60.1541877 55.3541664, -60.1498384 55.3541664, -60.1479519 55.3541664, -60.1463342 55.3541664, -60.1428966 55.3541664, -60.1401117 55.3541664, -60.13886 55.3541664, -60.1359213 55.3541664, -60.1243237 55.3541664, -60.0934379 55.3541664, -60.0922758 55.3541664, -60.0904735 55.3541664, -60.0889638 55.3541664, -60.0775724 55.3541664, -60.0773662 55.3541664, -60.0769539 55.3541664, -60.0763353 55.3541664, -60.0727066 55.3541664, -60.0717788 55.3541664, -60.0615935 55.3541664, -60.0590162 55.3541664, -60.0525364 55.3541664, -60.0472575 55.3541664, -60.0460372 55.3541664, -60.044996 55.3541664, -60.0435454 55.3541664, -60.0343738 55.3541664, -60.0145742 55.3541664, -60.0004525 55.3541664, -59.9987499 55.3541664, -59.9958221 55.3541664, -59.9930696 55.3541664, -59.9899048 55.3541664, -59.9859341 55.3541664, -59.9853547 55.3541664, -59.9792556 55.3541664, -59.9443494 55.3541664, -59.9427205 55.3541664, -59.9390196 55.3541664, -59.9373805 55.3541664, -59.9333187 55.3541664, -59.9304735 55.3541664, -59.9299271 55.3541664, -59.9229067 55.3541664, -59.9197109 55.3541664, -59.9076906 55.3541664, -59.902165 55.3541664, -59.8980105 55.3541664, -59.896794 55.3541664, -59.8952786 55.3541664, -59.8931833 55.3541664, -59.8931833 55.7350924, -60.1846106 55.7350924, -60.1870126 55.7350924, -60.1939094 55.7350924, -60.2142799 55.7350924, -60.2211457 55.7350924, -60.2252693 55.7350924, -60.2907127 55.7350924, -60.2907127 55.7334333, -60.2950631 55.7334333, -60.3050577 55.7334333, -60.3062726 55.7334333, -60.3089854 55.7334333, -60.3131605 55.7334333, -60.3138854 55.7334333, -60.3147204 55.7334333, -60.3153681 55.7334333, -60.3158667 55.7334333, -60.3285724 55.7334333, -60.3345325 55.7334333, -60.3390361 55.7334333, -60.3390361 55.7350924, -60.3394413 55.7350924, -60.3445443 55.7350924, -60.3632757 55.7350924, -60.3647087 55.7350924, -60.3654499 55.7350924, -60.3667292 55.7350924, -60.368147 55.7350924, -60.3691929 55.7350924, -60.370284 55.7350924, -60.3712884 55.7350924, -60.3725664 55.7350924, -60.3743277 55.7350924, -60.3825015 55.7350924, -60.4058645 55.7350924, -60.4134169 55.7350924, -60.4199212 55.7350924, -60.425876 55.7350924, -60.4332231 55.7350924, -60.4381289 55.7350924, -60.4630216 55.7350924, -60.4645986 55.7350924, -60.4743202 55.7350924, -60.4757056 55.7350924, -60.4757056 55.3541664))'
       , 4326
       ),
       SDO_GEOMETRY(
         EMPTY_CLOB()
         || 'POLYGON ((-61.0 55.7168071, -61.0 55.7167694, -60.9817025 55.7167694, -60.9795143 55.7167694, -60.9756928 55.7167694, -60.9687507 55.7167694, -60.9605554 55.7167694, -60.9591763 55.7167694, -60.948056 55.7167694, -60.9432483 55.7167694, -60.9368198 55.7167694, -60.9357168 55.7167694, -60.9333053 55.7167694, -60.9310483 55.7167694, -60.9229015 55.7167694, -60.917135 55.7167694, -60.9153211 55.7167694, -60.9136786 55.7167694, -60.9114387 55.7167694, -60.9094197 55.7167694, -60.8915441 55.7167694, -60.8899238 55.7167694, -60.8853053 55.7167694, -60.8837427 55.7167694, -60.8782716 55.7167694, -60.8769834 55.7167694, -60.8737722 55.7167694, -60.8724013 55.7167694, -60.8651472 55.7167694, -60.8651472 55.6917667, -60.8011799 55.6917667, -60.800015 55.6917667, -60.7211307 55.6917667, -60.7121103 55.6917667, -60.6970592 55.6917667, -60.6836059 55.6917667, -60.6446895 55.6917667, -60.6288652 55.6917667, -60.6117523 55.6917667, -60.6085565 55.6917667, -60.5682999 55.6917667, -60.5606712 55.6917667, -60.5160746 55.6917667, -60.512652 55.6917667, -60.4966009 55.6917667, -60.4757056 55.6917667, -60.4757056 55.6, -60.4752985 55.6, -60.4712781 55.6, -60.4688438 55.6, -60.4670646 55.6, -60.4631307 55.6, -60.4597782 55.6, -60.4582208 55.6, -60.4526069 55.6, -60.451966 55.6, -60.4513536 55.6, -60.4509127 55.6, -60.4448814 55.6, -60.4403962 55.6, -60.4402898 55.6, -60.4344438 55.6, -60.4334946 55.6, -60.4303203 55.6, -60.4285059 55.6, -60.4231674 55.6, -60.4174987 55.6, -60.415378 55.6, -60.4121859 55.6, -60.4102435 55.6, -60.4079514 55.6, -60.4012632 55.6, -60.397383 55.6, -60.3969371 55.6, -60.3965333 55.6, -60.3941915 55.6, -60.3929277 55.6, -60.3920227 55.6, -60.3880519 55.6, -60.3825638 55.6, -60.3417968 55.6, -60.3229132 55.6, -60.3192236 55.6, -60.3182227 55.6, -60.3153202 55.6, -60.3136306 55.6, -60.3079439 55.6, -60.306432 55.6, -60.3031708 55.6, -60.301988 55.6, -60.301039 55.6, -60.2743301 55.6, -60.2683018 55.6, -60.2606778 55.6, -60.2500322 55.6, -60.2466816 55.6, -60.2022765 55.6, -60.1960587 55.6, -60.1950389 55.6, -60.190461 55.6, -60.18668 55.6, -60.1853141 55.6, -60.18313 55.6, -60.1807056 55.6, -60.1794445 55.6, -60.177966 55.6, -60.1771734 55.6, -60.1762112 55.6, -60.1704969 55.6, -60.1515756 55.6, -60.1472902 55.6, -60.1411789 55.6, -60.1353758 55.6, -60.125382 55.6, -60.1170881 55.6, -60.11319 55.6, -60.1094956 55.6, -60.1012381 55.6, -60.0928554 55.6, -60.0907231 55.6, -60.0706492 55.6, -60.0680947 55.6, -60.0491625 55.6, -60.0468008 55.6, -60.0393243 55.6, -60.0356083 55.6, -60.0314167 55.6, -60.0233732 55.6, -60.0209438 55.6, -60.0198179 55.6, -60.0189851 55.6, -60.0159595 55.6, -60.0111296 55.6, -60.0107684 55.6, -60.00937 55.6, -60.0085104 55.6, -60.0062601 55.6, -60.0006418 55.6, -60.0 55.6, -60.0 56.0, -60.002849 56.0, -60.0083579 56.0, -60.0171017 56.0, -60.0329717 56.0, -60.0568461 56.0, -60.0606201 56.0, -60.0665906 56.0, -60.0743876 56.0, -60.1023102 56.0, -60.117125 56.0, -60.1306683 56.0, -60.1802246 56.0, -60.1826047 56.0, -60.2019078 56.0, -60.2796839 56.0, -60.2882126 56.0, -60.3038651 56.0, -60.4479776 56.0, -60.4521785 56.0'
         || ', -60.4694437 56.0, -60.4752348 56.0, -60.5014242 56.0, -60.5284461 56.0, -60.5349338 56.0, -60.5683671 56.0, -60.5737761 56.0, -60.6047555 56.0, -60.6107794 56.0, -60.6229453 56.0, -60.6389485 56.0, -60.642472 56.0, -60.6570749 56.0, -60.6688947 56.0, -60.6709022 56.0, -60.671506 56.0, -60.6719936 56.0, -60.6725549 56.0, -60.6728863 56.0, -60.6746121 56.0, -60.6755527 56.0, -60.6909816 56.0, -60.6951302 56.0, -60.7004948 56.0, -60.7076028 56.0, -60.7255689 56.0, -60.7329945 56.0, -60.7385602 56.0, -60.7627504 56.0, -60.7795795 56.0, -60.7839536 56.0, -60.8063863 56.0, -60.8203263 56.0, -60.8367709 56.0, -60.8418557 56.0, -60.85062 56.0, -60.8526281 56.0, -60.8539727 56.0, -60.8555074 56.0, -60.8610082 56.0, -60.8629168 56.0, -60.8651472 56.0, -60.88536 56.0, -60.8870747 56.0, -60.9041927 56.0, -60.9056821 56.0, -60.9158969 56.0, -60.9248846 56.0, -60.9297698 56.0, -60.9352106 56.0, -60.9399853 56.0, -60.9421551 56.0, -60.9576274 56.0, -60.9577205 56.0, -60.9718974 56.0, -60.9736478 56.0, -61.0 56.0, -61.0 55.7168071), (-60.3390361 55.7377957, -60.3390361 55.7387991, -60.3390361 55.7800998, -60.3389752 55.7800998, -60.3360487 55.7800998, -60.3341651 55.7800998, -60.33343 55.7800998, -60.3333919 55.7800998, -60.3282374 55.7800998, -60.3132115 55.7800998, -60.304166 55.7800998, -60.3025681 55.7800998, -60.3019472 55.7800998, -60.3016918 55.7800998, -60.3012382 55.7800998, -60.3008671 55.7800998, -60.2976713 55.7800998, -60.2970785 55.7800998, -60.2952229 55.7800998, -60.294991 55.7800998, -60.2941405 55.7800998, -60.2913828 55.7800998, -60.2907127 55.7800998, -60.2907127 55.7334333, -60.2950631 55.7334333, -60.3050577 55.7334333, -60.3062726 55.7334333, -60.3089854 55.7334333, -60.3131605 55.7334333, -60.3138854 55.7334333, -60.3147204 55.7334333, -60.3153681 55.7334333, -60.3158667 55.7334333, -60.3285725 55.7334333, -60.3345325 55.7334333, -60.3390361 55.7334333, -60.3390361 55.7377957))'
       , 4326
       )
FROM   DUAL
UNION ALL
-- Remove second set of co-ordinates
SELECT 2,
       SDO_GEOMETRY(
         'POLYGON ((-60.4757056 55.3541664, -60.4511908 55.3541664, -60.4465038 55.3541664, -60.4356551 55.3541664, -60.4329136 55.3541664, -60.4244905 55.3541664, -60.424006 55.3541664, -60.4117383 55.3541664, -60.4101404 55.3541664, -60.407924 55.3541664, -60.4043777 55.3541664, -60.3920481 55.3541664, -60.391244 55.3541664, -60.3518121 55.3541664, -60.3505029 55.3541664, -60.3468947 55.3541664, -60.3435134 55.3541664, -60.3216893 55.3541664, -60.3162255 55.3541664, -60.1931465 55.3541664, -60.1887886 55.3541664, -60.1870387 55.3541664, -60.1841416 55.3541664, -60.1831256 55.3541664, -60.1815229 55.3541664, -60.1810291 55.3541664, -60.1759252 55.3541664, -60.1732461 55.3541664, -60.1703932 55.3541664, -60.1689513 55.3541664, -60.1674771 55.3541664, -60.1621165 55.3541664, -60.1541877 55.3541664, -60.1498384 55.3541664, -60.1479519 55.3541664, -60.1463342 55.3541664, -60.1428966 55.3541664, -60.1401117 55.3541664, -60.13886 55.3541664, -60.1359213 55.3541664, -60.1243237 55.3541664, -60.0934379 55.3541664, -60.0922758 55.3541664, -60.0904735 55.3541664, -60.0889638 55.3541664, -60.0775724 55.3541664, -60.0773662 55.3541664, -60.0769539 55.3541664, -60.0763353 55.3541664, -60.0727066 55.3541664, -60.0717788 55.3541664, -60.0615935 55.3541664, -60.0590162 55.3541664, -60.0525364 55.3541664, -60.0472575 55.3541664, -60.0460372 55.3541664, -60.044996 55.3541664, -60.0435454 55.3541664, -60.0343738 55.3541664, -60.0145742 55.3541664, -60.0004525 55.3541664, -59.9987499 55.3541664, -59.9958221 55.3541664, -59.9930696 55.3541664, -59.9899048 55.3541664, -59.9859341 55.3541664, -59.9853547 55.3541664, -59.9792556 55.3541664, -59.9443494 55.3541664, -59.9427205 55.3541664, -59.9390196 55.3541664, -59.9373805 55.3541664, -59.9333187 55.3541664, -59.9304735 55.3541664, -59.9299271 55.3541664, -59.9229067 55.3541664, -59.9197109 55.3541664, -59.9076906 55.3541664, -59.902165 55.3541664, -59.8980105 55.3541664, -59.896794 55.3541664, -59.8952786 55.3541664, -59.8931833 55.3541664, -59.8931833 55.7350924, -60.1846106 55.7350924, -60.1870126 55.7350924, -60.1939094 55.7350924, -60.2142799 55.7350924, -60.2211457 55.7350924, -60.2252693 55.7350924, -60.2907127 55.7350924, -60.2907127 55.7334333, -60.2950631 55.7334333, -60.3050577 55.7334333, -60.3062726 55.7334333, -60.3089854 55.7334333, -60.3131605 55.7334333, -60.3138854 55.7334333, -60.3147204 55.7334333, -60.3153681 55.7334333, -60.3158667 55.7334333, -60.3285724 55.7334333, -60.3345325 55.7334333, -60.3390361 55.7334333, -60.3390361 55.7350924, -60.3394413 55.7350924, -60.3445443 55.7350924, -60.3632757 55.7350924, -60.3647087 55.7350924, -60.3654499 55.7350924, -60.3667292 55.7350924, -60.368147 55.7350924, -60.3691929 55.7350924, -60.370284 55.7350924, -60.3712884 55.7350924, -60.3725664 55.7350924, -60.3743277 55.7350924, -60.3825015 55.7350924, -60.4058645 55.7350924, -60.4134169 55.7350924, -60.4199212 55.7350924, -60.425876 55.7350924, -60.4332231 55.7350924, -60.4381289 55.7350924, -60.4630216 55.7350924, -60.4645986 55.7350924, -60.4743202 55.7350924, -60.4757056 55.7350924, -60.4757056 55.3541664))'
       , 4326
       ),
       SDO_GEOMETRY(
         EMPTY_CLOB()
         || 'POLYGON ((-61.0 55.7168071, -61.0 55.7167694, -60.9817025 55.7167694, -60.9795143 55.7167694, -60.9756928 55.7167694, -60.9687507 55.7167694, -60.9605554 55.7167694, -60.9591763 55.7167694, -60.948056 55.7167694, -60.9432483 55.7167694, -60.9368198 55.7167694, -60.9357168 55.7167694, -60.9333053 55.7167694, -60.9310483 55.7167694, -60.9229015 55.7167694, -60.917135 55.7167694, -60.9153211 55.7167694, -60.9136786 55.7167694, -60.9114387 55.7167694, -60.9094197 55.7167694, -60.8915441 55.7167694, -60.8899238 55.7167694, -60.8853053 55.7167694, -60.8837427 55.7167694, -60.8782716 55.7167694, -60.8769834 55.7167694, -60.8737722 55.7167694, -60.8724013 55.7167694, -60.8651472 55.7167694, -60.8651472 55.6917667, -60.8011799 55.6917667, -60.800015 55.6917667, -60.7211307 55.6917667, -60.7121103 55.6917667, -60.6970592 55.6917667, -60.6836059 55.6917667, -60.6446895 55.6917667, -60.6288652 55.6917667, -60.6117523 55.6917667, -60.6085565 55.6917667, -60.5682999 55.6917667, -60.5606712 55.6917667, -60.5160746 55.6917667, -60.512652 55.6917667, -60.4966009 55.6917667, -60.4757056 55.6917667, -60.4757056 55.6, -60.4752985 55.6, -60.4712781 55.6, -60.4688438 55.6, -60.4670646 55.6, -60.4631307 55.6, -60.4597782 55.6, -60.4582208 55.6, -60.4526069 55.6, -60.451966 55.6, -60.4513536 55.6, -60.4509127 55.6, -60.4448814 55.6, -60.4403962 55.6, -60.4402898 55.6, -60.4344438 55.6, -60.4334946 55.6, -60.4303203 55.6, -60.4285059 55.6, -60.4231674 55.6, -60.4174987 55.6, -60.415378 55.6, -60.4121859 55.6, -60.4102435 55.6, -60.4079514 55.6, -60.4012632 55.6, -60.397383 55.6, -60.3969371 55.6, -60.3965333 55.6, -60.3941915 55.6, -60.3929277 55.6, -60.3920227 55.6, -60.3880519 55.6, -60.3825638 55.6, -60.3417968 55.6, -60.3229132 55.6, -60.3192236 55.6, -60.3182227 55.6, -60.3153202 55.6, -60.3136306 55.6, -60.3079439 55.6, -60.306432 55.6, -60.3031708 55.6, -60.301988 55.6, -60.301039 55.6, -60.2743301 55.6, -60.2683018 55.6, -60.2606778 55.6, -60.2500322 55.6, -60.2466816 55.6, -60.2022765 55.6, -60.1960587 55.6, -60.1950389 55.6, -60.190461 55.6, -60.18668 55.6, -60.1853141 55.6, -60.18313 55.6, -60.1807056 55.6, -60.1794445 55.6, -60.177966 55.6, -60.1771734 55.6, -60.1762112 55.6, -60.1704969 55.6, -60.1515756 55.6, -60.1472902 55.6, -60.1411789 55.6, -60.1353758 55.6, -60.125382 55.6, -60.1170881 55.6, -60.11319 55.6, -60.1094956 55.6, -60.1012381 55.6, -60.0928554 55.6, -60.0907231 55.6, -60.0706492 55.6, -60.0680947 55.6, -60.0491625 55.6, -60.0468008 55.6, -60.0393243 55.6, -60.0356083 55.6, -60.0314167 55.6, -60.0233732 55.6, -60.0209438 55.6, -60.0198179 55.6, -60.0189851 55.6, -60.0159595 55.6, -60.0111296 55.6, -60.0107684 55.6, -60.00937 55.6, -60.0085104 55.6, -60.0062601 55.6, -60.0006418 55.6, -60.0 55.6, -60.0 56.0, -60.002849 56.0, -60.0083579 56.0, -60.0171017 56.0, -60.0329717 56.0, -60.0568461 56.0, -60.0606201 56.0, -60.0665906 56.0, -60.0743876 56.0, -60.1023102 56.0, -60.117125 56.0, -60.1306683 56.0, -60.1802246 56.0, -60.1826047 56.0, -60.2019078 56.0, -60.2796839 56.0, -60.2882126 56.0, -60.3038651 56.0, -60.4479776 56.0, -60.4521785 56.0'
         || ', -60.4694437 56.0, -60.4752348 56.0, -60.5014242 56.0, -60.5284461 56.0, -60.5349338 56.0, -60.5683671 56.0, -60.5737761 56.0, -60.6047555 56.0, -60.6107794 56.0, -60.6229453 56.0, -60.6389485 56.0, -60.642472 56.0, -60.6570749 56.0, -60.6688947 56.0, -60.6709022 56.0, -60.671506 56.0, -60.6719936 56.0, -60.6725549 56.0, -60.6728863 56.0, -60.6746121 56.0, -60.6755527 56.0, -60.6909816 56.0, -60.6951302 56.0, -60.7004948 56.0, -60.7076028 56.0, -60.7255689 56.0, -60.7329945 56.0, -60.7385602 56.0, -60.7627504 56.0, -60.7795795 56.0, -60.7839536 56.0, -60.8063863 56.0, -60.8203263 56.0, -60.8367709 56.0, -60.8418557 56.0, -60.85062 56.0, -60.8526281 56.0, -60.8539727 56.0, -60.8555074 56.0, -60.8610082 56.0, -60.8629168 56.0, -60.8651472 56.0, -60.88536 56.0, -60.8870747 56.0, -60.9041927 56.0, -60.9056821 56.0, -60.9158969 56.0, -60.9248846 56.0, -60.9297698 56.0, -60.9352106 56.0, -60.9399853 56.0, -60.9421551 56.0, -60.9576274 56.0, -60.9577205 56.0, -60.9718974 56.0, -60.9736478 56.0, -61.0 56.0, -61.0 55.7168071))'
       , 4326
       )
FROM   DUAL
2 rows affected

-- Finding cycles within the polygons
WITH points (id, idx, lt, ln, np) AS (
  SELECT t.id,
         p2.idx,
         p2.lt,
         p2.ln,
         MAX(p2.idx) OVER (PARTITION BY t.id)
  FROM   table_name t
         CROSS JOIN LATERAL (
           SELECT CEIL(idx/2) AS idx,
                  MAX(CASE MOD(idx, 2) WHEN 1 THEN value END) AS lt,
                  MAX(CASE MOD(idx, 2) WHEN 0 THEN value END) AS ln
           FROM   JSON_TABLE(
                    JSON_OBJECT(geometry2 RETURNING JSON),
                    '$.SDO_ORDINATES[*]'
                    COLUMNS
                      idx   FOR ORDINALITY,
                      value NUMBER PATH '$'
                  )
           GROUP BY CEIL(idx/2)
         ) p2
)
SELECT *
FROM   points p1
       INNER JOIN points p2
       ON p1.id = p2.id AND p1.idx < p2.idx AND p1.lt = p2.lt AND p1.ln = p2.ln


ID
IDX
LT
LN
NP
ID
IDX
LT
LN
NP


1
1
-61
55.7168071
254
1
217
-61
55.7168071
254

1
218
-60.3390361
55.7377957
254
1
254
-60.3390361
55.7377957
254

2
1
-61
55.7168071
217
2
217
-61
55.7168071
217


SELECT id,
       JSON_OBJECT(SDO_GEOM.SDO_INTERSECTION(geometry1, geometry2, 0.0000001)) as intersection
FROM   table_name
WHERE  SDO_GEOM.RELATE(geometry1, 'ANYINTERACT', geometry2, 0.0000001) = 'TRUE'


ID
INTERSECTION


1
null

2
{"SDO_GTYPE":2003,"SDO_SRID":4326,"SDO_POINT":{},"SDO_ELEM_INFO":[1,1003,1],"SDO_ORDINATES":[-60.4757056,55.6,-60.4752985,55.6,-60.4712781,55.6,-60.4688438,55.6,-60.4670646,55.6,-60.4631307,55.6,-60.4597782,55.6,-60.4582208,55.6,-60.4526069,55.6,-60.451966,55.6,-60.4513536,55.6,-60.4509127,55.6,-60.4448814,55.6,-60.4403962,55.6,-60.4402898,55.6,-60.4344438,55.6,-60.4334946,55.6,-60.4303203,55.6,-60.4285059,55.6,-60.4231674,55.6,-60.4174987,55.6,-60.415378,55.6,-60.4121859,55.6,-60.4102435,55.6,-60.4079514,55.6,-60.4012632,55.6,-60.397383,55.6,-60.3969371,55.6,-60.3965333,55.6,-60.3941915,55.6,-60.3929277,55.6,-60.3920227,55.6,-60.3880519,55.6,-60.3825638,55.6,-60.3417968,55.6,-60.3229132,55.6,-60.3192236,55.6,-60.3182227,55.6,-60.3153202,55.6,-60.3136306,55.6,-60.3079439,55.6,-60.306432,55.6,-60.3031708,55.6,-60.301988,55.6,-60.301039,55.6,-60.2743301,55.6,-60.2683018,55.6,-60.2606778,55.6,-60.2500322,55.6,-60.2466816,55.6,-60.2022765,55.6,-60.1960587,55.6,-60.1950389,55.6,-60.190461,55.6,-60.18668,55.6,-60.1853141,55.6,-60.18313,55.6,-60.1807056,55.6,-60.1794445,55.6,-60.177966,55.6,-60.1771734,55.6,-60.1762112,55.6,-60.1704969,55.6,-60.1515756,55.6,-60.1472902,55.6,-60.1411789,55.6,-60.1353758,55.6,-60.125382,55.6,-60.1170881,55.6,-60.11319,55.6,-60.1094956,55.6,-60.1012381,55.6,-60.0928554,55.6,-60.0907231,55.6,-60.0706492,55.6,-60.0680947,55.6,-60.0491625,55.6,-60.0468008,55.6,-60.0393243,55.6,-60.0356083,55.6,-60.0314167,55.6,-60.0233732,55.6,-60.0209438,55.6,-60.0198179,55.6,-60.0189851,55.6,-60.0159595,55.6,-60.0111296,55.6,-60.0107684,55.6,-60.00937,55.6,-60.0085104,55.6,-60.0062601,55.6,-60.0006418,55.6,-60,55.6,-60.0000000000002,55.7351724719282,-60.1846106,55.7350924,-60.1870126,55.7350924,-60.1939094,55.7350924,-60.2142799,55.7350924,-60.2211457,55.7350924,-60.2252693,55.7350924,-60.2907127,55.7350924,-60.2907127,55.7334333,-60.2950631,55.7334333,-60.3050577,55.7334333,-60.3062726,55.7334333,-60.3089854,55.7334333,-60.3131605,55.7334333,-60.3138854,55.7334333,-60.3147204,55.7334333,-60.3153681,55.7334333,-60.3158667,55.7334333,-60.3285724,55.7334333,-60.3345325,55.7334333,-60.3390361,55.7334333,-60.3390361,55.7350924,-60.3394413,55.7350924,-60.3445443,55.7350924,-60.3632757,55.7350924,-60.3647087,55.7350924,-60.3654499,55.7350924,-60.3667292,55.7350924,-60.368147,55.7350924,-60.3691929,55.7350924,-60.370284,55.7350924,-60.3712884,55.7350924,-60.3725664,55.7350924,-60.3743277,55.7350924,-60.3825015,55.7350924,-60.4058645,55.7350924,-60.4134169,55.7350924,-60.4199212,55.7350924,-60.425876,55.7350924,-60.4332231,55.7350924,-60.4381289,55.7350924,-60.4630216,55.7350924,-60.4645986,55.7350924,-60.4743202,55.7350924,-60.4757056,55.7350924,-60.4757056,55.6917667,-60.4757056,55.6]}


INSERT INTO table_name (id, geometry1, geometry2)
SELECT 3,
       SDO_GEOMETRY(
         'POLYGON ('
         || '(10 10, 10 20, 20 20, 20 10, 10 10), '
         || '(12 12, 18 12, 18 18, 12 18, 12 12)'
         || ')',
         4326
       ),
       SDO_GEOMETRY(
         'POLYGON ('
         || '(10 10, 10 20, 20 20, 20 10, 10 10)'
         || ')',
         4326
       )
FROM   DUAL
UNION ALL
SELECT 4,
       SDO_GEOMETRY(
         'POLYGON ('
         || '(10 10, 10 20, 20 20, 20 10, 10 10), '
         || '(8 8, 22 8, 22 22, 8 22, 8 8)'
         || ')',
         4326
       ),
       SDO_GEOMETRY(
         'POLYGON ('
         || '(10 10, 10 20, 20 20, 20 10, 10 10)'
         || ')',
         4326
       )
FROM   DUAL
UNION ALL
SELECT 5,
       SDO_GEOMETRY(
         'POLYGON ('
         || '(10 10, 10 20, 20 20, 20 10, 10 10), '
         || '(8 8, 8 22, 22 22, 22 8, 8 8)'
         || ')',
         4326
       ),
       SDO_GEOMETRY(
         'POLYGON ('
         || '(10 10, 10 20, 20 20, 20 10, 10 10)'
         || ')',
         4326
       )
FROM   DUAL
3 rows affected

SELECT id,
       JSON_OBJECT(SDO_GEOM.SDO_INTERSECTION(geometry1, geometry2, 0.0000001)) as intersection
FROM   table_name
WHERE  SDO_GEOM.RELATE(geometry1, 'ANYINTERACT', geometry2, 0.0000001) = 'TRUE'


ID
INTERSECTION


1
null

2
{"SDO_GTYPE":2003,"SDO_SRID":4326,"SDO_POINT":{},"SDO_ELEM_INFO":[1,1003,1],"SDO_ORDINATES":[-60.4757056,55.6,-60.4752985,55.6,-60.4712781,55.6,-60.4688438,55.6,-60.4670646,55.6,-60.4631307,55.6,-60.4597782,55.6,-60.4582208,55.6,-60.4526069,55.6,-60.451966,55.6,-60.4513536,55.6,-60.4509127,55.6,-60.4448814,55.6,-60.4403962,55.6,-60.4402898,55.6,-60.4344438,55.6,-60.4334946,55.6,-60.4303203,55.6,-60.4285059,55.6,-60.4231674,55.6,-60.4174987,55.6,-60.415378,55.6,-60.4121859,55.6,-60.4102435,55.6,-60.4079514,55.6,-60.4012632,55.6,-60.397383,55.6,-60.3969371,55.6,-60.3965333,55.6,-60.3941915,55.6,-60.3929277,55.6,-60.3920227,55.6,-60.3880519,55.6,-60.3825638,55.6,-60.3417968,55.6,-60.3229132,55.6,-60.3192236,55.6,-60.3182227,55.6,-60.3153202,55.6,-60.3136306,55.6,-60.3079439,55.6,-60.306432,55.6,-60.3031708,55.6,-60.301988,55.6,-60.301039,55.6,-60.2743301,55.6,-60.2683018,55.6,-60.2606778,55.6,-60.2500322,55.6,-60.2466816,55.6,-60.2022765,55.6,-60.1960587,55.6,-60.1950389,55.6,-60.190461,55.6,-60.18668,55.6,-60.1853141,55.6,-60.18313,55.6,-60.1807056,55.6,-60.1794445,55.6,-60.177966,55.6,-60.1771734,55.6,-60.1762112,55.6,-60.1704969,55.6,-60.1515756,55.6,-60.1472902,55.6,-60.1411789,55.6,-60.1353758,55.6,-60.125382,55.6,-60.1170881,55.6,-60.11319,55.6,-60.1094956,55.6,-60.1012381,55.6,-60.0928554,55.6,-60.0907231,55.6,-60.0706492,55.6,-60.0680947,55.6,-60.0491625,55.6,-60.0468008,55.6,-60.0393243,55.6,-60.0356083,55.6,-60.0314167,55.6,-60.0233732,55.6,-60.0209438,55.6,-60.0198179,55.6,-60.0189851,55.6,-60.0159595,55.6,-60.0111296,55.6,-60.0107684,55.6,-60.00937,55.6,-60.0085104,55.6,-60.0062601,55.6,-60.0006418,55.6,-60,55.6,-60.0000000000002,55.7351724719282,-60.1846106,55.7350924,-60.1870126,55.7350924,-60.1939094,55.7350924,-60.2142799,55.7350924,-60.2211457,55.7350924,-60.2252693,55.7350924,-60.2907127,55.7350924,-60.2907127,55.7334333,-60.2950631,55.7334333,-60.3050577,55.7334333,-60.3062726,55.7334333,-60.3089854,55.7334333,-60.3131605,55.7334333,-60.3138854,55.7334333,-60.3147204,55.7334333,-60.3153681,55.7334333,-60.3158667,55.7334333,-60.3285724,55.7334333,-60.3345325,55.7334333,-60.3390361,55.7334333,-60.3390361,55.7350924,-60.3394413,55.7350924,-60.3445443,55.7350924,-60.3632757,55.7350924,-60.3647087,55.7350924,-60.3654499,55.7350924,-60.3667292,55.7350924,-60.368147,55.7350924,-60.3691929,55.7350924,-60.370284,55.7350924,-60.3712884,55.7350924,-60.3725664,55.7350924,-60.3743277,55.7350924,-60.3825015,55.7350924,-60.4058645,55.7350924,-60.4134169,55.7350924,-60.4199212,55.7350924,-60.425876,55.7350924,-60.4332231,55.7350924,-60.4381289,55.7350924,-60.4630216,55.7350924,-60.4645986,55.7350924,-60.4743202,55.7350924,-60.4757056,55.7350924,-60.4757056,55.6917667,-60.4757056,55.6]}

3
{"SDO_GTYPE":2003,"SDO_SRID":4326,"SDO_POINT":{},"SDO_ELEM_INFO":[1,1003,1,11,2003,1],"SDO_ORDINATES":[10,10,20,10,20,20,10,20,10,10,12,12,12,18,18,18,18,12,12,12]}

4
{"SDO_GTYPE":2003,"SDO_SRID":4326,"SDO_POINT":{},"SDO_ELEM_INFO":[1,1003,1],"SDO_ORDINATES":[10,10,20,10,20,20,10,20,10,10]}

5
{"SDO_GTYPE":2003,"SDO_SRID":4326,"SDO_POINT":{},"SDO_ELEM_INFO":[1,1003,1],"SDO_ORDINATES":[10,10,20,10,20,20,10,20,10,10]}

ID	IDX	LT	LN	NP	ID	IDX	LT	LN	NP
1	1	-61	55.7168071	254	1	217	-61	55.7168071	254
1	218	-60.3390361	55.7377957	254	1	254	-60.3390361	55.7377957	254
2	1	-61	55.7168071	217	2	217	-61	55.7168071	217

ID	INTERSECTION
1	null
2	{"SDO_GTYPE":2003,"SDO_SRID":4326,"SDO_POINT":{},"SDO_ELEM_INFO":[1,1003,1],"SDO_ORDINATES":[-60.4757056,55.6,-60.4752985,55.6,-60.4712781,55.6,-60.4688438,55.6,-60.4670646,55.6,-60.4631307,55.6,-60.4597782,55.6,-60.4582208,55.6,-60.4526069,55.6,-60.451966,55.6,-60.4513536,55.6,-60.4509127,55.6,-60.4448814,55.6,-60.4403962,55.6,-60.4402898,55.6,-60.4344438,55.6,-60.4334946,55.6,-60.4303203,55.6,-60.4285059,55.6,-60.4231674,55.6,-60.4174987,55.6,-60.415378,55.6,-60.4121859,55.6,-60.4102435,55.6,-60.4079514,55.6,-60.4012632,55.6,-60.397383,55.6,-60.3969371,55.6,-60.3965333,55.6,-60.3941915,55.6,-60.3929277,55.6,-60.3920227,55.6,-60.3880519,55.6,-60.3825638,55.6,-60.3417968,55.6,-60.3229132,55.6,-60.3192236,55.6,-60.3182227,55.6,-60.3153202,55.6,-60.3136306,55.6,-60.3079439,55.6,-60.306432,55.6,-60.3031708,55.6,-60.301988,55.6,-60.301039,55.6,-60.2743301,55.6,-60.2683018,55.6,-60.2606778,55.6,-60.2500322,55.6,-60.2466816,55.6,-60.2022765,55.6,-60.1960587,55.6,-60.1950389,55.6,-60.190461,55.6,-60.18668,55.6,-60.1853141,55.6,-60.18313,55.6,-60.1807056,55.6,-60.1794445,55.6,-60.177966,55.6,-60.1771734,55.6,-60.1762112,55.6,-60.1704969,55.6,-60.1515756,55.6,-60.1472902,55.6,-60.1411789,55.6,-60.1353758,55.6,-60.125382,55.6,-60.1170881,55.6,-60.11319,55.6,-60.1094956,55.6,-60.1012381,55.6,-60.0928554,55.6,-60.0907231,55.6,-60.0706492,55.6,-60.0680947,55.6,-60.0491625,55.6,-60.0468008,55.6,-60.0393243,55.6,-60.0356083,55.6,-60.0314167,55.6,-60.0233732,55.6,-60.0209438,55.6,-60.0198179,55.6,-60.0189851,55.6,-60.0159595,55.6,-60.0111296,55.6,-60.0107684,55.6,-60.00937,55.6,-60.0085104,55.6,-60.0062601,55.6,-60.0006418,55.6,-60,55.6,-60.0000000000002,55.7351724719282,-60.1846106,55.7350924,-60.1870126,55.7350924,-60.1939094,55.7350924,-60.2142799,55.7350924,-60.2211457,55.7350924,-60.2252693,55.7350924,-60.2907127,55.7350924,-60.2907127,55.7334333,-60.2950631,55.7334333,-60.3050577,55.7334333,-60.3062726,55.7334333,-60.3089854,55.7334333,-60.3131605,55.7334333,-60.3138854,55.7334333,-60.3147204,55.7334333,-60.3153681,55.7334333,-60.3158667,55.7334333,-60.3285724,55.7334333,-60.3345325,55.7334333,-60.3390361,55.7334333,-60.3390361,55.7350924,-60.3394413,55.7350924,-60.3445443,55.7350924,-60.3632757,55.7350924,-60.3647087,55.7350924,-60.3654499,55.7350924,-60.3667292,55.7350924,-60.368147,55.7350924,-60.3691929,55.7350924,-60.370284,55.7350924,-60.3712884,55.7350924,-60.3725664,55.7350924,-60.3743277,55.7350924,-60.3825015,55.7350924,-60.4058645,55.7350924,-60.4134169,55.7350924,-60.4199212,55.7350924,-60.425876,55.7350924,-60.4332231,55.7350924,-60.4381289,55.7350924,-60.4630216,55.7350924,-60.4645986,55.7350924,-60.4743202,55.7350924,-60.4757056,55.7350924,-60.4757056,55.6917667,-60.4757056,55.6]}
3	{"SDO_GTYPE":2003,"SDO_SRID":4326,"SDO_POINT":{},"SDO_ELEM_INFO":[1,1003,1,11,2003,1],"SDO_ORDINATES":[10,10,20,10,20,20,10,20,10,10,12,12,12,18,18,18,18,12,12,12]}
4	{"SDO_GTYPE":2003,"SDO_SRID":4326,"SDO_POINT":{},"SDO_ELEM_INFO":[1,1003,1],"SDO_ORDINATES":[10,10,20,10,20,20,10,20,10,10]}
5	{"SDO_GTYPE":2003,"SDO_SRID":4326,"SDO_POINT":{},"SDO_ELEM_INFO":[1,1003,1],"SDO_ORDINATES":[10,10,20,10,20,20,10,20,10,10]}