Create a "dissolved view" of a polygon layer?

Question

Is it possible to create a SQL View of a feature layer that is essentially an "on the fly" dissolve?

I have a polygon layer of fire response districts in which many small polygons are all the responsibility of a single station.  Rather than creating a derivative layer (which I then have to maintain) by dissolving on the "station" attribute, it would be great to have a view of this layer in which each unique "station" value has only a single multi-part polygon.  I'm not sure whether this is something that can be done with a view...

Thanks.

jcarlson · Accepted Answer

The answer is "it depends". Specifically, it depends upon the database platform you're using, but in general, this sort of thing is certainly possible. We use PostgreSQL with PostGIS, for example, and what you're describing can be accomplished simply with:

SELECT category_field,
    ST_Union(f.geom) as singlegeom
FROM sometable f
GROUP BY category_field

ST_Union handles the dissolve, while my GROUP BY field(s) will determine what the dissolve is based on.

I'd suggest checking your database platform's spatial function documentation and testing a few things out. When you're working with spatial functions in a view, you can very easily get into CPU and memory-intensive queries.