February 24, 2016 at 4:55 pm
Folks,
I suspect this is easy but I'm struggling to find a good reference.
I have a table containing a WKT column of lines, points and polygons. I'm processing the WKT into a geography column.
For the most part that's working however I'd like to QA the WKT before processing.
I'm running into trouble here.
The simplest thing I tried was
select * from GEOMWKT where geometry::STGeomFromText(GEOG_WKT, 4326).STIsValid() = 0;
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: 24117: The LineString input is not valid because it does not have enough distinct points. A LineString must have at least two distinct points.
However this breaks when there's a genuinely faulty record.
To try and mitigate this I tried the following
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO
BEGIN TRY
select * from GEOMWKT where geometry::STGeomFromText(GEOG_WKT, 4326).STIsValid() = 0;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
While this returns what it can, the error is managed and returned but I still don't know which record it is.
How can I return the ID column or something of the record that's failing?
February 25, 2016 at 3:20 pm
This is an answer to my question using cursors. As I've read we don't like cursors as a rule but this works.
I would prefer the elegance of an Stored Procedure or Function but this gets it done.
Please note: per the original link this uses two approaches to finding invalid geometries. Sometimes you get a duplicate ID sometimes not depending on the failure.
I'm still interested in improvements as I'd like to set up a Stored Procedure to report these things before we do processing on them.
-- ----------------------------------------------------
-- Find invalid geometry in given Spatial table
-- ----------------------------------------------------
DECLARE @id INT, @n INT, @g varchar(max);
-- 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(MAX) NULL
) ON [PRIMARY];
-- define QA cursor on spatial table
DECLARE qa_cursor CURSOR FOR
SELECT GEOMID, GEOG_WKT
FROM dbo.GEOMWKT;
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 geometry::STGeomFromText(@g, 4326).STIsValid()=0
BEGIN
-- insert problematic record number into Log table
INSERT INTO dbo.ERR_CorruptedGeom (recordid, table_name, errtext)
VALUES (@id, 'dbo.GEOMWKT', 'Invalid Geometry');
END
-- trying to read invalid geometry will raise Error
SET @n = geometry::STGeomFromText(@g, 4326).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, 'dbo.GEOMWKT', 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
This is from here http://www.sqlexamples.info/SPAT/mssql_invalid_geometry.htm
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply