by David Crowther
Question:
Does QGIS work with tables in a SQL Spatial Database?
Answer:
Yes….. it does !
While QGIS is an Open Source geospatial tool and has an inbuilt Database Manager tool allowing you to easily connect to data stored in a PostGIS Database, QGIS is also happy to connect to, open, import into and manage tables in your SQL Spatial Database.
Before we explore these options within QGIS, Cadline also has a Spatial Importer tool, which allows you to undertake BULK uploads of many different GIS file formats, as well as database to database imports – which will save you time and effort for your project work.
1 - Connecting QGIS to SQL –
From the Manage Layers Toolbar – choose > Add MSSQL Spatial Layer
Create a New Connection and enter the details for your database:
- Connection Name – any name that helps you identify your database
- Host – the server details
- Login – the SQL User login details
- Choose Database – select the database to connect to
Once you have created the SQL connection choose Connect and the list of tables in your SQL Database will be shown.
Select a Table to open from the list and choose Add… the table is then opened as a Layer into QGIS.
2 - Uploading from QGIS to SQL –
If you have a GIS file e.g. SHP, TAB, JSON that you wish to upload into your SQL Database, simply open that file into your QGIS Project. Here we have opened a SHP file called Liverpool_Schools.
In order to upload the SHP file into your SQL Database, choose View > Panels > and ensure that you have the Browser Panel ticked.
Scroll down the Browser Panel and find the MSSQL connection. Choose the SQL connection that you just created and open up the DBO (default) schema. Below this will be a list of the spatial tables stored in your SQL Database.
This is a great way to now open SQL tables and add them to your map. For example, by selecting the Table Playzones, you can then right click and choose Add Layer to Project and the Table is added into the QGIS map window.
Now we have the Browser Panel open we can also use this to simply drag our SHP file into the SQL Database and this will action the import.
As the data uploads a progress bar is shown…..
..and once all of the records have been imported into the Table within your SQL database a success message will be shown.
3 - Managing Tables in SQL –
As well as using the Browser Panel to open from and import into your SQL Database, there are some extra options which allow you to undertake the following:
- Work with the Tables Fields
- Truncate a Table
- Export a Table
- Delete a Table
- Review the Table Properties
Working with Table Fields
Clicking on the arrow to the left of the Table name will allow you to reveal the Fields in the SQL Table..
.. and by right clicking on a field you can choose to delete that field from the Tables Design.
Once the Field has been deleted press the Refresh button and the field list now updates and shows that the chosen field no longer exists.
By right clicking on the Fields menu you can also Add New Fields….
… by choosing a Field Name and Field Type.
Export to Other Format –
Right clicking on the Layer and choosing > Export Layer > To File > allows you to export the Layer from the SQL Database and save as another GIS format.
Delete Layer –
Right clicking on the Layer and choosing > Delete Layer > allows you to delete the Table from your SQL Database.
Truncate Table –
Right clicking on the Layer and choosing > Truncate Table > allows you to delete all the records from the table, but maintain the Schema of your Table in your SQL Database.
Table Properties
Right clicking on the Layer and choosing > Layer Properties > allows you to preview the Metadata, Geometry and Attributes of the chosen SQL Table.
Finally, you can use the QGIS Browser Panel to Create a New SQL Table.
Complete the details for the new SQL Table, including:
- Table name
- Field names
- Field types
- Geometry Type – e.g. Multipoint
- CRS – e.g. 27700/BNG
Once you complete the details press OK and the new SQL Table is created in your SQL Database.