How To

Creating a Web Map Tile Cache in SQL Server

Article Number: 000001520 -  Last Modified: Jul 21, 2015

Example workspace showing how to generate a web map tile cache in SQL Server

Web Map Tiling

The basic MakeTiles workspace is below. The first step is to force the image to RGBA, which will make any NoData areas created by the Reprojector be transparent. The WebMapTiler requires the source data to be in the SPHERICAL_MERCATOR coordinate system, so the image is sent to the Reprojector, and then to the WebMapTiler.

The Maximum Zoom Level should be set to a value corresponding to the source image resolution, using the resolution table that can be found on the Bing Maps Tile System page. The Minimum Zoom Level will depend on the size of the source image, but a good way to pick the value is to divide the width of the image by 100, then look up the zoom level corresponding to that resolution.

SQL Server stores images in a blob column rather than as the feature geometry, so we must next use a RasterExtractor to extract the raster into an attribute. The Raster Format should be able to support RGBA rasters, so PNG is a good choice.

User-added image

Multiple Source Images

If you have more than one source image that you need to tile, you can run the whole process in a single workspace by adding a RasterMosaicker before the Reprojector and adding all the source files to the source dataset. However, reprojecting and tiling a very large image may result in running into memory limitations.

An alternative solution is to run all the sources through the MakeTiles individually. This will result in tiles being created with duplicate quadkeys on the image boundaries, but these can be cleaned up using another FME process.

The cleanup process consists of two workspaces. The first workspace, CleanDuplicates, uses a SQL query to extract all the quadkeys that have more than one tile in the table.
   select Quadkey from TileCache group by Quadkey having count(*) > 1

Each result of the query has a where clause constructed from the quadkey, which is then used in a WorkspaceRunner transformer to run the second workspace.

User-added image

The second workspace, MosaicDuplicatesWithDelete, uses the Where clause supplied by the first workspace to extract all the tiles for a particular Quadkey. One copy of the tiles are mosaicked together to form a single new tile, which has its fme_db_operation set to INSERT. The second copy of the tiles have their fme_db_operation set to DELETE. Writing these features back to the database will result in the new mosaicked image being added, while the original partial tiles are deleted.

User-added image

The result of the cleaning process is a seamless tile set, but because the tiles are cleaned by individual Quadkeys, the memory usage is very light.

Storage Requirements

A user responded:
@FMEEvangelist Good post on caching. I wonder how much storage space is needed?

Each zoom level takes .25 the space of the one below it, so if your lowest zoom level corresponds to the resolution of the source image, the total space will be:
org_size + 0.25 * org_size + .0625 * org_size + 0.015625 * org_size...etc.

My calculus is rusty, but I think this will limit out about 1.5 * org_size for a full set of zoom levels.

However, the raster type used in the RasterExtractor also has an effect. I generally use RGBA PNG format to ensure good transparency retention, which is 33% larger than uncompressed RGB and may be up to 10 times as large as JPEG, or 100 times as large as ECW or MrSID.

Suggested Similar Articles

This scenario shows how to prepare raster tiles from vector data for web mapping platforms.

This example demonstrates how to create a disk based image cache using FME and how FME Server can serve that imagery through a WMS service. It will serve image data to WMS capable viewers such as uDIG or Google Earth with a refresh time of less than 15 seconds for 1024x1024 image. The example dataset is imagery of the Austin, Texas area, part of which is included in our Self Study example data.

This demo explains how to set up SQL Server and FME Server to push data from multiple database edits at once to web applications using SQL Server Triggers

Extracts COBie schedules in Revit models to Excel spreadsheets.

This prototype successfully demonstrates that FME Desktop can be used to design data transforms and FME Server can be used to publish them via OGC web services in order to support spatial data integration of different real world data sources into the common data structure defined in the INSPIRE protected area schema.