Storing AIS-Catcher Data in PostgreSQL

[Parabellum123]

Hi everyone,

I'm currently working on a project where I need to capture AIS (Automatic Identification System) data using AIS-Catcher and store it in a PostgreSQL database. I've managed to get AIS-Catcher running, but I'm having trouble figuring out the best way to save the captured data directly into my PostgreSQL database.

Here’s a quick overview of my setup:

1. I'm using AIS-Catcher to capture AIS data.
2. I want to use Python to process this data and store it in PostgreSQL.
3. I’m able to run AIS-Catcher, but I'm encountering an error when trying to connect to the database and store the data.

Here’s a snippet of my Python code:

`import subprocess
import socket
import json
import psycopg2
from datetime import datetime
import os

def run_ais_catcher():
ais_catcher_dir = r"C:\Users\User\Downloads\AIS-catcher.x86 (edge win 32)"
ais_catcher_exe = "AIS-catcher.exe"
command = [
os.path.join(ais_catcher_dir, ais_catcher_exe),
"-N", "8100",
"-v", "10", "-X", "-u", "127.0.0.1", "10110", "-u", "127.0.0.1", "10111"
]
subprocess.Popen(command, cwd=ais_catcher_dir)

def connect_to_db():
conn = psycopg2.connect(
dbname="ship_ais",
user="andigit",
password="kmzway87aa",
host="localhost",
port="5432"
)
return conn, conn.cursor()

def receive_and_store_data():
TCP_IP = '127.0.0.1'
TCP_PORT = 8100
BUFFER_SIZE = 1024

s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.connect((TCP_IP, TCP_PORT))

conn, cursor = connect_to_db()

try:
    while True:
        data = s.recv(BUFFER_SIZE)
        if not data:
            break
        try:
            ais_data = json.loads(data.decode('utf-8'))
            mmsi = ais_data.get('mmsi')
            timestamp = datetime.fromtimestamp(ais_data.get('timestamp'))
            latitude = ais_data.get('latitude')
            longitude = ais_data.get('longitude')
            sog = ais_data.get('sog')
            cog = ais_data.get('cog')
            heading = ais_data.get('heading')
            vessel_name = ais_data.get('vessel_name')
            imo = ais_data.get('imo')
            call_sign = ais_data.get('call_sign')
            vessel_type = ais_data.get('vessel_type')

            cursor.execute(
                """
                INSERT INTO ais_data (mmsi, timestamp, latitude, longitude, sog, cog, heading, vessel_name, imo, call_sign, vessel_type)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """,
                (mmsi, timestamp, latitude, longitude, sog, cog, heading, vessel_name, imo, call_sign, vessel_type)
            )
            conn.commit()
        except Exception as e:
            print(f"Error: {e}")
finally:
    s.close()
    cursor.close()
    conn.close()

if name == "main":
run_ais_catcher()
receive_and_store_data()
`

The errors I'm encountering include:

ConnectionRefusedError: [WinError 10061] No connection could be made because the target machine actively refused it
usb_open error -3 Please fix the device permissions
Could anyone guide me on how to properly set up this pipeline to capture and store AIS data in PostgreSQL? Do I need to configure anything specific in my system settings or the code itself?

Any help or pointers would be greatly appreciated!

Thanks in advance!

[laleft]

Hi, @Parabellum123. AIS Catcher already has the option to store a lot of data in a PostgreSQL database. Take a look at the documentation: https://github.com/jvde-github/AIS-catcher?tab=readme-ov-file#writing-ais-messages-to-a-postgres-database

[Parabellum123]

Thank you @laleft for replying. I have followed the steps as mentioned in the link and obtained the SQL data from the DBMS folder here: https://github.com/jvde-github/AIS-catcher/tree/main/DBMS. Perhaps I made a mistake somewhere along the way. Could you assist me, or create a video tutorial so that I can correct my errors?

[Parabellum123]

I was able to successfully transfer data to my database with the help of someone who assisted me directly. The issue was with connecting to the PostgreSQL table, which I had overlooked. Thank you to those who responded to my comment