Applying and Extending Oracle Spatial: This guide takes you straight into the attributes of Oracle Spatial and teaches you to extend, apply, and combine them with other Oracle and open source technologies. A vital manual for solving everyday problems.

We use a schema with 12 tables to represent a spatial database for a city. This schema has tables to represent administrative areas managed at the city level, such as land parcels and neighborhoods, along with tables to manage natural features such as water boundaries.

The LAND_PARCELS table has information about land at the lowest administrative level of the city. Buildings have to be fully contained in these land parcels. A table called BUILDING_FOOTPRINTS has information about all the buildings in the city. This table has the footprint of each building along with other information, such as name, height, and other attributes. Sets of neighborhoods are defined as a collection of land parcels to create more granular administrative areas. These neighborhoods are stored in the PLANNING_NEIGHBORHOODS table. There is a master table, BASE_ADDRESSES, to store information about all the valid street addresses in the city. Every record in the BUILDING_FOOTPRINTS table must have one parent record in this master address table. Note that the master address table does not list all the addresses of the apartments in a building. Rather, it stores one record for each street level address. So, each record in the BUILDING_FOOTPRINTS table has only one corresponding record in the master address table.

There is also a master table, ROADS, that is used to store information about all the roads in the city. ROADS stores one record for each named road in the city so that all common information for the road can be stored together in one table. This is the only table in the schema without any geometry information. Each road in turn maps to a set of road segments that are stored in the ROAD_CLINES table. This table is used to store the geometric representation of center lines of road segments. This table also stores information about address ranges on these road segments. Road segments typically have different address ranges on the left side of the road and on the right side of the road. Each road segment also has a parent ROAD_ID associated with it from the ROADS table.

A city usually manages sidewalks and other assets, such as street lamps, trashcans, and benches that are placed on these sidewalks. The SIDEWALKS table stores the information for all the sidewalks managed by the city. The CITY_FURNITURE table stores all the data corresponding to the assets, such as benches, streetlights, and trashcans.

The ORTHO_PHOTOS table stores the collected information using aerial photography. The raster information stored in this table can be used to look for changes over time for the built-in features of the city.

The water features of the city are stored in two different tables: the WATER_LINE table is used to store the water line features, such as creeks, rivers, and canals. The WATER_AREA table is used to store area features, such as lakes, rivers, and bays. The following figure shows the entity-relationship (E-R) diagram for this data model:

The following figure shows the further E-R diagram for same data model:

Create a user called BOOK and assign it a password. Load the script <schema_load.sql> and it will create the tables required for running the examples described in this book. It will also create the Oracle Spatial metadata required for these tables. The following privileges are granted to the BOOK user:

grant connect,resource to Book identified by <password>;
grant connect, resource to book;
grant create table to book;
grant create view to book;
grant create sequence to book;
grant create synonym to book;
grant create any directory to book;
grant query rewrite to book;
grant unlimited tablespace to book;

Oracle Spatial requires certain metadata before the spatial data can be meaningfully used by applications. The database views that contain this metadata also act as a catalog for all the spatial data in the database. There are two basic views defined to store this metadata information: USER_SDO_GEOM_METADATA and ALL_SDO_GEOM_METADATA. The USER_ view is used to create a metadata entry for a single SDO_GEOMETRY column within a database table or view. An entry must be created for each SDO_GEOMETRY column within a table; entries for SDO_GEOMETRY columns in views are optional.

If a table has more than one column of type SDO_GEOMETRY, then there is one metadata entry for each column of spatial data in that table. The ALL_ view shows all of the spatial layers that can be accessed by the current user. If a user has the Select grant on another user’s table with SDO_GEOMETRY columns, the first user can see the metadata entries for those tables in the ALL_ view. The views are set up so that owners of the spatial tables or views can create the metadata for them. And, the owner of a layer can grant read access to a layer to other users in the system. Granting a Select privilege on the table or view to other users will let them see the metadata for these tables and views. The ALL_ view displays all the spatial tables owned by the user along with other spatial tables for which the current user has read access.

Each SDO_GEOMETRY object has a Spatial Reference System (SRS) associated with it, and all the SDO_GEOMETRY objects in a column should have the same SRS. In Oracle Spatial, a Spatial Reference ID (SRID) is used to associate an SRS with SDO_GEOMETRY objects. There are cases (for example, engineering drawings) where there is no SRS associated with an SDO_GEOMETRY object. In such cases, a NULL SRID is used to denote that the spatial data has no spatial reference information. An SRS can be geographic or non-geographic. A geographic SRS is used when the spatial data is used to represent features on the surface of the Earth. These types of SRS usually have a reference system that can relate the coordinates of the spatial data to locations on Earth. A unit of measurement is also associated with an SRS so that measurements can be done using a well-defined system. A non-geographic SRS is used when the spatial data is not directly related to locations on Earth. But these systems usually have a unit of measurement associated with them. Building floor plans is a good example of spatial data that is often not directly related to locations on Earth.

A geographic system can be either geodetic or projected. Coordinates in a geodetic system are often described using longitude and latitude. In Oracle Spatial, the convention is to use longitude as the first coordinate and latitude as the second coordinate. A projected system is a Cartesian system that is defined as a planar projection based on the datum and projection parameters.

Before an entry is created for a layer of data, the SRID associated with the data should be identified along with the tolerance to be used for the spatial layer. All the spatial data has an inherent accuracy associated with it. Hence, the tolerance value used for a spatial layer is very important and should be determined based on the accuracy of the data. Once these two values are identified, you are ready to create the metadata for the spatial layer.

Oracle Spatial supports hundreds of SRSs, and it is very important to choose the right SRS for any given data set. The definition of an SRS can be easily obtained by looking at the well-known text (WKT) for that SRS. The WKTs for all the SRSs supplied as part of Oracle Spatial are available from the MDSYS.CS_SRS view. In addition to this view, there are several other metadata tables under MDSYS that contain more details on how these SRSs are defined. Oracle Spatial also supports the EPSG standard-based SRSs. SRS in Oracle Spatial is flexible and allows users to define new reference systems if they are not present in the supplied SRSs. We will revisit user-defined SRSs in the following chapters.

The tables used in our sample schema contain data that is geographically referenced. Spatial metadata can be created using Insert statements into the USER_SDO_GEOM_METADATA view. This view is defined as a public-writable view on top of MDSYS.SDO_GEOM_METADATA_TABLE that is used to store metadata for all the spatial columns in the database. Let us look at the metadata creation process for some of the spatial tables. Most of the tables used in the current schema have spatial data in the California State Plane Zone 3 Coordinate System. In Oracle Spatial, the corresponding SRID for this SRS is 2872. This coordinate system has foot as the unit of measurement and we will use 0.05 as our tolerance (that is, five-hundredths of a foot). The metadata is created using the Insert statement as shown in the following code:

Insert Into USER_SDO_GEOM_METDATA Values (‘LAND_PARCELS’, ‘GEOM’, SDO_DIM_ARRAY(SDO_DIM_ARRAY(SDO_DIM_ELEMENT(‘X’, 5900000, 6100000, 0.05), SDO_DIM_ELEMENT(‘Y’,2000000, 2200000, 0.05)), 2872);

The SDO_DIM_ELEMENT object is used to specify the lower and upper bounds for each dimension of the coordinate system along with the tolerance value. The metadata allows one entry for each dimension, even though it is very common to use the same tolerance value for the X and Y dimensions. When storing 3D data, it is very common to use a different tolerance value for the Z dimension.

The BASE_ADDRESSES table has geometries stored in two columns: GEOMETRY and GEOD_GEOMETRY. The GEOMETRY column has data in the 2872 SRID, while the GEOD_GEOMETRY column has data in longitude and latitude. As this is a geodetic system, the tolerance for such systems is required to be in meters. So, a tolerance of 0.05 means a tolerance of 5cm. For geodetic data, it is recommended that the tolerance should not be less than 5cm for all of the topology and distance-based operations.

Insert Into USER_SDO_GEOM_METDATA Values(‘BASE_ADDRESSES’,
 ‘GEOD_GEOM’, SDO_DIM_ARRAY((SDO_DIM_ELEMENT(‘Longitude’,
 -122.51436, -122.36638, .05),
 SDO_DIM_ELEMENT(‘Latitude’, 37.7081463, 37.8309382,
.05)), 
8307);

As this is a geodetic system, the longitude range goes from -180 to 180 and the latitude range goes from -90 to 90. Even though it is normal practice to use these ranges for the metadata entry, many developers use the actual ranges spanned by the SDO_GEOMETRY object. Mapping tools and applications typically use this extent from the metadata to compute the initial extent of the data in each column of the spatial data.

Any application looking for all the spatial columns in the database should select the data from the ALL_SDO_GEOM_METADATA view. This will return one row for each column of spatial data in the database that is visible to the current user.

Open Geospatial Consortium (OGC) defines a different set of standardized metadata views. OGC standard metadata can be defined using a new set of tables or views in Oracle Spatial. For a simple solution for the OGC metadata schema, we will show a view-based implementation using the Oracle Spatial metadata table. All Oracle supplied packages, functions, and types of Oracle Spatial are in the MDSYS schema. It is generally not recommended to create any user objects under this schema as it might cause problems during database upgrades. Oracle also supplies another predefined schema called MDDATA that can be used for Oracle Spatial-related user objects that are general purpose in nature. We use this MDDATA schema to create the OGC metadata views. This user comes locked and it is recommended that you do not unlock this user. But, it does require a few privileges to make the following code work, so grant those privileges as required.

Connect to the database as a user with SYSDBA privileges and execute all the following steps as the MDDATA user by changing the current schema to MDDATA. We need to grant an explicit Select privilege on SDO_GEOM_METADATA_TABLE to MDDATA.

Alter session set current_schema=MDDATA;
GRANT Select on MDSYS.SDO_GEOM_METADATA_TABLE to MDDATA;

The OGC standard requires the geometry type as part of the metadata view. But, this is not part of the MDSYS owned metadata view and has to be computed based on the geometry table information stored in the MDSYS table. So, first define a function that can compute the geometry type based on the rows in the spatial tables. Note that this function just looks at the first non-NULL geometry and returns the type of that geometry. Users can modify this to make it look at the whole table to decide on the geometry type, but it can be a very expensive operation.

Create Or Replace Function MDDATA.GET_GEOMETRY_TYPE (tsname
  varchar2, tname varchar2, cname varchar2) Return Number IS
gtype number;
Begin
   Begin
    execute immediate
      ‘ Select a.’||
       SYS.DBMS_ASSERT.ENQUOTE_NAME(cname, false)||
      ‘.sdo_gtype From ‘||
       SYS.DBMS_ASSERT.ENQUOTE_NAME(tsname, false)||’.’||
       SYS.DBMS_ASSERT.ENQUOTE_NAME(tname, false)||
      ‘ a Where a.’||
       SYS.DBMS_ASSERT.ENQUOTE_NAME(cname, false)||
      ‘ is not null and rownum < 2’
      Into gtype;
    Return gtype MOD 100;
    EXCEPTION When OTHERS Then
       Return 4;
   End;
End;

Notice all the uses of the ENQUOTE_NAME function from the SYS.DBMS_ASSERT package. This is used to avoid any possible SQL injection issues typically associated with functions that create SQL statements using the user supplied SQL. As we are creating a general purpose function that can be invoked by any user directly or indirectly, it is a good idea to protect the function from any possible SQL injection.

Next, we define an OGC metadata view to see all the rows from the MDSYS owned metadata table.

Create Or Replace View MDDATA.OGC_GEOMETRY_COLUMNS As
Select GM.SDO_OWNER        As F_TABLE_SCHEMA,
       GM.SDO_TABLE_NAME   As F_TABLE_NAME,
       GM.SDO_COLUMN_NAME  As F_GEOMETRY_COLUMN,
       Get_Geometry_Type(GM.sdo_owner,
                         GM.sdo_table_name,
                         GM.sdo_column_name)
                         As GEOMETRY_TYPE,
       (Select count(*)
          From Table(GM.SDO_DIMINFO)
       )                 As COORD_DIMENSION,
       GM.SDO_SRID         As SRID
  From MDSYS.SDO_GEOM_METADATA_TABLE GM;

And finally, we define a user view that will show all the geometry columns that are visible to the current user.

Create Or Replace View  GEOMETRY_COLUMNS  As
Select  b.F_TABLE_SCHEMA ,
        b.F_TABLE_NAME ,
        b.F_GEOMETRY_COLUMN,
        b.COORD_DIMENSION,
        b.SRID,
        b.GEOMETRY_TYPE
From MDDATA.OGC_GEOMETRY_COLUMNS b,
     ALL_OBJECTS a
Where  b.F_TABLE_NAME = a.OBJECT_NAME
  And  b.F_TABLE_SCHEMA = a.OWNER
  And  a.OBJECT_TYPE in (‘TABLE’, ‘SYNONYM’, ‘VIEW’);

Grant Select On MDDATA.GEOMETRY_COLUMNS to public;
Create PUBLIC SYNONYM GEOMETRY_COLUMNS FOR
MDDATA.GEOMETRY_COLUMNS;

Tolerance is used in Oracle Spatial to associate a level of precision with the data and to check the validity of geometries among other things. Tolerance should be derived based on the resolution and accuracy of the data. If the devices or methods used to collect the spatial data are correct up to a five-meter resolution, the tolerance for that layer should be set to 5 meters. The actual tolerance value, inserted into the metadata view depends on the real-world tolerance value and the unit of measurement used in the coordinate system is used for the column of spatial data. For example, let the tolerance for the spatial data be 5 centimeters and the unit of measurement of the coordinate system used for the spatial column is feet. Then, the five-centimeter value should first be converted to feet (1 centimeter is 0.032 feet)—this comes out to be 0.164 feet. So, you use a value of 0.164 for tolerance in the metadata.

In practice, Oracle Spatial uses the following rules based on tolerance to determine if the geometry is valid or not. These are in addition to other topological consistency rules (as described by the OGC Simple Feature Specification) used to check the validity of geometries:

If a spatial column in a table contains data of one single geometry type (for example, polygon or line-string, but not both), we can say that spatial data in that column is homogeneous. In other situations, a column may contain data from one or more geometry types (heterogeneous representation). For example, the spatial description of a rare and endangered flora object may normally be a single plant (a plant via a point), but in other situations, it may be an area (a patch via a polygon). Consider the CITY_FURNITURE table that is used for storing city assets like benches, trashcans, and streetlights. The geometry for the benches is represented using line-strings, while streetlights and trashcans are represented with points. It is perfectly correct, semantically, to store different types of observations within a single geometry column. However, while some mapping software systems can cope with multiple geometry types per SDO_GEOMETRY column, others, such as some traditional GIS packages, require homogeneity. We will describe how to achieve this next, when a column in the table has heterogeneous data.

We define three views on top of the CITY_FURNITURE table corresponding to each of the types of data stored in the table. This table has three classes of objects: benches, trashcans, and streetlights. After the views are defined, we also need to create metadata entries for these views in USER_SDO_GEOM_METADATA so that any GIS tool can discover these views as if they are tables.

Create the database views corresponding to each of the three types of data stored in the CITY_FURNITURE table.

 -- DDL for View CITY_FURN_BENCHES
Create Or Replace FORCE VIEW
 CITY_FURN_BENCHES (FID, FEATTYPE, GEOM) As
  Select FID, FEATTYPE, GEOM From CITY_FURNITURE
 Where FEATTYPE=’BENCH’;
-- DDL for View CITY_FURN_LIGHTS
Create Or Replace FORCE VIEW
 CITY_FURN_LIGHTS (FID, FEATTYPE, GEOM) As
  Select FID, FEATTYPE, GEOM  From CITY_FURNITURE
Where FEATTYPE=’LIGHT’;
-- DDL for View CITY_FURN_TRASHCANS
Create Or Replace FORCE VIEW
 CITY_FURN_TRASHCANS  (FID, FEATTYPE, GEOM) As
  Select FID, FEATTYPE, GEOM From CITY_FURNITURE
Where FEATTYPE=’TRASHCAN’;

The preceding examples show how to use other relational attributes to create the required views. Another way to do this is to constrain based on the SDO_GTYPE attribute of the SDO_GEOMETRY column. The following example shows how to do this for one of the preceding views, as the rest can be done with similar SQL:

-- DDL for View CITY__FURN_BENCHES
Create Or Replace FORCE VIEW
 CITY_FURN_BENCHES (FID, FEATTYPE, GEOM) AS
  Select FID, FEATTYPE, GEOM From CITY_FURNITURE A
 Where A.GEOM.SDO_GTYPE = 2002;

Now create the metadata for each of these views so that any GIS can access this as if it is stored in a separate table. Note that these additional metadata entries are not required for the correct usage of Oracle Spatial. They are created only to facilitate the GIS tools that don’t support heterogeneous data in spatial columns.

Insert Into USER_SDO_GEOM_METADATA Values (
‘CITY_FURN_BENCHES’, ‘GEOM’,
SDO_DIM_ARRAY(SDO_DIM_ELEMENT(‘X’, 5900000, 6100000, .05),
SDO_DIM_ELEMENT(‘Y’, 2000000, 2200000, .05)), 2872);

Insert Into USER_SDO_GEOM_METADATA Values(
‘CITY_FURN_LIGHTS’, ‘GEOM’,
SDO_DIM_ARRAY(SDO_DIM_ELEMENT(‘X’, 5900000, 6100000, .05),
SDO_DIM_ELEMENT(‘Y’, 2000000, 2200000, .05)), 2872);

Insert Into USER_SDO_GEOM_METADATA Values(
‘CITY_FURN_TRASHCANS’, ‘GEOM’,
SDO_DIM_ARRAY(SDO_DIM_ELEMENT(‘X’, 5900000, 6100000, .05),
SDO_DIM_ELEMENT(‘Y’, 2000000, 2200000, .05)), 2872);

Applications typically look at the ALL_SDO_GEOM_METADATA view to see the spatial tables available in the database for a given user. If you select the data from this view, now you will see 11 rows returned: 8 rows corresponding to tables and 3 rows corresponding to the views defined in the CITY_FURNITURE table. From an application point of view, it does not make any difference whether this data is stored in a view or a table. It will all look the same to the application.

Sometimes it is useful to constrain the type of spatial data stored in the table to be homogeneous. For example, the ROAD_CLINES table should contain only linear geometries, as the roads are usually geometries of line type. This can be done by constraints that can be imposed by the spatial index defined in the ROAD_CLINES table. While creating the spatial index, provide the LAYER_GTYPE keyword and specify the type of data that will be stored in this table.

-- DDL for Index ROAD_CLINES_SIDX
  Create Index ROAD_CLINES_SIDX ON ROAD_CLINES (GEOM)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS
  (‘LAYER_GTYPE=LINE’);

Now, if you try to insert a row with a geometry that has a different SDO_GTYPE attribute than 2002, it will raise an error.

Insert Into ROAD_CLINES Values ( 198999, 402, 0, 0, 2300, 2498, 190201, 23564000,   23555000, 94107, 10,  ‘Y’, ‘DPW’, ‘Potrero Hill’, ‘190201’,  ‘03RD ST’, ‘3’, ‘3RD’,
SDO_GEOMETRY(2001, 2872, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), 
SDO_ORDINATE_ARRAY( 6015763.86, 2104882.29)));

*
ERROR at line 1:
ORA-29875: failed in the execution of the ODCIINDEXInsert routine
ORA-13375: the layer is of type [2002] while geometry inserted has type [2001]
ORA-06512: at “MDSYS.SDO_INDEX_METHOD_10I”, line 720
ORA-06512: at “MDSYS.SDO_INDEX_METHOD_10I”, line 225

The error message clearly indicates that the row that is currently being inserted has geometry with the wrong SDO_GTYPE attribute. This is the easiest way to strictly enforce the GTYPE constraints on the spatial data. However, this has the problem of rejecting the whole row when the geometry type does not match the LAYER_GTYPE keyword. And it is also not easy to log these cases as the error is thrown and the database moves on to process the next Insert statement. In some cases, the user might still want to insert the row into the table, but record the fact that there is invalid data in the row. Users can then come back and look at all the invalid entries and fix the issues. We will describe a few methods to do this logging and error processing later in this chapter.

Specifying the layer_gtype keyword is not the only way to constrain the type in a spatial layer. One can also use table level constraints to achieve the same result. With constraints, users get the additional benefit of specifying more complex constraints, such as allowing only points and lines in a layer. However, if only a single geometry type constraint is required, it is better to implement that constraint using the LAYER_GTYPE method as this is more efficient than the check constraint. These constraints can also be enforced with database triggers, and these trigger-based constraints are discussed in a later section.

Alter Table CITY_FURNITURE ADD Constraint city_furniture_gtype_ck
CHECK ( geom.sdo_gtype in (2002, 2001) );
Insert Into CITY_FURNITURE Values (432432, 'BENCH',
SDO_GEOMETRY(2003,2872,NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARRAY(6010548.53, 2091896.34, 6010550.45,2091890.11)));

This will fail with the following error:

ERROR at line 1:
ORA-02290: check Constraint (BOOK.CITY_FURNITURE_GTYPE_CK) violated

Similarly, this approach can be useful for an OBJTYPE column check in the CITY_FURNITURE table.

Alter Table CITY_FURNITURE ADD Constraint city_furniture_type_ck
  CHECK ( feattype in (‘BENCH’,’LIGHT’,’TRASHCAN’) );

Insert Into  CITY_FURNITURE Values (432432, ‘LIGHTS’,
SDO_GEOMETRY(2001,2872, SDO_POINT_TYPE(6010548.53, 2091896.34,
  NULL), NULL, NULL));
ERROR at line 1:
ORA-02290: check Constraint (BOOK.CITY_FURNITURE_TYPE_CK) violated

Now these two constraints are checking two independent columns, but what we really need is a more complex check to ensure each value of OBJTYPE has the corresponding SDO_GEOMETRY with the right type. That is, we want to make sure that TRASHCAN and LIGHT types have a point geometry and BENCH has a line geometry.

Alter Table CITY_FURNITURE Drop Constraint  city_furniture_gtype_ck;
Alter Table CITY_FURNITURE Drop Constraint  city_furniture_type_ck;


Alter Table CITY_FURNITURE
  ADD Constraint city_furniture_objtype_geom_ck
  CHECK (
         ( (“FEATTYPE”=’TRASHCAN’ Or “FEATTYPE”=’LIGHT’) 
           AND  “GEOM”.”SDO_GTYPE”=2001
         )
         Or (“FEATTYPE”=’BENCH’ AND “GEOM”.”SDO_GTYPE”=2002)
         /* Else Invalid combination */
       ) ;


Insert Into CITY_FURNITURE Values  (432432, ‘BENCH’,
SDO_GEOMETRY(2001,2872, SDO_POINT_TYPE(6010548.53, 2091896.34, NULL), NULL, NULL));

ERROR at line 1:
ORA-02290: check Constraint (BOOK.CITY_FURNITURE_TYPE_CK) violated

In some situations, it is beneficial to have multiple representations for the same geometric feature. For example, an address usually has a point representation for its location. If a footprint of a building is associated with the address, then that footprint will be represented as a polygon. In some cases, a building might have many different point locations associated with it. One point may allow a GIS application to draw an icon for the building depending on its function (for example, a fire station). Another point may allow the building to be labeled with its street address, and finally another one may show an alternate location that is used for main delivery or emergency services entry at the back of the building.

Similarly, a land parcel table can have an interior point of the parcel represented as point geometry in addition to the polygon representation. For a visualization application, it is sometimes useful to represent the land parcel as a point. When a map is displayed at a smaller scale (city level), the map will be cluttered if each land parcel is displayed as a polygon. In such cases, if land parcels are displayed as points with a suitable icon, the map will be less cluttered. When the map is displayed at a larger scale (street level), the same land parcel can be displayed as a polygon. Oracle Spatial allows such multiple representations by allowing multiple SDO_GEOMETRY columns in the same table.

We first start with the BUILDING_FOOTPRINTS table and alter it to add an additional SDO_GEOMETRY column to allow the street address to be represented at the center of the building via a point feature. We can use a spatial function that can compute a point inside a polygon automatically to populate this column.

We first alter the table to add a new SDO_GEOMETRY column. For this, we pick ADDRESS_POINT as the column name.

Alter Table BUILDING_FOOTPRINT ADD (ADDRESS_POINT SDO_GEOMETRY);

We can then update the BUILDING_FOOTPRINT table and compute a value for the new column using the INTERIOR_POINT function in the SDO_UTIL package. This is a nice utility function that can compute a point that is interior to the polygon geometry. This function works even if the geometry has multiple rings or multiple elements. If the geometry has multiple rings, it will find the interior point inside the largest ring. The same rule applies when the geometry has multiple elements. It will find the interior point inside the largest ring of the polygon. Note that this is a fairly expensive operation and can take a few minutes on the large BUILDING_FOOTPRINT table.

Update BUILDING_FOOT_PRINT SET ADDRESS_POINT = sdo_util.interior_point(GEOM, 0.05);

This function takes the geometry as input along with the tolerance value associated with the geometry. The return value is a point geometry that is guaranteed to be inside the source polygon geometry.

Once this column is populated, a new metadata entry needs to be created for this and, if required, a spatial index should also be created.

Insert Into USER_SDO_GEOM_METADATA Values(
‘BUILDING_FOOTPRINT’, ‘ADDRESS_POINT’,
SDO_DIM_ARRAY(SDO_DIM_ELEMENT(‘X’, 5900000, 6100000, .005),
SDO_DIM_ELEMENT(‘Y’, 2000000, 2200000, 0.05)), 2872);

  Create Index BUILDING_FOOTPRINT_PT_SIDX ON
  BUILDING_FOOTPRINT (ADDRESS_POINT)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX ;

In some cases, the alternate representation for a feature might be a geometry with the same shape but in a different coordinate system or with reduced resolution. Data in different coordinate systems is useful in cases where the data has to be published over the web in a different coordinate system than what is stored in the database schema.

For the sample schema, the data is stored in the California state plane, but this data might be published into a state-level database where the coordinate system is Geodetic 8307. In such cases, another column of SDO_GEOMETRY can be added to each of the tables in the schema and set to use SRID 8307.

We take the LAND_PARCELS table and add another column to store the geometry with SRID 8307. We create the appropriate metadata for the geodetic system and create an index on the new spatial column.

Alter Table LAND_PARCELS  ADD (GEOD_GEOM SDO_GEOMETRY);

Update LAND_PARCLES SET GEOD_GEOM = SDO_CS.TRANSFORM(GEOM, 8307);

Now the GEOD_GEOM column has the geometry for the land parcels in the Geodetic system. We now create the metadata for the new spatial layer and create an index for it. Note that the tolerance now should be specified in meters, and we will use 0.05 meters for tolerance.

Insert Into USER_SDO_GEOM_METADATA Values(
‘LAND_PARCELS’, ‘GEOD_GEOM’,
SDO_DIM_ARRAY(SDO_DIM_ELEMENT(‘Longitude’,  -180, 180, 0.05),
SDO_DIM_ELEMENT(‘Latitude’,  -90, 90, 0.05)),  8307);
  
Create Index LAND_PARCELS_GEOD_SIDX ON LAND_PARCELS(GEOD_GEOM)
INDEXTYPE Is MDSYS.SPATIAL_INDEX;

In some cases, a generalized representation of the geometry in the same SRS is used for mapping applications. When the map scale is very small, it is better to use a generalized version of the geometry. This improves the appearance of the shape on the map and the performance as less data is transferred to the client. For this example, we will take the WATER_AREAS table and add another geometry column to store a generalized geometry.

Alter Table WATER_AREAS ADD (GEN_GEOM SDO_GEOMETRY);

Update WATER_AREAS SET GEN_GEOMETRY = SDO_UTIL.SIMPLIFY(GEOM, 500,0.05);

The SDO_UTIL.SIMPLIFY function takes an SDO_GEOMETRY column and simplifies it using the Douglas-Peucker algorithm. Here, a threshold of 500 feet is used to simplify the geometry. We can then create a metadata entry for this column and create a spatial index.

See the section on triggers to see how these additional columns can be kept in sync with the base column in the table. That is, whenever a new row is inserted or an existing geometry column is changed, the corresponding additional geometry column can be populated automatically using triggers.

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.

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.

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.