locked
STUnion fails if polygons are very far RRS feed

  • Question

  • declare @g1 geometry = 'POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))'
    declare @g2 geometry = 'POLYGON ((134217730 134217730, 134217732 134217730, 134217732 134217732, 134217730 134217732, 134217730 134217730))'
    
    select @g1.STUnion(@g2).ToString()
    
    
    Result: MULTIPOINT ((134217732 134217732), (0 0))

    This polygons are nearly the same size (1 and 4), the operation fails  for the distance between them I think




    Wednesday, February 10, 2010 8:02 AM

All replies

  • What units are these in?  Perhaps specify an SRID and see what happens?
    Wednesday, February 10, 2010 12:32 PM
  • I've tried with srid 0, but in theory the data type geometry is for any kind of shapes, not only geographic ones.

    Anyway, I believe that SRID in geometries only affects to metadata. In geography is used to do some calculations, but it is not possible to do this test because the range of coordinates is -180 -> 180 and -90 -> 90
    Wednesday, February 10, 2010 2:11 PM
  • Hi Folks,

    This really is a scale issue.  Internally, all of the geometry operations happen on an integer grid of a fixed size.  But by fixed size, I mean that the number of divisions is fixed, not the scale of the grid: the scale is determined dynamically for each operation based on the inputs.

    Here the size of the working space is very large (134,217,732 x 13,421,7732) but each object is very small (1x1 or 1x2), and so each object is essentially degraded to a point on the grid.  The union then operates on these points.

    You are correct that the SRID is irrelevant in this case---and in all cases for geometry.

    Cheers,
    -Isaac
    Isaac Kunen, Microsoft SQL Server
    • Marked as answer by vIndEx Thursday, February 11, 2010 8:21 PM
    • Unmarked as answer by vIndEx Saturday, February 13, 2010 4:36 PM
    • Proposed as answer by Nemanja Matkovic Monday, August 30, 2010 11:07 AM
    Thursday, February 11, 2010 4:50 PM
  • Thanks for your answer. It's good to know how things works to avoid errors!
    Thursday, February 11, 2010 8:25 PM
  • Hum, thinking about it, I realised that using the grid is the reason for the coordinates to move (a little, but move) after performing operations.

    For me is important avoiding this, because it causes tiny overlaps and holes in layers that are supposed to be "complete" (like an administrative layer). So my question is, what are the dimensions of the grid? 

    Knowing this maybe I can figure if the coordinates are going to "move" (for a certain number of decimals), before i perform the operation, and try to fix the result using the original geometries

    By the way, sorry, but I unmarked your post as answer to help you notice this post
    Saturday, February 13, 2010 12:53 PM
  • I've made some tests. Using UTM coordinates (1 unit = 1 meter), I've figured out the maximum size of the box I can operate with for different levels of precission:

    1 decimals (decimeters)
    4194,303 kilometers

    2 decimals (centimeters)
    524,287 kilometers

    3 decimals (milimeters)
    65,535 kilometers

    4 decimals (10^-1 milimeters)
    4,095 kilometers

    5 decimals (10^-2 milimeters)
    0,511 kilometers

    wich means, if my box is bigger than for expample 524287 meters the second decimal of each coordinate will probably change. For some operations, this level of definition is not enough, and the only solution I can think is to use a bigger grid. 

    How? I don't know, maybe with extended methods, or defining a new type (you have int32 ant int64, so why not a sqlgeometry64?)


    Do you think I should post this in connect as a suggestion? Waiting for your oppinions...
    Wednesday, February 24, 2010 11:36 AM
  • Hi Isaac. Thanks for your explanation. We have experienced several precision problems when operating with SqlGeometry and this could be the reason. We have just checked that the geometries provided by my company to our users, have small overlaps and holes. We have traced all the operations done, and we have just discovered that these topological errores comes from the fact that some vertexes are slightly moved, causing those small overlaps and holes.

    So, the question could be: Is there any posibility to instruct SQLServer in order to use a finer grid? For instance, using a 64 bits integer grid. I think that a 64 bit grid could allow SQL Server to work with a similar precision that other spatial databases, avoiding these small rounding problems.

    Thanks in advance,




    Wednesday, February 24, 2010 6:05 PM
  • What if you do a check to see if they intersect first and if they don't then create a geometry collection out of your shapes:

    declare @g1 geometry = 'POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))'
    declare @g2 geometry = 'POLYGON ((134217730 134217730, 134217732 134217730, 134217732 134217732, 134217730 134217732, 134217730 134217730))'
    
    IF @g1.STIntersects(@g2) = 1
    BEGIN
    select @g1.STUnion(@g2).ToString();
    END
    ELSE
    BEGIN
    declare @g3 geometry = 'GEOMETRYCOLLECTION (' + CAST(@g1.STAsText() as varchar(max)) + ', ' 
    + CAST(@g2.STAsText() as varchar(max)) + ')' SELECT @g3 END

    Windows Live Developer MVP - http://rbrundritt.spaces.live.com
    Friday, March 5, 2010 11:02 AM
  • After Isaac explanation, I realized that the problem is not only how far are the geometries. With this 'grid issue', the size of the polygons is also a problem. Check this example: the union returns a single polygon when it is supposed to return a multipolygon

    declare @g1 geometry = 'POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))'
    declare @g2 geometry = 'POLYGON ((1 1, 134217732 1, 134217732 134217732, 1 134217732, 1 1))'
    
    select @g1.STIntersects(@g2)
     -- returns 1
    
    select @g1.STUnion(@g2).ToString() 
    -- returns POLYGON ((0 0, 134217732 0, 134217732 134217732, 0 134217732, 0 0))

    This is a big problem if you are trying to fix complete layers (like deleting tiny overlaps and gaps between geometries, for example, in a layer of countries)
    Sunday, March 7, 2010 11:28 PM