25

What's a correct format of a geographical address/location which is a good fit for any address on the Earth? At the moment I have:

  • country
  • city
  • street
  • number
  • text data (for simplicity)
  • zip
  • lat/lng

But I believe I can improve it: there might be a state/region of a country or something like area. Or no area/region/state, say, in Singapore or Hong Kong.

There might be no street, but road or boulevard or something else. A number of a building might be compound. There might be a floor. A room number. Etc....

closed as too broad by Basile Starynkevitch, gnat, user22815, Greg Burghardt, 8bittree Sep 25 '17 at 22:05

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 11
    You need to explain for what application, and who is providing that address. E.g. on most Web commercial stores/websites, I don't type any "latitude/longitude" which on the contrary is essential for ICBMs (or GPS). Also, altitude (and time and date) is important in some cases (think of some ship at sea, or some traveler on the Everest). So I am not sure there is any universal answer. – Basile Starynkevitch Sep 23 '17 at 9:03
  • 61
  • 6
    @BasileStarynkevitch: I think it is not so much important "for what application", but "for what use case(s)". If, for example, the use case is to make sure world wide postal services can deliver mails, I guess this question can be answered in a sensible manner. However, for this use case "lat/lng" won't be required. – Doc Brown Sep 23 '17 at 12:49
  • 34
    I think the universal format for an address is a single String. – Erik Eidt Sep 23 '17 at 15:17
  • 12
    The problem you raise is so painful, that some companies out there develop their universal way to address it, for example: what3words.com (boils down to mapping location coordinates to three words). They claim, that "With what3words, everyone and everywhere now has an address." – Roman Susi Sep 23 '17 at 19:13
51

Google has developed a library that helps validate postal addresses for every country in the world, which you can use to design a schema to store this data.

Look for the most common required fields across addresses from your targeted customer base to get started, and as you identify further countries with different requirements you can continue to adjust your schema.

42

The universal way to store a geographical address/location in a database is this one:

[Address] nvarchar(max) not null

This requires the least amount of programming code (and so cuts maintenance costs) and is fully compatible with any address. It has, however, three big issues:

  • The lack of data validation means that the field can be used for the purposes other than storing the address. One of the purposes is a DOS attack intended to fill the space of your database by entering 2 GB of data in the address field.

  • The data stored this way makes it impossible to process it for business intelligence and data mining purposes. For instance, how many users are from India? There is no easy way to tell, since those addresses won't be normalized.

  • The users may mistakenly enter an incomplete or plainly wrong address.

In order to mitigate the first issue, limit the field to what you think to be a reasonable limit. Personally, I would start with 1000 characters, and then reduce it based on the length of the addresses entered by the first users once you get a data set large enough.

In order to mitigate the other two issues, you can use a third-party API which parses addresses and presents you with the data containing the country, city, postal code, etc. If possible, the API should be able to display the address on a map back to the user to reduce the risk for the user of entering an incomplete or wrong address: most users know where they live, and seeing a different position on a map would immediately give them a clue that they should check their input.

Note that whatever API you use, it won't be perfect. It will find most addresses, but not all of them. This means that if the API tells that the address doesn't exist, but the user insists that it does, you should a priori trust the user, even if he might be wrong.

This also means that you still should store the original user's input, side-by-side with the result of the API. This means that the schema becomes:

[RawAddress] nvarchar(max) not null
[ParsedAddress] xml null
  • Note: At the very least, you could store the country separately, if this is necessary. For example, it could be automatically deduced from the address field, with the option for the user to change it. – Matthieu M. Sep 25 '17 at 17:01
  • 'use an API' just means somone else has got all the countrys' official formats. Theres no reason you cant do it yourself – Ewan Sep 25 '17 at 21:00
  • @Ewan No reasons except for time, money, language, and other barriers. – Andrew Piliser Sep 25 '17 at 22:05
  • sure, but are we providing answers on how to do stuff or comparing pricing of other people doing stuff for you? – Ewan Sep 25 '17 at 22:11
  • @Ewan: the question is about the storage format of addresses. The API doesn't dictate this format: the goal of my answer is to show that as soon as you have a plain text field and a XML/JSON/whatever field for parsed data, you can both store and statistically process an address from anywhere in the world. – Arseni Mourzenko Sep 26 '17 at 12:22
37

There isn't one.

Every country has different address formats. If you are lucky, and they have a format at all!

Obviously latitude/longitude will give you a point on the globe, but it's not really useful for identifying individual houses. Just consider a tower block for example.

Your best bet is to check each countries postal service for an official format. This can be great for your backend database. But you will probably have to simplify it for end users as it will contain many more fields than most people are used to.

The UK one for example includes things like 'double dependant locality', but no one would know what that meant if you asked them.

  • 3
    What's a universal way........... – Xwaro Sep 23 '17 at 12:33
  • 40
    @Xwaro They just said, There isn't one. – Zymus Sep 23 '17 at 16:03
  • 6
    I guess Xwaro means I am assuming addresses on earth. – Ewan Sep 23 '17 at 16:13
  • 3
    This is the official source for printed address formats: Universal Postal Union – grahamj42 Sep 24 '17 at 9:58
  • 3
    interesting. I think this is the relevant page though : upu.int/en/activities/addressing/s42-standard/… you can see how A: its only a few countries, and B: the mapping from s42 to the countries address format is not 1 to 1 – Ewan Sep 24 '17 at 13:48
21

The only universal format is to have a single text field which may have multiple lines of text. This will allow any possible address on earth.

  • 2
    Great, now everybody can describe the same address in a different, incompatible way. I suppose the question didn't ask about standards, so this is technically a correct answer. – Michael Sep 25 '17 at 18:55
  • @Michael: Addresses are different and incompatible across the world. There is no standard template. Having a multiple-line field allow the user to actually write the correct address. – JacquesB Sep 26 '17 at 7:24
  • @Michael Separate fields often force me to truncate/abbreviate one field or the other, which also leads to inconsistent representations. (Still works usually, postal services are quite experienced at this). – Hulk Sep 26 '17 at 8:57
  • Confirmed: en.wikipedia.org/wiki/Address_(geography) – Eric.Void Sep 26 '17 at 19:14
  • Just an interesting tidbit, this isn't technically true. In some areas of countries, parts of addresses are drawn as pictures. – KayakinKoder May 27 at 20:06
8

I have been developing software solutions to be used in many countries. We address this issue by starting with the larger entity first, i.e. country then have fields down to the least common or smallest. It works well for all countries we have experimented with so far. We also have a smart duplicate prevention system, and merger for those that have somehow get in the system since users are very 'creative'. In the admin section we have an address field order per country setting. i.e. Japan has the Post/Zip-code first where as UK/US last.

In general, we use:

  • Country
  • Post/Zip-code
  • State/Province/Prefecture/County
  • City/Town/Village
  • Street/Road/Block
  • Building Name/Number
  • Specific/Custom Information

Once entered and saved, a conjugated version can be displayed leaving out fields not necessary.

As I said, this works for all those countries we have software had software in and is the result of developing since 1989.

Hope this helps somehow or at least provides another insight.

  • how do you name a column in your db for "State/Province/Prefecture/County"? – Xwaro Sep 24 '17 at 1:17
  • 6
    @Xwaro It doesn't matter, name it whatever word you feel your developers will be least confused by. This is because the name is internal to your software and will never be seen by users. Address are never displayed with the name of the field. That is, you never see No 10 Street Downing Street, City Westminster, State London, Country UK. Instead you will see 10 Downing Street, Westminster, London, UK – slebetman Sep 25 '17 at 0:56
  • @slebetman The question was: how do you name a column in your db for "State/Province/Prefecture/County"? Not "how do you recommend me to name a column in my db for "State/Province/Prefecture/County"? – Dari Oct 3 '17 at 3:30
  • @Dari It doesn't matter, I name it whatever word I feel my developers will be least confused by. This is because the name is internal to my software and will never be seen by users. So it depends on what my team is used to. – slebetman Oct 3 '17 at 3:35
  • @slebetman - what do you name it? – Dari Oct 3 '17 at 4:07
0

As already stated, the most universal (but impractical to validate and perhaps least useful) is a single big unicode field.

You could separate country from the rest of the address and store it as the ISO country code. It would normalize the country and offer some utility in validating the remainder of the address.

You could also separate postal code aka zip code from the rest of the address. This would also have some utility in validating the remainder of the address, and could be helpful (though imprecise) in geolocation. For example: in Canada you can uniquely identify any address specifying only postal code and street number (aka house number); this may not be true in all countries.

Dedicating fields to states/provinces or cities starts to get more problematic because of the variations in the way each country formulates an address. I've set up address tables having such fields because the initial audience is focused on North America, knowing that an international audiences would pose a problem fitting in. In most cases, they can be "shoe-horned" in, but it is an awkward and potentially failure-prone compromise - definitely not universal.

0

Contrary to Mitchdav's answer, I would advise against using Google's library. I searched the repository for various international places with unorthodox addressing schemes hoping to find unit test data, but worryingly I found zero hits in the entire repository.

I think your best bet is to treat an address as free-form multi-line text. It sucks that you maybe cannot validate all addresses, but some addressing formats are really weird and possibly unanticipated and in the end the responsibility of filling in the correct address rests on the user and in most applications the user bears any negative consequences of filling in an invalid address.

You might, maybe, use a validator to provide a warning, but nothing more than that. But don't reject addresses that don't validate, because otherwise you might lose some customers. Which leads to the question of how to communicate the warning to the user in such a way that it will communicate that, if the user lives in an area with a weird address format, it's safe to ignore the warning...

-1

As you say any address on earth there's only lat long or...

https://what3words.com

What 3 words, is an algorithm (so not a database so can be embedded into anything) that can define a 3x3 metre patch of anywhere on Earth.

Tonga and a few other states have adopted it as their postcode system, whilst it'll not replace it as an overlay its pretty cool, and very well built and thought out.

Not the answer you're looking for? Browse other questions tagged or ask your own question.