3. postgreSQL Installation and Configuration

postgreSQL Database System

For your own database instance you have to install the postgreSQL installer via apt-get. Perform the following steps to install the database system:

1. Create and edit the PostgreSQL repository by running the command: sudo nano /etc/apt/sources.list.d/pgdg.list. Paste the following line into the file: deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main.
2. Save and Exit the file.
3. Download & import the repository key: wget --quiet -O -https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
4. Update your system's repository: sudo apt-get update && sudo apt-get upgrade
5. Install PostgreSQL via the following command: sudo apt-get install postgresql-9.3 pgadmin3 postgresql-contrib
6. Provide the necessary information (like user, password), which will be asked during the setup progress.

To access the database from everywhere, you have to set some parameters and settings. Grant access on the port on your host, which you have defined in the database setup progress.

1. To access the database publicly (which is mandatory), you also have to configure the database itself. Navigate to the postgreSQL installation /etc/postgresql/9.3/main and open the hba.conf-file. Read the instructions carefully. To grant access from the world wide web without any restrictions, you need to place the following line: host all all 0.0.0.0/0 md5 in the corresponding code-block.
2. Open the postgresql.conf via sudo nano postgresql.conf and change the "# CONNECTIONS AND AUTHENTICATION #" section in that way that the server listen to all addresses: listen_addresses = '*'
3. Save all changes and restart the postgreSQL service via sudo /etc/init.d/postgresql restart

PostGIS Extension

To install PostGIS perfrom the following steps:

1. Add PostGIs Source to your repository: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list'
2. Add repository key: wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
3. Update your apt-get list: sudo apt-get update
4. Install PostGIS via sudo apt-get install postgresql-9.3-postgis
5. Enable Adminpack via logging in as postgre user: sudo -u postgres psql
6. Create PostGIS Extension: CREATE EXTENSION adminpack; and quit pgsql: \q

Firewall Settings

Please note that postgreSQl communicates through the in the setup defined port (which is by default port 5432), which have to be opened by creating a Firewall exception to work properly. To define a new Firewall Rule, follow the steps below:

1. Enable the ufw-firewall: ufw allow
2. Type ufw allow 5432/tcp to open the port 5432.
3. To save the changes type ufw reload

The database is now accessible for example using pgadmin.

pgAdmin Connection

• Name: nickname you want to give your connection
• host: localhost (until you change the connection settings)
• port: 5432 by default
• maintenance DB: postgres by default
• username: whatever you chose in the step above
• password: whatever you chose in the step above

Enable postgis extensions on new database

Since you installed PostgreSQL and PostGIS from the PostgreSQL apt repository, you now have all the extensions for PostgreSQL, including PostGIS:

1. Expand the database tree in PGAdmin, and reveal the extensions node
2. Right-click the extensions node, and click new extension
3. Enable the postgis extension, as well as postgis_topology

Database Setup

Mainly the application is based on four tables, which is used for storing the provided information. On the server side, we save the temporal events and pub information, which are not save in Openstreetmap itself. Database functions are used for handling temporal events.

1. To set up the used tables, perform the following SQL commands.

   CREATE TABLE pub( pub_ref SERIAL NOT NULL, beer_price FLOAT, happy_hour BOOLEAN, PRIMARY KEY (pub_ref) );

   CREATE TABLE temporal_event( event_id SERIAL NOT NULL, pub_ref SERIAL NOT NULL, name TEXT, type TEXT, description TEXT, event BOOLEAN, entry_fee TEXT, PRIMARY KEY (event_id), FOREIGN KEY (pub_ref) REFERENCES pub ON DELETE CASCADE ON UPDATE CASCADE );

   CREATE TABLE closed( closed_id SERIAL NOT NULL, event_id SERIAL NOT NULL, start_time TIMESTAMP, end_time TIMESTAMP, PRIMARY KEY (closed_id), FOREIGN KEY (event_id) REFERENCES temporal_event ON DELETE CASCADE ON UPDATE CASCADE );

   CREATE TABLE opened( opened_id SERIAL NOT NULL, event_id SERIAL NOT NULL, start_time TIMESTAMP, end_time TIMESTAMP, PRIMARY KEY (opened_id), FOREIGN KEY (event_id) REFERENCES temporal_event ON DELETE CASCADE ON UPDATE CASCADE );

   /* Function determines if a certain pub is opened at a certain time Param: p_id INTEGER OSM Pub ID of the pub Param: ts TIMESTAMP Checks if the pub is opened at this time Returns: true: pub is opened, false: pub is closed */ CREATE FUNCTION is_open(p_id integer, ts timestamp) RETURNS BOOLEAN AS $$ DECLARE result BOOLEAN := FALSE; DECLARE number integer; BEGIN SELECT COUNT(event_id) INTO number FROM pub NATURAL INNER JOIN temporal_event NATURAL INNER JOIN opened WHERE pub.pub_ref = p_id AND (ts BETWEEN opened.start AND opened.end); IF number != 0 THEN result := TRUE; END IF; SELECT COUNT(event_id) INTO number FROM pub NATURAL INNER JOIN temporal_event NATURAL INNER JOIN closed WHERE pub.pub_ref = p_id AND (ts BETWEEN closed.start AND closed.end); IF number != 0 THEN result := FALSE; END IF; RETURN result; END; $$ LANGUAGE 'plpgsql';

   /* Function creates multiple entries in the "closed" respectively the "opened"-table Param: event_id INTEGER ID of the event for which the periodic opening/closed hours shall be created Param: state TEXT can be 'opened' or 'closed', decides which table will be used Param: start_date TIMESTAMP Start date of the first occurrence of opening hour/closed hour Param: end_date TIMESTAMP End date of the first occurrence of opening hour/closed hour Param: end_periodic TIMESTAMP Last occurrence of the periodic event Param: periodic INTEGER Timespan between end_periodic and next start of the event */ CREATE FUNCTION insert_periodic(event_id integer, state TEXT, start_date timestamp, end_date timestamp, end_periodic timestamp, periodic integer) RETURNS void AS $$ DECLARE start1 integer := EXTRACT(EPOCH FROM start_date); DECLARE end1 integer := EXTRACT(EPOCH FROM end_date); DECLARE end_periodic INTEGER := EXTRACT(EPOCH FROM end_periodic); BEGIN WHILE (start1 <= end_periodic) LOOP IF state = 'opened' THEN INSERT INTO opened (event_id, start_time, end_time) VALUES (event_id, to_timestamp(start1), to_timestamp(end1)); END IF; IF state = 'closed' THEN INSERT INTO closed (event_id, start_time, end_time) VALUES (event_id, to_timestamp(start1), to_timestamp(end1)); END IF; start1 := start1 + periodic; end1 := end1 + periodic; END LOOP; END $$ LANGUAGE 'plpgsql';

   /* Function deletes multiple entries from "closed" and the "opened"-table Param: e_id INTEGER ID of the event for which the periodic opening/closed hours shall be deleted Param: start_date TIMESTAMP Start date of the first occurrence of opening hour/closed hour Param: end_date TIMESTAMP End date of the first occurrence of opening hour/closed hour Param: end_periodic TIMESTAMP Last occurrence of the periodic event Param: periodic INTEGER Timespan between end_periodic and next start of the event */ CREATE FUNCTION delete_periodic(e_id integer, start_date timestamp, end_date timestamp, end_periodic timestamp, periodic integer) RETURNS void AS $$ DECLARE start1 integer := EXTRACT(EPOCH FROM start_date); DECLARE end1 integer := EXTRACT(EPOCH FROM end_date); DECLARE end_periodic INTEGER := EXTRACT(EPOCH FROM end_periodic); BEGIN WHILE (start1 <= end_periodic) LOOP DELETE FROM closed WHERE event_id = e_id AND start_time = to_timestamp(start1) AND end_time = to_timestamp(end1); DELETE FROM opened WHERE event_id = e_id AND start_time = to_timestamp(start1) AND end_time = to_timestamp(end1); start1 := start1 + periodic; end1 := end1 + periodic; END LOOP; END $$ LANGUAGE 'plpgsql';