that<\/em> patient. I’m still working on documenting and releasing some ETL code to process LandReg data, but for now that’s beyond the scope of this post so I’ll just assume that you’ve managed to load the data in such a way as to preserve the columns and can now query and group your data by postcode and year.<\/p>\nAffordability<\/h4>\n
The measure of affordability I’m using is derived from Median Gross Household Income for the entire UK. This is a reasonable measure since it’s closest to what the banks are looking at when they’re thinking about what to lend to a household in order to purchase a property. Historically, 3 times gross income was considered a fairly safe bet, but more recently these ratios have climbed rather steeply.<\/p>\n
The 1997 and 2012 incomes\u00a0have to be inflation-adjusted since the Land Registry doesn’t inflation-adjust the price paid. I wrote a function to do this for me directly within Postgres:<\/p>\n
[code]<\/p>\n
CREATE OR REPLACE FUNCTION RPIConversion (
\ni_price float8,
\ni integer,
\nj integer
\n)
\nRETURNS numeric
\nAS $$
\nDECLARE
\nadjusted numeric := 0; — total area
\nBEGIN
\nSELECT i_price * (SELECT rpi_idx FROM inflation.rpi_fct WHERE yr=j)\/(SELECT rpi_idx FROM inflation.rpi_fct WHERE yr=i) INTO adjusted;
\nRETURN ROUND(adjusted::numeric,2);
\nEND;
\n$$ LANGUAGE plpgsql;
\nALTER FUNCTION RPIConversion(float8, integer, integer) OWNER TO postgres;
\n[\/code]<\/p>\n
Note that this version of the function assumes that your database has an\u00a0inflation<\/em> schema with a RPI (rpi_fct<\/code>) table with the following layout:<\/p>\n[code]<\/p>\n
yr (smallint)<\/p>\n
rpi_idx (numeric)<\/p>\n
[\/code]<\/p>\n
Materialised Views<\/h4>\n
They’re not often used, but materialised views are a rather handy feature of good databases \u2013 the look and act like real tables, but they are dynamically created\u00a0using a SQL statement instead of the full CREATE TABLE command. The principal difference between Materialised and Non-Materialised (Immaterialised?) Views is that the latter are slower but update dynamically. In other words, if you add more data to one of the underlying tables involved in a Non-Materialised View then the view is also updated.<\/p>\n
Anyway, these play nicely with QGIS’ Postgres tools, and the results can be added as layers that are as quick to work with as a local shape file. I’ve parameterised the query below so that I can create annual views for the entire data set by simply running a Python script (i.e.<\/em> all ‘{}’ are substituted with the ‘current’ year’): to process all 20 years’ worth of data took approximately 10 minutes and the results can be added to QGIS in seconds.<\/p>\n[code]
\nCREATE MATERIALIZED VIEW viz.hex{} AS
\nSELECT
\nrow_number() OVER() as id,
\nextract(year from pp.completion_dt) as yr,
\nquantile(pp.price_int, 0.5) as median_price,
\nRPIConversion(lf.income_amt*52::numeric, 2012, {}) AS median_income,
\nround(quantile(pp.price_int, 0.5)::numeric \/ RPIConversion(lf.income_amt*52::numeric, 2012, {}), 2) AS affordability,
\nh.gid,
\nh.geom
\nFROM
\nlandreg.price_paid_fct pp,
\ninflation.hh_income_fct lf,
\nosopen.hex_mapping_dim hd,
\nosopen.hex_2500m h
\nWHERE extract(year from pp.completion_dt) = {}
\nAND lf.year = {}
\nAND lf.metric_nm::text = ‘Median’::text
\nAND lf.region_nm::text = ‘All households’::text
\nAND pp.pc = hd.pc
\nAND hd.h2500m = h.gid
\nGROUP BY yr, h.gid, lf.income_amt;<\/p>\n
[\/code]<\/p>\n
In the worst tradition of university lecturers: I’ll leave the remainder of the workflow\u00a0as an exercise for the reader.\t\t<\/p>\n","protected":false},"excerpt":{"rendered":"
One of the known problems with choropleth maps is that small zones, even if they contain very significant values, tend to get lost in amongst much larger zones. A current example is that the ridings in London are much smaller than those outside of London, so it can be hard to tell what’s happening in […]<\/p>\n","protected":false},"author":1,"featured_media":345,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,5,8,13],"tags":[19,60,81],"class_list":["post-342","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-economic-geography","category-mapping","category-postgis","category-visualisation","tag-affordability","tag-land-registry","tag-price-paid"],"_links":{"self":[{"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/342","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=342"}],"version-history":[{"count":0,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/342\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=\/"}],"wp:attachment":[{"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=342"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=342"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.reades.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=342"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Luminocity3D\"](\"http:\/\/www.reades.com\/wp-content\/uploads\/2015\/05\/Screen-Shot-2015-05-01-at-16.18.34.png\")
<\/a><\/p>\n![\"Guardian](\"http:\/\/www.reades.com\/wp-content\/uploads\/2015\/05\/Screen-Shot-2015-05-01-at-16.18.57.png\")
<\/p>\n![\"Affordability](\"http:\/\/www.reades.com\/wp-content\/uploads\/2015\/05\/1997-and-2012-2-1024x724.png\")
<\/a><\/p>\n