Extracting Coordinates from Oracle Spatial Object Table using FME?

Question

I've searched all available online sources but none appear to have this issue.

I've added an Oracle Spatial Reader, connected to desired table and now want to extract x/y but I get this error when running.

Error - Cannot have NULL value for 'z coordinate'

I've looked at the table in Oracle developer and indeed the Z values are Null.

I'm aware that this table has multiple Spatial columns so I've checked: Handle Multiple Spatial Columns: Yes

I've tried a SELECT statement to run before translation:

SELECT c.GEOM.SDO_POINT.X AS X, c.GEOM.SDO_POINT.Y AS Y, 
FROM A.ENTITIES c 
WHERE ACCESS_CODE = 'O'

I've tried to expose the attributes: oracle_sdo_point.x oracle_sdo_point.y oracle_sdo_point.z

Is there a way to force this to 2D rather than looking at the Z values?

Answer 1

You can convert your 3d geometry to 2d using this function

    create or replace
FUNCTION to_2d (geom MDSYS.SDO_GEOMETRY)      RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC   AS
      geom_2d       MDSYS.SDO_GEOMETRY;
      dim_count     INTEGER;       -- number of dimensions in layer
      gtype         INTEGER;       -- geometry type (single digit)
      n_points      INTEGER;       -- number of points in ordinates array
      n_ordinates   INTEGER;       -- number of ordinates
      i             INTEGER;
      j             INTEGER;
      k             INTEGER;
      offset        INTEGER;
BEGIN

          -- If the input geometry is null, just return null

      IF geom IS NULL

      THEN

         RETURN (NULL);

      END IF;



       -- Get the number of dimensions from the gtype

      IF LENGTH (geom.sdo_gtype) = 4

      THEN

         dim_count := SUBSTR (geom.sdo_gtype, 1, 1);

         gtype := SUBSTR (geom.sdo_gtype, 4, 1);

      ELSE

              -- Indicate failure

         raise_application_error (-20000, 'Unable to determine dimensionality from gtype');

      END IF;



      IF dim_count = 2

      THEN

              -- Nothing to do, geometry is already 2D

         RETURN (geom);

      END IF;



       -- Construct and prepare the output geometry

      geom_2d :=

         MDSYS.SDO_GEOMETRY (2000 + gtype,

                             geom.sdo_srid,

                             geom.sdo_point,

                             MDSYS.sdo_elem_info_array (),

                             MDSYS.sdo_ordinate_array ()

                            );



       -- Process the point structure

      IF geom_2d.sdo_point IS NOT NULL

      THEN

         -- It's a point, that's it...

         geom_2d.sdo_point.z := NULL;

      ELSE

         -- It's not a point



         -- Process the ordinates array



         -- Prepare the size of the output array

         n_points := geom.sdo_ordinates.COUNT / dim_count;

         n_ordinates := n_points * 2;

         geom_2d.sdo_ordinates.EXTEND (n_ordinates);



           -- Copy the ordinates array

         j := geom.sdo_ordinates.FIRST;                        -- index into input elem_info array

         k := 1;                                               -- index into output ordinate array



         FOR i IN 1 .. n_points

         LOOP

            geom_2d.sdo_ordinates (k) := geom.sdo_ordinates (j);                         -- copy X

            geom_2d.sdo_ordinates (k + 1) := geom.sdo_ordinates (j + 1);                 -- copy Y

            j := j + dim_count;

            k := k + 2;

         END LOOP;



           -- Process the element info array



         -- Copy the input array into the output array

         geom_2d.sdo_elem_info := geom.sdo_elem_info;



           -- Adjust the offsets

         i := geom_2d.sdo_elem_info.FIRST;



         WHILE i < geom_2d.sdo_elem_info.LAST

         LOOP

            offset := geom_2d.sdo_elem_info (i);

            geom_2d.sdo_elem_info (i) := (offset - 1) / dim_count * 2 + 1;

            i := i + 3;

         END LOOP;

      END IF;



      RETURN geom_2d;

   END to_2d;

Answer 2

If your data is 3D then all the Z must be filled. If they are all empty then your data is really 2D and you need to correct the geometry types accordingly.

For points you can just replace the type (3001 to 2001).

For other shapes the Z must be removed also. Do that using the { sdo_cs.make_2d() } function.

Oh and make sure to drop the spatial indexes and correct the metadata before updating, and re-create the spatial indexes after updating.

Answer 3

It's not great behaviour from FME. There's a problem report (PR#35869) around this. I've added a link to this conversation and increased the priority. Basically if the Z value is null we would be better off replacing it with a value of zero, instead of stopping the entire translation.

If you need more information then contact the FME support team and mention that PR number.

In our defence, I quote this comment from one of our team:

Basically, the Oracle documentation clearly states that you must not have null z values in your coordinates (including your z coordinates) for your spatial object. However, I was able to create a table with null values for some of its z coordinates and Oracle did not complain. Furthermore, Oracle's geometry validation functions seem to validate a geometry column with null z values in its coordinate arrays. So it seems that in practice, null coordinates are allowed, but in theory they shouldn't be.