LoRa Location Tracker Part 3: Server

Starting from where I stopped in Part 2, about the measurement on the RSSI value and getting the conversion on the RSSI to the distance. Now I can move on to the server part, as I hope to finally make it a commercial product, therefore a user-friendly interface is very important. In this part, I will share how I build the server and the user interface.

Build a server in Raspberry pi 4

First, install “Apache Web Server”.

sudo apt update

sudo apt install apache2 -y

sudo apt install php libapache2-mode-php -y

Then, install “phpMyAdmin”.

sudo apt install phpmyadmin

After installing “Apache Web Server” and “phpMyAdmin”, now I can start building my web interface and database.

Create database in phpMyAdmin

Let me introduce my database first. As my project is used in an elderly house, therefore in the database, I need to include patient information, patient’s contact person information, login user, and LoRa information.

Login user

CREATE TABLE users (

    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

    username VARCHAR(50) NOT NULL UNIQUE,

    password VARCHAR(255) NOT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP

);

Patient Information

CREATE TABLE Person (

    PatientID INT(8) NOT NULL PRIMARY KEY AUTO_INCREMENT,

    FirstName VARCHAR(45) NOT NULL,

    LastName VARCHAR(45) NOT NULL,

    PhoneNo int(8) NOT NULL,

    Gender VARCHAR(45) NOT NULL,

    HealthCondition VARCHAR(45) NOT NULL,

    RoomNo int(3) NOT NULL,

);

Patient’s Contact Person Information

CREATE TABLE ContactPerson (

    PatientID INT(8) NOT NULL PRIMARY KEY AUTO_INCREMENT,

    FirstName VARCHAR(45) NOT NULL,

    LastName VARCHAR(45) NOT NULL,

    Relationship VARCHAR(45) NOT NULL,

    PhoneNo int(8) NOT NULL,

    Gender VARCHAR(45) NOT NULL,

);

LoRa information

CREATE TABLE LoRa (

    PatientID INT(8) NOT NULL PRIMARY KEY AUTO_INCREMENT,

    LoRaNo VARCHAR(8) NOT NULL,

    State VARCHAR(45) NOT NULL,

    Position VARCHAR(45) NOT NULL,

);

RSSI values

CREATE TABLE RSSI (

    Time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    Number int(20) NOT NULL,

    RSSIValue int(10) NOT NULL,

);

After creating the database, we can move on to the webpage design.

For my web page, I use PHP to create. It simply displays patient information, such as their location, contact information, etc.

Login page

Home page

Patient List

LoRa List

In addition, to be more user-friendly, the webpage can be directly inserted into the patient information, without having to go to phpMyAdmin to set it up.

Insert Patient Information

Now I can continue with the main part of this project - how to send the RSSI value back to the server. As I mentioned before, I created an RSSI value database. I used Arduino to connect to the LoRa module as a transmitter. For the receiver part, I also used Arduino to connect to LoRa to receive the RSSI value and send it back to the Raspberry Pi 4, and store it inside the RSSI value database.

In the previous part, I shared how to convert RSSI value to distance, and in this stage, I can use it on my web page. For my database, it will keep collecting data every second because the RSSI value transmission is also every second. After the database collects data, my web page will retrieve data from the database and refresh it every second. In addition, I put the conversion part in PHP, so the web page can display the RSSI value and distance on the home page at the same time.

Finally, the main function of the device is to check the elderly in the elderly home.

In the above screenshot, I added a "Position" corner to the list for the staff to check if they are in the nursing home. Otherwise, they need to catch them back immediately.

Further Development:

Currently, I use a receiver and a transmitter to measure the distance between the elderly and the elderly home. LoRa seems to be a possible position tracker at this stage because of the high accuracy of distance tracking. In the future, I will use more receivers to obtain the actual XY coordinates of the target to monitor whether the elderly are in a nursing home.

In Parts 4 and 5, I will discuss the circuit design and the programming of the LoRa transmitter and LoRa Receiver. Also, in Part 6, it will be the final chapter of this series, which will talk about the calculation of the transmitter's position, and I will have a demonstration video of the whole project. Please enjoy!