tl;dr: Right now the categories are kind of useless, but in the future if that changes, we will wish we had more categories.
In my mind, the intent of any image category tag is to organize that image. That implies that someone will use the category to find all images of that specific type. Does that happen? Like it's been said before, I only imagine people looking in specific games' image categories. If we had the functionality to use categories as filters, then that would be somewhat more useful, as you may be looking for just characters in a certain guide or series, for example. If there's a way to tell the wiki to combine certain categories and exclude others and put the results into one page, I don't know how to do it.
I'm a fan of many categories and very specific ones, just in case we ever get any tools to make them useful. I also agree that Sprites isn't a very intuitive category, but I think Models is understandable. My problem with Sprites is that to me it implies a small image with poor graphics. Some images are huge and beautifully rendered in thousands of colors, but because they are two-dimensional they're supposed to be categorized as Sprites (I'm assuming this from the process of elimination, as they're definitely not Models and they're supposed to be one or the other).
Here are useful categories to me, with new ones bolded:
-Controller buttons (and all its subcategories)
-Controller images (or could be included in System images)
-Flyers (or Arcade flyers or Game flyers)
-Marquees (or Arcade marquees)
-Trading cards (the kind that can be used outside gameplay)
Here are categories I don't find useful:
-Artwork (too vague)
-Fighter portraits (why)
-Guide-specific images (going through a list of all games with images is the same as going through a list of all games)
-Models (who cares)
-Sprites (who cares)
I also don't like any category you put in another category, like putting an Icons category in the Controller buttons category, but this is currently the only way to "drill down" to more specific categories right now. Ideally, I think an image page should be built upwards with including the specific categories you want and filtering out ones that also include the categories you don't want.
On an implementation note, if we ever have a way to use a bunch of specific image categories, we should put checkboxes on the upload page to make it easy for people to add categories, as well as a separate field for the name of the guide its for. On the multiple upload page, it could be a dropdown list where multiple categories could be Ctrl-selected.