locked
Copy from a Valid Geometry outputs an Invalid Geometry RRS feed

  • Question

  • Hi,

    I have a set of geometries that are all valid. When I copy the geometry directly to another column, the result is a valid geometry. But when I use the text representation of the same geometry to copy it, the result geometry is invalid. So the following SQL…

    -- Clean the target table
    DELETE FROM [GEOM_ROUTES_TARGET]
     
    -- METHOD 1: Copy using the text representation
    INSERT INTO [GEOM_ROUTES_TARGET] ([Shape],[SourceType],[SourceID])
           SELECT
                  geometry::STGeomFromText( SHAPE.AsTextZM(), SHAPE.STSrid)
                  ,'Text' [SourceType]
                  ,[OBJECTID]
        FROM GEOM_ROUTES_SOURCE
    GO
     
    -- METHOD 2: Copying the geometry directly
    INSERT INTO [GEOM_ROUTES_TARGET] ([Shape],[SourceType],[SourceID])
           SELECT
                  SHAPE
                  ,'Geometry' [SourceType]
                  ,[OBJECTID]
        FROM GEOM_ROUTES_SOURCE
    GO
     
    --Check the results
    SELECT
           s.[OBJECTID] AS [Source ID]
           ,s.[Shape].STIsValid() AS [Source Is Valid]
        ,t.[Shape].STIsValid() AS [Target Is Valid]
        ,t.[SourceType]
           ,IIF(s.[Shape].STIsValid() = t.[Shape].STIsValid(), 'Yes', 'No') AS [Sucess]
    FROM
           [GEOM_ROUTES_TARGET] AS t
           INNER JOIN GEOM_ROUTES_SOURCE AS s ON s.OBJECTID = t.SourceID


    .. Produces this results


    Also when I try to update another field in the source table (where the geometries are valid) using the SQL Server Manager, it complaines about the so called "valid" geometry

    IMPORTANT: I can't use the MakeValid() method because it drops all Z values of the geometries :(

    Any help would be much appreciated J





    Thursday, August 6, 2015 2:25 PM

All replies

  • Did you try selecting the before and after values of the rows that are now invalid (as text) to see what's changed? It would be interesting to know.

    Try updating the rows that have problems with individual T-SQL statements (using WHERE clause) rather than with SSMS, as SSMS is not really a full-featured table editor, it may check the value of all rows you've displayed before it updates any of them, or some other behavior that's causing the error message.

    Cheers, Bob

     
    Thursday, August 6, 2015 5:18 PM