Database

We need a database for couple of things as part of our flow. Remember in the simulator diagram we persist validated journeys, then poll to start processing and simulate movement.

So the natural pick for me in this case is PostGIS, it sounds suitable seeing we are dealing with Geospatial. We will limit the functionalities we will use out of the box to keep the learning while making our life simple.

Database setup

We will seed our DB with from our geojson file to have the points available, I used a shell library ogr2ogr to convert my geojson to rows into my database.

Then I processed that table further into another table that only has one column of type geometry.

With this setup I can query very quickly the database to ask lets say for two random points that are distanced at least 1 KM apart using PostGIS built in functions. My Database table looks something like this

And then we will create journey table so once we validate these two points we persist a journey with these two points, start and end. It'll be fetched by another process and then we can start processing the journey and simulate.

Deploy

So how can we deploy easily and hassle free ?

My first thought is docker. Docker is an easy way to run an instance with seeded data in a repeatable and easy way. Meaning if for instance my server is deleted, all I need is my docker file and dump data and I can execute the docker and have a db instance running with required data.

So lets try and deploy :)

Docker

Lets first install docker on our server, lets ssh and get to our terminal

sudo apt update
sudo apt upgrade

Then let's head to the official document and install docker

https://docs.docker.com/engine/install/ubuntu/#installation-methods

sudo systemctl start docker
sudo systemctl enable docker

To test that our docker is working let's execute

sudo docker run hello-world

Should get a similar message, which would confirm our docker is running.

This message shows that your installation appears to be working correctly.

Docker file

Lets create our docker file to build our postgis image.

FROM postgis/postgis:15-3.3-alpine

# Optional: Copy initialization scripts
COPY ./init.sql /docker-entrypoint-initdb.d/
COPY ./road_map_points.sql /docker-entrypoint-initdb.d/

# Optional: Set environment variables
ENV POSTGRES_USER=trafficUser21
ENV POSTGRES_DB: traffic_db

Int our docker file not only we are pulling our postgis, but it also accommodates few other commands such as copying two initialization .sql file that would ensure the creation of the schema and few tables and also seed a table. In addition we have few env variables for the database to connect to.

Github Action (deployment)

name: Deploy Database

on:
  workflow_dispatch:

jobs:
  build-and-deploy:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v2

      - name: Build Docker image
        run: |
          cd database
          docker build -t doroha-postgis .
          docker save doroha-postgis > doroha-postgis.tar

      - name: Transfer Docker image to server
        run:  |
          # Write the SSH private key to a temporary file
          echo "${{ secrets.SSH_PRIVATE_KEY }}" > ssh_key
          chmod 600 ssh_key
          # Use the temporary file for SCP
          scp -o StrictHostKeyChecking=no -i ssh_key doroha-postgis.tar ${{ secrets.USERNAME }}@165.22.233.166:~/
          # Clean up the temporary key file
          rm ssh_key

      - name: Deploy to server
        uses: appleboy/ssh-action@master
        with:
          host: 165.22.233.166
          username: ${{ secrets.USERNAME }}
          key: ${{ secrets.SSH_PRIVATE_KEY }}
          script: |
            mkdir -p /var/www/databases
            mv ~/doroha-postgis.tar /var/www/databases
            docker load < /www/var/databases/doroha-postgis.tar
            docker stop doroha-postgis || true
            docker rm doroha-postgis || true
            docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=${{ secrets.POSTGRES_PASSWORD }} doroha-postgis

Now the above script would cause issues, in particular the Deploy to server part which uses docker command. This would cause permission problem so we need to do one more thing. We have to ssh to our server and add our user to docker group so we can perform docker commands without sudo. We can achieve this by executing on our terminal sudo usermod -a -G docker bohdan.