1

I'm not sure I'm doing this right, I've two locations, one a point and one a point within an area. I'm running them through st_clusterwithin, and they both create 2 individual cluster groups (which is fine). Problems start when I try to rejoin the cluster using st_contains. Anybody know what I'm doing wrong, or have I stumbled across a weird bug?

-- postgresql-9.5-postgis-2.2
CREATE TABLE test (name character varying, p geometry(point, 900913), a geometry(Geometry, 900913));
INSERT INTO test values ('a', '010100002031BF0D00713D0AD7F54711C1A4703D1A3D965941', null);
INSERT INTO test values ('b', '010100002031BF0D0002FB34FAC62E11C1FAF7FB8141965941', '010300002031BF0D00010000000D0000009A9999998B3211C114AE47F13D965941295C8FC2863211C1A4703D5A569659418FC2F528D33011C1EC51B84E5A9659417B14AE47B62F11C1B81E851B5A965941295C8FC2BC2F11C18FC2F5F85D96594114AE47E10D2C11C1295C8FE26F965941EC51B81EF32B11C1666666D6699659417B14AE47D12C11C1F6285CFF139659418FC2F528BA2F11C1F6285C7F14965941B81E85EBBE2F11C11F85EB01279659415C8FC2F5DB2F11C1D7A3702D2C965941F6285C8F2F3011C15C8FC235309659419A9999998B3211C114AE47F13D965941');

select name, st_contains(ST_CollectionHomogenize(c.g), u.p)
    from test u, 
        (select unnest(st_clusterwithin(coalesce(u2.a, st_expand(u2.p, 350)), 250)) g
        from test u2) c order by name;

  name | st_contains 
------+-------------
 a    | t
 a    | f
 b    | t
 b    | t
(4 rows)

Notice that the temporary table below is identical to the inner select above:

create temporary table test_g as 
  select unnest(st_clusterwithin(coalesce(a, st_expand(p, 350)), 250)) g  
  from test;
select name, st_contains(ST_CollectionHomogenize(c.g), u.p)
    from test u, test_g c order by name;
osm_gb-#  name | st_contains 
------+-------------
 a    | f
 a    | f
 b    | t
 b    | t
(4 rows)

Neither of the outputs appear to be correct, I'd expect one 'a' row to be true for it's cluster and false for the other, and 'b' likewise.

  • 1
    Based on @dbaston's find, a quick workaround is to recreate the geometry with ST_CollectionHomogenize(c.g)::text – Mike T Jun 3 '16 at 3:41
3

I'm pretty sure there's a weird bug here, but it's actually in ST_CollectionHomogenize. I filed a report here:

https://trac.osgeo.org/postgis/ticket/3569

As a workaround, you should be able to use ST_CollectionExtract instead of ST_CollectionHomogenize to turn your GeometryCollections into (Multi)Polygons to perform the intersection/containment test.

  • Thanks for filing and figuring it out! I tried to sign up to that trac but the signup process involved IRC so I took that as a 'not welcome here' sign. Your workaround was successful with the select being select name, ST_Contains(ST_CollectionExtract(c.g, 3), u.p). The 3 argument selects polygonal members of the GeometryCollection (they were all polygons in the example anyway) – EoghanM Jun 3 '16 at 6:57
  • This approach came about from using my own answer to stackoverflow.com/questions/35482754 Maybe there is a better approach to using ST_ClusterExtract? – EoghanM Jun 3 '16 at 7:06
  • The usability problem (for this scenario) comes the fundamental limitations of an aggregate (just like you can't say "give me the max ID and the name that goes with it"). A more flexible window function will be available in 2.3; see the docs or an example for ST_ClusterDBSCAN. Until then, you could consider abusing the Z or M ordinates to pass an ID through to make the join less painful. – dbaston Jun 3 '16 at 12:56

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.