locked
clipping geography polygons?! RRS feed

  • Question

  • Hi,

    I need your help...I am a kind of desperate...

    What I want to do:

    I have a set of Polygons(stored in SQL Geography) which I want to visualize  on bing maps. Thats no problem, everthing works fine. One functionality of my bing maos app is to modify the polygons, and then store them back (via a wcf with update...) to sql server.no problem, too, BUT now I want to make a kind of clipping, so that I do not have overlaps in my set of polygons. The "new" created polygon is a "master" and clips/cuts all other polygons which overlaps the "master" polygon, so that the result is a topological correct set of polygons with no overlaps .

    Do you have an Idea how to do it? My first thought was to use StOverlaps, but this returns only the number of overlapping polygons. and is it right, that I first have to transfor the geography polygons to geometry to use the spatial methods?

    Hope you can help me !!!

    best and thanks ,

    Thomas

    Thursday, September 30, 2010 1:29 PM

All replies

  • You can achieve this either using STIntersection() or STDifference() (depending on how exactly you want to allocate the overlap).

    STIntersection() returns the intersection (i.e. the area of overlap) between two polygons. So if you do MasterPolygon.STIntersection(otherpolygon), you'll get the areas of space that lie in both polygons, and you can then decide which polygon to allocate them to manually (you can merge them with the selected polygon using STUnion()).

    Or, STDifference() gives you only the area that lies within one polygon that doesn't lie within another polygon. So if you SET MasterPolygon = MasterPolygon.STDifference(otherpolygon), Master Polygon will be "clipped" to not contain any of the area that also lies in otherpolygon.

    If you do it the other way around: SET otherpolygon = otherpolygon.STDifference(MasterPolygon) then it will be the otherpolygon that is clipped, and MasterPolygon will retain all the area that lies in both.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, September 30, 2010 3:20 PM
    Answerer
  • Hi, thank you for your reply. Do I have to use a loop for getting the overlaps?because it is possible that the masterpolygon can have 1 to n overlaps.

    my current approch is to update the masterpolygon by ID to the database. do I have to start a stored procedure after updating or is it better to query it directly via wcf?

    best ,

    Thomas

    Thursday, September 30, 2010 3:32 PM
  • Can you post some sample data? It will be easier to explain ;)

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, September 30, 2010 4:15 PM
    Answerer
  • Sure, what data do you need? coordinates? or the queries I use? Is it possible to post pics?
    Thursday, September 30, 2010 4:22 PM
  • Hi, this is a pic what show a possible result with overlapping polygons:

    Photobucket

     

     

    the blue polygon ist the master polygon which will be updated...

    Another example with gaps:

     

    <a href="http://s1099.photobucket.com/albums/g381/Dru_MS/?action=view&current=gaps.png" target="_blank"><img src="http://i1099.photobucket.com/albums/g381/Dru_MS/gaps.png" border="0" alt="Photobucket"></a>

     

    Photobucket

    Do you have an idea how to solve these problems?

     

     

    Thursday, September 30, 2010 5:22 PM
  • The first example, where the "master" polygon has expanded to overlap the other polygons, is simple. In these cases, you just need to update the other polygons in the table to exclude the area that is also covered by the master polygon.

    UPDATE TABLE
    SET geom = geom.STDifference(@MasterPolygon);
    
    The master polygon will be assigned all areas where there is competition, and the other polygons lose this area and are shrunk accordingly.

    The second example, where your "master" polygon has shrunk is a bit more complicated, because you need to decide what rule you are going to follow to decide what polygon is allowed to "fill the gap". In your picture above, both the yellow and pink polygons have expanded, but by a seemingly arbitrary amount and shape - what is the rule that you want to apply here?


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, September 30, 2010 6:58 PM
    Answerer
  • Hi,

     

    the second example is a kind of worst case scenario. perhaps its a first approach to split the gap, 50 % to each? or are there any special rules? I don´t know some.

    The main thing is, that it´s not allowed to have gaps.

    I´ll try the other solution you just told me tomorrow, I am really so tired right now :-)

    Big thank you and to read you tomorrow again.

    THANKS

    Thursday, September 30, 2010 7:27 PM
  • Hi,

    Assuming that your initial set is topological correct:

    1) Find "touching" geometries (pink and yellow)

    2) For each one, find the vertex you are moving in master (it is possible that you'll need to add a new vertex)

    3) Replace the vertex in all the geometries. If all the geometries are valid, the whole operation is valid.

     

    This is just the logic. Performing these operations require the CLR programming, of course

     

    Friday, October 1, 2010 6:17 AM
  • Hi vIndEx,

    thanks for the reply. Good idea, but diificult for me to implement. I really have no experience with programming clr.

    A "little" stored procedure is ok, but more...

    And all still must be callable from within my bing maps app.

    When pushing the save button a wcf service updates the geometry to sql server, all necessary functions/procedures must start automatically.

    I have done that with one stored procedure, but not in a complex way.

    best

    Friday, October 1, 2010 6:49 AM
  • There are no "special" rules as far as SQL Server is concerned - the rules are entirely based on whatever the business logic of your program is (which we don't really know!). Saying that you want to assign "50%" to each is not really a rule - your picture above is simple because only a single point in the Master Polygon has moved.... but what about more complicated situations where there is not an obvious correlation between the "old" Master polygon and the updated Master polygon (perhaps there are a different number of vertices - perhaps it's been deleted altogether!), or where there are more than two neighbouring polygons that could fill the gap?

    Your previous comment says that the most important thing is that there should not be any gaps between polygons. In this case, I think you're looking at the problem the wrong way - when you drag a point in Bing Maps, you're not really editing a single polygon, but rather you're editing a vertex in a mesh that is connected to several polygons. This is essentially the same point that vIndEx has already made, but rather than alter those polygons in the DB, I'd suggest you handle this in Bing Maps itself - work out all the polygons that have been affected by the current user edit and write the changed values of all these polygons back to the DB.

    Assuming you're more comfortable with javascript than the CLR, that means you can code all your logic in javascript on the client side instead - you're only using the DB as a storage layer.

     


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, October 1, 2010 7:54 AM
    Answerer
  • Hi again,

    I know, a mesh would be perfect, but actually I am happy to be able to visualize my sql geography with the silverlight map control. silverlight support polygons, polylines, and pushpins. this isn´t a lot. through my actual approach I can visualize and edit data, multipolygons, multilines etc...(no support for interior rings yet). For using a mesh it would be important that all polygons are topological correct, so that the boundaries are identical for neighbor polygons. Another point is, that my data has a huge number of vertices, the performance would be very bad. Thats why I tried to solve it that way, with overlaps and filling gaps. I know it´s not perfect...but it´s a possible solution.

    Friday, October 1, 2010 9:10 AM
  • Hi Dru,

    Working with geography/geometry CLR stored procedures is quite easy. Take a look to

    http://msdn.microsoft.com/en-us/library/ms131094.aspx

     

    Also, Sql Server Spatial Tools is a very good example to start with.

    Saturday, October 2, 2010 7:05 PM
  • Hi vIndEx,

    thanks for your help. I will have a look at it.perhaps it is usefull. The most important thing is really to fill the gaps,the "management" of the free area would be important , too, but has a lower priority at the moment.

    Thanks !!!

     

    Sunday, October 3, 2010 11:06 AM