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  |
4
  • 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, 2018 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? Feb 21, 2018 at 22:34
  • @dbaston, I would expect A, B, and C to be a single group.
    – Matt
    Feb 22, 2018 at 14:07
  • @Matt I'd suggest looking at this answer, then: 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, 2018 at 14:41

1 Answer 1

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
2
  • What are s1 and tc0 ?
    – Basile
    Aug 30, 2019 at 9:11
  • @Basile They are aliases.
    – Ralph Tee
    Sep 21, 2019 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.