In this project, we used the cantonal geoportal to collect data about buildings, and combine this with an extract of anonymized and randomized residential data from the financial office representing the population of Bern. We built a proof of concept in the form of a map on the dufour platform that presents this information in a way that is accessible for political decisions and to the wider citizenry. For current status check the log, and feel free to leave us a comment.

🎥 Pitch Video 💡 Challenge

Process

The following steps outline the process of data aggregation and publication.

(1) Data extraction from the portal

• Export is limited to 1000 buildings
• With no interactive hint it makes it a trial-and-error
• Export only in proprietary format (Excel), no CSV option
• No geocoordinates in the exported data, hence ...

(2) Geocoding

• Alternatives to our worst favourite spreadsheet thing ($$) include Swisstopo and Nominatim (e.g. via LibreOffice)
• Use =CONCATENATE(C4," ",D4,", ",A4," ",B4) to collect the address into one column
• Use =LEFT(F2,FIND(",",F2)-1) and =RIGHT(F2,LEN(F2)-FIND(",",F2)-1) to split the geocoordinates into two columns

(3) Data aggregation

• You can find the script here
• Configure the input and output filenames directly in the code
• No special libraries required, just Python 3

(4) Geodata import

• Add a "Delimited Text" layer, specify the CSV file, make sure it's being properly loaded below and to specify WGS 84 in Geometry CRS as in the screenshot below

(5) GIS visualisation

• To load Swisstopo layers into QGIS, there's this guide

(6) Web publication

• Use the Spatial Export plugin in QGIS to generate a Data Package.
• Upload to dufour sandbox to create a map with annotation features.
• Share the link, embed it on your website as shown above, and invite collaborators or the citizens to participate.

(7) Rinse

• .. And repeat the data aggregation (step 1) another 281 times (due to mentioned 1000 building limitation) to cover the entire range of the dataset provided.
• It would be easier to contact the Geoportal office to request a complete data export.
• Run a focus group with parliamentarians, journalists, .. to build on this solution.

The original challenge text follows below.

Challenge

Den Gemeinden fehlen für diverse Fragestellungen die Entscheidgrundlagen, da die notwendigen Daten in unterschiedlichen Direktionen/Ämtern bewirtschaftet werden und somit nicht verknüpft werden können. Dies führt im Einzelfall dazu, dass Grundlagen aus verschiedenen Datenquellen mit grossem manuellen Aufwand erstellt werden müssen. Die Ergebnisse sind dann aufgrund der Medienbrüche und der fehlenden Verknüpfungsmöglichkeit mit Unschärfen behaftet. Die Informationen können dabei im regionalen Kontext gleichzeitig als Entscheidgrundlage für das Parlament genutzt werden.

↳ Version française | Daten-Download | Challenge slides

Im Geoportal des Kantons Bern (`Selektionsschema = "Gebäudeadressen") können Zonen freihändig oder mittels Werkzeugen (Kreis, Polygon, Strassen, ...) ausgewählt werden. Beim Klick auf die geografischen Flächen können Informationen zur Fläche (m2), zum Einwohnerregister und zu steuerlichen Information (statistische Werte der Personen, die mit den gewählten Gebäuden (EGID) verknüpft sind).

Dabei sind folgende Informationen denkbar:

• Quartierentwicklung: Monitoring der (finanziellen / bevölkerungsstrukturellen) Entwicklung von Quartieren, z.B. bei Bauvorhaben (Überbauung)
• Anzahl Einwohner pro Fläche (Quadratmeter/Hektare) -> Siedelungsdichte
• Alterstruktur der gewählten Fläche/Region
• Anzahl schulpflichitge und vorschulpflichitge Kinder pro Fläche -> Planung Schulhausstandort (Schliessung, Neubau)
• Steuertrag pro Fläche (getrennt nach Institution Bund, Kanton, Gemeinde) -> Controlling geplanter Ertrag zu effektivem Ertrag bei der Raumplanung / Überbauungsordnung / Quartierentwicklung
• Steuerertrag nach Unterabteilung der Kirchgemeinden (Ermittlung des Verteilschlüssels, da pro Gemeinde nur eine Kirchgemeinde hinterlegt ist -> Problematik JP mit mehreren Geschäftssitzen!)

Noch zu lösende Probleme sind:

• Mindestanzahl Personen pro Fläche definieren, die ausgewählt werden dürfen (Datenschutz)
• wird eine Geheimhaltungsvereinbarung für die Teilnehmer benötigt
• Berechtigungssteuerung auf Stufe Gemeinde, Kirchgemeinde

Folgende Erweiterungen sind ebenfalls denkbar:

• mit dem EWID kann die Wohnungsstruktur ermittelt werden
• mit Grundbuchdaten kann die Alterstruktur, sowie die Handänderungen der Liegenschaften ermittelt werden
• mit Steuerdaten auf Stufe Grundstück können die Unterhaltskosten ermittelt werden
• Verhältnis Miet- zu Eigentumswohnungen
• Bevölkerungsentwickulung / Wanderungssaldo
• ...

Grundsatz

1. Regionale Register- und Steuerauswertungen, welche über Gemeindegrenzen hinausgehen können (frei wählbar)
2. Verknüpfung von Register- und Steuerdaten mit GERES (EGID) und GIS
3. In einer ersten Phase stehen die Daten auf Stufe EGID zur Verfügung (enthält keine Personendaten)

High-level "Datenmodell":

Fläche -> Gebäude -> Person (im GERES ist jede Person ist einem Gebäude zugeordnet) -> Steuerregister- und Einwohnerregisterdaten

Nutzniesser:

Entscheidungsträger in Gemeinden / GR

Lösungsidee

Basis bildet eine ausgewählte Fläche im Geoportal. Mit den darin enthaltenen Gebäudeidentifikatoren (EGID) werden über einen Service die auf die Fläche konsolidierten Informationen zurückgegeben (Bsp. Register / Steuern). Bei den Data Hackdays steht dieser Service nicht zur Verfügung. Als Alternative wird ein CSV zur Verfügung gestellt, wo auf Stufe EGID alle Informationen zur Verfügung gestellt werden. Dabei handelt es sich um generierte Daten, damit das Datenschutzproblem umgangen werden kann (keine Geheimhaltungsvereinbarung notwendig).

Datengrundlagen

• GIS (EGID Koordinaten)
• Einwohnerkontrolle (Registerdaten)
• GERES (Mapping EGID Steuersubjekt)
• Steuerdaten (Einkommen, Vermögen, Steuererträge)

Challenge Owner

Hans Frauchiger - Leiter Steuerung und Controlling bei Steuerverwaltung des Kantons Bern, FIN-SV

Carolin Baumgartner - Sicherheitsbeauftragte SV, FIN-SV

Traduction en français

Les municipalités ne disposent pas de la base de décision pour diverses questions, car les données nécessaires sont gérées par différentes directions/bureaux et ne peuvent donc pas être reliées entre elles. Dans certains cas, cela conduit à ce que des bases provenant de différentes sources de données doivent être créées avec beaucoup d'efforts manuels. Les résultats sont alors sujets à des inexactitudes en raison des perturbations médiatiques et du manque de possibilités de liaison. En même temps, les informations peuvent être utilisées dans un contexte régional comme base de décision pour le parlement.

Sur le Geoportal du Canton (Schéma de sélection = "Adresses de bâtiments"), les zones peuvent être sélectionnées à main levée ou à l'aide d'outils (cercle, polygone, routes, ...). En cliquant sur les zones géographiques, on peut afficher des informations sur la superficie (m2), le registre de la population et des informations fiscales (valeurs statistiques des personnes liées aux bâtiments sélectionnés (EGID)).

Les informations suivantes sont envisageables : - Développement des quartiers : suivi du développement (financier / structure de la population) des quartiers, par ex. nombre d'habitants par zone (mètres carrés/hectare) -> densité de peuplement - structure d'âge de la zone/région sélectionnée - nombre d'enfants en âge de scolarité obligatoire et en âge préscolaire par zone -> planification de l'emplacement des bâtiments scolaires (fermeture, nouvelle construction) - recettes fiscales par zone (séparées par institution, Confédération, canton, commune) -> contrôle du développement prévu, Canton, commune) -> contrôle des recettes prévues par rapport aux recettes effectives dans l'aménagement du territoire / les règlements de construction / le développement du quartier - recettes fiscales selon la subdivision des paroisses (détermination de la clé de répartition, car une seule paroisse est enregistrée par commune -> problème JP avec plusieurs sièges d'exploitation ! )

Les problèmes qui doivent encore être résolus sont les suivants : - définir le nombre minimum de personnes par zone qui peuvent être sélectionnées (protection des données) - un accord de confidentialité est nécessaire pour les participants - contrôle d'autorisation au niveau de la congrégation, de la paroisse

Les extensions suivantes sont également envisageables : - la structure des logements peut être déterminée à l'aide de l'EWID - la structure d'âge et les changements de propriétaire des biens peuvent être déterminés à l'aide des données du registre foncier - les coûts d'entretien peuvent être déterminés à l'aide des données fiscales au niveau des biens - le rapport entre les appartements loués et les appartements occupés par leur propriétaire - l'évolution de la population / la migration nette - ...

Principes

1. évaluations régionales des registres et des taxes, qui peuvent s'étendre au-delà des limites communales (sélection libre)
2. liaison des données des registres et des taxes avec GERES (EGID) et GIS
3. dans une première phase, les données sont disponibles au niveau EGID (ne contient pas de données personnelles)

Modèle de données de haut niveau : zone -> bâtiment -> personne (dans le GERES, chaque personne est affectée à un bâtiment) -> données du registre fiscal et du registre de la population.

Bénéficiaires :

Décideurs dans les municipalités / GR

Idée de solution

La base est une zone sélectionnée dans le géoportail. Avec les identifiants des bâtiments (EGID) qu'ils contiennent, les informations consolidées sur la zone sont renvoyées via un service (par exemple, registre / taxes). Ce service n'est pas disponible pour les Data Hackdays. Comme alternative, un CSV est fourni où toutes les informations sont disponibles au niveau de l'EGID. Il s'agit de données générées afin de contourner le problème de la protection des données (aucun accord de confidentialité n'est nécessaire).

Base de données

• SIG (coordonnées EGID)
• Bureau du contrôle des habitants (données du registre)
• GERES (cartographie du sujet fiscal EGID)
• Données fiscales (revenus, patrimoine, rendements fiscaux)

Responsable du défi

Hans Frauchiger - Chef de la direction et du contrôle de gestion à l'Administration fiscale du canton de Berne, FIN-SV

Carolin Baumgartner - Responsable de la sécurité SV, FIN-SV