<jupyter_start><jupyter_text>Neighborhoods<jupyter_code>sql = """INSERT INTO spatial_groups (city, core_geom, core_id, lower_ids, spatial_name, approx_geom)
SELECT a.city, a.core_geom, a.core_id, array_agg(a.core_id), 'core', ST_multi(a.core_geom)
FROM spatial_groups a
where a.city='{city}' and a.spatial_name = 'ego'
GROUP BY a.core_id, a.core_geom, a.city;
""".format(city=CITY, tempname=CITY.lower())

result = engine.execute(text(sql))<jupyter_output><empty_output><jupyter_text>Land use<jupyter_code>land_gdf = gpd.read_file('zip://../../data/chicago/land_use/land_use.zip', dtype={'LANDUSE': str})
land_gdf = land_gdf.to_crs({'init': 'epsg:4326'}) 
land_gdf.head()
land_gdf['landuse'] = 'none'

land_gdf.loc[(land_gdf['LANDUSE'].str[:2].isin({'11'})) | (land_gdf['LANDUSE'].isin({'1216'})), 'landuse'] = 'residential'
land_gdf.loc[(land_gdf['LANDUSE'].str[:2].isin({'12', '13', '14', '15', '20'})) & (~land_gdf['LANDUSE'].isin({'1510', '1511', '1512', '1520', '1550', '1561', '1565'})), 'landuse'] = 'commercial'
 
land_gdf.loc[land_gdf['LANDUSE'].str[:1].isin({'3'}), 'landuse'] = 'recreational'
land_gdf.loc[land_gdf['LANDUSE'].str[:1].isin({'4'}), 'landuse'] = 'vacant'
land_gdf.head()<jupyter_output><empty_output><jupyter_text>Net area<jupyter_code>land_gdf_unique = land_gdf.copy()

land_gdf_unique.loc[:, 'x'] = land_gdf_unique.geometry.centroid.x
land_gdf_unique.loc[:, 'y'] = land_gdf_unique.geometry.centroid.y
land_gdf_unique = land_gdf_unique.drop_duplicates(subset=['x', 'y'])[['geometry', 'landuse']]
ins_gdf = process_geometry_SQL_insert(land_gdf_unique)
ins_gdf.to_sql('temptable_unique_{}'.format(CITY.lower()), engine, if_exists='replace', index=False, dtype={'geom': Geometry('MultiPolygon', srid=4326)})
sql = """
UPDATE temptable_unique_{tempname} p SET geom=ST_Multi(ST_buffer(p.geom, 0.0)) 
WHERE NOT ST_Isvalid(p.geom);
""".format(city=CITY, tempname=CITY.lower())

result = engine.execute(text(sql))
## This deletes the blocks that are related to streets
sql = """
DELETE FROM block b
WHERE city='{city}' and NOT EXISTS (select * from temptable_unique_{tempname} t where st_intersects(t.geom, b.geom) and t.landuse <> 'none');
""".format(city=CITY, tempname=CITY.lower())

result = engine.execute(text(sql))
sql = """
DELETE 
FROM temptable_unique_{tempname} t
USING unused_areas u 
WHERE u.city = '{city}' AND ST_Intersects(u.geom, t.geom) AND (NOT ST_Touches(u.geom, t.geom)) 
AND (ST_Contains(u.geom, t.geom) OR ST_AREA(ST_Intersection(t.geom, u.geom))/ST_Area(t.geom) > 0.5);
""".format(city=CITY, tempname=CITY.lower())

result = engine.execute(text(sql))
sql = """
INSERT INTO spatial_groups_net_area (sp_id, city, spatial_name, used_area) 
SELECT sp_id, city, spatial_name, SUM(ST_Area(ST_Intersection(s.approx_geom, t.geom)::geography))/1000000.
FROM temptable_unique_{tempname} t
INNER JOIN spatial_groups s ON ST_Intersects(s.approx_geom, t.geom) AND NOT ST_Touches(s.approx_geom, t.geom)
WHERE s.city = '{city}' AND s.spatial_name='core' 
GROUP BY sp_id, city, spatial_name;
""".format(city=CITY, tempname=CITY.lower())

result = engine.execute(text(sql))<jupyter_output><empty_output><jupyter_text>Refresh materialized views<jupyter_code>sql = """
REFRESH MATERIALIZED VIEW spatial_groups_unused_areas;
"""

result = engine.execute(text(sql))
2<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18505123568414708, 'fraction_numerical': 0.02109704641350211, 'mean_word_length': 7.97027027027027, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '57344', 'license': 'MIT', 'n_tokens_mistral': 1406, 'n_tokens_neox': 1339, 'n_words': 217, 'path': 'src/preprocess/chicago (core).ipynb', 'repo_name': 'denadai2/bayesian-crime-multiple-cities', 'size': '3318'}