Spatial SQL -> MS-SQL. Find record with corrupted geometry

This script shows how you can find corrupted geometry in existing spatial table. It is sometime not an easy task. Your table can store broken geometry (polygon or polyline) in some record, but regular select expression will raise Exception, and will leave you unaware about identity of the problematic record. In the following example table records are scaned one by one, corrupted are found and their id numbers are written into Errors Log 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].[ERROR_Geom]') AND type IN (N'U'))
DROP TABLE [dbo].[ERROR_Geom];

CREATE TABLE [dbo].[ERROR_Geom](
  id [int] NULL,
  errtext Varchar(2000) NULL
) ON [PRIMARY];


/* run over spatialized table. touch it's geometry. invalid geo object will raise Error */
DECLARE qa_cursor CURSOR FOR
  SELECT id, geom
  FROM test_polygons;

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

WHILE (@@FETCH_STATUS <> -1)
BEGIN

BEGIN TRY

  SET @n = @g.STNumGeometries();

  IF (@g.STIsEmpty()=1) OR (@g IS NULL)
  BEGIN
   INSERT INTO dbo.ERROR_Geom (id, errtext)
   VALUES (@id, 'Empty Object!');
  END

  END TRY

  -- error catch block
  BEGIN CATCH

    INSERT INTO dbo.ERROR_Geom (id, errtext)
    VALUES (@id, ERROR_MESSAGE());

  END CATCH;

  FETCH NEXT FROM qa_cursor INTO @id, @g;
END

-- release data structures that was allocated by cursor
CLOSE qa_cursor;
DEALLOCATE qa_cursor;





sqlexamples.info