|
Checking is a column in a table or a view is of type geometry or geography
Monday November 23 2009 at 20: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.
        
|
SpatialDB Advisor
Home
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 · 25 November 2009, 02: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, 12: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 · 26 November 2009, 00: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, 12:42 · #