This repository has been archived by the owner on Jun 19, 2024. It is now read-only.
forked from healthysustainablecities/global-indicators
-
Notifications
You must be signed in to change notification settings - Fork 0
/
_08_destination_summary.py
72 lines (61 loc) · 1.87 KB
/
_08_destination_summary.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
"""
Summarise destinations.
Summarise destination counts for grid cells.
"""
import sys
import time
import ghsci
from script_running_log import script_running_log
from sqlalchemy import text
def destination_summary(codename):
start = time.time()
script = '_08_destination_summary'
task = 'Summarise destinations'
r = ghsci.Region(codename)
sql = f"""
DROP TABLE IF EXISTS population_dest_summary;
CREATE TABLE IF NOT EXISTS population_dest_summary AS
SELECT p.grid_id,
d.dest_name_full,
COUNT(d.geom) AS count,
p.geom
FROM {r.config['population_grid']} p,
destinations d
WHERE ST_Intersects(p.geom,d.geom)
GROUP BY p.grid_id, d.dest_name_full, p.geom;
"""
with r.engine.begin() as conn:
result = conn.execute(text(sql))
count_sql = """
DROP TABLE IF EXISTS urban_dest_summary;
CREATE TABLE IF NOT EXISTS urban_dest_summary AS
SELECT a."study_region",
t.dest_name_full,
t.count,
a.pop_est,
a.area_sqkm,
a.pop_per_sqkm,
t.count/a.area_sqkm AS dest_per_sqkm,
t.count/a.area_sqkm/(pop_est/10000) AS dest_per_sqkm_per_10kpop
FROM urban_study_region a,
(SELECT d.dest_name_full,
COUNT(d.*) count
FROM destinations d,
urban_study_region c
WHERE ST_Intersects(d.geom, c.geom)
GROUP BY dest_name_full ) t
;
"""
with r.engine.begin() as conn:
result = conn.execute(text(count_sql))
# output to completion log
script_running_log(r.config, script, task, start)
r.engine.dispose()
def main():
try:
codename = sys.argv[1]
except IndexError:
codename = None
destination_summary(codename)
if __name__ == '__main__':
main()