We first create a type to store the messages in the queue. This type is defined based on the information one wants to store in the queue. Make sure this type has enough fields so that the procedure processing the messages from the queue does not need to look for information elsewhere. The message type we use here has the Primary Key (PK) of the LAND_PARCELS table along with the value of the PK. Before we start, we should first drop the previous trigger that we have created on the LAND_PARCELS table.

Drop trigger land_parcels_validate_v1;
Drop trigger land_parcels_validate_v2;

-- Now create the type required for the messages
Create Type validate_q_message_type As Object(
       pk_column Varchar2(32),
        pk_value  Integer,
        table_name Varchar2(32),
        column_name Varchar2(32));

Let us create a queue called VALIDATE_QUEUE with a corresponding queue table, VALIDATE_Q_MESSAGE_TABLE. Once it is created, we need to start the queue so that it can be used.

-- First create the queue table
EXEC DBMS_AQADM.Create_QUEUE_TABLE( queue_table => ‘validate_q_message_table’, queue_payload_type => ‘validate_q_message_type’);

-- Next create the queue
EXEC DBMS_AQADM.Create_QUEUE( queue_name => ‘validate_queue’, queue_table => ‘validate_q_message_table’);

-- And finally start the queue

EXEC DBMS_AQADM.START_QUEUE( queue_name => ‘validate_queue’);

Now the queue is ready to receive messages.

Next, we code the trigger to look at the rows coming into the LAND_PARCELS table and create messages for the queue. We create one message in the queue for each row inserted or updated in the table. The message has information about the columns of the table that are required to access the new or changed geometry.

Create Or Replace trigger LAND_PARCELS_VALIDATE_V3
after Insert or Update on LAND_PARCELS
For Each Row
declare
    queue_options       DBMS_AQ.ENQUEUE_OPTIONS_T;
    message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
    message_id          RAW(16);
    my_message          validate_q_message_type;
Begin
    my_message := validate_q_message_type(‘FID’,:NEW.FID,’LAND_PARCELS’,’GEOM’);
    DBMS_AQ.ENQUEUE(
        queue_name => ‘validate_queue’,
        enqueue_options => queue_options,
        message_properties => message_properties,
        payload => my_message,
        msgid => message_id);
End;

Now insert a row into the LAND_PARCELS table, and again we insert a row with an invalid geometry. Only this time, the row gets inserted into the table and no error is raised. But, a new message gets created in the message queue created by us.

Delete From LAND_PARCELS Where FID = 3326028;

Insert Into LAND_PARCELS values(3326028, ‘0026T05AA’, ‘0026T’,‘055A’, ‘2655’,   ‘HYDE’, ‘1 ST’, ‘HYDE’, ‘ST’,  ‘O’,
SDO_GEOMETRY(2003, 2872, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),SDO_ORDINATE_ARRAY(6006801.83, 2121396.9,  6006890.33, 2121409.23,  6006878.19, 2121495.89,  6006788.97, 2121483.45 )), NULL);

Now look at the message queue to check if there are any messages. The easiest way to do this is to look at the queue table created for our message queue.

Select * From AQ$VALIDATE_Q_MESSAGE_TABLE;

This query should return one row corresponding to the message that our trigger just inserted. As you can see, the table is created using the name that we supplied in the Create_QUEUE_TABLE call with an additional AQ$ prefix.

Next, we will look at some PL/SQL that can be used to browse messages from the queue and to look at the values in the message.

Set SERVEROUTPUT ON;
Declare
    queue_options       DBMS_AQ.DEQUEUE_OPTIONS_T;
    message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
    message_id          RAW(2000);
    my_message          validate_q_message_type;
Begin
    queue_options.dequeue_mode := DBMS_AQ.BROWSE;
    DBMS_AQ.DEQUEUE(
        queue_name => ‘validate_queue’,
        dequeue_options => queue_options,
        message_properties => message_properties,
        payload => my_message,
        msgid => message_id );
    Commit;
    DBMS_OUTPUT.PUT_LINE(
        ‘Dequeued PK Column: ‘ || my_message.pk_name);
    DBMS_OUTPUT.PUT_LINE(
        ‘Dequeued PK value: ‘ || to_char(my_message.pk_value));
    DBMS_OUTPUT.PUT_LINE(
        ‘Dequeued Table: ‘ || my_message.table_name);
    DBMS_OUTPUT.PUT_LINE(
        ‘Dequeued Column: ‘ || my_message.column_name);
End;
/

This should print the following messages in SQLPLUS. Note that this queue does not do anything useful yet:

Dequeued PK Column: FID
Dequeued PK value: 3326028
Dequeued Table: LAND_PARCELS
Dequeued Column: GEOM

If you run the preceding PL/SQL code when the queue is empty, it will wait until some messages are inserted into the queue. So, it will wait for another transaction to insert some data into the LAND_PARCELS table and commit. For browsing the messages, the dequeue mode is set to DBMS_AQ.BROWSE. If you change the mode to DBMS_AQ.REMOVE, it will remove the message from the queue. Next, we will look at the code to remove the message from the queue, process it, and take an action depending on the validity of the geometry.

Set SERVEROUTPUT ON;
Declare
    queue_options       DBMS_AQ.DEQUEUE_OPTIONS_T;
    message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
    message_id          RAW(2000);
    my_message          validate_q_message_type;
    tname               Varchar2(32);
    cname               Varchar2(32);
    pkname              Varchar2(32);
    result              Varchar2(100);
    geometry            SDO_GEOMETRY;
    rect_geom           SDO_GEOMETRY;
Begin
    queue_options.dequeue_mode := DBMS_AQ.REMOVE;
    DBMS_AQ.DEQUEUE( queue_name => ‘validate_queue’,
        dequeue_options => queue_options,
        message_properties => message_properties,
        payload => my_message, msgid => message_id );

    Execute IMMEDIATE ‘ Select ‘||
            SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.column_name)||
            ‘, sdo_geom.validate_geometry_with_context(‘ ||
            SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.column_name)||
            ‘, 0.05) From ‘ ||
            SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.table_name)||
            ‘ Where ‘ ||
            SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.pk_column)|| ‘ = :pkvalue ‘
    Into geometry, result USING my_message.pk_value ;
    If (result = ‘TRUE’) then
     return;
    else
     rect_geom := sdo_util.rectify_geometry(geometry, 0.05);
     result := sdo_geom.validate_geometry_with_context(rect_geom, 0.05);
     If (result = ‘TRUE’) then
       EXECUTE IMMEDIATE ‘ Update ‘||
            SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.table_name)||’ set ‘||
            SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.column_name)||
            ‘ = :geometry Where ‘||
            SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.pk_column)|| ‘ = :pkvalue ‘
       USING rect_geom, my_message.pk_value;
     Else
      RAISE_APPLICATION_ERROR(-20000, ‘Geometry cannot be fixed for row with ‘||
          my_message.pk_value|| ‘ in table’ ||
          my_message.table_name);
     End If;
    End If;
dbms_output.put_line(result);
    Commit;
End;

There are a few important things to note in this PL/SQL code. The message that we have created in the queue is very generic so that the same code can be used to process messages for many spatial tables. As the table name, column name, primary key column, and Primary Key value are all retrieved from the message, the same queue and dequeue mechanism can be used for a set of tables in the schema. This code can be easily modified to be a procedure, and it can be executed in a loop while waiting for messages in the queue. It can also be turned on when there is less activity in the DB to reduce the load. You also need to think about what happens when the geometry cannot be fixed using the rectify function. We left that part open in this code example as it depends on other available tools for fixing the invalid geometry data.

So far, we have only looked at constraints that mainly deal with the validity of data or type of data present in our spatial tables. We will now look at more complex scenarios that define relationships between objects in different tables. These rules enforce data quality and make sure that the objects in the schema follow certain spatial relationship rules. An example of such a constraint is the one that specifies that the building footprints must be contained within the land parcels. Similarly, a land parcel must be contained inside a planning zone.

These constraints can be implemented using triggers (synchronous or asynchronous) as described in the previous sections. They can also be implemented via a spatial constraints table that would provide a flexible and generic data-driven approach. With this data-driven approach, a generic model can be used to enforce constraints between different spatial layers in our schema. A rules table is first created to store all the allowed spatial relationships between different spatial layers. Then, triggers are used on spatial layers to enforce these rules. The advantage of creating a rule table is that all the allowed rules are stored in one place instead of spreading them across the different triggers. This makes it easy to document the rules so that any GIS tool can easily look up the available rules.

We first look at the rules that will describe possible relationships between objects for this schema. These rules can be defined as MASK values and their combinations that are valid for the SDO_GEOM.RELATE function. We will use this function to enforce the following rules in our schema:

Next, we look at how to create a rule-based constraint between the BUILDING_FOOTPRINTS tables and LAND_PARCELS tables. The rule specifies that a building should be inside a land parcel (either completely inside or touching the boundary) And a building cannot exist without containing a land parcel. We first create a relationship table that specifies which land parcel contains which building footprint. Note that this information can always be obtained by executing a spatial query. But sometimes it is easier to persistently store this information as it is computed anyway to enforce the data consistency.

We will also enforce the constraint that the only possible values allowed for the SPATIAL_RELATIONSHIP column in this table are CONTAINS and COVERS. That is, a land parcel can only contain or cover a building footprint. We also want to enforce the UNIQUE constraint on the BUILDING_ID column, as each building footprint can only have one parent land parcel record in our schema.

Create Table Building_Land_Parcel 
           (Land_parcel_id Varchar2(9),
            Building_id number,
            spatial_relationship Varchar2(100));

Alter Table Building_Land_Parcel add Constraint BLP_PK
  UNIQUE (Building_ID) enable;

Alter Table Building_Land_Parcel add Constraint BLP_FK_LP
  Foreign Key (Land_Parcel_ID) REFERENCES LANDPARCELS(BLKLOT) enable;

Alter Table Building_Land_Parcel add Constraint BLP_FK_BF
  Foreign Key (BUILDING_ID) REFERENCES BUILDING_FOOTPRINT(BUILDING_ID) enable;

Alter Table BUILDING_LAND_PARCEL add Constraint BLP_Spatial_CHECK
CHECK ( spatial_relationship in (‘COVERS’, ‘CONTAINS’) );

Once we have the relationship table created, we next create a utility function that can be used to check for different spatial relationships between objects of different spatial layers. This is a generic procedure that can be used for many different spatial layer pairs with minor changes.

Create Or Replace PROCEDURE Check_Relation (tname varchar2, 
                                           cname varchar2,
                                           pk_name varchar2,
                                           geometry SDO_GEOMETRY,
                                           mask  varchar2,
                                           pk_value OUT Integer,
                                           relation OUT varchar2) AS
stmt Varchar2(200);
rel_mask  Varchar2(200);
Begin
    rel_mask := ‘MASK=’||mask;
    stmt := ‘ Select ‘||
            SYS.DBMS_ASSERT.ENQUOTE_NAME(pk_name)||
            ‘, SDO_GEOM.RELATE(‘||SYS.DBMS_ASSERT.ENQUOTE_NAME(cname, false)||
            ‘ ,  ‘’DETERMINE’’, ‘||
            ‘:geometry,  0.05) From ‘||
            SYS.DBMS_ASSERT.ENQUOTE_NAME(tname, false)||
            ‘ Where SDO_RELATE(‘|| 
SYS.DBMS_ASSERT.ENQUOTE_NAME(cname)||
              ‘, :geometry, :rel) = ‘’TRUE’’ ‘;
   Begin
    EXECUTE IMMEDIATE stmt Into  pk_value, relation
           USING geometry, geometry, rel_mask;

    EXCEPTION When NO_DATA_FOUND Then
     pk_value := NULL;
        When OTHERS Then
           raise;
   End;
End;

This procedure takes spatial layer information (table name, column name) to search for the given geometry with the specified spatial relationship. It returns the Primary Key of the spatial layer so that the row that satisfies the specified spatial relationship is identified easily for further processing. We use this information in the trigger to populate the rows in the relationship table that we have defined in the preceding code. It is very important to handle the exceptions in this procedure so that when the insert fails, the users will know exactly what failed. For this case, if valid values for table name and column name are passed in, the SDO_RELATE query can have three possible results:

The first case is the valid case for our trigger, so we don’t need to do error processing for this case. The second case means there is no corresponding land parcel that either contains or covers the given building footprint. We let the trigger handle this error, so in this procedure, we just pass a NULL value for FID. The third case means something is wrong with the parameters or the tables involved in the query. In this case, we raise the actual error so that the user can further process the error condition.

Next, we create a trigger on the BUILDING_FOOTPRINTS table to check each building footprint that is newly created or updated for containment with the LAND_PARCELS table.

Create Or Replace TRIGGER BF_LP_RELATION
after Insert or Update on BUILDING_FOOTPRINTS
FOr EACH ROW
Declare
   building_id number;
   FID Number;
   relation Varchar2(100);
Begin
   Check_Relation(‘LAND_PARCELS’, ‘GEOM’,  ‘FID’,
                        :NEW.geom, ‘COVERS+CONTAINS’, fid, relation );
   If ( (FID is NULL) Or
        (relation <> ‘CONTAINS’ AND relation <> ‘COVERS’) ) then
     RAISE_APPLICATION_ERROR(-20000, ‘BuildingFootPrint with ID ‘||
      to_char(:NEW.fid)|| ‘ is not inside any landparcel’);
   Else
     Insert Into Building_Land_Parcel Values(fid, :NEW.fid, relation);
   End If;
End;

This trigger first checks the relationship of the current geometry that is being inserted into the BUILDING_FOOTPRINT table. If it finds a land parcel that contains the footprint, this information is inserted into the relationship table. If there is an error (that is, no containing land parcel is identified), the trigger will raise an error and the insert into the footprint table fails. This can also be implemented as an asynchronous trigger so that the insert into the footprint table is allowed even if there is an error, but some error information is recorded in the relationship table so that it can be checked and fixed later.

The trigger that we defined here will create the relationship for the new rows that are coming into the footprint table. In some cases, the data for these tables might have been populated using a batch process before this trigger is enabled. In such cases, how do we populate the relationship table? Next, we describe a process that looks at all the existing records in the footprint table and finds the land parcel that contains it. This can be a time-consuming process, as it has to check each footprint to find the corresponding land parcel.

Insert Into building_land_parcel
Select lp.fid, b.fid,

       sdo_geom.relate(lp.geom, ‘determine’, b.geom, 0.05)
From BUILDING_FOOTPRINTS b, LAND_PARCELS lp
Where SDO_RELATE(lp.geom, b.geom, ‘mask=CONTAINS+COVERS’) = ‘TRUE’;

This SQL takes each building footprint and finds the corresponding land parcel. While this SQL is efficient in execution, there is no error reporting as part of this SQL. If there is a building footprint without a corresponding record from the LAND_PARCELS table, there won’t be any record of that missing entry in the BUILDING_LAND_PARCEL table. The user should check the number of rows in the BUILDING_LAND_PARCEL table to make sure that there is one record corresponding to each building footprint in this table. This check is required only if the preceding SQL is used to populate this relationship table. In the normal processing of the building footprints, when new records arrive, the trigger that we have created on the BUILDING_FOOTPRINTS table will take care of these error conditions.