DEVHIDE
Login Or Sign up

Oracle SDO_INTERSECTION returning null where an intersection exists in SRID 4326

39 Views Asked by At

So, I am using oracle to first find the intersections that exist between two geometries, and I have hit a snag where I think this may be an actual oracle error. It has overall been working well, except in this case where it failed to find the intersection where one exists.

Here is my query below:

SELECT
    SDO_GEOM.SDO_INTERSECTION(a.geometry, b.geometry, 0.0000001) as intersection
FROM
(SELECT
    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)
as GEOMETRY
FROM DUAL) a,
(SELECT
    SDO_GEOMETRY(
    to_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')
    || to_clob(', -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)
as GEOMETRY
FROM DUAL) b
where SDO_GEOM.RELATE(a.geometry, 'ANYINTERACT', b.geometry, 0.0000001) = 'TRUE'

Do note that I did have to break up the second geometry since it was a clob, but other than that it's about as much as I can provide as an example. The RELATE function is returning a correct result that there is an intersection/interaction. However, when the SRID is set to 4326 this query will return that there is no intersection, when clearly there is one.

Sadly, I need to keep the SRID on the parent table as I need to do calculations about the area/width of the polygons that the original table contains. I have also confirmed that the geometries are validated using the built-in oracle functions and there are no errors in the geometry. Any ideas welcome.

Edit 1: Thanks to MTO for figuring out that it's the cutout in the 2nd polygon that is causing the problem. Using the following coordinates for the second geometry will actually cause it to succeed (Note that the coordinates are just the cutout directly).

-60.3390361 55.7800998, -60.2907127 55.7800998, -60.2907127 55.7334333, -60.3390361 55.7334333, -60.3390361 55.7800998
sql oracle geospatial spatial-query
Original Q&A
1

There are 1 best solutions below

3
MT0 On

It appears to be a problem with the data but I cannot quite work out what the exact problem is.

You have a polygon with two arrays of points POLYGON((...), (...)) and it does not work when you have both sets of points. If you delete the second set of points then it does work:

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

Then:

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'

Note: JSON_OBJECT is just used for display purposes.

Outputs:

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]}

fiddle

Related Questions in SQL

Related Questions in ORACLE

Related Questions in GEOSPATIAL

Related Questions in SPATIAL-QUERY

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs

Popular Questions

.

Copyright © 2021 Jogjafile Inc.