Top 5 Recent Articles

ARTICLES CATEGORIES

Convert Single Point stored in SDO_ORDINATES to SDO_POINT_TYPE

Oracle Spatial offers two ways of storing a single point:

  • In SDO_POINT_TYPE structure
  • In SDO_ORDINATE_ARRAY

Examples include:

  • SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(12, 14, NULL),NULL,NULL))
  • SDO_GEOMETRY(2001,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(12,14))

Oracle recommends the storage of single points using the SDO_POINT_TYPE structure. There are, of course, a number of situations where this is not possible or recommended:

  • Storage using the SDO_POINT_TYPE structure is impossible if the point had more than 3 ordinates.
  • If the geometry type of a particular SDO_GEOMETRY column in a table is MULTIPOINT and a particular observed value is a single point, multipoint.

How is it that SDO_ORDINATE_ARRAY based single points arise? Variously. The most common is that external loading software does so using a wrong parameter (some loader have switches for loading into SDO_POINT_TYPE rather than SDO_ORDINATE_ARRAY but the user forgets to set the right switch). Others (I’m guessing here) include ignorance; perhaps perceptions that a homogeneous approach to storing all geometry ordinates using the one method somehow brings benefits in terms of programming, support etc.

Conversion from SDO_ORDINATE_ARRAY to SDO_POINT_TYPE can occur in two ways:

  • Straight SQL
  • PL/SQL function.

Straight SQL

This is an easy thing to do as the following shows.

 WITH cTable AS (
   SELECT SDO_GEOMETRY(2001,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(12,14)) AS oGeom
     FROM DUAL
 )
 SELECT SDO_GEOMETRY(2001,a.oGeom.sdo_srid,SDO_POINT_TYPE(t.x,t.y,NULL),NULL,NULL) AS point
   FROM cTable a,
        TABLE(Sdo_Util.GetVertices(a.oGeom)) t
  WHERE t.id = 1;
 -- Results
 --
 POINT
 ------------------------------------------------------------
 SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(12,14,NULL),NULL,NULL)

Now if we had more than one point in the sdo_ordinate_array we can process them all by dropping off the t.id = 1 predicate as follows:

 WITH cTable AS (
   SELECT SDO_GEOMETRY(2001,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,3),SDO_ORDINATE_ARRAY(12,14,120,140,1200,1400)) AS oGeom
     FROM DUAL
 )
 SELECT SDO_GEOMETRY(2001,a.oGeom.sdo_srid,SDO_POINT_TYPE(t.x,t.y,NULL),NULL,NULL) AS point
   FROM cTable a,
        TABLE(Sdo_Util.GetVertices(a.oGeom)) t;
 -- Results
 --
 POINT
 ----------------------------------------------------------------------
 SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(12,14,NULL),NULL,NULL)
 SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(120,140,NULL),NULL,NULL)
 SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1200,1400,NULL),NULL,NULL)

Now let’s look at the PL/SQL approach.

PL/SQL function.

There is a function in my PL/SQL GEOM package called ToSdoPoint that is as follows:

   FUNCTION ToSdoPoint( p_geometry IN MDSYS.SDO_GEOMETRY )
     RETURN MDSYS.SDO_GEOMETRY
     IS
      v_element         NUMBER;
      v_elements        NUMBER;
      v_geometry        MDSYS.SDO_Geometry;
      v_SdoPoint        MDSYS.SDO_Point_Type := MDSYS.SDO_Point_Type(0,0,NULL);
      v_Ordinates       MDSYS.SDO_Ordinate_Array;
   BEGIN
     IF ( MOD(p_geometry.sdo_gtype,10) NOT IN (1,5) ) THEN
       v_geometry := NULL;
     ELSIF p_geometry.sdo_point IS NOT NULL THEN
       v_geometry := mdsys.sdo_geometry(p_geometry.sdo_gtype,p_geometry.sdo_srid,p_geometry.sdo_point,NULL,NULL);
     ELSE
       v_ordinates  := p_geometry.sdo_ordinates;
       v_SdoPoint.X := v_ordinates(1);
       v_SdoPoint.Y := v_ordinates(2);
       IF ( FLOOR(p_geometry.sdo_gtype/1000) = 3 ) THEN
         v_SdoPoint.Z := v_ordinates(3);
       END IF;
       v_geometry := mdsys.sdo_geometry(p_geometry.sdo_gtype,p_geometry.sdo_srid,v_SdoPoint,NULL,NULL);
     END IF;
     RETURN v_geometry;
   END ToSdoPoint;

This can be used as follows.

 WITH cTable AS (
   SELECT SDO_GEOMETRY(2001,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(12,14)) AS oGeom
     FROM DUAL
 )
 SELECT GEOM.ToSdoPoint(a.oGeom) AS point
   FROM cTable a;
 -- Results
 --
 POINT
 ------------------------------------------------------------
 SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(12,14,NULL),NULL,NULL)

I hope this is of use to someone.