Well, you need a database with two columns, one being the name of a city and the other the corresponding URL. Then it's elementary db programming to look up the url on the city name, and redirect to it.

Of course, it's hard to see the point of all this. Why can't I just type "San Francisco" into Google?

No, that's true of course. But if there is just a list of pages for cities, why not simply list them as links? There are bound to be marginal cases, where people are not sure if their own city merits a page...