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 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
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.
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.
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
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.
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
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_ 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
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
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
view displays all the spatial tables owned by the user along with other spatial tables for which the current user has read access.
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);
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.
BASE_ADDRESSES table has geometries stored in two columns:
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
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.
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 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.
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.
-- 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  while geometry inserted has type  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.
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
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
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.
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
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);
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);
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.
After the schema is decided and the spatial layers are defined, it is useful to add spatial constraints to the database so that many of the data consistency checks for spatial data can be done in the database. These checks can be defined using the same mechanisms used to define the traditional relational database constraints. Typically, these constraints are defined and implemented in GIS applications, but a database is a common data store that is accessed by many GIS applications. So, it makes more sense to define the common data level constraints at the database level so that each application does not have to define these constraints separately.
A spatial constraint is a data consistency check that makes sure that the data stored in the spatial layers follows certain spatial rules. For example, it may be that a building footprint should always be contained within a land parcel or a road should never cross a land parcel. At the database level, there are several different ways to implement these constraints. The most common way is to define triggers on the tables and check for data consistency as soon as a new row is inserted. Then, based on the result of the check, the row of data can be rejected or accepted. This is called synchronous trigger processing. However, there may be cases where the checks can be done at a later time so that the processes inserting the new data are not blocked when checking for data consistency. When a bunch of data is inserted, a background process can go through the new data, run the data consistency checks, and generate a report with invalid rows that the data consistency checks useless. Because the processing is separated from the transaction, this is called asynchronous trigger processing.
We first revisit the previous problem of constraining the spatial layer to contain a single type of data. In the previous section, we showed how to implement this constraint using the spatial index. Now, if we want to constrain a table to contain only points and lines, the index-based constraint mechanism does not work. That was why we used a column or table constraint. But, it is also possible to use a trigger-based mechanism to implement such a constraint.
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
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;
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;
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);
Asynchronous triggers can be implemented in different ways, and one common practice is to use a queue. A queue is used to detect changes in the tables, and then based on the base table, specific checks can be done. As we are introducing a new concept of queues here, we will keep the spatial constraint part simple and do the validation checks using an asynchronous trigger.
Oracle provides PL/SQL procedures in the
DBMS_AQ package for creating and using queues. In our example, a simple queue is used to insert information about new rows of data coming into the
LAND_PARCELS table. Then, an asynchronous process can take entries out of the queue and process them one at a time.
We first create a type to store the messages in the queue. This type is defined based on the information one wants to store in the queue. Make sure this type has enough fields so that the procedure processing the messages from the queue does not need to look for information elsewhere. The message type we use here has the Primary Key (PK) of the
LAND_PARCELS table along with the value of the PK. Before we start, we should first drop the previous trigger that we have created on the
Drop trigger land_parcels_validate_v1; Drop trigger land_parcels_validate_v2; -- Now create the type required for the messages Create Type validate_q_message_type As Object( pk_column Varchar2(32), pk_value Integer, table_name Varchar2(32), column_name Varchar2(32));
-- First create the queue table EXEC DBMS_AQADM.Create_QUEUE_TABLE( queue_table => ‘validate_q_message_table’, queue_payload_type => ‘validate_q_message_type’); -- Next create the queue EXEC DBMS_AQADM.Create_QUEUE( queue_name => ‘validate_queue’, queue_table => ‘validate_q_message_table’); -- And finally start the queue EXEC DBMS_AQADM.START_QUEUE( queue_name => ‘validate_queue’);
Next, we code the trigger to look at the rows coming into the
LAND_PARCELS table and create messages for the queue. We create one message in the queue for each row inserted or updated in the table. The message has information about the columns of the table that are required to access the new or changed geometry.
Create Or Replace trigger LAND_PARCELS_VALIDATE_V3 after Insert or Update on LAND_PARCELS For Each Row declare queue_options DBMS_AQ.ENQUEUE_OPTIONS_T; message_properties DBMS_AQ.MESSAGE_PROPERTIES_T; message_id RAW(16); my_message validate_q_message_type; Begin my_message := validate_q_message_type(‘FID’,:NEW.FID,’LAND_PARCELS’,’GEOM’); DBMS_AQ.ENQUEUE( queue_name => ‘validate_queue’, enqueue_options => queue_options, message_properties => message_properties, payload => my_message, msgid => message_id); End;
Now insert a row into the
LAND_PARCELS table, and again we insert a row with an invalid geometry. Only this time, the row gets inserted into the table and no error is raised. But, a new message gets created in the message queue created by us.
Delete From LAND_PARCELS Where FID = 3326028; Insert Into LAND_PARCELS values(3326028, ‘0026T05AA’, ‘0026T’,‘055A’, ‘2655’, ‘HYDE’, ‘1 ST’, ‘HYDE’, ‘ST’, ‘O’, SDO_GEOMETRY(2003, 2872, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),SDO_ORDINATE_ARRAY(6006801.83, 2121396.9, 6006890.33, 2121409.23, 6006878.19, 2121495.89, 6006788.97, 2121483.45 )), NULL);
Now look at the message queue to check if there are any messages. The easiest way to do this is to look at the queue table created for our message queue.
Select * From AQ$VALIDATE_Q_MESSAGE_TABLE;
This query should return one row corresponding to the message that our trigger just inserted. As you can see, the table is created using the name that we supplied in the
Create_QUEUE_TABLE call with an additional
Next, we will look at some PL/SQL that can be used to browse messages from the queue and to look at the values in the message.
Set SERVEROUTPUT ON; Declare queue_options DBMS_AQ.DEQUEUE_OPTIONS_T; message_properties DBMS_AQ.MESSAGE_PROPERTIES_T; message_id RAW(2000); my_message validate_q_message_type; Begin queue_options.dequeue_mode := DBMS_AQ.BROWSE; DBMS_AQ.DEQUEUE( queue_name => ‘validate_queue’, dequeue_options => queue_options, message_properties => message_properties, payload => my_message, msgid => message_id ); Commit; DBMS_OUTPUT.PUT_LINE( ‘Dequeued PK Column: ‘ || my_message.pk_name); DBMS_OUTPUT.PUT_LINE( ‘Dequeued PK value: ‘ || to_char(my_message.pk_value)); DBMS_OUTPUT.PUT_LINE( ‘Dequeued Table: ‘ || my_message.table_name); DBMS_OUTPUT.PUT_LINE( ‘Dequeued Column: ‘ || my_message.column_name); End; /
This should print the following messages in SQLPLUS. Note that this queue does not do anything useful yet:
Dequeued PK Column: FID Dequeued PK value: 3326028 Dequeued Table: LAND_PARCELS Dequeued Column: GEOM
If you run the preceding PL/SQL code when the queue is empty, it will wait until some messages are inserted into the queue. So, it will wait for another transaction to insert some data into the
LAND_PARCELS table and commit. For browsing the messages, the dequeue mode is set to
DBMS_AQ.BROWSE. If you change the mode to
DBMS_AQ.REMOVE, it will remove the message from the queue. Next, we will look at the code to remove the message from the queue, process it, and take an action depending on the validity of the geometry.
Set SERVEROUTPUT ON; Declare queue_options DBMS_AQ.DEQUEUE_OPTIONS_T; message_properties DBMS_AQ.MESSAGE_PROPERTIES_T; message_id RAW(2000); my_message validate_q_message_type; tname Varchar2(32); cname Varchar2(32); pkname Varchar2(32); result Varchar2(100); geometry SDO_GEOMETRY; rect_geom SDO_GEOMETRY; Begin queue_options.dequeue_mode := DBMS_AQ.REMOVE; DBMS_AQ.DEQUEUE( queue_name => ‘validate_queue’, dequeue_options => queue_options, message_properties => message_properties, payload => my_message, msgid => message_id ); Execute IMMEDIATE ‘ Select ‘|| SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.column_name)|| ‘, sdo_geom.validate_geometry_with_context(‘ || SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.column_name)|| ‘, 0.05) From ‘ || SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.table_name)|| ‘ Where ‘ || SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.pk_column)|| ‘ = :pkvalue ‘ Into geometry, result USING my_message.pk_value ; If (result = ‘TRUE’) then return; else rect_geom := sdo_util.rectify_geometry(geometry, 0.05); result := sdo_geom.validate_geometry_with_context(rect_geom, 0.05); If (result = ‘TRUE’) then EXECUTE IMMEDIATE ‘ Update ‘|| SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.table_name)||’ set ‘|| SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.column_name)|| ‘ = :geometry Where ‘|| SYS.DBMS_ASSERT.ENQUOTE_NAME(my_message.pk_column)|| ‘ = :pkvalue ‘ USING rect_geom, my_message.pk_value; Else RAISE_APPLICATION_ERROR(-20000, ‘Geometry cannot be fixed for row with ‘|| my_message.pk_value|| ‘ in table’ || my_message.table_name); End If; End If; dbms_output.put_line(result); Commit; End;
There are a few important things to note in this PL/SQL code. The message that we have created in the queue is very generic so that the same code can be used to process messages for many spatial tables. As the table name, column name, primary key column, and Primary Key value are all retrieved from the message, the same queue and dequeue mechanism can be used for a set of tables in the schema. This code can be easily modified to be a procedure, and it can be executed in a loop while waiting for messages in the queue. It can also be turned on when there is less activity in the DB to reduce the load. You also need to think about what happens when the geometry cannot be fixed using the rectify function. We left that part open in this code example as it depends on other available tools for fixing the invalid geometry data.
So far, we have only looked at constraints that mainly deal with the validity of data or type of data present in our spatial tables. We will now look at more complex scenarios that define relationships between objects in different tables. These rules enforce data quality and make sure that the objects in the schema follow certain spatial relationship rules. An example of such a constraint is the one that specifies that the building footprints must be contained within the land parcels. Similarly, a land parcel must be contained inside a planning zone.
These constraints can be implemented using triggers (synchronous or asynchronous) as described in the previous sections. They can also be implemented via a spatial constraints table that would provide a flexible and generic data-driven approach. With this data-driven approach, a generic model can be used to enforce constraints between different spatial layers in our schema. A rules table is first created to store all the allowed spatial relationships between different spatial layers. Then, triggers are used on spatial layers to enforce these rules. The advantage of creating a rule table is that all the allowed rules are stored in one place instead of spreading them across the different triggers. This makes it easy to document the rules so that any GIS tool can easily look up the available rules.
We first look at the rules that will describe possible relationships between objects for this schema. These rules can be defined as MASK values and their combinations that are valid for the
SDO_GEOM.RELATE function. We will use this function to enforce the following rules in our schema:
CONTAINS: A land parcel may contain a building footprint
COVERS: A planning neighborhood covers a land parcel
EQUAL: A planning neighborhood can be equal to a land parcel
TOUCH: A road segment can only touch another road segment
CONTAINS + COVERS: A land parcel may contain and cover a building footprint
Next, we look at how to create a rule-based constraint between the
BUILDING_FOOTPRINTS tables and
LAND_PARCELS tables. The rule specifies that a building should be inside a land parcel (either completely inside or touching the boundary) And a building cannot exist without containing a land parcel. We first create a relationship table that specifies which land parcel contains which building footprint. Note that this information can always be obtained by executing a spatial query. But sometimes it is easier to persistently store this information as it is computed anyway to enforce the data consistency.
We will also enforce the constraint that the only possible values allowed for the
SPATIAL_RELATIONSHIP column in this table are
COVERS. That is, a land parcel can only contain or cover a building footprint. We also want to enforce the
UNIQUE constraint on the
BUILDING_ID column, as each building footprint can only have one parent land parcel record in our schema.
Create Table Building_Land_Parcel (Land_parcel_id Varchar2(9), Building_id number, spatial_relationship Varchar2(100)); Alter Table Building_Land_Parcel add Constraint BLP_PK UNIQUE (Building_ID) enable; Alter Table Building_Land_Parcel add Constraint BLP_FK_LP Foreign Key (Land_Parcel_ID) REFERENCES LANDPARCELS(BLKLOT) enable; Alter Table Building_Land_Parcel add Constraint BLP_FK_BF Foreign Key (BUILDING_ID) REFERENCES BUILDING_FOOTPRINT(BUILDING_ID) enable; Alter Table BUILDING_LAND_PARCEL add Constraint BLP_Spatial_CHECK CHECK ( spatial_relationship in (‘COVERS’, ‘CONTAINS’) );
Once we have the relationship table created, we next create a utility function that can be used to check for different spatial relationships between objects of different spatial layers. This is a generic procedure that can be used for many different spatial layer pairs with minor changes.
Create Or Replace PROCEDURE Check_Relation (tname varchar2, cname varchar2, pk_name varchar2, geometry SDO_GEOMETRY, mask varchar2, pk_value OUT Integer, relation OUT varchar2) AS stmt Varchar2(200); rel_mask Varchar2(200); Begin rel_mask := ‘MASK=’||mask; stmt := ‘ Select ‘|| SYS.DBMS_ASSERT.ENQUOTE_NAME(pk_name)|| ‘, SDO_GEOM.RELATE(‘||SYS.DBMS_ASSERT.ENQUOTE_NAME(cname, false)|| ‘ , ‘’DETERMINE’’, ‘|| ‘:geometry, 0.05) From ‘|| SYS.DBMS_ASSERT.ENQUOTE_NAME(tname, false)|| ‘ Where SDO_RELATE(‘|| SYS.DBMS_ASSERT.ENQUOTE_NAME(cname)|| ‘, :geometry, :rel) = ‘’TRUE’’ ‘; Begin EXECUTE IMMEDIATE stmt Into pk_value, relation USING geometry, geometry, rel_mask; EXCEPTION When NO_DATA_FOUND Then pk_value := NULL; When OTHERS Then raise; End; End;
This procedure takes spatial layer information (table name, column name) to search for the given geometry with the specified spatial relationship. It returns the Primary Key of the spatial layer so that the row that satisfies the specified spatial relationship is identified easily for further processing. We use this information in the trigger to populate the rows in the relationship table that we have defined in the preceding code. It is very important to handle the exceptions in this procedure so that when the insert fails, the users will know exactly what failed. For this case, if valid values for table name and column name are passed in, the
SDO_RELATE query can have three possible results:
It finds exactly one row with the given mask
It finds no rows
It fails for some other reason
The first case is the valid case for our trigger, so we don’t need to do error processing for this case. The second case means there is no corresponding land parcel that either contains or covers the given building footprint. We let the trigger handle this error, so in this procedure, we just pass a
NULL value for
FID. The third case means something is wrong with the parameters or the tables involved in the query. In this case, we raise the actual error so that the user can further process the error condition.
Next, we create a trigger on the
BUILDING_FOOTPRINTS table to check each building footprint that is newly created or updated for containment with the
Create Or Replace TRIGGER BF_LP_RELATION after Insert or Update on BUILDING_FOOTPRINTS FOr EACH ROW Declare building_id number; FID Number; relation Varchar2(100); Begin Check_Relation(‘LAND_PARCELS’, ‘GEOM’, ‘FID’, :NEW.geom, ‘COVERS+CONTAINS’, fid, relation ); If ( (FID is NULL) Or (relation <> ‘CONTAINS’ AND relation <> ‘COVERS’) ) then RAISE_APPLICATION_ERROR(-20000, ‘BuildingFootPrint with ID ‘|| to_char(:NEW.fid)|| ‘ is not inside any landparcel’); Else Insert Into Building_Land_Parcel Values(fid, :NEW.fid, relation); End If; End;
This trigger first checks the relationship of the current geometry that is being inserted into the
BUILDING_FOOTPRINT table. If it finds a land parcel that contains the footprint, this information is inserted into the relationship table. If there is an error (that is, no containing land parcel is identified), the trigger will raise an error and the insert into the footprint table fails. This can also be implemented as an asynchronous trigger so that the insert into the footprint table is allowed even if there is an error, but some error information is recorded in the relationship table so that it can be checked and fixed later.
The trigger that we defined here will create the relationship for the new rows that are coming into the footprint table. In some cases, the data for these tables might have been populated using a batch process before this trigger is enabled. In such cases, how do we populate the relationship table? Next, we describe a process that looks at all the existing records in the footprint table and finds the land parcel that contains it. This can be a time-consuming process, as it has to check each footprint to find the corresponding land parcel.
Insert Into building_land_parcel Select lp.fid, b.fid, sdo_geom.relate(lp.geom, ‘determine’, b.geom, 0.05) From BUILDING_FOOTPRINTS b, LAND_PARCELS lp Where SDO_RELATE(lp.geom, b.geom, ‘mask=CONTAINS+COVERS’) = ‘TRUE’;
This SQL takes each building footprint and finds the corresponding land parcel. While this SQL is efficient in execution, there is no error reporting as part of this SQL. If there is a building footprint without a corresponding record from the
LAND_PARCELS table, there won’t be any record of that missing entry in the
BUILDING_LAND_PARCEL table. The user should check the number of rows in the
BUILDING_LAND_PARCEL table to make sure that there is one record corresponding to each building footprint in this table. This check is required only if the preceding SQL is used to populate this relationship table. In the normal processing of the building footprints, when new records arrive, the trigger that we have created on the
BUILDING_FOOTPRINTS table will take care of these error conditions.
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.