We are using QGIS 1.8 and Sqlserver 2008.Following http://hub.qgis.org/issues/8525 solution we have created table geometry_columns. since we already have lots of table how do I fill this table geometry_columns? Is there any tool?
I started by making table as mention in the bug post(geometry_columns):
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
)
Now some TSQL to insert data into geometry_columns :
DECLARE @RowsToProcess INT
DECLARE @CurrentRow INT
DECLARE @SelectCol_schema_name NVARCHAR(100)
DECLARE @SelectCol_table_name NVARCHAR(100)
DECLARE @run_sql NVARCHAR(max)
DECLARE @run_update NVARCHAR(max)
DECLARE @Selected_geometry_type NVARCHAR(100)
DELETE FROM [dbo].[geometry_columns]
CREATE TABLE #spatialTables (
RowID INT NOT NULL PRIMARY KEY identity(1, 1)
,f_table_catalog NVARCHAR(128) NOT NULL
,f_table_schema NVARCHAR(100)
,f_table_name NVARCHAR(256) NOT NULL
,f_geometry_column NVARCHAR(256) NOT NULL
,coord_dimension INT NOT NULL
,[srid] [int] NOT NULL
,[geometry_type] [varchar](30) NOT NULL
)
INSERT INTO #spatialTables
SELECT DISTINCT c.TABLE_CATALOG
,c.TABLE_SCHEMA AS TABLE_SCHEMA
,c.TABLE_NAME AS TABLE_NAME
,c.COLUMN_NAME AS COLUMN_NAME
,2 AS coord_dimension
,4326
,'nu'
FROM information_schema.columns c
JOIN information_schema.tables t ON c.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_TYPE IN (
'BASE TABLE'
,'VIEW'
)
WHERE c.DATA_TYPE = 'geometry'
ORDER BY c.TABLE_SCHEMA
,c.TABLE_NAME
SET @RowsToProcess = @@ROWCOUNT
SET @CurrentRow = 0
WHILE @CurrentRow < @RowsToProcess
BEGIN
SET @CurrentRow = @CurrentRow + 1
SELECT @SelectCol_schema_name = f_table_schema
,@SelectCol_table_name = f_table_name
FROM #spatialTables
WHERE RowID = @CurrentRow
SET @run_sql = 'select TOP 1 @table_geometry_type= SP_GEOMETRY.STGeometryType() from ' + @SelectCol_schema_name + '.' + @SelectCol_table_name
EXECUTE sp_executesql @run_sql
,N'@table_geometry_type nvarchar(100) OUTPUT'
,@table_geometry_type = @Selected_geometry_type OUTPUT
--print @Selected_geometry_type
--SET @run_update = 'update #spatialTables set [geometry_type] = ''' + @Selected_geometry_type + ''' where [f_table_name]=''' + @SelectCol_table_name + ''''
set @run_update='update #spatialTables set [geometry_type] = '''+@Selected_geometry_type+
''',coord_dimension = '+
'case
when '''+@Selected_geometry_type +'''= ''Point''
Then 0
when '''+@Selected_geometry_type +'''= ''LineString''
Then 1
Else 2
END'
+' where [f_table_name]='''+@SelectCol_table_name+''''
EXECUTE sp_executesql @run_update
--PRINT @run_update
END
INSERT INTO [dbo].[geometry_columns]
SELECT [f_table_catalog]
,[f_table_schema]
,[f_table_name]
,[f_geometry_column]
,[coord_dimension]
,[srid]
,[geometry_type]
FROM #spatialTables
DROP TABLE #spatialTables
Note :
- I got first five columns with ease querying information_schema
- But to get [coord_dimension] , [srid] , [geometry_type] I had to do some loop.
- I think coord_dimension is 0,1,2 from point,Line,Polygon
So, here is the question:
- A. I have no performance boost it takes same time.Is there any
mistake?
–> I got the performance boost. I need to select only look in geometry_columns when creating MSSQL Connection. - B. SQL server has MultiLineString Data type if 3 is
correct then what is it's cood_dimension value?
So, my problem here is I am not about to find information update geometry_type. How do I get geometry type information without quering any specific tables geometry column. Is there any tool to generate this table in QGIS for sqlserver?
Best Answer
I have a script that I use to populate the table, which works, kinda, mostly... but I'm not allowed to post it...
But, it uses the Top 1 from each table, and uses this: SP_GEOMETRY.STGeometryType() to get the geometry type of one entry.
Here's what I use, which works:
and I have entries like this:
QGIS can read these and gives me a list of our ~50 layers instantly.
I also added my SRID to another table spatial_ref_sys:
entry like this:
Note that you can get these from spatialreference.org, like this: http://spatialreference.org/ref/epsg/28355/postgis/, just modify it for SQL Server syntax.