Extracting an Address from a Postcode input

[Steve-vfp9user]

Hello all,

I am trying to find a way of a user entering a postcode for an address look up which I can later use when we create an order. I have tried the below but without success so I am asking for advice on what is wrong with the below or do the expert users know some other way to do this? When I run the code, there are no errors but I do not get a result which should be 10 Downing Street, London SW1A 2AA, UK.

I am only wanting addresses in the UK, here's what I have so far:

PROCEDURE GetAddressFromPostcode
LOCAL oHttp, cUrl, cResponse, cPostcode, cApiKey, cFormattedAddress

* Set your API key

cApiKey = "123456"    &&    This is a dummy API key (I do have Google API one)

cPostcode="SW1A 2AA"    &&    This is 10 Downing Street as an exmple!

* Validate input

IF EMPTY(cPostcode)
   Wait "No postcode entered" WINDOW NOWAIT
    RETURN
ENDIF

* Build the API request URL

cUrl = "https://maps.googleapis.com/maps/api/place/textsearch/json?query=" + ;
  cPostcode + "&key=" + cApiKey
 
* Create HTTP request object

oHttp = CREATEOBJECT("WinHttp.WinHttpRequest.5.1")
oHttp.Open("GET", cUrl, .F.)
oHttp.Send()

* Get response from Google API

cResponse = oHttp.ResponseText

* Check if response is valid

IF EMPTY(cResponse) OR "error" $ cResponse
    =MESSAGEBOX("Invalid postcode or API error.", 48, "Error")
    RETURN
ENDIF

* Extract the formatted address
cFormattedAddress = ExtractFormattedAddress(cResponse)

* Show the result
IF EMPTY(cFormattedAddress)
=MESSAGEBOX("No address found for this postcode.", 48, "Not Found")
    ELSE
    MESSAGEBOX("Address Found: " + cFormattedAddress, 64, "Success")
ENDIF

ENDPROC


FUNCTION ExtractFormattedAddress(cJsonString)

LOCAL cAddress, nStart, nEnd

* Find the position of "formatted_address"

nStart = AT('"formatted_address" : "', cJsonString) + 22
nEnd = AT('"', cJsonString, nStart + 1)

* Extract the address
IF nStart > 22 AND nEnd > nStart
    cAddress = SUBSTR(cJsonString, nStart, nEnd - nStart)
ELSE
    cAddress = ""
ENDIF
RETURN cAddress

ENDFUNC

Much appreciated.

 

[Paul Gibson]

I can't test your code without a Google API key and I don't have one to hand so I am getting an API Key error but if we assume you are not getting the API Key error, what are you getting in the ResponseText property? Are you saying it's just empty?

The code you've written for extracting the address looks to be assuming you are getting JSON back and that is one of the parameters of your query, so the question is are you? When you step through the code, what does cResponse get set to after you put the ResponseText property into that variable?

One thing that I did spot is your test code has a postcode string with a space in the middle - do you want to replace the space with %20 instead to encode the space instead of sending it as a space in the URL? Maybe that's all you need to do to get it working for you. Some parameters accept a plus symbol (+) as well so if %20 isn't working for you try the plus symbol instead and see if either of those get you closer to your solution.

 

[costin77]

Here it is the response you are getting from your "https://maps.googleapis.com/maps/api/place/textsearch/json?query=...." link

{
"error_message" : "The provided API key is invalid.",
"html_attributions" : [],
"results" : [],
"status" : "REQUEST_DENIED"
}

 

[Paul Gibson]

Also, I spotted the following at the docs link: https://developers.google.com/maps/...e/search-text#maps_http_places_textsearch-txt

This is for the formatted_address property that you are trying to extract from the JSON, for the UK:

Often this address is equivalent to the postal address. Note that some countries, such as the United Kingdom, do not allow distribution of true postal addresses due to licensing restrictions.

So if you are getting a valid response but for the fact that the formatted_address property is empty, that explanation is probably why.

For us, we don't try to use Google Maps for postcode to address backfills we've used other suppliers, with dedicated APIs for that task. They've told us in the past that Royal Mail license that address data out and in our application we actually have to license that functionality per user because of the rules set out by the RM, enforced by the API provider. If you are finding that the formatted_address property is empty, it might be exactly for that reason.

 

[Steve-vfp9user]

I appreciate the replies and this appears to be a bit over the top of my head so we've decided to abandon the idea.

 

[Steve-vfp9user]

Thanks Chris

Here's another API key I created from Google: AIzaSyCu0ntR_yWtyrdB5PoU__OntPzF8RacwWs

 

[Chriss Miller]

So what do you get from this?

I get this:
{
"error_message" : "This API project is not authorized to use this API.",
"html_attributions" : [],
"results" : [],
"status" : "REQUEST_DENIED"
}

In google you set up projects, you create API keys for them, but this is not Lord of the Rings, there is not one ring (API key) to rule them all. The key is correct, but you have to define a new/other project, or you have to modify the project, to enable it to use this API, the maps API. In very short: You have an organizational problem, not a technical one.

Just in general: Whenever you develop something to a mature solution, you can't do something like this:

IF EMPTY(cResponse) OR "error" $ cResponse
    =MESSAGEBOX("Invalid postcode or API error.", 48, "Error")
    RETURN
ENDIF

This ignores any error, doesn't even show it. As a developer you look at every response to see what's in it and get the message what's exactly wrong to tackle it until things work.

 

[Gary Sutherland]

I've written a system for this using the Royal Mail PAF files. If you're going to be storing the PAF data locally then you'll need to cater for around 15-20 lookup tables because there is so much data.

 

[Paul Gibson]

One potential solution is to use a different third-party provider, this can greatly simplify things for you but the downside being that you'll almost certainly have to pay the third-party for the privilege. That'll likely be a per-request charge or a recurring time-based charge, like an annual license fee per-user.

We used to use a service called ADF Postcode (I think), this ran by installing a new version each year on the applicable workstations, the new version came with the latest data as obviously the data changes frequently. This was licensed per-machine per-year and meant each year we had to go through the pain of helping the users perform the updates on the applicable workstations on site. It wasn't a nice solution for this reason, it was fine in the early 2000s but later it was too cumbersome to manage in the modern web world. Our application interacted with the ADF Postcode application to ask it to perform the queries in it's data files. Eventually, we dropped this solution because it was too old fashioned and we were licensing it per-machine and they wanted us to alter our implementation to license it per-user as apparently that was what the Royal Mail wanted to enforce.

Since they were trying to force us to do some rewrites and we didn't like their solution anymore we moved to a different provider so we could make a clean break. We went with a company called Simply Postcode and we went down the reseller route, us buying the licenses on behalf of our customers. This solution is a web API, similar to the Google example you've tried to show. Same as Google it required an API key and the specifics are that it was licensed per-user per-year. So, if a site has 20 users, 8 of them need the postcode to address backfill we'd need to buy 8 licenses and flag those 8 users as being able to use the backfill facility, the other 12 users being locked away from the functionality. Each license last for 12 months so we have to go through annual license renewal and that's a pain.

We could have gone with a per-request payment model but that would have required our customers buying credits up front and us coding the system to cope with all the bits and pieces around making requests when they had no credits. Our customers wanted a simple, predictable annual fee and that solution allowed us to remain in control of the licenses but as I say that control came with a cost of having to faff around with the license renewals each year.

These are just some examples of how we've provided that solution in the past and the current day in VFP. There are plenty of other ways of doing it, most official ways will not be free in the UK as I think the Royal Mail are keen to monetise the access to that data on every front that they can. In other, non-VFP, projects we do use the Google API to do address backfill, route mapping etc and we could have used Google's API in our VFP applications if we didn't have other solutions.

One key thing being that when you write a solution, especially with a Web API solution, write the bare bones for your proof of concept and then specifically step through the code to examine what you are sending and what the response comes back as. I'll echo what Chriss said, try not to write generic or heavy-handed error checking, you want to try to handle some of the common unhappy-path scenarios with some elegance - I know the code you showed is likely just first-version before you tidied it up but it's worth saying all the same. Checking if the characters "error" appear as a string in the response in that manner might have a risk of false positives, are there any street names, towns, cities, counties, etc that contain the string "error"? That's just a hypothetical.

For something like a JSON response, actually convert the JSON response to the object it represents, then look for the properties that indicate an error or success. If you have no experience with JSON and VFP take a look at the nfJSON project (https://github.com/VFPX/nfJson) it's free and you can leverage that to do your JSON conversions. It does all the heavy lifting for you with the conversion to and from JSON. There are other options but I've had nothing but success using that myself.

 

[Chriss Miller]

Paul, Steve just has a configuration problem in his Google Cloud account dashboard of his API projects to add that his Google API project also needs to specify to also want to use Maps. That's all there is to it.

 

[Chriss Miller]

Another point is that by the service endpoint URL you use in your old code (https://maps.googleapis.com) and by the documentation Paul pointed out this is a legacy API and the latest Places API has a starting point here:

Google Maps Platform Documentation | Places API | Google for Developers

Google Maps Platform Documentation

developers.google.com

Which uses the service endpoint https://places.googleapis.com/
And reference documentation for a text search is here:

Text Search (New) | Places API | Google for Developers

developers.google.com

And here's where you would enable this:

https://console.cloud.google.com/apis/library/places.googleapis.com

Roughly speaking I think the intent of the Places API is to find places like sights, also restaurants or generally businesses, and companies, not so much residential addresses. But there are many more APIs.

You might want to use https://developers.google.com/maps/documentation/address-validation instead or any other of the many APIs available. Here's an API picker that could help find what you want to use: https://developers.google.com/maps/documentation/api-picker

 

[Steve-vfp9user]

Appreciate the replies and not ignoring them, still looking at the minute.