Kostal Pico to PostgreSQL

05.2013 / Category: / Tags:

Everybody needs a little toy to play with, so I thought: Why not buy a toy helping me to get rid of my energy bill? So, I ordered a 10.5 kWp photovoltaic system for my house. The system was shipped with a Kostal Pico inverted to make sure electricity can be used directly by the grid.

Why should I use it?

Kostal offers some additional device which allows people to chart your electricity production over time. But, why pay if you can do it yourself using some little shell script and most important: PostgreSQL

As there is hardly code available on the net showing how to access Kostal Pico, I decided to publish this one:

And what does Kostal offer?

Kostal Pico offers a somewhat disgusting web interface providing you with textual data. And yes, the interface is disgusting, and it took a while to figure out what those columns actually mean. The nasty thing about it is that the data stream does not contain a real timestamp but the number of seconds since the system has been in production (if the system has been shut down for maintenance, this counter will NOT advance, but i have left this one out – it would be too complicated for a prototype to take into consideration).

We simply use wget to fetch the data and pipe the stuff through a series of processing steps. The important thing here is that we have to cut out some lines and columns to turn this into a PostgreSQL readable format (in our case semi-colon separated).
In my case the data will have 4 columns:

The first column in our PostgreSQL table is the timestamp we have talked about already. The next three columns represent my 3 solar fields. Each of those fields will report its production data. Once the data is downloaded, we merge it into our existing table to make sure that we can easily run the script over and over again without destroying something. I have yet to figure out if Kostal is always sending all data it has – but, we don't have to care because PostgreSQL will persist the data anyway and we just add what is missing. Our merging process can be a little crude here because we get just one row every 15 minutes (making the amount of data in our PostgreSQL table close to irrelevant).

Analyzing a timeseries

Now we can run some SQL to analyze the timeseries we got in our database. To make the code a little more robust I have added the time when the system was started up to the SQL directly. We should surely calculate that with some simple windowing function, but this would give us a wrong chart in case the HTTP request itself would fail.
One issue is that I wanted the timeseries to be complete. Meaning: If there is no production during the night Kostal Pico will not provide us with data for this time – so we somehow have to fill the gaps. We do so by outer joining our aggregated data with generate_series.

The result looks like that:

We can see a nice production peak during morning hours. This is expected because 2/3 of the system are heading east and 1/3 of those panels are heading roughly south. The data looks realistic – a peak production of 8.2kwh shortly before noon is perfectly expected.

The nice thing here is that it is not too hard to pipe the stuff to gnuplot or any other software to come up with a nice chart.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

Comments are closed.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram