Spatial DBMS

This topic continues the discussion begun in the Data Storage Strategies topic. In this topic we look more closely at storing GIS data, in particular drawings, within database management system (DBMS) packages.

Manifold can store drawings, images and surfaces in almost any DBMS using Manifold-managed spatial DBMS capabilities. In addition, Manifold can store drawings within major spatial DBMS products (IBM DB2 with IBM Spatial Extender, Microsoft SQL Server 2008 spatial, Oracle and PostgreSQL) using the native spatial DBMS facilities within those products. Finally, Manifold can store images and surfaces within Oracle DBMS products supporting Oracle GeoRaster capability.

In addition to using Manifold or native spatial DBMS capabilities, Manifold can also connect to ESRI-style SDE geodatabases and Personal geodatabases and import and link (for read/write editing) drawings in SDE or Personal geodatabases.

Two Types of Data in Drawings

Most GIS packages, Manifold included, use two types of data to make up drawings:

· One type of data is the geometric information that defines objects in the drawing. This type of geometric information is often simply called the geometry of the drawing. It specifies the shape and location of the points, lines and areas of which the drawing consists.

· The second type of information is the data in the optional attributes that may be attached to objects in a drawing. For example, a drawing that consists of points showing the locations of cities might also have attributes that give the name of each city, the population of that city and other information.

GIS packages use geometry to draw the shapes and locations of points, lines and areas in a window. Attribute data is normally shown using a row and column table visual interface like that used in DBMS packages. A drawing may or may not have attributes for the objects it contains, but it always has geometry because without geometry there would be no points, lines or areas in the drawing.

Storing Attribute Data in a DBMS

A GIS package becomes more flexible, useful and powerful if it can work with attribute data that is stored in an external DBMS. That's very useful even if the GIS continues to manage all of the geometry.

Many GIS packages have this capability, although it is usually limited to read-only linkages or available only in a limited number of data access methods, such as ODBC. Manifold allows linking of attribute data from an external DBMS with full read / write / edit capabilities using a very wide range of data access methods. See the Relations topic and the Attaching External Tables to Drawings topic for Manifold techniques for using attribute data from external sources.

Linking attribute data from a DBMS brings a lot of advantages to GIS users. The foremost advantage is that there is much more data maintained in commercial DBMS products than exists in GIS storage, so being able to link data into GIS projects from an external DBMS gives access to a much wider range of data.

The second key advantage is the ability to dynamically update a GIS project using data that is maintained in some external DBMS. Suppose, for example, we want to graphically display the sales of our company in various regions by thematically changing the color of regions in accordance with a sales attribute for each region. If the sales attribute is automatically fetched into the drawing from our corporate DBMS, whenever that corporate DBMS is updated by some other process (such as, perhaps, by our company's order entry system), our drawing will automatically be updated as well.

A third advantage is that storing attribute data in a DBMS also can take advantage of ubiquitous data interchange between applications that can work with DBMS. If our attribute data is stored in a DBMS there are likely thousands of applications and utilities that can work with it without having to know anything about our GIS package. Applications can connect to the DBMS as they have long known how to do, while the GIS takes data as needed from the DBMS.

Storing Geometry Data in a DBMS

There are many benefits to storing attribute data in an external DBMS even though geometry continues to be managed exclusively within the GIS. But so long as the GIS manages geometry users will be subject to whatever limitations that may involve. There are usually several limitations imposed by having geometry managed exclusively by the GIS package.

The first limitation is that GIS packages usually do not have the sheer capacity and cluster scalability of mainstream DBMS packages like Oracle or SQL Server. As a result, the number of objects in a drawing will normally be limited by the performance of the GIS running on a single machine.

A second limitation is often a restriction to single users or single processes working with the geometry data stored by the GIS. Mainstream DBMS packages have evolved to meet intensely multiuser, multi-process needs but most GIS packages (Manifold is an exception) have not been built with the multi-process, transaction-oriented architecture required for intensively multiuser operations on geometry.

A third limitation is limited dynamic interoperability between different GIS packages and other applications. GIS data can be interchanged more or less successfully in a static, limited way using well known formats such as shapefiles, but cannot in general be interchanged dynamically as is taken for granted with DBMS servers.

A GIS that can store geometry in a DBMS can escape the above limitations. Storing geometry in a DBMS can take advantage of the capacity and scalability of the DBMS, resulting in drawings that can be terabytes in size. If a DBMS stores geometry, a GIS can operate as a client to the DBMS server in multi-user settings, taking advantage of the extensive apparatus a modern DBMS will provide to manage the simultaneous use of data by many different users. Storing geometry within the DBMS using data types native to the DBMS allows interchange with any application that understands those data types, which is usually many more applications than can parse the proprietary internal formats used for geometry storage within even very popular GIS products.

Manifold can work with geometry stored in databases using the widest and most flexible range of data access methods of any GIS system. In fact, Manifold can do so simultaneously with many different data sources and access methods, including geometry stored within Manifold itself.

Native Geometry and Non-Native Geometry Types

All data stored in a DBMS is ultimately in binary form. When storing geometry within a DBMS the question is what internal format the DBMS should use to order the binary data used to store that geometry. There are two approaches.

One approach is used in DBMS products sold as "spatial" DBMS. A spatial DBMS will have a pre-defined way of organizing binary data to represent geometry, and this pre-defined way of organizing binary data is built into the DBMS in the form of a data type, such as SDO_GEOMETRY in Oracle Spatial or ST_GEOMETRY in IBM's DB2 Spatial Extender. Because this data type is built into the DBMS it is called a native geometry type. The data is still binary data, of course, but it has been organized in accordance with a format expected for geometry data by the DBMS. When a DBMS product has its own, native geometry type it also usually supports that data type with additional infrastructure, such as the automatic creation of spatial indices or the provision of DBMS server commands that understand that data type.

The other approach is used with DBMS products that do not specify a pre-defined way of organizing binary data to represent geometry but which allow applications to utilize a generic binary data type. Almost all modern DBMS packages provide a generic binary data type that can be used to store binary data unstructured by the DBMS. Often referred to as a blob, such generic binary storage can be employed by applications as they see fit. When such generic binary storage is used to store geometry in a form not built into the DBMS it is called a non-native geometry type. Although the use of non-native geometry types allows storing geometry within general-purpose DBMS products without requiring a special "spatial" form of a DBMS, it does require a GIS application that supports the geometry formats to be used.

Manifold can use either native or non-native types of storage when available within a DBMS and can even use both types simultaneously. It is important to note that in either case the data is stored in binary form using some organizing format to represent geometry in that binary data. The difference is that in the one case the DBMS has its own special format for organizing that binary data and in the other case it is the GIS interpreting that binary data. Both cases can be equally fast if the non-native type is an efficient geometry format such as Manifold GEOMETRY, since in both cases the speed is a function of the DBMS's ability to work with binary data, perhaps as assisted with specialized indices.

The main advantages of using a native geometry type within a spatial DBMS is first, that doing so provides interoperability with any application that uses the native type, and second, using a native type automatically takes advantage of the infrastructure within the spatial DBMS that supports that native data type. The disadvantage is that a native geometry type might not be available within the DBMS package we desire to use, or that it may involve extra cost.

The main advantages of non-native binary storage are first, Manifold makes use of spatial DBMS functionality utilizing non-native types within virtually every DBMS and second, using a choice of non-native geometry types can open the door to greater flexibility than is possible by committing to a single data type within a single DBMS vendor. A possible disadvantage is that choosing from a variety of geometry types can make interoperability with other applications more difficult. However, if a generic geometry type that is well-understood and accepted by many applications, such as WKB, is used, then interoperability might well be preserved.

Spatial DBMS

A spatial DBMS can make it especially convenient to leverage the power of the DBMS by using native geometry types within the DBMS and by extending the internal logic of the DBMS to handle spatial operations of interest for GIS applications. When a DBMS offers a native geometry type together with supporting capabilities (such as spatial indices) it is referred to as a spatial DBMS.

GIS has become such big business that major DBMS vendors have extended their DBMS products with native geometry types as well as with supporting capabilities. Locator capability within regular Oracle DBMS editions as well as Oracle's dedicated spatial product, Oracle Spatial, provide Oracle's SDO_GEOMETRY type as well as Oracle GeoRasters and powerful spatial operations. Other DBMS packages such as IBM DB2 have spatial extenders and open source DBMS packages such as PostgreSQL now include "spatial" capabilities as well.

Microsoft SQL Server allows third parties to extend the DBMS product to provide a native geometry type and spatial operators, as several third parties have done. In addition, Microsoft has announced that SQL Server 2008 (at the present writing code-named Katmai) will provide Microsoft-engineered geometry types and supporting spatial capabilities.

The availability of a spatial DBMS with a native geometry type together with built-in spatial operators confers enormous benefits to a GIS package that can work with that spatial DBMS. Capacity and scalability can increase to whatever the DBMS can handle, terabytes in the case of modern DBMS server clusters. Even if a GIS cannot manage terabytes at one time, by using the spatial capabilities of the DBMS to extract data in manageable area of interest (AOI) subsets, a GIS user can work effectively with that part of the data of interest taken from a data set that is much larger than the GIS client alone could handle.

When geometry is stored in a spatial DBMS multiuser and transaction issues need no longer be limited by the GIS but instead can be handled by the formidable apparatus of the DBMS while the data itself can be protected by the transactional integrity of the DBMS. DBMS servers provide transactions, triggers, views and many other capabilities not often found within a GIS.

Because mainstream DBMS packages are used by organizations for many more applications than GIS, storing data within the DBMS package's own native data types tends to assure interoperability with a far greater number of applications than can work with any one GIS vendor's proprietary formats, potentially including a greater number of GIS packages as well. GIS vendors may not be able to agree among themselves to open their formats to each other, but every GIS vendor knows they are not in the game if they do not support Oracle's SDO_GEOMETRY.

Spatial Indices

The spatial logic in a spatial DBMS normally consists of enabling infrastructure in the form of spatial indices maintained by the DBMS together with a collection of spatial operators that can be executed server-side by the DBMS server. A spatial index is a specialized form of DBMS index maintained for the geometry type that makes it possible to quickly identify objects based upon spatial characteristics, such as the location and extent of a particular object. Spatial operators are normally functions that ascertain spatial relationships, such as finding all objects that are contained within a given object.

Manifold can establish a spatial index within a DBMS for a database object (a table) that is to be treated as a drawing. A spatial index can only be created for a table that has a primary key. A primary key is created automatically whenever a drawing is exported to a DBMS and whenever the ID field is exported when a table is exported into a DBMS.

Once a spatial index is established for a table that is treated as a drawing, that index will be maintained primarily by the DBMS server. The spatial index allows the DBMS to fetch subsets of objects based upon location and extent, an obvious usage being to fetch manageable subsets of objects for a given area of interest from very large drawings.

Adding Spatial DBMS Capability to "Ordinary" DBMS

Manifold releases from Release 8 onwards can confer spatial DBMS functionality to almost any DBMS package even if the DBMS does not itself have "spatial" features such as a native geometry type or spatial indices. Manifold does this through use of Manifold-supported non-native geometry types and through Manifold-created spatial indices which the DBMS then operates.

Manifold releases prior to Release 8 supported use of non-native geometry types but without supporting spatial DBMS infrastructure. From Release 8 onward Manifold has introduced richer facilities that blur the distinction between native and non-native geometry storage by providing Manifold-sponsored spatial infrastructure within the DBMS to supports usage of non-native geometry as is done with native geometry types within a spatial DBMS.

There is nothing about a "spatial" DBMS's native geometry type which makes it any better as a binary format for storing data than, say, Manifold's own GEOMETRY type. Embed within a DBMS a spatial index tailored for GEOMETRY and we have almost all of what people really use within a spatial DBMS. In fact, Manifold GEOMETRY is actually significantly more efficient and powerful than the native geometry types used by either DB2 or PostgreSQL. It is just that those DBMS have some facilities that assume their native geometry type is in a particular format.

Manifold Release 8 introduced the idea of adding a generic spatial index in the target DBMS for Manifold geometry data stored in the DBMS. In such cases, Manifold stores geometry data as a binary blob, but it does so in a way the DBMS would do if it had its own geometry type, that is, with a spatial index maintained to allow sophisticated and powerful manipulation of that geometry data. The spatial index established by Manifold is maintained by the DBMS server using the power of the DBMS package and will automatically be updated if a linked drawing is edited. This confers upon ordinary DBMS packages a true "spatial" capability maintained together by the DBMS and by Manifold.

Some people may say that a DBMS cannot be considered a true "spatial" DBMS unless in addition to having a geometry type plus supporting spatial infrastructure such as spatial indices it also has the ability to do server-side spatial operators, for example, to have operators such as a CONTAINS within the DBMS package's own SQL. Server-side operators are very important when the spatial DBMS is used as the "black box" infrastructure behind some application, such as a web site where data from the database is fetched using SQL queries or other code. However such server-side spatial operators are not usually employed when a rich GIS package is used as a "front end" to the spatial DBMS.

Server-side spatial operators are almost never used in an environment where the interaction with the spatial DBMS occurs through a very rich client like Manifold. The reason is that most people prefer a point-and-click user interface for spatial tasks and find it difficult to work within a black-box, command-line (that is, exclusively SQL) environment as often ends up being the only useful way to work with server-side spatial components. As a practical matter, when a GIS package like Manifold provides the user interface perhaps the only server-side operation executed by most users is an Area of Interest (AOI) specification to grab a manageable subset of some very large drawing. However, AOI does not require server-side spatial operators - it requires only a spatial index as conferred by Manifold.

Therefore, the use of a Manifold-declared spatial index together with straightforward Manifold GEOMETRY storage within an "ordinary" DBMS such as MySQL or Microsoft SQL Server 2005 (that is, regular SQL Server and not the new spatial support in SQL Server 2008) can provide the benefits sought by users through "spatial" DBMS. It is a way for GIS users to get outstanding price/performance by using ordinary DBMS packages, even possibly free packages, as true spatial DBMS servers storing data of effectively limitless capacity, storing drawings, images and surfaces, and to leveraging the power of increasingly multiprocessor systems and increasingly immense RAM.

In fact, by using the spatial DBMS capability conferred by Manifold onto a regular DBMS we can usually gain more "spatial" capabilities than are provided by some spatial DBMS packages. For example, Manifold conferring spatial capability onto an ordinary DBMS allows us to store images and surfaces as well as drawings while some spatial DBMS packages can only store drawings.

Geometry Storage Choices

The bottom line to the above discussion is that Manifold provides two ways of storing drawings within spatial DBMS:

· Native storage within a spatial DBMS - Examples are Oracle Spatial, IBM DB2, Microsoft SQL Server 2008 (Katmai) or PostgreSQL using the native geometry type specified by the DBMS. Connecting to one of these spatial DBMS packages using the "native" connection, such as OCI for Oracle, tells Manifold to automatically convert local Manifold geometry for drawings into whatever the target spatial DBMS users as its own native geometry type.

· Non-native storage within almost any DBMS - Examples are storing drawings as tables within, say, MySQL or SQL Server using any of the binary geometry types supported by Manifold, such as GeomWKB (OGC WKB), GeomSHP (legacy ESRI geodatabases), or GEOMETRY (Manifold's own high performance geometry type). In addition, Manifold can establish a generic spatial index. The spatial index is used together with the binary data to allow "ordinary" DBMS packages to function as a spatial DBMS.

It is quite possible to use any of the above storage methods within the same DBMS. For example, Oracle provides Locator spatial capability so drawings can be stored in Oracle using Oracle's native SDO_GEOMETRY type as with a typical spatial DBMS. But Oracle also provides a full roster of other DBMS types, including generic binary storage, that may be used for non-native geometry types. If desired, we can store non-native geometry within Oracle tables, establish generic indices and so on. In fact, within the same Oracle Spatial DBMS Manifold could have three different drawings stored as three different tables with each table using a different geometry type.

Specifying the Spatial DBMS Technology to Use

If Manifold can work with spatial DBMS using either a vendor's native spatial DBMS technology or spatial DBMS technology provided by Manifold, how do we specify which technology we would like to use for any particular drawing?

· Choice of Type in Export Drawing Dialog - When exporting a drawing to a database the Export dialogs give us a Type box which allows us to specify either Manifold, for Manifold-managed geometry types or, if a native connection technology was used to connect to a spatial DBMS, the name of that spatial DBMS vendor.

· Automatic Choice when Importing or Linking - Manifold dialogs for importing or linking will automatically use whatever technology is used for storage of that component. When Database Console connects to a data source Manifold will examine the source and determine what technology is in use and use that technology when importing or linking any given component.

For example, if we connect to an Oracle server when exporting a drawing we will see two choices available in the Type box: Manifold, and Oracle. Choosing Oracle will export the drawing into Oracle's native spatial system using SDO_GEOMETRY. Choosing Manifold will export the drawing using whatever non-native geometry type we specify in the dialog together with creation of a Manifold-established spatial index.

When importing or linking from a database if we want to use a spatial DBMS's native spatial facilities we must connect using that DBMS's native connection technology. Once connected, Manifold will automatically recognize whether the data in use is the vendor's native spatial geometry, Manifold-managed non-native spatial geometry or ESRI SDE geodatabase or Personal geodatabase geometry (see below for information on ESRI geodatabases).

When Enterprise Edition is Required

Manifold Enterprise Edition or above is required to connect using native connections to spatial DBMS such as Oracle Spatial, DB2 with IBM Spatial Extender, SQL Server 2008 Spatial (Katmai) and PostgreSQL. Therefore, any usage of native spatial capabilities within these spatial DBMS products requires Enterprise Edition or above.

Manifold Enterprise Edition or above is required to create a spatial index when using Manifold to confer generic spatial DBMS capability upon almost any DBMS; however, once Enterprise Edition is used to create the spatial index any Manifold edition may be used to work with that data and that generic spatial index.

For example, we could use an Enterprise Edition license to load a SQL Server 2005 database with drawings supported with spatial indices, images and surfaces. We could then have many of our colleagues work with those drawings (including full read/write/edit capabilities), images or surfaces using Personal Edition licenses. However, if any of our colleagues wanted to upload a new drawing into that SQL Server database they would have to use an Enterprise Edition license to upload the drawing with an associated spatial index.

As a practical matter, large organizations that are working seriously with spatial DBMS using a natively spatial DBMS like Oracle Spatial or SQL Server 2008 Spatial will deploy Manifold using Enterprise licenses. Because of the very low cost of Enterprise Edition even those organizations that will use non-native spatial DBMS will tend to deploy Manifold using exclusively Enterprise Edition licenses so that all users can upload new drawings at any time.

In some specialized settings it can be useful to employ Personal or Professional licenses to connect to spatial DBMS to work with drawings previously uploaded using Enterprise Edition. Examples include small organizations on a budget, custom applications that utilize a Professional Runtime license, or organizations where there are many users with very undemanding, mainly "view only" interactions who will never be creating new drawings.

Supported Spatial DBMS

Manifold introduced support for spatial DBMS with support for Oracle SDO_GEOMETRY and Oracle GeoRaster formats in Manifold Release 7.00. Support for Oracle Spatial was extended in Manifold Release 7x and support for additional spatial DBMS packages was added in Manifold Release 8.

Manifold now features transparent, read / write / edit, multiuser connections to a variety of spatial DBMS packages. In general, operation is fully automatic: when a Manifold drawing is uploaded to a spatial DBMS the Manifold geometry will automatically be converted into the native DBMS geometry type. When a drawing is linked or imported from a spatial DBMS source the native DBMS geometry will be automatically cast into Manifold geometry.

When using generic spatial indices to store data as Manifold-managed data instead of using a native spatial DBMS, we have the choice of what geometry type we would like to use. Normally the choice will be made between Manifold Geometry type or OGC WKB type, as using ESRI geometry types does not make sense except for specialized applications such as interoperability with data trapped in legacy systems.

Current spatial DBMS products supported by Manifold include:

· IBM DB2 with Spatial Extender - Spatial DBMS functionality with DB2 depends upon installation of the IBM Spatial Extender for DB2.

· Microsoft SQL Server 2005 - Implemented using the Manifold Spatial Extender for SQL Server or through use of Manifold-managed generic spatial DBMS capability.

· Microsoft SQL Server 2008 Spatial - Microsoft has announced that SQL Server 2008, code-named Katmai, will provide spatial DBMS functionality. Manifold supports SQL Server 2008 spatial today for those Microsoft employees, partners, beta testers and independent developers who have been given access to SQL Server 2008 spatial and will continue to support SQL Server 2008 spatial when it is released to the public.

· Oracle Spatial and Oracle Locator - All Oracle products now have Locator capability to allow spatial DBMS capabilities with vector drawings. Oracle Spatial is required to store images and surfaces using Oracle's GeoRaster type.

· PostgreSQL - An open source DBMS that is extended into a spatial DBMS via the PostGIS package.

· ESRI SDE and Personal Geodatabases - Not really a separate spatial DBMS but rather a collection of proprietary ESRI types, metadata tables and facilities imposed upon one of the DBMSs supported by ESRI. Since many ESRI people think of these as spatial DBMS they are enumerated here because Manifold supports them as well.

The variety of options for Microsoft SQL Server are worth repeating with additional detail Manifold supports several ways of performing spatial DBMS work with Microsoft SQL Server:

· Microsoft SQL Server 2008 (codenamed Katmai) - This new Microsoft product will provide spatial support using native spatial DBMS capability like other spatial DBMS packages such as Oracle Spatial or IBM DB2 with Spatial Extender. Manifold Enterprise Edition and higher editions have built-in capability to works with SQL Server 2008 spatial to support native SQL Server 2008 spatial capabilities. As of this writing, SQL Server 2008 has been announced by Microsoft but has not yet shipped as a production product. As is typical with new Microsoft products, Microsoft has begun providing more and more information on this important new product and has made pre-release versions of SQL Server 2008 available to selected Microsoft partners. If Microsoft has provided SQL Server 2008 spatial to you, that should be your first choice for spatial DBMS work with Microsoft SQL Server.

· Microsoft SQL Server 2005 with Manifold Spatial Extender - Manifold has created a spatial extender for SQL Server 2005 to enable users to work with SQL Server 2005 as a high-performance spatial DBMS until SQL Server 2008 is released. The Manifold spatial extender utilizes Manifold geometry and Manifold spatial indices with a small amount of code that is uploaded into the SQL Server 2005 server for execution server-side. The uploaded code enables SQL Server 2005 to achieve better performance than a purely generic use of spatial indices. The Manifold Spatial Extender also works with SQL Server 2008; however, if we have SQL Server 2008 we should use SQL Server 2008's spatial capabilities. Note: When used with SQL Server Express 2005 the Manifold Spatial Extender requires SQL Server Express 2005 SP2.

· Microsoft SQL Server 2005 with Manifold Generic Spatial Index - Because SQL Server 2005, like almost every other big-name DBMS, provides binary storage we can use Manifold's generic, non-native spatial DBMS capability within SQL Server 2005 using whatever Manifold-supported geometry type we like (such as Manifold's Geometry or OGC WKB) together with Manifold-created spatial indices. This usage of SQL Server 2005 is just as we would use any other ordinary DBMS, such as MySQL, as a spatial DBMS through Manifold-managed spatial facilities. For performance reasons, it is always a better idea to use the Manifold Spatial Extender for SQL Server than to use generic spatial indices. Manifold generic spatial indices also work with SQL Server 2008; however, if we have SQL Server 2008 we should use SQL Server 2008's spatial capabilities.

· Microsoft SQL Server 2005 with ESRI SDE - ESRI products can utilize a SQL Server 2005 installation to host an ESRI SDE geodatabase. If SQL Server 2005 hosts an ESRI SDE geodatabase Manifold can connect to that ESRI SDE geodatabase and import and link drawings using ESRI conventions. It is possible for SQL Server 2005 to be used by ESRI products to host Personal geodatabases as well, in which case Manifold can also connect and work with that geodatabase like the SDE case. However, as of the present writing ESRI Personal geodatabases are usually encountered only in the form of Access .MDB files and only rarely using SQL Server, where SDE appears to predominate.

Important: Manifold.net cannot provide any information on SQL Server 2008. For information on this announced but not-yet-published new Microsoft product, you must contact your Microsoft sales and marketing representatives, consult the Microsoft website and otherwise make use of informational resources Microsoft makes available to you. Manifold's support for SQL Server 2008 spatial is mentioned in this documentation and has been made available within Manifold for the convenience of those people to whom Microsoft has provided pre-release versions of SQL Server 2008.

In addition to the usual roster of ADO .NET, OLE DB, and ODBC connections Manifold Enterprise Edition and higher editions provide specialized native DBMS connection choices that are used when adding a new data source using a spatial DBMS in the Data Source dialog. See the Data Source dialog topic for a current list.

The Manifold Spatial Extender

The Manifold Spatial Extender for SQL Server is a free extension module for SQL Server which provides spatial index functionality for Manifold System. The Manifold spatial extender works with all versions of SQL Server starting with SQL Server 2005. Always make a point of installing the Manifold spatial extender on the server machine whenever using SQL Server 2005 as a spatial DBMS.

See the Manifold Spatial Extender for SQL Server topic for details.

Connecting to ESRI ArcSDE, ArcGIS and Personal Geodatabases

ESRI, a legacy GIS vendor, has introduced a variety of products that can work with geometry and attribute data stored in DBMS servers. These are not spatial DBMS servers as such are generally understood, but rather are software products and middleware software that manage data stored in real DBMS servers using either blob or geometry types if the DBMS is a spatial DBMS. It is a classic case of a GIS vendor providing their own spatial capabilities for a DBMS by using their own non-native geometry types together with supporting capabilities supplied by the GIS vendor.

Manifold can also work with non-native data stored in databases using ESRI conventions. In such cases Manifold will work with ESRI's geometry types and supporting metadata tables using ESRI conventions for compatibility with ESRI products.

Many users are baffled by ESRI nomenclature when it comes to parsing the bewildering variety of marketing phrases ESRI has used to describe ESRI "geodatabase" formats. If you feel baffled, you are not alone. In a nutshell, ESRI at one point introduced the idea of storing geometry in DBMS using a format that more or less boiled down to storing shapefiles within blobs. This was done in a complex way using middleware called ArcSDE that worked with serious databases like Oracle, and it was also done in a somewhat simplified way in Personal Geodatabase products that used Access .MDB files and were later apparently updated to work with MSDE (a free version of Microsoft SQL Server) or with SQL Server Express. In recent years, the ArcSDE product name seems to have been dropped by ESRI: more recent versions of this technology have been packaged as part of the ArcGIS product family and have been referred to as geodatabases.

All such storage methods are technically similar and are generally referred to as SDE geodatabase formats or as Personal geodatabase formats when in the somewhat simplified form that uses Access .MDB for file-based storage. Since all such formats are similar or derived from ArcSDE, they are referred to by Manifold documentation as ESRI SDE or as ESRI Geodatabase or as Personal geodatabase data sources, the various terms being used interchangeably, regardless of which file format or DBMS system is used to store the data.

The terms are used interchangeably because some ESRI users come from a long ArcSDE tradition and don't realize that "geodatabase" is the new term for the same old thing, while some newer ESRI users might not realize that their "geodatabase" is really SDE technology with a new name. Because of the confusion caused by ESRI names for their SDE and their Personal technology being so similar, Manifold documentation will often refer to SDE and Personal geodatabases to underline that a particular capability is available whether one is working with either SDE geodatabases or the somewhat simpler Personal geodatabases.

Manifold can also connect to ESRI SDE and Personal geodatabase data sources for full read / write / edit capability. The only limitation is that unlike all other work with all other spatial DBMS, Manifold will not create new SDE or new Personal geodatabases, nor will Manifold add new drawings to an existing geodatabase. If we already have drawings in an SDE or Personal geodatabase, Manifold will happily import or link to those drawings. We can edit those drawings, adding new objects and deleting or editing old objects and in general perform whatever operation we like. For example, we could link to an existing drawing in a geodatabase and then copy and paste objects from some other drawing into that drawing. However, we cannot create new drawings or new geodatabases.

There are two reasons for this limitation. The first reason is that SDE and Personal geodatabases are seen as legacy storage that one would use only to rescue data already trapped in those formats or, in a pinch if the data could not be transferred to some more sensible format (such as native storage in Oracle Spatial or SQL Server 2008 spatial), to work with the data trapped in the ESRI geodatabase. There is no point in writing new tools to add to the amount of data trapped in a proprietary and risky format. If someone wants to add a new drawing to an existing SDE or Personal geodatabase storage, they can do so using the ESRI tools they used to create that geodatabase in the first place. If someone has data trapped in an SDE or Personal geodatabase by definition they have ESRI tools on hand to trap yet more drawings in those geodatabases if that is what they must do.

The second reason is that from a technical perspective both SDE and Personal geodatabases are regarded by Manifold engineering as lacking desired reliability due to the convoluted, old-fashioned architecture employed in those ESRI technologies. Simply put, the manifold.net team does not want to be blamed for problems arising from that lack of robustness. If someone wants to create a new drawing in an ESRI geodatabase and they run into problems when using an ESRI tool to create that drawing, then the problem is clearly ESRI's fault. In contrast, if Manifold were used to create that new drawing no matter how well Manifold accomplished the task if problems arose due to the basic architectural unreliability of ESRI geodatabases it would be Manifold that would be blamed. No thanks!

Storing Images and Surfaces within Spatial DBMS

Manifold provides two ways of storing images and surfaces within a DBMS:

· Oracle GeoRaster - Connecting via OCI (Oracle's native Oracle Call Interface) to an Oracle database that has GeoRaster capability (Oracle Spatial or Oracle Enterprise) allows us to store images within Oracle's native GeoRaster data type.

· Generic image storage - Connecting via ODBC or OLE DB to almost any DBMS that allows binary storage allows us to store images and surfaces (which are stored as images with an additional Height channel). Images are stored as tiles, with the export process allowing choice of a tile size. Exporting an image to such a data source creates a table with a record for each image tile. Image tiles are stored in compressed or uncompressed BGRA format. Importing or linking images from such a data source automatically reassembles tiles. Surfaces cannot be linked, but can be imported by importing the Height channel of the image that is stored for them into the database.

Manifold uses Database Console to connect to a database storing images to import or link to a stored image. Database console will recognize tables containing image tiles and will display them as images, allowing us to import or link to the image by using the database console's Import or Link buttons. We can also import individual channels of such images as surfaces.

Units of Measure in Linked or Imported Surfaces

A limitation of Manifold's mechanism for storing surfaces in spatial DBMS is that the linked or imported surfaces are expected to represent heights in meters. If a linked or imported surface is generated using a data set in which heights are expressed in feet or some other unit, then the hill shading effect will be overly dark. The workaround to this is to convert surface heights to meters before export to a database if they are in feet, using the Surface Transform calculator or an intermediate query. See the Queries and Images or Surfaces topic for an example query that will do the trick.

Projections and Spatial DBMS

Given the different methods of storing drawings within a DBMS there is some variation as to how projection information is also stored. In general, when importing or linking drawings from spatial DBMS Manifold will acquire the correct projection to use for that drawing. For the sake of discussing projections, we can consider three types of drawings stored in DBMS:

· Native drawings, that is, drawings which use geometry types and metadata or projection codes specific to a particular spatial database. Examples include drawings stored as SDO_GEOMETRY within Oracle, ST_GEOMETRY within IBM DB2 with Spatial Extender, native PostgreSQL geometry or native SQL Server 2008 geometry.

· ESRI drawings, that is, drawings which use geometry types and metadata specific to a particular ESRI technology, which may or may not be tied to a particular database. Examples include SDE drawings stored within SQL Server using ArcSDE or ESRI Personal Geodatabases stored within Access .MDB.

· Manifold drawings, that is, drawings stored by Manifold within any DBMS using Manifold's generic spatial DBMS capability (or using the Manifold spatial extender with SQL Server), regardless of which geometry type is used. Examples include drawings stored by Manifold using OGC WKB within MySQL with a spatial index created by Manifold or drawings stored by Manifold using Manifold Geom within, say, SQL Server Express with a spatial index created by Manifold.

Native drawings store coordinate systems using means specific to that particular spatial database. Such means usually include a table or a set of tables which store the definitions of all coordinate systems together with a table storing the bindings between columns in the database tables which contain geometry data and their coordinate systems.

Manifold can handle native drawings within DB2, Oracle and PostgreSQL and SQL Server 2008 spatial. Exporting a native drawing from Manifold allows selecting a coordinate system from the list of those registered in the database and creates the binding between the geometry column in the exported table and that coordinate system. Manifold will attempt to match the coordinate system in use to a coordinate system supported by the spatial DBMS. If an exact match is not found, Manifold will re-project the data into the closest coordinate system (projection) found. Importing or linking a native drawing into Manifold reads the binding and retrieves the coordinate system.

When reading and writing spatial data stored on SQL Server 2008 spatial, Manifold represents coordinate systems using EPSG codes. EPSG, the former European Petroleum Survey Group organization, has been absorbed into the International Association of Oil and Gas Producers (OGP), but the database of coordinate systems is still widely known as EPSG. EPSG comprises one of the largest vendor-neutral, unambiguous and expertly-developed sets of coordinate systems known using EPSG within SQL Server 2008 spatial is an ideal way of eliminating the confusion one might encounter with proprietary methods of specifying coordinate systems.

Although EPSG codes cover just about every coordinate system one might use, because Manifold can handle an even larger set of projections than those covered by EPSG, it is possible that a drawing in Manifold uses a coordinate system (projection) not available in EPSG. In that case, when the drawing is exported into SQL Server 2008, Manifold will re-project it on the fly into the nearest EPSG equivalent and use that EPSG code. This is similar to how Manifold will re-project drawings as needed for export into Oracle if a Manifold projection is used that is unavailable in Oracle. As is the case with Oracle, built-in Manifold coordinate systems include all EPSG coordinate systems so that drawings imported or linked from SQL Server 2008 spatial need never be re-projected for use in Manifold.

SQL Server 2008 spatial allows third parties to choose some other coding method to represent coordinate systems instead of EPSG. If some third party application has used some other coding scheme then Manifold will not be able to automatically detect the correct coordinate system in use, and the correct coordinate system will have to be specified using the Edit - Assign Projection dialog after the drawing is imported or linked into Manifold.

ESRI drawings are similar to native drawings but are not tied to a particular type of a database except as supported by ESRI. For example, Personal geodatabases are usually encountered only in the form of Access .MDB files, and not all DBMS systems are supported for use with SDE. There is a table storing the definitions of all coordinate systems, and a table storing the bindings between geometry columns and coordinate systems. Importing or linking an ESRI drawing into Manifold reads the binding and retrieves the coordinate system. Both Personal and SDE geodatabases store coordinate systems using a format similar to the PRJ extension introduced by ESRI to support storage of projected data within shapefiles. As a result of years of experience encountering many different variations of PRJ files Manifold is able to read virtually all coordinate systems encountered in either Personal or SDE geodatabases.

Manifold drawings are the simplest of all in that when Manifold exports a geometry type and creates a spatial index it also creates a table named MFD_META table to host metadata used by Manifold. All metadata is stored by Manifold in the MFD_META table, including information about coordinate systems (projections) used. Exporting a Manifold drawing writes the coordinate system into MFD_META. Importing or linking a Manifold drawing reads the coordinate system from MFD_META.

In general, Manifold always writes the coordinate system used during export of a drawing and always reads the coordinate system in use when importing or linking.

Projection Matching During Export

Different spatial DBMS vendors support different collections of coordinate systems (projections), as does Manifold. When exporting a drawing to a spatial database, Manifold matches the coordinate system of the drawing to the closest coordinate system supported by the database. If the database does not support the exact coordinate system of the component, Manifold will re-project objects on the fly.

To know the coordinate system that will be assigned to the exported component, press the [...] browse button near the projection readout in the export dialog. Manifold will show a dialog with a list of coordinate systems supported by the database with the intended coordinate system selected.

Sometimes DBMS vendors will use different names for what are the same coordinate systems, or may not even use obvious names. For example, each coordinate system in a PostgreSQL database is assigned a unique identifier, called an SRID (Spatial Reference system ID).

When exporting a drawing to a PostgreSQL database, pressing the [...] browse button near the projection readout in the export dialog will show a list of supported SRIDs together with the selected SRID. For example, for Latitude / Longitude the SRID value is 4030.

In another example exporting drawings to SQL Server 2008 spatial uses EPSG codes to represent coordinate systems, so the value reported will be the EPSG code for the coordinate system in use.

Manifold will do its best to come up with a reasonabe name for a coordinate system, if possible. For example, loading a coordinate system from a PostgreSQL data source parses the name of the coordinate system from its definition within PostgreSQL. Unnamed coordinate systems used in databases will be assigned names synthesized from their IDs (SRIDs).

Notes

Users sometimes ask why Manifold supports so many different ways of doing spatial DBMS, at times even different ways using the very same DBMS product. The answer is that the Manifold user community includes a very large number of different organizations and individuals who have very diverse interests and the company likes to provide as many options as possible to suit different tastes.

Although it is true that providing "generic" spatial DBMS capabilities which will also work within a native spatial DBMS such as Oracle may seem redundant, the internal architecture of Manifold is so modular that there is not a great engineering cost for making all forms of spatial DBMS storage available regardless of what data store is employed. This provides flexibility and freedom of choice which helps users get the maximum possible value out of their technology investments.

For example, although using native Oracle Spatial capabilities is clearly the first choice for GIS professionals working with Oracle, it could be that to take advantage of some third party utility or application that does not understand native Oracle Spatial but does understand, say, OGC WKB we would like to make some data available in our Oracle data store in OGC WKB form but still have a generic spatial index in play for good performance. Manifold makes that possible and easy.

The discussion of storing geometry within non-native geometry types in databases applies to Manifold itself, since, after all, tables within Manifold are in fact a dedicated database system of sorts. That's why Manifold can store geometry in the form of GEOMETRY, a binary data type, within Manifold's own tables. It is also why Manifold has binary types for other commonly-encountered GIS binary blob storage types, including GemoWKB for OGC WKB, GeomSHP for ESRI shapefile geodatabases and GeomSDE for SDE binary data. The availability of all these types makes it easy to convert geometry data between binary type formats used by different systems.

GeomSHP and GeomSDE are somewhat similar approaches with differences in binary data organization. GeomSHP stores coordinates as double-precision floating point values. GeomSDE stores coordinates as integer values which are compressed using a simple run-length encoding scheme. The coordinate values must be scaled and shifted using the information stored in metadata tables.

Note that there are no vendor-specific, spatial DBMS geometry types like Oracle's SDO_GEOMETRY within Manifold tables because those types are constructs that occur within the spatial DBMS. Manifold GEOMETRY (Geom) data is automatically converted back and forth with the target spatial DBMS geometry data type on data exchange with the spatial DBMS. In contrast, the various Geom types supported within Manifold tables are not specific to any one DBMS but can exist wherever binary types are allowed.

Although all Manifold editions can work with attribute data and can store geometry using blobs, only Enterprise Edition and above can take advantage of spatial DBMS to store geometry using a spatial DBMS server's native geometry type.

When Manifold manages attribute data it can do by storing the data either in a file such as Access .mdb or it can store the data in a true DBMS server like SQL Server. File storage using formats such as .mdb, Excel .xls, .csv or other formats are often employed as a source for attribute data in GIS projects. At times users might choose to employ file-based storage for small data, because of the convenience and simplicity of not having to install a DBMS. That is especially tempting in the case of Access .mdb, because it gives the appearance of database-like functionality and can even be, well, accessed through Microsoft's Access personal DBMS.

For any serious work it is a much better idea to use a real DBMS, perhaps using a free DBMS such as Oracle Express or SQL Server Express if capacity less than 4GB is required. A real DBMS provides much greater capacity, performance and reliability than possible with exclusively file-based storage. A real DBMS can offer the benefits of spatial DBMS as well as many highly useful features such as security, triggers and views. The new generation of "Express" DBMS packages from the major vendors has not only eliminated the cost of DBMS in many applications, it has also made it very easy to install a DBMS package. Manifold's Database Console makes it easy to manage a DBMS as well, so there is little extra effort involved in using a DBMS instead of file-based storage.

The discussion in this overview topic necessarily simplifies the nature of a spatial DBMS, as spatial DBMS is more than just a geometry type coupled with some spatial operators. There are numerous details that vary from system to system that Manifold must manage, such as a wide variety of accessory metadata tables. Spatial DBMS packages can also vary widely in capabilities and may have different operational requirements, such as limitations on their ability to store projections. In general, Manifold manages all such differences for the user, but at times users may need to be aware of differences in DBMS capabilities. This is not usually a problem because most users choose a particular DBMS and then stick with it, so they naturally become familiar with that DBMS.

This topic has discussed two forms of data, geometry and attribute data, in connection with drawings. However, there is also a third class of data, raster data for images and surfaces, that is also often stored within a spatial DBMS. All spatial DBMS packages enable storage of geometry for drawings, but not all enable storage of raster data for images and surfaces.

Manifold can work with raster data in some spatial DBMS packages, for example, Oracle Spatial. However, even within a particular DBMS product line raster capability may be an option not found in all versions of that DBMS. For example, the free Oracle Express package has Locator capability in it to allow spatial DBMS work with drawings, but Oracle Express does not include Oracle's GeoRaster type to enable work with images. One must step up to the full Oracle Spatial package to get GeoRaster capability.

When using Manifold's generic spatial DBMS capabilities, nearly any DBMS can be used to store images and surfaces. Excellent!