def init_demand(self):
"Create the demand database table"
if self.centroid == 'weighted':
self.demand_geo_weight = self.demand_geo_weight.merge(self.demand_pop, on = 'geouid')
print(list(self.demand_geo_weight))
centroid = Centroid(self.demand_geo, self.demand_geo_weight)
self.centroid_df = centroid.calculate_weighted_centroid()
else:
self.demand_geo = self.demand_geo.merge(self.demand_pop, on = 'geouid')
centroid = Centroid(self.demand_geo)
self.centroid_df = centroid.calculate_geographic_centroid()
#self.centroid_df.pop = self.centroid_df['pop_Total'].astype(float)
self.centroid_df.geouid = self.centroid_df['geouid'].astype(int)
self.centroid_df.reset_index(inplace = True)
self.centroid_df.rename(columns = {'index': 'id'}, inplace = True)
centroid_df = self.centroid_df.copy(deep = True)
centroid_df['centroid'] = [x.wkt for x in centroid_df['centroid']]
centroid_df = osgeo.ogr.Open(centroid_df.to_json())
layer = centroid_df.GetLayer(0)
# create demand table
query_create = """
DROP TABLE IF EXISTS demand;
CREATE TABLE demand(
id serial PRIMARY KEY,
geoUID int,
centroid geometry,
boundary geometry
"""
req_columns = ['id', 'geouid']
geo_columns = ['centroid', 'boundary']
pop_columns = []
for col in [col for col in self.centroid_df if col.startswith('pop_')]:
if self.centroid_df[col].dtype == 'O':
unit = 'text'
else:
unit = 'float'
pop_columns.append(col)
query_create = query_create + """, %s %s""" % ('"' + col + '"', unit)
sql_columns = req_columns + geo_columns + pop_columns
sql_col_string = '"' + '", "'.join(sql_columns) + '"'
query_create = query_create + """)"""
self.execute_query(query_create, "created demand")
for i in self.centroid_df.index:
feature = layer.GetFeature(i)
values = self.centroid_df.loc[i]
req_values = "'" + "', '".join(values[req_columns].astype(str).values.flatten().tolist()) + "'"
# req_values = self.centroid_df[req_columns].loc[i] # .astype(str).values.flatten().tolist()
geometry = feature.GetGeometryRef().ExportToWkt()
centroid = feature.GetGeometryRef().Centroid().ExportToWkt()
if len(pop_columns) == 0:
query_insert = """ INSERT into demand(%s) VALUES (%s, ST_Transform(ST_SetSRID(ST_GeomFromText(%s),%s),3347), ST_Transform(ST_SetSRID(ST_GeomFromText(%s),%s),3347));
""" % (sql_col_string, req_values, "'" + centroid + "'", self.config.demand_geo_crs, "'" + geometry + "'", self.config.demand_geo_crs)
else:
pop_values = "'" + "', '".join(values[pop_columns].astype(str).values.flatten().tolist()) + "'"
query_insert = """ INSERT into demand(%s) VALUES (%s, ST_Transform(ST_SetSRID(ST_GeomFromText(%s),%s),3347), ST_Transform(ST_SetSRID(ST_GeomFromText(%s),%s),3347), %s);
""" % (sql_col_string, req_values, "'" + centroid + "'", self.config.demand_geo_crs, "'" + geometry + "'", self.config.demand_geo_crs, pop_values)
self.execute_query(query_insert, "updated demand")
# (fuid,ST_Transform(ST_SetSRID(ST_GeomFromText(wkt),self.config.demand_geo_crs),3347),ST_Transform(ST_SetSRID(ST_GeomFromText(centroid),self.config.demand_geo_crs),3347),pop)
# loop through all features
'''
for i in range(layer.GetFeatureCount()):
# import pdb; pdb.set_trace()
feature = layer.GetFeature(i) # index value
fuid = feature.GetField('geouid') # id
centroid = feature.GetField('centroid') # centroid
if centroid.startswith("POINT (-n") or centroid.startswith("POINT (n"):
centroid = feature.GetGeometryRef().Centroid().ExportToWkt()
pop = feature.GetField('pop') # population / weight ?
geometry = feature.GetGeometryRef()
wkt = geometry.ExportToWkt()
self.execute_query("INSERT INTO demand (geoUID, boundary, centroid, pop) VALUES (%s,ST_Transform(ST_SetSRID(ST_GeomFromText(%s),%s),3347),ST_Transform(ST_SetSRID(ST_GeomFromText(%s),%s),3347),%s);", "updated demand",
(fuid, wkt, self.config.demand_geo_crs, centroid, self.config.demand_geo_crs, pop))
self.db_conn.conn.commit()
'''
# create index for demand table
self.execute_query("CREATE INDEX idx_demand ON demand USING GIST(centroid, boundary);", "indexed demand")
