-- just one set of titles
set pagesize 10000;

-- drop table
DROP TABLE SDO_TEST;

-- check srid - should return Irish Transverse Mercator
select * from MDSYS.CS_SRS where srid = 82086;

-- create test table
CREATE TABLE SDO_TEST (ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), GEOM SDO_GEOMETRY);

-- create an index in the metadata table
INSERT INTO USER_SDO_GEOM_METADATA
    (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
    VALUES ('SDO_TEST','GEOM',
           SDO_DIM_ARRAY(
               SDO_DIM_ELEMENT('X', -8048350, 8448349.95, .005), 
               SDO_DIM_ELEMENT('Y', -15680949, 4321303.19, .005)
           ),
           82086);
           
-- create index for table
CREATE INDEX SDO_TEST_IDX ON SDO_TEST(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

-- CREATE TYPE sdo_geometry AS OBJECT (
--  SDO_GTYPE NUMBER, 
--  SDO_SRID NUMBER,
--  SDO_POINT SDO_POINT_TYPE,
--  SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
--  SDO_ORDINATES SDO_ORDINATE_ARRAY);

-- CREATE TYPE sdo_point_type AS OBJECT (
--    X NUMBER,
--    Y NUMBER,
--    Z NUMBER);
-- CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;
-- CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;




-- add data
-- tried SDO_GEOMETRY(wkb BLOB, srid NUMBER DEFAULT NULL); - didn't work
-- point
INSERT INTO SDO_TEST VALUES (
    1, 'point', SDO_GEOMETRY(
        -- WKT
        -- 'POINT(200000 200000)', 82086
        -- BLOB: SDO_GTYPE, SDO_SRID, SDO_POINT_TYPE, SDO_ELEM_INFO, SDO_ORDINATES
        2001, 82086, SDO_POINT_TYPE(200000, 200000, NULL), NULL, NULL
    )
);

-- line segment
INSERT INTO SDO_TEST VALUES (
    2, 'linestring', SDO_GEOMETRY(
        -- WKT
        -- 'LINESTRING(200000 200000, 220000 200000, 220000 220000)', 82086
        -- BLOB: SDO_GTYPE, SDO_SRID, SDO_POINT_TYPE, SDO_ELEM_INFO, SDO_ORDINATE
        2002, 82086, SDO_POINT_TYPE(NULL,NULL,NULL),  
        SDO_ELEM_INFO_ARRAY(1,2,1),
        SDO_ORDINATE_ARRAY(200000, 200000, 220000, 200000, 220000, 220000)
    )
);

-- polygon
INSERT INTO SDO_TEST VALUES (
    3, 'polygon', SDO_GEOMETRY(
        -- WKT
        -- 'POLYGON((200000 200000, 220000 200000, 220000 220000, 200000 220000, 200000 200000))', 82086
        -- BLOB: SDO_GTYPE, SDO_SRID, SDO_POINT_TYPE, SDO_ELEM_INFO, SDO_ORDINATE
        2003, 82086, SDO_POINT_TYPE(NULL,NULL,NULL),  
        SDO_ELEM_INFO_ARRAY(1,1003,1),
        SDO_ORDINATE_ARRAY(200000, 200000, 220000, 200000, 220000, 220000, 200000, 220000, 200000, 200000)
    )
);

-- polygon with inner ring
INSERT INTO SDO_TEST VALUES (
    4, 'polygon with inner ring', SDO_GEOMETRY(
        -- WKT
        -- 'POLYGON((200000 200000, 220000 200000, 220000 220000, 200000 220000, 200000 200000)
        --          (202000 202000, 208000 208000, 208000 202000, 202000 202000))', 82086
        -- BLOB: SDO_GTYPE, SDO_SRID, SDO_POINT_TYPE, SDO_ELEM_INFO, SDO_ORDINATE
        2003, 82086, SDO_POINT_TYPE(NULL,NULL,NULL),
        SDO_ELEM_INFO_ARRAY(1,1003,1, 11,2003,1),
        SDO_ORDINATE_ARRAY(200000, 200000, 220000, 200000, 220000, 220000, 200000, 220000, 200000, 200000, 202000, 202000, 208000, 208000, 208000, 202000, 202000, 202000)
    )
);


-- multi-point
INSERT INTO SDO_TEST VALUES (
    5, 'multi point', SDO_GEOMETRY(
        -- WKT
        -- 'MULTIPOINT(200000 200000, 220000 200000, 220000 220000, 200000 220000, 200000 200000)', 82086
        -- BLOB: SDO_GTYPE, SDO_SRID, SDO_POINT_TYPE, SDO_ELEM_INFO, SDO_ORDINATE
        2005, 82086, SDO_POINT_TYPE(NULL,NULL,NULL),
        SDO_ELEM_INFO_ARRAY(1,1,1, 3,1,1, 5,1,1, 7,1,1),
        -- SDO_ELEM_INFO_ARRAY(1,1,4),
        SDO_ORDINATE_ARRAY(200000, 200000, 220000, 200000, 220000, 220000, 200000, 220000)
    )
);

-- multi-line
INSERT INTO SDO_TEST VALUES (
    6, 'multi line', SDO_GEOMETRY(
        -- WKT
        -- 'MULTILINESTRING((200000 200000, 220000 200000, 220000 220000, 200000 220000)
        --          (202000 202000, 208000 208000, 208000 202000))', 82086
        -- BLOB: SDO_GTYPE, SDO_SRID, SDO_POINT_TYPE, SDO_ELEM_INFO, SDO_ORDINATE
        2006, 82086, SDO_POINT_TYPE(NULL,NULL,NULL),
        SDO_ELEM_INFO_ARRAY(1,2,1, 9,2,1),
        SDO_ORDINATE_ARRAY(200000, 200000, 220000, 200000, 220000, 220000, 200000, 220000, 202000, 202000, 208000, 208000, 208000, 202000)
    )
);

-- multi-polygon
INSERT INTO SDO_TEST VALUES (
    7, 'multi polygon with 1 ring', SDO_GEOMETRY(
        -- WKT
        -- 'MULTIPOLYGON(((200000 200000, 220000 200000, 220000 220000, 200000 220000, 200000 200000)))', 82086
        -- BLOB: SDO_GTYPE, SDO_SRID, SDO_POINT_TYPE, SDO_ELEM_INFO, SDO_ORDINATE
        2007, 82086, SDO_POINT_TYPE(NULL,NULL,NULL), 
        SDO_ELEM_INFO_ARRAY(1,1003,1),
        SDO_ORDINATE_ARRAY(200000, 200000, 220000, 200000, 220000, 220000, 200000, 220000, 200000, 200000)
    )
);

-- geometry collection
INSERT INTO SDO_TEST VALUES (
    8, 'geometry collection - polygon and point', SDO_GEOMETRY(
        -- WKT
        -- 'GEOMETRYCOLLECTION(POLYGON(((200000 200000, 220000 200000, 220000 220000, 200000 220000, 200000 200000)),
        ---     LINESTRING(200000 200000, 220000 200000, 220000 220000),POINT(210000 210000))', 82086
        -- BLOB: SDO_GTYPE, SDO_SRID, SDO_POINT_TYPE, SDO_ELEM_INFO, SDO_ORDINATE
        2004, 82086, SDO_POINT_TYPE(NULL,NULL,NULL),
        SDO_ELEM_INFO_ARRAY(1,1003,1, 11,2,1, 17,1,1),
        SDO_ORDINATE_ARRAY(200000, 200000, 220000, 200000, 220000, 220000, 200000, 220000, 200000, 200000, 200000, 200000, 220000, 200000, 220000, 220000, 210000, 210000)
    )
);

-- rectangle
INSERT INTO SDO_TEST VALUES (
    9, 'rectangle - 2 points', SDO_GEOMETRY(
        -- WKT
        -- 'POLYGON((200000 200000, 220000 200000))', 82086
        -- BLOB: SDO_GTYPE, SDO_SRID, SDO_POINT_TYPE, SDO_ELEM_INFO, SDO_ORDINATE
        2003, 82086, SDO_POINT_TYPE(NULL,NULL,NULL),  
        SDO_ELEM_INFO_ARRAY(1,1003,3),
        SDO_ORDINATE_ARRAY(200000, 200000, 220000, 200000)
    )
);

-- circle
INSERT INTO SDO_TEST VALUES (
    10, 'circle - 3 points', SDO_GEOMETRY(
        -- WKT
        -- 'POLYGON((200000 200000, 220000 200000, 220000 220000))', 82086
        -- BLOB: SDO_GTYPE, SDO_SRID, SDO_POINT_TYPE, SDO_ELEM_INFO, SDO_ORDINATE
        2003, 82086, SDO_POINT_TYPE(NULL,NULL,NULL),  
        SDO_ELEM_INFO_ARRAY(1,1003,4),
        SDO_ORDINATE_ARRAY(200000, 200000, 220000, 200000, 220000, 220000)
    )
);