Skip to content

Instantly share code, notes, and snippets.

@HeikkiVesanto
Created January 15, 2022 02:37
Show Gist options
  • Save HeikkiVesanto/f008b0bb7a99ea0a4a30427f7b3d34c3 to your computer and use it in GitHub Desktop.
Save HeikkiVesanto/f008b0bb7a99ea0a4a30427f7b3d34c3 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment