数据库中的地址标准化 [英] Address standardization within a database

问题描述

在 MS Access 2013 中工作.有大量需要标准化的位置/地址.

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

示例包括以下地址:

• 500 瓦主街
• 500 West Main St
• 西大街500号

你明白了.

我考虑过运行一个查询来提取数据库中多次出现 left(7) 或某些字符的所有记录，但该逻辑存在明显缺陷.

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?

推荐答案

这是一件棘手的事情...... Black Magic 和 Science 是同等重要的.光是林荫大道的变化，您就会惊叹不已.

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

这就是我使用 Google API 的原因.对于初始数据集来说，这可能很耗时，但只需要解决新添加的问题.

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.

例如

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

部分返回

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

好消息是

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 示例:

执行以下代码后...

' 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

...字符串变量 response 包含以下 JSON 数据:

... 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"
}

这篇关于数据库中的地址标准化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！