The SqlGeometry with Microsoft SQL Server

I came across a curious error earlier today when attempting to use a SqlDataReader to read a column of geometry data from a SQL Server table:

System.InvalidCastException: Unable to cast object of type ‘Microsoft.SqlServer.Types.SqlGeometry’ to type ‘Microsoft.SqlServer.Types.SqlGeometry’

SqlGeometry to SqlGeometry… you’d think that would be a pretty easy cast, wouldn’t you? It turns out that this is a problem caused by a conflict between the spatial libraries used in SQL Server Denali compared to that in 2008/R2, and you’ll get this error depending on which version of Microsoft.SqlServer.Types.dll you use, and how you try to access geometry or geography columns from a datareader:

[php]
while (dataReader.Read())
{
// This works in SQL Server 2008/R2, but errors with Denali
SqlGeometry g = (SqlGeometry)dataReader.GetValue(0);

// This works in SQL Server 2008/R2, but errors with Denali
SqlGeometry g = (SqlGeometry)dataReader["GeomCol"];

// This works in Denali, but not in SQL Server 2008/R2
SqlGeometry g = SqlGeometry.Deserialize(reader.GetSqlBytes(0));

// This works in Sql Server 2008/R2/Denali
SqlGeometry g = new SqlGeometry();
g.Read(new BinaryReader(reader.GetSqlBytes(0).Stream));
}
[/php]

After a bit of digging around, it appears that using GetValue or square brackets notation [] to access a geometry/geography field in a SqlDataReader is hard-coded to load the 10.0 (SQL Server 2008) version of the Microsoft.SqlServer.Types library.

If you’ve got side-by-side installations of both SQL Server 2008/R2 and Denali (as I have), and try to reference the 11.0 (Denali) version of Microsoft.SqlServer.Types, you’ll therefore get an assembly mismatch when both versions of the library are loaded, which causes the slightly unhelpful error listed at the top of this post. Even if you’ve only got Denali installed, your code may still try to reference a (non-existent) 2008/R2 version of the Microsoft.SqlServer.Types.dll library, so you’ll get a different error instead:

Could not load file or assembly ‘Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.

The simplest way to resolve these errors is by changing the way you reference any geography/geometry (and, I imagine, hierarchyid) columns from your DataReader, as in the code example above. Alternatively, you can set up an assembly redirection in the application configuration file as explained here (about halfway down), which will allow you to correctly target the Denali version.

As per the What’s new in SQL Server Denali whitepaper, “… side-by-side installations of SQL Server Code-Named “Denali” CTP1 and CTP3 are not supported with existing SQL Server 2008 installations …”, so perhaps I only have myself to blame for this. Interestingly though, the person who raised this MS Connect issue, says that they have experienced exactly the same problem on a clean install of Denali CTP3. The response from Microsoft suggests that this may be due to older versions of the library being packaged with Visual Studio 2010, and also confirms that the problem will not be resolved prior to RTM of SQL Server Denali.

Strangely, I encountered another curious error a few months ago concerning version conflicts of Microsoft.SqlServer.Types. My CTP3 Management Studio Spatial Results tab does not plot curved geometries (selecting a CircularString or the result of BufferWithCurves etc. just produces a blank pane). I had originally assumed that, since this was only a CTP release, this feature had simply not been added yet. It turns out that curved geometries are supported in SSMS CTP3 Spatial Results tab but, if you have side-by-side SQL Server 2008 and Denali, this can corrupt this feature. I guess the reason is similar – that SSMS is somehow attempting to load the SQL Server 2008/R2 version of Microsoft.SqlServer.Types, which, of course, doesn’t support curved geometries.

Deleting Blank Tiles

 

Creating raster tilesets almost invariably leads to the creation of some blank tiles – covering those areas of space where no features were present in the underlying dataset. Depending on the image format you use for your tiles, and the method you used to create them, those “blank” tiles may be pure white, or some other solid colour, or they may have an alpha channel set to be fully transparent.

Here’s an example of a directory of tiles I just created. In this particular z/x directory, more than half the tiles are blank. Windows explorer shows them as black but that’s because it doesn’t process the alpha channel correctly. They are actually all 256px x 256px PNG images, filled with ARGB (0, 0, 0, 0):

image

What to do with these tiles? Well, there’s two schools of thought:

  • The first is that they should be retained. They are, after all, valid image files that can be retrieved and overlaid on the map. Although they aren’t visually perceptible, the very presence of the file demonstrates that the dataset was tested at this location, and confirms that no features exist there. This provides important metadata about the dataset in itself, and confirms the tile generation process was complete. The blank images themselves are generally small, and so storage is not generally an issue.
  • The opposing school of thought is that they should be deleted. It makes no sense to keep multiple copies of exactly the same, blank tile. If a request is received for a tile that is not present in the dataset, the assumption can be made that it contains no data, and a single, generic blank tile can be returned in all such instances – there is no benefit of returning the specific blank tile associated with that tile request. This not only reduces disk space on the tile server itself, but the client needs only cache a single blank tile that can be re-used in all cases where no data is present.

I can see arguments in favour of both sides. But, for my current project, disk and cache space is at a premium, so I decided I wanted to delete any blank tiles from my dataset. To determine which files were blank, I initially thought of testing the filesize of the image. However, even though I knew that every tile was of a fixed dimension (256px x 256px), an empty tile can still vary in filesize according to the compression algorithm used. Then I thought I could loop through each pixel in the image and use GetPixel() to retrieve the data to see whether the entire image was the same colour, but it turns out that GetPixel() is slooooowwwww….

The best solution I’ve found is to use an unsafe method, BitMap.LockBits to provide direct access to the pixel byte data of the image, and then read and compare the byte values directly. In my case, my image tiles are 32bit PNG files, which use 4 bytes per pixel (BGRA), and my “blank” tiles are completely transparent (Alpha channel = 0). Therefore, in my case I used the following function, which returns true if all the pixels in the image are completely transparent, or false otherwise:

public static Boolean IsEmpty(string imageFileName)
{
  using (Bitmap b = ReadFileAsImage(imageFileName))
  {
    System.Drawing.Imaging.BitmapData bmData = b.LockBits(new Rectangle(0, 0, b.Width, b.Height), System.Drawing.Imaging.ImageLockMode.ReadOnly, b.PixelFormat);
    unsafe
    {
      int PixelSize = 4; // Assume 4Bytes per pixel ARGB
      for (int y = 0; y < b.Height; y++)
      {
        byte* p = (byte*)bmData.Scan0 + (y * bmData.Stride);
        for (int x = 0; x < b.Width; x++)         {           byte blue = p[x * PixelSize]; // Blue value. Just in case needed later           byte green = p[x * PixelSize + 1]; // Green. Ditto           byte red = p[x * PixelSize + 2]; // Red. Ditto           byte alpha = p[x * PixelSize + 3];           if (alpha > 0) return false;
        }
      }
    }
    b.UnlockBits(bmData);
  }

  return true;
}

 

It needs to be compiled with the /unsafe option (well, it did say in the title that this post was dangerous!). Then, I just walked through the directory structure of my tile images, passing each file into this function and deleting those where IsEmpty() returned true.

 

Custom Fonts in Open Street Map

The default font family used for rendering text labels on maps created by Mapnik (and the one shipped with it) is Deja Vu. Deja Vu Sans Book is a pretty nice font, and can be seen, for example, in all the labels used on Open Street Map:

image

So what if you want to render text onto a custom tile overlay using a different font?

  • Perhaps you’d like to create a custom tile layer using image labels to match Google Maps?
  • Or how about going for a Bing Maps feel with image labels?

To do so, first you need to register the location of the directory containing the fonts you want to use. For example, to make all the installed Windows fonts available to Mapnik, add the following to the top of your python render script:

mapnik.register_fonts('c:/windows/fonts/')

Mapnik needs to reference these fonts by their font name (not by filename, for example); To find out the correct name to use for each font, once you’ve registered your custom fonts directory you can call the following:

for face in mapnik.FontEngine.face_names(): print face

On a fairly vanilla installation of Window 7, this gave me a list of available fonts as follows:

  • Aharoni Bold
  • Andalus Regular
  • Andy Bold
  • Angsana New Bold
  • Angsana New Bold Italic
  • Angsana New Italic
  • Angsana New Regular
  • AngsanaUPC Bold
  • AngsanaUPC Bold Italic
  • AngsanaUPC Italic
  • AngsanaUPC Regular
  • Aparajita Bold
  • Aparajita Bold Italic
  • Aparajita Italic
  • Aparajita Regular
  • Arabic Typesetting Regular
  • Arial Black
  • Arial Bold
  • Arial Bold Italic
  • Arial Italic
  • Arial Regular
  • Browallia New Bold
  • Browallia New Bold Italic
  • Browallia New Italic
  • Browallia New Regular
  • BrowalliaUPC Bold
  • BrowalliaUPC Bold Italic
  • BrowalliaUPC Italic
  • BrowalliaUPC Regular
  • Buxton Sketch Regular
  • Calibri Bold
  • Calibri Bold Italic
  • Calibri Italic
  • Calibri Regular
  • Candara Bold
  • Candara Bold Italic
  • Candara Italic
  • Candara Regular
  • Comic Sans MS Bold
  • Comic Sans MS Regular
  • Consolas Bold
  • Consolas Bold Italic
  • Consolas Italic
  • Consolas Regular
  • Constantia Bold
  • Constantia Bold Italic
  • Constantia Italic
  • Constantia Regular
  • Corbel Bold
  • Corbel Bold Italic
  • Corbel Italic
  • Corbel Regular
  • Cordia New Bold
  • Cordia New Bold Italic
  • Cordia New Italic
  • Cordia New Regular
  • CordiaUPC Bold
  • CordiaUPC Bold Italic
  • CordiaUPC Italic
  • CordiaUPC Regular
  • Courier New Bold
  • Courier New Bold Italic
  • Courier New Italic
  • Courier New Regular
  • DFKai-SB Regular
  • DRMS T1 Regular
  • DaunPenh Regular
  • David Bold
  • David Regular
  • DengXian Bold
  • DengXian Regular
  • DilleniaUPC Bold
  • DilleniaUPC Bold Italic
  • DilleniaUPC Italic
  • DilleniaUPC Regular
  • DokChampa Regular
  • Ebrima Bold
  • Ebrima Regular
  • Estrangelo Edessa Regular
  • EucrosiaUPC Bold
  • EucrosiaUPC Bold Italic
  • EucrosiaUPC Italic
  • EucrosiaUPC Regular
  • Euphemia Regular
  • FangSong Regular
  • FrankRuehl Regular
  • Franklin Gothic Medium Italic
  • Franklin Gothic Medium Regular
  • FreesiaUPC Bold
  • FreesiaUPC Bold Italic
  • FreesiaUPC Italic
  • FreesiaUPC Regular
  • Gabriola Regular
  • Gautami Bold
  • Gautami Regular
  • Georgia Bold
  • Georgia Bold Italic
  • Georgia Italic
  • Georgia Regular
  • Gisha Bold
  • Gisha Regular
  • Impact Regular
  • IrisUPC Bold
  • IrisUPC Bold Italic
  • IrisUPC Italic
  • IrisUPC Regular
  • Iskoola Pota Bold
  • Iskoola Pota Regular
  • JasmineUPC Bold
  • JasmineUPC Bold Italic
  • JasmineUPC Italic
  • JasmineUPC Regular
  • Jing Jing Regular
  • KaiTi Regular
  • Kalinga Bold
  • Kalinga Regular
  • Kartika Bold
  • Kartika Regular
  • Khmer UI Bold
  • Khmer UI Regular
  • KodchiangUPC Bold
  • KodchiangUPC Bold Italic
  • KodchiangUPC Italic
  • KodchiangUPC Regular
  • Kokila Bold
  • Kokila Bold Italic
  • Kokila Italic
  • Kokila Regular
  • Kootenay Regular
  • Lao UI Bold
  • Lao UI Regular
  • Latha Bold
  • Latha Regular
  • Leelawadee Bold
  • Leelawadee Regular
  • Levenim MT Bold
  • Levenim MT Regular
  • LilyUPC Bold
  • LilyUPC Bold Italic
  • LilyUPC Italic
  • LilyUPC Regular
  • Lindsey Regular
  • Lucida Console Regular
  • Lucida Sans Unicode Regular
  • MV Boli Regular
  • Malgun Gothic Bold
  • Malgun Gothic Regular
  • Mangal Bold
  • Mangal Regular
  • Marlett Regular
  • Microsoft Himalaya Regular
  • Microsoft JhengHei Bold
  • Microsoft JhengHei Regular
  • Microsoft MHei Bold
  • Microsoft MHei Regular
  • Microsoft NeoGothic Bold
  • Microsoft NeoGothic Regular
  • Microsoft New Tai Lue Bold
  • Microsoft New Tai Lue Regular
  • Microsoft PhagsPa Bold
  • Microsoft PhagsPa Regular
  • Microsoft Sans Serif Regular
  • Microsoft Tai Le Bold
  • Microsoft Tai Le Regular
  • Microsoft Uighur Regular
  • Microsoft YaHei Bold
  • Microsoft YaHei Regular
  • Microsoft Yi Baiti Regular
  • Miramonte Bold
  • Miramonte Regular
  • Miriam Fixed Regular
  • Miriam Regular
  • Moire Bold
  • Moire ExtraBold
  • Moire Light
  • Moire Regular
  • Mongolian Baiti Regular
  • MoolBoran Regular
  • Motorwerk Regular
  • Narkisim Regular
  • Nina Bold
  • Nina Regular
  • Nyala Regular
  • Palatino Linotype Bold
  • Palatino Linotype Bold Italic
  • Palatino Linotype Italic
  • Palatino Linotype Regular
  • Pericles Light
  • Pericles Regular
  • Pescadero Bold
  • Pescadero Regular
  • Plantagenet Cherokee Regular
  • Raavi Bold
  • Raavi Regular
  • Rod Regular
  • Sakkal Majalla Bold
  • Sakkal Majalla Regular
  • Segoe Condensed Bold
  • Segoe Condensed Regular
  • Segoe Marker Regular
  • Segoe Print Bold
  • Segoe Print Regular
  • Segoe Script Bold
  • Segoe Script Regular
  • Segoe UI Bold
  • Segoe UI Bold Italic
  • Segoe UI Italic
  • Segoe UI Light
  • Segoe UI Regular
  • Segoe UI Semibold
  • Segoe UI Symbol Regular
  • Segoe WP Black
  • Segoe WP Bold
  • Segoe WP Light
  • Segoe WP Regular
  • Segoe WP SemiLight
  • Segoe WP Semibold
  • Shonar Bangla Bold
  • Shonar Bangla Regular
  • Shruti Bold
  • Shruti Regular
  • SimHei Regular
  • SimSun-ExtB Regular
  • Simplified Arabic Bold
  • Simplified Arabic Fixed Regular
  • Simplified Arabic Regular
  • SketchFlow Print Regular
  • Sylfaen Regular
  • Symbol Regular
  • Tahoma Bold
  • Tahoma Regular
  • Times New Roman Bold
  • Times New Roman Bold Italic
  • Times New Roman Italic
  • Times New Roman Regular
  • Traditional Arabic Bold
  • Traditional Arabic Regular
  • Transport Medium
  • Trebuchet MS Bold
  • Trebuchet MS Bold Italic
  • Trebuchet MS Italic
  • Trebuchet MS Regular
  • Tunga Bold
  • Tunga Regular
  • Utsaah Bold
  • Utsaah Bold Italic
  • Utsaah Italic
  • Utsaah Regular
  • Vani Bold
  • Vani Regular
  • Verdana Bold
  • Verdana Bold Italic
  • Verdana Italic
  • Verdana Regular
  • Vijaya Bold
  • Vijaya Regular
  • Vrinda Bold
  • Vrinda Regular
  • Webdings Regular
  • Wingdings Regular
  • Yu Gothic Bold
  • Yu Gothic Regular

To use one of these fonts, set the TextSymbolizer face_name attribute in the style applied to your layer to the name of whatever font you want.

For example, here’s a style with default Deja Vu Sans Book text labels:


and this is the map it creates:

image

And here’s the same map rendered with, oh I don’t know, say Comic Sans MS Regular style (don’t ever, ever do this! Maybe unless you’re creating a map of how to get to a children’s party…)

which leads to this:

image