1

Is it possible to group rows together that have geometries that overlap? If so, is it also possible to make that a configurable threshold. Let's say I have a table representing data that looks like:

Imgur

Let's say I'd like to get the number of geometries in each overlapping cluster together with the bounding box of the cluster. I'd like to do something like:

SELECT 
    ST_Extent(geom) as bbox,
    COUNT(*) as count
FROM table
GROUP BY ???

The result that I would hope for is:

|bbox     |count|
|---------|-----|
|bbox(B,C)|  2  |
|bbox(D,E)|  2  |
|bbox(A)  |  1  |

As a bonus question, I'd also like to be able to parameterize this over an overlap threshold like Jaccard index. Meaning I'd like to get the same sort of grouping if the geometries overlap sufficiently. For example, I'd like to get the same grouping as previously mentioned but split up D and E if the don't overlap by the supplied threshold:

|bbox     |count|
|---------|-----|
|bbox(B,C)|  2  |
|bbox(D)  |  1  |
|bbox(E)  |  1  |
|bbox(A)  |  1  |
Matt
  • 402
  • 3
  • 10
  • What if A overlapped B ? Would you expect A, B, and C to be a single group? Or are you just looking for pairs? – dbaston Feb 21 '18 at 22:10
  • 1
    I've done unioned buffers, then broke out to singlepart features, then selected the ones that overlap... could that help here? – DPSSpatial_BoycottingGISSE Feb 21 '18 at 22:34
  • @dbaston, I would expect A, B, and C to be a single group. – Matt Feb 22 '18 at 14:07
  • @Matt I'd suggest looking at this answer, then: https://gis.stackexchange.com/a/94243/18189 PostGIS has clustering functions that are distance-based and intersection-based, but not overlap-based. You'll have to write some kind of procedure yourself, or use a difficult-to-scale recursive query. – dbaston Feb 22 '18 at 14:41

1 Answers1

1

The following SQL statement does the trick for your "non-parameterized" requirement. (Credits to DPSSpatial.)

select st_extent(s1.wkb_geometry), count(s1.id_grp)
from
(
    select (tc0).path[1] id_grp, (tc0).geom wkb_geometry
    from
    (
        select st_dump(st_union(wkb_geometry)) tc0 from "table"
    ) ts0
) s1, "table" s2
where 
    s1.wkb_geometry && s2.wkb_geometry and
    st_intersects(s1.wkb_geometry, s2.wkb_geometry)
group by s1.id_grp
KapilDev Neupane
  • 105
  • 2
Ralph Tee
  • 1,722
  • 12
  • 17