access - google api Address finder (1 Viewer)

Jayce72

Registered User.
Local time
Today, 22:04
Joined
Sep 26, 2011
Messages
60
I have searched and searched the internet and not found anything that I understand and helps.

I have an access db that records addresses, in several fields. i.e: House number, street, county etc.

Currently users complete this manually on a form and data inconsistency and accuracy is high. I would like to nail this down so that users enter a postcode and it gives them the corresponding addresses. They then select that address that will populate my fields.

Does anyone have any examples or point me in the right direction. I think the google API is the way to go.

PS: I am searching UK postcodes
 

Jayce72

Registered User.
Local time
Today, 22:04
Joined
Sep 26, 2011
Messages
60
oooh - looks promising - thanks - will post update after looking
 

isladogs

MVP / VIP
Local time
Today, 22:04
Joined
Jan 14, 2017
Messages
18,186
Thanks very much for saying it works OK :D
Perhaps you should look at it again properly this time.

There very definitely are road names together with house numbers / house names in the link that I provided ...
https://www.access-programmers.co.uk/forums/showthread.php?t=295378
You can even see them in the listbox on the left of the screenshot!

Where you may be getting confused is that the first version was a postcode locator - https://www.access-programmers.co.uk/forums/showthread.php?t=295233

Yes it will be necessary to update the postcode data each new release - this happens several times per year.

The postal addresses are downloaded 'on-the-fly' using the Ideal Postcodes API then tied into the Google maps API to get the map.
So that part is completely live

There was a lot of development work to get that far.

If you'd prefer to roll your own, that fine ... please do so.
AFAIK you cannot get a list of addresses for each postcode from the Google API
That's because the Post Office keeps tight control over this data
It can be bought either direct from the Post Office or by paying for searches from one of the website offering address APIs. I've used the second method.

You will need to connect your database to one of the various APIs to do the download.
Alternatively you can purchase the full dataset from the Post Office at £400 per year.
Your choice!
 
Last edited:

Jayce72

Registered User.
Local time
Today, 22:04
Joined
Sep 26, 2011
Messages
60
Thanks very much for saying it works OK :D
Perhaps you should look at it again properly this time.

There very definitely are road names together with house numbers / house names in the link that I provided ...
https://www.access-programmers.co.uk/forums/showthread.php?t=295378
You can even see them in the listbox on the left of the screenshot!

Where you may be getting confused is that the first version was a postcode locator - https://www.access-programmers.co.uk/forums/showthread.php?t=295233

Yes it will be necessary to update the postcode data each new release - this happens several times per year.

The postal addresses are downloaded 'on-the-fly' using the Ideal Postcodes API then tied into the Google maps API to get the map.
So that part is completely live

There was a lot of development work to get that far.

If you'd prefer to roll your own, that fine ... please do so.
AFAIK you cannot get a list of addresses for each postcode from the Google API
That's because the Post Office keeps tight control over this data
It can be bought either direct from the Post Office or by paying for searches from one of the website offering address APIs. I've used the second method.

You will need to connect your database to one of the various APIs to do the download.
Alternatively you can purchase the full dataset from the Post Office at £400 per year.
Your choice!

Hi - thanks for the update - I see now. It works really well. Can I ask why you use only partial data from the API server and pick up most from the Postcode database? Hence when it comes to Lond and Lat it would be best to get accurate data instead of the middle ground downloaded data. Only asking as trying to find the best way to update my DB. I'm willing to pay and therefore trying to find the best method
 

isladogs

MVP / VIP
Local time
Today, 22:04
Joined
Jan 14, 2017
Messages
18,186
Hi - thanks for the update - I see now. It works really well. Can I ask why you use only partial data from the API server and pick up most from the Postcode database? Hence when it comes to Lond and Lat it would be best to get accurate data instead of the middle ground downloaded data. Only asking as trying to find the best way to update my DB. I'm willing to pay and therefore trying to find the best method

OK here's a very long answer ....

When I was originally developing this, I tested the output from various online address providers including the PostOffice / IdealPostcodes / GetAddress / Hopeweiser / Postcoder and more.
All online providers charge for the data to cover the licensing costs from the Post office as well as their own costs & of course to make a profit.

I also tried obtaining all the data from Google maps hoping to get the data for free.
In theory you could download all 28 million addresses using their API & I started doing just that. However:
- Google throttles such requests to a 'crawl' after 200 or so results & it would have taken more than one lifetime to get them all (even if I used various proxy addresses)
- not all addresses are available (or at least aren't complete)
- I found larger discrepancies in long/lat data from Google compared to the ONS data (I've been told its deliberately inaccurate due to licensing issues)

So I abandoned Google & returned to paying online providers for the data.
I also found that the quoted longitude/latitude values for postcodes varied slightly for each provider. It wasn't just rounding errors - the figures differed slightly from each other and from that downloaded from the Office for National Statistics (ONS) via www.doogal.co.uk.

From memory, none of the providers gave exact values for each address - just for the approx centre of that postcode

When I inquired, one provider (GetAddress) informed me the quoted data was representative rather than precise for each postcode!
Not very helpful & one of several reasons why I didn't use them!

I eventually decided on IdealPostcodes as the address provider.
Their API & support are both excellent and their prices were less than most providers.
As a bonus, their long/lat data matches that from ONS (after rounding)
The ONS long/lat data has 6 decimal places,
IdealPostcodes approx 15 d.p. but the data matches extremely closely!

I could use either dataset on my form but decided 6 d.p. would be sufficient

BTW - the datasets from the Post Office are prohibitively expensive unless you are a major business needing many thousands of searches each day.
For small to medium scale use, online providers are much cheaper

I've been working on other projects for the past 2 months but am planning to continue developing the postcode database shortly with a view to selling an updated version via my website. This should be ready mid/late November

This will have additional features including:
a) a postcode search wizard (similar to that on satnavs) - useful if you aren't sure what postcodes exists for each area/district/sector - see screenshot

b) Postal address from UDPRN (unique delivery point reference number as used by delivery drivers) - see screenshot

c) Full address finder for all addresses matching a partial address
e.g. all addresses including '12 High Street'

d) Data for Northern Ireland - not included in the Doogal ONS dataset due to licensing issues - I have the data ready to import

a) & b) are done - c) & d) shouldn't be too difficult to add

If you want an address finder for more than the occasional search, you will have to pay for it in one way or another.
If you are interested in purchasing mine, please email me using the link in my signature line to discuss further

Best wishes
 

Attachments

  • AddressFromUDPRN.jpg
    AddressFromUDPRN.jpg
    88.8 KB · Views: 199
  • PostcodeWizard.PNG
    PostcodeWizard.PNG
    22.4 KB · Views: 186

Jhonsmith

New member
Local time
Tomorrow, 03:04
Joined
Dec 15, 2022
Messages
1
There is nothing on the internet like this to help me. There are a lot of users who are doing it by themself. There should be the right address so it will be good for searchers. IS there anyone who can guide me right ?
 

isladogs

MVP / VIP
Local time
Today, 22:04
Joined
Jan 14, 2017
Messages
18,186
There is plenty of help available on the internet. I know because I used it myself several years ago to get started

There should be the right address so it will be good for searchers. IS there anyone who can guide me right ?
It isn't clear what you want help with.

Start by looking at my series of articles on mapping in Access & the associated videos
 

Users who are viewing this thread

Top Bottom