Spatial SQL -> MS-SQL. Find invalid geometry in given Spatial table

Spatial method for finding Invalid (corrupted) geometry for MS-SQL Spatial datatype is STIsValid(). In some cases you will allso need to use Error Handler in order to Trap SQL-Server Exception caused by processing Invalid Geometry Object. The following example shows both possibilities.

USE milano
GO

-- ----------------------------------------------------
--   Find invalid geometry in given Spatial table
-- ----------------------------------------------------

DECLARE @id INT, @n INT, @g GEOMETRY;

-- create error log table
IF EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[ERR_CorruptedGeom]') AND type in (N'U'))
DROP TABLE [dbo].[ERR_CorruptedGeom];

CREATE TABLE [dbo].[ERR_CorruptedGeom](
  recordid [int] NULL,
  table_name Varchar(25) NULL,
  errtext Varchar(500) NULL
) ON [PRIMARY];

-- define QA cursor on spatial table
DECLARE qa_cursor CURSOR FOR
  SELECT recordid, geom
  FROM Buildings1;

OPEN qa_cursor -- open cursor
FETCH NEXT FROM qa_cursor INTO @id, @g;

-- run over table records from the begining to the end
WHILE (@@FETCH_STATUS <> -1)
BEGIN

BEGIN TRY


  -- conventional way to find invalid geometry
  IF @g.STIsValid()=0
  BEGIN
   -- insert problematic record number into Log table
   INSERT INTO dbo.ERR_CorruptedGeom (recordid, table_name, errtext)
   VALUES (@id, 'Buildings1', 'Invalid Geometry');
  END

  -- trying to read invalid geometry will raise Error
  SET @n = @g.STNumGeometries();

END TRY

-- error catch block
BEGIN CATCH

  -- insert problematic record reference to Log table
  INSERT INTO dbo.ERR_CorruptedGeom (recordid, table_name, errtext)
  VALUES (@id, 'Buildings1', ERROR_MESSAGE());

END CATCH;

FETCH NEXT FROM qa_cursor INTO @id, @g;

END

-- close cursor
CLOSE qa_cursor;
DEALLOCATE qa_cursor;


-- see results
select * from ERR_CorruptedGeom




sqlexamples.info