SDO_INTERSECTION unable to read return properly

Question

So this is the documentation of SDO_INTERSECTION : https://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_objgeom.htm#SPATL1118

And this is my code:

PROCEDURE ciudadInterseccionCarretera (carretera1 IN VARCHAR2,carretera2 IN 
VARCHAR2) IS
tupla Extremadura%ROWTYPE;
geomResultado SDO_GEOMETRY;
BEGIN

        SELECT SDO_GEOM.SDO_INTERSECTION(ex1.Geom,ex2.Geom,0.05) INTO geomResultado
        FROM Extremadura ex1, Extremadura ex2
        WHERE ex1.Nombre = carretera1 AND ex2.Nombre = carretera2;

        DBMS_OUTPUT.PUT_LINE('ciudad Intersección : '||geomResultado.SDO_GTYPE||' '||geomResultado.SDO_POINT.X);
 END ciudadInterseccionCarretera;

ciudad Intersección : 2001

geomResultado is returning SDO_GTYPE properly when I execute the code, it returns 2001 as the INTERSECTION between carretera1 and carretera2 is a POINT. However I'm not able to return, for example the X coordinate of such POINT using geomResultado.SDO_POINT.X . Any ideas? I think my code is ok..

EDIT: Furthermore, when I try to use this code trying to find the intersection of two lines that doesn't really intersect, the result of the query is empty, so the problem is in SDO_POINT 100%.

Answer 1

Oracle has two ways of storing a point geometry

The first is in the sdo_point part of the sdo_geometry.

sdo_geometry(2001,null,sdo_point(x,y,null),null,null)

The other is as ordinates in the ordinate array:

sdo_geometry(2001,null,null,sdo_elem_info_array(1,1,1),sdo_ordinate_array(x,y))

In this case sdo_intersection returns a geometry with coordinates in the ordinate array. You have to get the values from there.

Example:

declare
   l_geo1 sdo_geometry := sdo_geometry(2001
                                      ,null
                                      ,null
                                      ,sdo_elem_info_array(1, 2, 1)
                                      ,sdo_ordinate_array(0, 0, 10, 10));
   l_geo2 sdo_geometry := sdo_geometry(2001
                                      ,null
                                      ,null
                                      ,sdo_elem_info_array(1, 2, 1)
                                      ,sdo_ordinate_array(0, 10, 10, 0));

   l_geo3 sdo_geometry;

   l_x number;
   l_y number;
begin

   l_geo3 := sdo_geom.sdo_intersection(l_geo1, l_geo2, 0.05);

   l_x := l_geo3.sdo_ordinates(1);
   l_y := l_geo3.sdo_ordinates(2);

   dbms_output.put_line(l_x || ' ' || l_y);
end;