Mapnik for SQL Server

The following work-in-progress page gives some tips relating to using Mapnik to render spatial data (i.e. geometry/geography) from SQL Server.

Order, order!

The most important concept to understand about rendering maps in Mapnik is the concept of how features are ordered and layered. Maps are built up from a number of layers, stacked on top of each other using the “Painter’s Algorithm”. Very simply, what this means is that layers are drawn on top of one another, and any features drawn in higher layers (i.e. those nearer the top of the stack of layers) will obscure any features in lower layers. Just like when you paint a picture.

Layers are drawn in the order in which they appear in the mapfile, so it’s important to make sure you’ve thought about how many and which features should appear in which layer. A typical simple layer structure of a road map might look like this (from background to foreground):

• Topographic Relief Polygon Features (i.e. Shaded areas of land use – forest/water/scrub etc.). These form the “background” of the map, and are only visible if there are no other more prominent features placed on top of them.
• Topographic Line Features (i.e. Contour lines, cliff edges). Plotted after polygon features to ensure they are not obscured.
• Road casing Line layer (i.e. the “edges” of roads)
• Road fill layer
• Building outline Line layer
• Building fill Polygon layer
• Label text layer

Notice that it is common to have the outlines and fills of features like roads and buildings drawn separately, in two different layers. This means that the outlines of all roads will be drawn on the map, and only then will the middle of the roads will be filled. This is important to ensure that the edge casing of one road will never be drawn on top of the fill of a different road:

Plotting road casings and fills in a single layer may lead to road edges being plotted on top of fills.

Also, be aware that, within a layer, all features will share the same z-index, so they’ll simply be plotted in the order they are retrieved from the data source. It’s a relatively common misconception among new SQL Server developers to believe that, in the absence of an ORDER BY clause, the results of a SQL query will be returned “in the order that the records are stored in the database”. This, of course, is nonsense. The fact is that, unless you specify an ORDER BY, the order in which results are returned is completely non-deterministic. So, to arrange the features within each layer in the correct order, it’s really important that all the SQL queries have explicit ORDER BY clauses.

As to the question of what is the correct field to ORDER BY? Just think about the priority of your features – you want the more important features to be on top of other features, which means plotting them last.

The primary (pink) road is returned first in the dataset so plotted first, then the minor (grey) roads are plotted on top.

Adding an ORDER BY statement to the data query for this layer ensures that minor roads are retrieved and plotted first, then the primary road plotted on top.

An interesting exception to the general rule of plotting more important features last (and therefore on top) is when plotting text labels of features. Mapnik contains a label placement algorithm that prevent labels from colliding on the map, which would otherwise make it too cluttered with text. So, when plotting labels, you generally want to retrieve the most important features first, to ensure that they get plotted. Any subsequent labels will only be plotted on the map if they don’t collide with already plotted labels. So, if writing labels of cities, you’d want to ORDER BY the population of each city descending, to ensure larger cities get labelled first.

Defining a SQL Server Layer

Mapnik doesn’t have a dedicated SQL Server driver, but instead supports SQL Server via the generic OGR datasource. So, you can create an OGR VRT file that defines the connection and query to execute against SQL Server for your mapnik layer.

I originally thought I would be able to define all my layers within the same VRT file, but when I tried to do so, by e.g.:

<OGRVRTDataSource>
  <OGRVRTLayer name="OSVectorMap_Roads">
    <SrcDataSource>MSSQL:server=.\SQLEXPRESS;database=Spatial;trusted_connection=yes</SrcDataSource>
    <SrcSQL>SELECT FEATCODE, FEATDESC, geog4326.STAsText() AS geomWKT FROM TG20 WHERE type='roads'</SrcSQL>
    <GeometryField encoding="WKT" field="geomWKT"/>
  </OGRVRTLayer>
  <OGRVRTLayer name="OSVectorMap_Buildings">
    <SrcDataSource>MSSQL:server=.\SQLEXPRESS;database=Spatial;trusted_connection=yes</SrcDataSource>
    <SrcSQL>SELECT FEATCODE, FEATDESC, geog4326.STAsText() AS geomWKT FROM TG20 WHERE type='buildings'</SrcSQL>
    <GeometryField encoding="WKT" field="geomWKT"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

I got an error as follows:

ERROR 1: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt

I’m not sure how Mapnik/OGR uses shared connections, but it seems that you need to define a separate VRT file for each layer. Not a massive problem.

Creating the Mapfile

to be continued…

Applying Styles

to be continued…