Asynchronous triggers can be implemented in different ways, and one common practice is to use a queue. A queue is used to detect changes in the tables, and then based on the base table, specific checks can be done. As we are introducing a new concept of queues here, we will keep the spatial constraint part simple and do the validation checks using an asynchronous trigger.
Oracle provides PL/SQL procedures in the
DBMS_AQ package for creating and using queues. In our example, a simple queue is used to insert information about new rows of data coming into the
LAND_PARCELS table. Then, an asynchronous process can take entries out of the queue and process them one at a time.
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
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));
-- 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’);
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
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:
CONTAINS: A land parcel may contain a building footprint
COVERS: A planning neighborhood covers a land parcel
EQUAL: A planning neighborhood can be equal to a land parcel
TOUCH: A road segment can only touch another road segment
CONTAINS + COVERS: A land parcel may contain and cover a building footprint
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
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:
It finds exactly one row with the given mask
It finds no rows
It fails for some other reason
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
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.