Example: Storing an Image in SQL Server 2008

This topic continues the series of SQL Server 2008 examples set forth in the SQL Server Spatial DBMS Facilities topic. In this example we store a large image in SQL Server 2008 using Manifold spatial facilities. We then link it into a project to show the outstanding speed and performance obtained when even large images are stored in SQL Server 2008. This example assumes data sources have already been configured as set forth in the Example: Configuring SQL Server 2008 topic.

 

SQL Server 2008 spatial enhancements do not include a raster image type analogous to Oracle GeoRasters as provided within Oracle's flagship Oracle Spatial product. However, SQL Server 2008 does a masterful job of storing binary data and when that binary data is organized as geospatial image data using Manifold spatial DBMS capabilities, the result is that we can store very large images within SQL Server 2008, preserving full projection information, and utilize them as if SQL Server 2008 had its own native geospatial image storage type.

 

This example uses SQL Server 2008; however, because standard Manifold spatial DBMS capabilities are used it could be done using almost any DBMS, such as SQL Server 2005, ordinary Oracle distributions, PostgreSQL/PostGIS or MySQL. It does not require use of the Manifold spatial extender, as the native facilities of SQL Server are used to manage the binary data stored by Manifold.

 

images\eg_exp_img_sqls_01.gif

 

We will begin with an approximately 600 MB image that shows the Montara Mountain region of California. In the upper right is San Francisco International Airport on San Francisco Bay. At the lower left is the fishing port at Half Moon Bay on the Pacific Ocean. The long, diagonal lake and valley running from the center top to the center right just below and to the left of the settled area near the airport is the valley of the San Andreas fault, the rupture of which in 1906 leveled San Francisco by earthquake and fire.

 

This is an ordinary RGBa image kept within a Manifold project. It was created by opening the sample Montara Mountain surface used in many example topics, linking in an image from a Microsoft Virtual Earth Image Server and then downloading image tiles to approximately 0.3 meter resolution and unlinking the image to create a local image. That is an almost completely automatic process within Manifold, easily accomplished with just a few mouse clicks on menu commands. Manifold automatically maintains projections throughout that process, so the image retains the same Universal Transverse Mercator projection originally used by the Virtual Earth image server.

 

images\eg_exp_img_sqls_02a.gif

 

To export the image we choose File - Export - Image and in the type box choose Data Sources ().

 

images\eg_exp_img_sqls_02.gif

 

In the resulting Data Source dialog we click the user / password pair data source that has already been set up in the Example: Configuring SQL Server 2008 topic. This connects to the server using a specified user name and password and desired database. Press OK (or simply double-click the data source).

 

images\eg_exp_img_sqls_03.gif

 

In the resulting Export Image dialog we use defaults.

 

·      The only Type available is Manifold geospatial image type, since SQL Server 2008 does not have its own image type akin to Oracle GeoRaster. The Manifold spatial database image storage mechanism automatically captures all projection information, so the image will be stored in the Universal Transverse Mercator projection it uses.

·      The Create Pyramids option is a very important option because it automatically creates intermediate views that enable very fast display of the image whether it is zoomed in or zoomed out.

·      The Tile size is automatically estimated by default and should not be changed except by experts. Manifold will automatically store the image as tiles to enable faster and more flexible data retrieval and will automatically recompose the image on the fly. The use of tiles is an internal storage mechanism that is completely invisible to users.

·      The Compression option likewise should not be changed. Manifold will automatically use lossless compression algorithms to reduce the space required for large images within the database. The reduction in space increases performance as well, because modern processors can decompress data faster than disk drives can fetch larger data.

 

Press OK. Depending on the speed of our computers, a few minutes will be required to store the image into SQL Server 2008. We can now launch a new Manifold session to illustrate how to link the image from SQL Server into Manifold.

 

We begin by choosing Tools - Database Console to launch the Database Console, pressing the Refresh button to refresh the connection to the data source.

 

images\eg_exp_img_sqls_04.gif

 

Click on the image and click the Link button. [Note that if we prefer more friendly names in our database view we could use the Administrator Console to assign them.]

 

images\eg_exp_img_sqls_05.gif

 

Almost instantly, a new linked image appears in the project pane. The database cylinder in the icon indicates the image is linked from an external source.

 

images\eg_exp_img_sqls_06.gif

 

If we open the image we can see that it is indeed the image originally exported into SQL Server 2008. We can use the Zoom Box tool to zoom into the Northeast corner of the image.

 

images\eg_exp_img_sqls_07.gif

 

Zooming occurs virtually instantly. We can zoom again for an even closer view of San Francisco Airport.

 

images\eg_exp_img_sqls_08.gif

 

The image is so detailed that if we were to zoom to maximum resolution we could easily see every vehicle and in many cases individual people throughout many square miles of the San Francisco peninsula from the airport to Half Moon Bay. Despite the large size of the image, the power and speed of SQL Server together with Manifold are so fast that we can pan and zoom to any desired view virtually instantaneously.

 

Notes

 

Manifold spatial DBMS storage of images is so fast that if we have a fast DBMS like SQL Server 2008 available for image storage there is no gain in using compressed image technology like ECW.

 

For that matter, the technology in this topic scales so well that if we have the database server space and performance available to handle really large data, there is no need to manually chop up very large images into image libraries or other means of assembling larger images from smaller files. The performance available by combining Manifold with a fast DBMS allows a change in strategy for handling large files as compared to earlier technology.

 

For example, many government agencies and jurisdictions, from small cities to states and countries, are now compiling collections of very detailed aerial imagery. Such collections of images are often available to the public, if the public is able to handle the very large numbers of very large images involved. Such collections often include hundreds of images that are each hundreds of megabytes in size.

 

With older technology we might have had to deal with many large images by keeping the images separate so that each could be displayed with reasonable performance. Manifold storage of images in DBMS is so fast that an entire collection of hundreds of images could be combined into a single 100 GB+ image stored within a DBMS and Manifold would be able to display it almost instantly, using the spatial structures maintained by Manifold to fetch the right tiles for the right intermediate or full-resolution data for whatever view is required.

 

As a practical matter, most users will still keep some reasonable upper limit to the size of any one image in order to facilitate interchange and uploading into the DBMS, but there is no longer much of an upper limit in most applications to the size of images that, once stored in the DBMS, can be displayed within Manifold.

 

Historical Note

 

images\ill_gaspar_de_portola.gif

 

Gaspar de Portola (1717? - 1784?)

 

To the West of the Northern portion of San Andreas Lake stands Sweeney Ridge, upon the 1200 foot summit of which the expedition led by Gaspar de Portola on November 4, 1769, became the first Europeans to see San Francisco Bay.

 

For hundreds of years explorers had sailed past the rocky, narrow, difficult-to-see entrance to the greatest bay and natural anchorage in California without realizing the bay existed. For example, in 1580 Sir Francis Drake sailed past the entrance to San Francisco Bay and even put in for repairs to his ship, the Golden Hind, on the California coast just a few miles from the bay. Portola's overland expedition had walked past his earlier discovery of Monterey to find at last the great bay by land.

 

An intrepid explorer and able leader, Portola led numerous expeditions throughout Alta California, playing a key role in the founding of San Diego and Monterey and serving as Governor of Las Californias.

 

See Also

 

SQL Server Spatial DBMS Facilities

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

 

Data Source Dialog

Example: Storing an Image in Manifold Spatial DBMS

Manifold Spatial DBMS Facilities

Spatial DBMS Facilities

Tools - Database Console