Can Mapnik render spatial data from SQL Server?

Question

This is a very simple question - I just want to know if anybody can confirm that they have successfully used Mapnik to render tiles directly from data held in SQL Server. (And, if so, what did you do to make it work?) Please do not reply unless you have personally confirmed that this is possible... not simply that it should be possible

I've seen people recommend Mapnik for rendering data from SQL Server in threads such as this one: Any Good Map Rendering Engines for SqlGeometry/SqlGeography

Also, the mapnik website states that "all OGR formats are supported": http://mapnik.org/faq/#data

However, I have been completely unable to use a connection to my local SQL Server database as a datasource for a Mapnik layer. As a test, I'm using a straightforward test OGR VRT, which executes a SQL statement on a SQL Server to select a single inline geography Polygon as Well-Known Text, as follows:

<OGRVRTDataSource>
  <OGRVRTLayer name="test">
    <SrcDataSource>MSSQL:server=zangief\DENALICTP3;database=TempDB;</SrcDataSource> 
    <SrcSQL>SELECT geography::STPolyFromText('POLYGON((0 50, 2 50, 2 53, 0 53, 0 50))', 4326).STAsText() AS geomWKT</SrcSQL>
    <GeometryField encoding="WKT" field="geomWKT"/>
    <LayerSRS>EPSG:4326</LayerSRS>
    <GeometryType>wkbPolygon</GeometryType>
  </OGRVRTLayer>
</OGRVRTDataSource>

(Note I've also tried this with more conventional SELECT * FROM Table syntax). OGRINFO succesfully opens this VRT and reports the following:

ogrinfo mssql.ovf -so -al
INFO: Open of `mssql.ovf'
      using driver `VRT' successful.

Layer name: test
Geometry: Polygon
Feature Count: 1
Extent: (0.000000, 50.000000) - (2.000000, 53.000000)
Layer SRS WKT:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4326"]]
geomWKT: String (0.0)

However, setting this same VRT file as a datasource for a Mapnik layer reports the following error:

ERROR 1: Failed to open datasource `MSSQL:server=zangief\DENALICTP3;database=Tem
pDB;'.
Traceback (most recent call last):
  File "rundemo.py", line 48, in <module>
    provlines_lyr.datasource = mapnik.Ogr(file="mssql.ovf", layer="test")
  File "C:\OSGeo4W\apps\Python25\lib\site-packages\mapnik\__init__.py", line 418
, in Ogr
    return CreateDatasource(keywords)
RuntimeError: Failed to open datasource `MSSQL:server=zangief\DENALICTP3;databas
e=TempDB;'.

It seems that there's some problem between the way that the OGR driver creates the SQL Server connection and Mapnik, but I don't know exactly where it is. I've not yet found any evidence or examples to suggest that anybody else has successfully been able to achieve this either...

Answer 1

There seems to be a lack of a clear overview on the OGR Virtual Format help page. The phrase it can "provide an anchor file for access to non-file oriented datasources" seems to imply any connection type can be used, but I'm not sure this is the case.

Its original function was to allow spatial data to be created from non-spatial sources, for example by combining an X and Y column in a database table to create point features.

However there is no reason why a spatial database can't also be used for this purpose, but you should then use a standard ODBC connection rather than the MSSQL driver.

To do this create a DSN (Data Source Name) in Windows. Then use the ODBC connection and the DSN in the virtual layer configuration. So if your DSN is named OSVectorMap:

<OGRVRTDataSource>
  <OGRVRTLayer name="AASQLlayer">
    <SrcDataSource>ODBC:username/password@OSVectorMap</SrcDataSource>
    <SrcSQL>SELECT geom27700.STAsText() AS geomWKT FROM TG11_Settlement_Area</SrcSQL>
    <GeometryField encoding="WKT" field="geomWKT"/>
    <LayerSRS>EPSG:4326</LayerSRS>
    <GeometryType>wkbPolygon</GeometryType>
  </OGRVRTLayer>
</OGRVRTDataSource>

There are a few more details on creation of Virtual Layers in the MapServer wiki and Virtual Spatial Data page.

Great blog post by the way. I wouldn't worry too much about using Python 3.x - nearly all the useful GIS libraries are still in 2.x And the SQL Server Geometry and Geography types are great. The only downsides are that SQL Server unfortunately seems a second class citizen in the opensource world in terms of blog posts, and integration with other tools.

Answer 2

You can use the Virtual Format (GDAL) to pull data from your MSSQL Database

http://www.gdal.org/ogr/drv_vrt.html

    <OGRVRTDataSource>
  <OGRVRTLayer name="AASQLlayer">
    <SrcDataSource>MSSQL:server=.\SQLEXPRESS;database=OSVectorMap;trusted_connection=yes</SrcDataSource>
    <SrcSQL>SELECT geom27700.STAsText() AS geomWKT FROM TG11_Settlement_Area</SrcSQL>
    <GeometryField encoding="WKT" field="geomWKT"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

source: http://alastaira.wordpress.com/2011/06/16/tile-rendering-with-mapnik-part-1-of-n-where-n-is-large/

Though recommend Postgres PostGIS (has better Geometry functions on the database side)