Why We need convert addresses to geographic coordinates?
Today the logistics problems is a main issue in almost companies. An important part of the cost of transport includes the direct shipping cost of routes. Also there are a numerous online services that guides us to select the optimum directions such as Google map, Waze, Bing, MAP and etc.
Although these facilities are easily accessible, the logistics problems are very complicated. In a company that carries more 10,000 mail transactions a day the incomplete information leads to increase the costs. For example if a post agency write an address wrong, it will occur sequential systematic errors. so we must design an address verification system.
Also if we don’t have a correct address we can not find the best and optimum route. So its necessary to implement an automatic system to convert a address to geographic coordinate and a reverse one.
Whats is our solution?
well, First of all you need to use the information of all location on the earth in the online services that i mentioned above. there are a range of articles that outline the advantages and disadvantages of each. this solution we use the online services from MAP. you might be interested to use another one.
After registration and getting a private key called API key,You should review how to send and receive orders to the site. the services support JSON and XML usually. in this case we have used the JSON.
It is highly recommended to use JSON online formatting software. Here is an example of these sites:
Description of our problem in a logistics and transportation company:
There are a huge number of transaction are registered by several agent around country daily. each record at least e fields as the following:
1. ID: It s unique and the type of this fields is number (required)
2. Addr: It is the Address of the destination and the type of this field is short text – in some cases it might be as a Long text, it depends on the maximum length of addresses. (required)
3. Branch: The nearest agency of the destination and the type of this field is short text. (Optional)
In Microsoft Access it’s normal to set ID as a Auto-Number field. so it will get a new number to each new records.
Then in the design form we set a button to send the request to the services and get the result in a JSON format.
before starting the programming you must check what method is consider to get a valid answer in the web services.
there are 2 method to send the request:
It is important to check what method mentions on the website MAP.
As the website tells the POST is the method. The rest of solution is related to VBA programming.
First of all you need a JSON converter to convert the JSON to the proper VBA code. you must copy and paste the following file on your projects as s new module and name it “JSONConverter”.
after download you must extract it to a .bas file.
JsonConverter password: smartmng.ir