5

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 :

  1. I got first five columns with ease querying information_schema
  2. But to get [coord_dimension] , [srid] , [geometry_type] I had to do some loop.
  3. 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?

0

2 Answers 2

2

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:

CREATE TABLE [dbo].[geometry_columns](
[f_table_catalog] [varchar](50) NULL,
[f_table_schema] [varchar](50) NULL,
[f_table_name] [varchar](100) NULL,
[f_geometry_column] [varchar](50) NULL,
[coord_dimension] [int] NULL,
[srid] [int] NULL,
[geometry_type] [varchar](50) NULL
)

and I have entries like this:

f_table_catalog f_table_schema  f_table_name    f_geometry_column   coord_dimension srid    geometry_type
GISDB   dbo Accuracy    SP_GEOMETRY 1   28355   LineString
GISDB   dbo AssetAreas  SP_GEOMETRY 2   28355   Polygon

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:

CREATE TABLE [dbo].[spatial_ref_sys](
[srid] [int] NOT NULL,
[auth_name] [varchar](256) NULL,
[auth_srid] [int] NULL,
[srtext] [varchar](2048) NULL,
[proj4text] [varchar](2048) NULL
) ON [PRIMARY]

entry like this:

srid    auth_name   auth_srid   srtext  proj4text
28355   epsg    28355   +proj=utm +zone=55 +south +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs     PROJCS["GDA94 / MGA zone 55",GEOGCS["GDA94",DATUM["Geocentric_Datum_of_Australia_1994",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6283"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4283"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",147],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",10000000],AUTHORITY["EPSG","28355"],AXIS["Easting",EAST],AXIS["Northing",NORTH]]

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.

4
  • you have left srid and geometry type column null. Also,the link you mentioned spatialreference.org/ref/epsg/28355/postgis inserts data manually. Is the data value always same? Is there any way I could generate from within database?
    – kinkajou
    Sep 16, 2013 at 2:13
  • No I didn't. There's 7 columns, 7 entries. The link I mentioned is for adding an entry to the spatial_ref_sys table, which only needs one entry for each SRS you use. That data stays the same. The entries in geometry_columns can be generated from the database, but not easily. I wanted to set up a view to do it automatically... but didn't get there.
    – Alex Leith
    Sep 16, 2013 at 5:38
  • Does coord_dimension refer to point/line/polygon 0,1,2 ?
    – kinkajou
    Sep 16, 2013 at 7:27
  • "coord_dimension and srid define the the dimension of the geometry (2-, 3- or 4-dimensional) and the Spatial Reference system identifier that refers to the spatial_ref_sys table respectively" From here: workshops.opengeo.org/postgis-intro/geometries.html
    – Alex Leith
    Sep 16, 2013 at 22:14
0

This post on implementing geometry_columns table in SQL Server for QGIS originally from 2013 helped me a lot. But filling the geometry_columns did not work for me. Info on tables with same name in different schemes and having more than one geometry column is not correctly generated and the SRID, needed for showing in QGIS is not part of the code.

The link to get the projection data for dbo.spatial_ref_sys can be adjusted to get the right projection. In my case for the Netherlands: https://spatialreference.org/ref/epsg/28992/postgis/

I adjusted the TSQL to insert data into geometry_columns (made a sp):

DROP PROCEDURE IF EXISTS dbo.fill_geometry_columns
GO
CREATE PROCEDURE dbo.fill_geometry_columns AS 

DECLARE @RowsToProcess INT
DECLARE @CurrentRow INT
DECLARE @SelectCol_schema_name NVARCHAR(100)
DECLARE @SelectCol_table_name NVARCHAR(100)
DECLARE @SelectCol_column_name NVARCHAR(100)
DECLARE @run_sql1 NVARCHAR(max)
DECLARE @run_sql2 NVARCHAR(max)
DECLARE @run_update1 NVARCHAR(max)
DECLARE @run_update2 NVARCHAR(max)
DECLARE @Selected_geometry_type NVARCHAR(100)
DECLARE @Selected_geometry_srid 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
        ,@SelectCol_column_name = f_geometry_column
    FROM #spatialTables
    WHERE RowID = @CurrentRow

    SET @run_sql1 = 'SELECT TOP 1 @table_geometry_type= '+ @SelectCol_column_name + '.STGeometryType() FROM ' + @SelectCol_schema_name + '.' + @SelectCol_table_name + ' WHERE '+ @SelectCol_column_name + ' IS NOT NULL'

    EXECUTE sp_executesql @run_sql1
        ,N'@table_geometry_type nvarchar(100) OUTPUT'
        ,@table_geometry_type = @Selected_geometry_type OUTPUT
    
    print @SelectCol_table_name + '  ' + @SelectCol_column_name + ' ' + @Selected_geometry_type

    --SET @run_update1 = 'update #spatialTables  set [geometry_type] = ''' + @Selected_geometry_type + ''' where [f_table_name]=''' + @SelectCol_table_name + ''''
     
    set @run_update1='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+''' and [f_geometry_column]=''' + @SelectCol_column_name+''' and [f_table_schema]=''' + @SelectCol_schema_name + ''''
    
      EXECUTE sp_executesql @run_update1 
     
SET @run_sql2 = 'select TOP 1 @table_geometry_srid= '+ @SelectCol_column_name + '.STSrid from ' + @SelectCol_schema_name + '.' + @SelectCol_table_name + ' WHERE '+ @SelectCol_column_name + ' IS NOT NULL'

    EXECUTE sp_executesql @run_sql2
        ,N'@table_geometry_srid nvarchar(100) OUTPUT'
        ,@table_geometry_srid = @Selected_geometry_srid OUTPUT
    
   print @SelectCol_table_name + ' '+ @SelectCol_column_name + ' ' +  @Selected_geometry_srid

    SET @run_update2 = 'update #spatialTables  set [srid] = ''' + @Selected_geometry_srid + ''' where [f_table_name]=''' + @SelectCol_table_name + ''' and [f_geometry_column]=''' + @SelectCol_column_name+''' and [f_table_schema]=''' + @SelectCol_schema_name + ''''

    EXECUTE sp_executesql @run_update2

    --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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.