Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I've got a whole load of WKT data in a SQL Server table, of which I'm trying to detect the validity.

I'm currently doing a query like this:

UPDATE f SET 
    f."CurrentGeomValid" = geometry::STGeomFromText(f."GEOM_CURRENT",29900).STIsValid()
FROM "Feature" f 
WHERE f."CurrentGeomValid" IS NULL;

(Basically updating a column with the geometry validity). I'm hitting an issue when the WKT is completely invalid, for example:

POLYGON ((0 0, 10 10, 0 0) (100 100, 200 100, 200 200, 100 200, 100 100))

The initial loop of this polygon doesn't have enough points and gets me a message like so:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": 
System.FormatException: 24305: The Polygon input is not valid because the ring does not have enough distinct points. Each ring of a polygon must contain at least three distinct points.
System.FormatException: 
   at Microsoft.SqlServer.Types.Validator.Execute(Transition transition)
   at Microsoft.SqlServer.Types.ForwardingGeoDataSink.EndFigure()
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParseLineStringText()
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePolygonText()
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParseMultiPolygonText()
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)

The issue seems to be that the STGeomFromText function is failing to read the invalid geometry so that the STIsValid function can tell me that it's invalid!

I can certainly write some .NET code with an exception handler and process my dataset row by row; I'm wondering if there is a better way of doing this in a single query?

Any idea? Cheers!

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

Here's a stored procedure that will do it, no CLR needed, unfortunately it can't be a function because it uses TRY... CATCH...

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestWKT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TestWKT]

GO
------------------------------------------
CREATE PROCEDURE TestWKT(
    @wkt varchar(max),
    @srid int) 
AS
BEGIN

SET NOCOUNT ON;

DECLARE @valid bit;

BEGIN TRY
    SET @valid = geometry::STGeomFromText(@wkt, @srid).STIsValid();
END TRY
BEGIN CATCH
    SET @valid = 0
END CATCH

SELECT @valid;

END
------------------------------------------------
GO

exec TestWKT 'garbage', 4326

exec TestWKT 'POINT(-84 32)', 4326
share|improve this answer
    
That's excellent! Thank you! –  GHC Jul 23 '13 at 7:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.