Address standardization within a database

Question

Working in MS Access 2013. Have a ton of locations/addresses which need to be standardized.

Examples include addresses like:

500 W Main St
500 West Main St
500 West Main Street

You get the point.

I've considered running a query that pulls all records where the left(7) or something characters exist more than once in the database, but there are obvious flaws in that logic.

Is there a function or query or anything else that would help me generate a list of records whose addresses may exist multiple times, in slightly different fashions?

I don't think such function exists. You gotta handle it yourself. — Gurwinder Singh, Dec 20 '16 at 19:22

score 14 · Accepted Answer · edited Dec 20 '16 at 22:47

This is a tricky business ... equal parts Black Magic and Science. You will be amazed at the variations of Boulevard alone.

This is why I use the Google API. It can be time consuming, for the initial data-set, but only new adds would need to be resolved.

For example

https://maps.googleapis.com/maps/api/geocode/json?address=500 S Main St,Providence RI 02903

returns, in part

"formatted_address" : "500 S Main St, Providence, RI 02903, USA"

and the GOOD News is

https://maps.googleapis.com/maps/api/geocode/json?address=500 South Main Steet,Providence RI 02903

returns the same formatted address as the previous query

"formatted_address" : "500 S Main St, Providence, RI 02903, USA"

VBA Example:

Upon executing the following code ...

' VBA project Reference required:
' Microsoft XML, v3.0

Dim httpReq As New MSXML2.ServerXMLHTTP
httpReq.Open "GET", "https://maps.googleapis.com/maps/api/geocode/json?address=500 South Main Steet,Providence RI 02903", False
httpReq.send
Dim response As String
response = httpReq.responseText

... the string variable response contains the following JSON data:

{
   "results" : [
      {
         "address_components" : [
            {
               "long_name" : "500",
               "short_name" : "500",
               "types" : [ "street_number" ]
            },
            {
               "long_name" : "South Main Street",
               "short_name" : "S Main St",
               "types" : [ "route" ]
            },
            {
               "long_name" : "Fox Point",
               "short_name" : "Fox Point",
               "types" : [ "neighborhood", "political" ]
            },
            {
               "long_name" : "Providence",
               "short_name" : "Providence",
               "types" : [ "locality", "political" ]
            },
            {
               "long_name" : "Providence County",
               "short_name" : "Providence County",
               "types" : [ "administrative_area_level_2", "political" ]
            },
            {
               "long_name" : "Rhode Island",
               "short_name" : "RI",
               "types" : [ "administrative_area_level_1", "political" ]
            },
            {
               "long_name" : "United States",
               "short_name" : "US",
               "types" : [ "country", "political" ]
            },
            {
               "long_name" : "02903",
               "short_name" : "02903",
               "types" : [ "postal_code" ]
            },
            {
               "long_name" : "2915",
               "short_name" : "2915",
               "types" : [ "postal_code_suffix" ]
            }
         ],
         "formatted_address" : "500 S Main St, Providence, RI 02903, USA",
         "geometry" : {
            "bounds" : {
               "northeast" : {
                  "lat" : 41.82055829999999,
                  "lng" : -71.4028137
               },
               "southwest" : {
                  "lat" : 41.8204014,
                  "lng" : -71.40319219999999
               }
            },
            "location" : {
               "lat" : 41.8204799,
               "lng" : -71.40300289999999
            },
            "location_type" : "ROOFTOP",
            "viewport" : {
               "northeast" : {
                  "lat" : 41.8218288302915,
                  "lng" : -71.40165396970851
               },
               "southwest" : {
                  "lat" : 41.8191308697085,
                  "lng" : -71.40435193029151
               }
            }
         },
         "partial_match" : true,
         "place_id" : "ChIJicPQAT9F5IkRfq2njkYqZtE",
         "types" : [ "premise" ]
      }
   ],
   "status" : "OK"
}

@mcNets Failed to mention, you may have 2,000 hits per day, but the expanded access is very cheap. ALSO you get LAT/LNG with it as well — John Cappelletti, Dec 20 '16 at 19:43
I'm having some issue with Power BI maps, and this method can really help me. Even if I need two weeks to update my database. — McNets, Dec 20 '16 at 19:46
@mcNets Just a thought, You may be able to parse the work over several machines. I used this approach for tens of thousands of address which were HORRIBLE. Now, it is not 100% ... GIGO... but we had a nominal scrub afterwards. — John Cappelletti, Dec 20 '16 at 19:53
What a great tip! It even works for our European and localised addresses. — Gustav, Dec 20 '16 at 20:26
@JohnCappelletti - So how would the OP add this to Access? Does it require special registration? Do you just pass it the address from a textbox? It's a neat solution, but it's not a complete answer. If he's a n00b, he's not going to know what to do with this. — Johnny Bones, Dec 20 '16 at 20:48
@JohnnyBones You can use the Json modules from my project (for another purpose): [VBA.CVRAPI](https://github.com/CactusData/VBA.CVRAPI) — Gustav, Dec 21 '16 at 07:54
Gord - Thanks for providing an example, I'm thinking of adding this to a couple of the apps I use here. John - Thanks for the suggestion of using this method. I didn't even know it was a possibility. — Johnny Bones, Dec 21 '16 at 13:25

score 0 · Answer 2 · answered Jun 29 '20 at 23:20

John's answer is completely valid, I wanted to add also that you can achieve the same goal with HERE API. You can do this for free with HERE Maps and you won't need a credit card to get started.

https://geocode.search.hereapi.com/v1/geocode?q=500 West Main Street&apiKey=YOUR_API_KEY

Returns:

{
    "items": [
        {
            "title": "500 W Main St, Alhambra, CA 91801-3308, United States",
            "id": "here:af:streetsection:-2rEzgpCkFyX.gMQjWtV1A:CgcIBCCl6q07EAEaAzUwMChk",
            "resultType": "houseNumber",
            "houseNumberType": "PA",
            "address": {
                "label": "500 W Main St, Alhambra, CA 91801-3308, United States",
                "countryCode": "USA",
                "countryName": "United States",
                "state": "California",
                "county": "Los Angeles",
                "city": "Alhambra",
                "street": "W Main St",
                "postalCode": "91801-3308",
                "houseNumber": "500"
            },
            "position": {
                "lat": 34.09193,
                "lng": -118.13238
            },
            "access": [
                {
                    "lat": 34.09241,
                    "lng": -118.13272
                }
            ],
            "mapView": {
                "west": -118.13347,
                "south": 34.09103,
                "east": -118.13129,
                "north": 34.09283
            },
            "scoring": {
                "queryScore": 1.0,
                "fieldScore": {
                    "streets": [
                        1.0
                    ],
                    "houseNumber": 1.0
                }
            }
        },
        additional results...

So you can normalize your data based on the title.

Address standardization within a database

2 Answers2

Linked