Go Back   Access World Forums > Microsoft Access Reference > Sample Databases

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-21-2017, 04:35 PM   #1
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,468
Thanks: 41
Thanked 588 Times in 543 Posts
ridders will become famous soon enough ridders will become famous soon enough
UK Postal Addresses Database

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...d.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
Attached Images
File Type: jpg UKPostalAddressesForm.jpg (102.4 KB, 506 views)
Attached Files
File Type: zip UKAddressFinderDB.zip (756.0 KB, 99 views)
File Type: zipx UKPostalAddressFinderVIDEO.zipx (1.38 MB, 75 views)

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!

Last edited by ridders; 10-29-2017 at 10:23 AM. Reason: Added Edit comment
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
ahmed_optom (08-24-2017)
Old 11-10-2017, 03:51 AM   #2
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,468
Thanks: 41
Thanked 588 Times in 543 Posts
ridders will become famous soon enough ridders will become famous soon enough
Re: UK Postal Addresses Database

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
Attached Files
File Type: zip UKAF_v271.zip (763.6 KB, 23 views)
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Reply

Tags
json , uk postal addresses

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dutch postal codes namliam Excel 0 11-26-2013 11:22 AM
Use of Bitmap addresses severly impacting database performance PaulA Forms 7 07-13-2012 05:09 AM
Postal Code mugman17 ASP and ASP.NET 1 01-28-2008 12:56 AM
postal code problem wannabepro General 1 06-10-2005 12:48 PM
Generic Postal Code Little_Man22 Modules & VBA 1 07-24-2001 08:13 AM




All times are GMT -8. The time now is 12:33 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World