Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
Thanks for the reply. I have tried with an ODBC connection to SQL Server in the VRT also, and unfortunately this results in the same error. I don't think it's a problem with the OGR VRT connection to SQL Server as such, because I can load this VRT in QGIS or OGR2OGR, say, and access the SQL features without any problem. I think it's a problem with the Mapnik OGR driver but, as you say, unfortunately not that many OpenSource people use SQL Server :( Forunately, MapServer does work with SQL Server so, although I prefer Mapnik's rendering, I'll have to work with that instead. –  Alastair Aitchison Aug 10 '11 at 12:05
    
@alastair - Are you sure there's no difference? With MSSQL nothing was reaching the database through Mapnik, but with ODBC SQL Profiler showed the queries were being executed. I'll try with real data later. –  geographika Aug 10 '11 at 12:33
add comment

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)

share|improve this answer
    
MySQL -> MS SQL. I wanted to correct it myself, but it complains about "at least 6 chars needed in the edit". –  Igor Brejc Aug 8 '11 at 19:07
    
Typo fixed thanks Igor. –  Mapperz Aug 8 '11 at 19:10
    
I don't know whether you realised, but the blog link you provided is from the question author.... –  Kelso Aug 8 '11 at 22:36
    
@Mapperz - thankyou for taking the time to reply, but please read my question again - I want to hear from anybody who an personally confirm that they have got Mapnik to connect to SQL Server - not just that it theoretically should be possible. As Kelso points out, the blog article you link to is written by me and what's more, if you actually read it, you'll see that I describe how I was unsuccessful at getting the OGR connection to work (hence why I'm posting this question)!. Also, on what basis would you say PostGIS has better geometry functions? –  Alastair Aitchison Aug 9 '11 at 8:47
    
@Alastair: try asking the question on the OSM-dev mailing list (lists.openstreetmap.org/listinfo/dev), there are a lot of Mapnikers there. –  Igor Brejc Aug 9 '11 at 15:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.