Manifold supports several ways of performing spatial DBMS work with Microsoft SQL Server:
· Microsoft SQL Server 2008 - This new Microsoft product provides spatial support using native spatial DBMS capability such as Oracle Spatial or IBM DB2 with Spatial Extender. Manifold Enterprise Edition and higher editions have built-in capability to work with native SQL Server 2008 spatial capabilities, including both GEOMETRY and GEOGRAPHY data types. Manifold has supported SQL Server 2008 spatial capabilities since the very first SQL Server 2008 spatial pre-release in 2007. See the Example: Configuring SQL Server 2008 topic before working with SQL Server 2008.
· 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 with Manifold Generic Spatial Index - This applies to SQL Server 2000 and more recent SQL Server editions such as SQL Server 2005 and SQL Server 2008. Because SQL Server, like almost every other big-name DBMS, provides binary storage we can use Manifold's generic, non-native spatial DBMS capability within SQL Server 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 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, with SQL Server 2005 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.
Manifold can store images and surfaces in SQL Server 2008 and SQL Server 2005 using Manifold's generic spatial DBMS capabilities.
Very Important: Due to changes in SQL Server 2008 between pre-releases and final production form, it is very important that all work with SQL Server 2008 be done using a production version of SQL Server 2008 and the current version of Manifold. Do not use pre-release versions of SQL Server 2008 (so-called "CTP" versions) and do not use old versions of Manifold.
SQL Server 2008 Geometry Types
SQL Server 2008 spatial capabilities include support for two native geometry types: a planar GEOMETRY type similar to the geometry types used by other spatial DBMS products and a geodetic GEOGRAPHY type. The internal workings of either type have not been documented by Microsoft as of this writing, so Manifold uses these types the same way as it does SDO_GEOMETRY from Oracle Spatial.
That is, when data is brought in from SQL Server native GEOMETRY or GEOGRAPHY storage by importing or linking a drawing it is dynamically cast into Manifold Geometry type. When a Manifold drawing is exported into SQL Server, Manifold Geometry is automatically cast into SQL Server native GEOMETRY.
Any edits or other operations performed on SQL Server native GEOMETRY or GEOGRAPHY drawings that are linked into Manifold are dynamically manipulated so that whatever is done in the Manifold session is correctly updated within SQL Server native storage. Any re-projection required to show a linked SQL Server native GEOMETRY or GEOGRAPHY drawing within, say, a Manifold map in a different projection will automatically be done on the fly.
Note that Manifold can import or link a drawing from either SQL Server native GEOMETRY or GEOGRAPHY but that exports from Manifold will always be exported into SQL Server native GEOMETRY. Exports are not available into SQL Server GEOGRAPHY.
If you have a drawing that uses SQL Server GEOGRAPHY Manifold will be happy to use it. You can import it or link it into Manifold. If you link it into your Manifold project, Manifold will happily allow you to edit objects in it, to delete them, to create new objects and so forth. But Manifold will not create a new drawing for you within SQL Server that uses GEOGRAPHY. This limitation arises from the unique nature of the GEOGRAPHY type and may be relaxed in future versions. For the time being, it is not in any way a limitation for GIS operations since GIS data at the present time is universally planar in form, such as the SQL Server GEOMETRY type.
It is also possible that should Microsoft document either the GEOMETRY or GEOGRAPHY type Manifold will add these as intrinsic types that can be held in storage outside of SQL Server, much as can OGC WKB be used in the form of the Manifold Geometry(WKB) type.
SQL Server 2008 Projections
The GEOGRAPHY type always uses Latitude / Longitude. Importing or linking a drawing from SQL Server 2008 that uses GEOGRAPHY type will import or link the drawing into Manifold using Latitude / Longitude projection. Manifold recommends that all storage into GEOGRAPHY type adopt the convention of utilizing coordinates developed using the WGS84 datum.
When reading and writing spatial data stored on SQL Server 2008 spatial using Microsoft's GEOMETRY type, 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 with SQL Server 2008 spatial GEOMETRY is a great way of eliminating the confusion one might encounter with proprietary methods of specifying coordinate systems.
Although EPSG codes cover very many coordinate systems, because Manifold can handle a much 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. For example, EPSG does not have a code for Orthographic projection.
If a Manifold drawing uses a coordinate system not supported by EPSG, when the drawing is exported into SQL Server 2008 GEOMETRY, Manifold will re-project it on the fly into Latitude / Longitude form (using EPSG code 4326). Built-in Manifold coordinate systems include all EPSG coordinate systems so that drawings imported or linked from SQL Server 2008 GEOMETRY 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.
Re-projection in SQL Server 2008
Note that although Manifold will happily re-project data as necessary and can even re-project on the fly so that drawings stored within SQL Server 2008 using different projections can be used together for spatial analytics such as topology overlays, as of the present writing SQL Server 2008 does not have server-side re-projection capability. This means that the usual Manifold facilities for changing projections of drawings stored in spatial DBMS won't work if native SQL Server 2008 spatial enhancements are used. We can, however, still use Administrator Console (available in Database Administrator edition or Ultimate edition) to examine the projections in use.
Launching the administrator console we can connect to SQL Server 2008 using the data source that is configured in the example topics below.
We press the Columns button in the console's toolbar to turn on the Projection column if it is not already visible. This reports the projection in use by drawings. We choose a drawing, a version of the standard Mexico example, that has been uploaded to SQL Server 2008 spatial storage using an unusual projection, MTM Zone 3. We double-click into the projection cell for that drawing.
The Projection dialog used with EPSG codes reports the details of the projection in use. We can scroll down to EPSG 4326 and click on it to highlight it. We can try to re-project by pressing the OK button.
However, we cannot modify the projection (coordinate system) in place.
If we want to re-project the drawing this is easy to do in Manifold: import the drawing into Manifold, re-project it within Manifold, and then export it back into SQL Server 2008.
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.
Although the Manifold spatial extender works fine with SQL Server 2008 as well, normally we would use native SQL Server 2008 spatial capabilities instead of installing the Manifold spatial extender and using generic Manifold spatial capabilities.
See the Manifold Spatial Extender for SQL Server topic for details.
Technical Note on Pre-Release GEOGRAPHY Type Coordinate Ordering
This note applies only to users who experimented with pre-release versions of SQL Server 2008 or who are still using an older Manifold release with SQL Server 2008. If you are using a production version of SQL Server 2008 with a current Manifold release this note does not apply to you.
Experimenting with pre-release software always involves the risk that something important will change in the production version. Users who experimented with pre-release versions of SQL Server 2008 should take note of a very important change made by Microsoft in the final production version of SQL Server 2008.
During Microsoft's development of SQL Server 2008 several pre-releases, called CTP (Community Technology Preview) releases were issued during 2007 and 2008. Up until the final CTP and final release to manufacturing in 2008, SQL Server 2008 GEOGRAPHY type was unique in that it used (latitude, longitude) coordinate ordering, that is (Y, X), ordering instead of the (X, Y), (longitude, latitude) ordering that is universally used by all other spatial DBMS and GIS products and essentially all other vector and raster GIS formats, including SQL Server's own GEOMETRY type when storing Latitude / Longitude data.
Based upon feedback from beta testers and the development community, the final CTP and final production release of SQL Server 2008 changed the ordering of coordinates in GEOGRAPHY type to the industry standard, that is (X, Y), (longitude, latitude) ordering. Every application designed around pre-release versions of SQL Server 2008, including Manifold, therefore had to change accordingly.
Because Manifold supported GEOGRAPHY type since the very first spatially-capable CTP in 2007, all Manifold Release 8 builds published before the final release of SQL Server 2008 took into account the backwards ordering of coordinates in SQL Server 2008 pre-releases. When importing or linking GEOGRAPHY, all Manifold builds published up through and including July, 2008, would automatically swap coordinate ordering into the (X, Y) order expected by all other systems, including Manifold. The last Manifold build that automatically swapped coordinate ordering was 8.0.8.
All Manifold releases from August, 2008 onward were changed to follow SQL Server's adoption of industry standard ordering so that they do not swap coordinate order for GEOGRAPHY type. If you are reading this Help topic from within your Manifold installation you are operating a newer Manifold release which does not swap coordinate order in SQL Server 2008 GEOMETRY type.
Developers who were working with pre-release SQL Server 2008 versions should be aware that SQL Server 2008 GEOGRAPHY coordinate order has changed from backwards ordering in the earlier CTPs to industry standard ordering in production SQL Server 2008 and that Manifold has also changed accordingly. Developers should be aware that using a newer Manifold release with older data stored in a pre-release SQL Server 2008 database will result in backwards coordinate ordering, since the old SQL Server system was backwards but current Manifold releases no longer automatically flip the backwards ordering. Likewise, using a Manifold release issued on or before July, 2008, with the production version of SQL Server 2008 will also result in backwards coordinate interpretation, because the older Manifold release will think that the SQL Server coordinates are backwards and will swap them automatically.
The solution to avoiding all problems is very simple: use the latest Manifold release with production SQL Server 2008. Do not use pre-release SQL Server 2008 versions and do not use older Manifold releases with production SQL Server 2008.
SQL Server 2008 Examples
SQL Server 2008 has been used in a series of example topics illustrating work with this fine new Microsoft spatial DBMS product.
Example: Configuring SQL Server 2008
Example: Linking a Drawing from SQL Server 2008
Example: Storing a Drawing in Manifold Spatial DBMS
Example: Storing a Drawing in SQL Server 2008
Example: Tracing Virtual Earth into SQL Server 2008
Example: Storing an Image in SQL Server 2008
SQL Server 2005 Examples
SQL Server 2005 Express has been used in a series of example topics illustrating use of generic Manifold spatial DBMS capabilities. Many of the techniques illustrated (such as use of the Administrator Console) also apply to SQL Server 2008.
Example: Storing a Drawing in Manifold Spatial DBMS
Example: Storing an Image in Manifold Spatial DBMS
Example: Storing a Surface in Manifold Spatial DBMS
See Also
Database Administrator Edition
Manifold Spatial DBMS Facilities
Manifold Spatial Extender for SQL Server
Project Pane - Open Data Source