When running databases like PostgreSQL and MariaDB on a server, ensuring regular backups is crucial for protecting your data from unexpected events such as crashes, human error, or system failure. While there are several ways to create backups, scripting a backup solution gives you complete control and automation.

In this article, we'll show you how to create a simple yet effective backup script for PostgreSQL and MariaDB running in Docker containers on a server. We'll automate the process to ensure that your databases are regularly backed up without you needing to manually intervene.

## Why You Need Backups?

Before diving into the script, let’s take a moment to highlight why having regular backups is essential:

1. Protection Against Data Loss: Regular backups prevent loss of valuable data due to failures or accidents.
2. Disaster Recovery: Having up-to-date backups makes it easier to restore services quickly after a failure.
3. Compliance and Audits: Some industries require maintaining backup copies of data for regulatory compliance.
4. Peace of Mind: Knowing your data is safe provides confidence and good sleep.

## Prerequisites

Before creating the backup script, make sure you have:
- A VPS running a Linux-based operating system.
- Docker installed and running.
- PostgreSQL and MariaDB running inside Docker containers.
- Basic knowledge of using the terminal and running Docker commands.

For the purpose of this tutorial, let’s assume your PostgreSQL and MariaDB containers are named `postgres_container` and `mariadb_container`.

## Create a Backup Directory

Start by creating a directory on your server where the backups will be stored. This will help keep everything organized.

```bash
mkdir -p /home/youruser/backup
```
Replace `/home/youruser/backup` with the location where you'd like to store your backups.

## Create the Backup Script

Now let’s create a bash script that will run daily backups for both PostgreSQL and MariaDB databases. Open your favorite text editor and create a file named `backup_databases.sh`.
```bash
#!/bin/bash
set -euo pipefail

# Backup a PostgreSQL database into a daily file.

BACKUP_DIR="/data/backups"
LOG_FILE="/var/log/db_backup.log"
DAYS_TO_KEEP=30
POSTGRESDATABASES=("db1" "db2") # PostgreSQL DBs to backup
MARIADBDATABASES=("db1") # MariaDB DBs to backup
POSTGRESCONTAINER="postgres_container"
MARIADBCONTAINER="mariadb_container"

# Create necessary directories
mkdir -p "${BACKUP_DIR}"
mkdir -p "$(dirname "${LOG_FILE}")"

# Create backup directory if it doesn't exist
mkdir -p "${BACKUP_DIR}"

# Function to log messages
log() {
    local level="$1"
    local message="$2"
    local timestamp
    timestamp=$(date +"%Y-%m-%d %H:%M:%S")

    # Log to both stdout and a log file
    echo -e "${timestamp} [${level}] ${message}" | tee -a "${LOG_FILE}"
}

for DATABASE in "${POSTGRESDATABASES[@]}"; do
    TIMESTAMP=$(date +"%Y%m%d%H%M")
    FILE="${TIMESTAMP}_${DATABASE}.sql.gz"
    OUTPUT_FILE="${BACKUP_DIR}/${FILE}"

    log "INFO" "Starting backup for database: ${DATABASE}"

    # Perform the backup and compress the output
    if docker exec -i "${POSTGRESCONTAINER}" /usr/bin/pg_dump -U "${USER}" "${DATABASE}" | gzip -9 > "${OUTPUT_FILE}"; then
        log "SUCCESS" "Backup created: ${OUTPUT_FILE}"
        ls -l "${OUTPUT_FILE}" | tee -a "${LOG_FILE}"
    else
        log "ERROR" "Backup failed for database ${DATABASE}" >&2
        continue
    fi

    # Prune old backups
    find "${BACKUP_DIR}" -maxdepth 1 -mtime +"${DAYS_TO_KEEP}" -name "*${DATABASE}.sql.gz" -exec rm -f {} \; \
        && log "INFO" "Old backups deleted for database ${DATABASE}" \
        || log "ERROR" "Failed to delete old backups for ${DATABASE}" >&2
done

## MariaDB Backup
for DATABASE in ${MARIADBDATABASES[@]}; do
    TIMESTAMP=$(date +"%Y%m%d%H%M")
    FILE="${TIMESTAMP}_${DATABASE}.sql.gz"
    OUTPUT_FILE="${BACKUP_DIR}/${FILE}"

    # Perform the database backup (dump)
    if docker exec ${MARIADBCONTAINER} /usr/bin/mariadb-dump -u  root --password=yourpassword ${DATABASE} | gzip -9 >  ${OUTPUT_FILE}; then
        log "SUCCESS" "Backup created: ${OUTPUT_FILE}"
        ls -l "${OUTPUT_FILE}" | tee -a "${LOG_FILE}"
    else
        log "ERROR" "Backup failed for database ${DATABASE}" >&2
        continue
    fi

    # Prune old backups
    find "${BACKUP_DIR}" -maxdepth 1 -mtime +"${DAYS_TO_KEEP}" -name "*${DATABASE}.sql.gz" -exec rm -f {} \; \
        && log "INFO" "Old backups deleted for database ${DATABASE}" \
        || log "ERROR" "Failed to delete old backups for ${DATABASE}" >&2
done

log "INFO" "Finished database backups!"
```

**Explanation:**
- **Variables:** The script starts by defining a backup directory and more parameters like days to keep backups and individual databases to backup.
- **PostgreSQL Backup:** It uses `docker exec` to run the `pg_dump` command inside the PostgreSQL container to dump previously defined databases.
- **MariaDB Backup:** Similarly, it uses `docker exec` to run the `mysqldump` inside the MariaDB container and backup previously defined databases.
- **Compression:** All backups are compressed using `gzip` to save space.
- **Backup Cleanup:** Finally, the script deletes backups older than `DAYS_TO_KEEP` days to prevent disk space issues.

**Customizing the Script:**
- Change `BACKUP_DIR` to the directory where you want to store your backups.
- Replace `DAYS_TO_KEEP` with the days how long you want to keep backups.
- Replace `POSTGRESDATABASES` with the PostgreSQL databases to backup.
- Replace `MARIADBDATABASES` with the MariaDB databases to backup.
- Replace `POSTGRESCONTAINER` and  `MARIADBCONTAINER` with the name of your PostgreSQL and MariaDB containers.
- Replace `yourpassword` with the password for the root user in MariaDB..

## Make the Script Executable

After saving the script, make it executable:
```bash
chmod +x /path/to/backup_databases.sh
```

## Automate the Backup with Cron

To schedule automatic backups, set up a cron job.

- Open the crontab editor:
```bash
crontab -e
```
- Add the following line to run the backup script daily at 2:00 AM:
```bash
0 2 * * * /path/to/backup_databases.sh
```

This will execute the backup script every day at 2:00 AM.

Make sure to adjust the path ```/path/to/backup_databases.sh``` to the correct location of your script.

## Verify the Backup

It’s always a good idea to manually run the backup script once to ensure everything is working correctly.
```bash
/path/to/backup_databases.sh
```
Check the backup directory to ensure that the backup files have been created and compressed.

## Restore from Backup (Bonus)

In case you need to restore a backup, you can use the following commands to load the backups back into your PostgreSQL and MariaDB containers.

**PostgreSQL Restore:**
```bash
docker exec -i postgres_container psql -U postgres -d database < /home/youruser/backup/DATE_DATABASE.sql
```

**MariaDB Restore:**
```bash
docker exec -i mariadb_container mariadb u root --password=yourpassword database < /home/youruser/backup/DATE_DATABASE.sql
```

Replace `DATE` and `DATABASE` with the appropriate backup file’s date and database name

By following these steps, you've created a simple and automated backup solution for your PostgreSQL and MariaDB databases running inside Docker containers. Regular backups are essential for protecting your data, and this script ensures that your backups run smoothly without manual intervention. You can also use this script to backup databases e.g. in Unraid with the `User Script`plugin.

You can further enhance this backup strategy by sending notifications, backing up to remote storage (e.g., AWS S3 or Google Cloud), or setting up encryption for additional security.

With your databases securely backed up, you can rest easy knowing your data is safe and easily recoverable in case of an emergency.

Learn how to automate backups for PostgreSQL and MariaDB running in Docker containers on a server. Protect your data with this simple, customizable backup script, ensuring regular backups with minimal effort.

How to Create a Backup Script for PostgreSQL and MariaDB Containers on a Server

Webmentions are a powerful tool for adding decentralized social interactions, such as comments, likes, reposts, and replies, directly on your website. If you're building a dynamic site with Next.js, integrating Webmentions can help encourage cross-site conversations, boost SEO, and enhance user engagement. In this guide, I will show you how to implement Webmentions into your Next.js project with PostgreSQL for storing and displaying them.

## What Are Webmentions?

[Webmention](https://www.w3.org/TR/webmention/) is an open web standard (W3C Recommendation) that enables decentralized cross-site interactions.

In simpler terms, Webmentions allow users to interact with your content across the web by leaving [comments](https://indieweb.org/comment), [likes](https://indieweb.org/like), [reposts](https://indieweb.org/repost) and other [responses](https://indieweb.org/responses) on other sites. These interactions enrich your site’s user experience, and they help establish meaningful connections with others.

When you link to a webpage, you can send a Webmention notification. If the receiving site supports Webmentions, it may display your post as a comment, like, or response—enabling rich cross-site conversations.

**Why Should You Use Webmentions?**
- Encourage Engagement: Promote cross-site discussions and interactions.
- Boost Social Proof: Display interactions from well-known sources to establish credibility.
- Enhance SEO: Webmentions generate backlinks, improving visibility and search engine rankings.

Here’s an example of how Webmentions appear on my site:

![2024-02-23_18-23.png](https://cms.mxd.codes/uploads/2024_02_23_18_23_ad04b4ba37.png)

You can check a live version of this in the Webmentions section of this article: [/articles/fetching-and-storing-activities-from-garmin-connect-with-strapi-and-visualizing-them-with-next-js#replies].

A typical webmentions structure in JSON looks like this:

```json
{
  "type": "entry",
  "author": {
    "type": "card",
    "name": "Some Name",
    "photo": "URL to author image",
    "url": "URL to author profile"
  },
  "url": "Webmention URL",
  "wm-received": "Date of Webmention",
  "wm-id": 1876563,
  "wm-source": "Source URL",
  "wm-target": "Target URL",
  "wm-property": "Type of mention (e.g., like-of, repost-of)",
  "wm-private": false
}
```

To keep your Webmentions accessible even if an external service is discontinued, it’s a good idea to store them locally. In this tutorial, we’ll guide you through setting up a PostgreSQL database to store Webmentions and display them dynamically in your Next.js app.

## Setting Up PostgreSQL

Before we dive into Webmentions, ensure you have PostgreSQL installed on your server. If not, check on of these [guides](https://www.ecosia.org/search?method=index&q=setup+postgresql).

Once PostgreSQL is ready:

- **Create a New Database:**
```bash
# Create a new database for storing Webmentions
createdb personalwebsite
``` 
- **Create a Table to Store Webmentions:** Here's the SQL structure to store Webmentions in your database:
```sql
-- Define a table structure to store Webmentions

-- DROP TABLE public.webmentions;

CREATE TABLE public.webmentions (
	id serial4 NOT NULL,
	wm_id int8 NOT NULL,
	wm_source text NOT NULL,
	wm_target text NOT NULL,
	wm_property text NOT NULL,
	url text NULL,
	author_name text NULL,
	author_photo text NULL,
	author_url text NULL,
	content_html text NULL,
	content_text text NULL,
	published_at timestamp NULL,
	received_at timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT webmentions_pkey PRIMARY KEY (id),
	CONSTRAINT webmentions_wm_id_key UNIQUE (wm_id)
);
```

- **Create a Log Table for Fetching Webmentions:** This table logs each fetch, allowing you to track when Webmentions were last retrieved and avoid overloading the service:

```sql
-- public.webmention_fetch_log definition

-- Drop table

-- DROP TABLE public.webmention_fetch_log;

CREATE TABLE public.webmention_fetch_log (
	id serial4 NOT NULL,
	last_fetch timestamptz NOT NULL,
	CONSTRAINT webmention_fetch_log_pkey PRIMARY KEY (id)
);
```

## Using Webmention.io to Receive Webmentions

Before we store webmentions to display them we have to get them somewhere. If you dont want to implement your own Webmentions Receiver I recommend to use [Webmention.io](https://webmention.io/) which is a service to easily receive webmentions.

**Steps:**
1. **Set Up IndieAuth for Your Site:** Follow these[ IndieAuth setup instructions](https://indieauth.com/setup)
2. **Sign Up for Webmention.io:** Create an account at[ Webmention.io](https://webmention.io/)
3. **Add the Webmention Link to Your `_app.tsx`:**

```html
<Head>
  ...
  <link rel="webmention" href="https://webmention.io/username/webmention" />
  ...
  </Head>
```

From here on, Webmention.io will collect all the Webmentions for your site. Now, let’s create a script that fetches and stores them every ten minutes in the PostgreSQL database

## Fetching Webmentions from Webmention.io API

To keep Webmentions up-to-date, we'll fetch them periodically. Here’s the logic of the script.

![fetchWebmentions.jpg](https://cms.mxd.codes/uploads/fetch_Webmentions_396b7d7dd6.jpg)

Create a script `src/utils/fetch-webmentions.js` to fetch and store Webmentions:

```jsx
import fetch from "node-fetch"
import { Pool } from "pg"

const pool = new Pool({
  user: process.env.PGUSER,
  host: process.env.PGHOST,
  database: process.env.PGDATABASE,
  password: process.env.PGPASSWORD,
  port: process.env.PGPORT,
})

function isNotOlderThanTenMinutes(date: Date) {
  if (!(date instanceof Date) || isNaN(date.getTime())) return false
  return Date.now() - date.getTime() <= 10 * 60 * 1000
}

export async function fetchAndStoreWebmentions() {
  const client = await pool.connect() // Use a client for transaction safety
  try {
    console.log("🔄 Checking last webmention fetch...")

    // Get the latest fetch timestamp
    const { rows } = await client.query(
      `SELECT last_fetch FROM webmention_fetch_log ORDER BY last_fetch DESC LIMIT 1`
    )
    const lastFetchDate = rows[0]?.last_fetch

    if (isNotOlderThanTenMinutes(lastFetchDate)) {
      console.log("✅ Webmentions are already updated!")
      return
    }

    // Insert new fetch timestamp
    const now = new Date().toISOString()
    await client.query(`INSERT INTO webmention_fetch_log (last_fetch) VALUES ($1)`, [now])
    console.log("📌 Updated Webmentions fetch log")

    // Generate Webmention API URL
    const baseUrl = `https://webmention.io/api/mentions.jf2?domain=mxd.codes&per-page=1000&page=0&token=rxwJcoKxWCd9n3NA9SoxeQ`
    const webmentionsUrl =
      lastFetchDate instanceof Date && !isNaN(lastFetchDate.getTime())
        ? `${baseUrl}&since=${lastFetchDate.toISOString()}`
        : baseUrl

    // Fetch new Webmentions from Webmention.io
    console.log("🔄 Fetching webmentions from Webmention.io...")
    const response = await fetch(webmentionsUrl)
    const { children: webmentions } = await response.json()

    if (!Array.isArray(webmentions) || webmentions.length === 0) {
      console.log("⚠️ No new webmentions found.")
      return
    }

    console.log(`📥 Processing ${webmentions.length} webmentions...`)

    // Prepare batch insert query
    const insertQuery = `
      INSERT INTO webmentions (
        wm_id, wm_source, wm_target, wm_property, url,
        author_name, author_photo, author_url, content_html, content_text, published_at, received_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
      ON CONFLICT (wm_id) DO NOTHING;
    `

    for (const mention of webmentions) {
      const values = [
        mention["wm-id"],
        mention["wm-source"],
        mention["wm-target"],
        mention["wm-property"],
        mention["url"],
        mention.author?.name || null,
        mention.author?.photo || null,
        mention.author?.url || null,
        mention.content?.html || null,
        mention.content?.text || null,
        mention.published ? new Date(mention.published) : null,
        new Date(mention["wm-received"]),
      ]
      await pool.query(insertQuery, values)
    }

    console.log(`✅ Stored ${webmentions.length} webmentions successfully!`)
  } catch (error) {
    console.error("❌ Error fetching or storing webmentions:", error)
  } finally {
    client.release() // Ensure client is released back to the pool
  }
}

// Run the function
fetchAndStoreWebmentions()
```

This function can now be called everytime before webmentions are queried for a page from PostgreSQL database.
Ideally, you should abstract the logic for determining whether new webmentions need to be fetched into an API layer. This prevents unnecessary database queries with every request, but this is out of scope for this article.

## Retrieving Webmentions for a Page

To retrieve Webmentions dynamically for a page, we create an API route in `pages/api/get-webmentions.js`. This route allows us to fetch mentions for a specific target URL stored in our PostgreSQL database.

```jsx
import { Pool } from "pg"
import { fetchAndStoreWebmentions } from "@/src/utils/fetch-webmentions"

const pool = new Pool({
  user: process.env.PGUSER,
  host: process.env.PGHOST,
  database: process.env.PGDATABASE,
  password: process.env.PGPASSWORD,
  port: process.env.PGPORT,
})

export default async function handler(req, res) {
  if (req.method !== "GET")
    return res.status(405).json({ error: "Method not allowed" })

  const { target } = req.query
  if (!target) return res.status(400).json({ error: "Missing target URL" })

  // Updating Webmentions before selecting for page url
  await fetchAndStoreWebmentions()
  const query = `SELECT wm_id, wm_source, wm_target, wm_property, url, author_name, author_photo, author_url, content_text, published_at FROM webmentions WHERE wm_target LIKE '%${target}%' ORDER BY received_at DESC;`
  const result = await pool.query(query)

  res.json(result.rows)
}
```

Now you can call this API route and pass a query param `target` with the URL to get all Webmentions for a page.

## Displaying Webmentions in Next.js

To visually display Webmentions on our website, we create a dedicated React component `components/Webmentions.js`. This component fetches the Webmentions from our API and renders them.

```javascript
import { useEffect, useState } from "react";

// React component to display Webmentions for a given page
const Webmentions = ({ targetUrl }) => {
  const [mentions, setMentions] = useState([]);

  useEffect(() => {
  // Fetch Webmentions for the target URL from the API route
    fetch(`/api/get-webmentions?target=${encodeURIComponent(targetUrl)}`)
      .then((res) => res.json())
      .then((data) => setMentions(data));
  }, [targetUrl]);

  return (
    <div>
      <h3>Webmentions</h3>
      {mentions.length === 0 ? (
        <p>No webmentions yet.</p>
      ) : (
        mentions.map((mention) => (
          <div className="vcard h-card p-author" key={mention.wm_id} style={{ border: "1px solid #ddd", padding: "10px", marginBottom: "10px" }}>
            {mention.author_photo && (
               // Display author profile picture if available
              <img src={mention.author_photo} alt={mention.author_name} className="u-photo" style={{ width: "40px", height: "40px", borderRadius: "50%" }} />
            )}
            <p>
              <strong>{mention.author_name}</strong> {mention.wm_property.replace("-", " ")}
              {mention.wm_property === "like-of" && " ❤️"}
              {mention.wm_property === "repost-of" && " 🔁"}
              {mention.wm_property === "in-reply-to" && " 💬"}
            </p>
            <a className="u-url" href={mention.url || mention.wm_source} target="_blank" rel="noopener noreferrer">View Mention</a>
          </div>
        ))
      )}
    </div>
  );
};

export default Webmentions;
```

I highly recommend periodically verifying the authenticity of Webmention sources to prevent spam.

## Testing Webmentions

To ensure your Webmentions setup works correctly, use the following tools:

- [Webmention Rocks!](https://webmention.rocks/) – A tool for testing Webmention implementations.

By integrating Webmentions into your Next.js site, you can create an interactive and engaging web community. Whether you're running a blog, portfolio, or e-commerce site, Webmentions provide a powerful way to enhance content, boost SEO, and encourage meaningful connections.

If you have created a response to this post you can send me a webmention and it will appear below the post.

## More Links
- [IndieWeb Webmention](https://indieweb.org/Webmention)
- You can read more about Webmention and the IndieWeb on the [https://indieweb.org/](https://indieweb.org).

Learn how to integrate Webmentions into your Next.js site using PostgreSQL. Enhance engagement, foster social interactions, and build a dynamic web community with this step-by-step guide.

 Enhancing Social Interactions: Implementing Webmentions with Next.js and PostgreSQL

[MapProxy](https://mapproxy.org/ "MapProxy") is a powerful open-source proxy for geospatial data that allows for efficient caching and serving of map tiles. Combining MapProxy with Docker and Nginx can provide a scalable and easily manageable solution for serving cached map tiles. This guide will walk you through the process of setting up MapProxy using Docker and configuring Nginx to serve cached tiles.

**Prerequisites:**

1. Docker installed on your system
2. Basic understanding of Docker
3. Basic understanding of Nginx

If you don't meet this prerequisites yet I recommend to have a look at the following guides first:
- [Nginx Beginner’s Guide](https://nginx.org/en/docs/beginners_guide.html "Nginx Beginner’s Guide")
- [Docker overview](https://docs.docker.com/get-started/overview/ "Docker overview")
- [Install Docker Engine](https://docs.docker.com/engine/install/ "Install Docker Engine")

##  Set Up MapProxy with Docker

Start by creating a Network in Docker with:

```bash
sudo docker create network nginx
```

By adding the network nginx to the Nginx Container and the Mapproxy container the containers can communicate with each other without exposing ports on the server.

Create a Docker Compose file `docker-compose.yml` with the following content:

```docker
networks:
  default:
    external: true
    name: nginx
    
services:
  mapproxy:
    image: kartoza/mapproxy
    container_name: mapproxy
    restart: always
    environment:
      PRODUCTION: true
      PROCESSES: 4
      CHEAPER: 2
      THREADS: 8
      MULTI_MAPPROXY: true
      MULTI_MAPPROXY_DATA_DIR: /multi_mapproxy/configurations
      ALLOW_LISTING: true
    volumes:
      - /data/containers/mapproxy/data:/multi_mapproxy
```

Save the file and run:

```bash
docker-compose up -d
```

This will pull the MapProxy Docker image and start a container. MapProxy will be accessible on the container with `http://localhost:8080`.

## Set Up Nginx with Docker

Afterwards you need to create a Docker Compose file `docker-compose.yml` for Nginx. Here's a example:

```docker
version: '3'

networks:
  default:
    external: true
    name: nginx
    
services:
  nginx:
    image: nginx:latest
    container_name: nginx
    restart: always
    ports:
      - "80:80"
      - "443:443"
    volumes:
      ## Config
      - /data/containers/nginx/config/:/etc/nginx/
      ## SSL
      - /etc/letsencrypt/:/etc/letsencrypt/
      - /etc/ssl/:/etc/ssl/
      ## Logs
      - /data/containers/nginx/logs/:/var/log/nginx
      ## Cache
      - /data/containers/nginx/cache:/var/cache/nginx
```

Then you can create a virtual host for mapproxy under `/data/containers/nginx/config/sites-available/mapproxy` with:

```bash
sudo nano /data/containers/nginx/config/sites-available/mapproxy
```

Copy and paste the following virtual host configuration for your Mapproxy Container :

```nginx
upstream mapproxy_upstream {
  server mapproxy:8080;
}

server {

  server_name mapproxy.domain.com;

  listen 80;

  ## Mapproxy default
  location / {
    proxy_pass http://mapproxy_upstream/;
    proxy_set_header Host $http_host;
  }
}
```

After you created your configuration you need to create a symlink to `/data/containers/nginx/config/sites-enabled/` with:

```bash
sudo ln -S /data/containers/nginx/config/sites-available/mapproxy /data/containers/nginx/config/sites-enabled/
```

In order that your domain gets resolved you have to create a A-record for your Domain under which you want to publish MapProxy which points to your server ip.
Then you need to restart your Nginx Container and now should be able to access http://mapproxy.domain.com where you will be greeted by your MapProxy Instance.

![Screenshot_2024_01_25_154515_63e86fb113.png](https://cms.mxd.codes/uploads/Screenshot_2024_01_25_154515_63e86fb113_aed92b8f04.png)

## Step 3: Caching the Tiles with MapProxy

So far you have set up Nginx and MapProxy with Docker and MapProxy will cache by default all served tiles with the default configuration.
However there are some limitations to the MapProxy caching process because each time a tile gets requested from MapProxy, MapProxy will save the tile to it's cache. But it won't check by default if the tile is old and a newer tile could be served.

E.g. I am serving tiles from MapProxy which visualize my locations where I have ever been to. So I don't want to cache the tiles infinite because they change probably every day

But you can force MapProxy to refresh tiles from the source while serving if they are found to be expired.
The validity conditions are the same as for seeding:

```yaml
#Explanation
  # absolute as ISO time
  refresh_before:
    time: 2010-10-21T12:35:00
  # relative from the time of the tile request
  refresh_before:
    weeks: 1
    days: 7
    hours: 4
    minutes: 15
  # modification time of a given file
  refresh_before:
    mtime: path/to/file
```

So to stay at my example i added:

```yaml
refresh_before:
      days 1
```

This way MapProxy will refresh tiles everyday. This of course only makes sense for data where you want always latest data

So edit your `mapproxy.yaml` like this:

```yaml
services:
  demo:
  tms:
    use_grid_names: true
    # origin for /tiles service
    origin: 'nw'
  kml:
      use_grid_names: true
  wmts:
  wms:
    md:
      title: MapProxy WMS Proxy
      abstract: This is a minimal MapProxy example.

layers:
  - name: osm
    title: Omniscale OSM WMS - osm.omniscale.net
    sources: [osm_cache]

caches:
  osm_cache
    grids: [webmercator]
    sources: [osm_wms]
    refresh_before:
      days: 1

sources:
  osm_wms:
    type: wms
    req:
      url: https://maps.omniscale.net/v2/demo/style.default/service?
      layers: osm

grids:
    webmercator:
        base: GLOBAL_WEBMERCATOR

globals:
```

Save the file and restart your MapProxy Container.

Now MapProxy will always refresh tiles everyday.

## Step 4: Access the MapProxy Tiles

Visit `http://mapproxy.domain.com` in your browser, and you should see MapProxy serving tiles through Nginx. 

By following these steps, you've successfully set up MapProxy with Docker and configured Nginx to serve cached map tiles. This scalable solution allows for efficient geospatial data delivery with the added benefit of easy container management. Adjust the configurations based on your specific requirements and integrate this setup into your mapping projects.

If you decide to to take use of [MultiMapProxy](https://mapproxy.org/docs/nightly/deployment.html "MultiMapProxy")(scroll down to MultiMapProxy) you can just create more configuration files for MapProxy in `/data/containers/mapproxy/data/configurations` and  append the cache path and location blocks like in your existing Nginx configuration for MapProxy.

MapProxy is a powerful open-source proxy for geospatial data that allows for efficient caching and serving of map tiles. Combining MapProxy with Docker and Nginx can provide a scalable and easily manageable solution for serving cached map tiles.

 Setting Up MapProxy with Docker and Serving Cached Tiles via Nginx

2021-03-02 i started tracking my current location with [OwnTracks](https://owntracks.org/ "OwnTracks") and [Strapi](https://strapi.io/ "Strapi") ([How i constantly track my location and display a web-map with all the locations](/articles/constant-location-tracking-with-owntracks-strapi-and-visualizing-data-with-deckgl "How i constantly track my location and display a web-map with all the locations")) and created a [/map](/map "map with locations") which shows all the locations i have ever been to. 

Recently i reached one million datasets and unfortunately that means every user had to download about 50MB of location data before the map could be rendered. 
So i definitely needed a much faster solution and decided to render and serve server side some tiles which will be used to display the locations. 
In the end i built a tile-server following [Manually building a tile server (20.04 LTS)](https://switch2osm.org/serving-tiles/manually-building-a-tile-server-20-04-lts/ "Manually building a tile server (20.04 LTS)") with a custom Mapnik stylesheet.

In this article i want to show you how you can build your own Mapnik stylesheet for displaying any data from PostgreSQL/PostGIS.

The Mapnik stylesheet XML is not very handy but fortunately there are some tools which will help you to create one. After some research i decided to go with [TileMill](https://tilemill-project.github.io/tilemill/ "TileMill") which is an open source map design studio to design maps. It offers a simple UI and (more important) the possibility to export the created map-style as Mapnik stylesheet.

Actually i couldn't get the latest version running so i decided to go with TileMill v0.10.1 which offers anything you will need to create a stylesheet. At [https://tilemill-project.github.io/tilemill/docs/win-install/](https://tilemill-project.github.io/tilemill/docs/win-install/) you can download and install TileMill.

After you have installed TileMill you can create a new project and uncheck 'default data'. Otherwise TileMill will create some kind of basemap with a default style.

![content_tilemill_create_project_028d3d068a.png](https://cms.mxd.codes/uploads/content_tilemill_create_project_028d3d068a_f5f46bcd61.png)

Now you will have to add some data to the project. Therefore you click on the layer button and add a new layer.

![cover_tilemill_add_layer_9bfeb33806.png](https://cms.mxd.codes/uploads/cover_tilemill_add_layer_9bfeb33806_fcce2db562.png)

Switch to the **PostGIS** tab and fill out ID, Connection, Unique key field, Geometry field and SRS. It's actually pretty straight forward. 
Keep in mind that TileMill doesn't like large datasets so i would recommend you to set some extent.

In SRS you have to specify the PROJ.4 projection string. If you don't it know a look at https://epsg.io/[epsg-code] where you will replace [epsg-code] with the epsg code of your coordinate system and scroll down to PROJ.4 where you can copy the projection string. 

![proj4_2c3dab1b78.png](https://cms.mxd.codes/uploads/proj4_2c3dab1b78_f6547d0070.png)

Afterwards save the layer and the project. You won't see your data/features yet because you need to define some style for it. You can style the map with CartoCSS which is very similiar to CSS. For example i specified my layer id as locations and i am styling points so the CartoCSS properties could look like the following.

```config
#locations {
   [vel >= 0] { marker-width:6; marker-fill: #f45; marker-line-color: #813; marker-allow-overlap: true; }
   [vel >= 50] { marker-width:6; marker-fill: #f45; marker-line-color: #813; marker-allow-overlap: true; }
   [vel >= 100] { marker-width:6; marker-fill: #f45; marker-line-color: #813; marker-allow-overlap: true; }
}
```


With CartoCSS you can also dynamically style your features depending on some attribute values. For more information about CartoCSS have a look at [Styling data from TileMill](https://tilemill-project.github.io/tilemill/docs/crashcourse/styling/ "Styling data").

When you are happy with your style you can export if as Mapnik-XML and use if for example for your tile-server.

![tilemill_export_mapnik_xml_2d50702c3b.png](https://cms.mxd.codes/uploads/tilemill_export_mapnik_xml_2d50702c3b_8820136598.png)
 
At [/map](/map) you can see my current stylesheet in 'action'.
 

In this article i want to show you how you can build your own Mapnik stylesheet for displaying any data from PostgreSQL/PostGIS. The Mapnik Stylesheet XML can be used for a tile-server with your custom style.

How to create a Mapnik stylesheet for displaying any data from PostgreSQL/PostGIS

Inspired by Aaron Parecki who has been [tracking his location since 2008](https://aaronparecki.com/gps/ "location tracking") with an iPhone app ([Overland](https://overland.p3k.app/ "Overland")) and a server side tracking API ([Compass](https://github.com/aaronpk/Compass "Compass")), I decided to go for a similar approach.

I wanted to track my position constantly with my Android smartphone, save the data to a Postgres database and use the data to display an map with all locations i have ever been to.

To be able to track my position i needed an stable android app and after some tinkering around with different tracking apps I found [OwnTracks](https://owntracks.org/ "OwnTracks")  which basically has all i need (and a good [documentation](https://owntracks.org/booklet/ "OwnTracks Documentation") of it).

In this article, we will explore a step-by-step guide on how to set up a location tracking system using OwnTracks for mobile devices, Node.js for a webhook server, PostgreSQL for data storage, and GeoServer, MapProxy, and Nginx for visualizing the location data.

But first of all we need a database to be able to actually save the data.

## Set up PostgreSQL 

To install [PostgrSQL 16](https://www.postgresql.org/about/news/postgresql-16-released-2715/ "PostgreSQL 16") you will need to the repository that provides the packages.
First, update the package index and install required packages:

```bash
sudo apt update
sudo apt install gnupg2 wget vim
```

The PostgreSQL repository can then be added using the command:

```bash
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
```

Import the repository signing key:
```bash
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
```
And update your package list:

```bash
sudo apt update
```

Afterwards you can install the latest PostgreSQL (16) version with:
```bash
sudo apt-get -y install postgresql postgresql-contrib
```

Once installed, start and enable the service:
```bash
sudo systemctl start postgresql
sudo systemctl enable postgresql
```

Now you will need a user and database for your location data.

Connect to the PostgreSQL database with:
```bash
sudo su postgres
psql
```

and create a database with:
```sql
CREATE DATABASE locations;
```

aswell a user with:
```sql
CREATE USER USER <username> with encrypted password '<password>';
```

Then you can grant privileges for your user to the database:
```bash
GRANT ALL PRIVILEGES ON DATABASE locations TO <username>;
```

After you set up your database and user you also will need a table to store the data.
The table where I save my locations can be recreated with: 

```sql
CREATE TABLE public.locations (
	id bigserial NOT NULL,
	created_at timestamptz NULL DEFAULT CURRENT_TIMESTAMP,
	lat float8 NULL,
	lon float8 NULL,
	acc int4 NULL,
	alt int4 NULL,
	batt int4 NULL,
	bs int4 NULL,
	cog numeric(10, 2) NULL,
	rad int4 NULL,
	t varchar(255) NULL,
	tid varchar(255) NULL,
	tst int4 NULL,
	vac int4 NULL,
	vel int4 NULL,
	p numeric(10, 2) NULL,
	conn varchar(255) NULL,
	topic varchar(255) NULL,
	inregions jsonb NULL,
	ssid varchar(255) NULL,
	bssid varchar(255) NULL,
);
```
The most important attributes are **lat** (latitude), **lon** (longitude) and **alt** (altitude) which are used to render points later on.

Also I am saving the velocity, battery level of the smartphone and some more data which will be displayed at my [/now](/now "/now") page. The velocity and altitude are quite inaccurate though which probably also depends on the smartphone.

## Create a Node.js Webhook server

You can create a node.js server to act as a webhook to receive location data from OwnTracks. With the [pg](https://www.npmjs.com/package/pg "pg") npm package you can interact with the PostgreSQL database.

```javascript
const http = require("http");
const axios = require('axios');
const { Pool} = require('pg')

const pool = new Pool({
  user: 'username',
  database: 'database',
  password: 'pw!',
  port: port,
  host: 'localhost',
})

async function insertData(body) {
  try {
    const res = await pool.query(
      "INSERT INTO locations (lat, lon, acc, alt, batt, bs, tst, vac, vel, conn, topic, inregions, ssid, bssid) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)",
      [body.lat, body.lon, body.acc, body.alt, body.batt, body.bs, body.tst, body.vac, body.vel, body.conn, body.topic, body.inregions, body.ssid, body.bssid]
    );
    console.log(`Added location ${body.lat}, ${body.lon}`);
  } catch (error) {
    console.error(error)
  }
}

const server = http.createServer((request, response) => {
  let body = [];
  console.log(request.method)
  if (request.method === "POST") {
     request.on('data', (chunk) => {
      body.push(chunk);
      }).on('end', () => {
        body = JSON.parse(Buffer.concat(body).toString());
        insertData(body)
      })
  }

  response.end();
});

server.listen(9001);
```

So basically the webhook parses the request body and inserts the data via an insert statement into the postgres table. Also the server is listening on port 9001 so be sure you opened that port on your sever. The Url for Owntracks now could be **http://yourserverip:9001**.

## Setting up OwnTracks

The OwnTracks app runs in the background on your Android or iOS device and waits for the smart phone to tell it that the device has moved, whereupon OwnTracks sends out a message with its current coordinates (and a few other things we'll discuss in a moment)

With different settings for the app I could tailor the app exactly to my needs because I wanted the app to check my position every second and when i have moved more than two meters from my last known position it should send a POST-Request to my Strapi instance which will store the location data in a Postgres database.

In the following screenshot you can see the settings I am using for OwnTracks.

![content_Screenshot_Own_Tracks_settings_c7d95a33be.jpg](https://cms.mxd.codes/uploads/content_Screenshot_Own_Tracks_settings_c7d95a33be_4b3fcffdf5.jpg)

The most important seetings for me are the mode 
- **Significant location change mode**,
- **locatorDisplacement**,
- **locatorInterval**,
- **ignoreInaccurateLocations** and
- **url** for the POST-request.

The **significant location change mode** is a mode which will let you decide in which interval you want to track your position. I am using the **locationInterval** '1' which will track my position every second with an **LocatorDisplacement** of 1 which will check if i moved away a meter away from my last known position. If i did it will send an POST-request with the location data to the given url if the position data isn't more inaccurate than 10 meters.

Because the app has to run always in the background you probably have to switch off energy saving modes for your smartphone which might disable the background functionality. 

Be warned: You probably will have to charge your smartphone more often. Also sometimes the app has some tracking error if you are using parallel some other tracking app for example for activities like strava.

The url **http://yourserverip:9001** for the POST-request points to the nodejs server webhook which will run a insert command for the postgres database. 

Now the data will be stored in the postgres database but it still needs to be visualized somehow and therefore it needs a geometry attribute. 

## Creating a PostGIS extension

To be able to create a geometry you will need the [PostGIS](https://postgis.net/ "PostGIS") extension. You can extend your PostgreSQL database with 

```bash
sudo apt-get install postgis
sudo su postgres
psql
\c locations
CREATE EXTENSION postgis;
\q
```

Then create a view which creates a geometry based on the lat and lon attribute from the existing table. 

```sql
CREATE OR REPLACE VIEW public.locations_geom
AS SELECT id,
    lat,
    lon,
    alt,
    vel,
    st_setsrid(st_makepoint(lon, lat, alt::double precision), 4326) AS geom
   FROM locations;
```

At the moment I wrote/edited this post I have about two million datasets in the table, so fetching and rendering the data client-side would not be the best idea. This is where Geoserver takes over.

## Install and configure Geoserver

[GeoServer](https://geoserver.org/ "GeoServer") is an open source server for sharing geospatial data.

To setup GeoServer I created a stack in portainer:

```docker
version: '3'

networks:
  default:
    external: true
    name: nginx
    
services:
  geoserver:
    image: kartoza/geoserver:latest
    container_name: geoserver
    restart: always
    volumes:
      - /data/containers/geoserver:/opt/geoserver/data_dir
    ports:
      - "8080:8080"
    environment:
      GEOSERVER_DATA_DIR: ${GEOSERVER_DATA_DIR}
      GEOSERVER_ADMIN_PASSWORD: ${GEOSERVER_ADMIN_PASSWORD}
      GEOSERVER_ADMIN_USER: ${GEOSERVER_ADMIN_USER}
      INITIAL_MEMORY: ${INITIAL_MEMORY}
      MAXIMUM_MEMORY: ${MAXIMUM_MEMORY}
      DB_BACKEND: POSTGRES               
      HOST: ${POSTGRES_HOST}                         
      POSTGRES_PORT: 5432                
      POSTGRES_DB: gwc                
      POSTGRES_USER: ${POSTGRES_USER}    
      POSTGRES_PASS: ${POSTGRES_PASS}               
      POSTGRES_SCHEMA: public           
      DISK_QUOTA_SIZE: 20        
```

After deploying GeoServer you can login with the default user "admin" and password "geoserver" or the credentials you assigned with environment variables.

To add a new datasource you have to click on **Stores** and **Add new store**. Then you can select PostGIS (or whatever datasource you want to use).

![Screenshot_2024_01_31_162720_b6e7885b51.png](https://cms.mxd.codes/uploads/Screenshot_2024_01_31_162720_b6e7885b51_737a7d28df.png)

Fill out host, port, database, schema, user and passwd and save your connection.
Afterwards you can select layers which you want to publish.

![Screenshot_2024_01_31_162805_b0ba171177.png](https://cms.mxd.codes/uploads/Screenshot_2024_01_31_162805_b0ba171177_75e18fdc67.png)

After you published your layer you can preview the layer at **Layer Preview**.

Now I am not using Geoserver as endpoint to serve the tiles because I have a Mapproxy instance running which will take care of transforming tiles from internal or external ressources.

## MapProxy

Setting up MapProxy is optional but in this post I will use the endpoints from MapProxy to render a web map. So if you decied to skip MapProxy you will have to adjust the wms URL's to your endpoint from GeoServer.

[MapProxy](https://mapproxy.org/ "MapProxy") is an open source proxy for geospatial data. It caches, accelerates and transforms data from existing map services and serves any desktop or web GIS client.
I have decided create a own docker image [mapproxy-docker](https://github.com/dietrichmax/mapproxy-docker " dietrichmax/mapproxy-docker") which you can setup use in a portainer stack with:

```yaml
version: '3.9'

networks:
  default:
    external: true
    name: nginx
    
services:
  mapproxy:
    image: mxdcodes/mapproxy-docker:latest
    container_name: mapproxy
    restart: always
    ports:
      - 3050:80
    volumes:
      - /mapproxy/config:/persistentConfigFolder
      - /mapproxy/cache_data:/persistentCacheFolder
```

The only thing MapProxy needs one or multiple configuration files in yaml format for each service. So set up a locations.yaml under ```/persistentConfigFolder/locations.yaml```:

```yaml
services:
  demo:
  tms:
    use_grid_names: true
    # origin for /tiles service
    origin: 'nw'
  kml:
      use_grid_names: true
  wmts:
  wms:
    md:
      title: Location Data
      abstract: For mxd.codes.

layers:
  - name: locations
    title: Locations - mxd.codes
    sources: [locations_cache]

caches:
  locations_cache:
    grids: [webmercator]
    sources: [locations_wms]

sources:
  locations_wms:
    type: wms
    req:
      url: http://geoserver:8080/geoserver/mxdcodes/wms?
      layers: locations
      transparent: true

grids:
    webmercator:
        base: GLOBAL_WEBMERCATOR

globals:
  # cache options
  cache:
    # where to store the cached images
    base_dir: '/cache_data'
    # where to store lockfiles for concurrent_requests
    lock_dir: '/cache_data/locks'
    # where to store lockfiles for tile creation
    tile_lock_dir: '/cache_data/tile_locks'
    # request x*y tiles in one step
    meta_size: [4, 4]
  # image/transformation options
  image:
    # use best resampling for vector data
    resampling_method: bilinear # nearest/bicubic/bilinear
    formats:
      image/jpeg:
        encoding_options:
          # jpeg quality [0-100]
          jpeg_quality: 80
```

Be sure to replace the sources url with the url from your GeoServer instance.

Now you should have a endpoint for your wms tiles under **http://serverurl:3050/locations/service?** which you can use to create web map on your website.

I also created each service under a seperate subdomain and setup https for it with Cloudflare but I won't go into detail here because you will find hundreds of tutorials online.

## Creating a Web Map with Leaflet and React

The end product of all this should be a simple map on my [personal website](https://mxd.codes "mxd.codes") under [/map](/map "map"). The map component looks like the following:

```javascript
import React, { useState, useRef, useEffect } from "react"
import "ol/ol.css"
import Map from "ol/Map"
import TileLayer from "ol/layer/Tile"
import View from "ol/View"
import XYZ from "ol/source/XYZ"
import { transform, get as getProjection } from "ol/proj"
import WMTSTileGrid from "ol/tilegrid/WMTS.js"
import WMTS from "ol/source/WMTS.js"
import TileWMS from "ol/source/TileWMS.js"
import OSM from "ol/source/OSM.js"
import { getTopLeft, getWidth } from "ol/extent.js"

function LiveMap({data}) {
  const [map, setMap] = useState()
  const mapElement = useRef()
  const mapRef = useRef()
  mapRef.current = map

  const projection = getProjection("EPSG:3857")
  const projectionExtent = projection.getExtent()
  const size = getWidth(projectionExtent) / 256
  const resolutions = new Array(19)
  const matrixIds = new Array(19)
  for (let z = 0; z < 19; ++z) {
    // generate resolutions and matrixIds arrays for this WMTS
    resolutions[z] = size / Math.pow(2, z)
    matrixIds[z] = z
  }

  const locData = new TileLayer({
    opacity: 0.9,
    preload: Infinity,
    source: new WMTS({
      attributions: '&copy; <a href="https://mxd.codes/">Max Dietrich</a>',
      url: "tilelayerurl",
      layer: "locations",
      matrixSet: "webmercator",
      format: "image/png",
      projection: projection,
      tileGrid: new WMTSTileGrid({
        origin: getTopLeft(projectionExtent),
        resolutions: resolutions,
        matrixIds: matrixIds,
      }),
      style: "default",
      wrapX: true,
    }),
  })

  const aerial = new TileLayer({
    opacity: 0.9,
    preload: Infinity,
    source: new WMTS({
      attributions:
        "Tiles &copy; Esri &mdash; Source: Esri, i-cubed, USDA, USGS, AEX, GeoEye, Getmapping, Aerogrid, IGN, IGP, UPR-EGP, and the GIS User Community",
      url: "tilelayerurl",
      layer: "arcgisaerial",
      matrixSet: "webmercator",
      format: "image/png",
      projection: projection,
      tileGrid: new WMTSTileGrid({
        origin: getTopLeft(projectionExtent),
        resolutions: resolutions,
        matrixIds: matrixIds,
      }),
      style: "default",
      wrapX: true,
    }),
  })

  const center = transform([data.lon, data.lat], "EPSG:4326", "EPSG:3857")

  useEffect(() => {
    const initialMap = new Map({
      target: mapElement.current,
      layers: [aerial, locData],
      view: new View({
        center: center,
        zoom: 16,
        maxZoom: 16,
      }),
    })
    setMap(initialMap)
  }, [])

  return (
    <div
      style={{ height: "100%", width: "100%" }}
      ref={mapElement}
      className="map-container"
    />
  )
}

export default LiveMap
```


Inspired by Aaron Parecki and who he has been tracking his location since 2008 with an iPhone app and a server side tracking API i decided to go for a similar approach.

I wanted to track my position constantly with my Android smartphone and use the data to display a map with all locations i have ever been to.

A Guide to Location Tracking and Visualization with OwnTracks, Node.js, PostgreSQL, GeoServer, MapProxy, Nginx and OpenLayers

With getting into the [IndieWeb](https://indieweb.org/ "IndieWeb") i started to reflect about myself and how i can actually own my data instead of giving them to so called [silos](https://indieweb.org/silo "silos").

Due to the fact I am a passionate (mountain) bike rider i was thinking about how i could use tracking/activity apps in a way to get my data back, because obviously when i am going for a ride with my bike and i am tracking the route with strava and/or komoot the data is then saved by them.
Considering that every route is tracked on a Garmin device anyway and then synchronised to the apps i decided to have a look at the Garmin Connect/Activity API.

Unfortunately the [official Garmin Activity API](https://developer.garmin.com/gc-developer-program/activity-api/ "Garmin Activity API") is only available for approved business developer.

But after some searching i found the npm-package [garmin-connect](https://github.com/Pythe1337N/garmin-connect "garmin-connect") which allows you to connect to Garmin Connect for sending and receiving activity data.

## Fetching and storing activities from Garmin Connect with Strapi

You can install the package with
```bash
npm install garmin-connect
```
or

```bash
yarn add garmin-connect
```
and use it like
```javascript
const { GarminConnect } = require('garmin-connect');
// Create a new Garmin Connect Client
const GCClient = new GarminConnect();
// Uses credentials from garmin.config.json or uses supplied params
await GCClient.login('my.email@example.com', 'MySecretPassword');
const userInfo = await GCClient.getActivities());
```
I stored the email and the password for the login in environment variables and used them with 
```javascript
const { GarminConnect } = require('garmin-connect');
const GCClient = new GarminConnect();
await GCClient.login(process.env.GARMIN_EMAIL ,process.env.GARMIN_PWD);
const userInfo = await GCClient.getActivities());
```

Afterwards experimented a bit with the Garmin Connect and found out there are some very low limits. 
Approximately after ~50 requests in one minute couldn't get anymore any data and had to wait for some (maybe one?; i am not sure) hours until the request was successful again.

In general you can probably do way more with Garmin Connect as you will need, like for example:
- Get user info
- Get social user info
- Get heart rate
- Set body weight
- Get list of workouts
- Add new workouts
- Add workouts to you calendar
- Remove previously added workouts
- Get list of activities
- Get details about one specific activity
- Get the step count

I used only `GCClient.getActivities();` to get all activities and and  `GCClient.getActivity({ activityId: id });` to get the details of the activity (like spatial data representing the route, start-point and end-point).

To be able to store the data in Strapi i created a new content type collection  **activities** with the following fields/attributes:

![content_type_activity_3b64fb0368.png](https://cms.mxd.codes/uploads/content_type_activity_3b64fb0368_65ef0f7674.png)

Afterwards new entrys for activities can be created.

> Strapi has a documention which explains how to fetch external data and create entries with it: [Fetching external data](https://strapi.io/documentation/developer-docs/latest/guides/external-data.html#call-the-function "Fetching external data").

To get the data from Garmin Connect into Strapi i created a function `getGarminConnectActivities.js`  for Strapi ([https://gist.github.com/dietrichmax/306b36abd5a9d1ac0c938adcd15f2f69](https://gist.github.com/dietrichmax/306b36abd5a9d1ac0c938adcd15f2f69 "getGarminConnectActivities.js"))

The function will take care of:
- getting existing activities in Strapi,
- get recent activites from Garmin Connect,
- checking if an activity already exists in Strapi,
- creating the activity in Strapi if it doesn't exist yet and also get the details for it.

and basically looks like this:
```javascript
module.exports = async () => {
  await GCClient.login(process.env.GARMIN_USERNAME, process.env.GARMIN_PWD)
  const activities = await GCClient.getActivities()
  const exisitingActivities = await getExistingActivities()
  activities ? activities.map((activity) => {
    const isExisting = exisitingActivities.includes(activity.activityId)
    isExisting ? console.log(activity.activityId + " already exists") : createEntry(activity)
  })
  : console.log("no activities found")
}
```

After all activities from Garmin are fetched, i am mapping through them to

- check if they already exist in my cms
- and create them if needed

The exisiting activities in my CMS  are fetched with
```javascript
const getExistingActivities = async () => {
  const existingActivityIds = []
  const activities = await axios.get(`https://strapi.url/activities`)
  
  activities.data.map((activity) => {
    existingActivityIds.push(activity.activityID)
  })
  return existingActivityIds
}
```
and the activityIds (originally from Garmin Connect) are returned to be able to check if an entry already exists.
If the entry doesn't exist, details for the missing activity are fetched and a new entry is created with:

```javascript
const createEntry = async (activity) => {
  const details = await GCClient.getActivity({ activityId: activity.activityId });
  await strapi.query('activity').create({
    activityID: activity.activityId,
    activityName: activity.activityName,
    beginTimestamp: activity.beginTimestamp,
    activityType: activity.activityType,
    distance: activity.distance,
    duration: activity.duration,
    elapsedDuration:  activity.elapsedDuration,
    movingDuration: activity.movingDuration,
    elevationGain: activity.elevationGain,
    elevationLoss: activity.elevationLoss,
    minElevation: activity.minElevation,
    maxElevation: activity.minElevation,
    sportTypeId: activity.sportTypeId,
    averageSpeed: activity.averageSpeed * 3.6 //(m/s -> km/h),
    maxSpeed: activity.maxSpeed * 3.6 //(m/s -> km/h),,
    startLatitude: activity.startLatitude,
    startLongitude: activity.startLongitude,
    endLatitude: activity.endLatitude,
    endLongitude: activity.endLongitude,
    details: details
  })
}
```

You can save way more but i tried to cut it down to the ones i really need or eventually will need.

Only thing missing is some automatic triggering. For this you can use cron jobs in Strapi ([/config/functions/cron.js](https://strapi.io/documentation/developer-docs/latest/guides/external-data.html#call-the-function "cron.js")).

```javascript
module.exports = {
// Add your own logic here (e.g. send a queue of email, create a database backup, etc.).

    '0 0 18 * * *': () => {
      strapi.config.functions.getGarminConnectActivities();
    },
};
```

I decied to trigger the function everyday at 6 pm so i can have a look at my activites in the evening. 😎

Thats the fun from the 'backend part'.

Next step is to visualize the data in NextJS.

## Visualizing the data with NextJS

I am really liking the embeddable tours of [komoot](https://www.komoot.de/b2b/embed "komoot embed") optic-wise, so i decided to create a similiar looking option for the preview of my activities in the [posts-feed](/ "all posts") and [activites-feed](/activities).

So the preview should consist of
- an title of the activitie (+datetime),
- an symbol displaying the activity type (cycling, running, etc.)
- some basic metrics like distance, duration and average speed,
- a map displaying the line of the route with osm-data and and aerial-view,
- and optionally a graph displaying the elevation along the track (still in work).

The component looks like this at the moment:

![activity_post_preview_eeca5a51b1.png](https://cms.mxd.codes/uploads/activity_post_preview_eeca5a51b1_05959d7192.png)

### Showing some Metrics

In the **activityType** object you can find ```typeId``` which correlates to the type of the activity, e.g. cycling, running etc.
I created a small function which will return a icon from [react-icons](https://github.com/react-icons/react-icons "react-icons") visualizing the activity type.

``` javascript
import { FaRunning, FaBiking } from 'react-icons/fa';

const getTypeIcon = activity => {
    if (activity.activityType.typeId == 5) {
        return <FaBiking/>
    } else if (activity.activityType.typeId == 15) {
      return <FaRunning/>
 }

getTypeIcon(activity)
```

Due to the fact the duration is given in seconds and i wanted it to display like 1h 10m 12s there is also a need for a workaround which looks like the following:
```javascript
const secondsToHms = (s) => {
    const hours = (((s - s % 3600) / 3600) % 60)
    const minutes = (((s - s % 60) / 60) % 60)  
    const seconds = (s % 60)  
    return (`${hours}h ${minutes}min ${seconds}s`)
 }
secondsToHms(activity.duration)
```

### Displaying  a Map with "react-leaflet"

Then i created a small map with [react-leaflet](https://react-leaflet.js.org/ "react-leaflet") displaying 
- the track as polyline,
- the start point and
- the end point.

Therefore i created a new map-component:
```javascript
import React, { useEffect, useState } from "react"
import { Marker, MapContainer, TileLayer, LayersControl, Polyline } from "react-leaflet";

const Map = (data) => {
  const geo = data.data
  const style= { 
    color: '#11a9ed',
    weight: "5"
  }

  const bounds = [[geo.maxLat, geo.maxLon], [geo.minLat, geo.minLon]]
  return (
    <MapContainer
      style={{ height: "500px", width: "100%" }}
      bounds={bounds}       
      scrollWheelZoom={false}
    >
    <LayersControl position="topright">
      <LayersControl.BaseLayer checked name="OpenStreetMap.Mapnik">
        <TileLayer 
          url='https://{s}.basemaps.cartocdn.com/rastertiles/voyager/{z}/{x}/{y}{r}.png'
          attribution ='&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors &copy; <a href="https://carto.com/attributions">CARTO</a>'

        />
        </LayersControl.BaseLayer>
        <LayersControl.BaseLayer name="Esri World Imagery">
          <TileLayer
            attribution='Tiles &copy; Esri &mdash; Source: Esri, i-cubed, USDA, USGS, AEX, GeoEye, Getmapping, Aerogrid, IGN, IGP, UPR-EGP'
            url="https://server.arcgisonline.com/ArcGIS/rest/services/World_Imagery/MapServer/tile/{z}/{y}/{x}"
          />
        </LayersControl.BaseLayer>

        <Marker id="start" position={geo.startPoint}/>
        <Polyline pathOptions={style} positions={geo.polyline} />
        <Marker id="end" position={geo.endPoint}/>

      </LayersControl>
    </MapContainer>
  );
};

export default Map;
``` 
Luckily the data from the Garmin Connect has already exactly the structure we need to create the map, which are the coordinates for the polyline and the two points.

The coordinates can be found in **geoPolylineDTO** in the activitydetails 

![activity_details_a6d325373e.png](https://cms.mxd.codes/uploads/activity_details_a6d325373e_6bb242aae0.png)

With maxLat, maxLon, minLat and minLon i created the bounds which will set the default view for the map when passed to the MapContainer.
```javascript
const bounds = [[geo.maxLat, geo.maxLon], [geo.minLat, geo.minLon]]

<MapContainer
      style={{ height: "200px", width: "100%" }}
      bounds={bounds}       
      scrollWheelZoom={false}
 >
.
.

```
Then i added the LayerControl to be able to toggle between the two Tilelayers
- OSM (Carto) and
- Aerial Image (Esri)

After that i just created two markers and a polyline with the exisiting objects `startPoint`, `endPoint` and `polyline`.

```javascript
.
.
        <Marker id="start" position={geo.startPoint}/>
        <Polyline pathOptions={style} positions={geo.polyline} />
        <Marker id="end" position={geo.endPoint}/>
.
.
```

You can find several other tilelayers in [leaflet-providers-preview](/leaflet-extras-github/leaflet-providers/preview/ "leaflet-providers-preview").

For an example of the preview head over to [/activities](/activities  "activitites").
You can find the code for the [activity-preview](https://github.com/DaTurboD/mxd-codes-frontend/blob/v2/components/activity/activity-preview/activity-preview.js "activity-preview") in my github-repositiory.

For the actual post of the activity i made the map a bit larger and added some more metrics for now.

(osm)
![activity_post_bd91b407f4.png](https://cms.mxd.codes/uploads/activity_post_bd91b407f4_a951c65969.png)

(Aerial view)
![activity_post_aerial_11e09d89bf.png](https://cms.mxd.codes/uploads/activity_post_aerial_11e09d89bf_4424be4cd9.png)



Step-by-step guide explaining how to fetch data from Garmin Connect, store it in Strapi and visualize it with NextJS and React-Leaflet.

Fetching and storing activities from Garmin Connect with Strapi and visualizing them with NextJS

As the volume of data continues to surge, effective management becomes paramount. [Geographic Information System (GIS)](/articles/what-is-gis-geographic-information-system "geographic information system") databases emerge as powerful solutions, facilitating the storage, management, and querying of [geodata](/articles/what-are-geodata "geodata"). Here, we delve into both free and open-source as well as proprietary GIS databases to aid in your data management endeavors.

## Free and Open-Source GIS Databases

**ArangoDB Community Edition**
- A user-friendly, potent open-source NoSQL database with a unique feature set.
- Website: [https://www.arangodb.com](https://www.arangodb.com)

**PostGIS / PostgreSQL**
- PostGIS provides spatial objects for the PostgreSQL database, enabling storage and retrieval of location information.
- Website: [https://postgis.net](https://postgis.net) / [https://www.postgresql.org](https://www.postgresql.org)

**MariaDB**
- A leading database server developed by the original creators of MySQL.
- Website: [https://mariadb.org/](https://mariadb.org/)

**MySQL**
- Widely used relational database management system, available as open-source or commercial enterprise versions.
- Website: [https://www.mysql.com/](https://www.mysql.com/)

**OrientDB**
- An open-source NoSQL database written in Java, combining features of document-oriented and graph databases.
- Website: [https://www.orientdb.com](https://www.orientdb.com)

 **SQLite / SpatialLite**
- SpatialLite is an open-source library extending SQLite with comprehensive spatial SQL functions.
- Website: [https://www.sqlite.org](/sqlite/index.html) / [https://www.gaia-gis.it/fossil/libspatialite](https://www.gaia-gis.it/fossil/libspatialite/index)

## Proprietary GIS Databases

**Oracle Spatial**
- Oracle Spatial and Graph, a separately licensed component of the Oracle database, is utilized for storing and managing geographic information.
- Website: [https://www.oracle.com](/oracle/technetwork/database/options/spatialandgraph/downloads/index.html)

Whether you opt for the flexibility of open-source GIS databases or the robust features of proprietary solutions like Oracle Spatial, the choice depends on your project's specific needs.

With the ever increasing amount of data, the question arises at some point how it can be managed effectively. GIS databases are used here.