Go to content Go to navigation and search

Home

Current SQL Server Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Checking is a column in a table or a view is of type geometry or geography

Monday November 23 2009 at 09:45

Sometimes, some things are simple and prosaic rather than clever and complicated.

Building solutions in TSQL requires one to build a prior collection of handy functions before one can be really productive.

Here is a function I use called isGeo which if provided the name of a table/view and a column name returns 1 (true) if the column exists and is of type geometry or geography.

USE [GISDB]  /* Replace this with the name of your database */
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[isGeo] (schema_name nvarchar(128), 
                               object_name nvarchar(128), 
                               column_name nvarchar(128))
RETURNS int
WITH EXECUTE AS CALLER, 
     RETURNS NULL ON NULL INPUT
AS
BEGIN
  DECLARE v_result int;
        SELECT v_result=1
		  FROM (SELECT sc.name as schema_name,
					   ta.name as object_name,
					   co.name as column_name,
					   ty.name as column_type,
					   'TABLE' as object_type
				  FROM sys.tables ta
					   JOIN sys.schemas sc ON sc.schema_id = ta.schema_id 
					   JOIN sys.columns co ON ta.object_id = co.object_id
					   JOIN sys.types ty   ON co.user_type_id = ty.user_type_id
				 WHERE ty.name IN ('geography','geometry')
				 UNION ALL
				SELECT sc.name as schema_name,
					   va.name as object_name,
					   co.name as column_name,
					   ty.name as column_type,
					   'VIEW' as object_type
				  FROM sys.views va 
					   JOIN sys.schemas sc ON sc.schema_id = va.schema_id
					   JOIN sys.columns co ON va.object_id = co.object_id
					   JOIN sys.types ty   ON co.user_type_id = ty.user_type_id
				 WHERE ty.name IN ('geography','geometry')
				) a
			WHERE a.schema_name = schema_name
			  AND a.object_name = object_name
			  AND a.column_name = column_name ;
	Return case when v_result is null then 0 else v_result end;
END;

Now, some tests…

-- Check an existing geometry column in a physical table
select dbo.isGeo('dbo','ADMIN','GEOM') as Result;
Result
1
-- Check an existing geography column in a View
select dbo.isGeo('dbo','vw_terminal','shape') as Result;
Result
1
-- Try again with a non-existing schema
select dbo.isGeo('simon','ADMIN','GEOM') as Result;
Result
0
-- Check action of "RETURNS NULL ON NULL INPUT" function option
select dbo.isGeo(NULL,'ADMIN','GEOM') as Result;
Result
NULL
-- Check again with connected schema provided by appropriate function
select dbo.isGeo(SCHEMA_NAME(),'ADMIN','GEOM') as Result;
Result
1

I hope this helps someone.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [4]

Information_Schema nazi at your disposal :)

Simon — what’s wrong with doing this instead of all that messy JOIN stuff?

SELECT table_schema, column_name,data_type FROM information_schema.columns
WHERE DATA_TYPE IN(‘geometry’, ‘geography’);

It will catch both tables and views.

Regina · 24 November 2009, 15:36 · #

Regina,

Of course you are perfectly right to suggest use of Information_Schema. And, yes, it is more succinct.

Still, I didn’t know that geometry and geography were data types from the Information_Schema SQL standard!

regards
Simon

Simon · 25 November 2009, 01:20 · #

I think the standard says you have to list the data_type and for standard datatypes you have to call it a specific name even if you don’t call it that natively. Actually not sure it even says that. Anyrate I think most databases that support the information schema — always list the data type even if its specific to that brand of db.

MySQL — there is only one information_schema — so you have to be a bit careful — as their schema is really the database (much like Oracle :) guess another reason why Oracle and MySQL are a good pair) — so their catalog field is blank but the same query as above will work in MySQL if you include table_schema = database_name

PostgreSQL / PostGIS doesn’t quite work without more change. Reason is PostGIS is not a built in datatype.

So — the data_type field contains a useless ‘USER-DEFINED

and the real field you want to query is the udt_name (which like for varchar will have varchar (instead of character varying — which varchar is nicer anyway)

So your equivalent query in PostGIS would be

SELECT table_schema, table_name, column_name, udt_name As data_type
FROM information_schema.columns
WHERE udt_name IN(‘geometry’, ‘geography’);

Still pretty close though :)

Regina · 25 November 2009, 13:22 · #

Regina,

The udt_name field is only available in PostgreSQL and not SQL Server 2008.

I have used both the SQL Server 2008 proprietary catalog and Information_Schema in coding other functions so I am not against using it here.

In summary, I don’t disagree with using the shorter Information_Schema based approach. The physical implementation details are hidden from the user via use of the isGeo() function anyway.

regards
Simon

Simon · 27 November 2009, 01:42 · #