CREATE TABLE speciality 
(
   id_receptu  NUMBER PRIMARY KEY,
   nazev_jidla VARCHAR2(150)
);

INSERT INTO speciality VALUES 
   (1, 'Hovz rolky plnn vejci a unkou obloen brokolic s kroketami');
INSERT INTO speciality VALUES 
   (2, 'unkov rolky plnn lososovm krmem');
INSERT INTO speciality VALUES 
   (3, 'Karbantek s vejci a hoicovou omkou');
INSERT INTO speciality VALUES 
   (4, 'Hovz gul podvan v chlebovm bochnku');



CREATE TABLE plochy 
(
   id_plochy  NUMBER PRIMARY KEY,
   jmeno      VARCHAR2(12),
   hodnota    NUMBER(10,2),
   geom_utvar MDSYS.SDO_GEOMETRY
);


INSERT INTO plochy VALUES(1, 'Plocha_A', 2000, 
   MDSYS.SDO_GEOMETRY(2003, -- SDO_GTYPE dvojrozmrn mnohohlenk
                      NULL, -- SDO_SRID
                      NULL, -- SDO_POINT
                   -- jeden obdelnk (1003 = vnj)
                      MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), 
                   -- obdelnk definovan levm dolnm a pravm hornm rohem
                      MDSYS.SDO_ORDINATE_ARRAY(1,1, 8,8)   
                     )
);


INSERT INTO plochy VALUES(2, 'Plocha_B', 700, 
   MDSYS.SDO_GEOMETRY(2003, -- SDO_GTYPE dvojrozmrn mnohohlenk
                      NULL, -- SDO_SRID
                      NULL, -- SDO_POINT
                   -- jeden obdelnk (1003 = vnj)
                      MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), 
                   -- obdelnk definovan levm dolnm a pravm hornm rohem
                      MDSYS.SDO_ORDINATE_ARRAY(5,2, 10,5)   
                     )
);



INSERT INTO plochy VALUES(3, 'Plocha_C', 6000, 
   MDSYS.SDO_GEOMETRY(2003, -- SDO_GTYPE kruh jako dvojrozmrn mnohohlenk
                      NULL, -- SDO_SRID
                      NULL, -- SDO_POINT
                   -- jeden kruh (1003 = vnj), (4  kruh)
                      MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4), 
                   -- kruh definovan temi body
                      MDSYS.SDO_ORDINATE_ARRAY(6,10, 8,12,  6,14)   
                     )
);




INSERT INTO USER_SDO_GEOM_METADATA VALUES 
(
  'plochy',
  'geom_utvar',
   MDSYS.SDO_DIM_ARRAY( -- souadnicov mka 20 x 20 
                       MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
                       MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
                      ),
  NULL -- SRID
);



SELECT SDO_GEOM.SDO_DISTANCE(p_a.geom_utvar, p_c.geom_utvar, 0.005)
FROM plochy p_a, plochy p_c
WHERE p_a.jmeno = 'Plocha_A' AND p_c.jmeno = 'Plocha_C';


CREATE TABLE customers1 
( 
   CUSTOMER_ID NUMBER(6), 
   CUST_FIRST_NAME VARCHAR2(20), 
   CUST_LAST_NAME VARCHAR2(20), 
   CUST_ADDRESS OE.CUST_ADDRESS_TYP, 
   PHONE_NUMBERS OE.PHONE_LIST_TYP, 
   NLS_LANGUAGE VARCHAR2(3), 
   NLS_TERRITORY VARCHAR2(30), 
   CREDIT_LIMIT NUMBER(9, 2), 
   CUST_EMAIL VARCHAR2(30), 
   ACCOUNT_MGR_ID NUMBER(6), 
   CUST_GEO_LOCATION MDSYS.SDO_GEOMETRY
);


INSERT INTO customers1 VALUES 
    (1001,'Jan','Novak',
     cust_address_typ('Na prikope','11223','Praha','CZ','EU'),
     PHONE_LIST_TYP('+420 123 456'),
     'us','AMERICA','100','jannovak@xxx.com',149,
     MDSYS.SDO_GEOMETRY(2001, 8307, 
       MDSYS.SDO_POINT_TYPE (14.41935, 50.09193,NULL),NULL,NULL));
    
    
INSERT INTO customers1 VALUES 
    (1002,'Giuseppe','Garcia',
     cust_address_typ('Corsica Way','20190','Venice','IT','EU'),
     PHONE_LIST_TYP('+420 987 654'),
     'us','AMERICA','100','garcia@xxx.com',149,
     MDSYS.SDO_GEOMETRY(2001, 8307, 
        MDSYS.SDO_POINT_TYPE(12.28389, 45.45233,NULL),NULL,NULL));




CREATE TABLE warehouses1 
( 
   WAREHOUSE_ID NUMBER(3), 
   WAREHOUSE_NAME VARCHAR2(35), 
   WH_GEO_LOCATION MDSYS.SDO_GEOMETRY
)

INSERT INTO warehouses1 values
   (1, 'Mnichov', 
    MDSYS.SDO_GEOMETRY (2001,8307, MDSYS.SDO_POINT_TYPE (11.5424,48.2231, null),null, null));

INSERT INTO warehouses1 values
   (2, 'Rim', 
    MDSYS.SDO_GEOMETRY (2001,8307, MDSYS.SDO_POINT_TYPE (12.4833,41.9601, null),null, null));


INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) 
  VALUES ('warehouses1', 'WH_GEO_LOCATION', 
    MDSYS.SDO_DIM_ARRAY 
      (MDSYS.SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.005), 
       MDSYS.SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.005)  
      ), 
     8307); 


INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) 
  VALUES ('customers1', 'CUST_GEO_LOCATION', 
    MDSYS.SDO_DIM_ARRAY 
      (MDSYS.SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.005), 
       MDSYS.SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.005)  
      ), 
     8307); 

COMMIT;


CREATE INDEX warehouses1_sidx on warehouses1(WH_GEO_LOCATION)
indextype is mdsys.spatial_index;


CREATE INDEX customers1_sidx on customers1(CUST_GEO_LOCATION)
indextype is mdsys.spatial_index;


SELECT customer_id, cust_first_name, cust_last_name
FROM warehouses1 w, customers1 c
WHERE w.warehouse_id = 1
and sdo_nn (c.cust_geo_location, w.wh_geo_location, 'sdo_num_res=1') = 'TRUE';


