{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-09-15T05:11:36.222965Z",
     "iopub.status.busy": "2022-09-15T05:11:36.222553Z",
     "iopub.status.idle": "2022-09-15T05:11:37.711425Z",
     "shell.execute_reply": "2022-09-15T05:11:37.710585Z"
    }
   },
   "outputs": [],
   "source": [
    "import starepandas\n",
    "import geopandas\n",
    "import sqlalchemy\n",
    "import pandas\n",
    "import numpy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Prep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-09-15T05:11:37.714815Z",
     "iopub.status.busy": "2022-09-15T05:11:37.714143Z",
     "iopub.status.idle": "2022-09-15T05:11:37.727553Z",
     "shell.execute_reply": "2022-09-15T05:11:37.726795Z"
    }
   },
   "outputs": [],
   "source": [
    "cities = ['Buenos Aires', 'Brasilia', 'Santiago', \n",
    "          'Bogota', 'Caracas', 'Sao Paulo', 'Bridgetown']\n",
    "\n",
    "latitudes = [-34.58, -15.78, -33.45, 4.60, 10.48, -23.55, 13.1]\n",
    "longitudes = [-58.66, -47.91, -70.66, -74.08, -66.86, -46.63, -59.62]\n",
    "data =  {'City': cities, \n",
    "         'Latitude': latitudes, 'Longitude': longitudes}\n",
    "\n",
    "cities = starepandas.STAREDataFrame(data)\n",
    "sids = starepandas.sids_from_xy(cities.Longitude, cities.Latitude, level=27)\n",
    "cities.set_sids(sids, inplace=True)\n",
    "geom = geopandas.points_from_xy(cities.Longitude, cities.Latitude, crs='EPSG:4326')\n",
    "cities.set_geometry(geom, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# To SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-09-15T05:11:37.739722Z",
     "iopub.status.busy": "2022-09-15T05:11:37.738932Z",
     "iopub.status.idle": "2022-09-15T05:11:37.813204Z",
     "shell.execute_reply": "2022-09-15T05:11:37.812210Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_path = '' # Empty string for inmemory\n",
    "uri = 'sqlite:///{db_path}'.format(db_path=db_path)\n",
    "engine = sqlalchemy.create_engine(uri)\n",
    "\n",
    "cities = geopandas.io.sql._convert_to_ewkb(cities, 'geometry', 4326)\n",
    "cities.to_sql(name='cities', con=engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# From SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-09-15T05:11:37.817817Z",
     "iopub.status.busy": "2022-09-15T05:11:37.817307Z",
     "iopub.status.idle": "2022-09-15T05:11:37.850594Z",
     "shell.execute_reply": "2022-09-15T05:11:37.849552Z"
    }
   },
   "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>index</th>\n",
       "      <th>City</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>sids</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Buenos Aires</td>\n",
       "      <td>-34.58</td>\n",
       "      <td>-58.66</td>\n",
       "      <td>2663379193440875387</td>\n",
       "      <td>0101000020E610000014AE47E17A544DC00AD7A3703D4A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Brasilia</td>\n",
       "      <td>-15.78</td>\n",
       "      <td>-47.91</td>\n",
       "      <td>2867415364672350651</td>\n",
       "      <td>0101000020E610000014AE47E17AF447C08FC2F5285C8F...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Santiago</td>\n",
       "      <td>-33.45</td>\n",
       "      <td>-70.66</td>\n",
       "      <td>2723774768829278555</td>\n",
       "      <td>0101000020E61000000AD7A3703DAA51C09A99999999B9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Bogota</td>\n",
       "      <td>4.60</td>\n",
       "      <td>-74.08</td>\n",
       "      <td>2667981979956219515</td>\n",
       "      <td>0101000020E610000085EB51B81E8552C0666666666666...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Caracas</td>\n",
       "      <td>10.48</td>\n",
       "      <td>-66.86</td>\n",
       "      <td>2494081632617553403</td>\n",
       "      <td>0101000020E6100000D7A3703D0AB750C0F6285C8FC2F5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>Sao Paulo</td>\n",
       "      <td>-23.55</td>\n",
       "      <td>-46.63</td>\n",
       "      <td>2803225788975740475</td>\n",
       "      <td>0101000020E6100000713D0AD7A35047C0CDCCCCCCCC8C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>Bridgetown</td>\n",
       "      <td>13.10</td>\n",
       "      <td>-59.62</td>\n",
       "      <td>2518254660685127707</td>\n",
       "      <td>0101000020E61000008FC2F5285CCF4DC0333333333333...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index          City  Latitude  Longitude                 sids  \\\n",
       "0      0  Buenos Aires    -34.58     -58.66  2663379193440875387   \n",
       "1      1      Brasilia    -15.78     -47.91  2867415364672350651   \n",
       "2      2      Santiago    -33.45     -70.66  2723774768829278555   \n",
       "3      3        Bogota      4.60     -74.08  2667981979956219515   \n",
       "4      4       Caracas     10.48     -66.86  2494081632617553403   \n",
       "5      5     Sao Paulo    -23.55     -46.63  2803225788975740475   \n",
       "6      6    Bridgetown     13.10     -59.62  2518254660685127707   \n",
       "\n",
       "                                            geometry  \n",
       "0  0101000020E610000014AE47E17A544DC00AD7A3703D4A...  \n",
       "1  0101000020E610000014AE47E17AF447C08FC2F5285C8F...  \n",
       "2  0101000020E61000000AD7A3703DAA51C09A99999999B9...  \n",
       "3  0101000020E610000085EB51B81E8552C0666666666666...  \n",
       "4  0101000020E6100000D7A3703D0AB750C0F6285C8FC2F5...  \n",
       "5  0101000020E6100000713D0AD7A35047C0CDCCCCCCCC8C...  \n",
       "6  0101000020E61000008FC2F5285CCF4DC0333333333333...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pandas.read_sql_table('cities', con=engine)\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}