locked
Out of Memory when using STBuffer() on complex aggregated polygons RRS feed

  • Question

  • Hi chaps,

    I am experiencing an out of memory issue when processing complex shapes that consist of smaller aggregated polygons. Firstly I will tell you how I got here.

    I have a dataset that contains SQL geography polygons for every postcode in the UK. My goal is to create aggregate shapes for every possible postcode sector, these aggregate shapes need to load quickly so mustn't contain too many points (performance testing I have carried out show acceptable performance with shapes that contain 250 points or less). Note that a postcode sector consists of the outcode + the first digit of the incode, e.g. the postcodes G53 7WQ, G53 7NL and G53 7ZD belong to the G53 7 postcode sector.

    I calculated all possible sectors and used STUnion() in a cursor to create aggregate shapes for all postcode polygons that belong to that sector. I was left with some very complex postcode sector shapes, so I decided to reduce the complexity of these shapes using the Reduce() function.

    It is important that the reduced shapes do not lose coverage of any areas, or that any gaps appear between sectors, so I decided to use the STBuffer() function to buffer out the shape before reducing it (it is not a problem if a shape is a little too big, but it is a problem the other way around). I buffered them out using the following script:


    SET            BufferedShape = Shape.STBuffer((SQRT(shape.STArea()))/100)

    The idea behind this is that the amount I buffer out is related to the area of the shape, so for example if I used the above script on a square that was 100m by 100m then I would buffer out by 1m (the area is 100 x 100 = 10000m2, square root of 10000 is 100, divide by 100 = 1).

    Unfortunately it is at this stage I come across the out of memory error. It only occurs when attempting to STBuffer() these very complex aggregate shapes, I have read that that this memory issue can occur when a buffer larger than the radius is supplied.

    I have tried to divide the buffer task into smaller chunks and process and commit these chunks one at a time so to reduce the pressure on the VAS memory, but I have found that this doesn't make a difference as buffering a single complex geography can eat all the memory on my machine (12GBs).

    Could this be a problem specifically to do with multipolygons and buffering out some of the smaller polygons based on the STArea() of the geography value?

    Has anyone else come across this problem? If so what method did you use to tackle it? Even if you have not experienced this problem, does anyone have any suggestions on how to deal with it?

    Many thanks and apologies for the long post.

    Richard

    Monday, January 30, 2012 3:38 PM

Answers

  • Solved!

    I tried the same with RC0 but still ran into the OOM problem. In the end I applied a buffer and reduce to the polygons being aggregated so they were less complex, then aggregated these polygons together, finally I buffered and reduced the resulting shape to ensure it was performant.

    All full postcode polygons in a given postcode sector

    Aggregate shape comprised of these polygons (632 points)

    Thursday, March 22, 2012 11:58 AM
  • Buffered and reduced aggregate shape (235 points)

    Thursday, March 22, 2012 11:59 AM

All replies

  • I am not sure about the memory problem.

    But, creating a buffer as a function of an 'area of a geometrical shape' is not what we do often.

    Richard, can you please try this:

    1. get the center/centroid of the Shape.

    2. Find distance(let, @Distance) between farthest point and center.

    3. Draw buffer from center using the above distance(@Distance) as a param

     BufferedShape =@Shape.EnvelopeCenter().STBuffer('distance between center and farthest point from center');

    OR, BufferedShape =@Shape.EnvelopeCenter().STBuffer(@Distance);

    [ Shape.EnvelopeCenter() will return the center of the POLYGON: 'Shape']

     

    Step 2 might be long, but I don't think it will create a memory error. If you think, you missed the farthest corner while using Reduce(), you can add a little Constant with @Distance.

    Like: SET  @Distance = @Distance + K;

    Thanks.

    Wednesday, February 1, 2012 7:36 AM
  • I think memory usage of STBuffer() is a known problem.

    What version of SQL Server are you using? The only thing I can suggest is that you try downloading and using the latest (RC0) release of SQL Server 2012, in which (according to this document):

    "STBuffer() has been optimized and improved for all cases. It’s faster and has lower memory footprint, especially for large and complex objects."


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, February 1, 2012 8:11 AM
    Answerer
  • Mayukh - Thanks for your suggestion. I agree that using the method you suggested will create simplified shapes that will provide total coverage, but unfortunately the accuracy of the original shapes would be lost, so no good for me in this case - unless I have misunderstood, in which case please let me know.

    Tanoshimi - 2008 R2. I will try using RC0.

    A known workaround is to reduce polygons before buffering, thus reducing the number of points. I was worried that this would affect the accuracy of the shape, so my plan is:

    1.  Apply a 'light' reduce to the polygons.

    2.  Use STBuffer().

    3.  Apply a regular reduce to the polygons.

     

    I'm hoping the first reduce will bring down the number of points enough so I don't get the OOM problem when buffering later but still retain a fairly accurate shape. I have tried reducing without buffering before, but this has left gaps between polygons which is what I want to avoid.

    I will try RC0 first, if that fails, I will try the 2 stage reduce. I will let you know if I have success.

     

    Thanks :)

    Monday, February 6, 2012 4:44 PM
  • Solved!

    I tried the same with RC0 but still ran into the OOM problem. In the end I applied a buffer and reduce to the polygons being aggregated so they were less complex, then aggregated these polygons together, finally I buffered and reduced the resulting shape to ensure it was performant.

    All full postcode polygons in a given postcode sector

    Aggregate shape comprised of these polygons (632 points)

    Thursday, March 22, 2012 11:58 AM
  • Buffered and reduced aggregate shape (235 points)

    Thursday, March 22, 2012 11:59 AM