6

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?

  • 1
    The title doesn't appear to relate to the question - you might care to edit it. – BradHards Sep 16 '13 at 1:24
  • @BradHards Changed it ! – kinkajou Sep 16 '13 at 1:51
3

EDIT: Sorry, I didn't read your whole question. 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.

  • 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 '13 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 '13 at 5:38
  • Does coord_dimension refer to point/line/polygon 0,1,2 ? – kinkajou Sep 16 '13 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 '13 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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