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

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

· Nov 23, 08:45 pm by Simon Greener

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.