isladogs
MVP / VIP
- Local time
- Today, 08:15
- Joined
- Jan 14, 2017
- Messages
- 18,548
In response to requests from several forum members, I have now significantly extended the feature set of the UK postcode database I uploaded recently to the sample databases area - https://www.access-programmers.co.uk/forums/showthread.php?t=295233
This new version allows users to obtain a full list of postal addresses for selected UK postcodes
Its been a useful learning experience for me researching ways of getting postal addresses for specified postcodes.
Background Info:
There are approximately 1.7 million postcodes & 28 million postal addresses in the UK.
Typically, each postcode has between 20-40 addresses
A full set of postal addresses are freely available for many countries (https://openaddresses.io/) but not the UK.
This site (https://alpha.openaddressesuk.org/developers/download) contains a limited number of UK addresses - its far from complete
Despite FOI applications e.g. by the Guardian newspaper, the UK Post Office refuses to release its data though of course it is willing to sell it!
Large businesses requiring a lot of address searches will need to purchase an address dataset from e.g. the Post Office.
The full dataset of UK postal addresses costs £399 per year!
Other providers sell similar data but the quality & price vary enormously
However, for small scale use by individuals or small businesses, there is another solution.
Address lists can be downloaded as JSON files from various online providers & in various formats.
In general, you can download a few sets free each day but larger scale use is chargeable
Some of the JSON output formats are much more complex than others.
The one I found easiest to work with was from Ideal Postcodes www.ideal-postcodes.co.uk.
Its API documentation is also good.
Up to 20 free searches are available each day.
If you need more than that, purchase an API key from the above website.
Prices start at 3p per search including VAT (but reducing in price for large scale use)
The whole process is amazingly fast.
Select a postcode & the full list of postal addresses plus a static Google map for that postcode are imported and processed in less than a second (on my 5 year old PC)
The map can be zoomed in / out as required.
A new map is downloaded each time, again in a fraction of a second
Attached is a zipped short video as a demo of this in action - to cut file size, the video quality has been reduced & there's no sound
Creating this database gave me a good reason to solve parsing JSON data direct into Access (without using Excel PowerQuery)
As part of this, I use the VBA-JSON parsing code available from https://github.com/VBA-tools/VBA-JSON followed by a recordset to manage the import
NOTE:
1. You will also need to separately download the postcode data from https://www.doogal.co.uk/UKPostcodes.php as an MDB file.
Its huge – 90MB+ zipped & about 950MB unzipped
If you’ve already checked out the postcode db in sample databases, it’s the same datafile
You will need to relink the Postcodes table to the supplied database
2. The zip file containing the supplied database includes a sample file map.bmp.
Save in the same folder as the database.
You will also need to link the image in the startup form frmMain to the map.bmp file
3. The datafile includes about 600 addresses for 24 random postcodes as an example.
4. In this version, the postcode selector combobox contains all 1.7 million postcodes which makes scrolling through the list unrealistic.
Better to type in the postcode - a space is added automatically if not entered.
You can also paste the postcode from elsewhere but this takes a few seconds to complete
5. This is a cut-down version of the UK Address Finder software that will be available for purchase from my website sometime in October.
The full version will include additional features such as:
a) build a postcode search in stages - area e.g. BS ; district e.g. BS7 ; sector e.g. BS7 8 ; full postcode BS7 8HP
This is useful if you aren't sure which codes are available for a particular area
b) get a list of all UK addresses matching a partial address e.g. a street name
c) get a full postal address from its UDPRN - the unique delivery point reference number for each postal address which is used by delivery firms etc.
Any comments, questions or suggestions, please send me a private message or email me using the link at the bottom of this post
EDIT: 29/10/2017
The Postcodes MDB file from www.doogal.co.uk has recently been updated and for some reason the field GridRef has been renamed as Grid Ref (with a space). After downloading the file please rename the field removing the space or you will get an error when used with my UKAF db.
Many thanks to Gasman for informing me of this today
This new version allows users to obtain a full list of postal addresses for selected UK postcodes
Its been a useful learning experience for me researching ways of getting postal addresses for specified postcodes.
Background Info:
There are approximately 1.7 million postcodes & 28 million postal addresses in the UK.
Typically, each postcode has between 20-40 addresses
A full set of postal addresses are freely available for many countries (https://openaddresses.io/) but not the UK.
This site (https://alpha.openaddressesuk.org/developers/download) contains a limited number of UK addresses - its far from complete
Despite FOI applications e.g. by the Guardian newspaper, the UK Post Office refuses to release its data though of course it is willing to sell it!
Large businesses requiring a lot of address searches will need to purchase an address dataset from e.g. the Post Office.
The full dataset of UK postal addresses costs £399 per year!
Other providers sell similar data but the quality & price vary enormously
However, for small scale use by individuals or small businesses, there is another solution.
Address lists can be downloaded as JSON files from various online providers & in various formats.
In general, you can download a few sets free each day but larger scale use is chargeable
Some of the JSON output formats are much more complex than others.
The one I found easiest to work with was from Ideal Postcodes www.ideal-postcodes.co.uk.
Its API documentation is also good.
Up to 20 free searches are available each day.
If you need more than that, purchase an API key from the above website.
Prices start at 3p per search including VAT (but reducing in price for large scale use)
The whole process is amazingly fast.
Select a postcode & the full list of postal addresses plus a static Google map for that postcode are imported and processed in less than a second (on my 5 year old PC)
The map can be zoomed in / out as required.
A new map is downloaded each time, again in a fraction of a second
Attached is a zipped short video as a demo of this in action - to cut file size, the video quality has been reduced & there's no sound
Creating this database gave me a good reason to solve parsing JSON data direct into Access (without using Excel PowerQuery)
As part of this, I use the VBA-JSON parsing code available from https://github.com/VBA-tools/VBA-JSON followed by a recordset to manage the import
NOTE:
1. You will also need to separately download the postcode data from https://www.doogal.co.uk/UKPostcodes.php as an MDB file.
Its huge – 90MB+ zipped & about 950MB unzipped
If you’ve already checked out the postcode db in sample databases, it’s the same datafile
You will need to relink the Postcodes table to the supplied database
2. The zip file containing the supplied database includes a sample file map.bmp.
Save in the same folder as the database.
You will also need to link the image in the startup form frmMain to the map.bmp file
3. The datafile includes about 600 addresses for 24 random postcodes as an example.
4. In this version, the postcode selector combobox contains all 1.7 million postcodes which makes scrolling through the list unrealistic.
Better to type in the postcode - a space is added automatically if not entered.
You can also paste the postcode from elsewhere but this takes a few seconds to complete
5. This is a cut-down version of the UK Address Finder software that will be available for purchase from my website sometime in October.
The full version will include additional features such as:
a) build a postcode search in stages - area e.g. BS ; district e.g. BS7 ; sector e.g. BS7 8 ; full postcode BS7 8HP
This is useful if you aren't sure which codes are available for a particular area
b) get a list of all UK addresses matching a partial address e.g. a street name
c) get a full postal address from its UDPRN - the unique delivery point reference number for each postal address which is used by delivery firms etc.
Any comments, questions or suggestions, please send me a private message or email me using the link at the bottom of this post
EDIT: 29/10/2017
The Postcodes MDB file from www.doogal.co.uk has recently been updated and for some reason the field GridRef has been renamed as Grid Ref (with a space). After downloading the file please rename the field removing the space or you will get an error when used with my UKAF db.
Many thanks to Gasman for informing me of this today
Attachments
Last edited:
