解决数据库中的标准化问题 [英] 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 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.
例如
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屋!