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. Before continuing with this topic, please see the Spatial DBMS topic for a general introduction to spatial DBMS and Manifold.

 

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 and PostgreSQL / PostGIS.

 

Storing Drawings within Spatial DBMS

 

Manifold features integrated 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.

 

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 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 - Manifold can confer spatial DBMS capability to almost any DBMS. When using Manifold-managed spatial storage, we have the choice of what geometry type we would like to use. 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, often abbreviated GEOM). Normally the choice will be made between Manifold GEOM type or OGC WKB 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. A special case of Manifold-managed storage is the use of the Manifold Spatial Extender for SQL Server 2005 to provide especially fast spatial DBMS capability within SQL Server 2005. See the Example: Storing a Drawing in Manifold Spatial DBMS topic.

 

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, which 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.

 

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. See the Example: Storing an Image in Oracle topic.

·      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. See the Example: Storing an Image in Manifold Spatial DBMS and Example: Storing a Surface in Manifold Spatial DBMS topics.

 

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.

 

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. Note that we must use the native spatial DBMS connection (see below) in the Data Source dialog to enable use of native spatial DBMS capabilities.

·      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).

 

Data Source Connections for Spatial DBMS

 

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.

 

images\sc_spatial_dbms_connections.gif

 

DB2 Data Sources

Connect to IBM DB2 equipped with the DB2 Spatial Extender using IBM's native spatial connection technology.

Oracle Data Sources

Connect to Oracle using OCI, the Oracle Call Interface to utilize Locator or Oracle Spatial facilities.

PostgreSQL Data Sources

Connect to PostgreSQL equipped with the PostGIS spatial extender using native PostgreSQL connection technology. Connections made using this data source will have any password required masked so that later usage will not inadvertently expose the password.

SQL Server Data Sources

Connect to Microsoft SQL Server 2008 spatial DBMS facilities using native SQL Server 2008 spatial connection technology.

 

Users sometimes ask if there is a special connection when using SQL Server 2005 as a spatial DBMS with the Manifold Spatial Extender for SQL Server installed. There is no need for a special connection because Manifold when connecting to SQL Server using any OLE DB connection will recognize and automatically use spatial indices managed by the spatial extender when the Manifold spatial extender has been installed.

 

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 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 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

 

Current spatial DBMS products with spatial types understood 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. See the SQL Server Spatial DBMS Facilities topic.

·      Microsoft SQL Server 2008 - Microsoft has engineered spatial DBMS functionality into SQL Server 2008. Manifold supports SQL Server 2008 spatial extensions today and has since the very first SQL Server 2008 spatial pre-release in 2007. See the SQL Server Spatial DBMS Facilities topic.

·      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. See the Oracle Spatial Facilities topic for additional information.

·      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 can interact with them as well. See the ESRI Geodatabases topic.

 

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.

 

For specific discussion of different ways to use SQL Server in spatial DBMS, see the SQL Server Spatial DBMS Facilities topic.

 

Notes on Specific Spatial DBMS Products.

 

Oracle and SQL Server have dedicated topics discussing operations with those DBMS products. See the Oracle Spatial Facilities and the SQL Server Spatial DBMS Facilities topics.

 

IBM DB2

 

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.

 

PostgreSQL / PostGIS

 

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 a 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 a 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 Geodatabases

 

Not really a spatial DBMS in the sense of the above DBMS products, ESRI SDE and Personal geodatabases are discussed in the ESRI Geodatabases topic.

 

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.

 

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. See the ESRI Geodatabases topic for information on using such geodatabases with Manifold.

·      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.

 

For projections within SQL Server 2008 native GEOMETRY type, see the comments on SQL Server 2008 projections in the SQL Server Spatial DBMS Facilities topic.

 

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 reasonable 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).

 

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.

 

Notes

 

The idea 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.

 

Manifold allows simultaneous usage of multiple geometry types within the same DBMS. 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.

 

Technical Support

 

DBMS packages used for spatial DBMS work are highly complex software systems with many installation, configuration, operation and administration options. The tips above presume reasonably default configurations set up by administrators who are reasonably worldly about configuring DBMS installations in a portable and open way. Successful operation with the various spatial DBMS packages Manifold understands is not normally difficult, but can require significant DBMS and IT technical expertise from database administrators when DBMS installations become more elaborate. For example, permissions can be set in DBMS installations that may conflict with use of clients like Manifold. This will come as no surprise to the DBA with client integration experience

 

Manifold does not support third party DBMS products, with the exception that current Express editions of SQL Server (2008 and 2005), Oracle and DB2 will be supported for Enterprise Edition use with those DBMS products in default configurations. See comments in the Database Installations topic. Although the manifold.net team is enthusiastic about working with other DBMS products, such as PostgreSQL, the great variability encountered in open source installations does not make it economically feasible to provide technical support services beyond the three major commercial DBMS products.

 

Developer level support incidents may be used for questions about Enterprise Edition features used with the three supported database products, but only for the actual Enterprise dialogs. For example a question about connecting with Enterprise Edition via Database Console to an Oracle data source will result in an answer limited to the use of a specific Manifold dialog, such as explaining the purpose of the Server, User Name and Password boxes.

 

Support does not extend to explaining how you can configure users in the DBMS, how to determine what server name is being used or should be used or how to determine whether a given user has connectivity to a given data source. Use a database consultant to assist with DBMS-centric issues. There are many ways a clever (or overly clever or not so clever) database administrator can configure such complex DBMS products to make it impossible for clients like Manifold to work with them with the full feature set of either client or server - that's what keeps database integration consultants in business!

 

See Also

 

Spatial DBMS - The key introductory topic to spatial DBMS.

 

Data Storage Strategies

Database Installations

Database Administrator Edition

ESRI Geodatabases

Example: Storing a Drawing in Manifold Spatial DBMS

Example: Storing an Image in Manifold Spatial DBMS

Example: Storing a Surface in Manifold Spatial DBMS

Geometry in Tables

Linked Drawings

Manifold Spatial DBMS Facilities

Manifold Spatial Extender for SQL Server

Oracle Spatial Facilities

Project Pane - Open Data Source

Queries and Geoms

Spatial Extensions

SQL Server Spatial DBMS Facilities

Tools - Administrator Console

Tools - Database Console