In PostGIS how can I do an ST_Intersection() search with a GEOMETRYCOLLECTION Geography field?

Question

I have a Postgres table "files" with a lat/lon location field of type `geography(GeometryCollection,4326)`. Right now the field only contains lat/lon polygons. I have succeeded in creating a gist index for the field and in doing a bounding box search with a search polygon: select fileid, file_name, ST_AsText(location) from files where location && ST_MakeEnvelope (-81.0, 22.0, -76.0, 27.0); But, I need to do a fully intersection search but I hit the restriction that `ST_Intersection()` does not accept a GeometryCollection. Maybe I could use `ST_Distance()` since I only need to know if a search area intersects a location area, not what the intersection area is. But I don't see any way to specify a spherical earth calculation, and that is probably all I need most of the time. I have been thinking about changing the field to a multipolygon since that would probably work. It looks like `ST_Distance()` will work for what I need to do. It accepts a GeometryCollection and actually will do spherical earth calculations.

Answer 1

You don’t need ST_Intersection to test overlap, and you don’t need to switch to planar geometry either. Use ST_Intersects (or ST_DWithin(..., 0)) on geography. The main blocker in your setup is the column type: a geography(GeometryCollection,4326) is awkward to work with and poorly supported. Since your data are polygons, make them MultiPolygon geography (or extract polygons on-the-fly).