Rendering SQL Server spatial data with Mapnik (Part 2 of n, where n is large)

Last year, I wrote what was intended to be the first of a series of posts about Mapnik – a software toolkit for generating beautiful maps from various sets of spatial data. My intention had been to describe how to render spatial data from SQL Server as styled raster tile layers on Bing Maps, but the series got cut short rather prematurely when I discovered that it didn’t appear to be possible to connect Mapnik to SQL Server (even though it was an OGR-supported format).

Unfortunately, as is often the case with OS software, it seems that almost all Mapnik developers use PostGIS as a backend spatial database, and I failed to find anybody with any experience of SQL Server on either the Mapnik developers mailing list or on StackExchange (rather amusingly, the top-rated answer given to me on StackExchange gives, as its reference source, my own blog post where I explicitly state that the suggested approach doesn’t work!).

image

Since, at the time, I needed a tool to render SQL geometry data there and then, I left Mapnik alone and developed a solution using MapServer instead.

Now, MapServer is a great product: It’s a venerable and highly-respected product in the world of open source spatial software, developed by the University of Minnesota on behalf of NASA, and first released over 16 years ago (to put that in context: MapServer was released at around the same time as HTML 3.2 – long before CSS, AJAX, or Google Maps were heard of). And it’s still going strong today. However, I find that the archaic MapServer architecture and idiosyncracies of its syntax give away its age slightly.

Mapnik, by contrast, is freshly-designed, has a modern XML-style syntax, supports groovy things like node.js and Ruby, and generally feels like it’s not carrying so much baggage. If only it supported SQL Server….

6 months later, and the Release Candidate for Mapnik 2.0 is  now available to download, which claims to support SQL Server, so I thought I’d give it another try. Having just set it up and rendered some quick OS OpenData, I can confirm that it works. Hurray!

image

SSMS Spatial Results Tab Rendering of SQL Server data

image

Mapnik Rendering of the same SQL Server data

Since it seems that I’m one of only few people trying to use Mapnik and SQL Server together (and one of even less writing about it), I’ve started to document some tips in a page here – I’ll add to this page as I go along.

(On a separate note, I now have a whole different problem, because a recent change in the Bing Maps Terms of Use prevents you from adding your own custom road tile layers onto the Bing Map control. So, any tiles of OpenStreetMap or Ordnance Survey road data you create with Mapnik can’t be used with Bing Maps. Fortunately, there’s a workaround for that, but that’s for another post….)

About these ads
This entry was posted in Spatial, SQL Server and tagged , . Bookmark the permalink.

3 Responses to Rendering SQL Server spatial data with Mapnik (Part 2 of n, where n is large)

  1. yudong says:

    Indeed, so few people are trying to use mapnik with sql server, the reason why I’m doing it is because some historical reason, for the data we used to have are all in sql server. Moving data to PostGIS is not supported by the boss :(
    But the progress of solving problem is funny, and even more funny when you know you are not doing these alone.
    Thanks for your work :)

  2. Steve says:

    I’m wanting to do exactly the same thing Alistair. If you want to get in touch and work on some of this together, I’d be keen to. I’m pretty heavily invested in SQL server and .NET web applications, so would like to be able to get a great and well supported tool like Mapnik working for me for rendering web map tiles.

  3. Runge says:

    Steve, you can try another map rendering framework which is called MapSurfer.NET and it is written in C# (.NET).Moreover it can work with different SQL servers (Postgres, MySQL, Microsoft SQL). You can download it from http://openmapsurfer.uni-hd.de/Framework/info.html. In order to see what this framework is able to do, please visit http://openmapsurfer.uni-hd.de. All maps on this web page are rendered using only above-mentioned framework. Have fun in styling and rendering web maps!

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