Automating geometry_columns extents

I think this sets a record for becoming irrelevant in the shortest space of time… the most recent QGIS 2.0 dev-build has been changed so that it automtically calculates the extents of datasets in the geometry_columns table without the need for the extra columns. The dangers of dealing with actively developed open source!

Having populated min_x, min_y, max_x and max_y columns (all int/null) in the geometry_columns table in SQL Server (automatically created when using ogr2ogr) creates huge time savings when loading SQL Server tables into QGIS 2.0.

To add these column to geometry_columns, use the following SQL:

ALTER geometry_columns
ADD min_x int null,
min_y int null,
max_x int null,
max_y int null

The stored procedure below automatically populates the extent values of all records in the geometry_columns table. It is currently set to only populate records with a particular SRID. Either change the SRID to the appropriate value or remove altogether:

CREATE PROCEDURE usp_extentsUpdate

AS

DECLARE @tablename VARCHAR(MAX)
DECLARE @srid INT = 28355

BEGIN

SET NOCOUNT ON;
DECLARE extents_cursor CURSOR FOR
SELECT f_table_name
FROM geometry_columns
WHERE srid = @srid

OPEN extents_cursor;

FETCH NEXT FROM extents_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = ‘update geometry_columns set min_x = a.minx, min_y = a.miny, max_x = a.maxx, max_y = a.maxy from (select ‘ + ”” + @tablename + ”” + ‘ as table_name, MIN(ogr_geometry.STPointN(1).STX) as minx, MIN(ogr_geometry.STPointN(1).STY) as miny, MAX(ogr_geometry.STPointN(1).STX) as maxx, MAX(ogr_geometry.STPointN(1).STY) as maxy from ‘ + @tablename + ‘) as a where f_table_name = a.table_name’

EXEC (@sql)

SELECT @tablename + ‘ completed’

FETCH NEXT FROM extents_cursor
INTO @tablename
END
CLOSE extents_cursor
DEALLOCATE extents_cursor
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s