7

If i'm not terribly mistaken, ST_Intersects does not support GeometryCollection.

ERROR:  Relate Operation called with a LWGEOMCOLLECTION type.  This is unsupported.
HINT:  Change argument 2: 'GEOMETRYCOLLECTION(POLYGON((.......

Currently i am using a wrapper that cycles through the different types in a GeometryCollection and performing a ST_Intersect on each. Needless to say, this is terribly inneficient.

There must be a better way?

Improve this question
5
  • 1
    So it is documented in postgis.net/docs/ST_Intersects.html Do not call with a GEOMETRYCOLLECTION as an argument for geometry version.
    – user30184
    Commented Apr 15, 2016 at 13:16
  • @user30184 i was just there reading this. seems the workaround is to switch to geography instead of geometry. or not? i hope im not opening a can of worms.
    – Sharky
    Commented Apr 15, 2016 at 13:17
  • 2
    You could use ST_Dump with a ST_GeometryType in the where or ST_CollectionExtract to pull out the types you want and then run the Intersects. It will be more efficient if you create a new table and add a spatial index, naturally
    – John Powell
    Commented Apr 15, 2016 at 17:17
  • I'm curious, why do you say that the wrapper is "terribly inefficient" ? What would a more efficient implementation look like?
    – dbaston
    Commented Apr 18, 2016 at 20:48
  • @dbaston if im not terribly mistaken, its innefficient because if i search 1000 GEOMETRYCOLLECTION's against a point it will be 2000+ queries under the hood (based on my dataset where each GEOMETRYCOLLECTION has at least 2 different geometries). a more efficient implementation? i really don't know. that's my question :/
    – Sharky
    Commented Apr 19, 2016 at 6:38

2 Answers 2

Reset to default
11

Spatial relation operators do not work on geometry collections by design, as inherited from JTS.

Try using ST_CollectionHomogenize, which will return a regular geometry, if possible. Or ST_CollectionExtract to specify a geometry type.

Improve this answer
1
5

According to the PostGIS documentation for the ST_Intersects function, the PostGIS version "2.5.0 Supports GEOMETRYCOLLECTION".

It may be good to note that, the PostGIS version 2.5.0 release news states that "this release will work for PostgreSQL 9.4 and above but to take full advantage of what PostGIS 2.5 offers, you should be running PostgreSQL 11beta4+ and GEOS 3.7.0".

Improve this answer
1
  • 1
    ty for the update, putting the accepted mark on this answer
    – Sharky
    Commented Jan 2, 2019 at 12:07

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.