I am looking for a function that will return the intersection of 2 or more polygons (geography type).
I am aware of ST_UNION, ST_COLLECT but it works only for geometry type.
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this communityYou can cast to geometry and do the calculation. If your data goes over the poles or date-line that could be problematic. Also if you have long edges, in which the great-circle interpolation line differs from a simple cartesian interpolation. In those cases you might have to create your own aggregate function that re-projects to something reasonable before running the operation.
SELECT ST_Transform(
ST_Union(ST_Transform(geog::geometry, 26910)),
4326)::geography
FROM the_table
According to bostongis, there is no UNION that works on geography type. So you have to first cast geography to geometry:
geometry(geography(your_table.the_geom))
Then apply ST_UNION and cast back.