Top 5 Recent Articles

ARTICLES CATEGORIES

Convert Single Geometry to Multi-part Geometry in Oracle Spatial

Oracle allows single part and multipart geometry objects to existing in a single SDO_GEOMETRY column.

For example, a polygon and a multipolygon.

This is because the SDO_GEOMETRY object type is singly inherited. (This also means that a single column can also hold geometries from different geometry types eg points, lines and polygons: common in CAD world, not in GIS.)

If it was not, and adhered strictly to the OGC Geometry Type Hierarchy, this would not be the case.

While having mixed objects from a same geometry type in a column causes Oracle no issues, it may cause issues for other software.

For example, when adding an Oracle table/geometry as a layer in qGIS, qGIS represents the data according to its base type. The following image shows this in the add layer dialog box.

 CREATE FUNCTION st_multi(p_single_geometry IN mdsys.sdo_geometry)
 RETURN mdsys.sdo_geometry deterministic
 AS
 BEGIN
   IF ( p_single_geometry IS NULL OR p_single_geometry.get_gtype() IN (4,5,6,7) ) THEN
      RETURN p_single_geometry;
   END IF;
   RETURN sdo_geometry(p_single_geometry.sdo_gtype+4,
                       p_single_geometry.sdo_srid,
                       NULL,
                       CASE WHEN p_single_geometry.get_gtype()=1 AND p_single_geometry.sdo_point IS NOT NULL
                            THEN sdo_elem_info_array(1,1,1)
                            ELSE p_single_geometry.sdo_elem_info
                        END,
                       CASE WHEN p_single_geometry.get_gtype()=1 AND p_single_geometry.sdo_point IS NOT NULL
                            THEN CASE WHEN p_single_geometry.get_dims()=2
                                      THEN sdo_ordinate_array(p_single_geometry.sdo_point.x,p_single_geometry.sdo_point.y)
                                      ELSE sdo_ordinate_array(p_single_geometry.sdo_point.x,p_single_geometry.sdo_point.y,p_single_geometry.sdo_point.z)
                                 END
                            ELSE p_single_geometry.sdo_ordinates
                        END);
 END ST_Multi;
 /

Testing this gives us:

 SELECT name,
        sdo_geom.validate_geometry(mgeometry,0.0005) AS vmGeom,
        geometry,
        mgeometry
   FROM (SELECT name,
                geometry,
                ST_Multi(geometry) AS mGeometry
           FROM oracle_test_geometries a
         WHERE name NOT LIKE '13%'
           AND geometry IS NOT NULL
           AND a.geometry.get_gtype() IN (1,2,3)
       );
 NAME                              VMGEOM GEOMETRY
                                          MGEOMETRY
 ---------------------------------- ----- ---------------------------------------------------------------------------------------------------------------------------------------------------
 Single Point (SDO_POINT encoding) TRUE   SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(900,900,NULL),NULL,NULL)
                                          SDO_GEOMETRY(2005,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(900,900))
 Single Point (Ordinate Encoding)  TRUE   SDO_GEOMETRY(2001,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(10,5))
                                          SDO_GEOMETRY(2005,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(10,5))
 Line segment                      TRUE   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10,10,20,10))
                                          SDO_GEOMETRY(2006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10,10,20,10))
 Arc segment                       TRUE   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(10,15,15,20,20,15))
                                          SDO_GEOMETRY(2006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(10,15,15,20,20,15))
 Line string                       TRUE   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10,25,20,30,25,25,30,30))
                                          SDO_GEOMETRY(2006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10,25,20,30,25,25,30,30))
 Arc string                        TRUE   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(10,35,15,40,20,35,25,30,30,35))
                                          SDO_GEOMETRY(2006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(10,35,15,40,20,35,25,30,30,35))
 Compound line string              TRUE   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,4,3,1,2,1,3,2,2,7,2,1),SDO_ORDINATE_ARRAY(10,45,20,45,23,48,20,51,10,51))
                                          SDO_GEOMETRY(2006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,4,3,1,2,1,3,2,2,7,2,1),SDO_ORDINATE_ARRAY(10,45,20,45,23,48,20,51,10,51))
 Closed line string                TRUE   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10,55,15,55,20,60,10,60,10,55))
                                          SDO_GEOMETRY(2006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10,55,15,55,20,60,10,60,10,55))
 Closed arc string                 TRUE   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(15,65,10,68,15,70,20,68,15,65))
                                          SDO_GEOMETRY(2006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(15,65,10,68,15,70,20,68,15,65))
 Closed mixed line                 TRUE   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,4,2,1,2,1,7,2,2),SDO_ORDINATE_ARRAY(10,78,10,75,20,75,20,78,15,80,10,78))
                                          SDO_GEOMETRY(2006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,4,2,1,2,1,7,2,2),SDO_ORDINATE_ARRAY(10,78,10,75,20,75,20,78,15,80,10,78))
 Self-crossing line                TRUE   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10,85,20,90,20,85,10,90,10,85))
                                          SDO_GEOMETRY(2006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(10,85,20,90,20,85,10,90,10,85))
 Polygon                           TRUE   SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(10,105,15,105,20,110,10,110,10,105))
                                          SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(10,105,15,105,20,110,10,110,10,105))
 Polygon WITH void                 TRUE   SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3,5,2003,3),SDO_ORDINATE_ARRAY(50,135,60,140,51,136,59,139))
                                          SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3,5,2003,3),SDO_ORDINATE_ARRAY(50,135,60,140,51,136,59,139))
 Arc polygon                       TRUE   SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,2),SDO_ORDINATE_ARRAY(15,115,20,118,15,120,10,118,15,115))
                                          SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,2),SDO_ORDINATE_ARRAY(15,115,20,118,15,120,10,118,15,115))
 Compound polygon                  TRUE   SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,1,7,2,2),SDO_ORDINATE_ARRAY(10,128,10,125,20,125,20,128,15,130,10,128))
                                          SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,1,7,2,2),SDO_ORDINATE_ARRAY(10,128,10,125,20,125,20,128,15,130,10,128))
 Rectangle                         TRUE   SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(10,135,20,140))
                                          SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(10,135,20,140))
 Circle                            TRUE   SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,4),SDO_ORDINATE_ARRAY(15,145,10,150,20,150))
                                          SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,4),SDO_ORDINATE_ARRAY(15,145,10,150,20,150))
 Line                              TRUE   SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(100,100,900,900))
                                          SDO_GEOMETRY(2006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(100,100,900,900))
 Polygon no holes                  TRUE   SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(100,100,500,500))
                                          SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(100,100,500,500))
 Polygon WITH point AND a hole     TRUE   SDO_GEOMETRY(2003,NULL,SDO_POINT_TYPE(1000,1000,NULL),SDO_ELEM_INFO_ARRAY(1,1003,3,5,2003,3),SDO_ORDINATE_ARRAY(500,500,1500,1500,600,750,900,1050))
                                          SDO_GEOMETRY(2007,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3,5,2003,3),SDO_ORDINATE_ARRAY(500,500,1500,1500,600,750,900,1050))
 3D line                           TRUE   SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(442.062,799.423,63.157,450.611,802.963,63.157))
                                          SDO_GEOMETRY(3006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(442.062,799.423,63.157,450.611,802.963,63.157))
 2D Line WITH Measure              TRUE   SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(442.062,799.423,63.157,450.611,802.963,63.157))
                                          SDO_GEOMETRY(3306,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(442.062,799.423,63.157,450.611,802.963,63.157))
 4D line WITH Measure IN 4         TRUE   SDO_GEOMETRY(4002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(442.062,802.963,63.157,0,450.611,799.423,63.157,1))
                                          SDO_GEOMETRY(4006,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(442.062,802.963,63.157,0,450.611,799.423,63.157,1))
  35 ROWS selected

Applying this fix to the layer being added to qGIS results in this:

I hope this is of help to someone out there.