解决数据库中的标准化问题

本文介绍了解决数据库中的标准化问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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

例如包括地址如：

• 500 W Main St
• 500 West Main St
• li>

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

你得到了。

我考虑运行一个查询所有的记录，其中左（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.

是否有函数或查询

推荐答案

这是一个可以存在多个地址的记录列表，一个棘手的业务...等于黑色魔法和科学。您将惊叹于单独的Boulevard的变化。

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.

例如

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

部分返回

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

和好消息是

maps.googleapis/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", "maps.googleapis/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" }