7

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.

Improve this question
2
  • 3
    Do you have a geometry_columns metadata table in your database? If you can set one of these up and check the box in the 'Create a New MSSQL connection' dialog to only look there for geometry, then that should prevent the delay you encounter as the whole database is checked for geometry every time you connect to it. (I think, anyway. I've been puzzling over this myself).
    – Will
    Commented Dec 16, 2013 at 15:31
  • That turned out to be the right way to go about things, I've accepted Nathan's answer but thanks for pointing out the geometry_columns table, I'd not heard of it before.
    – Tumbledown
    Commented Dec 17, 2013 at 9:28

1 Answer 1

Reset to default
7

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.

Improve this answer
1
  • Thanks Nathan, that works like a charm. I already have spatial indexes and layers seem to load into QGIS in an acceptable time but I do still need to add 'normal' indexing to some of my postcode fields to speed things up even more on views.
    – Tumbledown
    Commented Dec 17, 2013 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.