-- Script de base
drop table traverse;

drop table route;

drop table parcelle;

drop table point_eau;

drop table commune;

drop table proprietaire;


create table commune (
nom_comm varchar(20) not null primary key,
commune_geom MDSYS.SDO_GEOMETRY,
parcelles MDSYS.SDO_GEOMETRY
);


create table proprietaire (
id_prop number(2) not null primary key,
nom varchar(15),
prenom varchar(15));

create table parcelle (
num_cad varchar(15) not null primary key,
id_prop number(2),
nom_comm varchar(20),
parcelle_geom MDSYS.SDO_GEOMETRY,
foreign key (nom_comm) references commune,
foreign key (id_prop) references proprietaire
);


create table point_eau (
num_point varchar(4) not null primary key,
profondeur varchar(15),
num_cad varchar(15) not null,
point_geom MDSYS.SDO_GEOMETRY,
foreign key (num_cad) references parcelle
);

create table route (
num_route varchar(15) not null primary key,
longueur varchar(15) not null,
route_geom MDSYS.SDO_GEOMETRY
);

create table traverse (
num_cad varchar(15) not null,
num_route varchar(15) not null,
primary key (num_cad,num_route),
foreign key (num_cad) references parcelle,
foreign key (num_route) references route
);
------------------------------------

insert into route (num_route,longueur,route_geom) values ('RN113','150 km',MDSYS.SDO_GEOMETRY
(2002,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY
(5,0,3,2,3,3,2,3,2,2,1,2,1,3,1,4,2,5,0,7,1,8)));
insert into route (num_route,longueur,route_geom) values ('RN110','65 km',MDSYS.SDO_GEOMETRY
(2002,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY
(9,0,9,1,8,2,8,2.5,5,2.5,4.5,3,5,4,5,6,5,8)));
insert into route (num_route,longueur,route_geom) values ('D09','27 km',MDSYS.SDO_GEOMETRY
(2002,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY
(5,6,4,6,3.5,6.5,2.5,6.5,2,7,3,8)));



insert into proprietaire values (01,'Dupont','Francois');
insert into proprietaire values (02,'Durand','Martin');
insert into proprietaire values (03,'Martin','Jacques');
insert into proprietaire values (04,'Untel','Patrick');
insert into proprietaire values (05,'Machin','Paul');
insert into proprietaire values (06,'Acote','Lionel');
insert into proprietaire values (07,'Truc','Fabien');



insert into commune (nom_comm,commune_geom,parcelles) values ('St Jean de
Vedas',MDSYS.SDO_GEOMETRY(2003,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(0,0,6,0,6,5,0,5,0,0)),MDSYS.SDO_GEOMETRY
(2007,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,3,1,15,3,1,25,3,1,39,3,1),
MDSYS.SDO_ORDINATE_ARRAY
(0,0,0,2,2,4,4,4,4,1,2,0,0,0,0,2,1,4,2,5,0,5,0,2,1,4,4,4,6,3,6,5,4,5,2,5,1,4,2,0,6,0,6,3,4,4,4,1,2,0)));
insert into commune (nom_comm,commune_geom,parcelles) values
('Fabregues',MDSYS.SDO_GEOMETRY(2003,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(0,6,0,7,1,7,2,8,3,7,3,6,0,6)),MDSYS.SDO_GEOMETRY
(2007,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,3,1,11,3,1),MDSYS.SDO_ORDINATE_ARRAY
(0,6,0,7,1,7,2,6,0,6,1,7,2,8,3,7,3,6,2,6,1,7)));
insert into commune (nom_comm,commune_geom,parcelles) values
('Saussan',MDSYS.SDO_GEOMETRY(2003,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(7,0,7,3,9,3,8,1,7,0)),null);



insert into parcelle (num_cad,nom_comm,id_prop,parcelle_geom) values ('BC1234','St Jean de
Vedas',01,MDSYS.SDO_GEOMETRY(2003,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(0,0,0,2,2,4,4,4,4,1,2,0,0,0)));
insert into parcelle (num_cad,nom_comm,id_prop,parcelle_geom) values ('BC1023','St Jean de
Vedas',02,MDSYS.SDO_GEOMETRY(2003,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(0,2,0,5,2,5,2,4,1,3,0,2)));
insert into parcelle (num_cad,nom_comm,id_prop,parcelle_geom) values ('BC4321','St Jean de
Vedas',03,MDSYS.SDO_GEOMETRY(2003,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(2,4,2,5,4,5,6,5,6,3,5,3.5,4,4,2,4)));
insert into parcelle (num_cad,nom_comm,id_prop,parcelle_geom) values ('BC2222','St Jean de
Vedas',04,MDSYS.SDO_GEOMETRY(2003,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(2,0,4,1,4,4,5,3.5,6,3,6,0,2,0)));
insert into parcelle (num_cad,nom_comm,id_prop,parcelle_geom) values ('BC0003','Fabregues',05,MDSYS.SDO_GEOMETRY(2003,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY
(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(0,6,0,7,1,7,2,6,0,6)));
insert into parcelle (num_cad,nom_comm,id_prop,parcelle_geom) values ('BC1010','Saussan',06,MDSYS.SDO_GEOMETRY(2003,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(7,0,7,3,9,3,8,1,7,0)));
insert into parcelle (num_cad,nom_comm,id_prop,parcelle_geom) values ('BC1111','Fabregues',07,MDSYS.SDO_GEOMETRY(2003,40967,null,MDSYS.SDO_ELEM_INFO_ARRAY
(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(1,7,2,8,3,7,3,6,2,6,1,7)));


insert into point_eau (num_point,profondeur,num_cad,point_geom) values ('p1','23
metres','BC1234',MDSYS.SDO_GEOMETRY(2001,40967,MDSYS.SDO_POINT_TYPE(1,1,0),null,null));
insert into point_eau (num_point,profondeur,num_cad,point_geom) values ('p2','12
metres','BC1234',MDSYS.SDO_GEOMETRY(2001,40967,MDSYS.SDO_POINT_TYPE(3,1,0),null,null));
insert into point_eau (num_point,profondeur,num_cad,point_geom) values ('p3','25
metres','BC1234',MDSYS.SDO_GEOMETRY(2001,40967,MDSYS.SDO_POINT_TYPE(3.5,2.5,0),null,null));
insert into point_eau (num_point,profondeur,num_cad,point_geom) values ('p4','10
metres','BC1023',MDSYS.SDO_GEOMETRY(2001,40967,MDSYS.SDO_POINT_TYPE(0.5,3,0),null,null));
insert into point_eau (num_point,profondeur,num_cad,point_geom) values ('p5','18
metres','BC4321',MDSYS.SDO_GEOMETRY(2001,40967,MDSYS.SDO_POINT_TYPE(4,4.5,0),null,null));
insert into point_eau (num_point,profondeur,num_cad,point_geom) values ('p6','12
metres','BC2222',MDSYS.SDO_GEOMETRY(2001,40967,MDSYS.SDO_POINT_TYPE(5,1,0),null,null));
insert into point_eau (num_point,profondeur,num_cad,point_geom) values ('p7','23
metres','BC2222',MDSYS.SDO_GEOMETRY(2001,40967,MDSYS.SDO_POINT_TYPE(5,3,0),null,null));
insert into point_eau (num_point,profondeur,num_cad,point_geom) values ('p8','09
metres','BC1010',MDSYS.SDO_GEOMETRY(2001,40967,MDSYS.SDO_POINT_TYPE(7.5,2.5,0),null,null));
insert into point_eau (num_point,profondeur,num_cad,point_geom) values ('p9','21
metres','BC1010',MDSYS.SDO_GEOMETRY(2001,40967,MDSYS.SDO_POINT_TYPE(7.5,1,0),null,null));
insert into point_eau (num_point,profondeur,num_cad,point_geom) values ('p10','11
metres','BC0003',MDSYS.SDO_GEOMETRY(2001,40967,MDSYS.SDO_POINT_TYPE(0.5,6.5,0),null,null));

insert into traverse values ('BC2222','RN113');
insert into traverse_par values ('BC2222','RN110');
insert into traverse_par values ('BC1234','RN113');
insert into traverse values ('BC1023','RN113');
insert into traverse_par values ('BC0003','RN113');
insert into traverse_par values ('BC1010','RN110');
insert into traverse values ('BC4321','RN110');
insert into traverse values ('BC1111','D09');


-------------------------Mise ˆ jour de la table des mŽtadonnŽes USER_SDO_GEOM_METADATA----------------------

insert into USER_SDO_GEOM_METADATA values ('route','route_geom',MDSYS.SDO_DIM_ARRAY
(MDSYS.SDO_DIM_ELEMENT('X', 0, 20, .01),MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, .01)),40967);

insert into USER_SDO_GEOM_METADATA values ('parcelle','parcelle_geom',MDSYS.SDO_DIM_ARRAY
(MDSYS.SDO_DIM_ELEMENT('X', 0, 20, .01),MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, .01)),40967);

insert into USER_SDO_GEOM_METADATA values ('commune','commune_geom',MDSYS.SDO_DIM_ARRAY
(MDSYS.SDO_DIM_ELEMENT('X', 0, 20, .01),MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, .01)),40967);

insert into USER_SDO_GEOM_METADATA values
('commune','parcelles',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 0, 20, .01),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, .01)),40967);

insert into USER_SDO_GEOM_METADATA values ('point_eau','point_geom',MDSYS.SDO_DIM_ARRAY
(MDSYS.SDO_DIM_ELEMENT('X', 0, 20, .01),MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, .01)),40967);



---- index----

CREATE INDEX route_idx ON route(route_geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('SDO_INDX_DIM=2');

CREATE INDEX parcelle_idx ON parcelle(parcelle_geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('SDO_INDX_DIM=2');

CREATE INDEX commune_idx ON commune(commune_geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('SDO_INDX_DIM=2');

CREATE INDEX communebis_idx ON commune(parcelles) INDEXTYPE IS
MDSYS.SPATIAL_INDEX PARAMETERS('SDO_INDX_DIM=2');

CREATE INDEX point_idx ON point_eau(point_geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('SDO_INDX_DIM=2');