Difference between revisions of "Příkazy k vytvoření cola markets"

Revision as of 11:25, 25 April 2011

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 - vnější polygon, s body definovanými proti směru hodinových ručiček)
   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)
 )

); Vytvoření gridu před vytvořením spatial indexu:

describe user_sdo_geom_metadata; {zjištění struktury tabulky uchovávající metadata}

INSERT INTO user_sdo_geom_metadata {vložení metainformací o tabulce cola_markets, pro kterou bude vytvořen prostorový index nad sloupcem "shape"}

   (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

);

CREATE INDEX cola_spatial_idx {vytvoření prostorového indexu}

  ON cola_markets(shape)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX;

select 'karel' {pomocný kontrolní dotaz}

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)
 )

);

Zpět na stránku PDB

@@ Line 55: / Line 55: @@
    )
 );
+'''
+Vytvoření gridu před vytvořením spatial indexu''':
-Vyvoření gridu před vytvořením spatial indexu.
+describe user_sdo_geom_metadata; {zjištění struktury tabulky uchovávající metadata}
-INSERT INTO user_sdo_geom_metadata
+INSERT INTO user_sdo_geom_metadata {vložení metainformací o tabulce cola_markets, pro kterou bude vytvořen prostorový index nad sloupcem "shape"}
      (TABLE_NAME,
       COLUMN_NAME,
@@ Line 73: / Line 75: @@
 );
-describe user_sdo_geom_metadata;
-CREATE INDEX cola_spatial_idx
+CREATE INDEX cola_spatial_idx {vytvoření prostorového indexu}
     ON cola_markets(shape)
     INDEXTYPE IS MDSYS.SPATIAL_INDEX;
-'''Operace nad prostorovými daty:'''
+select 'karel' {pomocný kontrolní dotaz}
+ from cola_markets;
-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(
-,  -- 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(
-,
-     NULL,
-     SDO_POINT_TYPE(10, 20, NULL),
-     NULL,
-     NULL), SDO_GEOMETRY(
-,
-     NULL,
-     SDO_POINT_TYPE(0, 0, NULL),
-     NULL,
-     NULL), 0.5)
-     from cola_markets;
 INSERT INTO cola_markets VALUES(

Difference between revisions of "Příkazy k vytvoření cola markets"

Revision as of 11:25, 25 April 2011

Navigation menu

Views

Personal tools

Navigation

Search

Tools