locked
Touches for Geography (tolerance for STTouches or similar operations) RRS feed

  • Question

  • I'm trying to see is a couple of Geographies touch each other and I'm finding that I really need a tolerance mechanism for this.

    Situation, I want to see if geographies touch or not. For example if we have a bunch of zip code shapes, they touch but do not overlap. So they should return 1 for STIntersects (b\c they touch), but the area of their intersecton should be 0.

    This I found is not working with our data as the Intersection area will be really, really small (20 or somthing), look like the line border, whereas that area is irrelevant when we are talking about 2 shapes with areas themselves of like 300 million or something.

    I also tried converting them to geometries and doing STTouches, but it still thinks they overlap and do not touch.

    Is there a tolerance mechanism for this "touching" scenario or should I just make my own?

    Thanks
    Tuesday, March 24, 2009 10:43 PM

Answers

  • STTouches() (and all the other STxxxx() methods) have very strict definitions based on a specific pattern of intersections using the DE-9IM matrix model - there is no allowance for any degree of 'fuzzy-matching' unfortunately.

    Additionally, as you've found out, some of the more specific methods such as STTouches() have limited use on 'real-life' data, since most data is not precise enough that touching instances only touch and not overlap slightly (and then there's the issue of floating-point approximations etc. etc.).

    So, what to do? Well, in your case you could write conditions something like this:
    A.STBuffer(0.01).STIntersects(B.STBuffer(0.01)) = 1 
    AND 
    A.STIntersection(B).STArea() < 20 


    The purpose of the first condition is to buffer the instances slightly before testing whether they intersect, to allow for the potential for fractional gaps between 'touching' instances.
    The purpose of the second condition is to create the intersection between the two instances and check that it has less than a certain area. (If they don't overlap at all, this will be 0). Change the value of 20 to provide different levels of tolerance.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by HoosierSql Wednesday, March 25, 2009 4:03 PM
    Wednesday, March 25, 2009 8:07 AM
    Answerer

All replies

  • STTouches() (and all the other STxxxx() methods) have very strict definitions based on a specific pattern of intersections using the DE-9IM matrix model - there is no allowance for any degree of 'fuzzy-matching' unfortunately.

    Additionally, as you've found out, some of the more specific methods such as STTouches() have limited use on 'real-life' data, since most data is not precise enough that touching instances only touch and not overlap slightly (and then there's the issue of floating-point approximations etc. etc.).

    So, what to do? Well, in your case you could write conditions something like this:
    A.STBuffer(0.01).STIntersects(B.STBuffer(0.01)) = 1 
    AND 
    A.STIntersection(B).STArea() < 20 


    The purpose of the first condition is to buffer the instances slightly before testing whether they intersect, to allow for the potential for fractional gaps between 'touching' instances.
    The purpose of the second condition is to create the intersection between the two instances and check that it has less than a certain area. (If they don't overlap at all, this will be 0). Change the value of 20 to provide different levels of tolerance.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by HoosierSql Wednesday, March 25, 2009 4:03 PM
    Wednesday, March 25, 2009 8:07 AM
    Answerer
  • Thanks Tanoshimi,

    From reading about sql spatial development it seems that they may stay with a pretty minimal OGC standards compliance in terms of native functions etc., and they will just rely on developers to make their own functions for other practical uses. Is that the feeling or word others are hearing, or does Microsoft plan on making fuller Spatial functionality in time?

    Thanks
    Wednesday, March 25, 2009 4:02 PM
  • I've found the spatial development team incredibly receptive to ideas from the community. Depending on how long you've been following these forums, you may not be aware of the infamous last minute coordinate-order swap that was implemented almost at the last minute before RTM - which was done as a direct result of input from this forum.
    I also think that they are happy to admit that the current spatial implementation is a 'first draft', as it were - there's lots more functionality that they would have wanted to implement that simply didn't make it in time.
    So I wouldn't necessarily say that there are no plans to develop further spatial functionality beyond minimal OGC-compliance, just that that was an existing standard that provided a base on which to build. One of the purposes of releasing the SqlGeometryBuilder and SqlGeographyBuilder interfaces was to allow the community to extend and develop their own functions, but I don't think that that means that future versions of SQL Server won't see a lot more inbuilt functionality being added too.

    If you have a specific feature you'd like to see implemented you can file a connect issue. For more general discussions, this forum is good.

    Isaac (or some other MSFT-ee) - could we possibly add a sticky topic to this forum for feature requests and/or a list of enhancements that are in progress for the future so that we could get public input on them? (There are some biggies - support for raster, 3d, removing the half-a-hemisphere limit - but there are lots of little issues too...)

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, March 25, 2009 4:39 PM
    Answerer