This is a definite edge case, but we have a database that pulls everything from OpenStreetMap for our county and keeps them in generalized tables (with an hstore column "tags" holding all the attributes).
If I'm doing something with transportation, I'll have a WHERE clause like
tags -> 'highway' is not null
to get the routable linear features. But I may want to toggle whole categories of features on and off within that layer, so a definition query to focus on pedestrian or cycling features can be handy, so having a handful of definition queries makes it easy to flip between different sets.
To me, the WHERE clause is for a filter I don't ever mean to adjust, it's part of the layer's "definition". The definition query, though, is more like a "filter widget" to me, and is something I intend to turn on and off as needed.
Of course, using discrete parameters in the query can accomplish that, too, for simpler use cases.
- Josh Carlson
Kendall County GIS