UK Postal Addresses Database (1 Viewer)

Status
Not open for further replies.

isladogs

MVP / VIP
Local time
Today, 22:10
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • UKAddressFinderDB.zip
    756 KB · Views: 1,376
  • UKPostalAddressFinderVIDEO.zipx
    1.4 MB · Views: 1,092
  • UKPostalAddressesForm.jpg
    UKPostalAddressesForm.jpg
    102.4 KB · Views: 6,712
Last edited:

isladogs

MVP / VIP
Local time
Today, 22:10
Joined
Jan 14, 2017
Messages
18,186
Further to my previous comments, the owner of the doogal.co.uk site (Chris Bell) which provides the postcode data has agreed to make 2 changes:

1. Data is now available in ACCDB or MDB formats - use whichever you prefer
2. He has now removed spaces and special characters from all field names

I've therefore updated the database to match the amended field names.
I've also added 2 new fields supplied with this update.

There is no other change to the functionality in this version (2.71)

Reminder - download the data from https://www.doogal.co.uk/ukpostcodes.php

NOTE: an enhanced version with additional features will be available to purchase from my website in the next month or so
 

Attachments

  • UKAF_v271.zip
    763.6 KB · Views: 803

isladogs

MVP / VIP
Local time
Today, 22:10
Joined
Jan 14, 2017
Messages
18,186
The UK Postal Address Finder application has at last been released as a commercial application via my website
http://www.mendipdatasystems.co.uk/uk-postal-address-finder

Features include:
a) full lists of postal addresses plus a static map for selected UK postcodes
b) address searches using the unique delivery point reference number (UDPRN) for each address
c) obtaining full addresses from partial addresses
d) creation of a daily itinerary together with route maps and directions
e) digital signature capture for use with customer delivery systems

The applicaton is available as an ACCDE file in 3 versions: Standard, Professional and Tablet.

Licensing is based on the number of concurrent users required (1, 3, 5, 10 or site licence).

Full source code can also be purchased

A time limited evaluation version is also available so that anyone interested can try out the application features

Many thanks to all who have assisted with different stages of this application.
In particular, thanks are due to Dave (Gemma-the-Husky) - a free copy has been supplied in gratitude for his excellent suggestion regarding licensing
 
Last edited:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom