hoods processing.sql

alter table dublin_hoods.all_dub_new add column geom_2157 geometry(polygon, 2157);

update dublin_hoods.all_dub_new set geom_2157 = st_transform(geom, 2157);

create index
idx_all_dub_new_geom_2157 on
dublin_hoods.all_dub_new using gist (geom_2157);

-- Hex grid needs to be created in QGIS

create index
idx_hex_grid_geom_centroid on
dublin_hoods.hex_grid using gist (st_centroid(geom));

update all_dub_new
set name = 'The Tenters'
where lower(replace(replace(replace(name, ' ', ''), '''', ''), '’', '')) = 'tenters'

update all_dub_new
set name = 'The Liberties'
where lower(replace(replace(replace(name, ' ', ''), '''', ''), '’', '')) = 'liberties'

update all_dub_new
set name = 'Phibsborough'
where lower(replace(replace(replace(name, ' ', ''), '''', ''), '’', '')) = 'phibsboro'

drop table dublin_hoods.all_dub_new_driver

create table dublin_hoods.all_dub_new_driver as
select
mode() within group (order by name),
lower(replace(replace(replace(name, ' ', ''), '''', ''), '’', '')),
count(*)
from
dublin_hoods.all_dub_new
group by
lower(replace(replace(replace(name, ' ', ''), '''', ''), '’', ''))
having count(*) > 2
order by 
count desc

delete from dublin_hoods.all_dub_new_driver where mode = 'Kilmainham/Inchicore';
delete from dublin_hoods.all_dub_new_driver where mode = 'Dublin 7';
delete from dublin_hoods.all_dub_new_driver where mode = 'Dublin 8';
delete from dublin_hoods.all_dub_new_driver where mode = 'D8';
delete from dublin_hoods.all_dub_new_driver where mode = 'D7';

drop table dublin_hoods.all_extent

create table dublin_hoods.all_extent as
select
dh."mode", dh.lower, dh.count as tot_count, ij.*, ((ij.count/dh.count::float) * 100)::int as prc
from dublin_hoods.all_dub_new_driver dh
left join lateral
(
select
g.id, g.geom, count(nh.*) as count
from
dublin_hoods.hex_grid g
left join dublin_hoods.all_dub_new nh on st_intersects(st_centroid(g.geom), geom_2157)
where lower(replace(replace(replace(name, ' ', ''), '''', ''), '’', '')) = dh.lower
group by g.id, g.geom
having count(nh.*) > 0
) ij on true

drop table dublin_hoods.all_dub_driver_map

create table dublin_hoods.all_dub_driver_map
as
select d.*, st_collect(g.geom) as geom from dublin_hoods.all_dub_new_driver d
left join dublin_hoods.all_extent g on g.lower = d.lower
where g.count > 3
group by d.mode, d.lower, d.count
order by d.count desc

create index
idx_all_extent_geom on
dublin_hoods.all_extent using gist (geom)

create index
idx_all_extent_id on
dublin_hoods.all_extent (id)

vacuum analyze dublin_hoods.all_extent

drop table dublin_hoods.all_dub_hoods

create table
dublin_hoods.all_dub_hoods
as
select o.*,
'no'::varchar as disputed
from
dublin_hoods.all_extent o
left join lateral
(
select i.lower from
dublin_hoods.all_extent i
where o.id = i.id
and i.tot_count >= 3 and i.prc >= 20 and i.count > 2
order by i.prc desc nulls last, i.count desc
limit 1
) ij on true
where tot_count >= 3 and prc >= 20 and o.count > 2
and ij.lower = o.lower

drop table dublin_hoods.all_dub_hoods_undisputed

create table
dublin_hoods.all_dub_hoods_undisputed
as
select o.*,
'no'::varchar as disputed
from
dublin_hoods.all_extent o
left join lateral
(
select count(*) from
dublin_hoods.all_extent i
where o.id = i.id
and i.tot_count >= 3 and i.prc >= 20 and i.count > 2
) ij on true
where tot_count >= 3 and prc >= 20 and o.count > 2
and ij.count = 1

drop table dublin_hoods.all_dub_hoods_disputed

create table
dublin_hoods.all_dub_hoods_disputed
as
select id, geom, ij.mode, ij.count, ij.prc, ij2.*, 'yes'::varchar as disputed
from
dublin_hoods.hex_grid o
left join lateral
(
select i.mode, i.prc, i.count from
dublin_hoods.all_extent i
where o.id = i.id
and i.tot_count >= 3 and i.prc >= 20 and i.count > 2
order by i.prc desc nulls last, i.count desc limit 1
) ij on true
left join lateral
(
select string_agg(i."mode", ', ' order by i.prc desc) as disp, string_agg(i."mode" || ' ' || ("prc"/20)::int * 20 || '%' -- (' || i.count || ')' 
, ', ' order by i.prc desc) as disp_prc
from
dublin_hoods.all_extent i
where o.id = i.id and i.mode != ij.mode
and i.tot_count >= 3 and i.prc >= 20 and i.count > 2
group by i.id
limit 1
) ij2 on true
where id not in (select id from dublin_hoods.all_dub_hoods_undisputed)
and ij.mode is not null

drop table dublin_hoods.all_dub_hoods_disputed_merged

create table dublin_hoods.all_dub_hoods_disputed_merged
as
select
mode,
disp,
disp_prc,
st_union(geom) as geom
from dublin_hoods.all_dub_hoods_disputed
group by
mode,
disp,
disp_prc

drop table dublin_hoods.all_dub_hoods_merged

create table
dublin_hoods.all_dub_hoods_merged
as select mode,
st_union(geom) as geom
from dublin_hoods.all_dub_hoods
group by
mode

--Create all_dub_hoods_merged_color in QGIS

create table all_dub_hoods_color_merged as 
select
h.mode,
h.tot_count,
("prc"/20)::int * 20 as percentage,
cc.color_id,
st_union(h.geom) as geom
from dublin_hoods.all_dub_hoods h
left join dublin_hoods.all_dub_hoods_merged_color cc on h.mode = cc.mode
group by
h.mode,
h.tot_count,
("prc"/20)::int * 20,
cc.color_id


drop table dublin_hoods.all_dub_hoods_labels
create table
dublin_hoods.all_dub_hoods_labels
as
select "mode", lower, st_union(geom) from
dublin_hoods.all_extent o
left join lateral
(
select count(*) from
dublin_hoods.all_extent i
where o.id = i.id
and i.tot_count >= 3 and i.prc >= 20 and i.count > 1
) ij on true
where tot_count >= 3 and prc >= 20 and o.count > 1
and ij.count = 1
group by "mode", lower