<jupyter_start><jupyter_text>Prerequisites* Setup Web App* Configure database inside docker-compose.yml with env variables<jupyter_code>import geopandas as gpd
import psycopg2
import folium
import folium.plugins
import fiona
import osmnx as ox
import networkx as nx
import operator
import matplotlib.cm as cmx
import matplotlib.colors as colors


def rgb(minimum, maximum, value):
    minimum, maximum = float(minimum), float(maximum)
    ratio = 2 * (value-minimum) / (maximum - minimum)
    b = int(max(0, 255*(1 - ratio)))
    r = int(max(0, 255*(ratio - 1)))
    g = 255 - b - r
    return r, g, b


def style_function(feature, n_colors):
    cid = feature['properties']['cid']
    return {
        'fillOpacity': 0.5,
        'weight': 0,
        'fillColor': '#red' if cid is None else "rgb{}".format(rgb(0, n_colors, cid))
    }

def init_style_function(n_colors):
    return lambda feature: style_function(feature, n_colors)
ox.utils.config(cache_folder='/data/osmnx_cache', use_cache=True)

def extended_hulls_query(aois_query):
    aois = gpd.read_postgis(aois_query, conn, geom_col='geometry')
    aois.crs = fiona.crs.from_epsg(3857)
    aois = aois.to_crs(fiona.crs.from_epsg(4326))
    central_nodes = []

    for aoi in aois.geometry:
        aoi_graph = ox.graph_from_polygon(aoi.buffer(0.001), network_type='all')
        closeness_centrality = nx.closeness_centrality(aoi_graph)
        sorted_nodes = sorted(closeness_centrality.items(), key=operator.itemgetter(1), reverse=True)
        central_nodes += [node[0] for node in sorted_nodes[:len(sorted_nodes) // 10]]

    central_nodes_ids = ', '.join([f'{key}' for key in central_nodes])

    return """
    WITH hulls AS ({hulls_query}),
    intersecting_lines AS (
        SELECT hulls.cid, ST_Intersection(way, ST_Buffer(hulls.geometry, 50)) AS geometry FROM planet_osm_line, hulls
        WHERE osm_id = ANY(
          SELECT id FROM planet_osm_ways
          WHERE nodes && ARRAY[{central_nodes_ids}]::bigint[]
        )
        AND ST_DWithin(planet_osm_line.way, hulls.geometry, 50)
    )

    SELECT 1 AS color, ST_ConcaveHull(ST_Union(geometry), 0.99) AS geometry FROM (
      SELECT cid, geometry FROM hulls
      UNION
      SELECT cid, geometry FROM intersecting_lines
    ) AS tmp
    GROUP BY cid
    """.format(hulls_query=aois_query, central_nodes_ids=central_nodes_ids)

points_coordinates = {
"Zürich": (47.3737, 8.5388),
"Rapperswil": (47.2269, 8.8187),
"Bern": (46.94813, 7.44656),
"Stäfa": (47.240647, 8.723481),
"Winterthur": (47.4995, 8.7258),
"Hombrechtikon": (47.2518, 8.7684),
"Basel": (47.5573, 7.5884),
"Genf": (46.2029, 6.1472),
"Rüti": (47.2582, 8.8504),
}

point_queries = {}
for name, coordinates in points_coordinates.items():
    point_queries[name] = "ST_Transform((ST_SetSRid(ST_MakePoint({}, {}), 4326)), 3857)".format(coordinates[1], coordinates[0])<jupyter_output><empty_output><jupyter_text>Select POIs and draw hulls<jupyter_code>current_point = "Rapperswil"
eps = 35
minpoints = 3

aois_query = """
WITH hulls AS (
  SELECT hull FROM preclusters WHERE ST_Intersects(hull, {point})
),
clusters AS (
  SELECT geometry,
       ST_ClusterDBSCAN(geometry, eps := {eps}, minpoints := {minpoints}) over () AS cid
  FROM pois, hulls
  WHERE ST_Within(geometry, hulls.hull)
)
SELECT * FROM clusters WHERE cid IS NOT NULL
UNION ALL
SELECT ST_ConvexHull(ST_Union(geometry)), cid FROM clusters WHERE cid IS NOT NULL GROUP BY cid
""".format(point=point_queries[current_point], eps=eps, minpoints=minpoints)

with psycopg2.connect("") as conn:
    aois = gpd.read_postgis(aois_query, conn, geom_col='geometry')
    aois.crs = fiona.crs.from_epsg(3857)
    
n_clusters = len(aois.groupby('cid').cid.nunique())


m = folium.Map(location=points_coordinates[current_point], zoom_start=15, tiles="cartodbpositron")

folium.plugins.Fullscreen().add_to(m)
folium.GeoJson(aois, style_function=init_style_function(n_clusters)).add_to(m)

m<jupyter_output><empty_output><jupyter_text>Do the same but extend with network centrality<jupyter_code>current_point = "Rapperswil"
eps = 35
minpoints = 3

aois_query = """
WITH hulls AS (
  SELECT hull FROM preclusters WHERE ST_Intersects(hull, {point})
),
clusters AS (
  SELECT geometry,
       ST_ClusterDBSCAN(geometry, eps := {eps}, minpoints := {minpoints}) over () AS cid
  FROM pois, hulls
  WHERE ST_Within(geometry, hulls.hull)
)
SELECT * FROM clusters WHERE cid IS NOT NULL
UNION ALL
SELECT ST_ConvexHull(ST_Union(geometry)), cid FROM clusters WHERE cid IS NOT NULL GROUP BY cid
""".format(point=point_queries[current_point], eps=eps, minpoints=minpoints)

with psycopg2.connect("") as conn:
    extended_aois = gpd.read_postgis(extended_hulls_query(aois_query), conn, geom_col='geometry')
    extended_aois.crs = fiona.crs.from_epsg(3857)

m = folium.Map(location=points_coordinates[current_point], zoom_start=15, tiles="cartodbpositron")

folium.plugins.Fullscreen().add_to(m)
folium.GeoJson(extended_aois).add_to(m)

m<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1264275696253256, 'fraction_numerical': 0.035664195551993585, 'mean_word_length': 4.7710982658959535, 'pattern_counts': {'":': 9, '<': 13, '<?xml version=': 0, '>': 13, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '441562', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1994, 'n_tokens_neox': 1825, 'n_words': 400, 'path': 'notebooks/notebooks/12 - DBSCAN local adaption Raphi.ipynb', 'repo_name': 'geometalab/aoi-osm', 'size': '4991'}