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