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  |
  • 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 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: 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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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