Advertisements

MkrGeo

An original point of view

GIS

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

Dolgall.co.uk UK address list area covered

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

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 a 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 to 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, enabling the user to make a quick conversion into .sql file.

Afterwards, 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 the 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, next step leads to export of the data sorted (Pic. 7).

PhpMyadmin UK address list

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

We can export our data, 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 all and paste in 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 literally last step is clicking two elements. One is the “Geocode” button, another is thick 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 as per 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 into the “Text” or “KML” options. Through the “Text” you can copy them and save as a .csv file or download 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 the 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 import 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 has been sorted. We don’t need to type the “SELECT DISTINCT” option here, because the PhpPgAdmin has been done it 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 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 into your file. Saying wholeheartedly far better option will be clicking the “Download” button underneath, that should show you entire list ready to take away (Pic. 14).

Before you get the data ready, firstly precising the 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”, finding 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 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

 

 

 

 

 

 

 

 

 

 

 

 

 

Advertisements

Theme by Anders Norén

Follow

Get the latest posts delivered to your mailbox:

%d bloggers like this: