To Create a View Joining Two Tables Together to Create a New Feature
The following creates two tables: TABLE1 (Point - Spatial) and TABLE2 (Non-Spatial). TABLE1 has an GID (Graphical ID) number, PROP1, PROP2 and a geometry column. TABLE2 has an AID (Attribute ID) number, the same PROP1 as found in TABLE1 and a unique PROP3. The tables are populated with three records. A multi-table view is created which will be recognized as a point feature when registered in the Geospatial Administrator. Finally triggers are set up to insert new point features into TABLE1, and to update and modify existing records in TABLE1 and TABLE2.
------------------------------------------------------------------------------------ -- Create TABLE1 table ------------------------------------------------------------------------------------ DROP TABLE TABLE1 CASCADE CONSTRAINT; CREATE TABLE "TABLE1" ( GID NUMBER CONSTRAINT TABLE1_PK PRIMARY KEY, PROP1 VARCHAR2(20), PROP2 VARCHAR2(20), GEOMETRY MDSYS.SDO_GEOMETRY); ------------------------------------------------------------------------------------ -- Insert spatial metadata ------------------------------------------------------------------------------------ DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='TABLE1'; INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('TABLE1', 'Geometry', MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT('X', 2197290.78, 2401264.08, 0.00000005), MDSYS.SDO_DIM_ELEMENT('Y', 703310.077, 911592.401, 0.00000005) ), NULL); ------------------------------------------------------------------------------------ -- Create spatial index ------------------------------------------------------------------------------------ DROP INDEX TABLE1_SIDX; CREATE INDEX TABLE1_SIDX ON TABLE1(Geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=POINT, sdo_indx_dims=2') ; ------------------------------------------------------------------------------------ -- Insert three POINT records into TABLE1 ------------------------------------------------------------------------------------ Insert into TABLE1 (GID,PROP1,PROP2,GEOMETRY) values (1,'ABC','123', MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(2300050, 814052, NULL), NULL, NULL)); Insert into TABLE1 (GID,PROP1,PROP2,GEOMETRY) values (2,'DEF','456', MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(2300510, 814000, NULL), NULL, NULL)); Insert into TABLE1 (GID,PROP1,PROP2,GEOMETRY) values (3,'GHI','789', MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(2300200, 813810, NULL), NULL, NULL)); ------------------------------------------------------------------------------------ -- Create a sequence for new records. Note that it starts at 4. -- This is because three records were previously inserted. ------------------------------------------------------------------------------------ DROP SEQUENCE "TABLE1_SEQ"; CREATE SEQUENCE TABLE1_SEQ START WITH 4 INCREMENT BY 1; ------------------------------------------------------------------------------------ -- Create TABLE2 table ------------------------------------------------------------------------------------ DROP TABLE TABLE2 CASCADE CONSTRAINT; CREATE TABLE "TABLE2" ( AID NUMBER PRIMARY KEY, PROP1 VARCHAR2(20), PROP3 VARCHAR2(20)); ------------------------------------------------------------------------------------ -- Insert three records into TABLE2 table. -- Note that PROP1 in TABLE1 matches PROP1 in TABLE2. ------------------------------------------------------------------------------------ INSERT INTO TABLE2 (AID, PROP1, PROP3) VALUES (1, 'ABC', '5500'); INSERT INTO TABLE2 (AID, PROP1, PROP3) VALUES (2, 'DEF', '5510'); INSERT INTO TABLE2 (AID, PROP1, PROP3) VALUES (3, 'GHI', '5520'); ------------------------------------------------------------------------------------ -- Create a multi-table view, disable the primary key, and update the spatial metadata. ------------------------------------------------------------------------------------ DROP VIEW 2TABLE_VIEW; CREATE OR REPLACE VIEW 2TABLE_VIEW AS SELECT TABLE1.GID, TABLE1.PROP1, TABLE1.PROP2, TABLE1.GEOMETRY, TABLE2.PROP3 FROM TABLE1, TABLE2 WHERE TABLE1.GID = TABLE2.AID; ALTER VIEW 2TABLE_VIEW ADD PRIMARY KEY (GID) DISABLE; DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='2TABLE_VIEW'; INSERT INTO USER_SDO_GEOM_METADATA SELECT '2TABLE_VIEW', COLUMN_NAME, DIMINFO, SRID FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='TABLE1'; ------------------------------------------------------------------------------------ -- Create INSERT, UPDATE and DELETE triggers. ------------------------------------------------------------------------------------ DROP TRIGGER 2TABLE_VIEW_INS; CREATE OR REPLACE TRIGGER 2TABLE_VIEW_INS INSTEAD OF INSERT ON 2TABLE_VIEW BEGIN INSERT INTO TABLE1 VALUES (:new.GID, :new.PROP1, :new.PROP2, :new.GEOMETRY); INSERT INTO TABLE2 VALUES (:new.GID, :new.PROP1, :new.PROP3); END 2TABLE_VIEW_INS; / DROP TRIGGER 2TABLE_VIEW_UPD; CREATE OR REPLACE TRIGGER 2TABLE_VIEW_UPD INSTEAD OF UPDATE ON 2TABLE_VIEW BEGIN UPDATE TABLE1 SET GID = :new.GID, PROP1 = :new.PROP1, PROP2 = :new.PROP2, GEOMETRY = :new.GEOMETRY WHERE GID = :old.GID; UPDATE TABLE2 SET AID = :new.GID, PROP1 = :new.PROP1, PROP3 = :new.PROP3 WHERE AID = :old.GID; END 2TABLE_VIEW_UPD; / DROP TRIGGER 2TABLE_VIEW_DEL; CREATE OR REPLACE TRIGGER 2TABLE_VIEW_DEL INSTEAD OF DELETE ON 2TABLE_VIEW BEGIN DELETE FROM TABLE1 WHERE GID = :old.GID; DELETE FROM TABLE2 WHERE AID = :old.GID; END 2TABLE_VIEW_DEL; / COMMIT;