bench_sjoin.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "485855e6-24f7-467b-8dfb-a41978dd8623",
   "metadata": {},
   "outputs": [],
   "source": [
    "import geopandas\n",
    "import pyogrio"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "277453b7-9f07-455c-a80a-9e5ca396c6f5",
   "metadata": {},
   "source": [
    "## OK Ordnance Survey point and polygon data\n",
    "\n",
    "Joining 1.7M points with a few polygons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b3b82762-6caf-42c1-ab22-d3714ebc12d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "polygons = pyogrio.read_dataframe(\n",
    "    \"data/raw/bdline_gpkg_gb/Data/bdline_gb.gpkg\",\n",
    "    layer=\"boundary_line_ceremonial_counties\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d0f4264d-ae4c-426a-8535-e84789d2e1ad",
   "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>Name</th>\n",
       "      <th>Area_Description</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bedfordshire</td>\n",
       "      <td>Ceremonial County</td>\n",
       "      <td>MULTIPOLYGON (((527179.004 248224.996, 527162....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Berkshire</td>\n",
       "      <td>Ceremonial County</td>\n",
       "      <td>MULTIPOLYGON (((504919.202 178392.095, 504918....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Buckinghamshire</td>\n",
       "      <td>Ceremonial County</td>\n",
       "      <td>MULTIPOLYGON (((505704.8 185453.304, 505704.8 ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cambridgeshire</td>\n",
       "      <td>Ceremonial County</td>\n",
       "      <td>MULTIPOLYGON (((571833.397 261707.896, 571813....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cheshire</td>\n",
       "      <td>Ceremonial County</td>\n",
       "      <td>MULTIPOLYGON (((401780.298 370227.103, 401778....</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Name   Area_Description  \\\n",
       "0     Bedfordshire  Ceremonial County   \n",
       "1        Berkshire  Ceremonial County   \n",
       "2  Buckinghamshire  Ceremonial County   \n",
       "3   Cambridgeshire  Ceremonial County   \n",
       "4         Cheshire  Ceremonial County   \n",
       "\n",
       "                                            geometry  \n",
       "0  MULTIPOLYGON (((527179.004 248224.996, 527162....  \n",
       "1  MULTIPOLYGON (((504919.202 178392.095, 504918....  \n",
       "2  MULTIPOLYGON (((505704.8 185453.304, 505704.8 ...  \n",
       "3  MULTIPOLYGON (((571833.397 261707.896, 571813....  \n",
       "4  MULTIPOLYGON (((401780.298 370227.103, 401778....  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "polygons.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "15dd5e1f-9ca1-46be-a473-30ad80ebfd2d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "91"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(polygons)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e84d76a0-8b7e-4b7e-a353-f3b5fef2230e",
   "metadata": {},
   "outputs": [],
   "source": [
    "points = pyogrio.read_dataframe(\n",
    "    'data/raw/codepo_gpkg_gb/Data/codepo_gb.gpkg',\n",
    "    columns=[\"postcode\", \"positional_quality_indicator\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "83e6b48e-abea-4485-b121-c13f8eea51c2",
   "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>positional_quality_indicator</th>\n",
       "      <th>postcode</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>AL1 1AG</td>\n",
       "      <td>POINT (515487 206498)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>AL1 1AJ</td>\n",
       "      <td>POINT (515491 206410)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>AL1 1AR</td>\n",
       "      <td>POINT (516270 205897)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "      <td>AL1 1AS</td>\n",
       "      <td>POINT (515005 206908)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>AL1 1AT</td>\n",
       "      <td>POINT (516131 206148)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   positional_quality_indicator postcode               geometry\n",
       "0                            10  AL1 1AG  POINT (515487 206498)\n",
       "1                            10  AL1 1AJ  POINT (515491 206410)\n",
       "2                            10  AL1 1AR  POINT (516270 205897)\n",
       "3                            10  AL1 1AS  POINT (515005 206908)\n",
       "4                            10  AL1 1AT  POINT (516131 206148)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "points.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f2d5ca68-2e2c-4182-8f00-58b2746d502b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1738225"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(points)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "43b77b86-e3a0-477c-93be-307b63d23990",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 330 ms, sys: 55.1 ms, total: 385 ms\n",
      "Wall time: 384 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<geopandas.sindex.SpatialIndex at 0x7f901649cc90>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "points.sindex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d4ea413f-fdb3-43b9-a186-c103f3f32572",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined = geopandas.sjoin(points, polygons, predicate=\"within\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ce8ea787-85b6-4a76-8f9f-b12dc524ac15",
   "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>positional_quality_indicator</th>\n",
       "      <th>postcode</th>\n",
       "      <th>geometry</th>\n",
       "      <th>index_right</th>\n",
       "      <th>Name</th>\n",
       "      <th>Area_Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1439045</th>\n",
       "      <td>10</td>\n",
       "      <td>SG8 0LF</td>\n",
       "      <td>POINT (526697 245067)</td>\n",
       "      <td>0</td>\n",
       "      <td>Bedfordshire</td>\n",
       "      <td>Ceremonial County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1414287</th>\n",
       "      <td>10</td>\n",
       "      <td>SG19 2HE</td>\n",
       "      <td>POINT (526620 247335)</td>\n",
       "      <td>0</td>\n",
       "      <td>Bedfordshire</td>\n",
       "      <td>Ceremonial County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1408408</th>\n",
       "      <td>10</td>\n",
       "      <td>SG18 8RR</td>\n",
       "      <td>POINT (523762 244268)</td>\n",
       "      <td>0</td>\n",
       "      <td>Bedfordshire</td>\n",
       "      <td>Ceremonial County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1408422</th>\n",
       "      <td>10</td>\n",
       "      <td>SG18 8SJ</td>\n",
       "      <td>POINT (523806 244303)</td>\n",
       "      <td>0</td>\n",
       "      <td>Bedfordshire</td>\n",
       "      <td>Ceremonial County</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1408409</th>\n",
       "      <td>10</td>\n",
       "      <td>SG18 8RS</td>\n",
       "      <td>POINT (523763 244343)</td>\n",
       "      <td>0</td>\n",
       "      <td>Bedfordshire</td>\n",
       "      <td>Ceremonial County</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         positional_quality_indicator  postcode               geometry  \\\n",
       "1439045                            10   SG8 0LF  POINT (526697 245067)   \n",
       "1414287                            10  SG19 2HE  POINT (526620 247335)   \n",
       "1408408                            10  SG18 8RR  POINT (523762 244268)   \n",
       "1408422                            10  SG18 8SJ  POINT (523806 244303)   \n",
       "1408409                            10  SG18 8RS  POINT (523763 244343)   \n",
       "\n",
       "         index_right          Name   Area_Description  \n",
       "1439045            0  Bedfordshire  Ceremonial County  \n",
       "1414287            0  Bedfordshire  Ceremonial County  \n",
       "1408408            0  Bedfordshire  Ceremonial County  \n",
       "1408422            0  Bedfordshire  Ceremonial County  \n",
       "1408409            0  Bedfordshire  Ceremonial County  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1ff59bba-bbcd-49f8-857a-32ed2f2cd755",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "980 ms ± 26.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "joined = geopandas.sjoin(points, polygons, predicate=\"within\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0abdaca9-3563-4a32-8a5b-5a39081d31f5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%snakeviz\n",
    "joined = geopandas.sjoin(points, polygons, predicate=\"within\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}