QGIS and SQL Server – Enhancing Performance with Spatial Tables Connection

connectionimportqgisqgis-2sql server

I'm trying to get a SQL Server 2008 R2 spatial database up and running to streamline the production of maps using QGIS.

Currently I just have a handful of spatial tables (a postcode level point table and a few polygon tables for various boundary levels).

My initial plan was to use these to create views (primarily point in polygon counts based on UK postcodes) which I could then pull into QGIS to make the data processing side of map production a lot faster and less fiddly.

Unfortunately my plan has been scuppered somewhat by the amount of time it takes just to connect to my SQL Server using the MSSQL layer tool. QGIS seems to process each table/view prior to allowing me to select any of them. Not only is this extremely slow, it also means that each time I add a new view or table to my database, the whole process is going to get slower and slower until it becomes unusable.

Does anyone have any pointers on how I might be able to speed the QGIS-SQL Server connection process up?

I am beginning to think that saving views is a bad idea and that I should maybe use 'select … into…' scripts to generate tables and minimise the processing required. I'd be interested in peoples' views on this.

Best Answer

Will is correct. You need a geometry_columns table. Without the geometry_columns table QGIS scans each table in order to find the type and srid.

The CREATE script looks like this:

CREATE TABLE [dbo].[geometry_columns](
    [f_table_catalog] [varchar](256) NOT NULL,
    [f_table_schema] [varchar](256) 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
) ON [PRIMARY]

and a sample entry:

enter image description here

f_table_name can also be the name of a view. If this table is found QGIS will look here first to find the metadata about the table and skip the full table scan.

Make sure you tell QGIS to only look in the geometry_columns table as well:

enter image description here

You should also ensure that all your tables have spatial indexes because this will make loading them a lot faster. QGIS will also look in the system spatial index table to find the extents of the layer.

Related Question