locked
Odd issues with STIntersects RRS feed

  • Question

  • Hi,

    I've been working with the geography data type and the 2007 TIGER/shapefiles verifying intersections when I noticed an odd problem. When using STIntersects in a WHERE clause against individual segments, many items which should intersect are not returning positive. However, by concatenating all the geometries using STUnion and comparing the result is correct.

    This seems to be happening most often with road segments which END at a given cross street, rather than physically intersecting it. Am I wrong in my thinking these items should return positive? When I compare the segments in question manually the result is correct, so maybe its my query/methodoloy itself. Any insight would be appreciated.

    Thanks,
    Brian


    Code Snippet

    DECLARE @TLID bigint,
    @NAME varchar(50),
    @FULLNAME varchar(50),
    @SNDEX char(4),
    @GEO geography,
    @GeoA geography,
    @GeoB geography;


    DECLARE @Intersections TABLE (
    TLIDA bigint,
    TLIDB bigint,
    CROSSA varchar(50),
    CROSSB varchar(50),
    INTERSECTION geography
    );

    DECLARE @CrossA varchar(50), @CrossB varchar(50);

    SET @CrossA = 'reynolda';
    SET @CrossB = 'wake forest';


    /* FullSegment returns a union geography where name or fullname matches given roadname */
    EXEC FullSegment @CrossA, @GeoA OUTPUT
    EXEC FullSegment @CrossB, @GeoB OUTPUT

    /* returns true */
    SELECT @GeoA.STIntersects(@GeoB) AS Intersects

    DECLARE Curs CURSOR READ_ONLY
    FOR

    SELECT TLID,NAME,FULLNAME,GEO
    FROM AddressVerify
    WHERE
    (NAME = @CrossA OR FULLNAME = @CrossA)



    OPEN Curs;
    FETCH NEXT FROM Curs INTO @TLID,@NAME,@FULLNAME,@GEO



    WHILE @@FETCH_STATUS = 0
    BEGIN

    INSERT @Intersections
    SELECT TLID,@TLID,@FULLNAME,FULLNAME,GEO.STIntersection(@GEO)
    FROM AddressVerify
    WHERE
    (NAME = @CrossB OR FULLNAME = @CrossB)
    AND
    (GEO.STIntersects(@GEO) = 1)

    FETCH NEXT FROM Curs INTO @TLID,@NAME,@FULLNAME,@GEO

    END

    CLOSE Curs;
    DEALLOCATE Curs;


    /* returns 0 records, but should be 3 */
    SELECT * FROM @Intersections


    Friday, October 3, 2008 12:52 PM

Answers

  • The STIntersects() method will return 1 (true) if two instances share at least one point in common, from either the interior or boundary of the geometries in question. Thus, STIntersects() is the most general test of intersection (the geometry-specific methods such as STCrosses(), STOverlaps(), STTouches(), STWithin() and STContains() all test particular subtypes of intersection - if any of these are true, then STIntersects() is also true).
    Therefore, you are correct in saying that a road segment whose endpoint lies some distance along another linestring (at a T-junction, say) intersects that road, and so STIntersects() should return 1.

    I'm not near a TIGER dataset at the moment, so I can't confirm the results in your case, but I suspect that the reason why STIntersects() is returning false is because the instances that appear to intersect do not actually quite do so. When you use STUnion() to create a combined geometry this may cause co-ordinate values to shift slightly (this is not unique to STUnion(), but rather a result of the imprecision of any floating-point calculations - see Isaac's post http://blogs.msdn.com/isaac/archive/2008/08/07/the-imprecise-nature-of-geometry.aspx). This fractional shift in precision is enough to make the instances truly intersect, which is why STIntersects() then returns 1.

    Like I say, this is a guess - but you'll be able to confirm this quite easily using STIntersection() - which tells you the actual geometry created by the intersection. Firstly, use Instance1.STIntersection(@Instance2) on the original instances, and find out the points at which they intersect. Since STIntersects() is returning 0, I expect the answer to this to be EMPTY. Then, use STUnion() to create the combined geometry and run Instance1.STIntersection(@UnionedInstance2) again. What is the point (or other geometry) defined by the intersection now? Is it contained in the original geometry?

    Friday, October 3, 2008 7:12 PM
    Answerer