Street Name Database

[Macedonio Heninger]

Thereare 2 arguments that I've been given for why we should keep it as is:1. Searching is easier when you can search against JUST the street name or number etc. but I'm thinking that a sql script along the lines of SELECT x FROM Address WHERE streetAddress LIKE "%INPUT%"; Sure it's not as fast, but it would work (and the dataset for that search is only on customers is incredibly smaller than the set of all addresses we have stored).

Of course, it depends what industry you're in and what the information is being used for. If invalid address data isn't costing your company anything, then by all means store invalid data. Be aware though that down the road you may wish to use this data for mailings, demographic reports etc. If the data is invalid, it's not trivial to fix it after the fact.

Also, in reference to searching "Where StreetAddress Like '%whatever%'". This is all well and good if you're doing a quick search for your own benefit, but when you come to attempt to automate parts of your system that rely on address data or even attempt to drop duplicates, provide users with auto-suggest etc etc, performance is degraded to a point that it will become unusable the larger the address table.

If invalid addresses aren't a worry that is going to cost the company real cash, then it's not an issue - but then, if you're not using the addresses for anything that is beneficial financially (or likely to be in the future), then why are you storing that information in the first place?

Legal subdivisions (LSDs) are used primarly in Oil & Gas and other primary resource industries in Alberta, Saskatchewan and Manitoba (although they are found in parts of B.C. too, they're not in such prevalent use). They all take the same format: Section, Township, Range, Meridian. For example:

You could simply use a single field and parse it with regular expressions or break it out into separate fields containing the breakdown of the LSD. Running regexes in SQL Server can be a pain when it comes to performance. My take on it is the same as that of address data in general, that because each piece of data is a separate unique piece of data that they should be stored in separate fields. However, given that the large majority of this type of address data is not used by the general public in lieu of a street address, I might recommend designing something that would allow this information to be separated from (but linked to) your main address data. Given however that the land description/LSD is also part of every Canadian address, I might be tempted to store it in my main address table depending on the target audience of the database.

One thing you will often find in Oil & Gas at least (which is where the bulk of my experience comes from) is that workers will often refer to only the first two parts of the LSD - i.e. 28 of 12, or 43 of 16. The remainder of the LSD is implied by the locality of the address - i.e. Grand Prairie, Fox Creek, Wolf Lake etc.

I used to think that was a good idea, until my applications were deployed and a constant stream of requests came in for changes. At the time, I lived in Ontario, Canada and I thought that I knew what a standard address looked like. Until some customer had an address that combined the P.O. Box and the street address into one. Then the Alberta customers started coming in with their structured codes mentioned in another answer. Then British Columbia adresses where there was no street or street number, just a Site and Compartment and Rural Route. C4,S16 RR7 Mountainville. And then with American suppliers, the postal code rules went out the window. And then the occasional British customer appeared in the database and everything that you thought you knew about addresses goes out the window. A building name with no street number, two street names, two town names all in one address!

That is a made up example, but they do exist. The British manage to get by because every local company has an up to date national address database and all they need is the postcode and house name or number. The rest is filled in from the database.

In the case of that address, there is probably another Waverly Crescent in Seething-under-Norton, which is why the second street name. And Seething-under-Norton was a village which long became incorporated into the town of Banbury, so both names are in the address. In British addresses you often get municipalities which do not exist. They are considered postal towns in that they exist only within the postal system. There is usually a historical basis for the name. Lots of London addresses are like that with people writing London one time, and Leyton or South Ruislip or Hillingdon another time. The letters all do get delivered promptly.

By the way, you mentioned identifying all the people on the same street by the street name. Have you checked out Denver Colorado where there are street names which end and pick up again, a mile further away. I once got lost in Littleton (Denver suburb) trying to find a certain address only to be told that I needed another such-and-such street which was elsewhere. Then there is the British practice of using two or more names for every road. For instance, there will be a Homerton Road which is then named Marsh Hill and then Homerton High Street and then Urswick Road and then Lower Clapton Road all in the space of a kilometer or two. More commonly, in the village of Wick there will be a Norton Road. If you follow it, after a mile or two you will note that your are now on Wick Road, entering the village of Norton.

Not the least of your problems is going to be training/forcing users to respect all the separate fields you give them to enter all the different parts that make up and address in a consistent format - most people just don't think of a street address being made up of up to 5 different parts, and will likely just enter stuff like they usually do.

In Europe, the street address is usually a name plus a "number" (where number can be something like "3a"). I've seen databases which store them separately for a single reason: You can look up the street names in an official database to verify them (for example to protect against typos). So for this use case, it makes sense to keep the verifiable and the non-verifiable parts in different columns.

Is a benefit if you are following an Objected Oriented approach for modeling your whole domain. Your question reminds me this blog titleMarch is not a number as an answer. Something analogue could be say about streets and addresses ("a street is not a string"). SnOrfus points out a valid problem on his comment.

While their may be advantages to storing each component of an address independently, you'll have to weigh the cost against your business needs and requirements. If you're not doing anything related to mailing or shipping, it may be overkill and complicate aspects of your architecture significantly. Furthermore, anyone else that works on your code may not understand what's going on and make introduce significant problems without realizing it, thus corrupting the database.

In this case, "PO Box" is actually the street name while 12345 is the primary number. Normal "formatting" and conventional wisdom suggest that an address should have the primary number listed first, as in "123 Main Street".

This is where address verification and standardization come in. At least within the United States and a few other nations modern nations, including Great Britain, you have the advantage of being able to submit the address to an online address verification service which can clean, standardize, and verify your address. Oftentimes, these services will give back the address as it should appear on the mail piece as well as the component parts of the address. If you have a business need for the components, then you can store them independently. Otherwise, another call to the address verification web service should yield the components again at the desired time.

In the interest of full disclosure, I'm the founder of SmartyStreets. We offer US-based address verification services which include CASS-Certified validation of your addresses. You're more than welcome to contact me personally with any questions you have.

Address Management Services reviews and reserves street names to be applied to right of way, easements or private drives throughout Travis County. Not all accesses may be named. Duplicate and/or similar-sounding street names are prevented and continuity with existing streets is maintained. For a complete list of street naming rules, as well as an explanation of the street name reservation process, see the Austin and Travis County Street Name Standards. To prevent duplication, 'Reserved' and 'Active' street names can be viewed below in the Street Name Database.

Street Name Database

The Street Name Database includes City of Austin and Travis County streets that are either in use or reserved for use. If a name appears in this list, it may not be used for another street. The database is updated on a nightly basis.

With information drawn from a 1960 annual of the member-supported Historical Society, the searchable database is a drop-down menu (above) that captures information on how New Canaan streets got their names, in a snapshot from that year.

I knew about the Monastery as there are still parts and stairs to it in the woods. Our house was built from 92-94. I just think that it would be great to eventually include all of the street names! Thank you for the information!

Thank you for this information. My great grandfather was a Monk at the Franciscan Monastery back in 1934. I was looking for information on the Monastery and perhaps finding some information on him. He was born in Ireland and we are trying to trace our family tree.

These instructions will help you locate your property information on our real property data system. You can search the online database by either the address, street name, account identifier or map reference. You can not search by owner's name, town, neighborhood, subdivision or zip code.

3a8082e126