Difference between revisions of "Příkazy k vytvoření cola markets"
From Wikivyuka
m |
m |
||
| Line 3: | Line 3: | ||
Stránka k prostorovým operacím: [http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28400/sdo_operat.htm] | Stránka k prostorovým operacím: [http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28400/sdo_operat.htm] | ||
| − | CREATE TABLE cola_markets ( | + | CREATE TABLE cola_markets ( |
mkt_id NUMBER PRIMARY KEY, | mkt_id NUMBER PRIMARY KEY, | ||
name VARCHAR2(32), | name VARCHAR2(32), | ||
shape SDO_GEOMETRY); | shape SDO_GEOMETRY); | ||
| − | INSERT INTO cola_markets VALUES( | + | INSERT INTO cola_markets VALUES( |
1, | 1, | ||
'cola_a', | 'cola_a', | ||
| Line 18: | Line 18: | ||
SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to | SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to | ||
) | ) | ||
| − | ); | + | ); |
| − | INSERT INTO cola_markets VALUES( | + | INSERT INTO cola_markets VALUES( |
2, | 2, | ||
'cola_b', | 'cola_b', | ||
| Line 30: | Line 30: | ||
SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1) | SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1) | ||
) | ) | ||
| − | ); | + | ); |
| − | INSERT INTO cola_markets VALUES( | + | INSERT INTO cola_markets VALUES( |
3, | 3, | ||
'cola_c', | 'cola_c', | ||
| Line 42: | Line 42: | ||
SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3) | SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3) | ||
) | ) | ||
| − | ); | + | ); |
| − | INSERT INTO cola_markets VALUES( | + | INSERT INTO cola_markets VALUES( |
4, | 4, | ||
'cola_d', | 'cola_d', | ||
| Line 54: | Line 54: | ||
SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11) | SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11) | ||
) | ) | ||
| − | ); | + | ); |
| − | + | ===Zápis informací o prostorové složce do tabulky s metadaty === | |
describe user_sdo_geom_metadata; {zjištění struktury tabulky uchovávající metadata} | 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"} | + | 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 ( | VALUES ( | ||
'cola_markets', | 'cola_markets', | ||
| Line 73: | Line 70: | ||
), | ), | ||
NULL -- SRID | NULL -- SRID | ||
| − | ); | + | ); |
| + | ===Vytvoření prostorového indexu=== | ||
| − | CREATE INDEX cola_spatial_idx { | + | CREATE INDEX cola_spatial_idx {} |
ON cola_markets(shape) | ON cola_markets(shape) | ||
INDEXTYPE IS MDSYS.SPATIAL_INDEX; | INDEXTYPE IS MDSYS.SPATIAL_INDEX; | ||
| − | select 'karel' {pomocný kontrolní dotaz} | + | select 'karel' {pomocný kontrolní dotaz} |
from cola_markets; | from cola_markets; | ||
| − | + | ||
| − | INSERT INTO cola_markets VALUES( | + | |
| + | INSERT INTO cola_markets VALUES( | ||
90, | 90, | ||
'point_only', | 'point_only', | ||
| Line 94: | Line 93: | ||
NULL)); | NULL)); | ||
| − | INSERT INTO cola_markets VALUES( | + | INSERT INTO cola_markets VALUES( |
91, | 91, | ||
'point_only', | 'point_only', | ||
| Line 104: | Line 103: | ||
NULL)); | NULL)); | ||
| − | INSERT INTO cola_markets VALUES( | + | INSERT INTO cola_markets VALUES( |
92, | 92, | ||
'point_only', | 'point_only', | ||
| Line 114: | Line 113: | ||
NULL)); | NULL)); | ||
| − | INSERT INTO cola_markets VALUES( | + | INSERT INTO cola_markets VALUES( |
10, | 10, | ||
'polygon_with_hole', | 'polygon_with_hole', | ||
| Line 125: | Line 124: | ||
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) | 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) | ||
) | ) | ||
| − | ); | + | ); |
[http://gis.vsb.cz/wikivyuka/index.php/Prostorov%C3%A9_datab%C3%A1ze Zpět na stránku PDB] | [http://gis.vsb.cz/wikivyuka/index.php/Prostorov%C3%A9_datab%C3%A1ze Zpět na stránku PDB] | ||
Revision as of 06:24, 22 March 2012
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 exterior polygon ring, 3 - rectangle stačí dva body k nadefinování(lower left and upper right)) 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 (1003 - 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), -- 4 - one circle - definován třemi body SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11) ) );
Zápis informací o prostorové složce do tabulky s metadaty
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
);
Vytvoření prostorového indexu
CREATE INDEX cola_spatial_idx {}
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, jsou zde dva prvky, -- 2003 je polygon definován jako interior polygon ring, jeho koordináty začínají od pozice 19 v poli koordinát 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) ) );
