After the schema is decided and the spatial layers are defined, it is useful to add spatial constraints to the database so that many of the data consistency checks for spatial data can be done in the database. These checks can be defined using the same mechanisms used to define the traditional relational database constraints. Typically, these constraints are defined and implemented in GIS applications, but a database is a common data store that is accessed by many GIS applications. So, it makes more sense to define the common data level constraints at the database level so that each application does not have to define these constraints separately.
A spatial constraint is a data consistency check that makes sure that the data stored in the spatial layers follows certain spatial rules. For example, it may be that a building footprint should always be contained within a land parcel or a road should never cross a land parcel. At the database level, there are several different ways to implement these constraints. The most common way is to define triggers on the tables and check for data consistency as soon as a new row is inserted. Then, based on the result of the check, the row of data can be rejected or accepted. This is called synchronous trigger processing. However, there may be cases where the checks can be done at a later time so that the processes inserting the new data are not blocked when checking for data consistency. When a bunch of data is inserted, a background process can go through the new data, run the data consistency checks, and generate a report with invalid rows that the data consistency checks useless. Because the processing is separated from the transaction, this is called asynchronous trigger processing.
We first revisit the previous problem of constraining the spatial layer to contain a single type of data. In the previous section, we showed how to implement this constraint using the spatial index. Now, if we want to constrain a table to contain only points and lines, the index-based constraint mechanism does not work. That was why we used a column or table constraint. But, it is also possible to use a trigger-based mechanism to implement such a constraint.
Consider the CITY_FURNITURE table. We mentioned that this table contains objects such as streetlights and benches. That is, this table can contain point and line type geometries. We define a simple trigger to enforce this constraint on this CITY_FURNITURE table.
Create Or Replace trigger CITY_FURNITURE_TYPE_CHK
before Insert or Update on CITY_FURNITURE
For Each Row
Begin
If ( (:NEW.GEOM.SDO_GTYPE <> 2001) AND (:NEW.GEOM.SDO_GTYPE <> 2002)) Then
RAISE_APPLICATION_ERROR(-20000, ‘Geometry does not have the right type for row with FID’ || to_char(:NEW.FID)) ;
End If;
End;This trigger is defined as a BEFORE trigger. That means the trigger body is executed before the row is actually inserted into the table so that no table data will be changed if there is an error.
Insert Into CITY_FURNITURE Values (100, ‘BENCH’, SDO_GEOMETRY(2003, 2872, null, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(0,0, 1,0, 1,1, 0,1, 0,0))); ERROR at line 1: ORA-20000: Geometry does not have the right type for row with FID 100 ORA-06512: at “BOOK.CITY_FURNITURE_TYPE_CONSTRAINT”, line 3 ORA-04088: error during execution of trigger ‘BOOK.CITY_FURNITURE_TYPE_CONSTRAINT’
As the error message shows, the row with the given FID does not have the right SDO_GTYPE for the geometry. Note that the error message is constructed to be very specific, and has enough information to find the correct row that caused the problem.
Tip
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/supportand register to have the files e-mailed directly to you.
With this trigger mechanism, we can do more complex checks to make sure that the data conforms to the requirements of the applications. Next, we look at an example to check the validity of the geometry data before it is stored in the table. First, we look at a synchronous trigger, and in the following section, we look at an asynchronous trigger to do the same check. If the table data is generated mostly from a user interface (for example, a desktop GIS), doing the checks with a synchronous trigger is better, as the user who is creating the data can be informed about the invalid data as soon as possible. If the data is usually created or loaded using a batch job, an asynchronous trigger is better suited for this situation. We will look at examples of asynchronous triggers in the following section:
Create Or Replace trigger LAND_PARCELS_VALIDATE_V1
after Insert or Update on LAND_PARCELS
For Each Row
Declare
result Varchar2(100);
Begin
result := sdo_geom.validate_geometry_with_context(:NEW.GEOM,0.05);
If (result <> ‘TRUE’) Then
RAISE_APPLICATION_ERROR(-20000, ‘Geometry not valid for row with FID ‘ || to_char(:NEW.FID) || ‘ with error ‘||result) ;
End If;
End;Now let us insert a row with invalid geometry data into this table. Note that we altered the LAND_PARCELS table to add a new GEOD_GEOM column. So here, we just pass a NULL value for that argument.
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); ERROR at line 3: ORA-20000: Geometry not valid for row with FID 3326028 with error 13348 [Element <1>] [Ring <1>] ORA-06512: at “BOOK.LAND_PARCELS_VALIDATE_V1”, line 6 ORA-04088: error during execution of trigger ‘BOOK.LAND_PARCELS_VALIDATE_V1’
Oracle Spatial provides several utility functions that can be used to fix any invalid geometry data. For example, the SDO_UTIL.RECTIFY_GEOMETRY function can fix most of the common errors in the spatial data. These utility functions can be used with ease when the data is already stored in a table. But, the preceding trigger has the problem that when there is an error, it will immediately throw the error and will not let the row be inserted until the geometry is fixed. This makes it hard to use the SDO_UTIL functions to fix the invalid geometry data. We now look at a different trigger that lets the row be inserted into the table even when the geometry data is invalid. The trigger will make a note of the invalid row information in a journal table. After the data is created, users can go back to the journal table and fix the invalid geometry data using SDO_UTIL functions. For this, we first create the LANDPARCELS_INVALID table and use it as the journal table.
Create Table LAND_PARCELS_INVALID (geom SDO_GEOMETRY,
FID Integer,
result Varchar2(100));
Drop trigger land_parcels_validate_v2;
Create Or Replace trigger LAND_PARCELS_VALIDATE_V2
before Insert or Update on LAND_PARCELS
For Each Row
Declare
result Varchar2(100);
Begin
result := sdo_geom.validate_geometry_with_context(:NEW.GEOM, 0.05);
If (result <> ‘TRUE’) Then
Insert Into LAND_PARCELS_INVALID Values(:NEW.GEOM, :NEW.FID, result);
:NEW.GEOM := NULL;
End If;
End;Note that this is also defined as a BEFORE trigger. The geometry is checked for validity, and if it is invalid, a new row is created in the journal table with the geometry and FID values. Also note that the geometry value in the row for the LAND_PARCELS table is set to NULL. This is done so that if the geometry is invalid, then some other processes depending on the geometry data don’t get any wrong data. Once the geometry is rectified, the row will be updated with the valid geometry value.
Once a batch of data is created, we go and update the invalid geometry data in the journal table.
Update LAND_PARCELS_INVALID SET GEOM = SDO_UTIL.RECTIFY_GEOMETRY(GEOM, 0.05); Commit; Select FID From LAND_PARCELS_INVALID Where SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(GEOM, 0.05) <> ‘TRUE’;
If the final statement in the preceding code does not return any rows, then we know that all the invalid data in the journal table is fixed. Now go and update the base table with this valid data.
Update LAND_PARCELS A SET A.GEOM = (Select B.GEOM From LAND_PARCELS_INVALID B Where B.FID = A.FID) Where A.FID IN (Select FID From LAND_PARCELS_INVALID);
This statement updates all the rows for which there are rows in the journal table.