qGIS, GEOMETRY_COLUMNS and SQL Server Spatial

Introduction

For a small investment in effort, use of the GEOMETRY_COLUMNS table within a SQL Server Spatial database, will improve the performance of spatial data discovery by qGIS.

Background

The ISO19125 standard – OpenGIS® Implementation Specification for Geographic information – Simple feature access – Part 2: SQL option – provides for the creation and use of the GEOMETRY_COLUMNS metadata table.

The GEOMETRY_COLUMNS table describes the available feature tables and their geometry properties.

Table Definition

The following definition can be used to create the GEOMETRY_COLUMNS table within SQL Server. This definition includes additional columns for qGIS (prefixed with qgis_).

CREATE TABLE [dbo].[geometry_columns] (
       [f_table_catalog]   [varchar](128) NOT NULL
      ,[f_table_schema]    [varchar](128) 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
      ,[qgis_xmin]         [float]
      ,[qgis_ymin]         [float]
      ,[qgis_xmax]         [float]
      ,[qgis_ymax]         [float]
      ,[qgis_pkey]         [varchar](128)
     CONSTRAINT [geometry_columns_pk] PRIMARY KEY CLUSTERED (
         [f_table_catalog]    ASC
         ,[f_table_schema]    ASC
         ,[f_table_name]      ASC
         ,[f_geometry_column] ASC
         ,[geometry_type]     ASC
     ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
    ON [PRIMARY]
) ON [PRIMARY];
GO

Populating the Table

The above table can be populated manually, or it can be populated via use of one of the following functions which are available within the TSQL functions downloadable from this website via a small donation.

• STPopulateGeometryColumns — Procedure that adds rows to the GEOMETRY_COLUMNS table for the specified schema/tables
• STAddGeometryColumn — Procedure that adds metadata rows for a single geometry column to GEOMETRY_COLUMNS
• STSetGeometryColumnExtent — Procedure that computes spatial extent of table/column and updates entry in GEOMETRY_COLUMNS

STPopulateGeometryColumns calls STAddGeometryColumn and STSetGeometryColumnExtent.

An example of how to call the STPopulateGeometryColumns procedure is as follows.

DROP TABLE dbo.foo;
GO

CREATE TABLE dbo.foo ( 
  id   int Identity not null,
  geom_type varchar(50),
  geom geography,
  constraint foo_pk primary key (id)
); 

INSERT INTO dbo.foo (geom) VALUES (geography::STGeomFromText('POINT(147.111 -32.2222)',4326));
INSERT INTO dbo.foo (geom) VALUES (geography::STGeomFromText('MULTIPOINT((147.21 -32.22),(147.31 -32.32))',4326));

INSERT INTO dbo.foo (geom) VALUES (geography::STGeomFromText('LINESTRING(147.1 -32.2, 147.1 -33.2)',4326));
INSERT INTO dbo.foo (geom) VALUES (geography::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING(147.0 -31.5, 147.5 -31.0, 148.0 -31.5),(148.0 -31.5, 147.0 -31.5))',4326));
INSERT INTO dbo.foo (geom) VALUES (geography::STGeomFromText('CIRCULARSTRING(148.0 -32.0,148.51 -31.5,149.0 -32.0)',4326));
INSERT INTO dbo.foo (geom) VALUES (geography::STGeomFromText('MULTILINESTRING((147.21 -32.2, 147.21 -33.2),(148.21 -33.0,148.22 -33.1))',4326));

INSERT INTO dbo.foo (geom) VALUES (geography::STGeomFromText('POLYGON((147.0 -31.0,147.5 -31.0,147.5 -30.5,147.0 -30.5,147.0 -31.0))',4326));
INSERT INTO dbo.foo (geom) VALUES (geography::STGeomFromText('MULTIPOLYGON (((148.0 -33.9,148.9 -33.9,148.9 -33.0,148.0 -33.0,148.0 -33.9)),((149.1 -33.1,149.2 -33.1,149.2 -33.0,149.1 -33.0,149.1 -33.1)))',4326));
INSERT INTO dbo.foo (geom) VALUES (geography::STGeomFromText('CURVEPOLYGON(COMPOUNDCURVE(CIRCULARSTRING(145.0 -34.5,145.5 -35.0, 146.0 -34.0),(146.0 -34.0, 145.0 -34.5)),(145.6 -34.5,145.6 -34.3,145.8 -34.3,145.8 -34.5,145.6 -34.5))',4326));

UPDATE dbo.foo SET geom_type = geom.STGeometryType();

delete from dbo.geometry_columns where f_table_name = 'foo';

execute dbo.STPopulateGeometryColumns 'dbo', 'foo';

The resultant entries are as follows.

NOTE: The STAddGeometryColumn procedure discovers all the Geometry Types within a nominated table/column. Currently the function creates geometry types based on the OGC Geometry Type hierarchy such that LineString/CircularString/CompoundCurve geometries are represented by a single LINESTRING type (similarly for polygons).
This function can be easily modified to create individual types eg CircularString.

qGIS Access

To enable access to this table by qGIS one needs to tick the following entries in the Database Connection dialog:

• Only look in the geometry_columns metadata table.
• Use layer extent from geometry_columns table.
• Use primary key from geometry_columns table.

Once saved and dismissed, pressing Connect on the Data Source Manager dialog will cause qGIS to access the records in the GEOMETRY_COLUMNS table.

NOTE: The performance difference between using GEOMETRY_COLUMNS or querying the SQL Server metadata and individual tables (to extract extent, geometry type etc) can be substantial especially where there are a lot of large tables being accessed. Additionally the performance hit is repeated every time the Connection is refreshed.

The two downsides for using GEOMETRY_COLUMNS are:

• If the spatial extent of the data changes one has to remember to update the GEOMETRY_COLUMNS entry.
• GEOMETRY_COLUMNS entries need to be created and maintained: this is made easier if one uses my TSQL functions for SQL Server.

Conclusion

Use of the GEOMETRY_COLUMNS metadata view is highly recommended, with greatly improved metadata access performance for all qGIS clients connecting to the database.

Creation and maintenance of this table is fairly straightforward with TSQL functions being made available via the shop on this website.