With the gracious help of Dane Springmeyer, I recently got Mapnik up and running on my Mac, so naturally the first thing I wanted to do was to plot iNaturalist observations. Mapnik will talk with a PostGIS database just fine, but it wont’t pull points from a MySQL database table, at least not directly. What it does support, though, is the all-powerful OGR. Back when I used to use Mapserver, I had used OGR a few times to act as a bridge between simple lat/lon columns in a MySQL table and Mapserver (great description here), so I figured the same would be possible in Mapnik.


First Hurdle: Getting Mapnik to Show Anything At All

First I made an OVF file so OGR would know how to talk to my database:

<OGRVRTDataSource>
  <OGRVRTLayer name="observations">
    <SrcDataSource>MYSQL:inaturalist_development,user=root,tables=observations</SrcDataSource> 
    <SrcSQL>SELECT id, latitude, longitude, iconic_taxon_id FROM observations</SrcSQL> 
    <GeometryType>wkbPoint</GeometryType>
    <GeometryField encoding="PointFromColumns" x="longitude" y="latitude"/> 
  </OGRVRTLayer>
</OGRVRTDataSource>

and made sure it was working:

mapnik-inat > ogrinfo inat.ovf
ERROR 4: Update access not supported for VRT datasources.
Had to open data source read-only.
INFO: Open of `inat.ovf'
      using driver `VRT' successful.
1: observations (Point)

Then I basically modified the Mapnik tutorial to use the OGR virtual data source:

import mapnik
 
# Setup the datasource and layer
ds = mapnik.Ogr(file="inat.ovf", layer="observations")
lyr = mapnik.Layer('test lyr')
lyr.datasource = ds
 
# Style the points using a simple PNG
m = mapnik.Map(600, 300, "+proj=latlong +datum=WGS84")
s = mapnik.Style()
r = mapnik.Rule()
symbolizer = mapnik.PointSymbolizer('mm_8_stemless_iNatGreen.png', 'png', 8, 8)
symbolizer.allow_overlap = True
r.symbols.append(symbolizer)
s.rules.append(r)
m.append_style('My Style', s)
lyr.styles.append('My Style')
m.layers.append(lyr)
 
# Zoom to the extent of the data and write the map to a PNG
m.zoom_to_box(lyr.envelope())
mapnik.render_to_file(m, 'test.png', 'png')

And then I got a map!

test

Ok, a kind of crappy map of test data, but actual, real, visible pixels! So much better than arcane error messages!

Second Hurdle: Google

Of course, it’s just a map of some test points, but still, it worked. Yay. Next thing I wanted to do was to see how easy it would be to lay these points over a Google map, since one of the things I’d like to do with Mapnik is have a layer showing ALL observations on the iNat observation browser. Turns out this was a piece of cake using tilelite.

First I put my map configuration in an XML file, adding a bit of complexity using SQL filters to show different markers for different kinds of points (here corresponding to the IDs for different iconic taxa, e.g. plants, animals, fungi, etc.).

<Map srs="+proj=latlong +datum=WGS84">
  <Style name="observationsStyle">
    <Rule>
      <Filter>[iconic_taxon_id] = 47126</Filter>
      <PointSymbolizer file="mm_8_stemless_iNatGreen.png" type="png" width="8" height="8" />
    </Rule>
    <Rule>
      <Filter>[iconic_taxon_id] = 1 or [iconic_taxon_id] = 47178 or [iconic_taxon_id] = 3 or [iconic_taxon_id] = 20978 or [iconic_taxon_id] = 26036 or [iconic_taxon_id] = 40151</Filter>
      <PointSymbolizer file="mm_8_stemless_DodgerBlue.png" type="png" width="8" height="8" />
    </Rule>
    <Rule>
      <Filter>[iconic_taxon_id] = 47115 or [iconic_taxon_id] = 47119 or [iconic_taxon_id] = 47158</Filter>
      <PointSymbolizer file="mm_8_stemless_OrangeRed.png" type="png" width="8" height="8" />
    </Rule>
    <Rule>
      <Filter>[iconic_taxon_id] = 47170</Filter>
      <PointSymbolizer file="mm_8_stemless_DeepPink.png" type="png" width="8" height="8" />
    </Rule>
    <Rule>
      <ElseFilter/>
      <PointSymbolizer file="mm_8_stemless_unknown.png" type="png" width="8" height="8" />
    </Rule>
  </Style>
 
  <Layer name="observationsLayer" srs="+proj=latlong +datum=WGS84">
    <StyleName>observationsStyle</StyleName>
    <Datasource>
      <Parameter name="type">ogr</Parameter>
      <Parameter name="file">inat.ovf</Parameter>
      <Parameter name="layer">observations</Parameter>
    </Datasource>
  </Layer>
</Map>

Then I fired up tilelite:

tilelite > python liteserv.py ~/projects/mapnik-inat/inatmap.xml
Using mapfile: '/Users/kueda/projects/mapnik-inat/inatmap.xml'
Listening on port 8000...

and added a new GTileLayer to my map:

var myCopyright = new GCopyrightCollection();
var allObsLyr = new GTileLayer(myCopyright, 0, 12, {
  isPNG: true,
  tileUrlTemplate: 'http://localhost:8000/{Z}/{X}/{Y}.png'
});
var allObsOverlay = new GTileLayerOverlay(allObsLyr);
map.addOverlay(window.allObsOverlay);

et voila:

Mapnik-rendered observation points on an iNat map.

I have no idea how scalable this kind of solution is, but I just wanted to show that it works. It was my first real experiment with Mapnik, and I’m pretty happy with how simple things were after the decidedly unsimple installation process.

2 Responses to “Using MySQL lat/lon Columns as a Mapnik Datasource”

  1. Very very very nice!

    I guess this opens many many new possibilities.

    Aggressive caching might make this very scalable – and I guess tiletile has it’s own caching strategies.

    Looking forward for this on iNaturalist!

    Udi

  2. Thanks, Udi. I might also give tilecache a try. Ideally I’d want a tile server with a small memory footprint that would accept taxon IDs in the URL and return tiles containing points for that taxon. Not entirely sure how easy that is to do yet. There’s also the problem that showing these kinds of points on a map make people want to click on them (people like me, for instance). I wish I knew how Google does it