Instructions for data users
===========================

First steps with the Datapool
------------------------------

The datapool is a package that uses a PostgreSQL database to store the sensor data and provides for a series
of functionalities in order to add, modify and delete data sources.

But, what is a database? Find more information `here <https://en.wikipedia.org/wiki/Database>`_.

`PostgrSQL <https://www.postgresql.org/about/>`_ is a powerful, open source, object-relational database.

To access the data stored on the database we use `SQL (Structured Query Language) <https://en.wikipedia.org/wiki/SQL>`_.
It makes sense to get a little familiar with the basic querying functionality in order to be able to access the data.
This `SQL tutorial <https://www.w3schools.com/sql/default.asp>`_ is a helpful reference.

The figure below shows the current database layout.

.. image:: ./graphics/DataModel.svg

Database schema :download:`download <graphics/DataModel.svg>`.


Connect to the Datapool
-----------------------

To connect with the database you need the following information from your admin:

- The host, e.g. "a.server.com"
- The port of the database, e.g. 5432
- The name of the database, e.g. "datapool"
- A database user name, e.g "data_user"
- The database user password

You can connect directly to the database via `psql <http://postgresguide.com/utilities/psql.html>`_.
However, it is more convenient to load the data required directly into the environment used for further analysis.

Python
------

We recommend using the `datapool_client package <https://github.com/Eawag-SWW/datapool_client>`_,
which uses *psycopg2* and offers some tailored high level functionality.


Datapool Client Package usage
`````````````

.. include:: examples/example_queries_datapool_client.rst

R
---

There is the possibility to use the `RPostgreSQL package <https://cran.r-project.org/web/packages/RPostgreSQL/>`_ to connect to the datapool using R.

.. example-sql-queries
Example SQL queries
--------------------

The SQL language may look cumbersome at first. However, it gives a lot of flexibility and allows to express
even very complex queries.
We will explore a few queries starting from some simple ones to more complex ones, to give an idea of its functionality.

.. include:: examples/example_queries.rst