-- Halozat.sql

-- Táblák eldobása

DROP TABLE cities;

DROP TABLE nodes_cover;

DROP TABLE paths;

DROP TABLE nodes;

DELETE FROM user_sdo_geom_metadata;

-- Táblák létrehozása

CREATE TABLE cities (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(16),
  shape SDO_GEOMETRY);

CREATE TABLE nodes (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(5),
  shape SDO_GEOMETRY);

CREATE TABLE nodes_cover (
  id NUMBER PRIMARY KEY,
  shape SDO_GEOMETRY);

CREATE TABLE paths (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(12),
  startpoint NUMBER,
  endpoint NUMBER,
  shape SDO_GEOMETRY);

ALTER TABLE paths ADD (
  CONSTRAINT "start" FOREIGN KEY(startpoint)
	REFERENCES nodes(id),
  CONSTRAINT "end" FOREIGN KEY(endpoint)
	REFERENCES nodes(id));

ALTER TABLE nodes_cover ADD (
  CONSTRAINT "id" FOREIGN KEY(id)
        REFERENCES nodes(id));

-- Sorok létrehozása

-- Városok

INSERT into cities VALUES(1, 'Budapest', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(5,4, 6,8, 5,11, 2,11, 1,8, 5,4)));


INSERT into cities VALUES(2, 'Zalaegerszeg', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 7,2,2), SDO_ORDINATE_ARRAY(2,18, 4,16, 5,18, 4,20, 2.586,19.414, 2,18)));


INSERT into cities VALUES(3, 'Szekesfehervar', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(9,14, 11,12, 13,14)));


INSERT into cities VALUES(4, 'Debrecen', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(17,16, 23,19)));


INSERT into cities VALUES(5, 'Szeged', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 9,2,2), SDO_ORDINATE_ARRAY(19,8, 16,3, 17,1, 19,3, 22,5, 21.121,7.121, 19,8)));

COMMIT;

-- Csomópontok

INSERT INTO nodes VALUES(1, 'BP1', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(2,10,NULL), NULL, NULL));
INSERT INTO nodes VALUES(2, 'BP2', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(3,8,NULL), NULL, NULL));
INSERT INTO nodes VALUES(3, 'BP3', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(5,10,NULL), NULL, NULL));
INSERT INTO nodes VALUES(4, 'BP4', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(4,6,NULL), NULL, NULL));
INSERT INTO nodes VALUES(5, 'ZEG1', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(4,18,NULL), NULL, NULL));
INSERT INTO nodes VALUES(6, 'SZFV1', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(10,13,NULL), NULL, NULL));
INSERT INTO nodes VALUES(7, 'SZFV2', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(11,15,NULL), NULL, NULL));
INSERT INTO nodes VALUES(8, 'DEB1', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(18,18,NULL), NULL, NULL));
INSERT INTO nodes VALUES(9, 'DEB2', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(22,18,NULL), NULL, NULL));
INSERT INTO nodes VALUES(10, 'DEB3', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(20,17,NULL), NULL, NULL));
INSERT INTO nodes VALUES(11, 'SZEG1', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(20,7,NULL), NULL, NULL));
INSERT INTO nodes VALUES(12, 'SZEG2', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(20,5,NULL), NULL, NULL));
INSERT INTO nodes VALUES(13, 'SZEG3', SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(17,4,NULL), NULL, NULL));

COMMIT;

-- Utak

INSERT INTO paths VALUES(1, 'BP1-BP2', 1, 2, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(2,10, 3,8)));
INSERT INTO paths VALUES(2, 'BP2-BP3', 2, 3, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(3,8, 5,10)));
INSERT INTO paths VALUES(3, 'BP2-BP4', 2, 4, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(3,8, 4,6)));
INSERT INTO paths VALUES(4, 'BP3-BP4', 3, 4, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(5,10, 4,6)));
INSERT INTO paths VALUES(5, 'BP1-ZEG1', 1, 5, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(2,10, 4,18)));
INSERT INTO paths VALUES(6, 'ZEG1-SZFV1', 5, 6, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(4,18, 10,13)));
INSERT INTO paths VALUES(7, 'SZFV1-SZFV2', 6, 7, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(10,13, 11,15)));
INSERT INTO paths VALUES(8, 'SZFV2-DEB1', 7, 8, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(11,15, 18,18)));
INSERT INTO paths VALUES(9, 'DEB1-DEB2', 8, 9, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(18,18, 22,18)));
INSERT INTO paths VALUES(10, 'DEB1-DEB3', 8, 10, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(18,18, 20,17)));
INSERT INTO paths VALUES(11, 'DEB2-DEB3', 9, 10, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(22,18, 20,17)));
INSERT INTO paths VALUES(12, 'DEB2-SZEG1', 9, 11, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(22,18, 20,7)));
INSERT INTO paths VALUES(13, 'DEB3-SZEG3', 10, 13, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(20,17, 17,4)));
INSERT INTO paths VALUES(14, 'SZEG1-SZEG2',11, 12, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(20,7, 20,5)));
INSERT INTO paths VALUES(15, 'SZEG2-SZEG3', 12, 13, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(20,5, 17,4)));
INSERT INTO paths VALUES(16, 'SZEG2-BP3', 12, 3, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(20,5, 5,10)));
INSERT INTO paths VALUES(17, 'SZEG3-BP4', 13, 4, SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(17,4, 4,6)));

COMMIT;

-- Lefedettség feltöltése

INSERT INTO nodes_cover VALUES(1, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(1,10, 2,9, 3,10)));
INSERT INTO nodes_cover VALUES(2, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(2,8, 3,7, 4,8)));
INSERT INTO nodes_cover VALUES(3, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(4,10, 5,9, 6,10)));
INSERT INTO nodes_cover VALUES(4, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(3,6, 4,5, 5,6)));
INSERT INTO nodes_cover VALUES(5, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(3,18, 4,17, 5,18)));
INSERT INTO nodes_cover VALUES(6, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(9,13, 10,12, 11,13)));
INSERT INTO nodes_cover VALUES(7, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(10,15, 11,14, 12,15)));
INSERT INTO nodes_cover VALUES(8, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(17,18, 18,17, 19,18)));
INSERT INTO nodes_cover VALUES(9, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(21,18, 22,17, 23,18)));
INSERT INTO nodes_cover VALUES(10, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(19,17, 20,16, 21,17)));
INSERT INTO nodes_cover VALUES(11, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(19,7, 20,6, 21,7)));
INSERT INTO nodes_cover VALUES(12, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(19,5, 20,4, 21,5)));
INSERT INTO nodes_cover VALUES(13, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(16,4, 17,3, 18,4)));

COMMIT;

-- Metaadatok betöltése

INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'cities',
  'shape',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 0, 30, 0.005),
    SDO_DIM_ELEMENT('Y', 0, 30, 0.005)
     ),
  NULL);


INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'nodes',
  'shape',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 0, 30, 0.005),
    SDO_DIM_ELEMENT('Y', 0, 30, 0.005)
     ),
  NULL);


INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'paths',
  'shape',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 0, 30, 0.005),
    SDO_DIM_ELEMENT('Y', 0, 30, 0.005)
     ),
  NULL);


INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'nodes_cover',
  'shape',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 0, 30, 0.005),
    SDO_DIM_ELEMENT('Y', 0, 30, 0.005)
     ),
  NULL);

COMMIT;

-- Indexek létrehozása

CREATE INDEX cities_index
   ON cities(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;

CREATE INDEX nodes_index
   ON nodes(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;

CREATE INDEX paths_index
   ON paths(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;

CREATE INDEX nodes_cover_index
   ON nodes_cover(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;

COMMIT;

-- Lekérdezések

-- Városok területei

SELECT name as city, SDO_GEOM.SDO_AREA(shape, 0.005) as area, 'km^2' as measure
FROM cities;

-- Melyik csomópont melyik városban van?

SELECT node, city
FROM (SELECT n.name as node, c.name as city, SDO_GEOM.RELATE(c.shape, 'anyinteract', n.shape, 0.005) as inside 
      FROM cities c, nodes n)
WHERE inside = 'TRUE';

-- Két város közötti fizikai kapcsolatok?

SELECT c1.name as city1, c2.name as city2, p.name as path_name
FROM cities c1, cities c2, nodes n1, nodes n2, paths p
WHERE NOT(c1.id=c2.id) AND
      SDO_GEOM.RELATE(c1.shape, 'contains', n1.shape, 0.005) = 'CONTAINS' AND
      SDO_GEOM.RELATE(c2.shape, 'contains', n2.shape, 0.005) = 'CONTAINS' AND
      p.startpoint = n1.id AND
      p.endpoint = n2.id;

-- Fizikai kapcsolatok hossza?

SELECT p.name as path, SDO_GEOM.SDO_LENGTH(p.shape, m.diminfo) as length, 'km' as km
FROM paths p , user_sdo_geom_metadata m
WHERE m.table_name = 'PATHS' AND m.column_name = 'SHAPE';

-- Városok közötti távolságok?

SELECT c1.name as city1, c2.name as city2, SDO_GEOM.SDO_DISTANCE(c1.shape, c2.shape, 0.005) as distance, 'km' as km
FROM cities c1, cities c2
WHERE c1.id < c2.id;

-- Debrecen lefedettsége?

SELECT city, SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_DIFFERENCE(shape, cover, 0.005), 0.005) / SDO_GEOM.SDO_AREA(shape, 0.005) * 100 as coverage, '%' as meas
FROM( SELECT c.name as city, SDO_GEOM.SDO_UNION(SDO_GEOM.SDO_UNION(nc1.shape, nc2.shape, 0.005), nc3.shape, 0.005) as cover, c.shape as shape
      FROM cities c, nodes n1, nodes n2, nodes n3, nodes_cover nc1, nodes_cover nc2, nodes_cover nc3, user_sdo_geom_metadata m
      WHERE n1.id = nc1.id AND
            n2.id = nc2.id AND
            n3.id = nc3.id AND
            n1.id < n2.id AND
            n1.id < n3.id AND
            n2.id < n3.id AND
            SDO_GEOM.RELATE(c.shape, 'contains', n1.shape, 0.005) = 'CONTAINS' AND
            SDO_GEOM.RELATE(c.shape, 'contains', n2.shape, 0.005) = 'CONTAINS' AND
            SDO_GEOM.RELATE(c.shape, 'contains', n3.shape, 0.005) = 'CONTAINS' AND
            c.name = 'Debrecen' AND
            m.table_name = 'NODES_COVER' AND
            m.column_name = 'SHAPE');