two that are fairly similar. One that is no longer there Meigs Field(screw Mayor Daley) and Albert Whited (SPG) in St. Petersburg, FL. SPG is right on Tampa Bay. Another cool one is the approach to Rwy 3 or Rwy 1 at Kansas City Downtown(MKC). As you come into the airport from this side you come directly over the top of the Buildings in Downtown KC.
Favorite approach is Telluride, CO. Went there this summer and man its gorgeous. I also like Lakefornt-New Orleans, though its not that scenic its a fun approach to fly over the water (unless you're in a 152).
I've flown the river route into DCA a few times (Prior to 9-11), its fun as the flying pilot, but as the non flying pilot youre busy checking points and altitudes. I used to go into DCA about 2 times a month, and unless WX was an issue we took the helicopter, now that's fun. Also flying into Manhattan ( 30th street pad ) in the helicopter was a blast. I find any approach path over water is neat exspecially if its in the caribean.
The "funnest" approach I get to do is at Guantanamo Bay, Cuba. The last reporting point is 20 miles south of Gitmo and you enter the right base for rwy 10, the difference is you CAN'T over fly the "fence" located 3/4 of mile from the end of the runway, so base to final happens in that space or less. I bet its alot more fun in a big airplane like the C-141 or C-5s.
The approach into HNL sounds like it would be gorgeous<(spelling?). Went there many years ago as a pax and was too drunk on free champagne to notice anything out the window.
There is a grass strip here in Ore at a airport called the Flying M Ranch and it is in a box canyon, very tight and unforgiving with a horse corale fence at the end of the rwy so make it right or crunch. It has a short strip, so on t/o you also have to clear the trees. But, it is beautiful to fly into or out of. Try and find it on MSflight sim, if your into it, and give it a go. Do it during the fall for those lovely colors on the mountain sides.
I personally like the ILS to TOA as dusk. If you cross seal beach you are given vectors to the approach right over the long beach harbor, with the queen mary on your right and all the city lights its just great!!!
Another fun one is Santa Paula... the approach is only entered on extended downwind because of a mountain just south of the field. Downwind is flown right next to it pretty close to the field... base is a quick turn, followed by final over some trees.. fun place!