-- (c) arnd.spiering@as-informatik.net
-- Convert Layer TO LRS Layer with mvdemo data
-- Drop Spatial Index
DROP INDEX "MVDEMO"."INTERSTATES_SDX";

-- Convert Entire Layer
SET SERVEROUTPUT ON;
DECLARE 
 status VARCHAR2(32); 
BEGIN 
 status := MDSYS.SDO_LRS.CONVERT_TO_LRS_LAYER('INTERSTATES', 'GEOM',0, 1000000,0.5);
 dbms_output.put_line(status);
END; 
/ 

-- Create Spatial Index
CREATE INDEX "MVDEMO"."INTERSTATES_SDX" ON "MVDEMO"."INTERSTATES"
  (
    "GEOM"
  )
  INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;
  
-- Create Referencing Table
create table pavement_condition ( 
   highway        varchar2(35), 
   from_measure   number, 
   to_measure     number, 
   condition      varchar2(6)); 

-- Insert Demo Data
insert into pavement_condition values (
  'I 95', 0, 5951.5, 'good');
insert into pavement_condition values (
  'I 95',5951.5 , 28000, 'fair');
insert into pavement_condition values (
  'I 95', 28000, 37194, 'poor');
insert into pavement_condition values (
  'I 495', 0, 16001, 'good');
insert into pavement_condition values (
  'I 495', 16001, 18004, 'poor');
insert into pavement_condition values (
  'I 295', 0, 3000, 'poor');
insert into pavement_condition values (
  'I 295', 3000, 4000, 'fair');
insert into pavement_condition values (
  'I 295', 4000, 8001.5, 'poor');
insert into pavement_condition values (
  'I 295', 8001.5, 9603, 'good');
commit;

-- Create Index
create index pavement_condition_idx on pavement_condition (highway);   


-- Create Views with user_sdo_geom_metadata  
create or replace view v_interstates_pavcond_poor as select SDO_LRS.CLIP_GEOM_SEGMENT(a.geom, b.from_measure, b.to_measure) as geom, FROM_MEASURE, TO_MEASURE,CONDITION ,
       a.highway 
from interstates a, 
         pavement_condition b 
where b.condition = 'poor'
  and a.highway = b.highway;   

create or replace view v_interstates_pavcond_fair as select SDO_LRS.CLIP_GEOM_SEGMENT(a.geom, b.from_measure, b.to_measure) as geom, FROM_MEASURE, TO_MEASURE,CONDITION ,
       a.highway 
from interstates a, 
         pavement_condition b 
where b.condition = 'fair'
  and a.highway = b.highway; 

INSERT INTO user_sdo_geom_metadata values (
  'v_interstates_pavcond_fair', 'GEOM', 
  sdo_dim_array ( 
    sdo_dim_element ('Long', -180, 180, 0.5), 
    sdo_dim_element ('Lat', -90, 90, 0.5)), 
  8307);

INSERT INTO user_sdo_geom_metadata values (
  'v_interstates_pavcond_poor', 'GEOM', 
  sdo_dim_array ( 
    sdo_dim_element ('Long', -180, 180, 0.5), 
    sdo_dim_element ('Lat', -90, 90, 0.5)), 
  8307);

COMMIT;
   
   