Mengenal Oracle Spatial

Oracle Spatial menyediakan SQL schema dan functions untuk storage, retrieval, update, dan query collections dari spatial data.

Oracle Spatial terdiri atas 4 komponen :
1. Schema (MDSYS) untuk menentukan the storage, syntax, dan suport geometric data tipe
2. Mekanisme spatial indexing
3. Fungsi untuk melakukan queries, spatial join queries dan operasi spatial analysis.
4. Administrative utilities

Oracle Spatial menyediakan manajemen data spasial yang lebih mudah bagi pemakai aplikasi berbasis Sistem Informasi Geografis. Sekali data ini disimpan di Oracle database dapat dengan mudah untuk di update dan digunakan.

Tipe data geometry yang didukung oleh Oracle Spatial adalah :
1. Points and point clusters
2. Line strings
3. n-point polygons
4. Arc line strings (All arcs are generated as circular arcs.)
5. Arc polygons
6. Compound polygons
7. Compound line strings
8. Circles
9. Optimized rectangles

Untuk lebih jelasnya langsung kita mulai tahapan tutorial dasar oracle spatial :

1. Masuk ke sqlplus user scott

C:\>sqlplus scott/tiger

2. Membuat table COLA_MARKETS

CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);

3. Inserts ke table Cola _Markets (cola_a, cola_b, cola_c, cola_d)

INSERT INTO cola_markets VALUES(
1,
’cola_a’,
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)
)
);

INSERT INTO cola_markets VALUES(
2,
’cola_b’,
MDSYS.SDO_GEOMETRY(2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
)
);

INSERT INTO cola_markets VALUES(
3,
’cola_c’,
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3)
)
);

INSERT INTO cola_markets VALUES(
4,
’cola_d’,
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4),
MDSYS.SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11)
)
);

4. Updates Table USER_SDO_GEOM_METADATA di schema MDSYS

INSERT INTO USER_SDO_GEOM_METADATA
VALUES (’cola_markets’,
’shape’,
MDSYS.SDO_DIM_ARRAY( — 20X20 grid
MDSYS.SDO_DIM_ELEMENT(’X’, 0, 20, 0.005),
MDSYS.SDO_DIM_ELEMENT(’Y’, 0, 20, 0.005)
),
NULL
);

5. Membuat spatial index (COLA_SPATIAL_IDX)

CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

6. Mencoba spatial queries

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_c’;

– Do two geometries have any spatial relationship?
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_b’ AND c_d.name = ‘cola_d’;

– Return the areas of all cola markets.
SELECT name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM cola_markets;
– Return the area of just cola_a.
SELECT c.name, SDO_GEOM.SDO_AREA(c.shape, 0.005) FROM cola_markets c
WHERE c.name = ‘cola_a’;

– Return the distance between two geometries.
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_d’;

Untuk melihat dalam bentuk visual bisa menggunakan MapViewer dan Mapserver .