-- CLEAN UP

drop table PROPERTY cascade constraints;
drop table MAP cascade constraints;
delete from user_sdo_geom_metadata where table_name = 'MAP' and column_name = 'SHAPE';
delete from user_sdo_geom_metadata where table_name = 'PROPERTY_MAP' and column_name = 'SHAPE';
drop view PROPERTY_MAP cascade constraints;

-- TABLE 1

create table PROPERTY (
  PROP_ID number(12,0),
  NAME varchar2(48),
  VALUE number(12,2),
  primary key(PROP_ID)
);

-- TABLE 2

create table MAP (
  SHAPE_ID number(12,0),
  SHAPE sdo_geometry,
  primary key(SHAPE_ID)
--foreign key(SHAPE_ID) references PROPERTY(PROP_ID)
);

-- JOIN VIEW

create view PROPERTY_MAP as
  select a.PROP_ID,
         a.NAME,
         a.VALUE,
         b.SHAPE
  from PROPERTY a, MAP b 
  where a.PROP_ID = b.SHAPE_ID;

-- INSERT TO METADATA

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
  values ( 'MAP', 'SHAPE',
    sdo_dim_array(sdo_dim_element('X', -180.0, 180, 0.5), sdo_dim_element('Y', -90, 90, 0.5)), 4326);

-- INSERT TO METADATA

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
  values ( 'PROPERTY_MAP', 'SHAPE',
    sdo_dim_array(sdo_dim_element('X', -180.0, 180, 0.5), sdo_dim_element('Y', -90, 90, 0.5)), 4326);

-- CREATE SPATIAL INDEX

create index MAP_SIDX on MAP(SHAPE)
  indextype is mdsys.spatial_index
  parameters('sdo_indx_dims=2, layer_gtype=multipolygon');

-- INSTEAD OF TRIGGER

create or replace trigger INSTEAD_OF_TRIGGER
  instead of insert or update or delete on PROPERTY_MAP
for each row
begin
  if inserting then
    insert into PROPERTY values (
      :new.PROP_ID,
      :new.NAME,
      :new.VALUE);
    insert into MAP values (
      :new.PROP_ID,
      :new.SHAPE);
  end if;
  if updating then
    update PROPERTY set
      NAME = :new.NAME,
      VALUE = :new.VALUE
    where PROP_ID = :old.PROP_ID;
    update MAP set
      SHAPE = :new.SHAPE
    where SHAPE_ID = :old.PROP_ID;     
  end if;
  if deleting then
    delete from PROPERTY_MAP WHERE PROP_ID = :old.PROP_ID;
  end if;
end;
/
show errors;

-- INSERT ONE RECORD

insert into PROPERTY_MAP (PROP_ID, NAME, VALUE, SHAPE)
  values (1, 'P1', 1000.00,
    sdo_geometry(2003, 4326, null, sdo_elem_info_array(1, 1003, 1),
      sdo_ordinate_array(1.0, 1.0, 1.0, 1.1, 1.1, 1.1, 1.1, 1.0, 1.0, 1.0)));

insert into PROPERTY_MAP (PROP_ID, NAME, VALUE, SHAPE)
  values (2, 'P2', 2000.00,
    sdo_geometry(2003, 4326, null, sdo_elem_info_array(1, 1003, 1),
      sdo_ordinate_array(2.0, 2.0, 2.0, 2.1, 2.1, 2.1, 2.1, 2.0, 2.0, 2.0)));

insert into PROPERTY_MAP (PROP_ID, NAME, VALUE, SHAPE)
  values (3, 'P3', 3000.00,
    sdo_geometry(2003, 4326, null, sdo_elem_info_array(1, 1003, 1),
      sdo_ordinate_array(3.0, 3.0, 3.0, 3.1, 3.1, 3.1, 3.1, 3.0, 3.0, 3.0)));

commit;

select * from map;
select * from property;

