Create "histogram" for multiple intersecting geometries RRS feed

  • Question

  • Hi,

    I have a couple of polygons and every polygon has a value (propability). Now I want to get the "highest" polygon, e.g. the polygon where all intersections have the highest value. It is like a histogram based on all polygons where every cell gets the values summed up. But I just like to use polygons instead of cells.

    Currently I'm using SQL Server 2008 and a C# implementaion. I only found very complex caluculations for grids and absolutly nothing to create a histogram based on multiple polygons.

    I implemented my own solution based on pairwise comparison and intersection of all polygons, intersections and so on. But this is really time consuming and I get a lot of problems when several polygons that are nearly the same get intersected. So this is not the best solution.

    I can't understand why there is absolutly no information how to implement this.

    Maybe someone has a good idea.

    Thanks in advance,


    Tuesday, August 14, 2012 10:00 AM

All replies

  • I can't quite follow your description - are you trying to create a Venn diagram and work out the probability of certain combined events based on the area of intersection between them?  Perhaps you could post a screenshot of your application?

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Tuesday, August 14, 2012 11:14 AM
  • Yes, I think you got the idea. Please find attached some example screenshots to clarify the idea. For example in picture 1 I have several geometries with certain probabilites intersecting. In picture 2 only the combined polygon is shown that is created out of the intersections. This polygon has the combined probabilities of the intersected polygons.

    Tuesday, August 14, 2012 11:54 AM
  • Ok, so it sounds like your pairwise comparison currently uses the STIntersection() method between two Polygons from your dataset, and you're then taking this output and using STIntersection() again with the next Polygon, and repeating this throughout the Polygons? If so, when do you stop? If there's any Polygon in your dataset that doesn't intersect the others, the result of STIntersection() with it will always lead to an empty geometry...?

    The first thing to note is that STIntersection() is symmetric - A.STIntersection(B) is the same as B.STIntersection(A) - so you don't need to repeat for every permutation of the Polygons, only for every combination of Polygons.

    Secondly, I'm assuming that you're currently comparing some cases where A.STIntersection(B) results in an empty geometry (because the two Polygons do not intersect at all) - you might get better performance by filtering out such cases in a predicate of the query.

    You say that you're using C# and SQL Server - so is the computation getting performed in the database itself, or in a client-side application that just retrieves data from the DB? If it's in the database, you may be able to harness a spatial index to improve the performance of your query. If you're doing it all client-side, this isn't an option.

    Although SQL Server 2012 introduced several new aggregate functions, "IntersectionAggregate()", which I think is what you are really trying to do, was not one of them. However, it wouldn't be roo hard to write a custom CLR aggregate function, which might be more efficient than calling STIntersection() in a loop.

    I'm not sure if any of that directly answers your question, but perhaps gives you some suggestions to think about!

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Tuesday, August 14, 2012 12:20 PM
  • Thanks for the answer. There are some ideas in your post I will try. This functions seems to be so "regular" that it should exist somewhere ;)

    I just upgraded to SQL Server 2012 to check out the new functions. But except the sp_help_spatial_geometry_histogram there seems nothing that is helpful.

    Thanks again.

    Tuesday, August 14, 2012 1:07 PM