The fastest way to execute UK address list using MySQL and PostgreSQL

The United Kingdom addresses are specific because usually, a single postcode applies to a single building or at most a compact group of properties. It means, that these objects can be found very quickly, even when we are facing a bulk of addresses. Imagine, that under one unique postcode, you have a dozen or so flats. Your goal will be achieved solely when you find the right postcode. This is a very convenient way.
In my example file, I have got a few thousand single flats in London, listed in MS Excel (Pic. 1). MS Excel is a common way to store bulk data of single properties address. Having it there, we can use the filter tool to group all single properties belonging to one unique postcode. Anyhow this is not my solution for now, because you would like to have them placed on the map I believe.

UK Address list example for London

Pic. 1 An example of a UK address list in MS Excel for properties in London. This is a snippet of a few thousand flats.

How to get about it, in order to have it placed on the map? First of all, I would recommend distinct all unique postcodes, visible in our Excel spreadsheet document. As I mentioned above, the first thing, that comes to our mind is the filter option in MS Excel, although I won’t explain it here.
For my purpose, I must make this Excel spreadsheet as a .sql file. The quickest option is a straight conversion between .xslx and .sql formats. You can do it using i.e. SQLizer.io (Pic. 2).

Convert Excel spreadsheet into SQL list

Pic. 2 The SQLizer.io – one of the few web tools, enables the user to make a quick conversion into a .sql file.

Afterward, we must import our new .sql database into the PHPMyAdmin (Pic. 3).

Upload SQL file to PhpMyadmin

Pic. 3 Uploading a .sql file to the PHPMyAdmin.

The image above shows only a general way to upload your file. You can use different options or upload a file format other than .sql.

If you uploaded the file successfully, you should have a list as follows (Pic. 4).

Phpmyadmin SQL UK address list

Pic. 4 An .sql file with address list (database) uploaded to PHPMyAdmin.

Now we must figure out, what to do, to squeeze up our address list as much as possible. As per my opinion above, you usually have a bulk of single addresses, assigned to much less amount of the unique postcodes – particular locations (at least it works in the United Kingdom like this). Then we must find a quick way to distinguish these postcodes.
Now, comes a simple query – SELECT DISTINCT column FROM the table (Pic. 5), which tidies up our data extremely quickly.

MySQL select distinct option

Pic. 5 MySQL SELECT DISTINCT option for the UK address list.

This command must be written in the “SQL” console, available in the main bar on your PHPMyAdmin (Pic. 6).

PHPmyadmin SQL console

Pic. 6 The MySQL console in PhpMyadmin.

Once we are happy with our result, the next step leads to the export of the data sorted (Pic. 7).

PhpMyadmin UK address list

Pic. 7 An overall view of our PHPMyAdmin with the UK address list sorted by the SELECT DISTINCT option. Black arrows show our code and the “Export” button.

We can export our data, by choosing one of a few file extensions (Pic. 8). I would recommend downloading our data as a .csv file.

PhpMyadmin file export

Pic. 8 PhpMyadmin file export options.

The .csv file extension can be quickly uploaded into batch geocoding tools available on the web. The most convenient, I believe is Doogal.co.uk (Pic. 9). You can literally copy and paste the codes there. Once your amount of codes is small, you even don’t need to export your data from PhpMyAdmin. Just copy and paste into this batch geocoding tool (Pic. 9).

Dolgall.co.uk batch geocoding

Pic. 9 Ready UK address list already placed in the Doogall.co.uk batch geocoding tool.

Our literal last step is clicking two elements. One is the “Geocode” button, and another is thick with the “Show area covered” option (Pic. 10).

Dolgall.co.uk batch geocoding

Pic. 10 Major geocoding options in the Dolgall.co.uk service.

Finally, you should get the result in the picture below (Pic. 11).

Dolgall.co.uk UK address list area covered

Pic. 11 Area covered by UK address list geocoded in the Doogall.co.uk service.

A map shown is one of three options to display the geocoded postcodes. You can also switch to the “Text” or “KML” options. Through the “Text” you can copy them and save them as a .csv file or download them directly as a .csv file. The “KML” option is the same and refers to the .kml files.

Doing it in the other way – by another, PostgreSQL database we must follow the steps:
In Sqlizar.io the option “PostgreSQL” must be selected (Pic. 12).

SQLizer.io and PostgreSQL selection

12. PostgreSQL saving option in the SQLizer.io tool.

Thereafter importing our newly created .sql file into PhpPgAdmin should be straightforward (Pic. 13).

PistgreSQL base

Pic. 13 PostgreSQL base imported to PhpPgAdmin with the “Postcode” section.

In this event, all columns belonging to our table have been sorted. We don’t need to type the “SELECT DISTINCT” option here, because the PhpPgAdmin has been done already for us. You must only “Browse” the interesting column as per in the picture above (Pic. 13). Clicking the “Browse” button you are instantly redirected to the column, where all our postcodes have been sorted along with the “SELECT DISTINCT” command, as we know from MySQL. Moreover, you also know how many times the single postcode repeats throughout the column (Pic. 14).

PostgreSQL Phppgadmin postcode sorted

Pic. 14 Column with postcodes already sorted in PhpPgAdmin.

Having this key information, you can export this data into your destination file like an Excel spreadsheet or .csv document. In this case, you can drag, mark all of them (if your dataset looks like this – very small), and copy them into your file. Saying wholeheartedly far better option will be clicking the “Download” button underneath, which should show you the entire list ready to take away (Pic. 14).

Before you get the data ready, firstly precise file format is required (Pic. 15).

PhpPgAdmin file export

Pic. 15 PhpPgAdmin file export

In my case, it’s the .csv file. Clicking on the “Export” button you should get raw data, ready for further use (Pic. 16). Thereafter copying it into i.e. MS Excel won’t be a big deal.

You should get the data compacted in one column, although the customization won’t be difficult (Pic. 17) because it’s only 1 step, which you can overcome. This is dividing the data into 2 columns.

Postcode from PhpPgAdmin to Excel

Pic. 16 Copying the postcode list from PhpPgAdmin to MS Excel.

MS Excel postcodes data customizing

Pic. 17 Customizing the data in the MS Excel software.

In your main toolbar select “Data”, and find the next “Text To Columns” as per in the picture above. As a result, you should have the data sorted (Pic. 18).

The data from PhpPgAdmin now divided into 2 columns

Pic. 18 The postcode list is ready for geotagging.

From this moment your data is ready for geotagging.
I have presented you two ways of swift UK postcode organization in order to further geotagging. It looks like the PhpPgMadmin option is quicker.

Mariusz Krukar

 

Links:

  1. Doogal.co.uk: BatchGeocoding

 

Read also:

  1. Input a multiple address list in Google Maps and Google Earth the quickest way

 

 

 

 

 

 

 

 

 

 

 

 

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *