Spatial Database

GISC 6383 Fall 2005

Bryan Dennie

Jian Huang

Jianghong Li

Judy Mays

Table of Contents

  1. Introduction3
  2. Comparison Criteria
  3. Overview, Cost, and Standards4
  4. GIS Application5
  5. Spatial Data Types6
  6. Query7
  7. Data loading9
  8. Other Criteria (Compatibility with GIS Applications,

Server Capability, Stand-Alone or part of System)10

  1. Summary11
  2. Appendix I: Oracle Spatial Functions13
  3. Appendix II: IBM DB2 Spatial Functions15
  4. References16

Introduction

At the request of the Retail Manager, the option of performing new store location assessment in-house is being investigated. In order to evaluate prospective locations this company, DFW Stores, Inc., needs to incorporate spatial data to its database systems. Our committee has researched several options and presents the results in this report.

Locational data may be integrated with business data by adding spatial capabilities to the database system. Querying and analyzing spatial data requires specialized data types and functions that are provided by the spatial database. Points, lines, and polygons are used to model geographic entities and are referenced to a specific location by means of coordinates such as latitude and longitude. Not only can spatial objects be referenced to each other, but this spatial data may then be related to other conventional business and demographic data within the database. Queries may then be made on this combined data and results viewed in database report format or in map form by means of a visualization tool.

The potential spatial databases considered for this project are: IBM DB2 Spatial Extender and Oracle Spatial, both industry-leading database providers; PostGIS, an open-source database that includes some spatial functionality; and ArcSDE, a spatial data engine from ESRI, the market leader in GIS applications. Following is a general introduction that provides an overview of each system.

Once a separate extension to IBM DB2 database, Spatial Extender is now integrated within current versions of the database. Spatial information, in formats such as points, lines, and polygons, can be analyzed, generated, stored, accessed, and queried within the spatial database.

Oracle Spatial, a paid extension to Oracle Enterprise Edition, offers the possibility to store, update, retrieve and analyze spatial data in and from the database. Central data management is made possible by storing both spatial and traditional data in the same location.

PostGIS adds geographic object support to the open-source object-relational database, PostgreSQL. In other words, PostGIS enables PostgreSQL to store, relate, join, query, analyze with spatial data inside the database.

ArcSDE is not an extension to a database, but rather middleware, or a server software product. ArcSDE augments the range of data types to include spatial data that can be managed in traditional relational databases like IBM DB2, Oracle, or Microsoft SQL Server. It is the necessary connection for ESRI’s ArcGIS systems, acting as a conduit to applications that perform geographic or spatial analysis as it serves data to users within an organization or to the internet.

In the next section, the following topics are used for comparison: cost, standards, GIS applications, spatial data types supported, database queries, spatial functions, data loading, compatibility with GIS applications, and server capability.

  1. Cost and Standards:

Spatial Database
Product /

Cost

/

Standards

IBM DB2 spatial extender / For earlier versions of DB2, cost to add SpatialExtender is $9,250
Now included in DB2v8.1 & higher:
IBM DB2 Personal Edition $461
IBM DB2 cost varies with number of licenses:
Express Edition $4,874
Enterprise Server Edition $33,125 / Conforms to the ISO SQL/MM Spatial Standard and the OpenGIS Consortium's (OGC's) Simple Feature Specification for SQL

ArcSDE

/ Starting price is $10,000 for commercial applications. / ArcSDE supports OpenGIS simple features and SQL statements.
Oracle Spatial / Enterprise Edition:
  • Named user license: $800
  • Processor license: $40,000
Oracle Spatial:
  • Named user license: $200
  • Processor license: $10,000
/ Oracle spatial implements the OpenGIS Consortium Simple Features guidelines. Oracle is also committed to supporting the new OGC Geographic Markup Language (GML) as well as Open Location Service interfaces.
PostgreSQL / PostGIS / Refractions Research under the GNU General Public License developed PostGIS for open source, free to the public distribution. It and the PostgreSQL database are completely open-source and can be easily downloaded over the internet for free. / Version 1.0 of PostGIS is currently under validation of the Open GIS Consortium “Simple Features Specification for SQL” which defines standard GIS object types, the functions required to manipulate them, and a set of metadata tables.

2. GIS Application

Spatial Database
Product /

GIS Application

IBM DB2
Spatial Extender / In 2001 IBM and ESRI partnered and jointly developed the IBM DB2 Spatial Extender, which incorporated geographic or spatial information with business and other data already residing in an IBM Universal Database. Spatial data may be manipulated within the database by utilizing spatial extender functions to return GIS information in tabular format. A separate visualization tool is needed to see a graphic representation such as a map.

ArcSDE

/ ArcSDE is a server software product used to access massively large multiuser geographic databases stored in relational database management systems (RDBMSs).
It is an integrated part of ArcGIS and a core element of any enterprise GIS solution. Its primary role is to act as the GIS gateway to spatial data stored in a RDBMS.
ArcSDE provides a suite of services that enhance data management performance, extend the range of data types that can be stored in a RDBMS, enable schema portability between RDBMSs, and offer configuration flexibility.

Oracle Spatial

/ Oracle Spatial is an extension to Oracle9i that provides geometry storage, indexing, and spatial search functions. When used with Oracle9i Enterprise Edition, Oracle Spatial provides a standards-based data management solution for the delivery of GIS, Internet mapping, and mobile location-based services. It serves as a technology platform for managing an enterprise-wide GIS or location-enabled e-business applications.
PostgreSQL / PostGIS / The software itself is a limited standalone GIS, but there are other open source applications that can be downloaded to enhance it’s GIS capabilities.
  1. Spatial Data Types:

Spatial Database Product / Spatial Data Type / Graphic
IBM DB2 Spatial Extender /
  • Points
  • Lines
  • Polygons
Ability to “subtype” these basic types /
ArcSDE / Support all the ESRI
geometry types /
Oracle Spatial / SDO_GEOMETRY – can support three geometric primitive types:
  • Points.
  • Line Strings (can be linear, curved or both)
  • Polygons
/
PostgreSQL / PostGIS /
  • Point, Multipoint
  • Line, Multiline
  • Polygon,,Multipolygon,
  • Geometrycollections
/

4. Query:

Spatial Database Product / Query / Functions
IBM DB2 Spatial Extender /
  • SQL access to spatial data
  • SQL querying of spatial data and/or joining of spatial data with conventional database or business data
/ 80 built-in spatial data functions
8 geometry type constructor functions
12 spatial data comparison functions
20 geometric conversion functions
40 geometric calculator functions
Examples:
ST_LineString, ST_Intersects, ST_AsShape, ST_Area
ArcSDE / The spatial query in ArcSDE and RDBMS client is implemented in the form of SQL statements.
Requests for data occur when actions like zooming in/out, querying rows in table, and displaying tabular or spatial data are performed. These types of requests are translated into SQL statements and passed to the RDBMS for processing. / All functions depend on the ArcGIS
software, such as ArcMap

Cont’d 4. Query

Spatial Database Product / Query / Functions
Oracle Spatial / Oracle Spatial uses a two-tier process for querying spatial data.
  • The first part of the query selects candidate rows based only on their spatial index.
  • The second step of the query looks at each candidate and determines if it meets the query exactly.
This two-tier approach allows for quick querying by leveraging the spatial indexes. / Over 400 spatial functions, Oracle Spatial includes functions for length/area calculations, buffer, centroid, convexhull, geometry intersection, union. list the major functions:
3 SDO_GEOMETRY object type method
17spatial operators
21 Geometry Function
5 Spatial Aggregate Functions
3 Coordinate System Transformation Functions
40 Linear Referencing Functions
6 Migration Procedures
11 Tuning Functions and Procedures
2 Utility Functions
PostgreSQL / PostGIS / Spatial queries are performed the same way they are constructed in any other database query. / Management Functions
AddGeometryColumn, DropGeometryColumn, SetSRID
Relate Functions
Distance, Equals, Disjoint, Intersects, Touches, Crosses, Within, Overlaps, Contains, Intersects, Relate
Processing Functions
Centroid, Area, Length, PointOnSurface, Boundary, Buffer, ConvexHull, Intersection, Difference, GeomUnion, Envelope, IsSimple, IsClosed, IsRing, NumPoints, ExteriorRing, NumInteriorRings, EndPoint, StartPoint, GeometryType, X, Y, & Z.
  1. Data loading:

Spatial Database
Product /

Data Loading

IBM DB2 spatial

extender / Spatial data may be imported into and exported from Spatial Extender through data exchange files. Two types are supported:
  • shapefiles and ESRI SDE transfer files.
  • Also supported are industry standard data types: Well Known Binary (OGC WKB) andd Well Known Text (OGC WKT)

ArcSDE

/
  • Raster data: ArcSDE supports the following formats: ERDAS IMAGINE, ERDAS(.lan & .gis), ERDAS Raw, GIF, BIL/BIP/BSQ, BMP, MrSID compressed images, JPEG, ADRG, PNG, ER Mapper, CIB, CADRG.
  • Vector data: shape file, AutoCAD, coverage, tab file for MapInfo etc.

Oracle Spatial

/
  • Writing SQL statement to create table & load data into the table directly.
  • Convert Shape to SDO
  • Using shp2sdo to create load assistant files
  • Using sqlplus to create table
  • Using sqlldr to load the actual data into the table
Other data formats should be converted into shapefile before loading.
PostgreSQL / PostGIS / Loading data can be done two ways.
  • The first way is using the loader, which converts ESRI Shape files into SQL suitable for loading in PostGIS/PostgreSQL. This is similar to importing data into an ESRI Geodatabase.
  • The other way to get data into the database are converting the data to a text representation, then use traditional SQL data loading statements.

6. Other Criteria

Spatial Database
Product / Compatibility with GIS applications / Server Capability / Stand-alone or part of DB system?

IBM DB2 spatial

extender / Supports visualization tools such as ESRI's ArcView GIS, ArcExplorer, ArcInfo, ArcView BusinessAnalyst as well as other vendors such as MapInfo.
To view graphic or map representation, a visualization tool must be used. / If deployed within DB2 edition with server capabilities then it can.
IBM recommends an architecture utilitizing ESRI’s ArcSDE for distributed computing. / DB2 Spatial Extender resides in IBM’s DB2 Universal Database

ArcSDE

/ Since ArcSDE if a part of ESRI GIS software fimaly, it supports all ESRI GIS software. At the same time, it support AutoCAD by Autodesk. / Serves spatial data to ArcGIS Desktop (ArcReader, ArcView, ArcEditor, and ArcInfo), to Internet clients through ArcIMS, and to applications developed with ArcGIS Engine and ArcGIS Server. / No, ArcSDE is only a gateway connected RDBMS to Client or GIS application

Oracle Spatial

/ Oracle Spatial is an option for Oracle Enterprise Edition that provides advanced spatial features to support high-end GIS and LBS solutions. / Integration with Oracle9i Application Server / Oracle Spatial is supported on Oracle Enterprise Edition.
PostgreSQL / PostGIS / Available GIS applications that can run PostGIS are uDig (User-friendly Desktop Internet GIS) and MapServer. Both are internet-web applications that can create, acquire, view, and modify spatial data. / PostgreSQL, an open source, object-relational database that is used in conjunction with PostGIS acts as the server. It is capable of running on Windows, Linux, and Unix operating systems. / PostGIS is a downloadable software that spatially extends PostgreSQL.

Summary

In order for DFW Stores, Inc. to evaluate retail site selection in-house, we need a spatial database to store/retrieve spatial data and enhance the company’s future. The main feature that we could use the spatial database for is deciding where to build/close down stores on the basis of costs, statistics, proximity of competitors, and potential clientele. Some functions that would be useful for our company are as follows:

  • View extent of sales area (buffer) and determine which customers live inside and outside of this area (geocode) or if the location of the store does not fully support customers in the area (centroid).
  • Determine nearest location from “model customers” via census tract data to nearest store (distance).
  • Ensure that new store sites do not interfere with other stores customers (overlap, contains, etc…).
  • Model non-producing stores with all available spatial data (customers, site location, competitor location, census bureau information, etc…) to determine why they do not produce so that the business model can be enhanced.

Criteria used in the selection for the spatial database are the following:

  • Cost
  • Standards met
  • GIS application
  • Spatial data types supported
  • Queries from the database
  • Spatial functions
  • Data loading
  • Compatibility with other GIS applications
  • Server capability
  • Ability to be stand alone or integration with database

While there are several databases and options that could have been used for this comparison, only the following spatial databases were reviewed:

  • ArcSDE—spatial data engine from ESRI, the market leader in GIS applications.
  • PostGIS—open source database and downloadable spatial extension
  • IBM DB2 Spatial Extender—major database vendor with own spatial extension
  • Oracle Spatial—database market share leader with own spatial extension

PostGIS

Despite the fact that PostGIS is absolutely free, it is hard to recommend it. As the saying goes ‘you get what you pay for’, and because of this fear, “in the spatial industry, open source freeware has a barely measurable market share compared with the proprietary products sold by … other geospatial vendors.” [9] Because of the limited market share, there are very few people that would be available for consulting/support in the event it was needed. Also, PostGIS is totally funded by Refractions Research and some “small time” consultant companies/vendors, and it has no major corporate support. There seems to be a high risk with longevity with this product.

ArcSDE

Built by the leading GIS vendor in the nation ESRI, it (ArcSDE) has little/no functionality by itself. This option requires the purchase of a qualified RDBMS ($30k - $40k), ArcSDE ($10k), and then ArcGIS Desktop (another $5k) in order to get the complete functionality of the system. The total is approximately $45,000-$55,000.

Granted, if this system were implemented, you would have one of the most capable and desirable Geographic Information Systems, but for our purposes, it is overkill.

Oracle Spatial

This selection is quite capable of handling the needs of this company. The functions that are built into it are exactly what this company is looking for. Also, it has been the leading database vendor for a long time, and has many followers. There is ample support available and plenty of consultants that would be available for hire if needed. However, the price for a single processor license of the Enterprise Oracle Database is $40k and one for Oracle spatial is $10k, which comes to a combined total of $50,000. If we were willing to buy this, then we should just go with ArcSDE and a non-Oracle Database.

IBM DB2 Spatial Extender

Very similar to Oracle Spatial, it has all the spatial functionality needed for our company. The IBM DB2 is the second leading vendor for RDBMS, so there are plenty of consultants available if needed. The ability to visually display the data in ArcExplorer (free by download from ESRI’s website) assisted in this decision. All created data can be stored in shape files for distributing data with other companies. Also, since the company will need to purchase a database for the storage of this system, it will be provided for free with the new purchase of the Enterprise Server Edition of the IBM DB2 database. The total cost for this spatial database will be ~$30,000. (This is $20,000 less than the Oracle Spatial and $15,000 less than the ArcSDE systems.)

Recommendation

The recommendation from this group is to use IBM DB2 Spatial Extender. It has the same functionality of Oracle Spatial, while being $20,000 less. It does not have the functionality of the ArcSDE system, but it is also $15,000 less. And, in the event we ever wanted all of the functionality of ArcSDE, then we could purchase it and update the database at that time.

Appendix I: Functions for use with Oracle Spatial

Over 400 spatial functions, Oracle Spatial includes functions for length/area calculations, buffer, centroid, convexhull, geometry intersection, union. list the major functions:

1. SQL Statements for Indexing Spatial Data

1

ALTER INDEX

ALTER INDEX REBUILD

ALTER INDEX RENAME TO

CREATE INDEX

DROP INDEX

1

2. SDO_GEOMETRY Object Type Methods

1

GET_DIMS

GET_GTYPE

GET_LRS_DIM

10 Spatial Operators

SDO_FILTER

SDO_NN

SDO_NN_DISTANCE

SDO_RELATE

SDO_WITHIN_DISTANCE

1

3. Geometry Functions

1

SDO_GEOM.RELATE

SDO_GEOM.SDO_ARC_DENSIFY

SDO_GEOM.SDO_AREA

SDO_GEOM.SDO_BUFFER

SDO_GEOM.SDO_CENTROID

SDO_GEOM.SDO_CONVEXHULL

SDO_GEOM.SDO_DIFFERENCE

SDO_GEOM.SDO_DISTANCE

SDO_GEOM.SDO_INTERSECTION

SDO_GEOM.SDO_LENGTH

SDO_GEOM.SDO_MAX_MBR_ORDINATE

SDO_GEOM.SDO_MBR

SDO_GEOM.SDO_MIN_MBR_ORDINATE

SDO_GEOM.SDO_POINTONSURFACE

SDO_GEOM.SDO_UNION

SDO_GEOM.SDO_XOR

SDO_GEOM.VALIDATE_GEOMETRY

SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT

SDO_GEOM.VALIDATE_LAYER

SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT