Applying and Extending Oracle Spatial

5 (1 reviews total)
By Simon Greener , Siva Ravada
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Defining a Data Model for Spatial Data Storage

About this book

Spatial applications should be developed in the same way that users develop other database applications: by starting with an integrated data model in which the SDO_GEOMETRY objects are just another attribute describing entities and by using as many of the database features as possible for managing the data. If a task can be done using a database feature like replication, then it should be done using the standard replication technology instead of inventing a new procedure for replicating spatial data. Sometimes solving a business problem using a PL/SQL function can be more powerful, accessible, and easier to use than trying to use external software. Because Oracle Spatial's offerings are standards compliant, this book shows you how Oracle Spatial technology can be used to build cross-vendor database solutions.

Applying and Extending Oracle Spatial shows you the clever things that can be done not just with Oracle Spatial on its own, but in combination with other database technologies. This is a great resource book that will convince you to purchase other Oracle technology books on non-spatial specialist technologies because you will finally see that "spatial is not special: it is a small, fun, and clever part of a much larger whole".

Publication date:
September 2013
Publisher
Packt
Pages
568
ISBN
9781849686365

 

Chapter 1. Defining a Data Model for Spatial Data Storage

Oracle Spatial and Graph provides a SQL schema and functions that facilitate the storage, update, and query of collections of spatial features in an Oracle database. Oracle Spatial and Graph is the new name for the feature formerly known as Oracle Spatial. In this book, we refer to this feature as Oracle Spatial for the sake of simplicity. We also focus exclusively on spatial feature of Oracle Spatial and Graph in this book. Oracle Spatial mainly consists of the following:

  • A schema (MDSYS derived from Multi-Dimensional System) that defines the storage, syntax, and semantics of the supported geometric (both vector and raster) data types

  • A spatial indexing mechanism for faster querying and retrieval

  • Operators, functions, and procedures for performing spatial analysis and query operations

  • A persistent topology data model for working with data about nodes, edges, and faces in a topology

  • A network data model for modeling and working with spatial networks

  • A GeoRaster data type and associated functions that let you store, index, query, analyze, and deliver raster data

The spatial component of a real-world feature is the geometric representation of its shape in some coordinate space (either in 2D or 3D), and in vector space, this is referred to as its geometry. Oracle Spatial is designed to make spatial data management easier and more natural to users of location-enabled business applications and geographic information system (GIS) applications. Oracle allows the storage of spatial data in a table using the SDO_GEOMETRY data type that is just like any other data type in the database. Once the spatial data is stored in the Oracle database, it can be easily manipulated, retrieved, and related to all other data stored in the database.

A spatial database should be designed just like any other database with a fully specified model. A fully specified model that is application independent should control the spatial data storage. A good data model supports and enhances application access without compromising the quality. In addition to these features, database features can be used to support applications that have limited functionality when it comes to table and column design. For example, some applications mandate a single spatial column per table or only a single homogeneous geometry type per spatial column. These limitations can be accommodated quite easily using database features such as views and triggers. In addition, there are a number of issues that arise when designing a data model that directly affects the data quality, performance, and access.

The goal of this chapter is to give readers an understanding of how to model spatial data as SDO_GEOMETRY columns within tables, how to support spatial constraints for improved data quality, how to use synchronous and asynchronous triggers for implementing topological constraint checking, and to present methods for coping with multiple representations for faster web service access. All these issues, with solutions, are covered in this chapter:

  • Defining a sample schema

  • Using spatial metadata

    • Using Oracle metadata views

    • Using OGC metadata views

  • Using different types of geometric representations

    • Implementing tables with homogeneous and heterogeneous columns

    • Implementing multiple representations for a single object

    • Implementing multiple instances of a single column, for example, pre-thinned data for different scales and reprojection for faster web service access

  • Restricting data access via views

    • Using views to expose a single geometry type when multiple geometry types are present in the table

    • Using views to expose tables with single geometry columns when multiple geometry columns are present in the table

  • Implementing spatial constraints at the database level

    • Restricting geometry types

    • Spatial topological constraints

    • Implementation of synchronous triggers

    • Implementation of asynchronous triggers

 

Defining a sample schema


We will first define a sample schema that will be used for all the examples in this book. The schema is intended to model typical spatial assets maintained in a city-level GIS. Oracle Spatial provides all the functionality needed to model or describe the spatial properties of an asset (in modeling, it is often called an entity). This spatial description of an asset should not be treated differently from any other descriptive attribute. In addition, a data model should describe all assets/entities within it independently of any application. This should include, to the best of the ability of SQL, all business rules that define or control these assets/entities within the database, and these rules should be implemented using standard database practices.

Defining the data model

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:

Creating tables in the schema

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;

Understanding spatial metadata

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.

Spatial Reference System

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.

More on Spatial Reference Systems

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.

Creating spatial metadata

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.

OGC-defined metadata views

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 in Oracle Spatial

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 the distance between two consecutive vertices in the geometry is less than the tolerance value, the geometry is invalid. This rule applies to line-string and polygon type geometries.

  • If the distance between a vertex and the nearest edge to that vertex in a polygon is less than the tolerance value, the geometry is invalid. This rule only applies to the polygon type geometries.

Managing homogeneous and heterogeneous data

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);

How metadata is used

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.

Using database check constraints

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

Multiple representations for the same objects

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.

Point representation for polygon objects

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 ;

Alternate representation in a different coordinate system

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;

Using generalized representation

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.

 

Implementing spatial constraints in the database


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.

Constraining the geometry type

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.

Implementing more complex triggers

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’

Fixing invalid data using triggers

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.

 

Constraints with asynchronous triggers


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.

Creating a queue

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.

Defining the trigger

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.

Implementing rule-based constraints

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.

Defining rules for the schema

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 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’) );

Trigger for checking spatial relationships

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 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.

Batch processing existing data

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.

 

Summary


A spatial database application should be designed just like any other database application. It should follow the same standard data model practices like any other database application. In this chapter, we introduced a data model that can be used in a city-wide spatial data management system. This data model is used throughout the book to illustrate different aspects of spatial database application development. We discussed the use of triggers and queues to manage spatial data in the database. We also showed how to design database level constraints for spatial data management. We introduced the concepts of database triggers and queues and showed how they can be used for spatial data management in the database.

The next chapter will show different techniques for loading spatial data from different formats into Oracle Spatial tables. We will also describe some of the common spatial data interchange formats and show some PL/SQL examples for generating data in these formats.

About the Authors

  • Simon Greener

    Simon Greener has university qualifications in geomatics, computing science, database technologies, and project management. He started his working career with mining and surveying experience. He then found his calling as a computer scientist with his first job as a database programmer on IBM mainframes for Telstra. He switched to GIS three years later through working in a multi-disciplinary GIS research team at Telecom's Research Laboratories (TRL) in Clayton, Victoria. While at TRL, he worked on projects whose outcome saw the creation of what is now Telstra's Sensis group. After leaving TRL, he worked as a lecturer and consultant for CenSIS (University of Tasmania) under Professor Peter Zwart, writing student and technical training courses. While there, he continued to consult to Telstra's Directory Services and Mobile groups. It was here that he came in contact with the Spatial DataBase Engine (SDBE) from Geographic Technologies Incorporated (GTI) and saw its potential for the management of large scale spatial databases within relational database technologies, a merging of his IT and GIS worlds. This led to the foundation of Salamanca Software Pvt Ltd (SalSoft), for which he was a Director until it was purchased by ESRI Australia in 1996. Some notable achievements while at SalSoft included helping brokers with the sale of SDBE to ESRI Inc (now ArcSDE), winning the first ArcSDE sale to Telstra to power its White pages/Yellow pages mapping portal, co-authoring a geocoding specification for Spatial Decision Systems (now Sensis), consulting for Geographic Technologies Australia on numerous projects based on Universal Press street directory data, and the creation of GeoCASE/Blueprint, the world's first data modeling tool that enabled the modeling of spatial data and relationships. In 1997, he was appointed GIS Manager for Forestry Tasmania (FT) in Hobart, Tasmania. While at FT, he architected the complete revamp of FT's GIS systems using Oracle Spatial (being one of the earliest adopters of the Sdo_Geometry implementation) as the core data management technology. He was concentrating on embedding geospatial data and processing within business systems via a value-oriented, business-centric computing model. He designed and built numerous systems during those years, the best of which was MapComposer, a three-click web-enabled business map production system that, when he left in 2005, had grown (2000-) to over 320 online uses, producing over 50,000 maps a year from a repository of over 100 different map templates (still in operation in 2013). His years at FT concluded with the writing of a GIS Strategy that saw the use of GIS increase yet the cost of the technology to the organization decrease. He left FT in September 2005 for the precarious world of self-employment. He was a sometime copyist for Directions Magazine. As a subcontractor to a Spatial distributor in Australia, he wrote a Radius Topology training course and provided Radius Topology and Oracle Spatial consulting services for them at numerous customer sites until May 2006. From May to August 2006, he was engaged by Spatial, Cambridge, UK, under a UK Government Department of Trade and Industry's GlobalWatch program to conduct research and development in relation to enhancing the export potential of their latest product, Radius Studio (this resulted in Radius Studio being integrated with Feature Data Objects – FDO technology to extend its data access capabilities). In his consulting career, he has written a spatial strategy document and conducted a database performance analysis review for a large Tasmanian Government department. He has conducted a number of Oracle spatial database best practice, tender and system, and return on investment reviews at a number of Victorian Government departments. He wrote and delivered a user requirements document for Enterprise GIS at a large Australian corporation. He also provided guidance and implementation services to an ambulance service helping integrate Oracle Spatial into a data warehouse project that used Oracle Portal, Discoverer, and Data Warehouse Builder. He delivered many solutions for a NSW water authority; and finally, he successfully completed many migration, publication, return on investment, process improvement, and database design contracts for a number of Canberra-based Federal Government departments. Simon makes available a collection of PL/SQL and Java-based sample solutions for the Oracle database via his website. He is also principal programmer for the SQL Developer spatial extension, GeoRaptor. Finally, he was awarded, the 2011 Oracle Spatial Excellence Award for Education and Research by Oracle. His technical areas of expertise include systems design and architecture (spatial and attribute), data management, and modeling in both the OLTP and OLAP spaces, and he is also an evangelist for O-RDMS-based spatial data. He is available for free-lance geospatial solutions architecture work, Java and PL/SQL programming, and he provides Oracle Spatial benchmarking and performance enhancement services. His non-technical interests are his family, friends, walking, reading, singing, and motorcycle riding.

    Browse publications by this author
  • Siva Ravada

    Siva Ravada earned a PhD degree from the University of Minnesota in the field of Spatial Databases before joining Oracle's Spatial development team. He is now a Senior Director of Development at Oracle Corporation. At Oracle, Siva was one of the founding team members of the Spatial development team before taking over the team management responsibilities. Siva now manages the Spatial and MapViewer development teams at Oracle. He has more than 15 years of experience in spatial databases and application development. He has also co-authored more than 30 articles published in journals, and holds more than 30 patents. He has also presented key-note speeches at several conferences on the topics of spatial databases and GIS. Oracle is the second largest software company and the number one database company in the world.

    Browse publications by this author

Latest Reviews

(1 reviews total)
HUGE Continue the good work make discounts on ebooks
Book Title
Access this book, plus 7,500 other titles for FREE
Access now