1-database-loading.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5005e571-09e1-4f62-9e74-72f796ded215",
   "metadata": {},
   "source": [
    "# Database Loading"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7889c6b8-0358-4515-8053-01eff25e6b29",
   "metadata": {},
   "source": [
    "## Import required packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5b777be0-058c-408f-9bb8-2462aa5cae37",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime, timedelta\n",
    "import os\n",
    "import aisdb\n",
    "import nest_asyncio\n",
    "nest_asyncio.apply()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1506c844-2014-4fdf-b57d-955d94df20f7",
   "metadata": {},
   "source": [
    "## SQLite database connection\n",
    "\n",
    "List the test data files included in the package:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "202d04ff-9ec5-499e-91a4-5a0d8edd7166",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['test_data_20210701.csv', 'test_data_20211101.nm4.zip', 'test_data_20211101.nm4', 'test_data_20211101.nm4.gz']\n"
     ]
    }
   ],
   "source": [
    "print(os.listdir(os.path.join(aisdb.sqlpath, '..', 'tests', 'testdata')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "32f2792c-e8bd-4b9d-9860-5b5f2032f47c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "generating file checksums...\n",
      "found matching checksum, skipping /home/ruixin/AISdb/aisdb/aisdb_sql/../tests/testdata/test_data_20210701.csv\n",
      "All files returned an existing checksum. Cleaning temporary data...\n"
     ]
    }
   ],
   "source": [
    "# Set the path for the SQLite database file to be used\n",
    "dbpath = './test_database.db'\n",
    "\n",
    "# Use test_data_20210701.csv as the test data\n",
    "filepaths = [os.path.join(aisdb.sqlpath, '..', 'tests', 'testdata', 'test_data_20210701.csv')]\n",
    "with aisdb.DBConn(dbpath = dbpath) as dbconn:\n",
    "    aisdb.decode_msgs(filepaths=filepaths, dbconn=dbconn, source='TESTING')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ffcbd0b-3a9d-40e5-8fdc-268e0b925722",
   "metadata": {},
   "source": [
    "To visualize the data we just loaded to path:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "843e0bbf-691c-4b48-a964-8f14f6a4adc5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Serving HTTP assets on localhost:3000\n",
      "Querying database...\tdone query\n",
      "Opening a new browser window to display track data. Press Ctrl-C to stop the server and close the webpage\n",
      "127.0.0.1:48916 - received: {'msgtype': 'meta'}\n",
      "127.0.0.1:48924 - received: {'msgtype': 'validrange'}\n",
      "127.0.0.1:48924 - received: {'msgtype': 'zones'}\n",
      "Received KeyboardInterrupt, stopping server...\n"
     ]
    }
   ],
   "source": [
    "start_time = datetime.strptime(\"2021-07-01 00:00:00\", '%Y-%m-%d %H:%M:%S')\n",
    "end_time = datetime.strptime(\"2021-07-02 00:00:00\", '%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "with aisdb.SQLiteDBConn(dbpath=dbpath) as dbconn:\n",
    "    qry = aisdb.DBQuery(\n",
    "        dbconn=dbconn,\n",
    "        dbpath='./AIS2.db',\n",
    "        callback=aisdb.database.sql_query_strings.in_timerange,\n",
    "        start=start_time,\n",
    "        end=end_time,\n",
    "    )\n",
    "    rowgen = qry.gen_qry()\n",
    "    tracks = aisdb.track_gen.TrackGen(rowgen, decimate=False)\n",
    "\n",
    "    if __name__ == '__main__':\n",
    "        aisdb.web_interface.visualize(\n",
    "            tracks,\n",
    "            visualearth=True,\n",
    "            open_browser=True,\n",
    "        )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db93599a-f6c1-4de1-b87c-79aec2b7edc4",
   "metadata": {},
   "source": [
    "## PostgreSQL database connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ba313d4-571a-4c2c-8687-191afa817afa",
   "metadata": {},
   "outputs": [],
   "source": [
    "from aisdb.database.dbconn import PostgresDBConn\n",
    "\n",
    "# Option 1: Using keyword arguments\n",
    "dbconn = PostgresDBConn(\n",
    "    hostaddr='127.0.0.1',      # Replace with the PostgreSQL address\n",
    "    port=5432,                 # Replace with the PostgreSQL running port\n",
    "    user='USERNAME',           # Replace with the PostgreSQL username\n",
    "    password='PASSWORD',  # Replace with your password\n",
    "    dbname='aisviz'          # Replace with your database name\n",
    ")\n",
    "\n",
    "# # Option 2: Using a connection string\n",
    "# dbconn = PostgresDBConn('postgresql://USERNAME:PASSWORD@HOST:PORT/DATABASE')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d3cef9aa-0896-4fa6-8092-92d69543cb67",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Serving HTTP assets on localhost:3000\n",
      "Querying database...\tdone query\n",
      "Opening a new browser window to display track data. Press Ctrl-C to stop the server and close the webpage\n",
      "127.0.0.1:41998 - received: {'msgtype': 'meta'}\n",
      "127.0.0.1:42012 - received: {'msgtype': 'validrange'}\n",
      "127.0.0.1:42012 - received: {'msgtype': 'zones'}\n",
      "Received KeyboardInterrupt, stopping server...\n"
     ]
    }
   ],
   "source": [
    "from aisdb.gis import DomainFromPoints\n",
    "from aisdb.database.dbqry import DBQuery\n",
    "from datetime import datetime\n",
    "\n",
    "# Define a spatial domain centered around the point (-63.6, 44.6) with a radial distance of 5000 meters.\n",
    "domain = DomainFromPoints(points=[(-63.6, 44.6)], radial_distances=[50000])\n",
    "\n",
    "# Create a query object to fetch AIS data within the specified time range and spatial domain.\n",
    "qry = DBQuery(\n",
    "    dbconn=dbconn,\n",
    "    start=datetime(2023, 1, 1), end=datetime(2023, 2, 1),\n",
    "    xmin=domain.boundary['xmin'], xmax=domain.boundary['xmax'],\n",
    "    ymin=domain.boundary['ymin'], ymax=domain.boundary['ymax'],\n",
    "    callback=aisdb.database.sqlfcn_callbacks.in_time_bbox_validmmsi\n",
    ")\n",
    "\n",
    "# Generate rows from the query\n",
    "rowgen = qry.gen_qry()\n",
    "\n",
    "# Convert the generated rows into tracks\n",
    "tracks = aisdb.track_gen.TrackGen(rowgen, decimate=False)\n",
    "\n",
    "# Visualize the tracks on a map\n",
    "aisdb.web_interface.visualize(\n",
    "    tracks,           # The tracks (trajectories) to visualize.\n",
    "    domain=domain,    # The spatial domain to use for the visualization.\n",
    "    visualearth=True, # If True, use Visual Earth for the map background.\n",
    "    open_browser=True # If True, automatically open the visualization in a web browser.\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d51bbe62-3698-4926-b6ef-14f59524f8d9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}