Database Design, Google Maps, and Address Fields

Question

I want to collect the addresses of my users so that I can plot them on a Google Map. I know I need to store the lat/long values of their address, which I can get from Google Map API.

I'm looking for recommendations on how to divide the various address parts and save them to the database. I commonly see things like this:

• Address Line 1
• Address Line 2
• City
• State/Region/Province
• ZIP/Postal Code
• Country

Google breaks down these address components differently, though. See, for example: http://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&sensor=false

I'm not sure what parts of Google address components equate to what is commonly seen in web forms (e.g. is administrative_area_level_1 always the state/region/province?). I'd like to store the various address components as atomically as possible so that I have the greatest control when displaying the address information later on.

NOTE: I also plan to store the formatted_address as I think that could be useful in some cases.

So, what should I store in my database?

Answer 1

This section of the Geocoding documentation provides a pretty good description of the types of data you get back from the reverse geocoder. These data types were developed by Google to describe any address in the world, so are probably a good starting point.

Based on the following quote the administrative_area_level_1 describes subnational jurisdictions, states in the US/Australia, prefectures in Japan, provinces in france etc:

administrative_area_level_1 indicates a first-order civil entity below the country level. Within the United States, these administrative levels are states. Not all nations exhibit these administrative levels.

You will probably need to be careful about the assumptions you make about these datatypes for other countries. For instance, the administrative_area_level_1 for addresses in London is England. But with a good understanding of this schema, you should be able to render locale friendly addresses anywhere in the world.