Building Pipelines for the Solar Industry

Internship Report

Geographic Information (Geodata)

id	name	category	elevation_m	geometry
1	Berlin Center	city	34	POINT(13.4050 52.5200)
2	Potsdam Station	transport	38	POINT(13.0657 52.3914)
3	Forest Plot A	sample_site	72	POINT(12.9876 52.4123)
4	Lake View	observation	29	POINT(13.1025 52.4568)
5	Weather Sensor 01	sensor	45	POINT(13.2211 52.3347)

Definition: [...] Information about locations on or near the surface of the Earth [...]"

michaelminn.net/tutorials/arcgis-pro-terrain/ Liu & Özsu, 2009: Encyclopedia of Database Systems

Basics: Spatial Operations

Definition: (Spatial) operations refer to the operations that an end user performs on maps stored in a database.

geopandas.org Liu & Özsu, 2009: Encyclopedia of Database Systems

Basics: Geographic Information Systems (GIS)

Definition: "[...] is a computer application designed to perform a wide range of operations on geographic information. [...] A GIS includes functions to input, store, visualize, export, and analyze (geographic) information."

©OpenStreetMap contributors Liu & Özsu, 2009: Encyclopedia of Database Systems

Internship Overview

Company: Enerparc AG

Industry domain: Solar industry

Duration: 360 hours over 4 months

Role: GIS Developer

My Background

Bachelor in Geography: Specialised in Remote Sensing and Geographic Information Systems and had 4 years of experience tutoring RS/GIS

Master in Computational Science: Currently pursuing, in my 4th semester

Why Enerparc

First industry experience

How is the dev experience in a non-tech company?

Enerparc Expertise

The company is involved in every stage of solar energy projects, acting as both a project developer, operator and service provider for third parties.

ENERPARC AG. enerparc.de

Enerparc in numbers

Covers the electricity needs of 1.2 million households

ENERPARC AG. enerparc.de

Enerparc Solar Parcs in Germany

ENERPARC AG. enerparc.de/en/projects#solar-parks-germany

My Team

Department: GIS Development & Administration

Personnel: 1 Team lead, 1 Full time, 2 Working students

Tasks: GIS Plugin development, Geodata engineering, WebGIS administration, Geodatabase administration

Project

Research question: How to handle geodata pipelines?

Needed: Daily updated geodata on energy assets of Germany

Use cases: Monitoring of market activity

Architecture: ETL (extract, transform, load)

ETL Pipeline

own illustration

Extraction

own illustration

Extraction: Marktstammdatenregister

Description: Official registry of all facilities and units in the German energy system

Provider: Bundesnetzagentur

Access Methods: SOAP API (Simple Object Access Protocol) / XML bulk download

marktstammdatenregister.de

Extraction: Marktstammdatenregister

marktstammdatenregister.de

Extraction: Marktstammdatenregister

Development:

1. Tried out the SOAP API
2. Realised an API is made for small extractions
3. Tried out the bulk download with hundreds of XML files
4. Realised the XML's are relational to each other and encoded
5. Wrote a whole library to handle the XML dump
6. Found out there is already an open source library for this (open-mastr)

Extraction: Enerparc Assets

Description: Current photovoltaic systems owned by Enerparc AG

Provider: Enerparc AG Engineering

Access Methods: REST API (Representational State Transfer)

Extraction: Enerparc Assets

Development:

1. Asked the engineering department for the list of IDs
2. Realised they always change
3. Asked the engineering department for access to the DB
4. They created a new REST API endpoint for this
5. Accessed this endpoint over a simple http request

Extraction: Borders

Description: Country & Municipality Borders

Provider: Enerparc AG GIS

Access Methods: SQL Queries

Database Model: Relational

Extraction: Borders

Development:

1. Added the data manually to the repository
2. Realised borders can also change
3. Accessed them using SQL statements during the processing

Transformation

own illustration

Transformation

Development:

1. Started with Python and GeoPandas (GeoDataFrames)
2. Unknowingly reinvented SQL in Python
3. Realised SQL is way better for stuff like this
4. Used a local SQLite DB for processing
5. But still needed to do some spatial operations in python
6. Realised the destination server has PostGIS (SQL for spatial operations)
7. Moved whole pipeline into the database with some absurdly long SQL aka "I replaced my entire stack with PostgreSQL"
8. Processing took over a day and crashed the server
9. Realised the database server only has 4 GB RAM and a weak CPU
10. Moved everything back to Python
11. They lived happily ever after

Loading

own illustration

Loading

Development:

1. Used GeoPandas built in upload functionality
2. Found out open-mastr can load it directly to the PostgreSQL database
3. Since I moved everything to the DB anyway, this was fitting
4. But then again I moved everything back

Architecture

own illustration

Results: Photovoltaic Systems

Data: Marktstammdatenregister

Results: Battery Storage Systems

Data: Marktstammdatenregister

Results: Wind Turbines

Data: Marktstammdatenregister

Reflections: Building ETL Pipelines

• "Those who do not understand SQL are condemned to reinvent it, poorly." Adapted from Henry Spencer

• Check your system specifications before you change architectures.

• There is still a lot to learn ...

Reflections: First Industry Experience

Positive

• Reduced pressure to perform
• Satisfaction of seeing your creation in production
• Obstkorb

Negative

• A lot of meetings
• Slow processes
• Rigid structures

Reflections: Dev Experience in a Non-Tech Company

Positive

• You can learn a lot
• More independence
• More control over your projects
• Access to more important projects

Negative

• More responsibilities
• More task switching
• Less supervision

Next Project

Geo-ETL library for WFS Services

• Amtliches Liegenschaftskatasterinformationssystem (Gemarkungen, Flure, Flurstuecke, Gebaeudedaten, ...)
• Bundesamt fuer Naturschutz (Vogelschutzgebiete, Nationalparke, Naturschutzgebiete,...)
• Biotope
• ...

Acknowledgments

Thanks to my team and Enerparc AG.

And thank you for listening.