0

I'm working on an exercise in Oracle Spatial. I want to check if two geometries overlap. I need to use SDO_GEOM.SDO_INTERSECTION to do so, and that's my main difficulty (I know how to do it with RELATE or DISTANCE). So, I'm calling the function and it's returning a geometry:

 geomIntersection := SDO_GEOM.SDO_INTERSECTION(geom1,dim,geom2,dim);

How can I check if this geometry is NULL? What does the function return if the two geometries don't have intersection?

EDIT: I'm trying this in SQL Developer 18.2.0.183.

The Oracle version is: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Improve this question
4
  • 2
    Oracle doesn't support an empty geometry (at least, it didn't in all the years I used it, which ended with 11gR2), so the result is likely NULL. Have you tried to test the result? Please edit the question to provide more information, including the exact release of Oracle and what your experimentation yielded.
    – Vince
    Dec 9, 2018 at 15:00
  • @Vince how could I test it? That's one of my problems really, I get the geometry but I don't know how to print it to check its state. I'll update the question with the Oracle version, thanks for the help
    – Jaime Alcántara Arnela
    Dec 9, 2018 at 15:58
  • 1
    PL/SQL isn't the easiest way to test this. Just generate simple SQL queries and display the results. Construct lines from 0,0 to 5,5 and 0,5 to 5,0 and query their intersection, then use lines from 0,0 to 0,5 and 5,5 to 5,0.
    – Vince
    Dec 9, 2018 at 16:19
  • @Vince Ok so if just tryied simply adding "if(geomIntersection is not null)" and it worked. I know it sounds so easy but I had read that didn't work so I didn't try. I'll suppose I will post it here, thanks for the help, I appreciate it.
    – Jaime Alcántara Arnela
    Dec 9, 2018 at 18:15

1 Answer 1

Reset to default
1

While Oracle does not have a specific encoding for an empty geometry, it fully understands NULL geometries, and uses that to represent empty geometries. So when you intersect two disjoint geometries, you get a NULL result:

SQL> select sdo_geom.sdo_intersection(c1.geom,c2.geom,0.005) from us_counties c1, us_counties c2 where c1.county='Denver' and c1.state_abrv = 'CO' and c2.county='Kings' and c2.state_abrv='NY';

SDO_GEOM.SDO_INTERSECTION(C1.GEOM,C2.GEOM,0.005)(SDO_GTYPE, SDO_SRID, SDO_POINT
-------------------------------------------------------------------------------
NULL

1 row selected.

Where things get interesting is when intersecting two adjacent polygons. Then the result is a linestring:

SQL> select sdo_geom.sdo_intersection(c1.geom,c2.geom,0.005) from us_counties c1, us_counties c2 where c1.county='Queens' and c1.state_abrv = 'NY' and c2.county='Kings' and c2.state_abrv='NY';

SDO_GEOM.SDO_INTERSECTION(C1.GEOM,C2.GEOM,0.005)(SDO_GTYPE, SDO_SRID, SDO_POINT
-------------------------------------------------------------------------------
SDO_GEOMETRY(2002, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY (-73.962906, 40.736, -73.952904, 40.738899, -73.942398, 40.735298, -73.937798, 40.729401, -73.929504, 40.727402, -73.922997, 40.7159, -73.922104, 40.7089, -73.904907, 40.6954, -73.906403, 40.693901, -73.901802, 40.691399, -73.897102, 40.682301, -73.869705, 40.694801, -73.866707, 40.681599, -73.862907, 40.678799, -73.861, 40.671101, -73.858307, 40.671398, -73.856201, 40.663399, -73.859001, 40.662899, -73.858101, 40.659698, -73.863907, 40.658001, -73.855598, 40.642601, -73.848701, 40.6436, -73.833702, 40.628899, -73.834801, 40.606602, -73.850197, 40.5882, -73.879601, 40.574299, -73.944458, 40.564663))

1 row selected.

If the two shapes touch at just one point, then the result is a point:

SQL> select sdo_geom.sdo_intersection(s1.geom,s2.geom,0.005) from us_states s1, us_states s2 where s1.state_abrv = 'CO' and s2.state_abrv='AZ';

SDO_GEOM.SDO_INTERSECTION(S1.GEOM,S2.GEOM,0.005)(SDO_GTYPE, SDO_SRID, SDO_POINT
-------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(-109.04457, 36.999088))

1 row selected.

Finally any operation that includes a NULL geometry returns NULL.

SQL> select sdo_geom.sdo_intersection(geom,null,0.005) from us_states where state_abrv='CO';

SDO_GEOM.SDO_INTERSECTION(GEOM,NULL,0.005)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y,
-------------------------------------------------------------------------------
NULL

1 row selected.

Measuring the area of the result of intersecting two disjoint polygons returns null:

SQL> select sdo_geom.sdo_area(sdo_geom.sdo_intersection(c1.geom,c2.geom,0.005),0.005) from us_counties c1, us_counties c2 where c1.county='Denver' and c1.state_abrv = 'CO' and c2.county='Kings' and c2.state_abrv='NY';

SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(C1.GEOM,C2.GEOM,0.005),0.005)
-------------------------------------------------------------------------
NULL

1 row selected.

If you use any not this in PL/SQL, i.e. assign the result to a variable, then you just check this variable for a NULL result

inter := sdo_geom.sdo_intersection(geom_1,geom_2,0.005);
if inter is not null then
  ...
end if;
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.