locked
SQL Geometry unioning polygons within a specified distance RRS feed

  • Question

  • Hi - Im trying to write SQL code that will do this:

    select Row_number() over (order by (select 1)) as id, geometry::UnionAggregate(g.shape) as shape
    from <table> g
    group by g.shape.STDistance(g.shape) < 50.

    This fails with the group by statement. I want to union polygons within 50m of each other.

    Using SQL Server 2012 and SQL Geometry
    Any ideas please?

    Geoff

    Thursday, May 26, 2016 5:50 AM

Answers

  • I'm not exactly sure what you mean by "union polygons within 50m of each other". If you start with a table of say, 100 rows, to obtain 100 resulting rows with the appropriate groups and aggregate geometries, you could start with a self-join:

    select Row_number() over (order by (select 1)) as agg_id, geometry::UnionAggregate(g2.shape) as agg_shape
    from <table> g1
    join <table> g2
      on g1.shape.STDistance(g2.shape) < 50
    group by g1.id;

    But if you're trying to aggregate rows and remove the originals (i.e. start with 100 rows and obtain 80 rows, some rows being aggregates), that's a bit more complex. Suppose polygon A is 30m from polygon B and 35m from polygon C, but polygon B is 60m from polygon C, should A aggregate only with B or C, or should A aggregate with B and separately aggregate with C? (which is what the query above would do) If you need logic to pick the appropriate 1 row to aggregate with you would have to accomplish this with additional logic, likely using procedural code. Try this on a small subset of the table and see if you get the results you're expecting.  

    • Marked as answer by GISGeoff Monday, May 30, 2016 11:08 PM
    Friday, May 27, 2016 7:58 PM

All replies

  • I'm not exactly sure what you mean by "union polygons within 50m of each other". If you start with a table of say, 100 rows, to obtain 100 resulting rows with the appropriate groups and aggregate geometries, you could start with a self-join:

    select Row_number() over (order by (select 1)) as agg_id, geometry::UnionAggregate(g2.shape) as agg_shape
    from <table> g1
    join <table> g2
      on g1.shape.STDistance(g2.shape) < 50
    group by g1.id;

    But if you're trying to aggregate rows and remove the originals (i.e. start with 100 rows and obtain 80 rows, some rows being aggregates), that's a bit more complex. Suppose polygon A is 30m from polygon B and 35m from polygon C, but polygon B is 60m from polygon C, should A aggregate only with B or C, or should A aggregate with B and separately aggregate with C? (which is what the query above would do) If you need logic to pick the appropriate 1 row to aggregate with you would have to accomplish this with additional logic, likely using procedural code. Try this on a small subset of the table and see if you get the results you're expecting.  

    • Marked as answer by GISGeoff Monday, May 30, 2016 11:08 PM
    Friday, May 27, 2016 7:58 PM
  • Tks Bob - have got this a bit clearer now after your explanation and more thinking. We will go down the procedural code method as we have to consider the land type between the separated polygons whether natural (eg reserve) versus man made (eg road).

    tks Geoff

    Monday, May 30, 2016 11:15 PM