Příkazy k vytvoření cola markets
Převzato a poskládáno ze stránek fy Oracle:
Stránka k prostorovým operacím: [1]
CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY, name VARCHAR2(32), shape SDO_GEOMETRY);
INSERT INTO cola_markets VALUES(
1, 'cola_a', SDO_GEOMETRY( 2003, -- two-dimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior) SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to )
);
INSERT INTO cola_markets VALUES(
2, 'cola_b', SDO_GEOMETRY( 2003, -- two-dimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring) SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1) )
);
INSERT INTO cola_markets VALUES(
3, 'cola_c', SDO_GEOMETRY( 2003, -- two-dimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring) SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3) )
);
INSERT INTO cola_markets VALUES(
4, 'cola_d', SDO_GEOMETRY( 2003, -- two-dimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), -- one circle SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11) )
);
Vyvoření gridu před vytvořením spatial indexu.
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( 'cola_markets', 'shape', SDO_DIM_ARRAY( -- 20X20 grid SDO_DIM_ELEMENT('X', 0, 20, 0.005), SDO_DIM_ELEMENT('Y', 0, 20, 0.005) ), NULL -- SRID
);
describe user_sdo_geom_metadata;
CREATE INDEX cola_spatial_idx
ON cola_markets(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
Operace nad prostorovými daty:
SELECT SDO_GEOM.SDO_INTERSECTION(c_a.shape, c_c.shape, 0.005)
FROM cola_markets c_a, cola_markets c_c WHERE c_a.name = 'cola_a' AND c_c.name = 'cola_b';
SELECT SDO_GEOM.RELATE(c_b.shape, 'anyinteract', c_d.shape, 0.005)
FROM cola_markets c_b, cola_markets c_d WHERE c_b.name = 'cola_a' AND c_d.name = 'cola_c';
SELECT name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM cola_markets;
SELECT c.name, SDO_GEOM.SDO_AREA(c.shape, 0.005) FROM cola_markets c
WHERE c.name = 'cola_a';
SELECT SDO_GEOM.SDO_DISTANCE(c_b.shape, c_d.shape, 0.005)
FROM cola_markets c_b, cola_markets c_d WHERE c_b.name = 'cola_b' AND c_d.name = 'cola_c';
SELECT c.name, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(c.shape, 0.005)
FROM cola_markets c WHERE c.name = 'cola_c';
Nová tabulka pro uchování výsledků:
CREATE TABLE val_results (sdo_rowid ROWID, result VARCHAR2(2000));
CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('COLA_MARKETS', 'SHAPE',
'VAL_RESULTS', 2);
SELECT * from val_results;
Použití metod objektu SDO_GEOMETRY:
SELECT c.shape.Get_WKT()
FROM cola_markets c WHERE c.name = 'cola_b';
SELECT c.shape.Get_Dims()
FROM cola_markets c WHERE c.name = 'cola_b';
SELECT c.shape.Get_GType()
FROM cola_markets c WHERE c.name = 'cola_b';
SELECT c.mkt_id, c.name
FROM cola_markets c WHERE SDO_ANYINTERACT(c.shape, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(4,6, 8,8)) ) = 'TRUE';
select SDO_GEOM.SDO_DISTANCE(sdo_point(1,1,1), sdo_point(1,1,1), 0.005) from cola_markets;
select SDO_GEOMETRY(
2003, -- two-dimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior) SDO_ORDINATE_ARRAY(1,1, 5,7) ) from cola_markets;
select 'karel' from cola_markets;
select unique sdo_geom.sdo_distance(SDO_GEOMETRY(
2001, NULL, SDO_POINT_TYPE(10, 20, NULL), NULL, NULL), SDO_GEOMETRY( 2001, NULL, SDO_POINT_TYPE(0, 0, NULL), NULL, NULL), 0.5) from cola_markets;
INSERT INTO cola_markets VALUES(
90, 'point_only', SDO_GEOMETRY( 2001, NULL, SDO_POINT_TYPE(12, 14, NULL), NULL, NULL));
INSERT INTO cola_markets VALUES(
91, 'point_only', SDO_GEOMETRY( 2001, NULL, SDO_POINT_TYPE(4, 4, NULL), NULL, NULL));
INSERT INTO cola_markets VALUES(
92, 'point_only', SDO_GEOMETRY( 2001, NULL, SDO_POINT_TYPE(12, 12, NULL), NULL, NULL));
INSERT INTO cola_markets VALUES(
10, 'polygon_with_hole', SDO_GEOMETRY( 2003, -- two-dimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1), -- polygon with hole SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4, 7,5, 7,10, 10,10, 10,5, 7,5) )
);