ETLSP Back-End System
Overview
The ETLSP Back-End System is designed to import, process, and visualise geospatial data through a combination of web technologies. It integrates a Laravel-based back-end for handling data import and API services, WordPress for the front-end visualisation including charts and maps, and Python scripts for generating dynamic maps. This system is tailored for handling geospatial data related to warehouse information and incorporates additional static geo-JSON files.
Components
-
WordPress
- Utilises ChartJS for data visualisation.
- Integrates maps for geospatial visualisation.
-
Laravel Back-End
- Provides an API for data access.
- Handles file uploads, specifically large Excel files for data import.
-
API Documentation Refer to the API documentation: https://stats.emalto.gov.et/docs/api/
-
Python
- Utilises Folium for creating interactive maps.
- Supports importing and processing large data files.
-
Alpine Calculator
- Accessible at Road Transport Cost Calculator.
The system's architecture ensures that the ChartJS graphs on the WordPress front-end dynamically reflect changes in the database. Both the front-end and back-end utilise separate databases. The Back-End tools share the same environment file.
Server Requirements
- Operating System: Linux (Rocky Linux / Ubuntu preferred)
- PHP: Version 8.2 (Currently running 8.1)
- MySQL: MySQL / MariaDB
- Web Server: Nginx / Apache
- Python: Version 3.8 or higher (Currently running 3.6)
Virtual hosts
- WordPress:
https://www.emalto.gov.et/ - Laravel:
https://stats.emalto.gov.et/
System Requirements
- Database Space: 32 GB
- Disk Space: 64 GB
- Memory: 8 GB
PHP Configuration (/etc/php.ini)
upload_max_filesize = 500M
post_max_size = 512M
memory_limit = 768M
max_execution_time = 600
max_input_vars = 3000
max_input_time = 1000Nginx Configuration (etlsp.conf)
server {
listen 80;
server_name etlsp.portal.africa
;
root "/var/live/etlsp";
allow all;
add_header X-Frame-Options "SAMEORIGIN";
add_header X-Content-Type-Options "nosniff";
index index.php index.html;
charset utf-8;
client_max_body_size 128M;
client_body_temp_path /tmp;
location / {
try_files $uri $uri/ /index.php?$query_string;
}
location ~ \.php$ {
include snippets/fastcgi-php.conf;
fastcgi_pass unix:/run/php/php8.2-fpm.sock;
}
location /stats {
alias /var/live/etlsp-stats/public;
try_files $uri $uri/ @rewsecond;
location ~ \.php$ {
include fastcgi_params;
fastcgi_param SCRIPT_FILENAME $request_filename;
fastcgi_pass unix:/run/php/php8.2-fpm.sock;
}
}
location ~* (.+)/(.+?)\.(xlsx|xls)$ {
add_header Content-Disposition "inline; filename=\"$2.$3\"";
}
location @rewsecond {
rewrite /stats/(.*)$ /stats/index.php?/$1 last;
}
location = /favicon.ico { access_log off; log_not_found off; }
location = /robots.txt { access_log off; log_not_found off; }
error_page 404 /index.php;
location ~ /\.ht {
deny all;
}
location ~ /\.(?!well-known).* {
deny all;
}
access_log /var/log/nginx/etlsp.portal.africa-access.log;
error_log /var/log/nginx/etlsp.portal.africa-error.log error;
}ChartJS example
The URL for the back-end API is https://stats.emalto.gov.et/docs/api/. Below is an example of HTML that can be added to WordPress in order to display graphs using data from the back-end:
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<div style="border: 2px solid white; margin: 10px; padding: 10px;">
<canvas id="dwellChart" width="400" height="200"></canvas>
</div>
<script>
// Assuming you have included Chart.js in your HTML
// Fetch the data from the URL
fetch('https://stats.emalto.gov.et/api/data/time/DDL02')
.then(response => response.json())
.then(data => {
const periods = data.data.period;
const transit = data.data.transit.map(Number);
const registration = data.data.registration.map(Number);
const clearance = data.data.clearance.map(Number);
const regression = data.data.regression.map(Number);
const volumes = data.data.n.map(Number);
// Calculate regression points if needed
// Create the chart
const ctx = document.getElementById('dwellChart').getContext('2d');
const myChart = new Chart(ctx, {
type: 'bar',
data: {
labels: periods,
datasets: [{
label: 'Transit',
data: transit,
borderColor: 'rgb(25, 250, 192)',
backgroundColor: 'rgba(247, 225, 15, 0.8)',
stack: 'Stack 0'
}, {
label: 'Registration',
data: registration,
borderColor: 'rgb(25, 250, 192)',
backgroundColor: 'rgba(175, 3, 3, 0.8)',
stack: 'Stack 0'
}, {
label: 'Clearance',
data: clearance,
borderColor: 'rgb(25, 250, 192)',
backgroundColor: 'rgba(37, 183, 194, 0.8)',
stack: 'Stack 0'
}, {
label: 'Regression',
type: 'line',
data: regression,
borderColor: 'rgba(22, 223, 49, 1)',
borderWidth: 6,
}, {
label: 'Volume (N)',
type: 'line',
data: volumes,
borderColor: 'rgba(1,111,205,1)',
borderWidth: 6,
yAxisID: 'y1'
}]
},
options: {
scales: {
y: {
beginAtZero: true,
stacked: true,
title: {
display: true,
text: 'Hours'
}
},
y1: {
type: 'linear',
display: true,
position: 'right',
title: {
display: true,
text: 'Volume'
},
}
}
}
});
})
.catch(error => console.error('Error fetching data:', error));
</script>Use a Map on Wordpress
<iframe src="https://stats.emalto.gov.et/maps/warehouses.html" width="100%" height="100vh"></iframe>
<script>
function resizeIframe() {
var iframe = document.querySelector('iframe');
iframe.style.height = (window.innerHeight-200) + 'px';
}
window.onresize = resizeIframe;
resizeIframe();
</script>Updating the data
When new data are sourced and updated, the system can be updated from Excel files. To update the database, you can log in to the Back-End and upload your Excel files in the File Upload area. If the system does not recognise your file, it could be that you have deviated from the original template. Please visit the Template section of the Back-End system to see the options of templates that can be used.