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
|