Book Image

Applying and Extending Oracle Spatial

Book Image

Applying and Extending Oracle Spatial

Overview of 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".
Table of Contents (20 chapters)
Applying and Extending Oracle Spatial
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
Table Comparing Simple Feature Access/SQL and SQL/MM–Spatial
Index

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.