Example: Storing an Image in Manifold Spatial DBMS

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.

 

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 an image into a non-spatial DBMS in a way that grants spatial DBMS capabilities. Thereafter, any Manifold edition can utilize those spatial DBMS capabilities for images 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 an Image to SQL Server Express 2005

 

In this example we use Manifold Enterprise Edition to export a sample image to SQL Server Express 2005:

 

images\eg_export_dbms_img_01.gif

 

We open a project with the image.

 

images\eg_export_dbms_img_02.gif

 

If we open the image we see it shows portion of the Microsoft campus in Redmond, Washington.

 

images\eg_export_dbms_img_02a.gif

 

This image is reasonably well detailed, at about 0.3 meter resolution, as we can see by zooming in.

 

images\eg_export_dbms_img_02b.gif

 

To export the image to a database we right click on the image in the project pane and choose Export.

 

images\eg_export_dbms_img_03.gif

 

In the Export Image dialog we choose Data Sources in the Save as type box.

 

images\eg_export_dbms_img_04.gif

 

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

 

images\eg_export_dbms_img_05.gif

 

In the Export Image dialog we accept defaults and press OK. The image will be uploaded into the DBMS using standard Manifold spatial DBMS surface storage technology for raster data sets (images and surfaces). Manifold stores images as tiles so that subsections of images can be retrieved very rapidly. The default Tile size recommended by Manifold may be adjusted up or down, but usually is the best choice.

 

The option to Create Pyramids creates pre-computed, intermediate views, called pyramids, which enable faster display of very large images when zoomed out. Compression using the default Deflate option is a good idea because the computational cost of decompression is negligible and there is no image quality lost, yet having a smaller amount of data in the database not only saves disk space but it helps the DBMS perform faster.

 

Note: Export of images to databases is robust. For example, exporting an image to a database as a tiled table (the actual storage mechanism used within the database) correctly rolls back all changes upon a failure.

 

Link an image from SQL Server Express 2005

 

Now that we have an image 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.

 

images\eg_export_dbms_img_06.gif

 

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.

 

images\eg_export_dbms_img_07.gif

 

Database Console shows us the contents of the server.

 

Important: In this example, we have continued using the same SQL Server Express installation employed in the Example: Storing a Drawing in Manifold Spatial DBMS topic. At the end of that example we used Administrator Console to configure a friendly name for the drawing that was stored. When Database Console connects to a DBMS that contains friendly names it automatically turns on with the Component View button enabled, so that only friendly names are shown and database object names that might be confusing to inexpert users are hidden.

 

Uploading an image into a DBMS uploads it without a friendly name until we configure a friendly name using Administrator Console. To see the image that was just uploaded we will have to turn Component View off to see everything in the database.

 

images\eg_export_dbms_img_08.gif

 

The image will appear using a Manifold image icon. To link the image into our project we click on it to highlight it and then we press the Link button in the Database Console toolbar.

 

images\eg_export_dbms_img_09.gif

 

The result is that a new, linked image appears in the project.

 

Note that unlike the case of importing or linking a drawing as seen in the previous Example, no Import / Link Options dialog appears when importing or linking an image to allow specifying a subset of the image for the area of interest. The reason is that the nature of image storage technology using tiles is such that at any moment there is, in effect, a virtual area of interest subset acquisition from the database, with tiles being fetched as needed for whatever view is commanded through panning and zooming a display window.

 

images\eg_export_dbms_img_10.gif

 

If we click this image open we see it is indeed the image exported in the previous part of this example. It has been linked complete with all projection information exactly as it was when exported.

 

Configure an image using Administrator Console

 

Being able to export images into almost any DBMS with true spatial DBMS tiled storage is extremely useful. However, as seen above the example is limited because it exposes complicated DBMS objects and names which might potentially confuse beginning users.

 

We can avoid complicated database names by using friendly names (component names) as provided through Administrator Console, a feature that is part of Manifold Database Administrator Edition and by Ultimate Edition. Once images are so configured using Database Administrator edition, thereafter any Manifold edition can connect to the database and use friendly names. For that reason, organizations that have many users using images stored within databases will usually have a few Manifold Database Administrator or Ultimate licenses for use by managers to configure images within the database to use friendly names, while the majority of their licenses are Enterprise or Universal licenses.

 

Using Database Administrator Edition we will specify 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.

 

images\eg_export_dbms_img_11.gif

 

Administrator Console shows all database objects using their database names. It's easy to tell which are images because they will have a Manifold image icon.

 

To provide a component name, that is, a friendly name, double-click into the Component cell for the Microsoft image and provide a desired name. For lack of anything better coming to mind, we will use the name Microsoft HQ. This name will be displayed when Database Console shows the contents of the database.

 

Linking an image Configured with Administrator Console

 

Now that the image in the database has been configured with Administrator Console we can use any Manifold edition to import it or link it with the benefit of friendly names.

 

images\eg_export_dbms_img_12.gif

 

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.

 

To link an image, click on it to highlight it and then press the Link button.

 

images\eg_export_dbms_img_13.gif

 

The result is an image within the project pane that is identical to the original image we exported.

 

Notes

 

New users may wonder about the need to switch off Component View to see the newly-uploaded image before a friendly name has been specified with Administrator Console. Would that not be an inconvenience to most users?

 

As a practical matter, no. When Manifold users have Administrator Console available it is used to assign friendly names when drawings, images or surfaces are uploaded into a database. In that case, the Component View button will always be on and users will always see the contents of their databases using friendly names. Friendly names are such a convenience that most users, even experts, strongly prefer them to raw, database names for items within a database.

 

In contrast, if a Database Administrator Edition license has not been acquired then no friendly names will be in use, the Component View button will always be off and users will always see the contents of their databases using the naming style used by that database. Many users will not mind that if their budgets do not allow for a Database Administrator Edition license.

 

See also the notes on SQL Server Express as an example DBMS at the end of the previous Example topic.

 

This example uses SQL Server 2005. We can also use a spatial DBMS such as SQL Server 2008 to store images using Manifold spatial capabilities. See the Example: Storing an Image in SQL Server 2008 topic for such an example.

 

See Also

 

Geometry in Tables

Manifold Spatial DBMS Facilities

Spatial DBMS

Spatial DBMS Facilities

 

Example: Storing a Drawing in Manifold Spatial DBMS

Example: Storing a Surface in Manifold Spatial DBMS

Example: Storing an Image in SQL Server 2008

 

Data Storage Strategies

Database Installations

Database Administrator Edition

Linked Drawings

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