2

Is there an alternative method that I can use to get a detailed message on why a Geometry is invalid?

Geometry.IsValidDetailed() is not available in SQL Server 2008 R2, so I need a work around to get this message.

Here is an example:

Using SQL Server 2008 R2 (I am on Service Pack 2).

Create this table:

CREATE TABLE [dbo].[GeoTests](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](50) NULL,
    [Geo] [geometry] NULL
)

Insert this invalid polygon into the table:

INSERT INTO GeoTests (
        Value, 
        Geo
    ) VALUES (
        'Polygon', 
        Geometry::STGeomFromText('POLYGON((99 99 0, 99 100 0, 100 100 0, 99 100 0, 98 100 0, 99 99 0))', 0)
    )

You can tell the polygon is invalid using this:

SELECT Geo.STIsValid() FROM GeoTests

This is what I have so far for selecting the error message:

BEGIN TRY
    SELECT TOP 1000 [Id]
        ,[Value]
        ,[Geo].STGeometryType()
    FROM [Tests].[dbo].[
END TRY 
BEGIN CATCH
    PRINT ERROR_MESSAGE()
    PRINT ERROR_NUMBER()
END CATCH

The problem is that the Error_Message() is generic and just shows that the Geometry is invalid, not any detail as to why.

Is there any way while using SQL Server 2008 R2 to get a similar error to Geo.IsValidDetailed() a function which was implemented in SQL Server 2012?

Kyle B
  • 2,328
  • 1
  • 23
  • 39

0 Answers0