def calc_landuse_prop(pc6_id, center_geom_txt, landuse_classes, buffer):
cur_1, conn_1 = uf.connect()
cur_1.execute("""select id, bg2015, st_area(ST_Intersection(geom, st_buffer(ST_GeomFromText(%s, 28992), %s))), st_area(st_buffer(ST_GeomFromText(%s, 28992), %s))
from bbg2015 where bg2015 in %s and ST_Intersects(geom, st_buffer(ST_GeomFromText(%s, 28992), %s));""", ([center_geom_txt, buffer, center_geom_txt, buffer, landuse_classes, center_geom_txt, buffer]))
result_set_1 = cur_1.fetchall()
if cur_1.rowcount==0:
#print ('postcode = ', pc6_id), ' is not overlapped with any land use classes', landuse_classes
return 0
landuse_area_sum = 0.0
for record in result_set_1:
landuse_id = record[0]
landuse_type = record[1]
landuse_area = record[2]
buffer_area = record[3]
# print "landuse_id = ", landuse_id, " ; landuse_type = ", landuse_type, " landuse_area = ", landuse_area, " ; buffer_area = ", buffer_area, " ; prop = ", landuse_area / buffer_area
landuse_area_sum = landuse_area_sum + landuse_area
cur_1.close()
conn_1.close()
landuse_prop = landuse_area_sum / buffer_area
return landuse_prop
def calc_bldg_area(id, geom_txt, buffer):
cur1, conn1 = uf.connect()
cur1.execute(
"""
SELECT sum(st_area(ST_MakePolygon(ways.linestring)::geography)) AS res_bldg_area
FROM ways
WHERE ST_Intersects(ways.linestring, ST_Transform(ST_Buffer(ST_GeomFromText(%s, 28992), %s), 4326))
and (ways.tags -> 'building'= 'apartments' or ways.tags -> 'building'= 'farm' or ways.tags -> 'building'= 'house' or ways.tags -> 'building'= 'detached' or ways.tags -> 'building'= 'residential' or ways.tags -> 'building'= 'dormitory' or ways.tags -> 'building'= 'terrace' or ways.tags->'building'='houseboat' or ways.tags->'building'='bungalow' or ways.tags->'building'='static_caravan' or ways.tags->'building'='cabin')
and ST_IsEmpty(ways.linestring) IS false
AND ST_IsValid(ways.linestring) IS true
and ST_IsClosed(ways.linestring);""", ([geom_txt, buffer]))
# too slow with --WHERE ST_Intersects(ST_Transform(ways.linestring, 28992), ST_Buffer(ST_SetSRID(ST_Point(%s, %s), 28992), %s))
# SELECT sum(st_area(ST_MakePolygon(ways.linestring)::geography)) AS res_bldg_area, json_agg(ways.id::text)
result_set1 = cur1.fetchall()
if cur1.rowcount == 0:
print('id = ', id), ' is not overlapped with any buildings'
return id, -2
result = result_set1[0]
bldg_area = result[0]
#osm_ids = result[1]
#print 'pc6 id = ', id, " ; osm ids = ", osm_ids
if bldg_area is None:
logger.info('id = ' + str(id) +
' is not overlapped with any streets!' +
" query result = " + str(result))
return id, 0
cur1.close()
conn1.close()
return id, bldg_area
def calc_nox_idx(id, geom_txt, buffer):
global buffer_area
cur_nox, conn_nox = uf.connect()
cur_nox.execute(
"""select nox_avg, ST_Area(ST_Intersection(geom,st_buffer(ST_GeomFromText(%s, 28992), %s)))
from nox_nl_2km_2015 where ST_Intersects(geom, st_buffer(ST_GeomFromText(%s, 28992), %s));""",
([geom_txt, buffer, geom_txt, buffer]))
result_set_nox = cur_nox.fetchall()
if cur_nox.rowcount == 0:
print('postcode pc6 = ', id), ' is not overlapped with any nox zones'
return 0, 0
nox_avg = 0.0
rate_sum = 0.0
for record in result_set_nox:
# print "nox record = ", record
nox = record[0]
intsec_area = record[1]
rate = intsec_area / buffer_area
# print "nox record = ", record, " ; rate = ", rate
nox_avg = nox_avg + nox * rate
rate_sum = rate_sum + rate
cur_nox.close()
conn_nox.close()
return nox_avg, rate_sum
def calc_nox_avg(gid, geom_txt, buffer):
cur_1, conn_1 = uf.connect()
cur_1.execute("""with clip_query as (SELECT ST_ValueCount(ST_Clip(st_union(rast), clipper.geom, false)) as pvc
FROM nox_2009 INNER JOIN
(select st_buffer(ST_GeomFromText(%s, 28992), %s) as geom) AS clipper
ON ST_Intersects(nox_2009.rast, clipper.geom) GROUP BY clipper.geom)
SELECT (pvc).value, SUM((pvc).count) As total from clip_query GROUP BY (pvc).value;""", ([geom_txt, buffer]))
result_set = cur_1.fetchall()
total_num = 0
total_value = 0
default_avg = -99999
for record in result_set:
value = record[0]
count = record[1]
total_num = total_num + count
total_value = total_value + value * count
if total_num == 0:
logger.info("pc6 gid = "+ str(gid) + " has no nox value!")
return gid, default_avg
avg_value = total_value / total_num
#print('avg = ', avg_value, total_value, total_num)
cur_1.close()
conn_1.close()
return gid, avg_value
def calc(table, buffer):
total_start_time = time.time()
cur, conn = uf.connect()
cur.execute("select gid, ST_AsText(st_centroid(geom)) from " +
source_table + ";")
record_result_set = cur.fetchall()
result_all_list = []
start_time = time.time()
row_index = 0
for row in record_result_set:
row_index = row_index + 1
id = row[0]
center_geom_txt = row[1]
result_one_tuple = ()
addr_num = calc_address_num(id, center_geom_txt, buffer)
result_one_tuple = (addr_num, id)
result_all_list.append(result_one_tuple)
if row_index % uf.batch_number == 0:
update_all_idx(result_all_list, buffer)
end_time = time.time()
time_diff = end_time - start_time # in seconds
time_diff = round(time_diff, 2)
time_diff_min = round(time_diff / 60, 2) # in mins
print("calc " + variable + " index for pc6, id = ", int(id),
" row_index = ", row_index, " result= ", result_one_tuple,
"; running time = ", time_diff_min, " mins! ", time_diff,
" sec! ", " buffer = ", buffer)
logger.info("calc " + variable + " index for pc6, id = " +
str(int(id)) + " row_index = " + str(row_index) +
" result= " + str(result_one_tuple) +
"; running time = " + str(time_diff_min) + " mins! " +
str(time_diff) + " sec! buffer = " + str(buffer))
# empty list
start_time = time.time()
result_all_list = []
update_all_idx(result_all_list, buffer)
cur.close()
conn.close()
total_end_time = time.time()
total_time_diff = total_end_time - total_start_time # in seconds
total_time_diff = round(total_time_diff / (60 * 60), 2) # in hours
print(
"finish calculating " + variable +
" index...processed time in hours = ", time_diff, " buffer size = ",
buffer)
logger.info("finish calculating " + variable +
" index...processed time in hours= " + str(time_diff) +
" buffer size = " + str(buffer))
def calc(table, buffer):
global buffer_area
global pixel_area
buffer_area = math.pi * buffer * buffer
pixel_area = 10 * 10
cur, conn = uf.connect()
cur.execute("select gid, ST_AsText(st_centroid(geom)) from " +
uf.source_table + ";")
record_result_set = cur.fetchall()
result_all_list = []
start_time = time.time()
row_index = 0
for row in record_result_set:
row_index = row_index + 1
id = row[0]
center_geom_txt = row[1]
result_one_tuple = ()
id, dn_1, dn_2, dn_3, dn_4, dn_5, dn_6 = calc_noise_distributions(
id, center_geom_txt, buffer)
dn_sum = dn_1 + dn_2 + dn_3 + dn_4 + dn_5 + dn_6
if dn_sum - 1 > 0.0001:
print("something wrong with calc area for pc6 ", id, "; dn_sum = ",
dn_sum)
result_one_tuple = (dn_1, dn_2, dn_3, dn_4, dn_5, dn_6, id)
result_all_list.append(result_one_tuple)
if row_index % uf.batch_number == 0:
update_all_noise(result_all_list, buffer)
end_time = time.time()
time_diff = end_time - start_time # in seconds
time_diff = round(time_diff, 2)
time_diff_min = round(time_diff / 60, 2) # in mins
print("calc noise for pc6 id ", int(id), "row_index = ", row_index,
" result= ", result_one_tuple, "; running time = ",
time_diff_min, " mins! ", time_diff, " sec! ", " buffer = ",
buffer)
logger.info("calc noise for pc6 id " + str(int(id)) +
" row_index = " + str(row_index) + " result= " +
str(result_one_tuple) + "; running time = " +
str(time_diff_min) + " mins! " + str(time_diff) +
" sec! buffer = " + str(buffer))
# empty list
start_time = time.time()
result_all_list = []
update_all_noise(result_all_list, buffer)
cur.close()
conn.close()
def calc(table, buffer):
cur, conn = uf.connect()
cur.execute("select gid, ST_AsText(st_centroid(geom)) from " + source_table +";")
record_result_set = cur.fetchall()
result_all_list = []
start_time =time.time()
row_index = 0
for row in record_result_set:
row_index = row_index + 1
id = row[0]
center_geom_txt = row[1]
if check_skip(id, buffer):
continue
result_one_tuple=()
id, no2_avg = calc_no2_avg(id, center_geom_txt, buffer)
id, nox_avg = calc_nox_avg(id, center_geom_txt, buffer)
id, pm25_avg = calc_pm25_avg(id, center_geom_txt, buffer)
id, pm10_avg = calc_pm10_avg(id, center_geom_txt, buffer)
result_one_tuple = (no2_avg, nox_avg, pm25_avg, pm10_avg, id)
result_all_list.append(result_one_tuple)
if row_index % uf.batch_number == 0:
update_all_air_idx(result_all_list, buffer)
end_time = time.time()
time_diff = end_time - start_time # in seconds
time_diff = round(time_diff, 2)
time_diff_min = round(time_diff/60, 2) # in mins
print("calc air pollution index for pc6, id = ", int(id), " row_index = " , row_index, " result= " , result_one_tuple, "; running time = ", time_diff_min, " mins! ", time_diff, " sec! ", " buffer = ", buffer)
logger.info("calc air pollution index for pc6, id = "+ str(int(id))+ " row_index = " + str(row_index) + " result= " + str(result_one_tuple) + "; running time = " + str(time_diff_min) + " mins! " + str(time_diff) + " sec! buffer = " + str(buffer))
# empty list
start_time = time.time()
result_all_list = []
update_all_air_idx(result_all_list, buffer)
cur.close()
conn.close()
def calc(table, buffer):
buffer_area = math.pi * buffer * buffer
cur, conn = uf.connect()
cur.execute("select gid, ST_AsText(st_centroid(geom)) from " +
source_table + ";")
record_result_set = cur.fetchall()
result_all_list = []
start_time = time.time()
row_index = 0
for row in record_result_set:
row_index = row_index + 1
id = row[0]
center_geom_txt = row[1]
result_one_tuple = ()
id, street_length = calc_street_length(id, center_geom_txt, buffer)
id, motorway_length = calc_motorway_length(id, center_geom_txt, buffer)
street_density = street_length / buffer_area
motorway_density = motorway_length / buffer_area
result_one_tuple = (street_length, street_density, motorway_length,
motorway_density, id)
result_all_list.append(result_one_tuple)
if row_index % batch_num == 0:
update_multiple_street_idx(result_all_list, buffer)
end_time = time.time()
time_diff = end_time - start_time # in seconds
time_diff = round(time_diff, 2)
time_diff_min = round(time_diff / 60, 2)
print("street index for pc6 id = ", int(id), " row_index",
row_index, " result= ", result_one_tuple,
"; running time = ", time_diff_min, " mins! ", time_diff,
" sec! ", " buffer = ", buffer)
logger.info("street index for pc6 id = " + str(int(id)) +
" row_index" + str(row_index) + " result= " +
str(result_one_tuple) + "; running time = " +
str(time_diff_min) + " mins! " + str(time_diff) +
" sec! buffer = " + str(buffer))
# empty list
start_time = time.time()
result_all_list = []
update_multiple_street_idx(result_all_list, buffer)
cur.close()
conn.close()
def calc_landuse_index(pc6_id, center_geom_txt, buffer):
global buffer_area
global pixel_area
global landuse_class_list
landuse_class_num = len(landuse_class_list)
cur_1, conn_1 = uf.connect()
cur_1.execute(
"""with clip_query as (SELECT ST_ValueCount(ST_Clip(st_union(rast), clipper.geom, false)) as pvc
FROM bbg2015_10m INNER JOIN
(select st_buffer(ST_GeomFromText(%s, 28992), %s) as geom) AS clipper
ON ST_Intersects(bbg2015_10m.rast, clipper.geom) GROUP BY clipper.geom)
SELECT (pvc).value, SUM((pvc).count) As total from clip_query GROUP BY (pvc).value;""",
([center_geom_txt, buffer]))
result_set_1 = cur_1.fetchall()
if cur_1.rowcount == 0:
print('postcode = ', pc6_id,
' is not overlapped with any land use classes!')
return 0, 0, 0
landuse_class_idx = 0.0
prop_sum = 0.0
land_prop_dict = {}
for record in result_set_1:
landuse_type = record[0]
landuse_count = record[1]
for i in range(landuse_class_num):
landuse_classes = landuse_class_list[i]
if landuse_type in landuse_classes:
if i in land_prop_dict:
land_prop_dict[i] = land_prop_dict[i] + landuse_count
else:
land_prop_dict[i] = landuse_count
break
for k in land_prop_dict:
prop = land_prop_dict[k] * pixel_area / buffer_area
land_prop_dict[k] = prop
prop_sum = prop_sum + prop
if prop > 0:
landuse_class_idx = landuse_class_idx + prop * math.log(
prop) / math.log(landuse_class_num)
landuse_class_idx = -landuse_class_idx
return landuse_class_idx, land_prop_dict, prop_sum
def update_all_idx(records, buffer):
try:
cur_update, conn_update = uf.connect()
cur_update.executemany("UPDATE " +target_table+"_"+ str(buffer) + " SET comm_bldg_ratio = %s, resi_bldg_ratio = %s where id = %s;",
records)
conn_update.commit()
except (Exception, psycopg2.Error) as error:
print("Failed inserting record into table {}".format(error))
finally:
# closing database connection.
if (conn_update):
cur_update.close()
conn_update.close()
def calc(table, buffer):
global buffer_area
global pixel_area
cur, conn = uf.connect()
cur.execute("select gid, ST_AsText(st_centroid(geom)) from " +
source_table + ";")
record_result_set = cur.fetchall()
result_all_list = []
start_time = time.time()
row_index = 0
for row in record_result_set:
row_index = row_index + 1
id = row[0]
center_geom_txt = row[1]
result_one_tuple = ()
id, ndvi_avg = calc_ndvi_avg(id, center_geom_txt, buffer)
result_one_tuple = (ndvi_avg, id)
result_all_list.append(result_one_tuple)
if row_index % uf.batch_number == 0:
update_all_ndvi(result_all_list, buffer)
end_time = time.time()
time_diff = end_time - start_time # in seconds
time_diff = round(time_diff, 2)
time_diff_min = round(time_diff / 60, 2) # in mins
print("calc ndvi for pc6 id ", int(id), "row_index = ", row_index,
" result= ", result_one_tuple, "; running time = ",
time_diff_min, " mins! ", time_diff, " sec! ", " buffer = ",
buffer)
logger.info("calc ndvi for pc6 id " + str(int(id)) +
" row_index = " + str(row_index) + " result= " +
str(result_one_tuple) + "; running time = " +
str(time_diff_min) + " mins! " + str(time_diff) +
" sec! buffer = " + str(buffer))
# empty list
start_time = time.time()
result_all_list = []
update_all_ndvi(result_all_list, buffer)
cur.close()
conn.close()
def calc_motorway_length(id, geom_txt, buffer):
try:
cur1, conn1 = uf.connect()
cur1.execute(
"""
SELECT sum(ST_Length(ST_Intersection(ways.linestring, ST_Transform(ST_Buffer(ST_GeomFromText(%s, 28992), %s), 4326))::geography)) AS motorway_length
FROM ways
WHERE ST_Intersects(ways.linestring, ST_Transform(ST_Buffer(ST_GeomFromText(%s, 28992), %s), 4326))
and (tags -> 'highway' IS NOT NULL)
and (tags -> 'highway'= 'primary' or tags -> 'highway'= 'primary_link' or tags -> 'highway'= 'trunk' or tags -> 'highway' ='trunk_link' or tags -> 'highway' ='secondary' or tags -> 'highway'= 'secondary_link' or tags -> 'highway' ='tertiary' or tags -> 'highway'= 'tertiary_link' or tags -> 'highway' ='motorway'
or tags -> 'highway' ='motorway_link')
and ST_IsEmpty(linestring) IS false
AND ST_IsValid(linestring) IS true
and ((convert_to_integer(tags->'maxspeed') >= 50)
or (convert_to_integer(tags->'maxspeed:forward') >= 50)
or (convert_to_integer(tags->'maxspeed:backward') >= 50));""",
([geom_txt, buffer, geom_txt, buffer]))
#or (convert_to_integer(tags->'maxspeed:backward') >= 50));""", ([geom_txt, buffer, geom_txt, buffer]));
# too slow with --WHERE ST_Intersects(ST_Transform(ways.linestring, 28992), ST_Buffer(ST_SetSRID(ST_Point(%s, %s), 28992), %s))
result_set1 = cur1.fetchall()
if cur1.rowcount == 0:
print('id = ', id), ' is not overlapped with any motorways'
return id, -2
result = result_set1[0]
motorway_length = result[0]
# osm_ids = result[1]
# motorway_num = result[2]
if motorway_length is None:
print('id = ', id, " query result = ",
result), ' is not overlapped with any motorways'
return id, 0
except (Exception, psycopg2.Error) as error:
print("error in caclculating motorway length".format(error))
logger.info("error in caclculating motorway length, for pc6 " +
str(id))
finally:
# closing database connection.
if (conn1):
cur1.close()
conn1.close()
return id, motorway_length
def update_all_noise(records, buffer):
try:
cur_update, conn_update = uf.connect()
cur_update.executemany(
"UPDATE " + uf.target_table + "_" + str(buffer) +
" SET dn_1 = %s, dn_2 = %s, dn_3 = %s, dn_4 = %s, dn_5 = %s, dn_6 = %s where id = %s",
records)
conn_update.commit()
except (Exception, psycopg2.Error) as error:
print("Failed inserting record into table {}".format(error))
finally:
# closing database connection.
if (conn_update):
cur_update.close()
conn_update.close()
def update_multiple_street_idx(records, buffer):
try:
cur_update, conn_update = uf.connect()
cur_update.executemany(
"UPDATE " + target_table + "_" + str(buffer) +
" set street_length = %s, street_density = %s, motorway_length = %s, motorway_density = %s where id = %s;",
records)
conn_update.commit()
except (Exception, psycopg2.Error) as error:
print("Failed inserting record into table {}".format(error))
finally:
# closing database connection.
if (conn_update):
cur_update.close()
conn_update.close()
def calc_noise_distributions(id, geom_txt, buffer):
global buffer_area
global pixel_area
cur_noise, conn_noise = uf.connect()
cur_noise.execute(
"""with clip_query as (SELECT ST_ValueCount(ST_Clip(st_union(rast), clipper.geom, false)) as pvc
FROM noise_2016_10m INNER JOIN
(select st_buffer(ST_GeomFromText(%s, 28992), %s) as geom) AS clipper
ON ST_Intersects(noise_2016_10m.rast, clipper.geom) GROUP BY clipper.geom)
SELECT (pvc).value, SUM((pvc).count) As total from clip_query GROUP BY (pvc).value;""",
([geom_txt, buffer]))
result_set_noise = cur_noise.fetchall()
if cur_noise.rowcount == 0:
print('id = ', id), ' is not overlapped with any noise areas'
return id, -2, -2, -2, -2, -2, -2
dn_1 = 0.0
dn_2 = 0.0
dn_3 = 0.0
dn_4 = 0.0
dn_5 = 0.0
dn_6 = 0.0
for record in result_set_noise:
#print "noise record = ", record
noise_dn = record[0]
noise_count = record[1]
if noise_dn == 1:
dn_1 = noise_count * pixel_area / buffer_area
elif noise_dn == 2:
dn_2 = noise_count * pixel_area / buffer_area
elif noise_dn == 3:
dn_3 = noise_count * pixel_area / buffer_area
elif noise_dn == 4:
dn_4 = noise_count * pixel_area / buffer_area
elif noise_dn == 5:
dn_5 = noise_count * pixel_area / buffer_area
else:
dn_6 = dn_6 + noise_count * pixel_area / buffer_area
cur_noise.close()
conn_noise.close()
return id, dn_1, dn_2, dn_3, dn_4, dn_5, dn_6
def check_skip(gid, buffer):
cur_5, conn_5 = uf.connect()
cur_5.execute("""select no2_avg notnull from """ +target_table+"_"+ str(buffer) + """ where id = %s """, ([gid]))
result_set5 = cur_5.fetchall()
result = result_set5[0]
flag = result[0]
skipped = True
if flag:
skipped = True
else:
skipped = False
cur_5.close()
conn_5.close()
return skipped
def calc_bldg_area(gid, geom_txt, buffer):
cur_1, conn_1 = uf.connect()
# skip connectivity=3; the 1-ways are typical for suburban areas,
cur_1.execute("""with bldg_query as (
select * from pand_dec_2016 where ST_Intersects(geom, st_buffer(ST_GeomFromText(%s, 28992), %s))
)
select a.gid, max(st_area(a.geom)), string_agg(b.gebruiksdo::text, ';') from bldg_query a, vobject_dec_2016 b where ST_Intersects(a.geom, b.geom) group by a.gid""", ([geom_txt, buffer]))
total_commercial_area = 0
total_residential_area = 0
result_set_1 = cur_1.fetchall()
#
if len(result_set_1)==0:
logger.info('id = '+str(id)+' is not overlapped with any '+ variable +' features!')
return 0, 0;
for record in result_set_1:
bldg_gid = record[0]
bldg_area = record[1]
bldg_functions = record[2]
if bldg_functions == None:
#logger.info('postcode id = '+str(gid)+' has buildings with not functions')
continue
bldg_functions = bldg_functions.split(";")
bldg_function_set = set(bldg_functions)
# check if commercial
if is_desired(bldg_function_set, commcercial_function):
total_commercial_area = total_commercial_area + bldg_area
# check if residential
if is_desired(bldg_function_set, residential_function):
total_residential_area = total_residential_area + bldg_area
cur_1.close()
conn_1.close()
return total_commercial_area, total_residential_area
def calc(table, buffer):
cur, conn = uf.connect()
cur.execute("select gid, ST_AsText(st_centroid(geom)) from " + source_table +";")
record_result_set = cur.fetchall()
result_all_list = []
start_time =time.time()
row_index = 0
for row in record_result_set:
row_index = row_index + 1
id = row[0]
center_geom_txt = row[1]
result_one_tuple=()
landuse_idx, land_prop_list, prop_sum = calc_landuse_index(id, center_geom_txt, buffer)
#if landuse_idx < 0.1 or abs(prop_sum - 1) > 0.001:
# print "not normal landuse index for pc6 = ", int(id), " landuse index = ", landuse_idx, " land_prop_list = ", land_prop_list, " prop_sum = ", prop_sum
# logger.info('"not noraml landuse index for pc6 = ' + str(id) + " ; lanuse index number = " + str(landuse_idx) + "; prop_sum number = " + str(prop_sum) + " land_prop_list = " + str(land_prop_list).strip('[]'))
result_one_tuple = (landuse_idx, id)
result_all_list.append(result_one_tuple)
if row_index % uf.batch_number == 0:
update_multiple_landuse_idx(result_all_list, buffer)
end_time = time.time()
time_diff = end_time - start_time # in seconds
time_diff = round(time_diff, 2)
time_diff_min = round(time_diff/60, 2) # in mins
print("calc landuse for pc6 id ", int(id), "row_index = " , row_index, " result= " , result_one_tuple, "; running time = ", time_diff_min, " mins! ", time_diff, " sec! ", " buffer = ", buffer)
logger.info("calc landuse for pc6 id "+ str(int(id))+ " row_index = " + str(row_index) + " result= " + str(result_one_tuple) + "; running time = " + str(time_diff_min) + " mins! " + str(time_diff) + " sec! buffer = " + str(buffer))
# empty list
start_time =time.time()
result_all_list = []
update_multiple_landuse_idx(result_all_list, buffer)
cur.close()
conn.close()
def calc_street_length(id, geom_txt, buffer):
cur1, conn1 = uf.connect()
cur1.execute(
"""
SELECT sum(ST_Length(ST_Intersection(ways.linestring, ST_Transform(ST_Buffer(ST_GeomFromText(%s, 28992), %s), 4326))::geography)) AS street_length
FROM ways
WHERE ST_Intersects(ways.linestring, ST_Transform(ST_Buffer(ST_GeomFromText(%s, 28992), %s), 4326))
and (tags -> 'highway' IS NOT NULL)
and (tags -> 'highway'= 'primary' or tags -> 'highway'= 'primary_link' or tags -> 'highway'= 'trunk' or tags -> 'highway' ='trunk_link' or tags -> 'highway' ='secondary' or tags -> 'highway'= 'secondary_link' or tags -> 'highway' ='tertiary' or tags -> 'highway'= 'tertiary_link' or tags -> 'highway' ='footway' or tags -> 'highway' ='path' or tags -> 'highway' ='steps' or tags -> 'highway' ='pedestrian' or tags -> 'highway' ='living_street' or tags -> 'highway' ='track' or tags -> 'highway'= 'residential' or tags -> 'highway' ='service' or tags -> 'highway' ='unclassified' or tags -> 'highway'= 'road' or tags -> 'highway' ='cycleway' )
and ST_IsEmpty(linestring) IS false
AND ST_IsValid(linestring) IS true
and (not (ways.tags::hstore ? 'maxspeed') or convert_to_integer(tags->'maxspeed') < 50)
and (not (ways.tags::hstore ? 'maxspeed:forward') or convert_to_integer(tags->'maxspeed:forward') < 50)
and (not (ways.tags::hstore ? 'maxspeed:backward') or convert_to_integer(tags->'maxspeed:backward') < 50);""",
([geom_txt, buffer, geom_txt, buffer]))
# SELECT sum(ST_Length(ST_Intersection(ways.linestring, ST_Transform(ST_Buffer(ST_GeomFromText(%s, 28992), %s), 4326))::geography)) AS street_length, json_agg(ways.id::text), count(*)
# SELECT sum(ST_Length(ways.linestring::geography)) AS street_length, json_agg(ways.id::text)
# and (not (ways.tags::hstore ? 'maxspeed:backward') or convert_to_integer(tags->'maxspeed:backward') < 50);""", ([geom_txt, buffer]));
# too slow with --WHERE ST_Intersects(ST_Transform(ways.linestring, 28992), ST_Buffer(ST_SetSRID(ST_Point(%s, %s), 28992), %s))
result_set1 = cur1.fetchall()
if cur1.rowcount == 0:
print('id = ', id), ' is not overlapped with any streets'
return id, -2
result = result_set1[0]
street_length = result[0]
#osm_ids = result[1]
#street_num = result[2]
if street_length is None:
print('id = ', id, " query result = ",
result), ' is not overlapped with any streets'
return id, 0
cur1.close()
conn1.close()
return id, street_length
def calc_no2_avg(id, geom_txt, buffer):
cur_noise, conn_noise = uf.connect()
cur_noise.execute("""with clip_query as (SELECT ST_ValueCount(ST_Clip(st_union(rast), clipper.geom, false)) as pvc
FROM no2_2011_100m INNER JOIN
(select st_buffer(ST_GeomFromText(%s, 28992), %s) as geom) AS clipper
ON ST_Intersects(no2_2011_100m.rast, clipper.geom) GROUP BY clipper.geom)
SELECT (pvc).value, SUM((pvc).count) As total from clip_query GROUP BY (pvc).value;""", ([geom_txt, buffer]))
result_set = cur_noise.fetchall()
if cur_noise.rowcount==0:
print ('id = ', id), ' is not overlapped with any no2 polluted areas'
return id, -99999
total_num = 0
total_value = 0
no2_avg = -99999
for record in result_set:
#print "noise record = ", record
no2_value = record[0]
no2_count = record[1]
if no2_value > 0 :
total_num = total_num + no2_count
total_value = total_value + no2_value * no2_count
if total_num == 0:
logger.info("pc6 gid = "+ str(id) + " has no no2 value !")
return id, -99999
#print('avg = ', ndvi_avg, total_value, total_num)
no2_avg = total_value / total_num
cur_noise.close()
conn_noise.close()
return id, no2_avg
def get_target_table(buffer):
if not uf.create_tables:
return uf.target_table+'_'+str(buffer)
start_time =time.time()
cur1, conn1 = uf.connect()
# drop column if exist and then create route column
cur1.execute("""drop TABLE if exists """+ uf.target_table+'_'+str(buffer)+""";""")
cur1.execute("""CREATE TABLE """ +uf.target_table+'_'+str(buffer)+""" (
id integer primary key,
pc6 character(6)
);""")
## geometry of the source ojbect
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS geom;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN geom geometry(MultiPolygon,28992);""")
cur1.execute(""" INSERT INTO """ +uf.target_table+'_'+str(buffer)+"""(id, pc6, geom)
(SELECT gid, pc6, geom FROM pc6_2017_jan)""");
## copy geometry and id
conn1.commit()
cur1.close()
conn1.close()
end_time = time.time()
time_diff = end_time - start_time # in seconds
print("create table for ", uf.target_table+'_'+str(buffer), "; running time = ", (time_diff/60), " mins! ", time_diff, " sec! ", time_diff*1000, " million seconds!")
logger.info("create table for "+ uf.target_table+'_'+str(buffer) + "; running time = " + str((time_diff/60)) + " mins! " + str(time_diff) + " sec! " + str(time_diff*1000) + " million seconds!")
return uf.target_table+'_'+str(buffer)
def calc_air_idx(id, geom_txt, buffer):
global buffer_area
cur_air, conn_air = uf.connect()
cur_air.execute(
"""select no2_avg, pm25_avg, pm10_avg, ST_Area(ST_Intersection(geom,st_buffer(ST_GeomFromText(%s, 28992), %s)))
from air_pollution_nl_2015 where ST_Intersects(geom, st_buffer(ST_GeomFromText(%s, 28992), %s));""",
([geom_txt, buffer, geom_txt, buffer]))
result_set_air = cur_air.fetchall()
if cur_air.rowcount == 0:
print('postcode pc6 = ',
id), ' is not overlapped with any air pollution zones'
return 0, 0, 0, 0
no2_avg = 0.0
pm25_avg = 0.0
pm10_avg = 0.0
rate_sum = 0.0
for record in result_set_air:
# print "air index record = ", record
no2 = record[0]
pm25 = record[1]
pm10 = record[2]
intsec_area = record[3]
rate = intsec_area / buffer_area
# print "air index record = ", record, ' rate = ', rate
no2_avg = no2_avg + no2 * rate
pm25_avg = pm25_avg + pm25 * rate
pm10_avg = pm10_avg + pm10 * rate
rate_sum = rate_sum + rate
cur_air.close()
conn_air.close()
return no2_avg, pm25_avg, pm10_avg, rate_sum
def calc_crossings(gid, geom_txt, buffer):
cur_1, conn_1 = uf.connect()
# skip connectivity=3; the 1-ways are typical for suburban areas,
cur_1.execute("""select count(*) from crossings_2016 where ST_Intersects(geom, st_buffer(ST_GeomFromText(%s, 28992), %s)) and edgecount !=3.0""", ([geom_txt, buffer]))
result_set_1 = cur_1.fetchall()
result = result_set_1[0]
crossing_count = result[0]
if crossing_count is None:
logger.info('id = '+str(id)+' is not overlapped with any connectivity points!'+ " query result = " +str(result))
return 0
cur_1.close()
conn_1.close()
return crossing_count
def calc_address_num(gid, geom_txt, buffer):
cur_1, conn_1 = uf.connect()
# skip connectivity=3; the 1-ways are typical for suburban areas,
cur_1.execute(
"""select count(*) from addr_dec_2016 where ST_Intersects(geom, st_buffer(ST_GeomFromText(%s, 28992), %s));""",
([geom_txt, buffer]))
result_set_1 = cur_1.fetchall()
result = result_set_1[0]
addr_count = result[0]
if addr_count is None:
logger.info('id = ' + str(id) + ' is not overlapped with any ' +
variable + ' geometry!' + " query result = " +
str(result) + " within buffer = " + buffer)
return 0
cur_1.close()
conn_1.close()
return addr_count
def add_columns(buffer):
cur1, conn1 = uf.connect()
## residential building area and density
if uf.add_bldg_column:
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS res_bldg_area;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN res_bldg_area double precision default 0;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS res_bldg_density;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN res_bldg_density double precision default 0;""")
if uf.add_street_column:
## motorway length and density
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS motorway_length;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN motorway_length double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS motorway_density;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN motorway_density double precision;""")
## street length and density
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS street_length;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN street_length double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS street_density;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN street_density double precision;""")
## noise column
if uf.add_noise_column:
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS dn_1;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN dn_1 double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS dn_2;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN dn_2 double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS dn_3;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN dn_3 double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS dn_4;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN dn_4 double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS dn_5;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN dn_5 double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS dn_6;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN dn_6 double precision;""")
## air pollution column
if uf.add_airpollution_column:
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS no2_avg;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN no2_avg double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS nox_avg;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN nox_avg double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS pm25_avg;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN pm25_avg double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS pm10_avg;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN pm10_avg double precision;""")
# landuse index
if uf.add_landuse_column:
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS landuse_idx;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN landuse_idx double precision;""")
# ndvi index
if uf.add_ndvi_column:
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS ndvi_avg;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN ndvi_avg double precision;""")
# crossing index
if uf.add_crossing_column:
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS crossing_num;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN crossing_num double precision;""")
if uf.add_commercial_column:
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS comm_bldg_ratio;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN comm_bldg_ratio double precision;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS resi_bldg_ratio;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN resi_bldg_ratio double precision;""")
# degree of urbanization
if uf.add_addr_column:
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS addr_num;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN addr_num double precision;""")
# no2 in 2011
if uf.add_no2_column:
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS no2_2011;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN no2_2011 double precision;""")
# rdvi index
if uf.add_rdvi_column:
cur1.execute("""
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" DROP COLUMN IF EXISTS rdvi_avg;
ALTER TABLE """ +uf.target_table+'_'+str(buffer)+""" ADD COLUMN rdvi_avg double precision;""")
conn1.commit()
cur1.close()
conn1.close()
return uf.target_table+'_'+str(buffer)
def calc(table, buffer):
global buffer_area
buffer_area = math.pi * buffer * buffer
cur, conn = uf.connect()
cur.execute("select gid, ST_AsText(st_centroid(geom)) from " +
source_table + ";")
record_result_set = cur.fetchall()
result_all_list = []
start_time = time.time()
row_index = 0
for row in record_result_set:
row_index = row_index + 1
id = row[0]
center_geom_txt = row[1]
result_one_tuple = ()
no2_avg, pm25_avg, pm10_avg, rate_sum1 = calc_air_idx(
id, center_geom_txt, buffer)
nox_avg, rate_sum2 = calc_nox_idx(id, center_geom_txt, buffer)
if rate_sum1 - 1 > 0.0001 or rate_sum2 - 1 > 0.0001:
print("something wrong with calc air index for pc6 = ", id,
rate_sum1, rate_sum2)
print("pc6 = ", id, "; area = ", buffer_area, "; no2_avg = ",
no2_avg, "; nox_avg = ", nox_avg, "; pm25_avg = ", pm25_avg,
"; pm10_avg = ", pm10_avg)
logger.info('"something wrong with calc air index for pc6 = ' +
str(id) + " ; sum1 = " + str(rate_sum1) +
" ; sum2 = " + str(rate_sum2))
logger.info("pc6 id = " + str(id) + "; area = " +
str(buffer_area) + "; no2_avg = " + str(no2_avg) +
"; nox_avg = " + str(nox_avg) + "; pm25_avg = " +
str(pm25_avg) + "; pm10_avg = " + str(pm10_avg))
result_one_tuple = (no2_avg, nox_avg, pm25_avg, pm10_avg, id)
result_all_list.append(result_one_tuple)
if row_index % uf.batch_number == 0:
update_all_air_idx(result_all_list, buffer)
end_time = time.time()
time_diff = end_time - start_time # in seconds
time_diff = round(time_diff, 2)
time_diff_min = round(time_diff / 60, 2) # in mins
print("calc air pollution index for pc6, id = ", int(id),
" row_index = ", row_index, " result= ", result_one_tuple,
"; running time = ", time_diff_min, " mins! ", time_diff,
" sec! ", " buffer = ", buffer)
logger.info("calc air pollution index for pc6, id = " +
str(int(id)) + " row_index = " + str(row_index) +
" result= " + str(result_one_tuple) +
"; running time = " + str(time_diff_min) + " mins! " +
str(time_diff) + " sec! buffer = " + str(buffer))
# empty list
start_time = time.time()
result_all_list = []
update_all_air_idx(result_all_list, buffer)
cur.close()
conn.close()
