Oracle Spatial – How to Check if a Geometry is NULL in Oracle Spatial

oracle-spatial

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

Best Answer

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;
Related Question