SDO_GEOMETRY

Description

The geometric description of a spatial object is stored in a single row, in a single column of object type SDO_GEOMETRY in a user-defined table. Any table that has a column of type SDO_GEOMETRY must have another column, or set of columns, that defines a unique primary key for that table. (Oracle SQL Language Reference SDO_GEOMETRY Data Type)

Definition of SDO_GEOMETRY object:

CREATE TYPE SDO_GEOMETRY AS OBJECT
  (sgo_gtype        NUMBER, 
   sdo_srid         NUMBER,
   sdo_point        SDO_POINT_TYPE,
   sdo_elem_info    SDO_ELEM_INFO_ARRAY,
   sdo_ordinates    SDO_ORDINATE_ARRAY);
/

The SDO_GEOMETRY object is not supported in Snowflake. A workaround for this data type is to use Snowflake GEOGRAPHY, however that transformation is currently not supported by SnowConvert.

Sample Source Patterns

SDO_GEOMETRY in Create Table

Oracle

Query

CREATE TABLE geometry_table(
    geometry_column SDO_GEOMETRY
);

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.geometry_table (
geometry_column GEOMETRY
                            );

Inserting data in SDO_GEOMETRY Table

Oracle

Query

INSERT INTO geometry_table VALUES (
	SDO_GEOMETRY('POINT(-79 37)')
);

INSERT INTO geometry_table VALUES (
    SDO_GEOMETRY('LINESTRING(1 3, 1 5, 2 7)')
);

INSERT INTO geometry_table VALUES (
    MDSYS.SDO_GEOMETRY(
		2001,
		8307,
		MDSYS.SDO_POINT_TYPE (
			-86.13631,
			40.485424,
			NULL),
		NULL,
		NULL
	)
);

INSERT  INTO geometry_table VALUES (
SDO_GEOMETRY(
    2003,
    12,
    SDO_POINT_TYPE(12, 14, -5),
    SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(1,1, 5,7) 
  )
);

INSERT INTO geometry_table VALUES (
NULL);

Snowflake

Query

INSERT INTO PUBLIC.geometry_table VALUES (/*** MSC-WARNING - MSCEWI1049 - MDSYS.SDO_GEOMETRY FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.MDSYS.SDO_GEOMETRY_UDF('MDSYS.SDO_GEOMETRY(		2001,		8307, /*** MSC-WARNING - MSCEWI1049 - MDSYS.SDO_POINT_TYPE FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/ PUBLIC.MDSYS.SDO_POINT_TYPE_UDF(\'MDSYS.SDO_POINT_TYPE (			-86.13631,			40.485424,			NULL)\'),		NULL,		NULL	)')
);

INSERT  INTO PUBLIC.geometry_table VALUES (/*** MSC-WARNING - MSCEWI1049 - SDO_GEOMETRY FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.SDO_GEOMETRY_UDF('SDO_GEOMETRY(    2003,    12, /*** MSC-WARNING - MSCEWI1049 - SDO_POINT_TYPE FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/ PUBLIC.SDO_POINT_TYPE_UDF(\'SDO_POINT_TYPE(12, 14, -5)\'), /*** MSC-WARNING - MSCEWI1049 - SDO_ELEM_INFO_ARRAY FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/ PUBLIC.SDO_ELEM_INFO_ARRAY_UDF(\'SDO_ELEM_INFO_ARRAY(1,1003,3)\'), /*** MSC-WARNING - MSCEWI1049 - SDO_ORDINATE_ARRAY FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/ PUBLIC.SDO_ORDINATE_ARRAY_UDF(\'SDO_ORDINATE_ARRAY(1,1, 5,7)\')  )')
);

NSERT INTO PUBLIC.geometry_table VALUES (/*** MSC-WARNING - MSCEWI1049 - SDO_GEOMETRY FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.SDO_GEOMETRY_UDF('SDO_GEOMETRY(\'POINT(-79 37)\')')
);

INSERT INTO PUBLIC.geometry_table VALUES (/*** MSC-WARNING - MSCEWI1049 - SDO_GEOMETRY FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
PUBLIC.SDO_GEOMETRY_UDF('SDO_GEOMETRY(\'LINESTRING(1 3, 1 5, 2 7)\')')
);

INSERT INTO PUBLIC.geometry_table VALUES (
NULL);

Migration using the GEOGRAPHY data type

Oracle

Query

CREATE TABLE geometry_table(
    geometry_column SDO_GEOMETRY
);

INSERT INTO geometry_table VALUES (
	SDO_GEOMETRY('POINT(-79 37)')
);

INSERT INTO geometry_table VALUES (
    SDO_GEOMETRY('LINESTRING(1 3, 1 5, 2 7)')
);

/*
--NOT SUPPORTED BY SNOWFLAKE GEOGRAPHY
INSERT INTO geometry_table VALUES (
    MDSYS.SDO_GEOMETRY(
		2001,
		8307,
		MDSYS.SDO_POINT_TYPE (
			-86.13631,
			40.485424,
			NULL),
		NULL,
		NULL
	)
);
INSERT  INTO geometry_table VALUES (
SDO_GEOMETRY(
    2003,
    12,
    SDO_POINT_TYPE(12, 14, -5),
    SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(1,1, 5,7) 
  )
);
*/

SELECT * FROM geometry_table;

Result

GEOMETRY_COLUMN                                         |
--------------------------------------------------------+
[2001, null, [-79, 37, null], [NULL], [NULL]]           |--(POINT(-79,37))
[2002, null, [null, null, null], [1,2,1], [1,3,1,5,2,7]]|--(LINESTRING(1 3, 1 5, 2 7))

Snowflake

Query

CREATE TABLE geometry_table(
    geometry_column GEOMETRY
);

INSERT INTO geometry_table VALUES (
    ('POINT(-79 37)')
);

INSERT INTO geometry_table VALUES (
    ('LINESTRING(1 3, 1 5, 2 7)')
);

ALTER SESSION SET geography_output_format='WKT';
SELECT * FROM geometry_table;

Result

GEOMETRY_COLUMN        |
-----------------------+
POINT(-79 37)          |
LINESTRING(1 3,1 5,2 7)|

Known Issues

1. SDO_GEOMETRY Data Type not transformed

SDO_GEOMETRY Data Type is not being transformed by SnowConvert.

Related EWIs

1. MSCEWI1028: Type is not supported.

2. MSCEWI1049: Not recognized function