"""
Standorte Map Visualizer
========================
Visualizes standorte data from any xlsx file with auto-detected columns.
Expected sheet layout (sheet name: "Standorte"):
Row 1 — section names (e.g. "Betonproduktionsstandort"), one per group of columns
Row 2 — column headers per section; the app recognises:
"Ort"/"Stadt"/"Standort" → location name used for city filter
"Bundesland"/"State"/"Bundesstaat" → federal state used for state filter
"Name"/"Bezeichnung"/"Firma" → marker label
"Link"/"Website"/"URL" → popup hyperlink
"Koordinaten" (or contains it) → "lat, lon" coordinate string
any other header → shown as extra field in popup
Row 3+ — data
Requirements:
pip install gradio folium openpyxl
Run:
python app.py
"""
import gradio as gr
import folium
import openpyxl
import state_lookup
COLOR_PALETTE = [
"#3388ff", "#e74c3c", "#27ae60", "#9b59b6", "#e67e22",
"#003580", "#8B0000", "#006400", "#5F9EA0", "#FF69B4",
"#FF8C00", "#2E8B57", "#DC143C", "#4169E1", "#DAA520",
]
_cached_filepath = None
_cached_section_configs = None
_cached_data = None
def parse_coords(coord_str):
"""Parse 'lat, lon' string → (float, float) or None."""
if not coord_str:
return None
try:
parts = str(coord_str).split(",")
if len(parts) == 2:
lat, lon = float(parts[0].strip()), float(parts[1].strip())
if 47.0 <= lat <= 55.5 and 5.5 <= lon <= 15.5:
return lat, lon
except (ValueError, AttributeError):
pass
return None
def _cell(row, col):
"""Safe row accessor; returns None if col is None or out of range."""
return row[col] if col is not None and len(row) > col else None
def detect_sections(header_row1, header_row2):
"""
Auto-detect section boundaries and column roles from the two header rows.
Row 1 non-empty cells mark where a new section begins.
Row 2 cell text determines the role of each column within its section.
Returns {section_name: {col_ort, col_state, col_name, col_link, col_coords, extra_cols, color}}
"""
section_starts = [
(col, str(val).strip())
for col, val in enumerate(header_row1)
if val and str(val).strip()
]
section_configs = {}
for i, (start_col, section_name) in enumerate(section_starts):
end_col = section_starts[i + 1][0] if i + 1 < len(section_starts) else len(header_row2)
col_ort = col_state = col_name_field = col_link = col_coords = None
extra_cols = {}
for col in range(start_col, end_col):
if col >= len(header_row2) or not header_row2[col]:
continue
original = str(header_row2[col]).strip()
h = original.lower()
if h in ("ort", "stadt", "location", "standort"):
col_ort = col
elif h in ("bundesland", "state", "bundesstaat"):
col_state = col
elif h in ("name", "bezeichnung", "firma", "unternehmen"):
col_name_field = col
elif h in ("link", "website", "url", "homepage", "webseite"):
col_link = col
elif "koordinat" in h or h in ("coords", "coordinates", "lat/lon", "lat, lon"):
col_coords = col
else:
extra_cols[original] = col
if col_coords is None:
continue
section_configs[section_name] = {
"col_ort": col_ort,
"col_state": col_state,
"col_name": col_name_field,
"col_link": col_link,
"col_coords": col_coords,
"extra_cols": extra_cols,
"color": COLOR_PALETTE[i % len(COLOR_PALETTE)],
}
return section_configs
def load_data(filepath):
"""Parse the xlsx and return (section_configs, {section: [records]}). Results are cached."""
global _cached_filepath, _cached_section_configs, _cached_data
if filepath == _cached_filepath and _cached_data is not None:
return _cached_section_configs, _cached_data
wb = openpyxl.load_workbook(filepath, read_only=True)
if "Standorte" not in wb.sheetnames:
raise ValueError("Sheet 'Standorte' not found in the uploaded file.")
ws = wb["Standorte"]
rows = list(ws.iter_rows(values_only=True))
if len(rows) < 3:
raise ValueError("File must have at least 2 header rows and one data row.")
section_configs = detect_sections(rows[0], rows[1])
if not section_configs:
raise ValueError(
"Could not detect any sections. "
"Row 1 must have section names and row 2 must include a coordinate column "
"(header containing 'Koordinat' or named 'Coords'/'Coordinates')."
)
data_rows = rows[2:]
result = {}
for section, cfg in section_configs.items():
records = []
for row in data_rows:
ort = _cell(row, cfg["col_ort"])
name = _cell(row, cfg["col_name"])
link = _cell(row, cfg["col_link"])
coord_raw = _cell(row, cfg["col_coords"])
if not ort and not name:
continue
coords = parse_coords(coord_raw)
if coords is None:
continue
if cfg["col_state"] is not None:
state = _cell(row, cfg["col_state"]) or ""
else:
state = state_lookup.get_state(coords[0], coords[1])
record = {
"Ort": ort or "",
"Bundesland": state,
"Name": name or "",
"Link": link or "",
"lat": coords[0],
"lon": coords[1],
}
for label, col in cfg["extra_cols"].items():
record[label] = _cell(row, col) or ""
records.append(record)
result[section] = records
_cached_filepath = filepath
_cached_section_configs = section_configs
_cached_data = result
return section_configs, result
def get_all_cities(all_data):
cities = set()
for records in all_data.values():
for rec in records:
if rec["Ort"]:
cities.add(rec["Ort"].strip())
return sorted(cities)
def get_all_states(all_data):
states = set()
for records in all_data.values():
for rec in records:
if rec.get("Bundesland"):
states.add(rec["Bundesland"].strip())
return sorted(states)
def build_popup(record, section_cfg):
html = f"{record.get('Name','')}
{record.get('Ort','')}"
bundesland = record.get("Bundesland", "")
if bundesland:
html += f"
{bundesland}"
link = record.get("Link", "")
if link:
html += f'
🔗 Website'
for label in section_cfg["extra_cols"]:
val = record.get(label, "")
if val:
html += f"
{label}:
{val}"
return html
def build_map_legend(section_configs):
items = "".join(
f'● {section}
\n'
for section, cfg in section_configs.items()
)
return (
''
f'Legende
{items}
'
)
def build_sidebar_legend(section_configs):
if not section_configs:
return "Upload a file to see the colour legend.
"
items = "".join(
f''
f''
f'{section}
'
for section, cfg in section_configs.items()
)
return (
f''
f'
Colour Legend{items}'
f'
'
f'Click any marker for details.
Upload a file to see the colour legend.
",
gr.update(choices=[], value=[]),
)
if filepath is None:
return empty
try:
section_configs, all_data = load_data(filepath)
cities = get_all_cities(all_data)
sections = list(section_configs.keys())
states = get_all_states(all_data)
return (
gr.update(choices=cities, value=[]),
gr.update(choices=sections, value=sections),
build_sidebar_legend(section_configs),
gr.update(choices=states, value=[]),
)
except Exception as e:
return (
gr.update(choices=[], value=[]),
gr.update(choices=[], value=[]),
f"Error reading file: {e}
",
gr.update(choices=[], value=[]),
)
def select_all_cities(filepath):
if filepath is None:
return gr.update(value=[])
try:
_, all_data = load_data(filepath)
return gr.update(value=get_all_cities(all_data))
except Exception:
return gr.update(value=[])
def select_all_states(filepath):
if filepath is None:
return gr.update(value=[])
try:
_, all_data = load_data(filepath)
return gr.update(value=get_all_states(all_data))
except Exception:
return gr.update(value=[])
def generate_map(
filepath,
selected_sections,
city_filter_enabled,
selected_cities,
state_filter_enabled,
selected_states,
):
if filepath is None:
return "Please upload an xlsx file.
"
if not selected_sections:
return "Please select at least one data type to display.
"
try:
section_configs, all_data = load_data(filepath)
except Exception as e:
return f"Error reading file: {e}
"
filter_cities = None
if city_filter_enabled and selected_cities:
filter_cities = {c.strip() for c in selected_cities}
filter_states = None
if state_filter_enabled and selected_states:
filter_states = {s.strip() for s in selected_states}
m = folium.Map(location=[51.2, 10.4], zoom_start=6, tiles="CartoDB positron")
total_points = 0
section_counts = {}
for section in selected_sections:
if section not in all_data:
continue
cfg = section_configs[section]
records = all_data[section]
if filter_cities is not None:
records = [r for r in records if r["Ort"].strip() in filter_cities]
if filter_states is not None:
records = [r for r in records if r.get("Bundesland", "").strip() in filter_states]
layer = folium.FeatureGroup(name=section, show=True)
for rec in records:
folium.CircleMarker(
location=[rec["lat"], rec["lon"]],
radius=3,
color=cfg["color"],
fill=True,
fill_color=cfg["color"],
fill_opacity=0.75,
tooltip=rec["Name"] or rec["Ort"],
popup=folium.Popup(build_popup(rec, cfg), max_width=320),
).add_to(layer)
total_points += 1
section_counts[section] = len(records)
layer.add_to(m)
folium.LayerControl(collapsed=False).add_to(m)
m.get_root().html.add_child(folium.Element(build_map_legend(section_configs)))
stats_text = " | ".join(f"{s}: {n}" for s, n in section_counts.items())
notes = []
if filter_cities:
notes.append(f"🏙️ {len(filter_cities)} cities")
if filter_states:
notes.append(f"🗺️ {len(filter_states)} states")
filter_note = (
f"
{' | '.join(notes)} filtered"
if notes else ""
)
m.get_root().html.add_child(folium.Element(f"""
📍 {total_points} Standorte gesamt{filter_note}
{stats_text}
"""))
return m._repr_html_()
# ──────────────────────────────────────────────
# Gradio UI
# ──────────────────────────────────────────────
with gr.Blocks(title="Standorte Karte Deutschland", theme=gr.themes.Soft()) as demo:
gr.Markdown("""
# 🗺️ Standorte in Deutschland
Upload the xlsx file — sections and columns are detected automatically from the header rows.
Choose data types, optionally filter by state or city, then generate the map.
""")
with gr.Row():
# ── Left control panel ───────────────────────────────────────────────
with gr.Column(scale=1):
file_input = gr.File(
label="📂 Upload xlsx File",
file_types=[".xlsx"],
)
section_selector = gr.CheckboxGroup(
choices=[],
value=[],
label="📋 Select Data Types",
)
# State filter
with gr.Accordion("🗺️ State Filter (optional)", open=False):
state_filter_toggle = gr.Checkbox(
label="Enable state filter",
value=False,
)
gr.Markdown(
"_Filter by federal state (Bundesland). "
"Requires a 'Bundesland' column in your Excel file._"
)
state_selector = gr.Dropdown(
choices=[],
value=[],
multiselect=True,
label="Select States",
allow_custom_value=False,
interactive=True,
)
with gr.Row():
select_all_states_btn = gr.Button("✅ Select All", size="sm")
clear_states_btn = gr.Button("🗑️ Clear", size="sm")
# City filter
with gr.Accordion("🏙️ City Filter (optional)", open=False):
city_filter_toggle = gr.Checkbox(
label="Enable city filter",
value=False,
)
gr.Markdown(
"_Type to search, or scroll to browse. "
"Only selected cities will appear on the map. "
"Leave empty (or disable) to show all cities._"
)
city_selector = gr.Dropdown(
choices=[],
value=[],
multiselect=True,
label="Select Cities",
allow_custom_value=False,
interactive=True,
)
with gr.Row():
select_all_btn = gr.Button("✅ Select All", size="sm")
clear_btn = gr.Button("🗑️ Clear", size="sm")
run_btn = gr.Button("🗺️ Generate Map", variant="primary")
legend_html = gr.HTML(
value="Upload a file to see the colour legend.
"
)
# ── Map output ───────────────────────────────────────────────────────
with gr.Column(scale=3):
map_output = gr.HTML(
value=""
"Upload a file and click Generate Map to begin.
",
)
# ── Event wiring ─────────────────────────────────────────────────────────
file_input.change(
fn=on_file_upload,
inputs=[file_input],
outputs=[city_selector, section_selector, legend_html, state_selector],
)
select_all_states_btn.click(
fn=select_all_states,
inputs=[file_input],
outputs=[state_selector],
)
clear_states_btn.click(
fn=lambda: gr.update(value=[]),
inputs=[],
outputs=[state_selector],
)
select_all_btn.click(
fn=select_all_cities,
inputs=[file_input],
outputs=[city_selector],
)
clear_btn.click(
fn=lambda: gr.update(value=[]),
inputs=[],
outputs=[city_selector],
)
run_btn.click(
fn=generate_map,
inputs=[
file_input,
section_selector,
city_filter_toggle,
city_selector,
state_filter_toggle,
state_selector,
],
outputs=[map_output],
)
if __name__ == "__main__":
demo.launch(share=False, server_port=7860, inbrowser=True)