From 61769a1badeb56ddfc1323d61e9af41850d76086 Mon Sep 17 00:00:00 2001
From: Marc Tobias Metten <mtmail@gmx.net>
Date: Sun, 4 Nov 2018 21:50:00 +0100
Subject: [PATCH] GB postcode: new conversion script, documentation

---
 data-sources/gb-postcodes/README.md           | 63 +++++++++++++++++++
 .../gb-postcodes/convert_codepoint.php        | 37 +++++++++++
 docs/CMakeLists.txt                           |  1 +
 docs/mkdocs.yml                               |  1 +
 4 files changed, 102 insertions(+)
 create mode 100644 data-sources/gb-postcodes/README.md
 create mode 100755 data-sources/gb-postcodes/convert_codepoint.php

diff --git a/data-sources/gb-postcodes/README.md b/data-sources/gb-postcodes/README.md
new file mode 100644
index 00000000..241d30cb
--- /dev/null
+++ b/data-sources/gb-postcodes/README.md
@@ -0,0 +1,63 @@
+# GB Postcodes
+
+
+The server [importing instructions](https://www.nominatim.org/release-docs/latest/admin/Import-and-Update/) allow optionally download [`gb_postcode_data.sql.gz`](https://www.nominatim.org/data/gb_postcode_data.sql.gz). This document explains how the file got created.
+
+## GB vs UK
+
+GB (Great Britain) is more correct as the Ordnance Survey dataset doesn't contain postcodes from Northern Ireland.
+
+## Importing separately after the initial import
+
+If you forgot to download the file, or have a new version, you can import it separately:
+
+1. Import the downloaded `gb_postcode_data.sql.gz` file.
+
+2. Run `utils/setup.php --calculate-postcodes` from the build directory. This will copy data form the `gb_postcode` table to the `location_postcodes` table.
+
+
+
+## Converting Code-Point Open data
+
+1. Download from [Code-Point® Open](https://www.ordnancesurvey.co.uk/business-and-government/products/code-point-open.html). It requires an email address where a download link will be send to.
+
+2. `unzip codepo_gb.zip`
+
+   Unpacked you'll see a directory of CSV files.
+
+   ```
+   $ more codepo_gb/Data/CSV/n.csv
+   "N1 0AA",10,530626,183961,"E92000001","E19000003","E18000007","","E09000019","E05000368"
+   "N1 0AB",10,530559,183978,"E92000001","E19000003","E18000007","","E09000019","E05000368"
+   ```
+
+   The coordinates are "Northings" and "Eastings" in [OSGB 1936](http://epsg.io/1314) projection. They can be projected to WGS84 like this
+
+   ```
+   SELECT ST_AsText(ST_Transform(ST_SetSRID('POINT(530626 183961)'::geometry,27700), 4326));
+   POINT(-0.117872733220225 51.5394424719303)
+   ```
+   [-0.117872733220225 51.5394424719303 on OSM map](https://www.openstreetmap.org/?mlon=-0.117872733220225&mlat=51.5394424719303&zoom=16)
+
+
+
+3. Create database, import CSV files, add geometry column, dump into file
+
+   ```
+   DBNAME=create_gb_postcode_file
+   
+   createdb $DBNAME
+   echo 'CREATE EXTENSION postgis' | psql $DBNAME
+   
+   cat data/gb_postcode_table.sql | psql $DBNAME
+   
+   cat codepo_gb/Data/CSV/*.csv | ./data-sources/gb-postcodes/convert_codepoint.php | psql $DBNAME
+   
+   cat codepo_gb/Doc/licence.txt | iconv -f iso-8859-1 -t utf-8 | dos2unix | sed 's/^/-- /g' > gb_postcode_data.sql
+   pg_dump -a -t gb_postcode $DBNAME | grep -v '^--' >> gb_postcode_data.sql
+   
+   gzip -9 -f gb_postcode_data.sql
+   ls -lah gb_postcode_data.*
+   
+   # dropdb $DBNAME
+   ```
diff --git a/data-sources/gb-postcodes/convert_codepoint.php b/data-sources/gb-postcodes/convert_codepoint.php
new file mode 100755
index 00000000..12f2c674
--- /dev/null
+++ b/data-sources/gb-postcodes/convert_codepoint.php
@@ -0,0 +1,37 @@
+#!/usr/bin/env php
+<?php
+
+echo <<< EOT
+
+ALTER TABLE gb_postcode ADD COLUMN easting bigint;
+ALTER TABLE gb_postcode ADD COLUMN northing bigint;
+
+TRUNCATE gb_postcode;
+
+COPY gb_postcode (id, postcode, easting, northing) FROM stdin;
+
+EOT;
+
+$iCounter = 0;
+while ($sLine = fgets(STDIN)) {
+    $aColumns = str_getcsv($sLine);
+
+    // insert space before the third last position
+    // https://stackoverflow.com/a/9144834
+    $postcode = $aColumns[0];
+    $postcode = preg_replace('/\s*(...)$/', ' $1', $postcode);
+
+    echo join("\t", array($iCounter, $postcode, $aColumns[2], $aColumns[3]))."\n";
+
+    $iCounter = $iCounter + 1;
+}
+
+echo <<< EOT
+\.
+
+UPDATE gb_postcode SET geometry=ST_Transform(ST_SetSRID(CONCAT('POINT(', easting, ' ', northing, ')')::geometry, 27700), 4326);
+
+ALTER TABLE gb_postcode DROP COLUMN easting;
+ALTER TABLE gb_postcode DROP COLUMN northing;
+
+EOT;
diff --git a/docs/CMakeLists.txt b/docs/CMakeLists.txt
index 57178fc8..c54ea083 100644
--- a/docs/CMakeLists.txt
+++ b/docs/CMakeLists.txt
@@ -15,6 +15,7 @@ ADD_CUSTOM_TARGET(doc
    COMMAND ${CMAKE_COMMAND} -E create_symlink ${CMAKE_CURRENT_SOURCE_DIR}/extra.css ${CMAKE_CURRENT_BINARY_DIR}/extra.css
    COMMAND ${CMAKE_COMMAND} -E create_symlink ${CMAKE_CURRENT_SOURCE_DIR}/data-sources/overview.md ${CMAKE_CURRENT_BINARY_DIR}/data-sources/overview.md
    COMMAND ${CMAKE_COMMAND} -E create_symlink ${PROJECT_SOURCE_DIR}/data-sources/us-tiger/README.md ${CMAKE_CURRENT_BINARY_DIR}/data-sources/US-Tiger.md
+   COMMAND ${CMAKE_COMMAND} -E create_symlink ${PROJECT_SOURCE_DIR}/data-sources/gb-postcodes/README.md ${CMAKE_CURRENT_BINARY_DIR}/data-sources/GB-Postcodes.md
    COMMAND ${CMAKE_COMMAND} -E create_symlink ${PROJECT_SOURCE_DIR}/data-sources/country-grid/README.md ${CMAKE_CURRENT_BINARY_DIR}/data-sources/Country-Grid.md
    COMMAND ${CMAKE_COMMAND} -E create_symlink ${PROJECT_SOURCE_DIR}/data-sources/country-grid/mexico.quad.png ${CMAKE_CURRENT_BINARY_DIR}/data-sources/mexico.quad.png
    COMMAND ${CMAKE_CURRENT_SOURCE_DIR}/bash2md.sh ${PROJECT_SOURCE_DIR}/vagrant/Install-on-Centos-7.sh ${CMAKE_CURRENT_BINARY_DIR}/appendix/Install-on-Centos-7.md
diff --git a/docs/mkdocs.yml b/docs/mkdocs.yml
index 69926f4e..e59ad569 100644
--- a/docs/mkdocs.yml
+++ b/docs/mkdocs.yml
@@ -24,6 +24,7 @@ pages:
     - 'External Data Sources':
         - 'Overview' : 'data-sources/overview.md'
         - 'US Census (Tiger)': 'data-sources/US-Tiger.md'
+        - 'GB Postcodes': 'data-sources/GB-Postcodes.md'
         - 'Country Grid': 'data-sources/Country-Grid.md'
     - 'Appendix':
         - 'Installation on CentOS 7' : 'appendix/Install-on-Centos-7.md'
-- 
2.45.2