ctab-postgis-demo.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PostGIS on Greenplum Database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ne_10m_admin_0_countries\n",
    "Simple layer of countries at the Admin 0 level. Column geom contains the geometry, and name the common name of the country. Also includes various other data fields such as population, abbreviations, GDP estimates, and names in various other languages. More information: https://www.naturalearthdata.com/downloads/10m-cultural-vectors/10m-admin-0-countries/\n",
    "\n",
    "#### ne_10m_admin_1_states_provinces\n",
    "Contains level 1 adminitrative subdivisions such as states and provinces. Column geom contains the geometry and name contains the name of the division. Various other fields are also available. More information: https://www.naturalearthdata.com/downloads/10m-cultural-vectors/10m-admin-1-states-provinces/\n",
    "\n",
    "#### ne_10m_populated_places\n",
    "Contains point locations of populated places. Column geom contains the geometry, and name contains the name of the place. Various other fields also included. More information: https://www.naturalearthdata.com/downloads/10m-cultural-vectors/10m-populated-places/\n",
    "\n",
    "#### ne_10m_lakes\n",
    "Contains global lakes and reservoirs, including the Europe and North America supplements. Column geom contains the geometry, featurecla is a feature class to differentiate between lakes and reservoirs, and name contains the name of the feature. More information: https://www.naturalearthdata.com/downloads/10m-physical-vectors/10m-lakes/\n",
    "\n",
    "#### ne_10m_rivers\n",
    "Contains global rivers and lakes centerlines, including the Europe and North America supplements. Column geom contains the geometry, and name contains the common name of the feature. More information: https://www.naturalearthdata.com/downloads/10m-physical-vectors/10m-rivers-lake-centerlines/\n",
    "\n",
    "#### ne_10m_airports\n",
    "Airport information derives from [Mile High Club](https://github.com/nvkelso/mile-high-club), a detailed GIS compilation of world wide airports that is in the public domain. Column geom contains the geometry, and name contains the common name of the feature. More information: https://www.naturalearthdata.com/downloads/10m-cultural-vectors/airports/\n",
    "\n",
    "#### ne_10m_time_zones\n",
    "Time zones primarily derive from the Central Intelligence Agency map of Time Zones, downloaded from the World Factbook website May 2012. Boundaries were adjusted to fit the Natural Earth line work at a scale of 1:10 million and to follow twelve nautical mile territorial sea boundary lines when running along coasts. More information: https://www.naturalearthdata.com/downloads/10m-cultural-vectors/timezones/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Workflow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os, re\n",
    "from IPython.display import display_html, display_markdown\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "CONNECTION_STRING = os.getenv('AWSGPDBCONN')\n",
    "\n",
    "cs = re.match('^postgresql:\\/\\/(\\S+):(\\S+)@(\\S+):(\\S+)\\/(\\S+)$', CONNECTION_STRING)\n",
    "\n",
    "DB_USER   = cs.group(1)\n",
    "DB_PWD    = cs.group(2)\n",
    "DB_SERVER = cs.group(3)\n",
    "DB_PORT   = cs.group(4)\n",
    "DB_NAME   = cs.group(5)\n",
    "con = CONNECTION_STRING \n",
    "\n",
    "%reload_ext sql\n",
    "%sql $CONNECTION_STRING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>version_info</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PostgreSQL 9.4.24 (Greenplum Database 6.12.0 build commit:4c176763c7619fb678ce38095e6b3e8fb9548186) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct 28 2020 19:42:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>POSTGIS=&quot;2.5.4&quot; [EXTENSION] PGSQL=&quot;94&quot; GEOS=&quot;3.4.2-CAPI-1.8.2 r3921&quot; PROJ=&quot;Rel. 4.8.0, 6 March 2012&quot; GDAL=&quot;GDAL 1.11.1, released 2014/09/24&quot; LIBXML=&quot;2.9.1&quot; LIBJSON=&quot;0.12&quot; RASTER</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('PostgreSQL 9.4.24 (Greenplum Database 6.12.0 build commit:4c176763c7619fb678ce38095e6b3e8fb9548186) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct 28 2020 19:42:15',),\n",
       " ('POSTGIS=\"2.5.4\" [EXTENSION] PGSQL=\"94\" GEOS=\"3.4.2-CAPI-1.8.2 r3921\" PROJ=\"Rel. 4.8.0, 6 March 2012\" GDAL=\"GDAL 1.11.1, released 2014/09/24\" LIBXML=\"2.9.1\" LIBJSON=\"0.12\" RASTER',)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $DB_USER@$DB_SERVER\n",
    "SELECT UNNEST(ARRAY[version, postgis_full_version]) version_info FROM (SELECT version()) A, (SELECT postgis_full_version()) B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def display_gist_url(url):\n",
    "    gist = re.match('^http:\\/\\/geojson.io\\/#id=gist:\\/(\\S+)$', url)\n",
    "    gist = 'https://gist.github.com/cantzakas/' + gist.group(1)\n",
    "    return display_markdown(gist, raw=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### How to download and load the data into the database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```sh\n",
    "wget -P ~/tmp_files/ https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_admin_0_countries.zip\n",
    "wget -P ~/tmp_files/ https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_admin_1_states_provinces.zip\n",
    "wget -P ~/tmp_files/ https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_populated_places_simple.zip\n",
    "wget -P ~/tmp_files/ https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/physical/ne_10m_lakes.zip\n",
    "wget -P ~/tmp_files/ https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/physical/ne_10m_rivers_lake_centerlines.zip\n",
    "wget -P ~/tmp_files/ https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_airports.zip\n",
    "wget -P ~/tmp_files/ https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_time_zones.zip\n",
    "\n",
    "unzip ~/tmp_files/ne_10m_admin_0_countries.zip -d ~/tmp_files/\n",
    "unzip ~/tmp_files/ne_10m_admin_1_states_provinces.zip -d ~/tmp_files/\n",
    "unzip ~/tmp_files/ne_10m_populated_places_simple.zip -d ~/tmp_files/\n",
    "unzip ~/tmp_files/ne_10m_lakes.zip -d ~/tmp_files/\n",
    "unzip ~/tmp_files/ne_10m_rivers_lake_centerlines.zip -d ~/tmp_files/\n",
    "unzip ~/tmp_files/ne_10m_airports.zip -d ~/tmp_files/\n",
    "unzip ~/tmp_files/ne_10m_time_zones.zip -d ~/tmp_files/\n",
    "\n",
    "/usr/local/greenplum-db/bin/shp2pgsql -d -D -s 900913 -i ~/tmp_files/ne_10m_admin_0_countries.shx ne_10m_admin_0_countries dev | psql -d dev\n",
    "/usr/local/greenplum-db/bin/shp2pgsql -d -D -s 900913 -i ~/tmp_files/ne_10m_admin_1_states_provinces.shx ne_10m_admin_1_states_provinces dev | psql -d dev\n",
    "/usr/local/greenplum-db/bin/shp2pgsql -d -D -s 900913 -i ~/tmp_files/ne_10m_populated_places_simple.shx ne_10m_populated_places_simple dev | psql -d dev\n",
    "/usr/local/greenplum-db/bin/shp2pgsql -d -D -s 900913 -i ~/tmp_files/ne_10m_lakes.shx ne_10m_lakes dev | psql -d dev\n",
    "/usr/local/greenplum-db/bin/shp2pgsql -d -D -s 900913 -i ~/tmp_files/ne_10m_rivers_lake_centerlines.shx ne_10m_rivers_lake_centerlines dev | psql -d dev\n",
    "/usr/local/greenplum-db/bin/shp2pgsql -d -D -s 900913 -i ~/tmp_files/ne_10m_airports.shx ne_10m_airports dev | psql -d dev\n",
    "/usr/local/greenplum-db/bin/shp2pgsql -d -D -s 900913 -i ~/tmp_files/ne_10m_time_zones.shx ne_10m_time_zones dev | psql -d dev\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Notes on shp2pgsql utility\n",
    "\n",
    "USAGE: **shp2pgsql** [OPTIONS] shapefile [schema.]table, where _[OPTIONS]_:\n",
    "\n",
    "```-d``` Drops the table, then recreates it and populates it with current shape file data.\n",
    "\n",
    "```-D``` Use postgresql dump format (defaults to sql insert statements).\n",
    "\n",
    "```-s from_srid:to_srid``` If `-s :to_srid` is not specified, then `from_srid` is assumed and no transformation happens.\n",
    "\n",
    "```-i``` Use `int4` type for all integer dbf fields.    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [PostGIS.ST_WithIn(geometry A, geometry B)](https://postgis.net/docs/ST_Within.html): returns true if geometry A is completely inside geometry B"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find all airports in the United Kingdom"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>geom</th>\n",
       "        <th>name_en</th>\n",
       "        <th>abbrev</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>010100002031BF0D0017467205AB8EFABF22E8C6133EEF4A40</td>\n",
       "        <td>Leeds Bradford International Airport</td>\n",
       "        <td>LBA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>010100002031BF0D00FF0F778A935DFBBF1A295C32BF844B40</td>\n",
       "        <td>Newcastle Airport</td>\n",
       "        <td>NCL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>010100002031BF0D0068FD0AD46FB70AC0605F760C06B34940</td>\n",
       "        <td>Cardiff Airport</td>\n",
       "        <td>CWL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>010100002031BF0D0056A24D4FEDDBC4BF6ED455D6EF934940</td>\n",
       "        <td>Gatwick Airport</td>\n",
       "        <td>LGW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>010100002031BF0D00B7E924C28400DDBFE98FCE9749BC4940</td>\n",
       "        <td>Heathrow Airport</td>\n",
       "        <td>LHR</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('010100002031BF0D0017467205AB8EFABF22E8C6133EEF4A40', 'Leeds Bradford International Airport', 'LBA'),\n",
       " ('010100002031BF0D00FF0F778A935DFBBF1A295C32BF844B40', 'Newcastle Airport', 'NCL'),\n",
       " ('010100002031BF0D0068FD0AD46FB70AC0605F760C06B34940', 'Cardiff Airport', 'CWL'),\n",
       " ('010100002031BF0D0056A24D4FEDDBC4BF6ED455D6EF934940', 'Gatwick Airport', 'LGW'),\n",
       " ('010100002031BF0D00B7E924C28400DDBFE98FCE9749BC4940', 'Heathrow Airport', 'LHR')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT airports.geom\n",
    "    , airports.name_en\n",
    "    , airports.abbrev\n",
    "FROM public.ne_10m_airports airports\n",
    "    , public.ne_10m_admin_0_countries countries \n",
    "WHERE \n",
    "    ST_WithIn(airports.geom, countries.geom)\n",
    "    AND countries.name = 'United Kingdom'\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "http://geojson.io/#id=gist:/92940e9dd363a3e32a528793ced2ffc6"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "https://gist.github.com/cantzakas/92940e9dd363a3e32a528793ced2ffc6"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import geojsonio\n",
    "import geopandas as gpd\n",
    "\n",
    "sql = \"\"\"\n",
    "SELECT airports.geom\n",
    "    , airports.name_en\n",
    "    , airports.abbrev\n",
    "FROM public.ne_10m_airports airports\n",
    "    , public.ne_10m_admin_0_countries countries \n",
    "WHERE \n",
    "    ST_WithIn(airports.geom, countries.geom)\n",
    "    AND countries.name = 'United Kingdom'\n",
    "\"\"\"\n",
    "\n",
    "df = gpd.read_postgis(sql, con)\n",
    "url=geojsonio.display(df.to_json())\n",
    "\n",
    "display_markdown(url, raw=True)\n",
    "display_gist_url(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [PostGIS.ST_Intersects(geometry A, geometry B)](https://postgis.net/docs/ST_Intersects.html): returns true if two Geometries/Geography spatially intersect in 2D (have at least one point in common)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find all rivers in the United States of America"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>name_en</th>\n",
       "        <th>?column?</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Stanislaus</td>\n",
       "        <td>Stanislaus</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[-119.668039517312,38.3777123067649],[-119.677072720437,38.3838972025982],[-119.685454881895,38.3927676453065],[-119.688465949604,38.399481512494],[-119.6937...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iliamna Lake Outlet</td>\n",
       "        <td>Iliamna Lake Outlet</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[-155.885487433979,59.344305731244],[-155.969349738666,59.3107363953065],[-155.99819902252,59.306708074994],[-156.093251105854,59.311590887494],[-156.1510310...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eel</td>\n",
       "        <td>Eel</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[-124.069162564187,40.4793561869732],[-124.082427538145,40.4828555359315],[-124.096424933979,40.4944929057232],[-124.083729621479,40.5088972025982],[-124.081...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Suwannee</td>\n",
       "        <td>Suwannee</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[-82.7207738923121,31.163397528119],[-82.6678767568954,31.1203880880149],[-82.6436254548121,31.0983340515565],[-82.6310115225204,31.0905215515565],[-82.61754...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cedar</td>\n",
       "        <td>Cedar</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[-93.2110082673121,43.5586205098899],[-93.1777237621037,43.5497500671815],[-93.1549373037704,43.5242780619732],[-93.1216527985621,43.4704857442649],[-93.0763...</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Stanislaus', 'Stanislaus', '{\"type\":\"MultiLineString\",\"coordinates\":[[[-119.668039517312,38.3777123067649],[-119.677072720437,38.3838972025982],[-119.685454881895,38.3927676453065],[-119.688465949604,38.399481512494],[-119.6937...'),\n",
       " ('Iliamna Lake Outlet', 'Iliamna Lake Outlet', '{\"type\":\"MultiLineString\",\"coordinates\":[[[-155.885487433979,59.344305731244],[-155.969349738666,59.3107363953065],[-155.99819902252,59.306708074994],[-156.093251105854,59.311590887494],[-156.1510310...'),\n",
       " ('Eel', 'Eel', '{\"type\":\"MultiLineString\",\"coordinates\":[[[-124.069162564187,40.4793561869732],[-124.082427538145,40.4828555359315],[-124.096424933979,40.4944929057232],[-124.083729621479,40.5088972025982],[-124.081...'),\n",
       " ('Suwannee', 'Suwannee', '{\"type\":\"MultiLineString\",\"coordinates\":[[[-82.7207738923121,31.163397528119],[-82.6678767568954,31.1203880880149],[-82.6436254548121,31.0983340515565],[-82.6310115225204,31.0905215515565],[-82.61754...'),\n",
       " ('Cedar', 'Cedar', '{\"type\":\"MultiLineString\",\"coordinates\":[[[-93.2110082673121,43.5586205098899],[-93.1777237621037,43.5497500671815],[-93.1549373037704,43.5242780619732],[-93.1216527985621,43.4704857442649],[-93.0763...')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT rivers.name\n",
    "    , rivers.name_en\n",
    "    , SUBSTRING(ST_AsGeoJSON(rivers.geom), 0, 200) || '...'\n",
    "FROM \n",
    "    public.ne_10m_admin_0_countries AS countries, \n",
    "    public.ne_10m_rivers_lake_centerlines AS rivers\n",
    "WHERE \n",
    "    countries.name = 'United States of America' \n",
    "    AND rivers.featurecla = 'River'\n",
    "    AND rivers.name != '' \n",
    "    AND ST_Intersects(countries.geom, rivers.geom)\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "http://geojson.io/#id=gist:/b128e4a05a5ad2d12a2726112946cdb1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "https://gist.github.com/cantzakas/b128e4a05a5ad2d12a2726112946cdb1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "SELECT rivers.name\n",
    "    , rivers.name_en\n",
    "    , rivers.geom\n",
    "FROM \n",
    "    public.ne_10m_admin_0_countries AS countries, \n",
    "    public.ne_10m_rivers_lake_centerlines AS rivers\n",
    "WHERE \n",
    "    countries.name = 'United States of America' \n",
    "    AND rivers.featurecla = 'River'\n",
    "    AND rivers.name != '' \n",
    "    AND ST_Intersects(\n",
    "        countries.geom,\n",
    "        rivers.geom\n",
    "    )\n",
    "\"\"\"\n",
    "df = gpd.read_postgis(sql, con)\n",
    "url=geojsonio.display(df.to_json())\n",
    "\n",
    "display_markdown(url, raw=True)\n",
    "display_gist_url(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [PostGIS.ST_Union()](https://postgis.net/docs/ST_Union.html): merging geometries to produce a result geometry with no overlaps"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find top-10 by population capital cities in Europe, with a river running through it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "26 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>rank</th>\n",
       "        <th>st_asgeojson</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vistula</td>\n",
       "        <td>5</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[18.9369409514379,49.9319115255149],[18.9447534514379,49....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Riga</td>\n",
       "        <td>9</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[24.0999653714032,56.950023823161]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rome</td>\n",
       "        <td>3</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[12.481312562874,41.8979014850989]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paris</td>\n",
       "        <td>1</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[2.33138946713035,48.8686387898146]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tevere</td>\n",
       "        <td>3</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[12.0520125660212,43.7651634786399],[12.0378524097712,43....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belgrade</td>\n",
       "        <td>8</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[20.4660448220205,44.8205913044467]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belgrade</td>\n",
       "        <td>8</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[20.4660448220205,44.8205913044467]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Zagreb</td>\n",
       "        <td>10</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[15.9999946682457,45.8000067332725]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Seine</td>\n",
       "        <td>1</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[4.71265709727126,47.5126406921815],[4.70411217539626,47....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Soroksari Duna</td>\n",
       "        <td>6</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[19.0634057951879,47.4726830098899],[19.0739038420629,47....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Daugava</td>\n",
       "        <td>9</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[32.0775659514379,56.9077416036399],[32.0830184253962,56....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thames</td>\n",
       "        <td>2</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[-2.05077063710374,51.8420677755149],[-2.02212480377042,5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>2</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[-0.118667702475932,51.5019405883275]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Desna</td>\n",
       "        <td>4</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[33.9414168628962,54.3722598328065],[33.9795028003962,54....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dnipro</td>\n",
       "        <td>4</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[33.3906356128962,54.9171817078065],[33.3387964201879,54....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sava</td>\n",
       "        <td>8</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[15.2295028003962,46.0458438171815],[15.2469995451879,46....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sava</td>\n",
       "        <td>10</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[15.2295028003962,46.0458438171815],[15.2469995451879,46....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Warsaw</td>\n",
       "        <td>5</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[20.9980536924653,52.2519464883956]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Prague</td>\n",
       "        <td>7</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[14.4640339170485,50.0852828734783]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kiev</td>\n",
       "        <td>4</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[30.5146821104722,50.4353131876072]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kiev</td>\n",
       "        <td>4</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[30.5146821104722,50.4353131876072]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Danube</td>\n",
       "        <td>6</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[17.2064722014379,48.061102606244],[17.2429305347712,48.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Danube</td>\n",
       "        <td>8</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[17.2064722014379,48.061102606244],[17.2429305347712,48.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Budapest</td>\n",
       "        <td>6</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[19.0813748187597,47.5019521849914]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Budapest</td>\n",
       "        <td>6</td>\n",
       "        <td>{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[19.0813748187597,47.5019521849914]}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vltava</td>\n",
       "        <td>7</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[13.5785425139379,49.0132103536399],[13.6137801441462,49....</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Vistula', 5, '{\"type\":\"MultiLineString\",\"coordinates\":[[[18.9369409514379,49.9319115255149],[18.9447534514379,49....'),\n",
       " ('Riga', 9, '{\"type\":\"Point\",\"coordinates\":[24.0999653714032,56.950023823161]}'),\n",
       " ('Rome', 3, '{\"type\":\"Point\",\"coordinates\":[12.481312562874,41.8979014850989]}'),\n",
       " ('Paris', 1, '{\"type\":\"Point\",\"coordinates\":[2.33138946713035,48.8686387898146]}'),\n",
       " ('Tevere', 3, '{\"type\":\"MultiLineString\",\"coordinates\":[[[12.0520125660212,43.7651634786399],[12.0378524097712,43....'),\n",
       " ('Belgrade', 8, '{\"type\":\"Point\",\"coordinates\":[20.4660448220205,44.8205913044467]}'),\n",
       " ('Belgrade', 8, '{\"type\":\"Point\",\"coordinates\":[20.4660448220205,44.8205913044467]}'),\n",
       " ('Zagreb', 10, '{\"type\":\"Point\",\"coordinates\":[15.9999946682457,45.8000067332725]}'),\n",
       " ('Seine', 1, '{\"type\":\"MultiLineString\",\"coordinates\":[[[4.71265709727126,47.5126406921815],[4.70411217539626,47....'),\n",
       " ('Soroksari Duna', 6, '{\"type\":\"MultiLineString\",\"coordinates\":[[[19.0634057951879,47.4726830098899],[19.0739038420629,47....'),\n",
       " ('Daugava', 9, '{\"type\":\"MultiLineString\",\"coordinates\":[[[32.0775659514379,56.9077416036399],[32.0830184253962,56....'),\n",
       " ('Thames', 2, '{\"type\":\"MultiLineString\",\"coordinates\":[[[-2.05077063710374,51.8420677755149],[-2.02212480377042,5...'),\n",
       " ('London', 2, '{\"type\":\"Point\",\"coordinates\":[-0.118667702475932,51.5019405883275]}'),\n",
       " ('Desna', 4, '{\"type\":\"MultiLineString\",\"coordinates\":[[[33.9414168628962,54.3722598328065],[33.9795028003962,54....'),\n",
       " ('Dnipro', 4, '{\"type\":\"MultiLineString\",\"coordinates\":[[[33.3906356128962,54.9171817078065],[33.3387964201879,54....'),\n",
       " ('Sava', 8, '{\"type\":\"MultiLineString\",\"coordinates\":[[[15.2295028003962,46.0458438171815],[15.2469995451879,46....'),\n",
       " ('Sava', 10, '{\"type\":\"MultiLineString\",\"coordinates\":[[[15.2295028003962,46.0458438171815],[15.2469995451879,46....'),\n",
       " ('Warsaw', 5, '{\"type\":\"Point\",\"coordinates\":[20.9980536924653,52.2519464883956]}'),\n",
       " ('Prague', 7, '{\"type\":\"Point\",\"coordinates\":[14.4640339170485,50.0852828734783]}'),\n",
       " ('Kiev', 4, '{\"type\":\"Point\",\"coordinates\":[30.5146821104722,50.4353131876072]}'),\n",
       " ('Kiev', 4, '{\"type\":\"Point\",\"coordinates\":[30.5146821104722,50.4353131876072]}'),\n",
       " ('Danube', 6, '{\"type\":\"MultiLineString\",\"coordinates\":[[[17.2064722014379,48.061102606244],[17.2429305347712,48.0...'),\n",
       " ('Danube', 8, '{\"type\":\"MultiLineString\",\"coordinates\":[[[17.2064722014379,48.061102606244],[17.2429305347712,48.0...'),\n",
       " ('Budapest', 6, '{\"type\":\"Point\",\"coordinates\":[19.0813748187597,47.5019521849914]}'),\n",
       " ('Budapest', 6, '{\"type\":\"Point\",\"coordinates\":[19.0813748187597,47.5019521849914]}'),\n",
       " ('Vltava', 7, '{\"type\":\"MultiLineString\",\"coordinates\":[[[13.5785425139379,49.0132103536399],[13.6137801441462,49....')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH ranked AS (\n",
    "SELECT places.gid AS pgid\n",
    "    , rivers.gid AS rgid\n",
    "    , DENSE_RANK () OVER (ORDER BY places.pop_max DESC) AS rank\n",
    "FROM\n",
    "    public.ne_10m_populated_places_simple AS places,\n",
    "    public.ne_10m_rivers_lake_centerlines AS rivers\n",
    "WHERE \n",
    "    places.adm0cap = 1 \n",
    "    AND rivers.name_en != '' \n",
    "    AND ST_Within(places.geom,\n",
    "            (SELECT ST_Union(geom) FROM public.ne_10m_admin_0_countries WHERE continent='Europe')) \n",
    "    AND ST_Intersects(ST_Buffer(places.geom, 0.05), rivers.geom)\n",
    "GROUP BY places.gid, rivers.gid, places.pop_max)\n",
    "\n",
    "SELECT pl.name, ranked.rank, ST_AsGeoJSON(pl.geom)\n",
    "FROM ranked, public.ne_10m_populated_places_simple AS pl\n",
    "WHERE ranked.rank <= 10 AND pl.gid = ranked.pgid\n",
    "UNION ALL\n",
    "SELECT rr.name, ranked.rank, SUBSTRING(ST_AsGeoJSON(rr.geom), 0, 100) || '...'\n",
    "FROM ranked, public.ne_10m_rivers_lake_centerlines AS rr\n",
    "WHERE ranked.rank <= 10 AND rr.gid = ranked.rgid;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is happening here:\n",
    "- Use country's geometry to create a merged result, 'Continent' geometry for Europe: \n",
    "```sql\n",
    "SELECT ST_Union(geom) FROM public.ne_10m_admin_0_countries WHERE continent='Europe'\n",
    "``` \n",
    "- Returns true if 'Place' geometry is completely inside 'Continent' geometry:\n",
    "```sql\n",
    "ST_Within(places.geom,\n",
    "    (SELECT ST_Union(geom) FROM public.ne_10m_admin_0_countries WHERE continent='Europe')) \n",
    "```\n",
    "- Calculate a geometry covering all points within a given distance (0.05 units) from a 'Place' geometry:\n",
    "```sql\n",
    "ST_Buffer(places.geom, 0.05)\n",
    "```\n",
    "  See https://epsg.io/900913 for more information on EPSG:900913 - Google Maps Global (Spherical) Mercator\n",
    "- Returns true if two Geometries/Geography spatially intersect in 2D (have at least one point in common):\n",
    "```sql\n",
    "AND ST_Intersects(ST_Buffer(places.geom, 0.05), rivers.geom)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "http://geojson.io/#id=gist:/0e460e08df3aa22e19d6c9bea164d2fd"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "https://gist.github.com/cantzakas/0e460e08df3aa22e19d6c9bea164d2fd"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "WITH ranked AS (\n",
    "SELECT places.gid AS pgid\n",
    "    , rivers.gid AS rgid\n",
    "    , DENSE_RANK () OVER (ORDER BY places.pop_max DESC) AS rank\n",
    "FROM\n",
    "    public.ne_10m_populated_places_simple AS places,\n",
    "    public.ne_10m_rivers_lake_centerlines AS rivers\n",
    "WHERE \n",
    "    places.adm0cap = 1 \n",
    "    AND rivers.name_en != '' \n",
    "    AND ST_Within(places.geom,\n",
    "            (SELECT ST_Union(geom) FROM public.ne_10m_admin_0_countries WHERE continent='Europe')) \n",
    "    AND ST_Intersects(ST_Buffer(places.geom, 0.05), rivers.geom)\n",
    "GROUP BY places.gid, rivers.gid, places.pop_max)\n",
    "\n",
    "SELECT pl.name, ranked.rank, pl.geom\n",
    "FROM ranked, public.ne_10m_populated_places_simple AS pl\n",
    "WHERE ranked.rank <= 10 AND pl.gid = ranked.pgid\n",
    "UNION ALL\n",
    "SELECT rr.name, ranked.rank, rr.geom\n",
    "FROM ranked, public.ne_10m_rivers_lake_centerlines AS rr\n",
    "WHERE ranked.rank <= 10 AND rr.gid = ranked.rgid;\n",
    "\"\"\"\n",
    "\n",
    "df = gpd.read_postgis(sql, con)\n",
    "url=geojsonio.display(df.to_json())\n",
    "\n",
    "display_markdown(url, raw=True)\n",
    "display_gist_url(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [PostGIS.ST_Intersection()](https://postgis.net/docs/ST_Intersection.html): returns a geometry representing the shared portion of geometries A and B."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Rank Spain, France, Italy, Greece, Turkey, Cyprus and Morocco, by the length of their coastline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://gpadmin:***@ec2-35-178-74-236.eu-west-2.compute.amazonaws.com:5432/dev\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>continent</th>\n",
       "        <th>rank</th>\n",
       "        <th>coastline_length</th>\n",
       "        <th>geom</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Greece</td>\n",
       "        <td>Europe</td>\n",
       "        <td>1</td>\n",
       "        <td>97.7651003203332</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[24.1352645190001,34.8150088560001],[24.108571811,34.8165550800001]],[[24.1352645190001,34.8217634140001],[24.1352645190001,34.8150088560001]],[[24.122325066...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>Europe</td>\n",
       "        <td>2</td>\n",
       "        <td>67.1164736288018</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[55.6831160820001,-21.3707821589999],[55.659922722,-21.3695614559999]],[[55.6268009770001,-21.3693986959999],[55.6015731130001,-21.3703752579999]],[[55.60157...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>Europe</td>\n",
       "        <td>3</td>\n",
       "        <td>62.9627191763533</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[12.6110945970001,35.489243882],[12.6030379570001,35.491400458]],[[12.6212671230001,35.4923363300001],[12.6110945970001,35.489243882]],[[12.598317905,35.4943...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Turkey</td>\n",
       "        <td>Asia</td>\n",
       "        <td>4</td>\n",
       "        <td>62.4945056142846</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[35.9113875660001,35.9178734400001],[35.911306186,35.9177513690001],[35.9113054195336,35.9177504336715]],[[35.9113875660001,35.9178734400001],[35.92269941500...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>Europe</td>\n",
       "        <td>5</td>\n",
       "        <td>52.0293945891915</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[-17.9794815749999,27.642523505],[-17.9941300119999,27.6422386740001]],[[-17.9941300119999,27.6422386740001],[-18.0157771479999,27.6534691430001]],[[-17.9713...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Morocco</td>\n",
       "        <td>Africa</td>\n",
       "        <td>6</td>\n",
       "        <td>26.1571721814535</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[-17.0128067699999,21.4452171900001],[-17.013742642,21.4199893250001],[-17.0137433252967,21.4199710975983]],[[-17.0128067699999,21.4452171900001],[-16.971058...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cyprus</td>\n",
       "        <td>Asia</td>\n",
       "        <td>7</td>\n",
       "        <td>2.35111551731967</td>\n",
       "        <td>{&quot;type&quot;:&quot;MultiLineString&quot;,&quot;coordinates&quot;:[[[32.749196811,34.6498477230001],[32.7073673840001,34.6454125020001]],[[33.0156346359914,34.6344245410001],[33.0156356130001,34.6344261740001],[33.02588951900...</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Greece', 'Europe', 1, 97.7651003203332, '{\"type\":\"MultiLineString\",\"coordinates\":[[[24.1352645190001,34.8150088560001],[24.108571811,34.8165550800001]],[[24.1352645190001,34.8217634140001],[24.1352645190001,34.8150088560001]],[[24.122325066...'),\n",
       " ('France', 'Europe', 2, 67.1164736288018, '{\"type\":\"MultiLineString\",\"coordinates\":[[[55.6831160820001,-21.3707821589999],[55.659922722,-21.3695614559999]],[[55.6268009770001,-21.3693986959999],[55.6015731130001,-21.3703752579999]],[[55.60157...'),\n",
       " ('Italy', 'Europe', 3, 62.9627191763533, '{\"type\":\"MultiLineString\",\"coordinates\":[[[12.6110945970001,35.489243882],[12.6030379570001,35.491400458]],[[12.6212671230001,35.4923363300001],[12.6110945970001,35.489243882]],[[12.598317905,35.4943...'),\n",
       " ('Turkey', 'Asia', 4, 62.4945056142846, '{\"type\":\"MultiLineString\",\"coordinates\":[[[35.9113875660001,35.9178734400001],[35.911306186,35.9177513690001],[35.9113054195336,35.9177504336715]],[[35.9113875660001,35.9178734400001],[35.92269941500...'),\n",
       " ('Spain', 'Europe', 5, 52.0293945891915, '{\"type\":\"MultiLineString\",\"coordinates\":[[[-17.9794815749999,27.642523505],[-17.9941300119999,27.6422386740001]],[[-17.9941300119999,27.6422386740001],[-18.0157771479999,27.6534691430001]],[[-17.9713...'),\n",
       " ('Morocco', 'Africa', 6, 26.1571721814535, '{\"type\":\"MultiLineString\",\"coordinates\":[[[-17.0128067699999,21.4452171900001],[-17.013742642,21.4199893250001],[-17.0137433252967,21.4199710975983]],[[-17.0128067699999,21.4452171900001],[-16.971058...'),\n",
       " ('Cyprus', 'Asia', 7, 2.35111551731967, '{\"type\":\"MultiLineString\",\"coordinates\":[[[32.749196811,34.6498477230001],[32.7073673840001,34.6454125020001]],[[33.0156346359914,34.6344245410001],[33.0156356130001,34.6344261740001],[33.02588951900...')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name\n",
    "\t, continent\n",
    "\t, DENSE_RANK() OVER (ORDER BY coastline_length DESC) AS rank\n",
    "\t, coastline_length\n",
    "\t, SUBSTRING(ST_AsGeoJSON(geom), 0, 200) || '...' as geom\n",
    "FROM (\n",
    "\tSELECT name\n",
    "\t\t, continent\n",
    "\t\t, ST_UNION(geom) AS geom\n",
    "\t\t, ST_LENGTH(ST_UNION(geom)) AS coastline_length\n",
    "\tFROM (\n",
    "\t\tSELECT cntr.\"name\" \n",
    "\t\t\t, cntr.continent \n",
    "\t\t\t, (ST_DUMP(ST_INTERSECTION(cntr.geom, cst.geom))).geom geom\n",
    "\t\tFROM ne_10m_admin_0_countries cntr \n",
    "\t\t\t, ne_10m_coastline cst\n",
    "\t\tWHERE\n",
    "\t\t\tST_INTERSECTS(cst.geom, cntr.geom)\n",
    "            AND name IN ('Spain', 'France', 'Italy', 'Greece', 'Turkey', 'Cyprus', 'Morocco')\n",
    "\t\t\t) clipped\n",
    "\tWHERE ST_DIMENSION(clipped.geom) = 1\n",
    "\tGROUP BY name, continent\n",
    "\t) foo\n",
    "ORDER BY coastline_length DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is happening here:\n",
    "- Returns true if `ne_10m_coastline.geom`, and `ne_10m_admin_0_countries.geom` spatially intersect in 2D (have at least one point in common):\n",
    "```sql\n",
    "\t\t\tST_INTERSECTS(ne_10m_coastline.geom, ne_10m_admin_0_countries.geom)\n",
    "```\n",
    "- Find the geometry representing the shared portion of geometries `ne_10m_coastline.geom`, and `ne_10m_admin_0_countries.geom`, and get the set of `geometry_dump` rows for the components of this geometry:\n",
    "```sql\n",
    "\t\t\t, (ST_DUMP(ST_INTERSECTION(ne_10m_coastline.geom, ne_10m_admin_0_countries.geom))).geom\n",
    "```\n",
    "- Returns a geometry representing the point-set union of the  geometry, aka `Country Coastline Borders`.\n",
    "```sql\n",
    "\t\t, ST_UNION(geom)\n",
    "```\n",
    "- Calculate the length of `Country Coastline Border` for each country\n",
    "```sql\n",
    "\t\t, ST_LENGTH(ST_UNION(geom)) AS coastline_length\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "http://geojson.io/#id=gist:/45ed669c782c14551e2c769cd39e8122"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "https://gist.github.com/cantzakas/45ed669c782c14551e2c769cd39e8122"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "SELECT name\n",
    "\t, continent\n",
    "\t, DENSE_RANK() OVER (ORDER BY coastline_length DESC) AS rank\n",
    "\t, coastline_length\n",
    "\t, geom\n",
    "FROM (\n",
    "\tSELECT name\n",
    "\t\t, continent\n",
    "\t\t, ST_UNION(geom) AS geom\n",
    "\t\t, ST_LENGTH(ST_UNION(geom)) AS coastline_length\n",
    "\tFROM (\n",
    "\t\tSELECT cntr.\"name\" \n",
    "\t\t\t, cntr.continent \n",
    "\t\t\t, (ST_Dump(ST_Intersection(cntr.geom, cst.geom))).geom geom\n",
    "\t\tFROM ne_10m_admin_0_countries cntr \n",
    "\t\t\t, ne_10m_coastline cst\n",
    "\t\tWHERE\n",
    "\t\t\tST_INTERSECTS(cst.geom, cntr.geom)\n",
    "            AND name IN ('Spain', 'France', 'Italy', 'Greece', 'Turkey', 'Cyprus', 'Morocco')\n",
    "\t\t\t) clipped\n",
    "\tWHERE ST_DIMENSION(clipped.geom) = 1\n",
    "\tGROUP BY name, continent\n",
    "\t) foo\n",
    "ORDER BY coastline_length DESC;\n",
    "\"\"\"\n",
    "\n",
    "df = gpd.read_postgis(sql, con)\n",
    "url=geojsonio.display(df.to_json())\n",
    "\n",
    "display_markdown(url, raw=True)\n",
    "display_gist_url(url)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}