(For more resources related to this topic, see here.)
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 article 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 article:
Defining a sample schema
Using spatial metadata
Using Oracle metadata views
Using OGC metadata views
Using different types of geometric representations
Implementing tables with homogeneous and heterogeneous columns
Implementing multiple representations for a single object
Implementing multiple instances of a single column, for example, pre-thinned data for different scales and reprojection for faster web service access
Restricting data access via views
Using views to expose a single geometry type when multiple geometry types are present in the table
Using views to expose tables with single geometry columns when multiple geometry columns are present in the table
Implementing spatial constraints at the database level
Restricting geometry types
Spatial topological constraints
Implementation of synchronous triggers
Implementation of asynchronous triggers
Defining a sample schema
We will first define a sample schema that will be used for all the examples in this article. 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 article. 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.
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 cthe 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 article.
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.
Summary
A spatial database application should be designed just like any other database application. It should follow the same standard data model practices like any other database application. In this article, we introduced a data model that can be used in a city-wide spatial data management system. This data model is used throughout the article 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.
Resources for Article:
Further resources on this subject:
Remote Job Agent in Oracle 11g Database with Oracle Scheduler [Article]
Introduction to Oracle Service Bus & Oracle Service Registry [Article]
Configuration, Release and Change Management with Oracle [Article]
Read more
United States
Great Britain
India
Germany
France
Canada
Russia
Spain
Brazil
Australia
Singapore
Canary Islands
Hungary
Ukraine
Luxembourg
Estonia
Lithuania
South Korea
Turkey
Switzerland
Colombia
Taiwan
Chile
Norway
Ecuador
Indonesia
New Zealand
Cyprus
Denmark
Finland
Poland
Malta
Czechia
Austria
Sweden
Italy
Egypt
Belgium
Portugal
Slovenia
Ireland
Romania
Greece
Argentina
Netherlands
Bulgaria
Latvia
South Africa
Malaysia
Japan
Slovakia
Philippines
Mexico
Thailand
