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.