QGIS – How do you load and then access SQL Server Spatial data within QGIS?

Question:

I would like to store my spatial data within a SQL Server Database and then access that via QGIS, what are the best steps to do this?

Answer:

To load your GIS files into a SQL Database the Cadline DynamicMaps Team would recommend using the Open Source translation tool Ogr2Ogr. This tool is available using the OSGeo4W suite of applications and can be launched using the OSGeo4W Shell icon which is available when you install QGIS.

Having opened the OSGeo4W Shell window, you will have access to a suite of tools available using the Geospatial Data Abstraction Libraries (GDAL) suite. Please note that we have found the GDAL 1.11.3 release (available via QGIS 2.12 Lyon) to be the most robust when looking to import spatial datasets into SQL Server.

At the command line you can now type (or copy and paste) the relevant code to import your GIS file into your chosen SQL Database. In the example below, we will import an ESRI Shapefile of UK Motorways (MWAYS) from my local D drive (D:/Temp/) into a new SQL Table called UK_Motorways.

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=SERVERNAME;database=DATABASENAME;trusted_connection=yes;driver=SQL Server" "D:\Temp\MWAYS.shp" -overwrite -SkipFailures -nln "UK_Motorways" -lco PRECISION=NO -progress

Once you have ran the code the command shell will notify you that the process was successful:

 And your GIS file will now have been imported into your SQL Database:

To connect to your SQL Table via QGIS, choose Layer > Add Layer > Add MSSQL Layer > and in the Data Source Manager choose to create a New Connection and enter the details for your SQL Database. You will need;

• a name for the connection (Connection Name) and this can be anything
• the location of the server (Host) which will be the servers name or IP address
• the username and password to access that SQL Instance

 Now having made a connection to your SQL Database, a list of available tables will be shown:

Select the Table you wish to load and press Add, and the SQL table will then be loaded into your QGIS project:

Tip – to upload a GIS file and specify a new projection, simply add the value -t_srs "EPSG:27700” after the name of the table that you wish to create. E.g.:

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=SERVERNAME;database=DATABASENAME;trusted_connection=yes;driver=SQL Server" "D:\Temp\MWAYS.shp" -overwrite -SkipFailures -nln "UK_Motorways_27700" -t_srs "EPSG:27700” -lco PRECISION=NO -progress