Some good spots are:
Santa Teresa Park, San Jose
Stevens Creek Park, Cupertino
Black diamond mines, East Bay area
I'd like to share a few of my spots as well. I'm leaving specific areas of these places out, because I think a lot of the fun with finding queens is exploring nature and finding things for yourself. That being said, a lot of people in the Bay Area don't even know where to start because of how common Argentine ants are here.
Something I'd like to make clear though is that the chances are you WILL NOT see all of these species if you go to these places. Temperature is very important, especially since most of these parks are in traditionally cold parts of the Bay Area that don't heat up when the rest of it is warm. That being said, be selective about when you visit places. Aim for 72+ degrees days (Make sure you check the temperature of the place you're going, not just the place you live. The Bay Area is a land of microclimates.) and have a general idea of what you're looking for when you go.
San Bruno Mountain, near Daly City - Easy to get to and fantastic Formica spot, at least 5 species up there. I've also seen mature and very nicely coloured red and black Camponotus vicinus colonies, full of alates. This is a great spot to see huge Formica integroides mounds, they are right next to the parking lots. The huge picnic field is covered in Formica fusca as well.
San Pedro Valley/Montarra Mountain, near Pacifica - Big, uphill trail that starts you off in San Pedro Valley and brings you all the way up to Montarra Mountain. Tons of Formica, Camponotus, and if you look closely enough you'll see Monomorium everywhere as well. Tons of Formica argentea in the parking lot as well. The further up the trail you go the more diversity you encounter. It starts with Formica fusca and Aphaenogaster occidentalis, and as you climb you begin to find things like Camponotus hyatti and Formica integroides. I found Temnothorax on the Manzanita bushes in the scrub about halfway up. Another cool thing about this trail is that P.S. Ward went through it in the 90's on an ant collection campaign and identified over 27 different species in the valley, including Pogonomyrmex subdentatus, Pheidole californica, and Crematogaster coarctica.
Tennessee Valley Trails, near Mill Valley - Great spot for Camponotus after dark, I've seen at least three species. Semitestaceus flies here in swarms. I find Formica in the Fusca group here as well, along with Aphaenogaster occidentalis. I find Camponotus semitestaceus and Camponotus vicinus queens just after dark here when they fly. You'll have to avoid stepping on them throughout certain trails as they can get really plentiful. Most of the Formica I see are on the road leading up to the trailhead and Miwok Stables, not in the valley itself.
Mount Tamalpais, near Mill Valley - All around great spot for ants. Most of it is undisturbed wilderness with hiking trails running through it. Good spot for Camponotus, amazing spot for Aphaenogaster occidentalis (lift up wet logs), decent Formica diversity, big Veromessor andrei trails, and I caught a Liomepotum occidentale queen there last year. You'll find plenty of Tapinoma sessile too, if you're into that. Despite having spent so much time up on the mountain, I was mostly photographing amphibians. I haven't had a good chance to really scope out ants up there, but I suspect there's a lot more than I've listed.
Edgewood Natural Preserve, near Redwood City - Veromessor andrei are the stars of this place. Instead of being the regular red & black variety that you find everywhere else in the bay area, the Veromessor andrei here are pure black. Good spot for Prenolepis imparis too, I caught a queen there in February. Camponotus vicinus and a few Formica species can be found here too. Unfortunately, a few areas are overrun with Linepithema humile.
