How to filter ship signals by IMO number and push updated data to MySQL?

[Nikseell]

Hi everyone,

I'm working on a project where I need to filter ship signals to isolate a specific ship using its IMO number. Additionally, I want to push this data to a MySQL database every time the ship's information gets updated in real-time. Can anyone guide me on the best approach or share any resources/tutorials that might help?

Thanks in advance!

[jvde-github]

Hi,

there are probably utilities to push NMEA into a mysql database on the web. I have not looked for it. I have added an option to filter on MMSI which allows you to filter on a ship with a particular IMO number:

AIS-catcher -o 5 FILTER on SELECT_MMSI xxxxx

This will produce JSON output that you can try to push into mysql via a Python or bash script? Any other thoughts.

[Parabellum123]

`import os
import time
import subprocess
import socket
import mysql.connector
from datetime import datetime

Fungsi untuk menjalankan AIS-Catcher

def run_ais_catcher():
ais_catcher_dir = r"C:\Users\User\Downloads\AIS-catcher.x86 (edge win 32)"
ais_catcher_exe = "AIS-catcher.exe"

# Path ke file AIS-Catcher
ais_catcher_path = os.path.join(ais_catcher_dir, ais_catcher_exe)

# Periksa apakah file AIS-Catcher ada
if not os.path.isfile(ais_catcher_path):
    raise FileNotFoundError(f"File {ais_catcher_path} tidak ditemukan.")

# Command untuk menjalankan AIS-Catcher
command = [ais_catcher_path, "-N", "8100"]

# Jalankan AIS-Catcher
try:
    subprocess.Popen(command, cwd=ais_catcher_dir)
    print("AIS-Catcher is running...")
    time.sleep(5)  # Tunggu 5 detik agar AIS-Catcher siap
except Exception as e:
    print(f"Error running AIS-Catcher: {e}")

Fungsi untuk koneksi ke database MySQL

def connect_to_db():
try:
conn = mysql.connector.connect(
host="localhost",
user="root",
password="andi123",
database="ship_ais"
)
print("Database connection successful.")
return conn
except mysql.connector.Error as err:
print(f"Database connection failed: {err}")
return None

Fungsi untuk parsing data NMEA menjadi JSON sederhana

def parse_nmea_to_json(nmea_message):
try:
# Debug: Cetak data asli NMEA
print(f"Raw NMEA Data: {nmea_message}")

    # Split pesan NMEA
    parts = nmea_message.split(',')

    # Validasi jumlah elemen dalam pesan NMEA
    if len(parts) < 6:
        print("Invalid NMEA message format, skipping...")
        return None

    # Ekstrak MMSI dari pesan (posisi ke-3 atau ke-4 dalam beberapa format)
    mmsi = parts[3] if len(parts) > 3 else None
    timestamp = datetime.utcnow()  # Gunakan waktu server
    latitude = None  # Dekode latitude jika diperlukan
    longitude = None  # Dekode longitude jika diperlukan

    parsed_data = {
        "mmsi": mmsi,
        "timestamp": timestamp,
        "latitude": latitude,
        "longitude": longitude,
        "sog": None,  # Speed over ground
        "cog": None,  # Course over ground
        "heading": None,
        "vessel_name": None,
        "imo": None,
        "call_sign": None,
        "vessel_type": None,
    }

    # Debug: Tampilkan hasil parsing
    print("Parsed JSON Data:", parsed_data)

    return parsed_data

except Exception as e:
    print(f"Error parsing NMEA: {e}")
    return None

Fungsi utama untuk menerima data dan menyimpan ke MySQL

def receive_and_store_data():
TCP_IP = '127.0.0.1' # Alamat lokal
TCP_PORT = 8100 # Port TCP dari AIS-Catcher
BUFFER_SIZE = 4096 # Ukuran buffer untuk membaca data

# Buka koneksi socket ke AIS-Catcher
try:
    s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    s.connect((TCP_IP, TCP_PORT))
    print(f"Connected to AIS-Catcher at {TCP_IP}:{TCP_PORT}")
except Exception as e:
    print(f"Error connecting to AIS-Catcher TCP server: {e}")
    return

# Koneksi ke database
conn = connect_to_db()
if conn is None:
    print("Stopping process due to database connection failure.")
    return

cursor = conn.cursor()

try:
    while True:
        data = s.recv(BUFFER_SIZE)
        if not data:
            break

        # Decode data NMEA
        nmea_message = data.decode('utf-8').strip()
        print(f"Received NMEA message: {nmea_message}")

        # Parse NMEA ke JSON
        if nmea_message.startswith('!AIVDM'):
            ais_data = parse_nmea_to_json(nmea_message)

            # Simpan ke database jika data valid
            if ais_data and ais_data.get('mmsi'):
                print("Parsed Data to be inserted:", ais_data)  # Debugging log

                try:
                    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)
                        """,
                        (
                            ais_data['mmsi'],
                            ais_data['timestamp'],
                            ais_data['latitude'],
                            ais_data['longitude'],
                            ais_data['sog'],
                            ais_data['cog'],
                            ais_data['heading'],
                            ais_data['vessel_name'],
                            ais_data['imo'],
                            ais_data['call_sign'],
                            ais_data['vessel_type'],
                        )
                    )
                    conn.commit()
                    print(f"Inserted AIS data for MMSI: {ais_data['mmsi']}")
                except mysql.connector.Error as err:
                    print(f"Database insert error: {err}")
except KeyboardInterrupt:
    print("Process stopped by user.")
finally:
    s.close()
    cursor.close()
    conn.close()

Main program

if name == "main":
run_ais_catcher() # Jalankan AIS-Catcher
receive_and_store_data() # Terima data dan simpan ke database
`

[Running] python -u "c:\TESIS SPARK DLL\Dashboard Monitoring Emisi Kapal\src\scripts\PERCOBAAN RTL-SDR MYSQL.py"
!AIVDM,1,1,,B,17ljFim0007a>rctPNCU4hGR0@N7,012 ( MSG: 1, REPEAT: 0, MMSI: 525113031, signalpower: -27.2283, ppm: -2.02546, timestamp: 20250205092250)
AIS-Catcher is running...
AIS-catcher (build Jul 4 2024) v0.59-172-g5d1b2556
(C) Copyright 2021-2023 jvde-github and other contributors
This is free software; see the source for copying conditions.There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
usb_open error -3
Please fix the device permissions, e.g. by installing the udev rules file rtl-sdr.rules
Error: RTLSDR: cannot open device.
!AIVDM,1,1,,B,17ljFim0007a>rctPNCU4hG`08PH,039 ( MSG: 1, REPEAT: 0, MMSI: 525113031, signalpower: -28.4308, ppm: -3.76157, timestamp: 20250205092253)
Connected to AIS-Catcher at 127.0.0.1:8100
Database connection successful.
!AIVDM,1,1,,A,17ljFim0007a>rctPNCU4hGn0@0c,007 ( MSG: 1, REPEAT: 0, MMSI: 525113031, signalpower: -28.3618, ppm: -0.578704, timestamp: 20250205092259)
!AIVDM,1,1,,B,17ljFim0007a>rctPNCU4hF00?j8,025 ( MSG: 1, REPEAT: 0, MMSI: 525113031, signalpower: -21.4483, ppm: -0.868056, timestamp: 20250205092302)

This works, but why can't it save to the MySQL database

[jvde-github]

I don't understand how you get the AIS-catcher output, you listen to port 8100 but that is where the webserver sits. I suggest you have a look at the documentation at the networking output modes: https://docs.aiscatcher.org/configuration/output/TCP-server/

Also, for most output modes you can define it in JSON format. This will make it easy to extract MMSI etc.