This example uses Microsoft SQL Server Express 2005 as a default example of a non-spatial DBMS to which spatial DBMS capabilities can be added by using Manifold. Such usage is referred to as Manifold Spatial DBMS since Manifold is used to manage the storage, even though the actual geometry stored might be some non-Manifold type such as OGC WKB.
This example assumes the SQL Server Express 2005 DBMS has been installed using defaults as set forth in the SQL Server Express Edition topic and that a data source has been created in the Data Source dialog to connect to that SQL Server Express database as set forth in the Data Source dialog topic.
As advised in the Spatial DBMS topic, users are strongly encouraged to install the Manifold Spatial Extender for SQL Server when using SQL Server 2005 as a spatial DBMS. Procedures using with the spatial extender installed or not are identical, but use of the Manifold spatial extender will improve performance of spatial indices.
Manifold Enterprise Edition is required to export a drawing into a non-spatial DBMS in a way that grants spatial DBMS capabilities. Thereafter, any Manifold edition can utilize those spatial DBMS capabilities for drawings stored using Enterprise Edition. In addition, Manifold Database Administrator Edition provides very useful and convenient features that may be employed by administrators to configure DBMS storage to make life easier for ordinary users using other Manifold editions. Database Administrator Edition is required for the Administrator Console procedures shown later in this example.
Export a Drawing to SQL Server Express 2005
In this example we use Manifold Enterprise Edition to export a sample drawing of Mexico to SQL Server Express 2005:
In the project pane we right click on the drawing and choose Export.
In the Export Drawing dialog we choose Data Sources in the Save as type box.
In the Data Source dialog we click on the entry for SQL Server and then press OK.
[This screen shot assumes we have previously created an entry for our SQL Server connection as set forth in the Data Sources dialog topic and have named it Local SQL Server Express.
We can see that this particular Manifold installation also has a connection configured to an Oracle Express data source.]
In the Export Drawing dialog we accept defaults and press OK. All fields (columns) will be exported except intrinsic fields. The drawing will be uploaded into the DBMS using standard Manifold geometry types and a spatial index will be created.
The Export Drawing dialog gives us some options as to how we store the geometry. The Type box near the top of the dialog in this case will have only one choice, Manifold, unless the DBMS to which we are exporting has native spatial DBMS capabilities. If we are exporting to a non-spatial DBMS such as SQL Server 2005 (as opposed to SQL Server 2008 with the new spatial capability Microsoft has announced for SQL Server 2008) the only choice will be Manifold, meaning that Manifold will provide the spatial DBMS capabilities. If we were exporting to a DBMS with native spatial capabilities, such as Oracle, there would be a choice of the native spatial DBMS type as well, for example, Oracle, in addition to the Manifold choice.
Manifold can support non-Manifold geometry types for drawing storage and such are available within the Geometry type box even though it is a terrible idea for beginners not to use the default choice, Geometry, which chooses the Manifold Geometry type. Manifold geometry is by far the fastest, most robust and most modern of any of the various geometry types available.
In certain rare circumstances experts may prefer to choose Geometry(WKB) to use OGC WKB geometry or even Geometry (SHP) for an ESRI-style, "shapefile"-derived geometry type. If we pick something other than Geometry we are asking for trouble, as the other geometry types don't do as good a job of storing coordinate information as Manifold Geometry. There might be some justification for experts to use WKB if they like OGC ways of doing things and are willing to deal with the difficulties involved, but there is no good reason to use Geometry(SHP) outside of very unusual and exotic applications
Link a Drawing from SQL Server Express 2005
Now that we have a drawing in SQL Server we can import it or link it into Manifold using any Manifold edition. The following sequence, for example, can be used in Manifold Personal Edition or any other Manifold edition.
We open a project and launch Database Console. Within Database Console we choose the SQL Server Express installation from the Data Source dialog if it is not already loaded into the Data Source box. We press the Refresh button to see the contents of the DBMS server.
Database Console shows us the contents of the server. The drawing will appear using a Manifold drawing icon.
By default, Database Console will show us all of the contents of the server. If our budget allows, we can use the Administrator Console feature from Manifold Database Administrator Edition to configure the database to use friendly names so that the display is simplified for new users. See an example below.
To link the drawing into our project we click on it to highlight it and then we press the Link button in the Database Console toolbar.
The Import / Link Options dialog allows us to specify a name to use for the linked component and to choose between using all objects or only using those objects which fall within a desired area of interest. Note that area of interest specification, a serious spatial DBMS capability, is available to us even though the DBMS we are using is not a DBMS with built-in spatial features and even though we might not be using Manifold Enterprise Edition. Press OK to continue.
The result is that a new, linked drawing appears in the project.
If we click this drawing open we see it is indeed the sample Mexico drawing exported in the previous part of this example. It has been linked complete with all projection information exactly as it was when exported.
Configure a Drawing using Administrator Console
Being able to export drawings into almost any DBMS with true spatial DBMS capabilities available upon import is extremely useful. However, as seen above the example is limited for two main reasons: First, it exposes complicated DBMS objects and names which might potentially confuse beginning users and second, it does not allow formatting for the linked drawing to be stored on the DBMS server.
We can add both capabilities by using Administrator Console, a feature provided by Manifold Database Administrator Edition and by Ultimate Edition. Once drawings are so configured using Database Administrator edition, thereafter any Manifold edition can connect to the database and use friendly names and formatting stored on the database. For that reason, organizations that have many users using drawings stored within databases will usually have a few Manifold Database Administrator or Ultimate licenses for use by managers to configure drawings within the database to use friendly names and to allow formatting, while the majority of their licenses are Enterprise or Universal licenses.
We will first turn on storage of formatting within the DBMS and then provide a friendly name (component name) to use.
Launch Manifold Database Administrator Edition and connect to the SQL Server Express database with Administrator Console. Press Refresh to see the contents of the database.
We note that the column showing format status is not turned on in the display. To turn it on, we press the Columns button.
In the Columns dialog we check the Format box to display that column.
We double-click into the Format cell for the entry for the Mexico drawing and set the Format value to Yes.
Note that Administrator Console shows all database objects using their database names. It's easy to tell which are drawings because they will have a projection and a spatial index value.
Next we will provide a component name, that is, a friendly name.
To do so, double-click into the Component cell for the Mexico drawing and provide a desired name. For lack of anything better coming to mind, we will use the name Mexico. This name will be displayed when Database Console shows the contents of the database.
Linking a Drawing Configured with Administrator Console
Now that the drawing in the database has been configured with Administrator Console we can use any Manifold edition to import it or link it, with the benefits of friendly names and formatting on the database.
Launch any Manifold System edition, open a project and launch Database Console. Connect to the SQL Server Express database used and press the Refresh button.
Database Console will see that component names are available in the database and will automatically engage the Component View button to display database items in the console view using friendly names. Instead of a list of confusing database objects we see Manifold components using friendly names as configured by Administrator Console in the previous parts of this example. Excellent!
To link a drawing, click on it to highlight it and then press the Link button.
When we open the drawing we can apply formatting, and the formatting will be stored on the database so that any other user linking the drawing will see that formatting.
Notes
The procedures used herein will work with almost any DBMS that can store binary data, such as MySQL, Informix or others. SQL Server Express 2005 is used as a convenient example since a SQL Server Express installation is on the Manifold DVD. Oracle Express or DB2 could have been used just as conveniently. However, since Oracle Express includes built-in Locator capability (Oracle's spatial DBMS technology for drawings as in Oracle Spatial) and DB2 Express-C can be equipped with the free DB2 Spatial Extender, we have used SQL Server 2005 to provide an example of a modern DBMS that does not include built-in spatial DBMS.
When discussing spatial storage in SQL Server one should keep in mind there are several possibilities depending upon which version of SQL Server is being used and which spatial technology is employed. See the discussion in the Spatial DBMS topic.
See Also
Manifold Spatial DBMS Facilities
Example: Storing an Image in Manifold Spatial DBMS
Example: Storing a Surface in Manifold Spatial DBMS
Database Administrator Edition
Manifold Spatial Extender for SQL Server