data_extraction.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## iNatAtor Data Extractor\n",
    "\n",
    "(also provided as .py)\n",
    "\n",
    "This notebook will walk you through how to gather data for fine tuning. Start the database instance if you are running it locally, if you are connecting to azure isntance of the database, you will specify connection parameters in .env, explained below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "from dotenv import load_dotenv\n",
    "import os\n",
    "import h3\n",
    "import shapely\n",
    "import pyproj\n",
    "import numpy as np\n",
    "import json\n",
    "\n",
    "import datetime\n",
    "\n",
    "load_dotenv()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parameters\n",
    "\n",
    "You need to supply a .env file that contains necessary db secrets, or hardcode them here.\n",
    "`max_amount` determines how many points you will sample from a hexagon.\n",
    "You can think of `sampling_amount = max(max_amount - hexagon_size, 0) + 1`, therefore the amount of points you sample is inversely correlated to the hexagon's resolution.\n",
    "\n",
    "`sampling_mode` refers to sampling strategy. `polygon` randomly checks points in a square that lies inside the hexagon. `circle` calculates a radius from the center of the hexagon to its corner and samples a point inside the circle, this elminates repeadetly checking if a point lies in a polygon. `circle` is slightly faster than `polygon` but samples with slight errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'db': 'inat',\n",
       " 'user': 'postgres',\n",
       " 'password': 'inat4cg',\n",
       " 'url': 'postgresql+psycopg2://postgres:inat4cg@localhost:5433/inat',\n",
       " 'sampling_mode': 'circle',\n",
       " 'max_amount': 7}"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "params = {\n",
    "    'db': os.getenv('POSTGRES_DB'),\n",
    "    'user': os.getenv('POSTGRES_USER'),\n",
    "    'password': os.getenv('POSTGRES_PASSWORD'),\n",
    "    'url': os.getenv('DATABASE_URL'),\n",
    "    'sampling_mode': 'circle', # polygon | circle\n",
    "    'max_amount': 7\n",
    "}\n",
    "\n",
    "params"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can try any query you want, however, to use with the fine-tuner you need `taxa_id`, `hex_id`, `type` from the query result. These three fields are what the model uses to fine tune.\n",
    "\n",
    "Some example queries are:\n",
    "\n",
    "`SELECT an.\"taxa_id\", ah.\"hex_id\", ah.\"hex_type\" FROM \"annotation\" AS an INNER JOIN \"annotation_hexagon\" AS ah ON an.\"annotation_id\"=ah.\"annotation_id\" WHERE an.\"taxa_id\" = 12345`\n",
    "- This query gets annotations for only one specific taxa."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "QUERY = 'SELECT an.\"taxa_id\", ah.\"hex_index\", ah.\"hex_type\" FROM \"annotation\" AS an INNER JOIN \"annotation_hexagon\" AS ah ON an.\"annotation_id\"=ah.\"annotation_id\"'"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This block makes communication with the database and reads by executing the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>taxa_id</th>\n",
       "      <th>hex_index</th>\n",
       "      <th>hex_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5165</td>\n",
       "      <td>84962e7ffffffff</td>\n",
       "      <td>presence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5165</td>\n",
       "      <td>84975d7ffffffff</td>\n",
       "      <td>presence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5165</td>\n",
       "      <td>8497517ffffffff</td>\n",
       "      <td>presence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5165</td>\n",
       "      <td>84962cbffffffff</td>\n",
       "      <td>presence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5165</td>\n",
       "      <td>8497537ffffffff</td>\n",
       "      <td>presence</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   taxa_id        hex_index  hex_type\n",
       "0     5165  84962e7ffffffff  presence\n",
       "1     5165  84975d7ffffffff  presence\n",
       "2     5165  8497517ffffffff  presence\n",
       "3     5165  84962cbffffffff  presence\n",
       "4     5165  8497537ffffffff  presence"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine = sqlalchemy.engine.create_engine(url=params['url'])\n",
    "df = pd.read_sql(QUERY, engine)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2404"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This block provides functions to sample points from a hexagon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_geo_distance(loc1, loc2):\n",
    "    lat1, lng1 = loc1\n",
    "    lat2, lng2 = loc2\n",
    "\n",
    "    geod = pyproj.Geod(ellps=\"WGS84\")\n",
    "    _, _, distance = geod.inv(lons1=lng1, lats1=lat1, lons2=lng2, lats2=lat2)\n",
    "    return distance\n",
    "\n",
    "\n",
    "def generate_random_points_in_polygon(boundary, N):\n",
    "    polygon = shapely.Polygon(boundary)\n",
    "    min_alt, min_lng, max_alt, max_lng = polygon.bounds\n",
    "\n",
    "    random_points = []\n",
    "    while len(random_points) < N:\n",
    "        alt = np.random.uniform(min_alt, max_alt)\n",
    "        lng = np.random.uniform(min_lng, max_lng)\n",
    "\n",
    "        point = shapely.Point(alt, lng)\n",
    "        if polygon.contains(point):\n",
    "            random_points.append((alt, lng))\n",
    "\n",
    "    return random_points\n",
    "\n",
    "def generate_random_points_in_circle(lat, lng, R, N):\n",
    "    random_points = []\n",
    "    while len(random_points) < N:\n",
    "        r = R * np.sqrt(np.random.uniform(0, 1)) # random distance from center\n",
    "        theta = np.random.uniform(0, 2 * np.pi) # random degree\n",
    "        \n",
    "        # 111320m distance between longitudes and latitudes at the equator\n",
    "        x = lng + r * np.cos(theta) / (111320 * np.cos(lat * np.pi / 180)) # r * np.cos(theta) / / (111320 * np.cos(lat * np.pi / 180)) finds the random point in x axis, division adjusts for length in the poles\n",
    "        y = lat + r * np.sin(theta) / 111320\n",
    "        \n",
    "        random_points.append((y, x))\n",
    "    \n",
    "    return random_points"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "hex_resolution = [h3.h3_get_resolution(hex_id) for hex_id in df['hex_index']]\n",
    "df['hex_resolution'] = hex_resolution\n",
    "\n",
    "hex_boundary = [h3.h3_to_geo_boundary(hex_id, geo_json=False) for hex_id in df['hex_index']]\n",
    "df['hex_boundary'] = hex_boundary\n",
    "\n",
    "if params['sampling_mode'] == 'polygon':\n",
    "    pass\n",
    "else:\n",
    "    center_point = [h3.h3_to_geo(hex_id) for hex_id in df['hex_index']]\n",
    "    df['center_point'] = center_point\n",
    "\n",
    "    radius = [min([calculate_geo_distance(r['center_point'], loc) for loc in r['hex_boundary']]) for _, r in df.iterrows()]\n",
    "    df['R'] = radius"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>taxa_id</th>\n",
       "      <th>hex_index</th>\n",
       "      <th>hex_type</th>\n",
       "      <th>hex_resolution</th>\n",
       "      <th>hex_boundary</th>\n",
       "      <th>center_point</th>\n",
       "      <th>R</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5165</td>\n",
       "      <td>84962e7ffffffff</td>\n",
       "      <td>presence</td>\n",
       "      <td>4</td>\n",
       "      <td>((-14.973196837524473, 27.789938965859054), (-...</td>\n",
       "      <td>(-14.769088700222758, 27.62719511680403)</td>\n",
       "      <td>28519.239849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5165</td>\n",
       "      <td>84975d7ffffffff</td>\n",
       "      <td>presence</td>\n",
       "      <td>4</td>\n",
       "      <td>((-17.736216364299104, 23.30326368482732), (-1...</td>\n",
       "      <td>(-17.5302380946822, 23.143965361646774)</td>\n",
       "      <td>28170.591584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5165</td>\n",
       "      <td>8497517ffffffff</td>\n",
       "      <td>presence</td>\n",
       "      <td>4</td>\n",
       "      <td>((-17.913493465296703, 25.757271715344977), (-...</td>\n",
       "      <td>(-17.708921620453513, 25.594704115579415)</td>\n",
       "      <td>28293.899780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5165</td>\n",
       "      <td>84962cbffffffff</td>\n",
       "      <td>presence</td>\n",
       "      <td>4</td>\n",
       "      <td>((-16.669469802640553, 26.809174339152214), (-...</td>\n",
       "      <td>(-16.46511955136319, 26.646279688575284)</td>\n",
       "      <td>28420.970601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5165</td>\n",
       "      <td>8497537ffffffff</td>\n",
       "      <td>presence</td>\n",
       "      <td>4</td>\n",
       "      <td>((-16.938454389750355, 26.43976487571977), (-1...</td>\n",
       "      <td>(-16.73395359665564, 26.277087683934333)</td>\n",
       "      <td>28392.018654</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   taxa_id        hex_index  hex_type  hex_resolution  \\\n",
       "0     5165  84962e7ffffffff  presence               4   \n",
       "1     5165  84975d7ffffffff  presence               4   \n",
       "2     5165  8497517ffffffff  presence               4   \n",
       "3     5165  84962cbffffffff  presence               4   \n",
       "4     5165  8497537ffffffff  presence               4   \n",
       "\n",
       "                                        hex_boundary  \\\n",
       "0  ((-14.973196837524473, 27.789938965859054), (-...   \n",
       "1  ((-17.736216364299104, 23.30326368482732), (-1...   \n",
       "2  ((-17.913493465296703, 25.757271715344977), (-...   \n",
       "3  ((-16.669469802640553, 26.809174339152214), (-...   \n",
       "4  ((-16.938454389750355, 26.43976487571977), (-1...   \n",
       "\n",
       "                                center_point             R  \n",
       "0   (-14.769088700222758, 27.62719511680403)  28519.239849  \n",
       "1    (-17.5302380946822, 23.143965361646774)  28170.591584  \n",
       "2  (-17.708921620453513, 25.594704115579415)  28293.899780  \n",
       "3   (-16.46511955136319, 26.646279688575284)  28420.970601  \n",
       "4   (-16.73395359665564, 26.277087683934333)  28392.018654  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>taxa_id</th>\n",
       "      <th>hex_index</th>\n",
       "      <th>hex_type</th>\n",
       "      <th>hex_resolution</th>\n",
       "      <th>hex_boundary</th>\n",
       "      <th>center_point</th>\n",
       "      <th>R</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2399</th>\n",
       "      <td>5174</td>\n",
       "      <td>84bc447ffffffff</td>\n",
       "      <td>absence</td>\n",
       "      <td>4</td>\n",
       "      <td>((-34.18267871344204, 23.829406513185848), (-3...</td>\n",
       "      <td>(-33.99821041999803, 23.656778590222167)</td>\n",
       "      <td>24856.871403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2400</th>\n",
       "      <td>5174</td>\n",
       "      <td>84bc445ffffffff</td>\n",
       "      <td>absence</td>\n",
       "      <td>4</td>\n",
       "      <td>((-34.34708962764286, 24.289870021150534), (-3...</td>\n",
       "      <td>(-34.16352079371115, 24.116443398782966)</td>\n",
       "      <td>24847.296287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2401</th>\n",
       "      <td>5174</td>\n",
       "      <td>84bc407ffffffff</td>\n",
       "      <td>absence</td>\n",
       "      <td>4</td>\n",
       "      <td>((-33.23663321785284, 23.255461102402393), (-3...</td>\n",
       "      <td>(-33.04949250162404, 23.084506231542456)</td>\n",
       "      <td>25093.853527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2402</th>\n",
       "      <td>5174</td>\n",
       "      <td>84bc401ffffffff</td>\n",
       "      <td>absence</td>\n",
       "      <td>4</td>\n",
       "      <td>((-33.6274772906967, 23.31276536178162), (-33....</td>\n",
       "      <td>(-33.44122544070739, 23.141383249306227)</td>\n",
       "      <td>24980.174438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2403</th>\n",
       "      <td>5174</td>\n",
       "      <td>84bc409ffffffff</td>\n",
       "      <td>absence</td>\n",
       "      <td>4</td>\n",
       "      <td>((-34.0163963486983, 23.37030977336294), (-33....</td>\n",
       "      <td>(-33.831046627187575, 23.198498678885336)</td>\n",
       "      <td>24865.336236</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      taxa_id        hex_index hex_type  hex_resolution  \\\n",
       "2399     5174  84bc447ffffffff  absence               4   \n",
       "2400     5174  84bc445ffffffff  absence               4   \n",
       "2401     5174  84bc407ffffffff  absence               4   \n",
       "2402     5174  84bc401ffffffff  absence               4   \n",
       "2403     5174  84bc409ffffffff  absence               4   \n",
       "\n",
       "                                           hex_boundary  \\\n",
       "2399  ((-34.18267871344204, 23.829406513185848), (-3...   \n",
       "2400  ((-34.34708962764286, 24.289870021150534), (-3...   \n",
       "2401  ((-33.23663321785284, 23.255461102402393), (-3...   \n",
       "2402  ((-33.6274772906967, 23.31276536178162), (-33....   \n",
       "2403  ((-34.0163963486983, 23.37030977336294), (-33....   \n",
       "\n",
       "                                   center_point             R  \n",
       "2399   (-33.99821041999803, 23.656778590222167)  24856.871403  \n",
       "2400   (-34.16352079371115, 24.116443398782966)  24847.296287  \n",
       "2401   (-33.04949250162404, 23.084506231542456)  25093.853527  \n",
       "2402   (-33.44122544070739, 23.141383249306227)  24980.174438  \n",
       "2403  (-33.831046627187575, 23.198498678885336)  24865.336236  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Executed in:  0:00:00.472690\n"
     ]
    }
   ],
   "source": [
    "start_time = datetime.datetime.now()\n",
    "\n",
    "psuedo_points = []\n",
    "for i, r in df.iterrows():\n",
    "    random_n_points = None\n",
    "    N = max(params['max_amount'] - r['hex_resolution'], 0) + 1\n",
    "    if params['sampling_mode'] == 'polygon':\n",
    "       random_n_points = generate_random_points_in_polygon(r['hex_boundary'], N)\n",
    "    else:\n",
    "        lat, lng = r['center_point']\n",
    "        random_n_points = generate_random_points_in_circle(lat, lng, r['R'], N)\n",
    "\n",
    "    for random_lat, random_lng in random_n_points:\n",
    "        psuedo_point = {\n",
    "            'taxon_id': r['taxa_id'],\n",
    "            'hex_type': 1 if r['hex_type'] == 'presence' else 0,\n",
    "            'latitude': random_lat,\n",
    "            'longitude': random_lng\n",
    "        }\n",
    "\n",
    "        psuedo_points.append(psuedo_point)\n",
    "\n",
    "df_psuedo_points = pd.DataFrame(psuedo_points)\n",
    "\n",
    "end_time = datetime.datetime.now()\n",
    "print('Executed in: ', (end_time - start_time))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(   taxon_id  hex_type   latitude  longitude\n",
       " 0      5165         1 -14.636833  27.610717\n",
       " 1      5165         1 -14.618621  27.511168\n",
       " 2      5165         1 -14.790290  27.693306\n",
       " 3      5165         1 -14.689536  27.622481\n",
       " 4      5165         1 -17.750372  23.167425,\n",
       " 9616)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_psuedo_points.head(), len(df_psuedo_points)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your annotation will be saved in a .csv file with a time stamp.\n",
    "\n",
    "You can use the new annotation data you extracted to use in fine tuning, head to `fine_tune_main.py` view instructions on how to set parameters and start fine-tuning a geomodel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"paths.json\", 'r') as f:\n",
    "    paths = json.load(f)\n",
    "\n",
    "date_now = datetime.datetime.now()\n",
    "df_psuedo_points.to_csv(os.path.join(paths['annotation'], str(date_now)+'.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "inatator",
   "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.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}