Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 4 down vote favorite
1

I have a big list of addresses of businesses that I am trying to "normalize".

What keeps happening is that, every now and then I verify the address of the place by going to their website and all too often, what is within my database as "100 Forest Park Drive # 100" is really "100 Forest Park Drive Suite 100"

I have not yet come across any entry where I have it written as Suite which is in reality #

I am not an expert, but it seems to me, that the # sign is interchangeable with "Suite".

Maybe the experts can chime in, and help me. In your experience, are there are any places where I better leave the # sign intact, instead of running a sql replace function to # to Suite.

Some research I have conducted in google suggests to me, that even USPS suggest that I only use # in absense of an Apartment, or Suite. And it just seems to be that Suite is more likely in a business environment, since if a business leases a space within a building, it would be more likely called a "Suite".

As an evidence, when I used to live in an apartment, if I used Suite instead of 123 SW apartment drive suite 20 the mail was delivered correctly to me 100% of the time.

As an added bonus, Suite looks much cleaner.

EDITS

I should have added that I already have all of these addresses geocoded, and the use case is not to be used as a mailer of any form. Simply as search.

And I did not intent to purely use find a replace. Running regular expressions on my address strings is how I discovered the issue in the first place when I started to discover many variations.

Take for example: 5619 DTC Parkway, Greenwood Village, CO

I have three businesses on this address in my database and the format that they use.

  1. Podoll & Podoll (Suite 400)
  2. EPS Settlements Group (Suite 600)
  3. Frank Patternson and Associates. (# 1100)

The data in the parenthesis is a exact copy of the information on their website and my database. (Suite & #)

share|improve this question

3 Answers

up vote 5 down vote accepted

This is hardly a GIS issue, but to humour you, it partially depends on the local standards set by the postal service where you're from. The majority of the weight of this issue comes from the validity and cleanliness of your original data and the flow on effects your change will have.

In my opinion, the '#' sign is as much a substitute for 'Suite' insofar as 'Ocean' is a substitute for 'River'.

You would be wise not to mess with the original data unless you've evaluated all possible cases that could arise. Consider the following:

Example 1

Original Value: '#6, Level 10 #72 George St'

By your standards we would exchange the address for the following:

Second Value: 'Suite 6, Level 10 Suite 72 George St'

Likewise, the following series of replacements demonstrates the flaw in your logic:

Example 2

Original Value: 'Suite #6 #72 George St'

Second Value: 'Suite Suite 6 Suite 72 George St' (What?)

Example 3

Original Value: '#106,100 George St'

Second Value: 'Suite 106,100 George St' (106, 100 or 106000?)

I'm sure you can see where I'm going with this.

If you're going to employ a system whereby you're changing data, it needs to be non-arbitrary and your method should be proven for all possible cases. You can't just run a find / replace and expect everything to be OK. To do this, you need to have a handle on regex as it's the most efficient way to locate and replace strings. You also need to have systems in place to clean the data on the way in in a consistent manner. Lastly, you need to track the lineage of the data in case something goes wrong.

Basically, the amount of the work you need to do to implement this solution is more than likely going to outweigh your reason for doing it, or your manager's reason for wanting to do it (which I'm assuming is purely aesthetic).

share|improve this answer
2  
+1 Very good advice presented extremely well: thanks! – whuber♦ Jul 3 '12 at 13:32
Regex is how I discovered the problem in the first place. I edited to show that I already have lat/lng for these address strings and my use case is online search. I do have the data segregated by street,city,state. So I don't have to worry too much about Suite Suite 600, 100 example st. – John Jul 3 '12 at 20:48
Also, it was my decision (no manager here). What happens is that I run a SQL query and order by street. Like the example I mentioned above in my edit, 2/3 businesses on a street in my DB use Suite, and one uses # (verified from their website). In that case, would it not be prudent for me to replace the # with a suite for just that street. "5619 DTC Parkway". Example, using regex, all instances of "#" to be replace with Suite for street of "5619 DTC Parkway" in Greenwood Village, CO. – John Jul 3 '12 at 20:58
@John You have two options (one really if you need efficiency): 1) Explode your address into components (ie. one column for each building, floor, unit prefix, unit, street no, street name, street type, city, suburb, post code, state etc.) 2) (slow) would be to run your query, then post-process the list to identify street names and sort by what appears to be the street name. Given that you're going to identify street names, you might as well explode your data at this point. – Geoist Jul 3 '12 at 22:49
I was thinking of doing something similar. Thanks. – John Jul 3 '12 at 23:12
up vote 1 down vote

The use or creation of a Address Standardizer is highly advisable in this instance. These usually use copies of the USPS AIS/AMS Databases that include references to that are used to correct address sub-units.

Productions such as Pitney-Bowes Centrus or MelissaData have address standardization and clean-up processes that can help you make sure the sub-unit in your addresses are correct per the USPS DPV data.

share|improve this answer
I did use a standardizer that lets my run 10 examples from Qas.com and zipcode lookup usps. What ended up happening is that QAS would in 9/10 cases return # replaced by Suite. USPS would take the # 60, and Suite 60 without any issues. – John Jul 3 '12 at 20:51
up vote -1 down vote

I see no problem with substitution.

While there is no canonical reference I know of regarding US addresses, let alone international ones. This should be a safe switch.

I have run across some businesses that use Ste as a shortened version of Suite. I think this is to make sure it fits on one line of some standard address label or within some database field.

share|improve this answer
2  
Actually yes there is; the USPS maintains a list of appropriate sub-unit types that can translate to APT, FLR, BLDG, TRLR etc. So just assuming and doing a wholesale replacement of locations is not always advisable. – D.E.Wright Jul 3 '12 at 16:41
Having scanned in thousands of business cards, and pulled many more from websites, I can say there is no standard. I am always amazed by the formatting I see. – mhoran_psprep Jul 3 '12 at 17:00
1  
You are confusing the inability of people to use their correctly formatted address, with the lack of standards. – Stevetech Jul 3 '12 at 17:08
Correct Steve, that is the underlying issue with most of these cases is people put a shortened format on a card to save costs, but when it comes to a letter being sent to that address and passing through a CASS certified standardizer for mass-mailing that address is cleaned-up to standards. – D.E.Wright Jul 3 '12 at 17:16
I added some information in the original post. Basically, I am not using this to mail anything. But rather when I see three entries in my database using the same street but 2/3 using Suite and one using #. Those businesses are using the same entries on their website as well. So it's this case that made me try to see if anyone else has run through similar scenario and done the same thing I was thinking of doing. Mostly, if 2/3 businesses use Suite on "5616 DTC Parkway", then convert the remaining # entry into a suite, so that all of them are now using Suites. – John Jul 3 '12 at 21:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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