New feature to assist in fixing invalid data : WhereIsInvalid() ? RRS feed

  • General discussion

  • There have been a couple of posts recently on the subject of "invalid" geography instances caused, it seems, by data in which one or more of the polygon rings is self-intersecting.
    In many cases, these problems can be fixed by shifting only one coordinate value. See, for example, http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/8b4feeeb-a34e-48bb-a095-a7195de403e2

    However, the problem (from a practical point of view at least) is how to identify which point(s) needs to be adjusted to make the instance valid.

    Currently, I generally start by performing a visual analysis of the geometry to look for likely places where intersection is occurring, then checking the coordinate values from the instance in the vicinity, but this is not always practical. I'm wondering if anybody can suggest a better programmatic way of doing this?

    I'm essentially thinking of a new method, something like geom.WhereIsInvalid() :

    • For cases where STIsValid() returns 1, I'd expect this to return an empty geometry.
    • For cases where STIsValid() returns 0, I'd expect this to return a geometry or geometry collection somehow representing the invalid portions of the geometry in question. Something like shown below:

    Given this result, it would be a lot easier to target any actions requires to "fix" the data in question. Obviously, my suggestion would only apply to invalid geometry instances in SQL Server 2008 (since, by definition, an existing geography instance is always valid), but it would at least be a start.

    Would anybody else find this sort of feature useful, or have any ideas about how it could be implemented?

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, February 1, 2011 8:36 AM

All replies

  • I raised this in connect a year ago, https://connect.microsoft.com/SQLServer/feedback/details/535743/reason-geometry-is-invalid

    It is marked as "fixed" although I have no idea what that means.  Perhaps it is in Denali?


    Tuesday, February 1, 2011 11:41 AM
  • Hi @wimpy1 - thanks for sharing.

    That is interesting, indeed... I couldn't find any sign of this being "fixed" in the current Denali CTP but it's still early days. I remember the "Spatial Results" tab not being introduced until after the last CTP of SQL Server 2008, so let's hope this is a feature that makes it in before RTM.

    I've voted for your issue, anyway!

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, February 1, 2011 12:17 PM
  • Alistair - i'm a  noob with this spatial stuff, despite using it since the betas ... and I can't stress how much life i've lost trying to :-

    1 - deal / fix invalid geographies
    2 - import .shp files (because there's invalid data).
    3 - get performing queries because i suck at spatial indexing.
    4 - Unioning geographies (fixed in Denali, i've read but not tested .. and yes ... codeplex has a workaround but it's not always fine if it somehow creates an invalid geog).


    So 2/3 of my world-problems are related to your post :) I live in geographies because i've been working with locations in the world .. so i thought that's the best way to work.

    I'll sacrifice a kidney to the demo-gods to get this stuff 'just working' for Sql vNext.

    Left or Right kidney? I'm not worried.


    -Pure Krome-
    Wednesday, February 9, 2011 11:52 AM