{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a4fe3691-3845-4f4f-9564-baf581b78782",
   "metadata": {},
   "source": [
    "# Reading data from spatial databases\n",
    "\n",
    "## Databases\n",
    "\n",
    "Example syntax for reading and writing data from/to databases. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5fd92c9",
   "metadata": {},
   "source": [
    "### Read PostGIS database using psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8f593ef-8484-4194-b1ab-81b5ba2bd2e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import psycopg2\n",
    "\n",
    "# Create connection to database with psycopg2 module (update params according your db)\n",
    "conn, cursor = psycopg2.connect(\n",
    "    dbname=\"my_postgis_database\",\n",
    "    user=\"my_usrname\",\n",
    "    password=\"my_pwd\",\n",
    "    host=\"123.22.432.16\",\n",
    "    port=5432,\n",
    ")\n",
    "\n",
    "# Specify sql query\n",
    "sql = \"SELECT * FROM MY_TABLE;\"\n",
    "\n",
    "# Read data from PostGIS\n",
    "data = gpd.read_postgis(sql=sql, con=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad87ecbb",
   "metadata": {},
   "source": [
    "### Read / write PostGIS database using SqlAlchemy + GeoAlchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50a4c127",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.engine.url import URL\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import MetaData\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from geoalchemy2 import WKTElement, Geometry\n",
    "\n",
    "# Update with your db parameters\n",
    "HOST = \"123.234.345.16\"\n",
    "DB = \"my_database\"\n",
    "USER = \"my_user\"\n",
    "PORT = 5432\n",
    "PWD = \"my_password\"\n",
    "\n",
    "# Database info\n",
    "db_url = URL(\n",
    "    drivername=\"postgresql+psycopg2\",\n",
    "    host=HOST,\n",
    "    database=DB,\n",
    "    username=USER,\n",
    "    port=PORT,\n",
    "    password=PWD,\n",
    ")\n",
    "\n",
    "# Create engine\n",
    "engine = create_engine(db_url)\n",
    "\n",
    "# Init Metadata\n",
    "meta = MetaData()\n",
    "\n",
    "# Load table definitions from db\n",
    "meta.reflect(engine)\n",
    "\n",
    "# Create session\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "# ========================\n",
    "# Read data from PostGIS\n",
    "# ========================\n",
    "\n",
    "# Specify sql query\n",
    "sql = \"SELECT * FROM finland;\"\n",
    "\n",
    "# Pull the data\n",
    "data = gpd.read_postgis(sql=sql, con=engine)\n",
    "\n",
    "# Close session\n",
    "session.close()\n",
    "\n",
    "# =========================================\n",
    "# Write data to PostGIS (make a copy table)\n",
    "# =========================================\n",
    "\n",
    "# Coordinate Reference System (srid)\n",
    "crs = 4326\n",
    "\n",
    "# Target table\n",
    "target_table = \"finland_copy\"\n",
    "\n",
    "# Convert Shapely geometries to WKTElements into column 'geom' (default in PostGIS)\n",
    "data[\"geom\"] = data[\"geometry\"].apply(lambda row: WKTElement(row.wkt, srid=crs))\n",
    "\n",
    "# Drop Shapely geometries\n",
    "data = data.drop(\"geometry\", axis=1)\n",
    "\n",
    "# Write to PostGIS (overwrite if table exists, be careful with this! )\n",
    "# Possible behavior: 'replace', 'append', 'fail'\n",
    "\n",
    "data.to_sql(target_table, engine, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6d98349",
   "metadata": {},
   "source": [
    "### Read / write Spatialite database "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4368224",
   "metadata": {},
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import sqlite3\n",
    "import shapely.wkb as swkb\n",
    "from sqlalchemy import create_engine, event\n",
    "\n",
    "# DB path\n",
    "dbfp = \"L2_data/Finland.sqlite\"\n",
    "\n",
    "# Name for the table\n",
    "tbl_name = \"finland\"\n",
    "\n",
    "# SRID (crs of your data)\n",
    "srid = 4326\n",
    "\n",
    "# Parse Geometry type of the input Data\n",
    "gtype = data.geom_type.unique()\n",
    "assert len(gtype) == 1, \"Mixed Geometries! Cannot insert into SQLite table.\"\n",
    "geom_type = gtype[0].upper()\n",
    "\n",
    "# Initialize database engine\n",
    "engine = create_engine(\"sqlite:///{db}\".format(db=dbfp), module=sqlite)\n",
    "\n",
    "# Initialize table without geometries\n",
    "geo = data.drop([\"geometry\"], axis=1)\n",
    "\n",
    "with sqlite3.connect(dbfp) as conn:\n",
    "    geo.to_sql(tbl_name, conn, if_exists=\"replace\", index=False)\n",
    "\n",
    "# Enable spatialite extension\n",
    "with sqlite3.connect(dbfp) as conn:\n",
    "    conn.enable_load_extension(True)\n",
    "    conn.load_extension(\"mod_spatialite\")\n",
    "    conn.execute(\"SELECT InitSpatialMetaData(1);\")\n",
    "    # Add geometry column with specified CRS with defined geometry typehaving two dimensions\n",
    "    conn.execute(\n",
    "        \"SELECT AddGeometryColumn({table}, 'wkb_geometry',\\\n",
    "        {srid}, {geom_type}, 2);\".format(\n",
    "            table=tbl_name, srid=srid, geom_type=geom_type\n",
    "        )\n",
    "    )\n",
    "\n",
    "# Convert Shapely geometries into well-known-binary format\n",
    "data[\"geometry\"] = data[\"geometry\"].apply(lambda geom: swkb.dumps(geom))\n",
    "\n",
    "# Push to database (overwrite if table exists)\n",
    "data.to_sql(tbl_name, engine, if_exists=\"replace\", index=False)"
   ]
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}