Mapnik for SQL Server

mapnik-logoThe 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:

image

image 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.

image

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

image

image 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…

5 Responses to Mapnik for SQL Server

  1. lance says:

    Great start to a topic I’ve been eager to find a solution for myself. I await the next entry with great interest.

    Lance

  2. DutchHarry says:

    Hi
    Would you think at the moment the tools are vailable to build a mirror of openstreetmap on the Windows platform based on SQL 2008 R2?
    Including basic routing an drivetime stuff?
    My usual analytical work on healthcare data is all SQL Server based, so extending this with some good spatial stuff would be great.

    I did spend some time in the past to build OSM on SuseLinux in an Hyper-V VM, just to discover that most of my potential ‘clients’ (if you like) are just totally hooked on the MS platform and will never consider something different. Before repeating the exercise I would wnat to knwo if a pro like you considers this feasible at the moment.

    BTW, bough your new book, as aplpha version, and cannot stop studying, as it opens up possibilities I never could have imagined some time go.

    Cheers

    • alastaira says:

      Hi Harry,

      I’m glad you’re finding the book interesting!
      I’m almost certain that it would be possible to host a complete OSM mirror in SQL Server, using .NET to provide routing functionality etc. The main barrier you will have to overcome is that almost all existing OSM developers use PostGIS, so there is very little out there in the way of support/documentation for OSM in SQL Server. The same is true for some other excellent Open-Source Geo products, like OGR2OGR and Mapnik, which is why I’ve started to gather guides on those software myself here.

      I already have lots of half-converted code samples that perform various aspects of OSM loading/validation/routing etc. in .NET. At some point when I get round to it, I’ll probably start a codeplex project to copy them up to.

  3. Sasha says:

    Great article !

    Have you got this working via an XML file as opposed to a python file ? I’ve got everything working nicely and using tilecache, python,mapnik on windows, but in my mapfile (i.e. my mapnik XML file) I am using a shapefile as a data source:

    shape
    ne_110m_admin_0_countries.shp

    Do you know what the parameters are to connect to MS SQL ?

    • Sasha says:

      mmm, it seems that a lot of the XML I posted in my comment was erased. I’ll try again:

      Datasource
      Parameter name=”type” (variable value = shape)
      Parameter name=”file” (variable value = ne_110m_admin_0_countries.shp)
      /Datasource

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s