README.md

Overview

The following scripts are needed in order to process data for the CA London Development Database datamap.

The information here assumes a basic familiarity with Postgre SQL, as well as installing software and running python scripts from the command line. It does not require the ability to code, but to get the most out of this collection of scripts, you would need to know how to write SQL queries.

There are three scripts in total. The first two, in the dataPreparation directory, only need to be run once. The third script can be run for each dataset you wish to create for the LDD datamap.

Setting up the London Development Database

First install postgre sql into your system. See the Postgre SQL website for more details.

CREATE ROLE ldd login nosuperuser inherit nocreatedb nocreaterole noreplication
CREATE DATABASE ldd OWNER ldd
CREATE SCHEMA app_ldd AUTHORIZATION ldd;

Then, as the postgres user in ldd database, run the following:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

Get the LDD sql extract: https://data.london.gov.uk/dataset/london-development-database-sql-extract

As postgres user from command line, in directory with LDD sql extract pg_restore –clean -d ldd ldddata_DDDDDDDD.sql.tar (where Ds are the date info at the end of the filename)

This process will take a while and give little indication as to where it is up to. Be patient and let it do its thing!

convertGeoLatLon.py all

Purpose

The London Development Database stores co-ordinates in Easting and Northing. This script converts these co-ordinates to the more frequently used longitude and latitude.

It will add the following tables:

ns_permlatlon: latitude and longitude for each planning application, expressed as two numeric values.
nsll_ld_permissions_geo: geopoint and polygon in longitude and latitude for each planning application, as POSTGIS geography objects

Running the script

Run the following command (from the directory the script is located in):


python convertGeoToLatLon.py all

The script will prompt you for a postgre SQL username and password.

Requirements

The script is written for python 2.7. It uses the following libraries:

io
sys
pyproj
psycopg2
psycopg2.extras
string
re
getpass

normaliseFloorspaceStructure.py

Purpose

This script normalises the use data in the London Development Database; this is principally data on floorspace, but includes a few other categories (such as HMO bedrooms, traveller pitches, etc). The reason for doing this is to make querying the database less labourous. This script only needs to be run once on the database, unless its contents are changed.

It will add the following tables:

ca_exist_non_res_floorspace: Details existing non-residential floorspace for a planning application.
app_ldd.ca_exist_non_res_accom: Details existing accommodation outside the standard C3 residential use codes (e.g. hotels, traveller pitches, care homes, prisons etc)
app_ldd.ca_prop_non_res_floorspace: Details proposed non-residential floorspace for a planning application.
app_ldd.ca_prop_non_res_accom: Details proposed accommodation outside the standard C3 residential use codes

It also adds additional codes to the ’ld_ref_codes’ table, under the domain CA_PLANNING_USE_CLASS.

It does not remove existing data or fields from the table ’ld_permissions'.

Running the script


  python normaliseFloorspaceStructure.py

No arguments are required, but the file ‘codes.csv’ must be present in the same directory as it is run. On running, it will ask for a username and password for postgres SQL - this must be a user which has read/write access to your version of the London Development Database, including the ability to create and delete tables.

Requirements

The script is writen for python 2.7.

You also need the following python libraries:

psycopg2
psycopg2.extras
csv
getpass

processLDDtoMapJson.py

This is a python script which queries the London Development Database to create maps for the Concrete Action LDD Data Map. Unlike the other scripts in this pack, this one is run as many times as you like to create different datasets.

You can run this script as follows:


  python processLDDtoMapJson.py <file out path> <sql query file> <impact field> <impact cat>

Definitions of the arguments are below:

file out path: the directory and filename you want to output to (note that the script will provide a minified json under this file name followed by .min)
sql query file: an sql file with the query that will be run on the database. Please use existing queries as templates – they are located in the ‘q’ directory.
impact field: either ’tenure_type_rc’ (for housing tenure) or ‘unit_provider_rc’ (for who is responsible for the property e.g. council or private)"
impact cat: ‘S’ for Social (with tenure_type_rc), ‘L’ for Local Authority (with unit_provider_rc), ’total’ for all housing values (all lower case)\n"

Example:


  python processLDDtoMapJson.py existingsocialhousing.json q/existsocialhousing.sql tenure_type_rc S

Requirements

The script is writen for python 2.7.

You also need the following python libraries:

io
numbers
sys
pyproj
psycopg2
psycopg2.extras
string
re
dicttoxml
xmltodict
json
getpass