← Back to Blog
Uncategorized

Clustering England & Wales House Prices Using MADlib

2 September 2022  ·  Tom Johnson

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

This Data Science (DS) demonstration makes use of MADlib, an open-source technology for Machine Learning (ML) in SQL.

MADlib allows ML algorithms to be executed within the database using SQL – no Python needed!

Our example database is hosted using Greenplum, the Massively Parallel Processing (MPP) data platform based on PostgreSQL.

5489f gpdb

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

71813 fig1
Fig. 1 – Distribution of height measurements

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

daafb fig2
Fig. 2 – Distribution of heights sorted into three clusters

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

c5aaf fig3
Fig. 3 – Distribution of heights sorted into three clusters, with centroids marked

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

03cbf fig4
Fig. 4 – Distribution of height and weight measurements, before and after clustering

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

2789f picture5

The HM Land Registry ‘Price Paid Data’ (PPD) contains information on all property sold in England and Wales, ranging all the way from the start of 1995 up until the end of the previous month (June 2022 at the time of writing).

Fields relevant to our worked example include ‘sale price’ and ‘postcode’, which are self-explanatory. These are the chosen data points of interest for the clustering model.

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;
 
create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);
 
insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;
 
2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;
 
create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);
 
insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;
 
2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;
 
create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);
 
insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;
 
2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;
 
create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);
 
insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;
 
2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;
 
create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);
 
insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;
 
2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;
 
create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);
 
insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;
 
2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;
 
create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);
 
insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;
 
2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

3db4c fig5
Fig. 5 – All rows in ‘kmeans_price_paid_cluster_summary’

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

5fe36 fig6
Fig. 6 – 10 rows in ‘kmeans_price_paid_cluster_final’

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

7ad9d fig9
Fig. 9 – Bar chart showing min, avg and max cluster prices vs rank
aa4ae figg10
Fig. 10 – Bar chart showing cluster populations vs rank

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

  • no need to export data from the database
  • no need to learn new languages(s)
  • use your existing favourite SQL tool
  • no extra software required for Greenplum/Postgres users
  • harness the power of the database engine.

drop table kmeans_price_paid_cluster_final;

create table kmeans_price_paid_cluster_final
(postcode_out char(04) not null /* PK */
,avg_sale_price integer not null
,sale_count integer not null
,cluster_rank smallint not null
,cluster_desc char(07) not null)
distributed by (postcode_out);

insert into kmeans_price_paid_cluster_final
(postcode_out
,avg_sale_price
,sale_count
,cluster_rank
,cluster_desc)
select t2.postcode_out
,t2.avg_sale_price
,t2.sale_count
,t1.cluster_rank
,t1.cluster_desc
from kmeans_price_paid_cluster_summary t1
inner join kmeans_price_paid_cluster t2
on t1.cluster_id = t2.column_id;

2,242 rows inserted

← Back to Blog