Spatial DBMS Facilities

Manifold includes extensive features to take advantage of spatial DBMS capabilities, even with those DBMS products that do not provide native spatial DBMS capability. See the Spatial DBMS topic for a general introduction to spatial DBMS and Manifold.

Manifold can work with major spatial DBMS servers to read, write and edit drawings stored within a native spatial DBMS, and in addition can use Manifold facilities to store images and surfaces in almost any DBMS.

In addition to working with DBMS products sold by their vendors as spatial DBMS products, Manifold can confer upon most "ordinary" DBMS products many of the benefits of a spatial DBMS through the use of Manifold-supported geometry types and a Manifold-created spatial index within the DBMS to create generic spatial DBMS capability, also referred to as Manifold spatial DBMS capability.

This topic surveys the various spatial DBMS servers with which Manifold can work and provides links to more detailed information.

Spatial DBMS Storage

Manifold provides a choice of storage methods and even allows multiple different storage methods within the same DBMS. In addition, spatial DBMS products such as Oracle Spatial also allow data storage using the full roster of "ordinary" types supported in regular Oracle DBMS usage. Manifold can work either with native spatial DBMS facilities or use Manifold-managed spatial facilities.

Each of the spatial DBMS products supported by Manifold (Oracle, DB2 with Spatial Extender, SQL Server 2008 and PostgreSQL) has a native connection technology, for example, OCI (Oracle Call Interface) for Oracle, and a native geometry type and supporting facilities such as the vendor's own spatial indices. Manifold dialogs for exporting drawings, images or surfaces to spatial DBMS allow choice of either the vendor's native technology or generic Manifold technology.

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, an Oracle database could contain drawings stored using Oracle's own SDO_GEOMETRY type using Oracle's own spatial indices, and that same database could also contain drawings stored using Manifold's Geometry type using generic spatial indices created by Manifold. In theory, it could also store an SDE geodatabase using ESRI-style technology. When Manifold connects to that Oracle database it will correctly identify all technologies in use and utilize appropriate methods when importing the Oracle, Manifold or ESRI types from that database.

It may seem odd to want to connect to a spatial DBMS and store spatial data within that DBMS while not using the native geometry type provided by that DBMS. There are cases where that nonetheless makes sense. For example, we may have the budget for only one enterprise-class DBMS server installation within our organization, and we may have chosen Oracle for that DBMS with the idea of standardizing upon Locator with SDO_GEOMETRY (Oracle's native geometry type) for our spatial geometry needs. But we might have occasion to want to provide data for interoperability with some client software that does not understand SDO_GEOMETRY but which understands OGC WKB. In that case, we could connect to our Oracle server and choose the Manifold type of spatial storage using Geometry (WKB) to enable export to the DBMS of drawings that are stored using OGC WKB.

In fact, quite remarkably we can link drawings to Oracle using different connection technologies for different drawings. One drawing could be stored using Oracle's native geometry. Another drawing could be stored using Geometry (WKB) managed by Manifold. We can move objects into the OGC form by simply copying and pasting from one drawing to the other.

Supported Spatial DBMS Products

In addition to conferring generic spatial DBMS capability on almost any DBMS that can store binary data, Manifold can work with native spatial geometry types and native spatial logic in the following spatial DBMS products:

IBM DB2

In addition to usage of DB2 to store object geometry in a generic way, when IBM's DB2 Spatial Extender has been install we can use DB2 as a spatial DBMS, taking advantage of native DB2 geometry. The IBM Spatial Extender for DB2 is a free download for DB2 Express-C and other DB2 editions.

Manifold usage of DB2 spatial DBMS data sources uses Database Console as the primary interface and includes:

· Connecting to an DB2 data source via the native DB2 interface. The native DB2 interface is faster and more robust than accessing DB2 through intermediate database layers such as ODBC or OLE DB. Manifold will remember the last DB2 data source to which a successful connection has been made. Omitting a login name when connecting to a native DB2 data source uses integrated security.

· Importing, linking and exporting tables.

· Listing the drawings in an DB2 data source in Database Console.

· Importing drawings.

· Linking drawings in read-write mode.

· Exporting drawings.

When importing, linking or exporting drawings, reading data will automatically and transparently translate DB2 geometry into Manifold Geom values. Writing data will automatically and transparently translate Manifold Geom values into DB2 Geometry.

When linking a drawing or table from a native DB2 data source Manifold will examine the DB2 table privileges. If the detected table privileges do not allow writing the resulting component within Manifold will be read-only.

To work with DB2 data sources we must have all DB2 client DLLs (for example, DB2CLI.DLL plus any DLLs it depends on) to be installed in a folder referenced in the user's or system's PATH variable. The easiest way to be sure this is done is include the folder containing all DB2 client DLLs in the system PATH variable.

Microsoft SQL Server

Manifold supports SQL Server 2008 spatial as well as spatial DBMS work with SQL Server 2005 using the Manifold Spatial Extender for SQL Server. See the discussion in the Spatial DBMS topic. Until public availability of Microsoft's announced SQL Server 2008 spatial product, SQL Server does not provide a native spatial type. It nonetheless can be used as a spatial DBMS by using Manifold geometry types together with a Manifold-created spatial index. Creating a spatial index requires Manifold Enterprise Edition or higher edition.

The Manifold Spatial Extender for SQL Server is a free download that should always be installed on SQL Server 2005 installations for faster spatial index performance.

How to Export a Drawing into SQL Server Express

1. In the project pane right click on the drawing and choose Export.

2. In the Export Drawing dialog choose Data Sources () in the Save as type box.

3. In the Data Source dialog click the data source for the SQL Server Express database you wish to use to highlight it. If a data source for the desired SQL Server Express database has not yet been added to the list of data sources provided in the Data Source dialog, please see the Data Source Dialog topic for instructions on adding a new data source to that dialog. Press OK.

4. In the Export Drawing dialog that pops up choose the fields to be exported with the drawing. By default, all fields except intrinsic fields are checked. Accept the other settings. (Note that the Create spatial index box is checked by default, which will create a spatial index for this drawing and thus confer upon SQL Server Express essential functionality as a spatial DBMS.) Press OK.

The drawing will be exported to SQL Server with a spatial index created.

We can now use Database Console to import or link the drawing into any Manifold project:

1. Launch Database Console and connect to the SQL Server data source.

2. We will see the drawing appear with a Manifold drawing icon next to it. If we have used Administrator Console (available within Database Administrator Edition) to give it a friendly name it will appear using whatever name was given to it in Administrator Console using the Properties dialog.

3. Import or link the drawing by clicking on it to highlight it and then pressing the Import or Link button in the toolbar. Because we declared a spatial index for the drawing we can choose to import or link objects only from a desired area of interest.

Manifold dialogs for export to SQL Server 2005 by default use the native Manifold Geometry type. Manifold's Geometry type is actually just as efficient or more so than the native geometry types employed by various vendors, but if desired some other geometry type such as Geometry(WKB) may be used and supported with a spatial index.

A spatial index declared by Manifold is processed mostly by the DBMS server and not by the client, so the performance attained will in general be set by the DBMS server, just as with a dedicated spatial DBMS. Editing a linked drawing will automatically update the spatial index.

The main usage of spatial DBMS storage when a GIS package front ends the DBMS is to extract an area of interest so that drawings of manageable size can be viewed and edited in the GIS client even though the DBMS contains a huge, seamless data set that otherwise would be too big to edit in its entirety. Filtering using the spatial index to achieve extraction of an area of interest subset is likewise done mostly on the server so in this key task using a generic spatial index with Geometry stored in SQL Server works quite well.

The comments for use of SQL Server as a spatial DBMS using Manifold geometry and spatial indices apply to almost any big-name DBMS. For example, we can use MySQL in the same way.

Oracle

Manifold Enterprise Edition or above can connect to Oracle Spatial or regular Oracle databases via Locator to provide spatial DBMS capability. See the Oracle Spatial Facilities topic for details.

PostgreSQL

PostgreSQL (pronounced "post-gres-que-ell" in English) is an open source DBMS that has been extended into spatial capabilities via the PostGIS set of open source extensions.

Manifold usage of PostgreSQL data sources uses Database Console as the primary interface and includes:

· Connecting to an PostgreSQL data source via the native PostgreSQL interface. Manifold will remember the last PostgreSQL data source to which a successful connection has been made.

· Importing, linking and exporting tables.

· Listing the drawings in an PostgreSQL data source in Database Console.

· Importing drawings.

· Linking drawings in read-write mode.

· Exporting drawings. When exporting a drawing to a PostgreSQL data source there is an option to create a spatial index (on by default), and an option to create a sequence and an update trigger (on by default), similar to the options provided for export to Oracle. See the Example: Storing a Drawing in Oracle and Export Drawing - Oracle topics.

To work with PostgreSQL data sources we must have all PostgreSQL client DLLs (for example, LIBPQ.DLL plus any DLLs it depends on) to be installed in a folder referenced in the user's or system's PATH variable. The easiest way to be sure this is done is include the folder containing all PostgreSQL client DLLs in the system PATH variable.

When importing, linking or exporting drawings, reading data will automatically and transparently translate PostgreSQL geometry into Manifold Geom values. Writing data will automatically and transparently translate Manifold Geom values into PostgreSQL Geometry.

Exchanging geometry data with PostgreSQL tolerates data with Z and M values, although Z and M values are not used by Manifold. Reading geometry data with Z and M values throws these values away. Writing geometry data with Z and M values inserts zeros.

ESRI ArcSDE / ArcGIS / Personal Geodatabases

ESRI's ArcSDE product stores drawing geometry and other GIS data within ordinary, non-spatial DBMS servers. ESRI products can then work with this data as if they were working with a true spatial DBMS, albeit in a proprietary way. ESRI refers to such stored data as geodatabases. A simpler ESRI technology that appears mostly in the form of Access .mdb files is called Personal geodatabases. See the discussion in the Spatial DBMS topic.

This same ESRI SDE technology is known by various names, including ESRI geodatabases, ArcGIS geodatabases (often abbreviated to GDB in casual writing) , ArcSDE or simply SDE databases. Although ESRI is moving away from ArcSDE as a product name, the SDE nomenclature has been so widely used to describe this technology that it appears to be persisting over the newer names for the same thing. Manifold documentation tends to follow the most common nomenclature and refer to such databases as SDE or, more technically, SDE data sources. All such geodatabases use essentially the same SDE technology.

Technically, one can organize an SDE data source on almost any database, but as a practical matter they are usually only organized on big-name databases explicitly supported by ESRI, such as IBM DB2, IBM Informix, Microsoft SQL Server 2000 or 2005 and Oracle. SDE data sources using Access .mdb appear to have been replaced with SQL Server Express 2005. Personal geodatabases seem to be found almost exclusively within .mdb files.

Manifold knows to look for SDE or Personal geodatabase data if we use Database Console to connect to a database. Manifold usage of SDE or Personal geodatabase data sources uses Database Console as the primary interface and includes:

· Connecting to an SDE data source using ODBC or OLE DB.

· Listing the drawings in an SDE data source in Database Console.

· Importing drawings.

· Linking drawings in read-write mode.

When importing or linking drawings from SDE or Personal geodatabase data sources Manifold will fetch the coordinate systems (projections) in use from ESRI metadata. Importing and linking a drawing assigns it the coordinate system stored on the data source.

Manifold will convert ESRI style objects within the SDE database into Manifold equivalents. For example, reading data from an SDE geodatabase reads parametric curves, flattening them into lines with straight line segments. As of the current writing Manifold does not accept "multipoint" values, although this capability is expected to be added in future editions.

Although Manifold can connect to an existing SDE database, read (import) drawings, write drawings, link drawings and edit drawings, Manifold will not export new drawings to an SDE database nor will Manifold create a new SDE database. It does not make sense to create new SDE databases since these are significantly inferior to using native standards within a true spatial DBMS like Oracle or SQL Server. The only reason to work with SDE is to support legacy SDE data sources that have already been created within ESRI installations. This should not be an obstacle for ESRI users who are operating legacy SDE databases, since such users normally have ESRI software in use for organizing new SDE databases.

The purpose of Manifold's ability to work with SDE is therefore to provide the capability of dynamically working with existing SDE databases in legacy installations, and not to encourage the creation of new SDE databases. For example, an organization that has a large amount of data in legacy SDE form can use Manifold licenses, which have far better price/performance than ESRI licenses, to interact with that data, while maintaining a few ESRI licenses should they need to ever extend their usage of SDE into new SDE databases.

In contrast, new GIS users will never choose SDE over use of a real spatial. In modern times it does not make sense to create a new SDE database when true spatial DBMS servers, like Oracle Spatial, are now available. Using a true spatial DBMS provides far greater reliability, performance, flexibility and interoperability with other applications and usually dramatically lower costs as well. It therefore makes no sense to use SDE if we have a real spatial DBMS available.

For that matter, SDE is so bizarre, fragile and undocumented that even if a spatial DBMS is not available it does not make sense to use SDE in preference to the use of generic geometry types in a more open way within a non-spatial DBMS, supported with a spatial index.

See the Spatial DBMS topic for more information on the advantages and disadvantages of spatial storage within a DBMS.

Deleting Drawings stored within Spatial DBMS

Deleting a drawing stored within a spatial DBMS is usually simple, but requires a few nuances due to the multi-user / multi-process nature of most DBMS servers. See the discussion in the Tech Tip in the Database Console topic.

Storing Images and Surfaces within Spatial DBMS

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

· Oracle GeoRasters - 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 and surfaces 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, with the surfaces being stored as images. 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 uncompressed BGRA format. Importing or linking images from such a data source automatically reassembles tiles. Surfaces may be imported by importing the Height channel of the image used to store that surface.

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 (sufarces may be imported, but not linked). We can also import individual channels of such images as surfaces.