-
-
Save christippett/9fd0a4b8e6d01b2c34395103ae1c4d66 to your computer and use it in GitHub Desktop.
-- ============================================= | |
-- Author: Chris Tippett | |
-- Create date: 2014-08-12 | |
-- Description: Detect columns with geometry datatypes and add them to [dbo].[geometry_columns] | |
-- ============================================= | |
CREATE PROCEDURE [dbo].[Populate_Geometry_Columns] @schema VARCHAR(MAX) = '', @table VARCHAR(MAX) = '' | |
AS | |
BEGIN | |
SET NOCOUNT ON; | |
DECLARE | |
@db_name VARCHAR(MAX) | |
,@tbl_schema VARCHAR(MAX) | |
,@tbl_name VARCHAR(MAX) | |
,@tbl_oldname VARCHAR(MAX) | |
,@clm_name VARCHAR(MAX) | |
,@geom_srid INT | |
,@geom_type VARCHAR(MAX) | |
,@msg VARCHAR(MAX) | |
SET @msg = '--------------------------------------------------'+CHAR(10) | |
SET @msg += 'FINDING GEOMETRY DATATYPES' | |
RAISERROR(@msg,0,1) WITH NOWAIT | |
-- check whether [dbo].[geometry_columns] exists and create it if necessary | |
SET @msg = ' > Checking whether table [dbo].[geometry_columns] exists' | |
RAISERROR(@msg,0,1) WITH NOWAIT | |
IF OBJECT_ID(DB_NAME()+'.dbo.geometry_columns') IS NULL | |
BEGIN | |
SET @msg = ' - Table does not exist, creating it now' | |
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 | |
CONSTRAINT [geometry_columns_pk] PRIMARY KEY CLUSTERED ( | |
[f_table_catalog] ASC | |
,[f_table_schema] ASC | |
,[f_table_name] ASC | |
,[f_geometry_column] ASC | |
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
) ON [PRIMARY] | |
END | |
ELSE | |
SET @msg = ' - Table already exists, no further action necessary' | |
RAISERROR(@msg,0,1) WITH NOWAIT | |
SET @schema = NULLIF(@schema,'') | |
SET @table = NULLIF(@table,'') | |
-- setup temporary table to contain the SRID and type of geometry | |
CREATE TABLE #geom_info (SRID INT, GEOM_TYPE VARCHAR(50), Count_Type INT) | |
DECLARE column_cursor CURSOR FOR | |
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME | |
FROM INFORMATION_SCHEMA.COLUMNS | |
WHERE | |
DATA_TYPE = 'geometry' | |
AND TABLE_CATALOG = DB_NAME() | |
AND TABLE_SCHEMA LIKE COALESCE(@schema,'%') | |
AND TABLE_NAME LIKE COALESCE(@table,'%') | |
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME | |
OPEN column_cursor | |
FETCH NEXT FROM column_cursor INTO @db_name, @tbl_schema, @tbl_name, @clm_name | |
SET @msg = ' > Searching ['+@db_name+'].['+@tbl_schema+'].['+@tbl_name+'] for geometry columns' | |
RAISERROR(@msg,0,1) WITH NOWAIT | |
IF @@FETCH_STATUS < 0 | |
BEGIN | |
SET @msg = ' - No columns with geometry datatype found' | |
RAISERROR(@msg,0,1) WITH NOWAIT | |
END | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
-- check whether column exists already in [geometry_columns] | |
IF EXISTS ( | |
SELECT 1 | |
FROM dbo.geometry_columns | |
WHERE | |
[f_table_catalog] = @db_name AND | |
[f_table_schema] = @tbl_schema AND | |
[f_table_name] = @tbl_name AND | |
[f_geometry_column] = @clm_name | |
) | |
BEGIN | |
SET @msg = ' - Geometry column "'+@clm_name+'" found and already exists in geometry_columns table' | |
RAISERROR(@msg,0,1) WITH NOWAIT | |
END | |
ELSE | |
BEGIN | |
-- use dynamic sql to get srid and geometry type | |
INSERT INTO | |
#geom_info | |
EXEC(' | |
SELECT | |
['+@clm_name+'].STSrid AS SRID | |
,['+@clm_name+'].MakeValid().STGeometryType() AS GEOM_TYPE | |
,COUNT(*) AS Count_Type | |
FROM | |
['+@db_name+'].['+@tbl_schema+'].['+@tbl_name+'] | |
WHERE | |
['+@clm_name+'].STIsValid() = 1 | |
GROUP BY | |
['+@clm_name+'].STSrid | |
,['+@clm_name+'].MakeValid().STGeometryType() | |
') | |
IF @@ROWCOUNT > 1 | |
BEGIN | |
SET @msg = ' - WARNING: More than 1 geometry type detected in column. Taking most frequent type for column definition' | |
RAISERROR(@msg,0,1) WITH NOWAIT | |
END | |
-- assign srid and geometry type to variables | |
SELECT TOP 1 | |
@geom_srid = SRID | |
,@geom_type = UPPER(GEOM_TYPE) | |
FROM | |
#geom_info | |
ORDER BY | |
Count_Type DESC | |
-- reset @geom_info contents | |
DELETE FROM #geom_info | |
-- insert into [geometry_columns] if the column doesn't already exist | |
SET @msg = ' - Adding column "'+@clm_name+'" to geometry_columns table'+CHAR(10) | |
SET @msg += ' + geometry type: '+@geom_type+CHAR(10) | |
SET @msg += ' + srid: '+CAST(@geom_srid AS VARCHAR(10)) | |
RAISERROR(@msg,0,1) | |
INSERT INTO dbo.geometry_columns | |
VALUES (@db_name, @tbl_schema, @tbl_name, @clm_name, 2, @geom_srid, @geom_type) | |
END | |
-- iterate cursor | |
FETCH NEXT FROM column_cursor INTO @db_name, @tbl_schema, @tbl_name, @clm_name | |
-- check whether the cursor is looping through another column of the previous table (purely for messaging purposes) | |
IF @tbl_name <> @tbl_oldname | |
BEGIN | |
SET @msg = ' > Searching ['+@db_name+'].['+@tbl_schema+'].['+@tbl_name+'] for geometry columns' | |
RAISERROR(@msg,0,1) WITH NOWAIT | |
END | |
SET @tbl_oldname = @tbl_name | |
END | |
CLOSE column_cursor | |
DEALLOCATE column_cursor | |
SET @msg = '--------------------------------------------------'+CHAR(10) | |
SET @msg += 'Done!'+CHAR(10) | |
SET @msg += '--------------------------------------------------' | |
RAISERROR(@msg,0,1) WITH NOWAIT | |
END |
This is great. Thanks!
This is great. Thanks!
A blast from the past! You're very welcome, glad it could be of some help.
Sweet! I was about to create a similar script and this saves me a lot of time. Thanks for posting it!
EDIT: I made a minor revision in a fork, consider updating your Gist if you like it: https://gist.github.com/MattiasSp/d4527ea708d432bd1eb926fb7e029291/revisions#diff-ab1a43887de43b5bfb059cb689cc75d4
Thanks @MattiasSp, I've incorporated your changes :)
Hi, thanks for the code! I have a question regarding your script. I am newby in QGIS + MS SQL, I am trying to execute your script with right click on connected DB, then "execute SQL" but after copy pasting and running your code in the SQL window nothing happens. I do not see the geometry_columns table in the dbo schema. Any idea what am I doing wrong? the code itself runs OK without any errors.
@MaciejSzypulski, the included code creates a stored procedure, you need to execute the stored procedure for it to create and populate the geometry_columns
table.
Try running this SQL snippet: EXEC [dbo].[Populate_Geometry_Columns];
Sweet! I was about to create a similar script and this saves me a lot of time. Thanks for posting it!
EDIT: I made a minor revision in a fork, consider updating your Gist if you like it: https://gist.github.com/MattiasSp/d4527ea708d432bd1eb926fb7e029291/revisions#diff-ab1a43887de43b5bfb059cb689cc75d4