nearest_neighbour.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "user=os.getenv('DB_USER')\n",
    "password=os.getenv('DB_PASSWORD')\n",
    "engine = create_engine(f'postgresql://{user}:{password}@localhost:5432/gis')\n",
    "\n",
    "azure_user=os.getenv('PGUSER')\n",
    "azure_password=os.getenv('PGPASSWORD')\n",
    "azure_host=os.getenv('PGHOST')\n",
    "azure_port=os.getenv('PGPORT')\n",
    "azure_database=os.getenv('PGDATABASE')\n",
    "azure_engine = create_engine(f'postgresql://{azure_user}:{azure_password}@{azure_host}:{azure_port}/{azure_database}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL nearest neighbour (distinct)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Azure, postgresql, 1v core, 2 gb ram"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#show work mem\n",
    "\"\"\"SHOW work_mem;\"\"\"\n",
    "#set work mem\n",
    "\"\"\"SET work_mem TO '2GB';\"\"\"\n",
    "\"\"\"SET work_mem TO '16GB';\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timedelta('0 days 00:04:53.149282')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = pd.Timestamp.now()\n",
    "#connection\n",
    "conn = azure_engine.raw_connection()\n",
    "#conn = engine.raw_connection()\n",
    "cursor = conn.cursor()\n",
    "\n",
    "#execute sql\n",
    "sql=\"\"\"\n",
    "    DROP TABLE IF EXISTS os.knn;\n",
    "    WITH knn AS (\n",
    "        SELECT DISTINCT ON (A.uprn)\n",
    "            A.uprn as origin,\n",
    "            B.postcode as destination,\n",
    "            round(ST_Distance(A.geom, B.geom)::numeric,2) as distance\n",
    "        FROM\n",
    "            os.open_uprn_white_horse as A,\n",
    "            os.code_point_open_white_horse as B\n",
    "        WHERE\n",
    "            ST_DWithin(A.geom, B.geom, 5000)\n",
    "        ORDER BY\n",
    "            A.uprn,\n",
    "            ST_Distance(A.geom, B.geom) ASC,\n",
    "            destination\n",
    "    )\n",
    "    SELECT * INTO os.knn FROM knn \n",
    "\"\"\"\n",
    "cursor.execute(sql)\n",
    "conn.commit()\n",
    "conn.close()\n",
    "\n",
    "t2 = pd.Timestamp.now()\n",
    "dt = t2-t1\n",
    "dt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL nearest neighbour (lateral)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timedelta('0 days 00:01:26.346841')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = pd.Timestamp.now()\n",
    "#connection\n",
    "conn = azure_engine.raw_connection()\n",
    "cursor = conn.cursor()\n",
    "\n",
    "#execute sql\n",
    "sql=\"\"\"\n",
    "    DROP TABLE IF EXISTS os.knn_l;\n",
    "    WITH knn AS (\n",
    "        SELECT \n",
    "            A.uprn as origin,\n",
    "            B.postcode as destination,\n",
    "            round(ST_Distance(A.geom, B.geom)::numeric,2) as distance\n",
    "        FROM\n",
    "            os.open_uprn_white_horse as A\n",
    "        CROSS JOIN LATERAL (\n",
    "            SELECT\n",
    "                B.postcode,\n",
    "                B.geom\n",
    "            FROM\n",
    "                os.code_point_open_white_horse as B\n",
    "            WHERE\n",
    "                ST_DWithin(A.geom, B.geom, 5000)\n",
    "            ORDER BY\n",
    "                ST_Distance(A.geom, B.geom) ASC,\n",
    "                B.postcode\n",
    "            LIMIT\n",
    "                1   \n",
    "        ) B\n",
    "        ORDER BY\n",
    "            A.uprn\n",
    "    )\n",
    "    SELECT * INTO os.knn_l FROM knn \n",
    "\"\"\"\n",
    "cursor.execute(sql)\n",
    "conn.commit()\n",
    "conn.close()\n",
    "\n",
    "t2 = pd.Timestamp.now()\n",
    "dt = t2-t1\n",
    "dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "sql = \"\"\"SELECT * FROM os.knn_l\"\"\"\n",
    "data2 = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, data2, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Geopandas sjoin_nearest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"SELECT uprn, geom FROM os.open_uprn_white_horse\"\"\"\n",
    "uprn = gpd.read_postgis(sql, engine, geom_col='geom')\n",
    "\n",
    "sql = \"\"\"SELECT postcode, geom FROM os.code_point_open_white_horse\"\"\"\n",
    "codepoint = gpd.read_postgis(sql, engine, geom_col='geom')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timedelta('0 days 00:00:01.948764')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = pd.Timestamp.now()\n",
    "knn = (\n",
    "    uprn.sjoin_nearest(codepoint, max_distance=5000, distance_col='distance')\n",
    "    .rename(columns = {'uprn': 'origin', 'postcode': 'destination'})\n",
    "    .sort_values(['origin', 'destination'])\n",
    "    .drop_duplicates(subset='origin')\n",
    "      )\n",
    "t2 = pd.Timestamp.now()\n",
    "dt = t2-t1\n",
    "dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>geom</th>\n",
       "      <th>index_right</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, geom, index_right, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# python nearest neighbour - shapely (all vs all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"SELECT uprn, geom FROM os.open_uprn_white_horse\"\"\"\n",
    "uprn = gpd.read_postgis(sql, engine, geom_col='geom')\n",
    "\n",
    "sql = \"\"\"SELECT postcode, geom FROM os.code_point_open_white_horse\"\"\"\n",
    "codepoint = gpd.read_postgis(sql, engine, geom_col='geom')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#function that returns index of nearest geometry in b for every geometry in a\n",
    "def nearest_neighbour(geoma, geomb, maxdist):\n",
    "    \n",
    "    #combine, filter, compute distance, select nearest\n",
    "\n",
    "    combinations = [\n",
    "        geomb[geomb.intersects(i.buffer(maxdist))].distance(i).idxmin()\n",
    "        for i in geoma\n",
    "    ]    \n",
    "    \n",
    "    nearest = pd.DataFrame(\n",
    "        {\n",
    "            'origin': [i[0] for i in geoma.items()],\n",
    "            'destination': combinations,\n",
    "            'distance': [i.distance(geomb[combinations[n]]) for n,i in enumerate(geoma)]\n",
    "        }\n",
    "    )\n",
    "                    \n",
    "    return nearest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timedelta('0 days 00:15:15.376360')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = pd.Timestamp.now()\n",
    "\n",
    "#get ids of nearest neighbours\n",
    "knn = nearest_neighbour(uprn.geometry, codepoint.sort_values('postcode').geometry, 5000)\n",
    "\n",
    "#get values for ids\n",
    "knn = uprn[['uprn']].assign(\n",
    "    destination = codepoint.iloc[knn['destination']]['postcode'].to_list(),\n",
    "    distance = knn.distance.to_list()\n",
    "    )\n",
    "knn.columns = ['origin', 'destination', 'distance (m)']\n",
    "\n",
    "t2 = pd.Timestamp.now()\n",
    "dt = t2-t1\n",
    "dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance (m)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance, destination_y, distance (m)]\n",
       "Index: []"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# python nearest neighbour - shapely (strtree)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from shapely.strtree import STRtree"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"SELECT uprn, geom FROM os.open_uprn_white_horse\"\"\"\n",
    "uprn = gpd.read_postgis(sql, engine, geom_col='geom')\n",
    "\n",
    "sql = \"\"\"SELECT postcode, geom FROM os.code_point_open_white_horse\"\"\"\n",
    "codepoint = gpd.read_postgis(sql, engine, geom_col='geom')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#function that returns index of nearest geometry in b for every geometry in a\n",
    "def nearest_neighbour(geoma, geomb):\n",
    "    #tree\n",
    "    geomb = geomb.to_list()\n",
    "    tree = STRtree(geomb)\n",
    "    nearest = [[i[0], tree.query_nearest(i[1], return_distance = True)] for i in geoma.items()]\n",
    "    nearest = [[i[0], j, i[1][1][n]] for i in nearest for n, j in enumerate(i[1][0])]\n",
    "    \n",
    "    nearest = (\n",
    "        pd.DataFrame(\n",
    "            nearest,\n",
    "            columns = ['origin', 'destination', 'distance']\n",
    "        )\n",
    "    )\n",
    "                    \n",
    "    return nearest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timedelta('0 days 00:00:03.008664')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = pd.Timestamp.now()\n",
    "\n",
    "#get ids of nearest neighbours\n",
    "knn = nearest_neighbour(uprn.geometry, codepoint.geometry)\n",
    "\n",
    "#get values for ids\n",
    "knn = pd.merge(uprn, knn, left_index = True, right_on = 'origin')\n",
    "knn = pd.merge(codepoint, knn, left_index = True, right_on = 'destination')\n",
    "knn = knn[['uprn', 'postcode', 'distance']]\n",
    "knn.columns = ['origin', 'destination', 'distance (m)']\n",
    "knn = (\n",
    "    knn\n",
    "    .sort_values(['origin', 'destination'])\n",
    "    .drop_duplicates(subset='origin')\n",
    ")\n",
    "\n",
    "t2 = pd.Timestamp.now()\n",
    "dt = t2-t1\n",
    "dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance (m)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance, destination_y, distance (m)]\n",
       "Index: []"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#mismatch due to equidistant postcodes\n",
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scikit-Learn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.neighbors import NearestNeighbors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"SELECT uprn, geom FROM os.open_uprn_white_horse\"\"\"\n",
    "uprn = gpd.read_postgis(sql, engine, geom_col='geom')\n",
    "\n",
    "sql = \"\"\"SELECT postcode, geom FROM os.code_point_open_white_horse ORDER BY postcode\"\"\"\n",
    "codepoint = gpd.read_postgis(sql, engine, geom_col='geom')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timedelta('0 days 00:00:00.599692')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = pd.Timestamp.now()\n",
    "\n",
    "#get coordinates from geodataframes\n",
    "uprn_xy = list(zip(uprn.geometry.x, uprn.geometry.y))\n",
    "codep_xy =  list(zip(codepoint.geometry.x, codepoint.geometry.y))\n",
    "\n",
    "#get indexes for nearest neighbour\n",
    "neigh = NearestNeighbors(n_neighbors=1, radius=5000)\n",
    "neigh.fit(codep_xy)\n",
    "knn = neigh.kneighbors(uprn_xy)\n",
    "\n",
    "#get values for indexes\n",
    "knn = uprn[['uprn']].assign(\n",
    "    destination = codepoint.iloc[[i[0] for i in knn[1]]]['postcode'].to_list(),\n",
    "    distance = knn[0]\n",
    "    )\n",
    "knn.columns = ['origin', 'destination', 'distance (m)']\n",
    "\n",
    "t2 = pd.Timestamp.now()\n",
    "dt = t2-t1\n",
    "dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance (m)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7411</th>\n",
       "      <td>10014028783</td>\n",
       "      <td>OX12 7AG</td>\n",
       "      <td>40.82</td>\n",
       "      <td>OX12 7BB</td>\n",
       "      <td>40.816663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10682</th>\n",
       "      <td>10014069848</td>\n",
       "      <td>OX14 3JP</td>\n",
       "      <td>16.76</td>\n",
       "      <td>OX14 3LN</td>\n",
       "      <td>16.763055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53906</th>\n",
       "      <td>100120908478</td>\n",
       "      <td>OX14 5AU</td>\n",
       "      <td>29.15</td>\n",
       "      <td>OX14 5FU</td>\n",
       "      <td>29.154759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65398</th>\n",
       "      <td>100120920096</td>\n",
       "      <td>OX2 9BW</td>\n",
       "      <td>60.42</td>\n",
       "      <td>OX2 9DB</td>\n",
       "      <td>60.415230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74235</th>\n",
       "      <td>100120929030</td>\n",
       "      <td>OX12 9AW</td>\n",
       "      <td>39.20</td>\n",
       "      <td>OX12 9EL</td>\n",
       "      <td>39.204592</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             origin destination_x  distance destination_y  distance (m)\n",
       "7411    10014028783      OX12 7AG     40.82      OX12 7BB     40.816663\n",
       "10682   10014069848      OX14 3JP     16.76      OX14 3LN     16.763055\n",
       "53906  100120908478      OX14 5AU     29.15      OX14 5FU     29.154759\n",
       "65398  100120920096       OX2 9BW     60.42       OX2 9DB     60.415230\n",
       "74235  100120929030      OX12 9AW     39.20      OX12 9EL     39.204592"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance (m)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance, destination_y, distance (m)]\n",
       "Index: []"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['distance']!=data3['distance (m)'].round(2)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Apache Sedona"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sedona.spark import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "25/01/29 21:01:48 WARN Utils: Your hostname, leo resolves to a loopback address: 127.0.1.1; using 192.168.0.71 instead (on interface wlp0s20f3)\n",
      "25/01/29 21:01:48 WARN Utils: Set SPARK_LOCAL_IP if you need to bind to another address\n",
      "https://artifacts.unidata.ucar.edu/repository/unidata-all added as a remote repository with the name: repo-1\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ":: loading settings :: url = jar:file:/home/carlos/.local/share/virtualenvs/spatial_knn-oihhALkR/lib/python3.12/site-packages/pyspark/jars/ivy-2.5.1.jar!/org/apache/ivy/core/settings/ivysettings.xml\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Ivy Default Cache set to: /home/carlos/.ivy2/cache\n",
      "The jars for the packages stored in: /home/carlos/.ivy2/jars\n",
      "org.postgresql#postgresql added as a dependency\n",
      "org.apache.sedona#sedona-spark-3.3_2.12 added as a dependency\n",
      "org.datasyslab#geotools-wrapper added as a dependency\n",
      ":: resolving dependencies :: org.apache.spark#spark-submit-parent-a359bfec-1b53-43f6-b6ef-5f9b82accda7;1.0\n",
      "\tconfs: [default]\n",
      "\tfound org.postgresql#postgresql;42.7.5 in central\n",
      "\tfound org.checkerframework#checker-qual;3.48.3 in central\n",
      "\tfound org.apache.sedona#sedona-spark-3.3_2.12;1.7.0 in central\n",
      "\tfound org.apache.sedona#sedona-common;1.7.0 in central\n",
      "\tfound org.apache.commons#commons-math3;3.6.1 in central\n",
      "\tfound org.locationtech.jts#jts-core;1.20.0 in central\n",
      "\tfound org.wololo#jts2geojson;0.16.1 in central\n",
      "\tfound org.locationtech.spatial4j#spatial4j;0.8 in central\n",
      "\tfound com.google.geometry#s2-geometry;2.0.0 in central\n",
      "\tfound com.google.guava#guava;25.1-jre in central\n",
      "\tfound com.google.code.findbugs#jsr305;3.0.2 in central\n",
      "\tfound com.google.errorprone#error_prone_annotations;2.1.3 in central\n",
      "\tfound com.google.j2objc#j2objc-annotations;1.1 in central\n",
      "\tfound org.codehaus.mojo#animal-sniffer-annotations;1.14 in central\n",
      "\tfound com.uber#h3;4.1.1 in central\n",
      "\tfound net.sf.geographiclib#GeographicLib-Java;1.52 in central\n",
      "\tfound com.github.ben-manes.caffeine#caffeine;2.9.2 in central\n",
      "\tfound com.google.errorprone#error_prone_annotations;2.5.1 in central\n",
      "\tfound org.apache.sedona#sedona-spark-common-3.3_2.12;1.7.0 in central\n",
      "\tfound org.xerial#sqlite-jdbc;3.41.2.2 in central\n",
      "\tfound commons-lang#commons-lang;2.6 in central\n",
      "\tfound graphframes#graphframes;0.8.3-spark3.4-s_2.12 in spark-packages\n",
      "\tfound org.slf4j#slf4j-api;1.7.36 in central\n",
      "\tfound org.scala-lang.modules#scala-collection-compat_2.12;2.5.0 in central\n",
      "\tfound org.beryx#awt-color-factory;1.0.0 in central\n",
      "\tfound org.datasyslab#geotools-wrapper;1.7.0-28.5 in central\n",
      ":: resolution report :: resolve 1480ms :: artifacts dl 62ms\n",
      "\t:: modules in use:\n",
      "\tcom.github.ben-manes.caffeine#caffeine;2.9.2 from central in [default]\n",
      "\tcom.google.code.findbugs#jsr305;3.0.2 from central in [default]\n",
      "\tcom.google.errorprone#error_prone_annotations;2.5.1 from central in [default]\n",
      "\tcom.google.geometry#s2-geometry;2.0.0 from central in [default]\n",
      "\tcom.google.guava#guava;25.1-jre from central in [default]\n",
      "\tcom.google.j2objc#j2objc-annotations;1.1 from central in [default]\n",
      "\tcom.uber#h3;4.1.1 from central in [default]\n",
      "\tcommons-lang#commons-lang;2.6 from central in [default]\n",
      "\tgraphframes#graphframes;0.8.3-spark3.4-s_2.12 from spark-packages in [default]\n",
      "\tnet.sf.geographiclib#GeographicLib-Java;1.52 from central in [default]\n",
      "\torg.apache.commons#commons-math3;3.6.1 from central in [default]\n",
      "\torg.apache.sedona#sedona-common;1.7.0 from central in [default]\n",
      "\torg.apache.sedona#sedona-spark-3.3_2.12;1.7.0 from central in [default]\n",
      "\torg.apache.sedona#sedona-spark-common-3.3_2.12;1.7.0 from central in [default]\n",
      "\torg.beryx#awt-color-factory;1.0.0 from central in [default]\n",
      "\torg.checkerframework#checker-qual;3.48.3 from central in [default]\n",
      "\torg.codehaus.mojo#animal-sniffer-annotations;1.14 from central in [default]\n",
      "\torg.datasyslab#geotools-wrapper;1.7.0-28.5 from central in [default]\n",
      "\torg.locationtech.jts#jts-core;1.20.0 from central in [default]\n",
      "\torg.locationtech.spatial4j#spatial4j;0.8 from central in [default]\n",
      "\torg.postgresql#postgresql;42.7.5 from central in [default]\n",
      "\torg.scala-lang.modules#scala-collection-compat_2.12;2.5.0 from central in [default]\n",
      "\torg.slf4j#slf4j-api;1.7.36 from central in [default]\n",
      "\torg.wololo#jts2geojson;0.16.1 from central in [default]\n",
      "\torg.xerial#sqlite-jdbc;3.41.2.2 from central in [default]\n",
      "\t:: evicted modules:\n",
      "\torg.checkerframework#checker-qual;2.0.0 by [org.checkerframework#checker-qual;3.48.3] in [default]\n",
      "\tcom.google.errorprone#error_prone_annotations;2.1.3 by [com.google.errorprone#error_prone_annotations;2.5.1] in [default]\n",
      "\torg.checkerframework#checker-qual;3.10.0 by [org.checkerframework#checker-qual;3.48.3] in [default]\n",
      "\t---------------------------------------------------------------------\n",
      "\t|                  |            modules            ||   artifacts   |\n",
      "\t|       conf       | number| search|dwnlded|evicted|| number|dwnlded|\n",
      "\t---------------------------------------------------------------------\n",
      "\t|      default     |   28  |   0   |   0   |   3   ||   25  |   0   |\n",
      "\t---------------------------------------------------------------------\n",
      ":: retrieving :: org.apache.spark#spark-submit-parent-a359bfec-1b53-43f6-b6ef-5f9b82accda7\n",
      "\tconfs: [default]\n",
      "\t0 artifacts copied, 25 already retrieved (0kB/23ms)\n",
      "25/01/29 21:01:52 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n",
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "config = (\n",
    "    SedonaContext\n",
    "    .builder()\n",
    "    .master(\"local[*]\")\n",
    "    .config('spark.jars.packages',\n",
    "            'org.postgresql:postgresql:42.7.5,'\n",
    "            'org.apache.sedona:sedona-spark-3.3_2.12:1.7.0,'\n",
    "            'org.datasyslab:geotools-wrapper:1.7.0-28.5')\n",
    "    .config('spark.jars.repositories', 'https://artifacts.unidata.ucar.edu/repository/unidata-all')\n",
    "    .config(\"spark.executor.memory\", \"12g\")\n",
    "    .config(\"spark.driver.memory\", \"12g\")\n",
    "    .getOrCreate()\n",
    ")\n",
    "sedona = SedonaContext.create(config)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"SELECT uprn uprn, geom FROM os.open_uprn_white_horse\"\"\"\n",
    "uprn = gpd.read_postgis(sql, engine)\n",
    "\n",
    "sql = \"\"\"SELECT postcode, geom FROM os.code_point_open_white_horse ORDER BY postcode\"\"\"\n",
    "codepoint = gpd.read_postgis(sql, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "codepoint = sedona.createDataFrame(codepoint)\n",
    "uprn = sedona.createDataFrame(uprn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "uprn.createOrReplaceTempView(\"uprn\")\n",
    "codepoint.createOrReplaceTempView(\"codepoint\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## partial sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "25/01/25 17:25:18 WARN JoinQuery: UseIndex is true, but no index exists. Will build index on the fly.\n",
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Timedelta('0 days 00:03:56.683261')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = pd.Timestamp.now()\n",
    "\n",
    "sql = \"\"\"\n",
    "    SELECT\n",
    "        A.uprn origin,\n",
    "        B.postcode destination,\n",
    "        ST_Distance(A.geom, B.geom) as distance_m\n",
    "    FROM\n",
    "        uprn A,\n",
    "        codepoint B\n",
    "    WHERE\n",
    "        ST_Distance(A.geom, B.geom) <= 5000        \n",
    "\"\"\"\n",
    "knn = sedona.sql(sql).sort(['origin', 'distance_m', 'destination']).dropDuplicates(['origin']).toPandas()\n",
    "\n",
    "t2 = pd.Timestamp.now()\n",
    "dt = t2-t1\n",
    "dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_m</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance, destination_y, distance_m]\n",
       "Index: []"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## pure sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "25/01/29 21:02:39 WARN JoinQuery: UseIndex is true, but no index exists. Will build index on the fly.\n",
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Timedelta('0 days 00:04:41.992870')"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = pd.Timestamp.now()\n",
    "\n",
    "sql = \"\"\"\n",
    "    WITH knn AS (\n",
    "        SELECT \n",
    "            A.uprn as origin,\n",
    "            B.postcode as destination,\n",
    "            round(ST_Distance(A.geom, B.geom),2) as distance,\n",
    "            row_number() OVER (PARTITION BY A.uprn ORDER BY A.uprn,ST_Distance(A.geom, B.geom) ASC, B.postcode) AS rn\n",
    "        FROM\n",
    "            uprn A,\n",
    "            codepoint B\n",
    "        WHERE\n",
    "            ST_DWithin(A.geom, B.geom, 5000)\n",
    "        ORDER BY\n",
    "            A.uprn,\n",
    "            ST_Distance(A.geom, B.geom) ASC,\n",
    "            destination\n",
    "    )\n",
    "    SELECT origin, destination, distance FROM knn WHERE rn = 1      \n",
    "\"\"\"\n",
    "knn = sedona.sql(sql).toPandas()\n",
    "\n",
    "t2 = pd.Timestamp.now()\n",
    "dt = t2-t1\n",
    "dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## KNN operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Timedelta('0 days 00:00:10.045720')"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = pd.Timestamp.now()\n",
    "sql = \"\"\"\n",
    "    WITH knn AS (\n",
    "        SELECT\n",
    "            A.uprn origin,\n",
    "            B.postcode destination,\n",
    "            ST_Distance(A.geom, B.geom) as distance,\n",
    "            row_number() OVER (PARTITION BY A.uprn ORDER BY A.uprn,ST_Distance(A.geom, B.geom) ASC, B.postcode) AS rn\n",
    "        FROM\n",
    "            uprn A\n",
    "        JOIN\n",
    "            codepoint B\n",
    "        ON\n",
    "            ST_KNN(A.geom, B.geom, 10, FALSE) \n",
    "        ORDER BY\n",
    "            A.uprn,\n",
    "            ST_Distance(A.geom, B.geom) ASC,\n",
    "            destination        \n",
    "    )\n",
    "    SELECT origin, destination, distance FROM knn WHERE rn = 1           \n",
    "\"\"\"\n",
    "knn = sedona.sql(sql).toPandas()\n",
    "\n",
    "t2 = pd.Timestamp.now()\n",
    "dt = t2-t1\n",
    "dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Kotlin all vs all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "knn = pd.read_csv('knn_kotlin/kotlin_all_vs_all.csv', skipinitialspace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Kotlin strtree"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "knn = pd.read_csv('knn_kotlin/kotlin_tree.csv', skipinitialspace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scala all vs all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "knn = pd.read_csv('knn_scala/scala_all_vs_all.csv', skipinitialspace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scala strtree"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "knn = pd.read_csv('knn_scala/scala_tree.csv', skipinitialspace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Rust all vs all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "knn = pd.read_csv('knn_rust/rust_all_vs_all.csv', skipinitialspace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "tags": []
   },
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Rust tree"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "knn = pd.read_csv('knn_rust/rust_tree.csv', skipinitialspace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": []
   },
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BigQuery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\" \n",
    "CREATE SCHEMA os;\n",
    "CREATE TABLE IF NOT EXISTS os.code_point_open_white_horse\n",
    "(\n",
    "    postcode text,\n",
    "    geom geometry\n",
    ");\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS os.open_uprn_white_horse\n",
    "(\n",
    "    uprn bigint,\n",
    "    \"X_COORDINATE\" double precision,\n",
    "    \"Y_COORDINATE\" double precision,\n",
    "    \"LATITUDE\" double precision,\n",
    "    \"LONGITUDE\" double precision,\n",
    "    geom geometry\n",
    ");\n",
    "\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "    CREATE TABLE os.knn AS\n",
    "    WITH knn AS (\n",
    "        SELECT \n",
    "            A.UPRN as origin,\n",
    "            B.postcode as destination,\n",
    "            round(ST_Distance(A.geom, B.geom, TRUE),2) as distance,\n",
    "            row_number() OVER (PARTITION BY A.UPRN ORDER BY A.UPRN,ST_Distance(A.geom, B.geom) ASC, B.postcode) AS rn\n",
    "        FROM\n",
    "            os.open_uprn_white_horse as A,\n",
    "            os.code_point_open_white_horse as B\n",
    "        WHERE\n",
    "            ST_DWithin(A.geom, B.geom, 5000)\n",
    "        ORDER BY\n",
    "            A.UPRN,\n",
    "            ST_Distance(A.geom, B.geom, TRUE) ASC,\n",
    "            destination\n",
    "    )\n",
    "    SELECT * EXCEPT(rn) FROM knn WHERE rn = 1;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "knn = pd.read_csv('big_query_result.csv', skipinitialspace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "tags": []
   },
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>981</th>\n",
       "      <td>10014021676</td>\n",
       "      <td>OX143ER</td>\n",
       "      <td>34.93</td>\n",
       "      <td>OX143HU</td>\n",
       "      <td>34.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1313</th>\n",
       "      <td>10014022051</td>\n",
       "      <td>SN7 8QE</td>\n",
       "      <td>681.36</td>\n",
       "      <td>SN7 8QD</td>\n",
       "      <td>682.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5899</th>\n",
       "      <td>10014027118</td>\n",
       "      <td>OX1 5AZ</td>\n",
       "      <td>51.35</td>\n",
       "      <td>OX1 5BA</td>\n",
       "      <td>51.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5946</th>\n",
       "      <td>10014027169</td>\n",
       "      <td>OX145EE</td>\n",
       "      <td>24.48</td>\n",
       "      <td>OX145SA</td>\n",
       "      <td>24.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9880</th>\n",
       "      <td>10014068978</td>\n",
       "      <td>SN7 7YS</td>\n",
       "      <td>52.33</td>\n",
       "      <td>SN7 7YT</td>\n",
       "      <td>52.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16470</th>\n",
       "      <td>10014080969</td>\n",
       "      <td>OX129RQ</td>\n",
       "      <td>73.25</td>\n",
       "      <td>OX129JR</td>\n",
       "      <td>73.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17288</th>\n",
       "      <td>10014081839</td>\n",
       "      <td>SN7 7YY</td>\n",
       "      <td>89.45</td>\n",
       "      <td>SN7 7BW</td>\n",
       "      <td>89.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18986</th>\n",
       "      <td>10014083731</td>\n",
       "      <td>OX2 0PH</td>\n",
       "      <td>1.00</td>\n",
       "      <td>OX2 0PL</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19068</th>\n",
       "      <td>10014083814</td>\n",
       "      <td>SN7 8QS</td>\n",
       "      <td>490.98</td>\n",
       "      <td>SN7 8QR</td>\n",
       "      <td>491.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20213</th>\n",
       "      <td>10024886689</td>\n",
       "      <td>SN6 8HX</td>\n",
       "      <td>822.11</td>\n",
       "      <td>SN6 8JG</td>\n",
       "      <td>823.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20596</th>\n",
       "      <td>10024897047</td>\n",
       "      <td>SN7 7QL</td>\n",
       "      <td>275.44</td>\n",
       "      <td>SN7 7QN</td>\n",
       "      <td>275.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20635</th>\n",
       "      <td>10024897191</td>\n",
       "      <td>OX120AH</td>\n",
       "      <td>544.03</td>\n",
       "      <td>OX120DG</td>\n",
       "      <td>544.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22344</th>\n",
       "      <td>10090579021</td>\n",
       "      <td>OX141SG</td>\n",
       "      <td>245.92</td>\n",
       "      <td>OX141RZ</td>\n",
       "      <td>246.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24638</th>\n",
       "      <td>10090581329</td>\n",
       "      <td>OX129GB</td>\n",
       "      <td>46.45</td>\n",
       "      <td>OX129GE</td>\n",
       "      <td>46.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26173</th>\n",
       "      <td>10090582867</td>\n",
       "      <td>OX128FU</td>\n",
       "      <td>57.73</td>\n",
       "      <td>OX128FW</td>\n",
       "      <td>57.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26846</th>\n",
       "      <td>10093195331</td>\n",
       "      <td>OX129JQ</td>\n",
       "      <td>72.50</td>\n",
       "      <td>OX129JH</td>\n",
       "      <td>72.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28035</th>\n",
       "      <td>10093196528</td>\n",
       "      <td>OX128FY</td>\n",
       "      <td>22.13</td>\n",
       "      <td>OX128BP</td>\n",
       "      <td>22.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28602</th>\n",
       "      <td>10093197098</td>\n",
       "      <td>OX116GY</td>\n",
       "      <td>75.50</td>\n",
       "      <td>OX116FF</td>\n",
       "      <td>75.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28896</th>\n",
       "      <td>10093197392</td>\n",
       "      <td>OX144FZ</td>\n",
       "      <td>57.90</td>\n",
       "      <td>OX144TR</td>\n",
       "      <td>57.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29704</th>\n",
       "      <td>10093198206</td>\n",
       "      <td>OX128BH</td>\n",
       "      <td>15.69</td>\n",
       "      <td>OX128AX</td>\n",
       "      <td>15.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32135</th>\n",
       "      <td>10094277230</td>\n",
       "      <td>OX2 9HX</td>\n",
       "      <td>120.71</td>\n",
       "      <td>OX2 9FG</td>\n",
       "      <td>120.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32704</th>\n",
       "      <td>10094277799</td>\n",
       "      <td>OX136GL</td>\n",
       "      <td>200.26</td>\n",
       "      <td>OX136GY</td>\n",
       "      <td>200.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34250</th>\n",
       "      <td>10094279348</td>\n",
       "      <td>OX129JX</td>\n",
       "      <td>77.10</td>\n",
       "      <td>OX129RL</td>\n",
       "      <td>77.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35248</th>\n",
       "      <td>10094280351</td>\n",
       "      <td>OX127GB</td>\n",
       "      <td>62.97</td>\n",
       "      <td>OX127FX</td>\n",
       "      <td>63.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35985</th>\n",
       "      <td>10094281089</td>\n",
       "      <td>OX127FU</td>\n",
       "      <td>85.37</td>\n",
       "      <td>OX127GH</td>\n",
       "      <td>85.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36023</th>\n",
       "      <td>10094281127</td>\n",
       "      <td>OX2 9AA</td>\n",
       "      <td>82.41</td>\n",
       "      <td>OX2 9AF</td>\n",
       "      <td>82.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36979</th>\n",
       "      <td>10095809653</td>\n",
       "      <td>OX2 9TF</td>\n",
       "      <td>25.48</td>\n",
       "      <td>OX2 9TJ</td>\n",
       "      <td>25.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37242</th>\n",
       "      <td>10095809916</td>\n",
       "      <td>SN7 7NH</td>\n",
       "      <td>162.13</td>\n",
       "      <td>SN7 7FE</td>\n",
       "      <td>162.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37686</th>\n",
       "      <td>10095810363</td>\n",
       "      <td>OX127GF</td>\n",
       "      <td>114.02</td>\n",
       "      <td>OX127GS</td>\n",
       "      <td>114.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38680</th>\n",
       "      <td>10095811359</td>\n",
       "      <td>OX129FT</td>\n",
       "      <td>136.45</td>\n",
       "      <td>OX129FX</td>\n",
       "      <td>136.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38847</th>\n",
       "      <td>10095811526</td>\n",
       "      <td>OX141YL</td>\n",
       "      <td>374.19</td>\n",
       "      <td>OX141XE</td>\n",
       "      <td>374.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38849</th>\n",
       "      <td>10095811528</td>\n",
       "      <td>OX141YL</td>\n",
       "      <td>372.90</td>\n",
       "      <td>OX141XE</td>\n",
       "      <td>373.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41237</th>\n",
       "      <td>100120899382</td>\n",
       "      <td>OX142ES</td>\n",
       "      <td>42.52</td>\n",
       "      <td>OX142ET</td>\n",
       "      <td>42.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44689</th>\n",
       "      <td>100120902856</td>\n",
       "      <td>OX145PN</td>\n",
       "      <td>73.01</td>\n",
       "      <td>OX145PZ</td>\n",
       "      <td>73.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45976</th>\n",
       "      <td>100120904144</td>\n",
       "      <td>OX136JZ</td>\n",
       "      <td>47.38</td>\n",
       "      <td>OX136LA</td>\n",
       "      <td>47.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48117</th>\n",
       "      <td>100120906293</td>\n",
       "      <td>OX141HD</td>\n",
       "      <td>44.03</td>\n",
       "      <td>OX141HE</td>\n",
       "      <td>44.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48939</th>\n",
       "      <td>100120907116</td>\n",
       "      <td>OX136JX</td>\n",
       "      <td>73.68</td>\n",
       "      <td>OX136DF</td>\n",
       "      <td>73.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50018</th>\n",
       "      <td>100120908205</td>\n",
       "      <td>OX145RT</td>\n",
       "      <td>52.15</td>\n",
       "      <td>OX145RY</td>\n",
       "      <td>52.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50043</th>\n",
       "      <td>100120908230</td>\n",
       "      <td>OX136NG</td>\n",
       "      <td>60.44</td>\n",
       "      <td>OX136PD</td>\n",
       "      <td>60.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50734</th>\n",
       "      <td>100120908930</td>\n",
       "      <td>OX142LY</td>\n",
       "      <td>101.61</td>\n",
       "      <td>OX142ND</td>\n",
       "      <td>101.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51913</th>\n",
       "      <td>100120910116</td>\n",
       "      <td>OX142PQ</td>\n",
       "      <td>80.61</td>\n",
       "      <td>OX142PA</td>\n",
       "      <td>80.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59053</th>\n",
       "      <td>100120917341</td>\n",
       "      <td>SN7 7UJ</td>\n",
       "      <td>53.14</td>\n",
       "      <td>SN7 7UR</td>\n",
       "      <td>53.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61710</th>\n",
       "      <td>100120920028</td>\n",
       "      <td>OX2 9DR</td>\n",
       "      <td>46.86</td>\n",
       "      <td>OX2 9DW</td>\n",
       "      <td>46.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61778</th>\n",
       "      <td>100120920096</td>\n",
       "      <td>OX2 9BW</td>\n",
       "      <td>60.42</td>\n",
       "      <td>OX2 9DB</td>\n",
       "      <td>60.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61854</th>\n",
       "      <td>100120920174</td>\n",
       "      <td>OX2 9GT</td>\n",
       "      <td>79.03</td>\n",
       "      <td>OX2 9BP</td>\n",
       "      <td>79.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63274</th>\n",
       "      <td>100120921605</td>\n",
       "      <td>OX2 9FQ</td>\n",
       "      <td>60.03</td>\n",
       "      <td>OX2 9DN</td>\n",
       "      <td>60.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63889</th>\n",
       "      <td>100120922221</td>\n",
       "      <td>OX1 5QS</td>\n",
       "      <td>97.58</td>\n",
       "      <td>OX1 5QZ</td>\n",
       "      <td>97.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65422</th>\n",
       "      <td>100120923765</td>\n",
       "      <td>SN6 8TN</td>\n",
       "      <td>61.40</td>\n",
       "      <td>SN6 8TW</td>\n",
       "      <td>61.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67441</th>\n",
       "      <td>100120925811</td>\n",
       "      <td>OX127HP</td>\n",
       "      <td>55.95</td>\n",
       "      <td>OX127HF</td>\n",
       "      <td>55.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68964</th>\n",
       "      <td>100120927368</td>\n",
       "      <td>OX129YB</td>\n",
       "      <td>62.03</td>\n",
       "      <td>OX129YD</td>\n",
       "      <td>62.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69750</th>\n",
       "      <td>100120928161</td>\n",
       "      <td>OX127JE</td>\n",
       "      <td>74.11</td>\n",
       "      <td>OX127JJ</td>\n",
       "      <td>74.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70615</th>\n",
       "      <td>100120929030</td>\n",
       "      <td>OX129AW</td>\n",
       "      <td>39.20</td>\n",
       "      <td>OX129EL</td>\n",
       "      <td>39.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72023</th>\n",
       "      <td>100120930451</td>\n",
       "      <td>OX127LU</td>\n",
       "      <td>45.88</td>\n",
       "      <td>OX127LH</td>\n",
       "      <td>45.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73818</th>\n",
       "      <td>100120932730</td>\n",
       "      <td>OX144EG</td>\n",
       "      <td>140.43</td>\n",
       "      <td>OX144EQ</td>\n",
       "      <td>140.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74127</th>\n",
       "      <td>100120933225</td>\n",
       "      <td>OX110DT</td>\n",
       "      <td>53.76</td>\n",
       "      <td>OX110DU</td>\n",
       "      <td>53.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74617</th>\n",
       "      <td>100120933875</td>\n",
       "      <td>OX119QN</td>\n",
       "      <td>113.16</td>\n",
       "      <td>OX119QJ</td>\n",
       "      <td>113.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78165</th>\n",
       "      <td>100121310011</td>\n",
       "      <td>OX120HZ</td>\n",
       "      <td>72.45</td>\n",
       "      <td>OX120JX</td>\n",
       "      <td>72.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78653</th>\n",
       "      <td>100121310631</td>\n",
       "      <td>OX128AH</td>\n",
       "      <td>8.60</td>\n",
       "      <td>OX128AL</td>\n",
       "      <td>8.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80077</th>\n",
       "      <td>100121312765</td>\n",
       "      <td>OX143LT</td>\n",
       "      <td>6.40</td>\n",
       "      <td>OX143LZ</td>\n",
       "      <td>6.41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             origin destination_x  distance_x destination_y  distance_y\n",
       "981     10014021676       OX143ER       34.93       OX143HU       34.94\n",
       "1313    10014022051       SN7 8QE      681.36       SN7 8QD      682.68\n",
       "5899    10014027118       OX1 5AZ       51.35       OX1 5BA       51.37\n",
       "5946    10014027169       OX145EE       24.48       OX145SA       24.49\n",
       "9880    10014068978       SN7 7YS       52.33       SN7 7YT       52.36\n",
       "16470   10014080969       OX129RQ       73.25       OX129JR       73.38\n",
       "17288   10014081839       SN7 7YY       89.45       SN7 7BW       89.52\n",
       "18986   10014083731       OX2 0PH        1.00       OX2 0PL        1.00\n",
       "19068   10014083814       SN7 8QS      490.98       SN7 8QR      491.63\n",
       "20213   10024886689       SN6 8HX      822.11       SN6 8JG      823.20\n",
       "20596   10024897047       SN7 7QL      275.44       SN7 7QN      275.78\n",
       "20635   10024897191       OX120AH      544.03       OX120DG      544.96\n",
       "22344   10090579021       OX141SG      245.92       OX141RZ      246.09\n",
       "24638   10090581329       OX129GB       46.45       OX129GE       46.56\n",
       "26173   10090582867       OX128FU       57.73       OX128FW       57.77\n",
       "26846   10093195331       OX129JQ       72.50       OX129JH       72.60\n",
       "28035   10093196528       OX128FY       22.13       OX128BP       22.17\n",
       "28602   10093197098       OX116GY       75.50       OX116FF       75.56\n",
       "28896   10093197392       OX144FZ       57.90       OX144TR       57.99\n",
       "29704   10093198206       OX128BH       15.69       OX128AX       15.70\n",
       "32135   10094277230       OX2 9HX      120.71       OX2 9FG      120.84\n",
       "32704   10094277799       OX136GL      200.26       OX136GY      200.45\n",
       "34250   10094279348       OX129JX       77.10       OX129RL       77.23\n",
       "35248   10094280351       OX127GB       62.97       OX127FX       63.09\n",
       "35985   10094281089       OX127FU       85.37       OX127GH       85.43\n",
       "36023   10094281127       OX2 9AA       82.41       OX2 9AF       82.49\n",
       "36979   10095809653       OX2 9TF       25.48       OX2 9TJ       25.49\n",
       "37242   10095809916       SN7 7NH      162.13       SN7 7FE      162.39\n",
       "37686   10095810363       OX127GF      114.02       OX127GS      114.15\n",
       "38680   10095811359       OX129FT      136.45       OX129FX      136.55\n",
       "38847   10095811526       OX141YL      374.19       OX141XE      374.36\n",
       "38849   10095811528       OX141YL      372.90       OX141XE      373.03\n",
       "41237  100120899382       OX142ES       42.52       OX142ET       42.59\n",
       "44689  100120902856       OX145PN       73.01       OX145PZ       73.03\n",
       "45976  100120904144       OX136JZ       47.38       OX136LA       47.44\n",
       "48117  100120906293       OX141HD       44.03       OX141HE       44.06\n",
       "48939  100120907116       OX136JX       73.68       OX136DF       73.81\n",
       "50018  100120908205       OX145RT       52.15       OX145RY       52.26\n",
       "50043  100120908230       OX136NG       60.44       OX136PD       60.46\n",
       "50734  100120908930       OX142LY      101.61       OX142ND      101.64\n",
       "51913  100120910116       OX142PQ       80.61       OX142PA       80.70\n",
       "59053  100120917341       SN7 7UJ       53.14       SN7 7UR       53.17\n",
       "61710  100120920028       OX2 9DR       46.86       OX2 9DW       46.89\n",
       "61778  100120920096       OX2 9BW       60.42       OX2 9DB       60.44\n",
       "61854  100120920174       OX2 9GT       79.03       OX2 9BP       79.10\n",
       "63274  100120921605       OX2 9FQ       60.03       OX2 9DN       60.10\n",
       "63889  100120922221       OX1 5QS       97.58       OX1 5QZ       97.65\n",
       "65422  100120923765       SN6 8TN       61.40       SN6 8TW       61.42\n",
       "67441  100120925811       OX127HP       55.95       OX127HF       55.99\n",
       "68964  100120927368       OX129YB       62.03       OX129YD       62.19\n",
       "69750  100120928161       OX127JE       74.11       OX127JJ       74.23\n",
       "70615  100120929030       OX129AW       39.20       OX129EL       39.22\n",
       "72023  100120930451       OX127LU       45.88       OX127LH       45.91\n",
       "73818  100120932730       OX144EG      140.43       OX144EQ      140.48\n",
       "74127  100120933225       OX110DT       53.76       OX110DU       53.87\n",
       "74617  100120933875       OX119QN      113.16       OX119QJ      113.27\n",
       "78165  100121310011       OX120HZ       72.45       OX120JX       72.59\n",
       "78653  100121310631       OX128AH        8.60       OX128AL        8.61\n",
       "80077  100121312765       OX143LT        6.40       OX143LZ        6.41"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Redshift"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\" \n",
    "CREATE SCHEMA os;\n",
    "CREATE TABLE IF NOT EXISTS os.code_point_open_white_horse\n",
    "(\n",
    "    postcode text,\n",
    "    geom geometry\n",
    ");\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS os.open_uprn_white_horse\n",
    "(\n",
    "    uprn bigint,\n",
    "    \"X_COORDINATE\" double precision,\n",
    "    \"Y_COORDINATE\" double precision,\n",
    "    \"LATITUDE\" double precision,\n",
    "    \"LONGITUDE\" double precision,\n",
    "    geom geometry\n",
    ");\n",
    "\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "    CREATE TABLE os.knn AS\n",
    "    WITH knn AS (\n",
    "        SELECT \n",
    "            A.UPRN as origin,\n",
    "            B.postcode as destination,\n",
    "            round(ST_Distance(A.geom, B.geom),2) as distance,\n",
    "            row_number() OVER (PARTITION BY A.UPRN ORDER BY A.UPRN,ST_Distance(A.geom, B.geom) ASC, B.postcode) AS rn\n",
    "        FROM\n",
    "            os.open_uprn_white_horse as A,\n",
    "            os.code_point_open_white_horse as B\n",
    "        WHERE\n",
    "            ST_DWithin(A.geom, B.geom, 5000)\n",
    "        ORDER BY\n",
    "            A.UPRN,\n",
    "            ST_Distance(A.geom, B.geom) ASC,\n",
    "            destination\n",
    "    )\n",
    "    SELECT origin, destination, distance FROM knn WHERE rn = 1;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "knn = pd.read_csv('redshift_result.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Athena"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "    CREATE EXTERNAL TABLE IF NOT EXISTS `gis`.`code_point_open_white_horse` (\n",
    "    `postcode` string,\n",
    "    `geom` binary,\n",
    "    `wkt` string\n",
    "    )\n",
    "    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'\n",
    "    WITH SERDEPROPERTIES ('field.delim' = '|')\n",
    "    STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'\n",
    "    LOCATION 's3://giscarlos/codepoint_wh/'\n",
    "    TBLPROPERTIES ('classification' = 'csv');\n",
    "\n",
    "    CREATE EXTERNAL TABLE IF NOT EXISTS `gis`.`open_uprn_white_horse` (\n",
    "    `uprn` bigint,\n",
    "    `x_coordinate` double,\n",
    "    `y_coordinate` double,\n",
    "    `latitude` double,\n",
    "    `longitude` double,\n",
    "    `geom` binary,\n",
    "    `wkt` string\n",
    "    )\n",
    "    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'\n",
    "    WITH SERDEPROPERTIES ('field.delim' = '|')\n",
    "    STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'\n",
    "    LOCATION 's3://giscarlos/uprn_wh/'\n",
    "    TBLPROPERTIES ('classification' = 'csv');\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"    \n",
    "    WITH knn AS (\n",
    "        SELECT \n",
    "            A.UPRN as origin,\n",
    "            B.postcode as destination,\n",
    "            round(ST_Distance(ST_GeometryFromText(A.wkt), ST_GeometryFromText(B.wkt)),2) as distance,\n",
    "            row_number() OVER (PARTITION BY A.UPRN ORDER BY A.UPRN,ST_Distance(ST_GeometryFromText(A.wkt), ST_GeometryFromText(B.wkt)) ASC, B.postcode) AS rn\n",
    "        FROM\n",
    "            open_uprn_white_horse as A,\n",
    "            code_point_open_white_horse as B\n",
    "        WHERE\n",
    "            ST_Distance(ST_GeometryFromText(A.wkt), ST_GeometryFromText(B.wkt)) <= 5000\n",
    "        ORDER BY\n",
    "            A.UPRN,\n",
    "            ST_Distance(ST_GeometryFromText(A.wkt), ST_GeometryFromText(B.wkt)) ASC,\n",
    "            destination\n",
    "    )\n",
    "    SELECT origin, destination, distance FROM knn WHERE rn = 1;    \n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "knn = pd.read_csv('athena_results.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Snowflake (x-small)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"    \n",
    "    WITH knn AS (\n",
    "        SELECT \n",
    "            A.UPRN as origin,\n",
    "            B.postcode as destination,\n",
    "            round(ST_Distance(ST_GeometryFromText(A.wkt), ST_GeometryFromText(B.wkt)),2) as distance,\n",
    "            row_number() OVER (PARTITION BY A.UPRN ORDER BY A.UPRN,ST_Distance(ST_GeometryFromText(A.wkt), ST_GeometryFromText(B.wkt)) ASC, B.postcode) AS rn\n",
    "        FROM\n",
    "            open_uprn_white_horse as A,\n",
    "            code_point_open_white_horse as B\n",
    "        WHERE\n",
    "            ST_Distance(ST_GeometryFromText(A.wkt), ST_GeometryFromText(B.wkt)) <= 5000\n",
    "        ORDER BY\n",
    "            A.UPRN,\n",
    "            ST_Distance(ST_GeometryFromText(A.wkt), ST_GeometryFromText(B.wkt)) ASC,\n",
    "            destination\n",
    "    )\n",
    "    SELECT origin, destination, distance FROM knn WHERE rn = 1;    \n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "knn = pd.read_csv('snowflake_result.csv').rename(columns=lambda x: x.lower())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Databricks (Standard_DS3_v2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "knn = pd.read_csv('part-00000-tid-83222403381116274-4d1c858d-cce3-41a1-a392-7cb7afb59909-633-1-c000.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>origin</th>\n",
       "      <th>destination_x</th>\n",
       "      <th>distance_x</th>\n",
       "      <th>destination_y</th>\n",
       "      <th>distance_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [origin, destination_x, distance_x, destination_y, distance_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"SELECT * FROM os.knn\"\"\"\n",
    "data = pd.read_sql(sql, engine)\n",
    "\n",
    "data3 = pd.merge(data, knn, how = 'outer', on = 'origin')\n",
    "data3[data3['destination_x']!=data3['destination_y']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "results = [\n",
    "    {'test' : 'SQL distinct 16GB', 'time': pd.Timedelta('0 days 00:04:50')},\n",
    "    {'test' : 'SQL distinct 2GB', 'time': pd.Timedelta('0 days 00:04:50')},\n",
    "    {'test' : 'SQL lateral 16GB', 'time': pd.Timedelta('0 days 00:01:30')},\n",
    "    {'test' : 'SQL lateral 2GB', 'time': pd.Timedelta('0 days 00:01:30')},\n",
    "    {'test' : 'Geopandas sjoin_nearest', 'time': pd.Timedelta('0 days 00:00:01')},\n",
    "    {'test' : 'Shapely all vs all', 'time': pd.Timedelta('0 days 00:02:00')},\n",
    "    {'test' : 'Shapely strtree', 'time': pd.Timedelta('0 days 00:00:02')},\n",
    "    {'test' : 'Scikit-Learn nearest neighbour', 'time': pd.Timedelta('0 days 00:00:00.25')},\n",
    "    {'test' : 'Apache Sedona partial sql', 'time': pd.Timedelta('0 days 00:04:00')},\n",
    "    {'test' : 'Apache Sedona pure sql', 'time': pd.Timedelta('0 days 00:04:00')},\n",
    "    {'test' : 'Apache Sedona st_knn', 'time': pd.Timedelta('0 days 00:00:10')},    \n",
    "    {'test' : 'kotlin all vs all', 'time': pd.Timedelta('0 days 00:00:35')},\n",
    "    {'test' : 'kotlin strtree', 'time': pd.Timedelta('0 days 00:00:4')},\n",
    "    {'test' : 'scala strtree', 'time': pd.Timedelta('0 days 00:00:4')},\n",
    "    {'test' : 'scala all vs all', 'time': pd.Timedelta('0 days 00:00:10')},\n",
    "    {'test' : 'rust strtree', 'time': pd.Timedelta('0 days 00:00:0.4')},\n",
    "    {'test' : 'rust all vs all', 'time': pd.Timedelta('0 days 00:00:11')},\n",
    "    {'test' : 'BigQuery', 'time': pd.Timedelta('0 days 00:00:03')},\n",
    "    {'test' : 'BigQuery (Slot time consumed)', 'time': pd.Timedelta('0 days 00:05:10')},\n",
    "    {'test' : 'RedShift', 'time': pd.Timedelta('0 days 00:00:26')},\n",
    "    {'test' : 'Athena', 'time': pd.Timedelta('0 days 00:01:50')},\n",
    "    {'test' : 'Snowflake', 'time': pd.Timedelta('0 days 00:01:00')},\n",
    "    {'test' : 'Databricks pure sql', 'time': pd.Timedelta('0 days 00:01:00')}\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "tags": []
   },
   "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>test</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Scikit-Learn nearest neighbour</td>\n",
       "      <td>0 days 00:00:00.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rust strtree</td>\n",
       "      <td>0 days 00:00:00.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Geopandas sjoin_nearest</td>\n",
       "      <td>0 days 00:00:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shapely strtree</td>\n",
       "      <td>0 days 00:00:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BigQuery</td>\n",
       "      <td>0 days 00:00:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>scala strtree</td>\n",
       "      <td>0 days 00:00:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>kotlin strtree</td>\n",
       "      <td>0 days 00:00:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>scala all vs all</td>\n",
       "      <td>0 days 00:00:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Apache Sedona st_knn</td>\n",
       "      <td>0 days 00:00:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>rust all vs all</td>\n",
       "      <td>0 days 00:00:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>RedShift</td>\n",
       "      <td>0 days 00:00:26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>kotlin all vs all</td>\n",
       "      <td>0 days 00:00:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Databricks</td>\n",
       "      <td>0 days 00:01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Snowflake</td>\n",
       "      <td>0 days 00:01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>SQL lateral 2GB</td>\n",
       "      <td>0 days 00:01:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>SQL lateral 16GB</td>\n",
       "      <td>0 days 00:01:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Athena</td>\n",
       "      <td>0 days 00:01:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Shapely all vs all</td>\n",
       "      <td>0 days 00:02:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Apache Sedona pure sql</td>\n",
       "      <td>0 days 00:04:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Apache Sedona partial sql</td>\n",
       "      <td>0 days 00:04:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>SQL distinct 2GB</td>\n",
       "      <td>0 days 00:04:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>SQL distinct 16GB</td>\n",
       "      <td>0 days 00:04:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>BigQuery (Slot time consumed)</td>\n",
       "      <td>0 days 00:05:10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              test                   time\n",
       "0   Scikit-Learn nearest neighbour 0 days 00:00:00.250000\n",
       "1                     rust strtree 0 days 00:00:00.400000\n",
       "2          Geopandas sjoin_nearest        0 days 00:00:01\n",
       "3                  Shapely strtree        0 days 00:00:02\n",
       "4                         BigQuery        0 days 00:00:03\n",
       "5                    scala strtree        0 days 00:00:04\n",
       "6                   kotlin strtree        0 days 00:00:04\n",
       "7                 scala all vs all        0 days 00:00:10\n",
       "8             Apache Sedona st_knn        0 days 00:00:10\n",
       "9                  rust all vs all        0 days 00:00:11\n",
       "10                        RedShift        0 days 00:00:26\n",
       "11               kotlin all vs all        0 days 00:00:35\n",
       "12                      Databricks        0 days 00:01:00\n",
       "13                       Snowflake        0 days 00:01:00\n",
       "14                 SQL lateral 2GB        0 days 00:01:30\n",
       "15                SQL lateral 16GB        0 days 00:01:30\n",
       "16                          Athena        0 days 00:01:50\n",
       "17              Shapely all vs all        0 days 00:02:00\n",
       "18          Apache Sedona pure sql        0 days 00:04:00\n",
       "19       Apache Sedona partial sql        0 days 00:04:00\n",
       "20                SQL distinct 2GB        0 days 00:04:50\n",
       "21               SQL distinct 16GB        0 days 00:04:50\n",
       "22   BigQuery (Slot time consumed)        0 days 00:05:10"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(results).sort_values('time').drop_duplicates().reset_index(drop = True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "spatial_knn-oihhALkR",
   "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.12.3"
  },
  "toc-autonumbering": true,
  "toc-showcode": false,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}