A White Paper
iSMART Enterprise GeoSpatial Suite
Database-level Interoperability for GIS Tools
October 2005
Eamon Walsh, Chief Technology Officer Bryan Hughes, Senior Consultant eSpatial Solutions
© eSpatial Solutions, 2005
Page ii
Table of Contents
1. 2. 3. 4. 5. 6. 7. 8. INTRODUCTION & SUMMARY ............................................................................ 2 DATA SHARING ................................................................................................... 3 COMMON STYLING ............................................................................................. 4 TOPOLOGY SHARING......................................................................................... 5 ESRI ARCSDE ISSUES ........................................................................................ 7 IMPLEMENTATION APPROACH ......................................................................... 8 APPENDIX: OVERVIEW OF ISMART .................................................................. 8 APPENDIX: ESPATIAL COMPANY BACKGROUND........................................... 9
© eSpatial Solutions, 2005
Page 1
1.
INTRODUCTION & SUMMARY
eSpatial (www.espatial.com ) provide the iSMART Enterprise Geospatial Suite of software products; comprising iSMART GeoPortal for pure web map viewing, analysis, and editing; iSMART Editor for multi-user GIS data capture both on-line and off-line, and the iSMART Server run-time platform and associated development environment for spatially enabled applications in a standard enterprise IT environment, using Oracle. Interoperability between different GIS tools may be provided for using the capabilities (where available) of the GIS tools to directly access spatial databases such as Oracle Spatial. There are a number of issues that need to be addressed to allow multiple GIS vendor’s tools share a common Oracle Spatial Database. While standard-based tools for spatial applications such as iSMART from eSpatial work with any database schema, including for spatial data, this is not the case for existing specialist GIS tools. Each GIS tool has rules for Oracle Spatial schemas, often requiring several specified column names and types, or even separation of geometries and attributes into different tables. Each tool also adds catalog, styling and symbolization information. Further, ESRI ArcSDE creates ‘Alpha’ and ‘Delta’ tables to record changes rather than directly updating data.
Tool 1
Tool 2
ESRI Arc SDE
Small World InSync
eSpatial iSMART
Base Map Updates
View 1 Cat-1 Cat-
Oracle Spatial
View 2 Cat-2 CatTriggers View 3 Cat-3 CatView 4 Cat-4 Cat-
Data
Proprietary Views to Common Data Schema + Triggers to update Catalogs
Figure 1: Shared Spatial Database.
Robust solutions can and have been deployed to meet these challenges. These use database views, and where necessary, triggers and stored procedures, to provide each tool with a view of the data that matches its proprietary requirements, and to synchronize styling and symbolization metadata between the tools. Interoperability at the database may be achieved as an organization-specific effort to meet particular needs, or through the use of more generic tools to do this. eSpatial can provide either solution.
© eSpatial Solutions, 2005
Page 2
2.
DATA SHARING
2.1 Overview – Data Sharing
Interoperability between different GIS tools may be provided for using the capabilities (where available) of the GIS tools to directly access spatial databases such as Oracle Spatial. There are a number of issues that need to be addressed to allow multiple GIS vendor’s tools share a common Oracle Spatial Database. While standard-based tools for spatial applications such as iSMART from eSpatial work with any database schema, including for spatial data, this is not the case for existing specialist GIS tools. Each GIS tool has rules for Oracle Spatial schemas, often requiring several specified column names and types, or even separation of geometries and attributes into different tables. Each tool also adds catalog, styling and symbolization information. Enabling a number of different GIS tools to read the same database is relatively common. There are more complex issues to be solved to also enable write access. There are also some best practices: For example, while it may be technically feasible to allow more than one GIS tool type to edit the same table of spatial data, it is best to only have one tool edit any given table and let others view that table (and potentially redline or annotate such data, but storing new data in another table). In this case it is still common to have several GIS tools editing the same database, but just no two tools editing the same table. Multiple GIS tools editing the same table can be provided for if required.
2.2 Technical Detail – Data Sharing
For this technical discussion, three GIS are going to be used as examples, namely iSMART Editor, ESRI ARCEDITOR and MapInfo Professional. It is worth noting that ARCEDITOR and in fact the ARCGIS suite generally, use ARCSDE as the interface between the client GIS and the Oracle Spatial database, either the full version or the client-side drivers which are known as Direct Connect. In order to view or edit vector data stored in Oracle Spatial using a GIS:
•
The data must be stored using the SDO_GEOMETRY data type in a table within the database. Valid Oracle meta-data must exist USER_SDO_GEOM_METADATA table, and for that table in the
•
•
A spatial index must be created on the SDO_GEOMETRY column.
These are general Oracle requirements for manipulating spatial data. For view access, all GIS require the spatial table has a not null numeric column by which to identify individual spatial objects, and require the column is indexed for performance reasons. Normally this column would be defined as the primary key. For read-write access, specialist GIS tools have more specific requirements e.g. ARCSDE requires a column called OBJECTID of type NUMBER(32).
© eSpatial Solutions, 2005
Page 3
Where the GIS tools to be supported have differing database schema requirements, or where it is desirable that the base schema does not GIS vendor specific database requirements, then standard RDBMS views may be used to present each GIS tool with a view meeting its requirements. Standard-based tools for spatial applications such as iSMART from eSpatial work with any database schema. Note that the “Interoperability initiative” for Oracle Spatial is as yet very much only a partial solution – addressing ‘directed points’ but not the issues outlined above.
3.
COMMON STYLING
3.1 Overview – Styling
Each GIS tool needs catalogue, styling and symbolization information. This is stored in the database. This data is sometimes referred to as “metadata” (distinct from Oracle metadata or ISO 19118 metadata). It is desirable that the GIS tools catalogues are kept synchronised, and that styles are matched as closely as possible. The recommended solution to this is to use one GIS tool to set up catalog and styling information and to use database triggers and stored procedures to automatically translate and copy to the other tools’ metadata stores. iSMART stores its metadata in a separate database / table-space to the user application data and so does not affect the database schema for user data. Some GIS tools store styling and other metadata in the user data tables (e.g. MI_STYLE column with MapInfo): Keeping the physical store of these separate can be addressed using Views as discussed under data sharing above.
3.2 Technical Detail – Styling
In order to view the same data in each of the GIS, the GIS tools’ meta-data schemas must contain copies of each others’ meta-data. Depending on the GIS tool, of of two mechanisms to maintain meta data may be provided:
•
iSMART provides a configuration tool to populate its meta-data. ESRI also provides a tool (to populate its SDETABLE and SDELAYER tables). This can be achieved in some cases using specific GIS data loading tools e.g. MapInfo Easy Loader, which not only loads MapInfo data into the database but populates the required MapInfo meta-data.
•
Using GIS tools to configure the meta-data is feasible, but has two disadvantages:
• •
The same information must entered multiple times – once for each GIS tool Care has to be taken that one tool doesn’t change aspects of the data that the other tool has set. For example, ESRI’s ARCCATALOG, which performs many of the operations of the command line utilities fails if a primary key already exists on a table as it tries to make the OBJECTID column the primary key.
© eSpatial Solutions, 2005
Page 4
A better alternative is use one GIS tool to set up catalog and styling information and to use database triggers and stored procedures to automatically translate and copy to the other tools’ metadata stores. This provides a greater amount of flexibility, and has the advantage of being executed from within the database. Using this mechanism it is a simple task to populate meta-data for one GIS automatically when meta-data for another GIS is populated.
4.
TOPOLOGY SHARING
4.1 Introduction to Topology for Oracle Spatial
There are two basic approaches to topology storage and editing in spatial databases, including Oracle Spatial: 1. A Topology Gateway – Store and Edit in Oracle Spatial as normal, and derive a Topology from the Spatial data 2. Directly update, view and analyse a topology data store (e.g. Oracle 10g Topology Data Model). Each of the above approaches stores topology information (Faces / Areas, Edges and Nodes) in ‘Topology Base Tables’ in the Oracle Database using Oracle Spatial geomteries to store the geometry information for the relevant topological entities. Features (Roads, rivers, etc) are related to one or more topological entities (e.g. edges). Topology Gateway
Roads, boundaries, rivers, parcel areas… Spatial Tables
- Attributes - SDO_GEOMETRY
Topology Base Tables Topology Gateway
e.g. iSMART, RADIUS
(triggers)
SDO_GEOM… {
Edit (any Tool) (any Tool) – insert, update, delete
Figure 2: Topology Gateway
Areas (Faces) Edges Nodes
With the Topology Gateway approach, spatial data is stored as normal in a database schema incorporating Oracle Spatial geometries. Initially, populate the Topology data store from the Oracle Spatial data. Edit the Oracle Spatial (SDO) data as normal: The
© eSpatial Solutions, 2005
Page 5
topology gateway is automatically triggered by data updates of dynamically update the topology data from them. Updates inserting spatial data that is not topologically correct will cause errors to be reported by the topology gateway. iSMART provides a facility to check spatial data for topological correctness before committing to the database. This is very useful with this approach. The triggers in the Topology Gateway can automatically correct some errors, and apply other rules, such as snapping rules. The topology data store (base topology tables) with this approach could be Oracle 10g Topology data store, but typically are a data store proprietary to the topology solution vendor. Topology Data Store
“Feature Layer” Tables
-Attributes - SDO_TOPO_GEOMETRY
10g Topology Tables
*Reference!
Roads, boundaries, rivers, parcel areas…
Synchronise changes iSMART Editor – insert, update, delete
Oracle Topo SQL & Java APIs
Figure 3: Direct Management of Oracle 10g Topology, using iSMART
In the second approach spatial data is not kept in standard Oracle Spatial data-types, but instead all data is kept in the Oracle 10g Topology data store, and this is edited directly rather than via a gateway from Oracle Spatial. Oracle provide facilities to build the initial topology data from existing spatial data. (Note that these require a skilled consultant to use). In this approach, the feature data is stored in Oracle 10g Topology “Feature Layer” tables (SDO_TOPO_GEOMETRY’s). These are derived from the topology but reference topology elements rather than duplicate data, whereas in the gateway approach the topology is derived from spatial data and copies the data. This approach is more sophisticated and powerful, but few tools support it: iSMART provides the facility to directly update, view and analyse Oracle 10g Topology data. To Edit topology data directly, it is also necessary to make corresponding changes to the references in the “feature layers” at the same time. There is no need for triggers or stored procedures with this approach. Because the topology is edited directly and features derive from it, the issue of topological incorrect
© eSpatial Solutions, 2005
Page 6
data updates does not arise. Hierarchical topologies (an option with Oracle 10g topology) automatically ensure the correspondences that require snapping rules with gateways.
4.2 Technical Detail - Laser-Scan RADIUS Topology
The Laser-Scan RADIUS Topology solution is a Topology Gateway from Oracle Spatial. RADIUS uses a proprietary schema to store its topology data: A number of tables are populated by RADIUS when topology is built on a spatial (“base”) table. These include topology “object primitive” tables that store the nodes and lines that describe the topological entities in the base spatial table. Topology is stored as relational links between the spatial table and the topology (“object primitive”) tables. The links are implemented as two additional numeric columns for each spatial table that can be added to any spatial table regardless of the GIS used to view it. Radius employs two methods for editing topology; 1. The spatial table is edited when the user desires to add new, or delete existing topological features; 2. The topology tables are edited when the user wishes to edit topology. For both methods this implies that the spatial table and the topology tables can be edited by the GIS. Providing the tables follow the rules previously described this can be done. Bearing in mind the complex model employed by ARCSDE however, it is recommended that RADIUS topology tables are not edited using ARCGIS. RADIUS employs database triggers to reflect changes made to the spatial tables in the topology tables and vice versa. Laser-scan indexes the topological tables using proprietary indexes built by extending Oracle indexing, but can use standard Oracle spatial indexes if the GIS in use does not support the former. Laser-Scan have announced ability to copy the topology data to the Oracle 10g topology data store. iSMART provides ability to edit Laser-Scan RADIUS topology, from its iSMART Editor tool, and using eSpatial’s pure web editing technology, including in iSMART GeoPortal.
5.
ESRI ARCSDE ISSUES
There are further issues to be addressed when working with ArcGIS and ArcSDE with Oracle Spatial and other GIS tools. eSpatial have addressed these for a number of clients. Some details of these are set out below: Technical detail – Data sharing with ArcGIS and ArcSDE In addition to the issues identified above, ESRI ArcSDE also creates ‘Alpha’ and ‘Delta’ tables to record changes rather than directly updating data in Oracle Spatial. tables To provide read-write capability, ARCSDE has much more specific requirements. In addition to the primary key requirements, it also requires that the table has been registered as versioned using ARCCATALOG. This procedure creates two additional
© eSpatial Solutions, 2005
Page 7
tables in the user’s schema, and then a further two tables for each base table to be edited, called ALPHA and DELTA tables. This is the mechanism provided by ESRI to support versioning. When the data is edited in ARCEDITOR, the changes are stored in the ALPHA and DELTA tables, not in the base spatial table. The changes are not reflected back to the base table until the database is compressed using the SDETABLE utility. The outcome of this is that users of other GIS accessing the same data in the database will not see updates made to the data. The solution to this problem is to use Oracle views to join the base table with the ALPHA and DELTA tables to provide a consistent view of the data. This is the method recommended by ESRI. Views are also useful for other purposes regarding this topic e.g. a column can be renamed to that required by ARCSDE i.e. OBJECTID, simply by including a column alias in the column list. The solution described above assumes ARCEDITOR is being used for editing and all other GIS tools only for viewing. To provide read-write support for other GIS tools requires further solution: procedures are required to update the ALPHA and DELTA tables when an update is performed by the other GIS. eSpatial have the ability and experience of addressing this.
6.
IMPLEMENTATION APPROACH
This approach to interoperability outlined here can have very powerful benefits:
•
Data integrity is maintained within an industry standard reliable, secure and scalable environment. Querying, analysis, and business intelligence across all business and spatial data and using powerful established tools is enabled. Not least, standard-based spatially enabled enterprise applications can be deployed directly from this database through addition of an application server tier spatial platform.
•
•
The main potential pitfalls of this approach are if the GIS tools or versions already used do not provide reliable and usable support for data in Oracle Spatial. Interoperability at the database may be achieved as an organization-specific effort to meet particular needs, or through the use of more generic tools to do this. eSpatial can provide either.
7.
APPENDIX: OVERVIEW OF ISMART
eSpatial provide the iSMART Enterprise Geospatial Suite of software products; comprising iSMART GeoPortal for pure web map viewing, analysis, and editing; iSMART Editor for multi-user GIS data capture both on-line and off-line, and the iSMART Server run-time platform and associated development environment for spatially enabled applications in a standard enterprise IT environment, using Oracle.
© eSpatial Solutions, 2005
Page 8
Figure: iSMART Architecture Overview
•
Pure web based platform with desktop type functionality: Easy to use Web viewing, publishing and editing ideal for non-GIS end users Configuration tools to manage spatial data within Oracle Ease of integration and co-existence within existing enterprise environments for both Geospatial and business applications Tight integration with the Oracle platform that completely leverages and extends the power and capabilities of Oracle and Oracle Spatial Provides a truly flexible and open J2EE, N-Tier architecture Single licensing environment – removes complexity of multiple software and maintenance licenses Significantly lower software and maintenance costs that increasingly lowers as number of users scale up
• •
•
• •
•
8.
APPENDIX: ESPATIAL COMPANY BACKGROUND
eSpatial (www.espatial.com ) was established in April 1997 and is wholly owned by the investment company, International Investment & Underwriting Limited (IIU). The company’s headquarters are in Dublin, Ireland where it employs 30 staff in product development, sales and marketing, administration, technical support, services and training. The U.S. headquarters is located in Herndon, VA with sales and technical staff.
© eSpatial Solutions, 2005
Page 9
The company addresses a market requirement for the delivery of spatial systems via the Internet leveraging open-standards technology. The company has a strategic focus on the development of web based spatial technology. The company’s vision is: “to become the world-leader in enterprise spatial solutions, using market leading information technology management.” Over the past three years, eSpatial has worked closely with the Oracle New England Development Center (NEDC) to refine and package the iSMART product suite. The first release of the core product, iSMART Server, was in March 2001. The latest release, iSMART 5 is available in November 2005. eSpatial works closely in partnership with a number of the world’s largest IT companies and systems integrators including Titan Corporation, Accenture, HP, and NAVTEQ, and geoLeaders. eSpatial is a member of the Open Geospatial Consortium.
eSpatial iSMART 5 Product Sheet 2005.09.16 For further information please contact: eSpatial Solutions Ltd. eSpatial, Inc. 2325 Dulles Corner Blvd, Suite 500, Herndon, VA 20171, Phone: +353 (0)1 870 8800 USA. Email: info@espatial.com Website: www.espatial.com Copyright© 2005 eSpatial Solutions Ltd. All Rights Reserved
Swords Business Park,
Co. Dublin, Ireland.
© eSpatial Solutions, 2005
Page 10