• Blog posts

    Bio

    sqlandqgis_FAQ3.png

    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.

    20.PNG

    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.

    21.PNGAt 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:

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

     23.PNG

    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

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

    25.PNG

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

    26.PNG

    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