At least according to Wikipedia, these types of sites can be referred to as geolocation-oriented photo sharing sites.

Here is the list of what I use:

• Flickr Map
• Google Maps(Click on "Photos" feature)
• Photosynth on Bing Maps(I click into it from the PS site)
• Google Earth
• Panoramio(The same as the two above) - More info at StackOverFlow
• Stuck On Earth(iPad)
• Fotopedia Heritage
• Fotopedia National Parks
• SmugMug Map
• Globitude(I haven't actually used this one)

This next one gets it's own category, because it simply is indispensable for Landscape photography especially, and really any location scouting:

• Photographers Ephemeris(sun and moon calculator)

Also I know that I did not directly answer your question. It is hard for tool to answer the "best photography locations" question - because clearly for each individual the experience can be different. I wouldn't trust some "Yelp" or "TripAdvisor" like review site for locations, I want to see the photos! So to me the above fulfills that need. You can use the different tools to drill down into certain keywords or popular photos in an area, and simply find something that appeals to your eye, and not the text from someone elses reviews! Further - You are probably better off using something like TripAdvisor to just find global destinations to travel to, then use the above to find the actual photo locations that interest you.