· 8 years ago · Jul 19, 2018, 03:22 PM
1-- =============================================
2-- Author: Chris Tippett
3-- Create date: 2014-08-12
4-- Description: Detect columns with geometry datatypes and add them to [dbo].[geometry_columns]
5-- =============================================
6CREATE PROCEDURE [dbo].[Populate_Geometry_Columns] @schema VARCHAR(MAX) = '', @table VARCHAR(MAX) = ''
7AS
8BEGIN
9 SET NOCOUNT ON;
10
11 DECLARE
12 @db_name VARCHAR(MAX)
13 ,@tbl_schema VARCHAR(MAX)
14 ,@tbl_name VARCHAR(MAX)
15 ,@tbl_oldname VARCHAR(MAX)
16 ,@clm_name VARCHAR(MAX)
17 ,@geom_srid INT
18 ,@geom_type VARCHAR(MAX)
19 ,@msg VARCHAR(MAX)
20
21 SET @msg = '--------------------------------------------------'+CHAR(10)
22 SET @msg += 'FINDING GEOMETRY DATATYPES'
23 RAISERROR(@msg,0,1) WITH NOWAIT
24
25 -- check whether [dbo].[geometry_columns] exists and create it if necessary
26 SET @msg = ' > Checking whether table [dbo].[geometry_columns] exists'
27 RAISERROR(@msg,0,1) WITH NOWAIT
28
29 IF OBJECT_ID(DB_NAME()+'.dbo.geometry_columns') IS NULL
30 BEGIN
31 SET @msg = ' - Table does not exist, creating it now'
32
33 CREATE TABLE [dbo].[geometry_columns] (
34 [f_table_catalog] [varchar](128) NOT NULL
35 ,[f_table_schema] [varchar](128) NOT NULL
36 ,[f_table_name] [varchar](256) NOT NULL
37 ,[f_geometry_column] [varchar](256) NOT NULL
38 ,[coord_dimension] [int] NOT NULL
39 ,[srid] [int] NOT NULL
40 ,[geometry_type] [varchar](30) NOT NULL
41 CONSTRAINT [geometry_columns_pk] PRIMARY KEY CLUSTERED (
42 [f_table_catalog] ASC
43 ,[f_table_schema] ASC
44 ,[f_table_name] ASC
45 ,[f_geometry_column] ASC
46 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
47 ) ON [PRIMARY]
48 END
49 ELSE
50 SET @msg = ' - Table already exists, no further action necessary'
51
52 RAISERROR(@msg,0,1) WITH NOWAIT
53
54 SET @schema = NULLIF(@schema,'')
55 SET @table = NULLIF(@table,'')
56
57 -- setup temporary table to contain the SRID and type of geometry
58 CREATE TABLE #geom_info (SRID INT, GEOM_TYPE VARCHAR(50), Count_Type INT)
59
60 DECLARE column_cursor CURSOR FOR
61 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
62 FROM INFORMATION_SCHEMA.COLUMNS
63 WHERE
64 DATA_TYPE = 'geometry'
65 AND TABLE_CATALOG = DB_NAME()
66 AND TABLE_SCHEMA LIKE COALESCE(@schema,'%')
67 AND TABLE_NAME LIKE COALESCE(@table,'%')
68 ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
69
70 OPEN column_cursor
71 FETCH NEXT FROM column_cursor INTO @db_name, @tbl_schema, @tbl_name, @clm_name
72
73 SET @msg = ' > Searching ['+@db_name+'].['+@tbl_schema+'].['+@tbl_name+'] for geometry columns'
74 RAISERROR(@msg,0,1) WITH NOWAIT
75
76 IF @@FETCH_STATUS < 0
77 BEGIN
78 SET @msg = ' - No columns with geometry datatype found'
79 RAISERROR(@msg,0,1) WITH NOWAIT
80 END
81
82 WHILE @@FETCH_STATUS = 0
83 BEGIN
84
85 -- check whether column exists already in [geometry_columns]
86 IF EXISTS (
87 SELECT 1
88 FROM dbo.geometry_columns
89 WHERE
90 [f_table_catalog] = @db_name AND
91 [f_table_schema] = @tbl_schema AND
92 [f_table_name] = @tbl_name AND
93 [f_geometry_column] = @clm_name
94 )
95 BEGIN
96 SET @msg = ' - Geometry column "'+@clm_name+'" found and already exists in geometry_columns table'
97 RAISERROR(@msg,0,1) WITH NOWAIT
98
99 END
100
101 ELSE
102 BEGIN
103 -- use dynamic sql to get srid and geometry type
104 INSERT INTO
105 #geom_info
106 EXEC('
107 SELECT
108 '+@clm_name+'.STSrid AS SRID
109 ,'+@clm_name+'.MakeValid().STGeometryType() AS GEOM_TYPE
110 ,COUNT(*) AS Count_Type
111 FROM
112 '+@db_name+'.'+@tbl_schema+'.'+@tbl_name+'
113 WHERE
114 '+@clm_name+'.STIsValid() = 1
115 GROUP BY
116 '+@clm_name+'.STSrid
117 ,'+@clm_name+'.MakeValid().STGeometryType()
118 ')
119
120 IF @@ROWCOUNT > 1
121 BEGIN
122 SET @msg = ' - WARNING: More than 1 geometry type detected in column. Taking most frequent type for column definition'
123 RAISERROR(@msg,0,1) WITH NOWAIT
124 END
125
126 -- assign srid and geometry type to variables
127 SELECT TOP 1
128 @geom_srid = SRID
129 ,@geom_type = UPPER(GEOM_TYPE)
130 FROM
131 #geom_info
132 ORDER BY
133 Count_Type DESC
134
135 -- reset @geom_info contents
136 DELETE FROM #geom_info
137
138 -- insert into [geometry_columns] if the column doesn't already exist
139 SET @msg = ' - Adding column "'+@clm_name+'" to geometry_columns table'+CHAR(10)
140 SET @msg += ' + geometry type: '+@geom_type+CHAR(10)
141 SET @msg += ' + srid: '+CAST(@geom_srid AS VARCHAR(10))
142 RAISERROR(@msg,0,1)
143
144 INSERT INTO dbo.geometry_columns
145 VALUES (@db_name, @tbl_schema, @tbl_name, @clm_name, 2, @geom_srid, @geom_type)
146
147 END
148
149 -- iterate cursor
150 FETCH NEXT FROM column_cursor INTO @db_name, @tbl_schema, @tbl_name, @clm_name
151
152 -- check whether the cursor is looping through another column of the previous table (purely for messaging purposes)
153 IF @tbl_name <> @tbl_oldname
154 BEGIN
155 SET @msg = ' > Searching ['+@db_name+'].['+@tbl_schema+'].['+@tbl_name+'] for geometry columns'
156 RAISERROR(@msg,0,1) WITH NOWAIT
157 END
158 SET @tbl_oldname = @tbl_name
159
160 END
161
162 CLOSE column_cursor
163 DEALLOCATE column_cursor
164
165 SET @msg = '--------------------------------------------------'+CHAR(10)
166 SET @msg += 'Done!'+CHAR(10)
167 SET @msg += '--------------------------------------------------'
168 RAISERROR(@msg,0,1) WITH NOWAIT
169END