Using the SQL Server Spatial library in Windows Azure

Microsoft recently announced that SQL Azure will support working with native spatial data in June of this year. This is great news, and significantly enhances the usability of SQL Azure as a geographic data respository. As a result, many folks may want to start utilizing spatial data in their Azure hosted Web applications. Working with SQL Server spatial data in a .NET application usually involves working with the SQL Server Spatial (SqlServerSpatial.dll) and Types (Microsoft.SqlServer.Types.dll) libraries included with the SQL Server System CLR Types feature pack. The technical capabilities of the SQL Server Spatial library have been established in numerous blog posts, conference sessions, forums, and in the product documentation. Basically the library offers a standard, light-weight set of spatial operators built on geometry and geography spatial data types that are relatively easy to use and integrate in a .NET application. You can download, install, use, and distribute these libraries as needed. The latest edition of the SQL CLR Types was released in November 2009 for SQL Server 2008 R2. If the .NET application that uses the SQL spatial libraries is installed on a local machine or hosted on a local server, you have complete control over the environment in which it operates. However, if you choose to deploy an ASP.NET application as a Windows Azure web service, you will encounter a limitation. The platform for Windows Azure is a flavor of 64-bit Windows Server 2008 prior to the R2 release. The Nov 2009 SQL Spatial library is dependent on the system library MSVCRT80.dll which is not included with this operating system. As a result, when you attempt to use the SQL Spatial library in your Azure hosted ASP.NET web service, you'll likely see the following error:

Unable to load DLL 'SqlServerSpatial.dll': The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log for more detail. (Exception from HRESULT: 0x800736B1)

MSVCR80.dll is required by the SQL Spatial library and must be installed/configured on the host operation system, it cannot simply be dropped in the same directory. In addition, you cannot install features and components in your Windows Azure workspace. So how can you use the SQL Spatial library in an Azure hosted application? Simple, include a SQL Spatial library from a SQL Server 2008 (not R2) edition of the CLR Types. The functionality is basically unchanged.

Here are the steps:

1) Download the 64-bit SQL Server 2008 edition of the SQL Server CLR Types feature pack. I used the October 2008 download successfully.

2) If you don't want to install the feature pack, or you don’t have a 64-bit system on which to install, you can extract the 64-bit version of this library using the following command:

msiexec /a SQLSysClrTypes_64bit.msi /qb TARGETDIR="C:\MyFolder"

3) Copy the Microsoft.SqlServer.Types.dll and SqlServerSpatial.dll into the bin of your Web application. I was able to successfully deploy and use SQL Server Spatial logic in Windows Azure service using the February 2010 edition of the Azure SDK.

Now let's change gears and chat about ESRI's foray into the SQL Server Spatial and Azure world. Currently ESRI has a product named MapIt which includes an ASP.NET Web service, the Spatial Data Service, that uses the SQL Server Spatial library to work with native spatial data in SQL Server 2008. The Spatial Data Service can be deployed as a Windows Azure service and work with spatial data in SQL Azure or any third party that hosts spatial data in SQL Server 2008 (e.g. Discount ASP.NET). You can see the SDS in Windows Azure in action here: http://mapit.cloudapp.net/. To deploy your own SDS as a Windows Azure service, use the Azure deployment utility on the MapIt resource center.

As a parting thought, keep in mind that Microsoft will likely update the Windows Azure platform to include the foundational libraries necessary to support the SQL Server 2008 R2 edition of the spatial library. Hopefully the information in this post will suffice until that time.

Dev Summit 2010, Image Services, and Silverlight

About a week before the Dev Summit I was tasked with showcasing ArcGIS Server 10 image services in the plenary using Silverlight as the platform. The tricky part was limiting the temptation to “light it up” with Silverlight so as not to overshadow image services. And there was another problem - the last time I did anything related to image processing my cell phone was the size of an army field radio, Netscape was the browser du jour, and Enron was a hot stock buy. So I had to ramp up on image services quickly… which meant spending some quality time with the raster gurus at the factory (ESRI). Along the way, I gathered some valuable info and thought it might be interesting to relay a few pivotal gems on the use of image services and Silverlight in ArcGIS 10.

• ArcGIS 10 supports a new geodatabase model, the mosaic dataset, which can easily and effectively manage small and large collections of imagery. They are created and maintained in ArcMap or ArcCatalog and you use a set of GP tools to manage. Mosaic datasets support imagery from multiple sensor platforms including QuickBird, IKONOS, and WorldView. Most importantly, there is no preprocessing required. Mosaicking, pansharpening, and ortho-rectification happen on the fly. Merely add the image to the mosaic dataset and it assimilates.
  
  
• Mosaic datasets support time awareness. Image services are time aware if the underlying mosaic dataset has been time enabled. You enable time awareness in the mosaic dataset properties, not the layer properties (as with feature layers in a map service).
  
  
• Sometimes rasters in a mosaic dataset will not display at full or larger extents. In this case, overviews must be generated. Overviews are similar to pyramid layers in that they allow for the quick display of very large collections of imagery. Overviews do this by resampling source data to generate "snapshot" images for larger map resolutions (larger extents). In most cases, you will want to generate overviews. However, this poses a problem when you want to display a set of rasters that occupy the same space in a time series. Overviews are generated for a specific map resolution (map units/pixel) ranges, not a specific instant or span of time. Therefore if you're viewing your image service at a map resolution that uses an overview, the overview will only show one instant of time regardless of the time extent you specify. In this situation, avoid generating overviews and increase the maximum cell size range factor to enable the display of primary rasters at larger map resolutions. To do this, use the Calculate Cell Size Ranges option from the mosaic dataset context menu in ArcCatalog/ArcMap or use the geoprocessing tool of the same name. Increase the range factor to increase the threshold at which overviews are used, if available.
  
  
• 9.3.1 Image Server functionality is now part of core ArcGIS Server 10.
  
  
• All rasters, including mosaic datasets, can be published as image services. An individual image on disk is termed a "raster dataset" or "raster layer".
  


• Image services include an enhanced export image operation and three new operations: query, identify, and download. Each are covered in detail below.
  


• The export image operation has a new JPGPNG image format which lets the image service define the ideal output format. If the extent requested contains transparent portions, generate a png. If not, generate a jpg.
  


• The export image operation uses mosaic rules to order, filter, and select tiles displayed in the mosaicked image. For example, if a service hosts a set of overlapping images you can define which ones are displayed over others based on an attribute such as date, or a point of interest. The REST API documentation provides minimal insight into parameter descriptions. The SOAP API documentation may provide more information (it definitely will in ArcGIS Server 10 final). Also note, the identify operation uses mosaic rules to order and filter rasters to modify results.
  


• The export image operation provides rendering rules to dynamically change the display of raster data using a set of predefined raster functions. For example, you can apply a custom aspect, slope, color map or shaded relief on the fly. Only 8 raster functions are available with the ArcGIS Server 10 REST API. One item you may notice in a rendering rule is the variableName attribute. It must be set to the value defined in the REST API doc, either “DEM” or “Raster”. It represents a property defined in ArcObjects where values may include “Raster2” or a user defined name for a function chain. These other values are not exposed in the REST API.
  


• The query operation enables clients to define an attribute and/or spatial query and return information about rasters in a dataset hosted by an image service. You can also use it to return raster footprints or a raster thumbnail.
  


• The identify operation enables clients to return a pixel value for rasters hosted in an image service given a user-defined location. The location can be a point or polygon. If a polygon, the centroid of the polygon is used. The order of rasters during the operation can be defined by a mosaic rule. Unlike the identify operation on a map service, image services do not provide a tolerance since only one pixel value per raster can be returned. Do not let the pixel size parameter fool you; it defines a pixel resolution to use when identifying pixel values. This means you can identify on overviews available in the mosaic dataset, and return an interpolated value in an overview image. Interpolation occurs when the overview is generated by resampling source rasters.
  


• The download operation basically enables clients to download individual rasters and metadata hosted by an image service. You can also clip imagery on the server before download and define the download image format. If you don't want users to download individual rasters, you can easily disable this capability of the service.

So what does this mean for the ArcGIS API for Microsoft Silverlight/WPF? To put it simply, the ArcGISImageServiceLayer has been updated to support working with ArcGIS Server 10 image services. A set of new classes will be included to support mosaic and rendering rules. The QueryTask can be used with the image service Query operation. The Identify operation will require a new task, and the Download operation may be supported in the near future. 2.0 public beta will be released in April and you'll get a chance to try out some of this new functionality.

Also, at some point in the near future a set of ArcGIS Server 10 image services will be hosted online by ESRI to demonstrate their functionality. They may or may not be officially announced. In any case, I’ll tweet when they’re up, running, and public (@rex_hansen).