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
CREATE TABLE geometry_table(
geometry_column SDO_GEOMETRY
);
Snowflake
CREATE OR REPLACE TABLE geometry_table (
geometry_column GEOMETRY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Inserting data in SDO_GEOMETRY Table
Oracle
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
INSERT INTO geometry_table
VALUES (
SDO_GEOMETRY('POINT(-79 37)') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_GEOMETRY' NODE ***/!!!
);
INSERT INTO geometry_table
VALUES (
SDO_GEOMETRY('LINESTRING(1 3, 1 5, 2 7)') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_GEOMETRY' NODE ***/!!!
);
INSERT INTO geometry_table
VALUES (
MDSYS.SDO_GEOMETRY(
2001,
8307,
MDSYS.SDO_POINT_TYPE (
-86.13631,
40.485424,
NULL) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'MDSYS.SDO_POINT_TYPE' NODE ***/!!!,
NULL,
NULL
) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'MDSYS.SDO_GEOMETRY' NODE ***/!!!
);
INSERT INTO geometry_table
VALUES (
SDO_GEOMETRY(
2003,
12,
SDO_POINT_TYPE(12, 14, -5) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_POINT_TYPE' NODE ***/!!!,
SDO_ELEM_INFO_ARRAY(1,1003,3) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_ELEM_INFO_ARRAY' NODE ***/!!!,
SDO_ORDINATE_ARRAY(1,1, 5,7) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_ORDINATE_ARRAY' NODE ***/!!!
) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_GEOMETRY' NODE ***/!!!
);
INSERT INTO geometry_table
VALUES (
NULL);
Migration using the GEOGRAPHY data type
Oracle
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;
Snowflake
CREATE OR REPLACE TABLE geometry_table (
geometry_column GEOMETRY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO geometry_table
VALUES (
SDO_GEOMETRY('POINT(-79 37)') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_GEOMETRY' NODE ***/!!!
);
INSERT INTO geometry_table
VALUES (
SDO_GEOMETRY('LINESTRING(1 3, 1 5, 2 7)') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_GEOMETRY' NODE ***/!!!
);
/*
--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;
Related EWIs
SSC-EWI-0073: Pending Functional Equivalence Review.
Last updated