Geofency Backend DB

What is Geofency?

Geofency is an iPhone/IOS app automatically tracks the times when you enter or leave your points of interest. Works great to monitor working hours, client visits, lunch times and the stays on your favorite places! You find oud more at Geofency site

There are losts of mobile applications with the same functionality, but I prefer this app overall for the clean interface and simplicity.

Why backend DB

The database informations can be used to generates reports about your work hours, time you spend with clients or project and more.

Addtionally you can integrate the data with other services such as FHEM, FHEM is a GPL'd perl server for house automation, which has lots of features:

  • Support for a lot of protocols used in house automation, audio/video devices, weather services, online calendars and more.

  • Autocreating devices/logs when receiving data from a new device: start FHEM and watch how the plots of your sensors are appearing automatically.

  • logging events to files or a database, with regexp filters notifying external programs or scripts when receiving certain events
    timed commands (e.g. switching a lamp on from sunset till midnight)

  • Lot of interfaces: simple text, JSON, XML, each of them over plain TCP/IP, SSL or HTTP.

  • Modular architecture with currently over 150 modules, easy to add your special device

  • Lot of frontends, choose your favorite

This is beyond this article but you can dig more into FHEM's documentation

Postgres has json operators to to work with FHEM

Server requirements

The informations below tested successfully on Ubuntu 13.10

Servers Installations

Assuming you have knowledge or some with Linux

Let's begin.
There are few Apache and PHP modules should be installed in order to authenticate from geofency app.

First if you don't have Apache/PHP/Postgresql already installed, please refer to Ubuntu offecial documantation or any OS you prefer to use.

Modules installation
  • From the Linux shell install the required modules.
apt-get install php5-pgsql libaprutil1-dbd-pgsql postgresql-9.1 libapache2-mod-php5 libapache2-mod-authnz-external postgresql-contrib-9.1  
  • Before we configure Apache server, make sure the DB modules are loaded.
cd /etc/apache2/mods-enabled  
  • Check dbd modules are loaded If not, create a link from mods-available directory to mods-enable directory.
ln -s ../mods-available/dbd.load .  
ln -s ../mods-available/authn_dbd.load .  
  • Restart Apache
service apache2 restart  

If you see any errors, check Apache default error logs or syslog
in /var/log/apache2/error.log and /var/log/syslog

Postgres DB Configurations
  • Change to postgres account
su - postgres  
  • Login to Postgresql DB and enable the below extentions.
psql template1  
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;  
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;  
  • Create the DB admin account
CREATE USER geoadmin WITH PASSWORD 'GeoAdmin123';  
  • Create the database
CREATE DATABASE geodb  
  WITH OWNER = geoadmin
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       CONNECTION LIMIT = -1;
COMMENT ON DATABASE geodb IS 'Apache and PHP authentication Geofency';  
  • Grant DB PRIVILEGES to geoadmin account
GRANT ALL PRIVILEGES ON DATABASE geodb to geoadmin;  
  • Login to Postgresql with username geoadmin and password GeoAdmin123
psql -h 127.0.0.1 -d geodb -U geoadmin -W  
  • Copy and paste the DB tables below
CREATE TABLE geodata (  
    id integer NOT NULL,
    datetime timestamp(6) without time zone NOT NULL,
    device character varying(200) NOT NULL,
    locationid character varying(200) DEFAULT ''::character varying NOT NULL,
    latitude character varying(20) NOT NULL,
    longitude character varying(20) NOT NULL,
    entry character varying(20) NOT NULL,
    radius real NOT NULL,
    name character varying(200)
);
ALTER TABLE public.geodata OWNER TO geoadmin;

CREATE SEQUENCE geodata_id_seq  
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE public.geodata_id_seq OWNER TO geoadmin;  
ALTER SEQUENCE geodata_id_seq OWNED BY geodata.id;

CREATE TABLE groups (  
    group_id integer NOT NULL,
    group_name character varying(20) NOT NULL,
    group_desc character varying(200),
    group_permission smallint NOT NULL
);
ALTER TABLE public.groups OWNER TO geoadmin;

CREATE SEQUENCE groups_group_id_seq  
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE public.groups_group_id_seq OWNER TO geoadmin;  
ALTER SEQUENCE groups_group_id_seq OWNED BY groups.group_id;

CREATE TABLE users (  
    user_id integer NOT NULL,
    username character varying(20) NOT NULL,
    password character varying(40) NOT NULL,
    group_id integer DEFAULT 2 NOT NULL
);
ALTER TABLE public.users OWNER TO geoadmin;

CREATE SEQUENCE users_user_id_seq  
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE public.users_user_id_seq OWNER TO geoadmin;  
ALTER SEQUENCE users_user_id_seq OWNED BY users.user_id;  
ALTER TABLE ONLY geodata ALTER COLUMN id SET DEFAULT nextval('geodata_id_seq'::regclass);  
ALTER TABLE ONLY groups ALTER COLUMN group_id SET DEFAULT nextval('groups_group_id_seq'::regclass);  
ALTER TABLE ONLY users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'::regclass);

SELECT pg_catalog.setval('geodata_id_seq', 1, false);  
SELECT pg_catalog.setval('groups_group_id_seq', 1, false);  
SELECT pg_catalog.setval('users_user_id_seq', 1, false);

ALTER TABLE ONLY geodata  
    ADD CONSTRAINT geodata_id_pkey PRIMARY KEY (id);

ALTER TABLE ONLY groups  
    ADD CONSTRAINT groups_pkey PRIMARY KEY (group_id);

ALTER TABLE ONLY users  
    ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);

ALTER TABLE ONLY users  
    ADD CONSTRAINT users_username_key UNIQUE (username);

REVOKE ALL ON SCHEMA public FROM PUBLIC;  
REVOKE ALL ON SCHEMA public FROM postgres;  
GRANT ALL ON SCHEMA public TO postgres;  
GRANT ALL ON SCHEMA public TO PUBLIC;  
  • Create web admin account and groups for later use.
insert into "public"."groups" ( "group_name", "group_desc", "group_permission") values ( 'admins', 'GeoFency Adminstrators Group', '1');

insert into "public"."groups" ( "group_name", "group_desc", "group_permission") values ( 'users', 'GeoFency Adminstrators Group', '2');

insert into users  
(user_id, username, password, group_id) VALUES (1,'admin','{SHA}'||encode(digest('AdminPassword123','sha1'),'base64'), '1');

The username is admin and the password is AdminPassword123
The group with permission 1 admins and users with permission2
The group permissions are used for later to allow only users with admins permission to add users to the database from the PHP web interface.

  • Check your configurations by issuing the following commands:
 geodb=> \d
                 List of relations
 Schema |        Name         |   Type   |  Owner
--------+---------------------+----------+----------
 public | geodata             | table    | geoadmin
 public | geodata_id_seq      | sequence | geoadmin
 public | groups              | table    | geoadmin
 public | groups_group_id_seq | sequence | geoadmin
 public | users               | table    | geoadmin
 public | users_user_id_seq   | sequence | geoadmin
(6 rows)
geodb=> SELECT * FROM users;  
geodb=> SELECT * FROM groups;  
geodb=> SELECT * FROM geodata;  

If you experiance error with locale

  • Install locales package and reconfigure locale
aptitude install locales  
dpkg-reconfigure locales  
export LC_ALL=en_US.UTF-8  
export LANG=en_US.UTF-8  
export LANGUAGE=en_US.UTF-8  

More information can be found here at Github

To make these changes active in the current shell, source the .bashrc

$ source ~/.bashrc
  • Modify Postgres configuration to allow www-data Apache user to map to geoadmin account, otherwise the queries from HTTP BASIC AUTH will fail. Geofency App uses this method to authenticate users.

  • Edit pg_hba.conf located as default installation under ubuntu here /etc/postgresql/9.1/main/pg_hba.conf and pg_ident.conf located in the same directory

Your ident mapping should like like below.

pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   geodb           geoadmin                                ident map=apache  
# "local" is for Unix domain socket connections only
local   all             all                                     peer  
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5  
# IPv6 local connections:
host    all             all             ::1/128                 md5  

pg_ident.conf

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
apache          www-data                geoadmin  
  • Restart Postgresql
service postgresql restart  
Apache mod_dbd configurations
  • Edit Apache main site file or virtual host config file. On Ubuntu default Apache is the file located in /etc/apache2/sites-enabled/000-default.conf or your virtual host file in the same directory.
    The basic configurations should like the following:
mkdir /var/www/geo  
vi /etc/apache2/sites-enabled/000-default.conf  
<VirtualHost *:80>  
        ServerName YOUR-DOMAIN.com
        ServerAlias www.YOUR-DOMAIN.com
        ServerAdmin webmaster@YOUR-DOMAIN.com
        DocumentRoot /var/www
        ErrorLog ${APACHE_LOG_DIR}/error.log
        CustomLog ${APACHE_LOG_DIR}/access.log combined
# mod_dbd configuration
        DBDriver pgsql
        DBDParams "hostaddr=127.0.0.1 host=localhost port=5432 dbname=geodb user=geoadmin password=GeoAdmin123"
        DBDMin  4
        DBDKeep 8
        DBDMax  20
        DBDExptime 300
<Directory /var/www/geo>  
        AuthType Basic
        AuthName "GeoFency"
        AuthBasicProvider dbd
        Require valid-user
        AuthDBDUserPWQuery "select password FROM users where username = %s"
</Directory>  
</VirtualHost>  

In the configuration above, I placed in geofancy files in a sub-directory in the main www folder called geo /var/www/geo, you can choose any directory name you prefer.

  • Restart Apache
service apache2 restart  

If Apache does not start, check the logs in /var/log/apache2/error.log, you can always tail the logs during the service restart.

service apache2 restart; tailf /var/log/apache2/error.log  
PHP Scripts

I wrote a basic scripts to collect geo data from Geofancy App and add it to the database with basic admin page to add users.

First we want to make a sub-directory in the main /var/www directory.

  • Make the directory
mkdir /var/www/admin/lib  
mkdir /var/www/admin  
cd /var/www/geo  

The sub-directory for PHP scripts configurations.

  • Create a file in the lib directory ../admin/lib/DB.php directory and copy and paste the contant below. The admin directory for placing Geofency basic dashboard
vi lib/DB.php  
<?php  
/* 
// pgsql hostname 
*/
$pgsql_hostname = 'localhost';
/*
// pgsql username 
*/
$pgsql_username = 'geoadmin';
/*
// pgsql password
*/
$pgsql_password = 'GeoAdmin123';
/*
// database name 
*/
$pgsql_dbname = 'geodb';
/* 
// select the users name from the database 
*/
$dbh = new PDO("pgsql:host=$pgsql_hostname;dbname=$pgsql_dbname", $pgsql_username, $pgsql_password);
/*
// set the error mode to excptions 
*/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>
  • Create a index.php file in the geo directory and copy and paste the content below.
vi index.php  
<?php  
include_once '../admin/lib/DB.php';  
$locationid = addslashes($_REQUEST['id']);
$name       = addslashes($_REQUEST['name']);
$device     = addslashes($_REQUEST['device']);
$entry      = addslashes($_REQUEST['entry']);
$latitude   = addslashes($_REQUEST['latitude']);
$longitude  = addslashes($_REQUEST['longitude']);
$radius     = addslashes($_REQUEST['radius']);
$client     = $_SERVER['REMOTE_ADDR'];

$stmt  = $dbh->prepare("INSERT INTO geodata (name, entry, device, locationid, latitude, longitude, radius, datetime)
                        values ('".$name."','".$entry."', '".$device."', '".$locationid."', '".$latitude."', '".$longitude."', '".$radius."', now());");
$stmt->execute();
if ( $stmt === false ){  
        echo "DB Error";
} else {
        echo "Data received successfully from ".$client;
}
$stmt->closeCursor();
?>
  • Create file ../admin/index.php and copy and past the content below.
vi ../admin/index.php  
<?php  
/*** begin our session ***/
session_start();  
/*** set a form token ***/
$form_token = md5( uniqid('auth', true) );
/*** set the session form token ***/
$_SESSION['form_token'] = $form_token;
?>

<html>  
<head>  
<title>Login</title>  
</head>

<body>  
<center>  
<h2>Login</h2>  
<form action="dashboard.php" method="post">  
<fieldset>  
<p>  
<label for="username">Username</label>  
<input type="text" id="username" name="username" value="" maxlength="20" />  
</p>  
<p>  
<label for="password">Password</label>  
<input type="password" id="password" name="password" value="" maxlength="20" />  
</p>  
<p>  
<input type="hidden" name="form_token" value="<?php echo $form_token; ?>" />  
<input type="submit" value="Login" />  
</p>  
</fieldset>  
</form>  
</center>  
</body>  
</html>  
  • Create file ../admin/dashboard.php and copy and past the content below.
vi ../admin/dashboard.php  
<?php

include_once __DIR__ . '/lib/DB.php';  
/*** begin our session ***/
session_start();  
/*** set a form token ***/
$form_token = md5( uniqid('auth', true) );
/*** set the session form token ***/
$_SESSION['form_token'] = $form_token;

/*** check if the users is already logged in ***/
if(isset( $_SESSION['user_id'] ))  
{
    $message = 'Users is already logged in';
}
/*** check that both the username, password have been submitted ***/
if(!isset( $_POST['username'], $_POST['password']))  
{
    $message = 'Please enter a valid username and password';
}
/*** check the username is the correct length ***/
elseif (strlen( $_POST['username']) > 20 || strlen($_POST['username']) < 4)  
{
    $message = 'Incorrect Length for Username';
}
/*** check the password is the correct length ***/
elseif (strlen( $_POST['password']) > 20 || strlen($_POST['password']) < 4)  
{
    $message = 'Incorrect Length for Password';
}
/*** check the username has only alpha numeric characters ***/
elseif (ctype_alnum($_POST['username']) != true)  
{
    /*** if there is no match ***/
    $message = "Username must be alpha numeric";
}
/*** check the password has only alpha numeric characters ***/
elseif (ctype_alnum($_POST['password']) != true)  
{
        /*** if there is no match ***/
        $message = "Password must be alpha numeric";
}
else  
{
    /*** if we are here the data is valid and we can insert it into database ***/
    $username = filter_var($_POST['username'], FILTER_SANITIZE_STRING);
    $password = filter_var($_POST['password'], FILTER_SANITIZE_STRING);

    /*** now we can encrypt the password ***/
    $password = '{SHA}' . base64_encode(sha1($password, true));
    try
    {
        /*** prepare the select statement ***/
        $stmt = $dbh->prepare("SELECT user_id, username, password FROM users
                    WHERE username = :username AND password = :password");

        /*** bind the parameters ***/
        $stmt->bindParam(':username', $username, PDO::PARAM_STR);
        $stmt->bindParam(':password', $password, PDO::PARAM_STR, 40);

        /*** execute the prepared statement ***/
        $stmt->execute();

        /*** check for a result ***/
        $user_id = $stmt->fetchColumn();

        /*** if we have no result then fail boat ***/
        if($user_id == false)
        {
                $message = 'Login Failed';
        }
        /*** if we do have a result, all is well ***/
        else
        {
                /*** set the session user_id variable ***/
                $_SESSION['user_id'] = $user_id;

                /*** tell the user we are logged in ***/
                $message = 'You are now logged in';
        }


    }
    catch(Exception $e)
    {
        /*** if we are here, something has gone wrong with the database ***/
        $message = 'We are unable to process your request. Please try again later"';
    }
}
?>
<html>  
<head>  
<title>Dashboard</title>  
</head>

<body>  
<center><h1>Welcome to Geofency Dashboard</h1></center>  
<hr />  
<h2>Add user</h2>  
<form action="addusersubmit.php" method="post">  
<fieldset>  
<p>  
<label for="username">Username</label>  
<input type="text" id="username" name="username" value="" maxlength="20" />  
</p>  
<p>  
<label for="password">Password</label>  
<input type="password" id="password" name="password" value="" maxlength="20" />  
</p>  
<p>  
<label for="groups">Groups</label>  
<select name="group_id">  
<option value="1">Admins</option>  
<option value="2">Users</option>  
</select>  
</p>  
<p>  
<input type="hidden" name="form_token" value="<?php echo $form_token; ?>" />  
<input type="submit" value="Add User" />  
</p>  
</fieldset>  
</form>  
</body>  
</html>  
  • Create file ../admin/addusersubmit.php and copy and past the content below.
vi ../admin/addusersubmit.php  
<?php  
include_once __DIR__ . '/lib/DB.php';  
/*** begin our session ***/
session_start();

/*** first check that both the username, password and form token have been sent ***/
if(!isset( $_POST['username'], $_POST['password'], $_POST['group_id'],$_POST['form_token']))  
{
    $message = 'Please enter a valid username and password';
}
/*** check the form token is valid ***/
elseif( $_POST['form_token'] != $_SESSION['form_token'])  
{
    $message = 'Invalid form submission';
}
/*** check the username is the correct length ***/
elseif (strlen( $_POST['username']) > 20 || strlen($_POST['username']) < 4)  
{
    $message = 'Incorrect Length for Username';
}
/*** check the password is the correct length ***/
elseif (strlen( $_POST['password']) > 20 || strlen($_POST['password']) < 4)  
{
    $message = 'Incorrect Length for Password';
}
/*** check the username has only alpha numeric characters ***/
elseif (ctype_alnum($_POST['username']) != true)  
{
    /*** if there is no match ***/
    $message = "Username must be alpha numeric";
}
/*** check the password has only alpha numeric characters ***/
elseif (ctype_alnum($_POST['password']) != true)  
{
        /*** if there is no match ***/
        $message = "Password must be alpha numeric";
}
else  
{
        $stmt = $dbh->prepare("SELECT users.username, users.password, users.group_id, groups.group_id
                                FROM groups INNER JOIN users ON groups.group_id = users.group_id
                                WHERE users.group_id = '1' AND user_id = :user_id");
        $stmt->bindParam(':user_id', $_SESSION['user_id'], PDO::PARAM_INT);
        $stmt->execute();
        $username = $stmt->fetchColumn();
        if($username == false)
        {
            $message = 'Access Denied';
        }
        else {
            /*** if we are here the data is valid and we can insert it into database ***/
    $username = filter_var($_POST['username'], FILTER_SANITIZE_STRING);
    $password = filter_var($_POST['password'], FILTER_SANITIZE_STRING);
    $group_id   = filter_var($_POST['group_id'], FILTER_SANITIZE_STRING);

    /*** now we can encrypt the password ***/
    $password = '{SHA}' . base64_encode(sha1($password, true));
    try
    {
        /*** prepare the insert ***/
        $stmt = $dbh->prepare("INSERT INTO users (username, password, group_id ) VALUES (:username, :password, :group_id)");

        /*** bind the parameters ***/
        $stmt->bindParam(':username', $username, PDO::PARAM_STR);
        $stmt->bindParam(':password', $password, PDO::PARAM_STR, 40);
        $stmt->bindParam(':group_id', $group_id, PDO::PARAM_STR);

        /*** execute the prepared statement ***/
        $stmt->execute();

        /*** unset the form token session variable ***/
        unset( $_SESSION['form_token'] );

        /*** if all is done, say thanks ***/
        $message = 'New user added';
    }
    catch(Exception $e)
    {
        /*** check if the username already exists ***/
        if( $e->getCode() == 23000)
        {
            $message = 'Username already exists';
        }
        else
        {
            /*** if we are here, something has gone wrong with the database ***/
            $message = 'We are unable to process your request. Please try again later"';
        }
}
    }
}
?>
<html>  
<head>  
<script>  
function goBack()  
  {
  window.history.back()
  }
</script>  
<title>Successfull</title>  
</head>  
<body>  
<p><?php echo $message; ?>  
<br />  
<button onclick="goBack()">Go Back</button>  
</body>  
</html>  

Your directory should look like:

.
??? admin
?   ??? addusersubmit.php
?   ??? dashboard.php
?   ??? index.php
?   ??? lib
?       ??? DB.php
??? geo
    ??? index.php
  • Change the permissions to www-data or apache user
chown -R www-data:www-data /var/www/geo  
chown -R www-data:www-data /var/www/admin  

Alternatively you can download the codes at github

Test if Geofency App is working with the DB

If you are already Geofency user, you are propably familiar with the Webhook. It should be staight forward.
* Open the App and click on any geofence location under settings * Add URL for Notify on Entry and URL Notify on Exit The URLs should be http or https://YOUR-DOMAIN.com/geo/index.php
* Click on test on Entry and test on Exit. If all good. Check the database and make sure there is data.

psql -h 127.0.0.1 -d geodb -U geoadmin -W  
SELECT * FROM users;  
...
SELECT * FROM groups;  
...
SELECT * FROM geodata;  
...

Please let me know if you have any suggestions to improve the article or the code.

Thank you for reading.

Hassan El-Masri

Network Consultant Engineer

comments powered by Disqus