Opening MS SQL Server 2008 Spatial tables in QGIS – Correctly


EDIT (22-07-12): QGIS 1.8 and above now ships with a native MS SQL data provider.  Look for Add MSSQL Spatial Layer in the Layer menu.

Turns out the last blog post I did on this subject contained a few errors, mainly that QGIS wouldn’t render the layer when you opened it.

The answer is so obvious it’s almost embarrassing :)

In order to open and display a SQL Server 2008 layer in QGIS correctly, via OGR, you must have a geometry_columns table in your database with the name, geometry type and srid of the layer. That’s it! Oh look, it was even right in front of me in the OGR code for the mssqlspatial driver.

int OGRMSSQLSpatialTableLayer::FetchSRSId()
{
    CPLODBCStatement oStatement = CPLODBCStatement( poDS->GetSession() );
    oStatement.Appendf( "select srid from geometry_columns "
                    "where f_table_schema = '%s' and f_table_name = '%s'",
                    pszSchemaName, pszTableName );

    if( oStatement.ExecuteSQL() && oStatement.Fetch() )
    {
        if ( oStatement.GetColData( 0 ) )
            nSRSId = atoi( oStatement.GetColData( 0 ) );
    }

    return nSRSId;
}

So the process to open a MS SQL 2008 spatial layer in OGR is as follows.

There are two main tables which tell OGR how to read a layers projection:

  • geometry_columns
  • spatial_ref_sys

geometry_columns contains the table name and the key for the table spatial_ref_sys which contains the projection string. The projection string is the info that QGIS needs in order to correctly render a layer.

The easiest way to get the correct tables is to let OGR handle it for you via ogr2ogr, then just adding any other tables you may have already in your database to the geometry_columns table.

So to get ogr2ogr to create the right tables for you it’s as simple as running the following command from inside the OSGeo4W shell, changing the connection string part of course:

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=.\MSSQLSERVER2008;database=geodb;trusted_connection=yes" "rivers.tab"

(sample taken from http://www.gdal.org/ogr/drv_mssqlspatial.html)

Uploading even just one table this way will create both tables and fill in the needed info.
The geometry_columns table:

f_table_catalogf_table_schemaf_table_namef_geometry_column

geodb dbo rivers ogr_geometry

coord_dimensionsridgeometry_type

2 32768 POLYGON

The spatial_ref_sys table:

sridauth_nameauth_sridsrtextproj4text

32768 NULL NULL PROJCS[“UTM_Zone_56_Southern_Hemisph…. +proj=utm +zone=56 +south +ellps=GRS80 +units=m +no_defs

So if you have already existing tables in your MS SQL 2008 database that were loaded, via say MapInfo’s EasyLoader, you would just upload one table via ogr2ogr to create the two tables needed by QGIS(using OGR) and then add the other tables to the geometry_columns table. If they are all in the same projection than you are in luck as you will only need to upload one in order to get the right strings in the spatial_ref_sys table, if not just upload a small sample for each projection.

Then you can open the table in QGIS using:

uri = "MSSQL:server={serverName};database={databaseName};tables={tableName};trusted_connection=yes"
qgis.utils.iface.addVectorLayer(uri,'{yourLayerNameHere}','ogr')

Tip: In order to test you have correctly set the table in geometry_columns you can run another ogr tool ogrinfo:

ogrinfo -al "MSSQL:server=localhost;database={your database};tables={your table}" -fid 1

If you see a value in Layer SRS WKT: then chances are it’s set right and QGIS should be able to render it, however if you see: Layer SRS WKT:(unknown) Than chances are QGIS will not render it correctly.

Hopefully this help people use MS SQL 2008 Spatial with QGIS, a important step I think in the world of using QGIS on Windows (especially when you don’t have the freedom to run PostGIS:) ).

I might even do a video tutorial when I get some free time after my exams and my wedding.

About these ads

21 thoughts on “Opening MS SQL Server 2008 Spatial tables in QGIS – Correctly

  1. Don’t forget that ogr is case sensitive and windows mssql isn’t. Actually, ogr use only small letter. I think that I have problem when I was define geometry columns with use of capital and small letter.
    Keep the good work on!

    Mladen

  2. Don’t feel too bad, I was reading the MSSQLSpatial and noticed the reference to the geometry_columns table also. Didn’t think anything of it at the time, thanks for spelling out the issue.

    • Just tested it on my original data and it’s working fine after populating the geometry_columns, and spatial_ref_sys tables.

      Thanks again!

      • Good stuff!. Glad it worked.

        There is a script in PostGIS that you can run that will auto populate the geometry_columns tables with table information that are already in the database but not in geometry_columns, I’ll see if I can modify it for MS SQL use.

  3. Don’t suppose you’ve tried GDAL/OGR 1.8 to access SQL Server 2008 as a datasource for Mapnik, have you?

    I can get SQL Server working fine with OGR2OGR, and I’ve also created a virtual layer .OVR file that goes to SQL Server – both of which OGRINFO will report as correct, but can’t get Mapnik to connect to SQL Server… I’ve tried following your steps here just to see if it was a similar issue as with QGIS, but sadly no dice. Thanks!

  4. I have found that for some reason, geometry_columns and spatial_ref_sys need to be owned by dbo when accessing layers on mssqlspatial using ogr. However, when importing tables to mssqlspatial with ogr2ogr (at least when using a trusted connection), the tables are created with the current user as owner. So the tables either need to be changed using sp_changeobjectowner or to be explicitly created with dbo as owner.

  5. We’ve tried to get this to work and it does great for tables that have a gemetry data type, but for tables with a geography data type, we always get an error like :

    Anyone have any insight into this?
    We’re using qgis 1.8

    • Hi there, How did you use the ogr2ogr ? what I have to write in the “rivers.tab” area ? I really do not understant the syntax. I ‘m using MS Sql Server 2008 R2 and QGIS 1.8. Any idea?

      • I have problem understand the syntax to. Where do I find the “*.tab”. Is it a table or a file?

      • I have exactly the same question: Where can I find more information about what “rivers.tab” is and what it looks like?

      • rivers.tab is just an example MapInfo Tab file. You just have to pass the MapInfo TAB file (it can be any format that ogr2ogr supports run ogr2ogr –help) as the last argument to ogr2ogr.

      • Do you have an explanation for a novice and I’m new to this. What typically goes in the .tab file when you want to run QGIS against a SQL Server 2008 R2 database?

  6. Any idea when we’ll see a easy to use Add SQL Server 2008 Layer function like that for PostGIS in Quantum GIS? Might even consider paying for such an function if someone lets me know what it would cost. My company is all for using QGIS if I can make it easy for average users to add new layers.

    C

    • Good news! It’s currently in the works just got a bit held up by a few things over Christmas, the guy working on it has a few other big things on at the current time. I’ll update the post when I know more about it’s status.

  7. Pingback: QGIS now with 100% more MS SQL 2008 support « Nathans QGIS and GIS blog

  8. Hi, please could you help me about this ?
    After several trials I get the right string but with errors

    ogr2ogr -overwrite -f “MSSQLSpatial” “MSSQL:server=MyServer;database=GS;UID=sa;PWD=12345″ “D:\folder\test.TAB”

    This creates 2 so deparately needed table in dbo schema (geometry_columns, spatial_ref_sys) and my dbo.TEST table but without any objects in it (it consists only from polylines) and I got this errors:

    ERROR 1: Column ogr_geometry requested for geometry, but it does not exist.
    *(this is probably because i dont have ogr_geometry column in my test table)

    ERROR 1: Error creating field column1, [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object “dbo.test” because it does not exist or you do not have permissions.
    * I dont get it, as you see its my SA account.

    ERROR 1: INSERT command for new feature failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘)’.
    ERROR 1: Terminating translation prematurely after failed translation of layer test-ogr (use -skipfailures to skip errors)
    * i dont understand at all

    p.s
    I even try to change driver but I dont get anything either in MSSQL database or command prompt. Is this correct at all ?
    ogr2ogr -overwrite -f “MSSQLSpatial” “Driver={SQL Server Native Client 10.0};MSSQL:server=MyServer;database=GS;UID=sa;PWD=12345″ “D:\folder\test.TAB”

    Thanks for your blog. I am a long time reader but this is my first post.

    • Sorry. This is solved. Table name in my post was not copied right, it was actually “test-ogr.tab”. After removal of “-” character, everything went fine.

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