Opening MS SQL Server 2008 Spatial tables in QGIS

Posted on by Nathan

EDIT:  If you are having trouble opening MS SQL 2008 in QGIS I will have a blog post coming explaining how to correct it. Or you can read the comments between TheGeoist and I below which will have the answer.

Just a quick tip.

Thanks to GDAL/OGR 1.8 QGIS can now open MS SQL Server 2008 spatial tables via the OGR MSSQLSpatial driver.

First you must be running a version of QGIS that is using GDAL/OGR 1.8.  Opening the QGIS about page will tell you if it is supported.

Need version 1.8 or higher

As I am writing this on my Ubuntu install I only have version 1.6.3 but the latest dev version of QGIS (upcoming 1.7 release) for Windows in the OSGeo4W installer is complied with version 1.8.

Now open the python console in QGIS and type the following:

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

Replacing {serverName} with your server name, if installed on your local machine you can use localhost; {databaseName} with the name of the database with the tables;{tableName} with the table to open; {yourLayerNameHere} with the name you would like the layer to have in the map legend.

After that you should see your MS SQL Spatial table displayed in QGIS, with editing support.

At the moment there is no nice interface in QGIS to open MS SQL tables like there is for PostGIS, although that might be a good plugin project for someone to work on.

16 thoughts on “Opening MS SQL Server 2008 Spatial tables in QGIS”

  1. Kudos to you for posting this. This is very easy and straight forward. Thank you!

    I certainly prefer PostGres, but the inclusion of MSSQL elevates the functionality of Qgis.

    I have noticed some performance issues on large data sets, particularly with getting data from views with the GDAL/OGR driver for MSSQL, but more testing is required.

    1. No worries. Yeah I also prefer PostGIS but my work place is looking at going for MS SQL 2008 so I am happy QGIS supports that to some extent.

      I haven’t really tested it with a really large dataset but I’ll have a go over the next few days to see what it’s like.

  2. Nathan

    thanks for this.
    I’ve managed to run this in qgis and can see the table displayed in the layer list on the left
    However, there is no map layer visible on the right. if i try to zoom to a record via the attributes table, i get an error
    I’ve tried the connection with another table imported into my SQL database, but the same result.
    Any help appreciated

    1. Do you see the item in the legend list with a little line/point/region symbol or a table like icon?

      I tried this using the latest revision on my work machine a couple of days and I also got a blank map. I’m still looking in the code for what might be causing the problem.

  3. Nathan
    Yes, the table is visible in the layer list on the left, but only a blank map on the right

    Simon

      1. Do you both happen to be running XP? I just threw together a Window 7 virtual Machine with SQL Server 2008 Express and latest build of QGIS and it seems to work fine. However my work machine is a XP machine and it doesn’t work on that one….very strange.

  4. Yes, i’m running XP.I have a laptop with Windows 7 on it and QGIS, but not SQL Server. I might install that and see what happens

    Simon

    1. I am on Windows 7. I am using the nightly trunk of Qgis. I now have it working again, maybe a change in Qgis for me.

  5. I take it back, trying it with different projections, or different sql datatypes seems to make a difference. Qgis displays the attribute tables, but does not always display geometry. Setting the CRS doesnt seem to help. More testing, and maybe a better understanding of the OGR library.

  7. Just running through your tutorial and thought I would comment.

    I’m on XP (SP2) using OSGEO4W current build of QGIS (1.6 w/ GDAL 1.8) and can confirm that I have it working. Starting with an empty table, I have the ability to read / write spatial data from / to SQL Server 2008 using the native geom format.

    I have noticed, however, that if any objects are created / updated with an SRID that is not ‘0’ the information will not be displayed. Any other table that has pre-existing data with an SRID (ie. 4326) will not be displayed, and is not editable.

    Regards/

    1. Cool, now I know what seems to be causing the issue it might be easier to find the bug and kill it. I have been able to reproduce it on my machine so that is a start.

      Thanks!

    2. Bingo! Found out how to do it. You have to have a geometry_columns table as Neil suggested above and add the table that you want to open with it’s SRID.

      Run this:
      CREATE TABLE [dbo].[geometry_columns](
      [f_table_catalog] [varchar](128) NOT NULL,
      [f_table_schema] [varchar](128) NOT NULL,
      [f_table_name] [varchar](256) NOT NULL,
      [f_geometry_column] [varchar](256) NOT NULL,
      [coord_dimension] [int] NOT NULL,
      [srid] [int] NOT NULL,
      [geometry_type] [varchar](30) NOT NULL,
      CONSTRAINT [geometry_columns_pk] PRIMARY KEY CLUSTERED
      (
      [f_table_catalog] ASC,
      [f_table_schema] ASC,
      [f_table_name] ASC,
      [f_geometry_column] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]

      and then add your table as a entry.

      As a test you can run ogrinfo -al "{connection string}" -fid 1 and if Layer SRS WKT: gets returned as (unknown) then something is wrong.

      I will update my blog post to reflect it.

Leave a comment