UseCases/Proximity-To-Climate-Risk/Proximity_To_Climate_Risk.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Prerequisites for Demonstration\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"../../images/TeradataLogo.png\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "<hr>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Python Packages</b>. Depending on the environment, additional packages may be needed.  At the very minimum, this demo requires the following.  Install manually or execute the code cells that follow.<p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Install folium, geopandas, plotly, shapely</li>\n",
    "    </ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install other required packaged\n",
    "%pip install folium geopandas plotly shapely"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Analyzing Proximity to Climate Risk At Scale using VantageCloud Lake and ClearScape Analytics\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"../../images/TeradataLogo.png\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "<hr>\n",
    "<br>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The effects of climate change pose risks to persons and property in the proximity of the more vulnerable geographic regions on the planet.  The ability to rapidly analyze individuals in the path of catastrophic weather or other types of events can save countless lives.  Additionally, having this capability combined with other enterprise planning and analytics processes can assist with business operations, disaster preparedness, and long-term strategic planning.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Teradata <b>ClearScape Analytics Functions</b> combine traditional analytics, machine learning, and advanced statistical processing along with <b>Geospatial</b> capabilities.  This allows enterprises to rapidly analyze this geographic-related information in real-time at any scale - effectively understanding the impacts of these events on entire populations.</p>\n",
    "\n",
    "\n",
    "<hr>\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233C'>Demonstration Overview</b>\n",
    "    \n",
    "        \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In 2023, the North Island of New Zealand expericed drastic and catastrophic flooding.  This demonstration notebook will illustrate how analysts can leverage location and proximity information at scale to analyze which specific addresses are within a certain proximity to flooding.  This information can be used for lifesaving flood risk warnings, better disaster preparedness, or even more efficient insurance and planning regulations.<p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The steps are as follow:</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Inspect Geospatial data</b> using client-side tools and visualization</li>\n",
    "    <li><b>Analyze Proximity at Scale</b> using ClearScape Analytic functions to calculate flood location centroids and risk boundaries, and then apply the analysis at scale to analyze every New Zealand address and asses high, medium, and low risk based on proximity to the flood zones</li>\n",
    "    </ol>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Python Package Imports</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Standard practice to import required packages and libraries; execute this cell to import packages for Teradata automation as well as client-side geospatial and mapping packages.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:32:03.971178Z",
     "start_time": "2023-03-20T16:32:01.933148Z"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "import geopandas as gpd\n",
    "import folium\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "from shapely import wkt\n",
    "from folium import plugins\n",
    "\n",
    "from teradataml import *\n",
    "from branca.element import Figure\n",
    "\n",
    "from IPython.display import display as ipydisplay\n",
    "\n",
    "display.suppress_vantage_runtime_warnings = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Connect to Teradata Vantage</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:32:27.110846Z",
     "start_time": "2023-03-20T16:32:04.005694Z"
    }
   },
   "outputs": [],
   "source": [
    "# load vars json\n",
    "with open('../../vars.json', 'r') as f:\n",
    "    session_vars = json.load(f)\n",
    "\n",
    "# Create the SQLAlchemy Context\n",
    "host = session_vars['environment']['host']\n",
    "username = session_vars['hierarchy']['users']['business_users'][0]['username']\n",
    "password = session_vars['hierarchy']['users']['business_users'][0]['password']\n",
    "\n",
    "eng = create_context(host=host, username=username, password=password)\n",
    "\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#00233C'><b>Demo 1 - Inspect the Data</b></p>\n",
    "\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Two datasets have been loaded into the Vantage system in preparation for this demonstratio.  Data was obtained in ESRI shape format, and loaded into Vantage using teradataml client libraries.</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>New Zealand Street Addresses</b>  All data for the North Island</li>\n",
    "    <li><b>Flood Areas</b>  Seven areas in total</li>\n",
    "    </ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Analyze some of the data locally:</p>\n",
    "            <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                <li><b>Inspect the Data</b> Look at a sample of rows, row counts</li>\n",
    "                <li><b>Map the Flood Zones</b> Using client-side tools</li>\n",
    "                <li><b>Calculate and Map Centroids</b> Use client tools to calculate the centroids of each region</li>\n",
    "            </ul>\n",
    "\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Street Addresses</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:32:45.548995Z",
     "start_time": "2023-03-20T16:32:27.127476Z"
    }
   },
   "outputs": [],
   "source": [
    "tdf_addrs = DataFrame('\"demo\".\"nz_addrs_geoV\"')\n",
    "\n",
    "print('NZ Street Addresses with Lat, Lon, and POINT object:')\n",
    "ipydisplay(tdf_addrs.head(2))\n",
    "print('1.6 Million Addresses:')\n",
    "ipydisplay(tdf_addrs.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Flood Risk Areas</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:33:33.197598Z",
     "start_time": "2023-03-20T16:33:16.037922Z"
    }
   },
   "outputs": [],
   "source": [
    "tdf_flood = DataFrame('\"demo\".\"nz_floods_geoV\"')\n",
    "\n",
    "#df_flood.reset_index(inplace = True)\n",
    "tdf_flood.to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Putting the Flood Zones on a Map</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This demonstration uses the <a href = 'https://python-visualization.github.io/folium/'>folium</a> package along with <a href = 'https://geopandas.org/en/stable/'>geopandas</a> to overlay the flood zone data on an interactive map</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:33:46.463373Z",
     "start_time": "2023-03-20T16:33:46.418232Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = Figure(width=600, height=400)\n",
    "m = folium.Map(location=[-38.6661126,175.9408615], zoom_start=7)\n",
    "minimap = plugins.MiniMap()\n",
    "m.add_child(minimap)\n",
    "fig.add_child(m)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Draw the Flood Regions</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Use Pandas, GeoPandas, and Folium to paint the flood zones</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:33:47.188259Z",
     "start_time": "2023-03-20T16:33:47.149258Z"
    }
   },
   "outputs": [],
   "source": [
    "# Load the flood zone data into local geopandas dataframe\n",
    "\n",
    "df = tdf_flood.to_pandas()\n",
    "df[\"GEOM\"] = df[\"GEOM\"].apply(wkt.loads)\n",
    "df_flood = gpd.GeoDataFrame(df, geometry=\"GEOM\", crs=4326)\n",
    "\n",
    "\n",
    "# add the flood zones to the map\n",
    "for _, r in df_flood.iterrows():\n",
    "    sim_geo = gpd.GeoSeries(r['GEOM']).simplify(tolerance=0.001)\n",
    "    geo_j = sim_geo.to_json()\n",
    "    geo_j = folium.GeoJson(data=geo_j,\n",
    "                           style_function=lambda x: {'fillColor': 'red', 'color': 'red', 'weight': 0.5})\n",
    "    geo_j.add_to(m)\n",
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Calculate Centroids</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In order to properly compute geometric properties, in this case centroids, of the geometries, we need to project the data to a projected coordinate system.</p>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:39:29.664441Z",
     "start_time": "2023-03-20T16:39:29.608670Z"
    }
   },
   "outputs": [],
   "source": [
    "# Project to New Zealand Transverse Mercator projected Coordinate Reference System\n",
    "\n",
    "df_flood = df_flood.to_crs(epsg=2193)\n",
    "\n",
    "# Access the centroid attribute of each polygon\n",
    "df_flood['centroid'] = df_flood.centroid\n",
    "\n",
    "# Project to WGS84 geographic crs\n",
    "# geometry (active) column\n",
    "# EPSG 4326 is the projection based on WGS84 (GPS) coordinate system\n",
    "df_flood = df_flood.to_crs(epsg=4326)\n",
    "\n",
    "# Centroid column\n",
    "df_flood['centroid'] = df_flood['centroid'].to_crs(epsg=4326)\n",
    "\n",
    "df_flood.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Put Centroids on the Map</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:39:31.501749Z",
     "start_time": "2023-03-20T16:39:31.470747Z"
    }
   },
   "outputs": [],
   "source": [
    "for _, r in df_flood.iterrows():\n",
    "    lat = r['centroid'].y\n",
    "    lon = r['centroid'].x\n",
    "    folium.Marker(location=[lat, lon], popup = 'FID: {}'.format(r['fid'])\n",
    "                  ).add_to(m)\n",
    "\n",
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#00233C'><b>Demo 2 - Teradata Geospatial Analysis for Proximity</b></p>\n",
    "\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The prior demonstration illustrated how one can use common client-side tools for Geospatial analysis.  In reality, the true value in this analysis occurs both at the <b>scale</b> of human populations, or <b>combined</b> with other analytic and machine learning insights.  <b>Teradata Vantage ClearScape Analytics</b> allows just this.  This demonstration will show how to:</p>\n",
    "            <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                <li><b>Calculate Centroids and Buffer Zones</b> around the Flood Zone shapes</li>\n",
    "                <li><b>Map the Buffer Zones</b> Using client-side tools</li>\n",
    "                <li><b>Calculate Address Proximity</b> Analyze all 1.6 Million Records in-Database in seconds</li>\n",
    "                <li><b>Map Degree of Risk</b> Based on Proximity calculations</li>\n",
    "            </ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Calculate Centroids and Buffer Zones in-Database</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Use the <a href = ''>centroid</a> property as well as the <a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/Geospatial/teradataml-GeoDataFrame/Methods/Geospatial-Specific-Methods/Methods-for-All-Types-of-Geometries/buffer'>buffer</a> method of the teradataml DataFrame to inspect and calculate this data in-database.  Note this python syntax is automaticaly translated to SQL to run in the Vantage system.  The <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Geospatial-Data-Types/July-2021/ST_Geometry-Constructors-and-Methods/ST_Centroid-Method'>ST_Centroid</a> and <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Geospatial-Data-Types/July-2021/ST_Geometry-Constructors-and-Methods/ST_Buffer-Method'>ST_Buffer</a> SQL functions can be run directly as well to perform the same calculations.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get a reference to the data in Vantage\n",
    "tdf_flood = GeoDataFrame('\"demo\".\"nz_floods_geoV\"')\n",
    "\n",
    "# Call the teradataml assign() method to create two new columns \"CNTRD\" and \"CNTRD_BUFFER\" \n",
    "# containing the centroid and .5KM boundary shape respectively\n",
    "\n",
    "tdf_flood = tdf_flood.assign(CNTRD = tdf_flood.GEOM.centroid, CNTRD_BUFFER = tdf_flood.GEOM.centroid.buffer(.5))\n",
    "\n",
    "# Inspect the new columns for Flood Zone 7\n",
    "tdf_flood[tdf_flood['fid'] == 7].to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Map the Buffer</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Retrieve the buffer shape to the client, and use GeoPandas methods to simplify the shape so it can be easily drawn on the map.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:39:40.348346Z",
     "start_time": "2023-03-20T16:39:40.325339Z"
    }
   },
   "outputs": [],
   "source": [
    "df_buffer = tdf_flood[tdf_flood['fid'] == 7].to_pandas()\n",
    "df_buffer[\"CNTRD_BUFFER\"] = df_buffer[\"CNTRD_BUFFER\"].apply(wkt.loads)\n",
    "gdf_buffer = gpd.GeoDataFrame(df_buffer, geometry=\"CNTRD_BUFFER\", crs=4326)\n",
    "gdf_buffer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:39:41.946544Z",
     "start_time": "2023-03-20T16:39:41.915536Z"
    }
   },
   "outputs": [],
   "source": [
    "for _, r in gdf_buffer.iterrows():\n",
    "    sim_geo = gpd.GeoSeries(r['CNTRD_BUFFER']).simplify(tolerance=0.001)\n",
    "    geo_j = sim_geo.to_json()\n",
    "    geo_j = folium.GeoJson(data=geo_j,\n",
    "                           style_function=lambda x: {'fillColor': 'grey', 'color': 'grey', 'weight': 0.5})\n",
    "#     folium.Popup(r['ZONETYPE']).add_to(geo_j)\n",
    "    geo_j.add_to(m)\n",
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Find all addresses within the Buffer</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>First, join the buffer shape to the address information.  Next, use the <a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/Geospatial/teradataml-GeoDataFrame/Methods/Geospatial-Specific-Methods/Methods-for-All-Types-of-Geometries/within'>within</a> method to find all addresses inside the buffer boundary.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_nz_addrs = GeoDataFrame('\"demo\".\"nz_addrs_geoV\"')\n",
    "tdf_nz_addrs = tdf_nz_addrs.join(tdf_flood[tdf_flood['fid'] == 7][['CNTRD_BUFFER']], how = 'cross')\n",
    "\n",
    "\n",
    "tdf_nz_addrs[tdf_nz_addrs['POINT'].within(tdf_nz_addrs['CNTRD_BUFFER']) == 1].to_sql(schema_name = 'demo', \n",
    "                                                                                     table_name = 'addrs_in_buffer', \n",
    "                                                                                     if_exists = 'replace')\n",
    "tdf_within = GeoDataFrame('\"demo\".\"addrs_in_buffer\"')\n",
    "print('Number of Addresses within Flood Zone Seven Buffer:')\n",
    "ipydisplay(tdf_within.shape)\n",
    "\n",
    "print('Sample of the Data:')\n",
    "ipydisplay(tdf_within[['address_id', 'full_add_2','POINT']].to_pandas(num_rows = 2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Calculate distance in KMs to all addresses within the buffer from Flood Zone 7</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This step requires two Teradata Geospatial methods;</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li><b><a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/Geospatial/teradataml-GeoDataFrame/Methods/Geospatial-Specific-Methods/Methods-for-All-Types-of-Geometries/transform'>transform</a></b> Which will transform our geometry objects to the desired coordinate reference system.  For the SQL syntax see <a href = 'https://docs.teradata.com/r/W1AEeHO2cxTi3Sn7dtj8hg/Tu9pmmM_0OQEqwK4eq86fg'>ST_Transform</a>.  This function takes two primary arguments in \"Well Known Text\" (WKT) Format; the <b>target</b> Coordinate System, and the <b>source</b> coordinate system.  Teradata stores all supported CRS in a specific database table \"SYSSPATIAL\".\"SPATIAL_REF_SYS\".  The values from this table can be used as the required arguments.</li>\n",
    "    <li><b><a href = 'https://docs.teradata.com/r/Lake/Teradata-Package-for-Python-Function-Reference-on-VantageCloud-Lake/Geospatial/teradataml-GeoDataFrame/Methods/Geospatial-Specific-Methods/Methods-for-All-Types-of-Geometries/distance'>distance</a></b> which will calculate the distance between two geometry objects.  For the SQL syntaxt see <a href = 'https://docs.teradata.com/r/W1AEeHO2cxTi3Sn7dtj8hg/V30FcBuekQCTfWVD~ifJag'>ST_Distance</a>.\n",
    "        </ol>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Once we have the distance of each selected address to the actual flood boundary, filter the results to return only addresses less than 10KM from the flood zone.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This set step is a good example of where SQL can be easier to express than the associated teradataml method.  Note here we can combine the various tables including the CRS, as well as distance filtering and the original flood zones table data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT \n",
    "    AD.TOWN_CITY,\n",
    "    AD.FULL_ADD_2,\n",
    "    AD.POINT,\n",
    "    FZ.GEOM.ST_TRANSFORM(SRS_1.srtext,SRS_2.srtext).ST_DISTANCE(AD.POINT.ST_TRANSFORM(SRS_1.srtext,SRS_2.srtext))*1/1000 as DIST_KM\n",
    "FROM \n",
    "    demo.addrs_in_buffer AD,\n",
    "    demo.nz_floods_geoV FZ,\n",
    "    sysspatial.SPATIAL_REF_SYS SRS_1,\n",
    "    sysspatial.SPATIAL_REF_SYS SRS_2\n",
    "WHERE\n",
    "    FZ.FID = 7\n",
    "    AND SRS_1.AUTH_SRID = 2193 -- NZTM (to) Projected System\n",
    "    AND SRS_2.AUTH_SRID = 4326 -- WGS84 (from) Geographic System\n",
    "    AND FZ.GEOM.ST_TRANSFORM(SRS_1.srtext,SRS_2.srtext).ST_DISTANCE(AD.POINT.ST_TRANSFORM(SRS_1.srtext,SRS_2.srtext))*1/1000 < 10;\n",
    "'''\n",
    "\n",
    "tdf_st_dist = DataFrame.from_query(qry)\n",
    "print(\"Number of Addresses within 10KM of the Flood Zone:\")\n",
    "ipydisplay(tdf_st_dist.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:45:40.555730Z",
     "start_time": "2023-03-20T16:45:39.809893Z"
    }
   },
   "outputs": [],
   "source": [
    "df_st_dist = tdf_st_dist.sample(frac = .1).to_pandas(all_rows = True)\n",
    "df_st_dist[\"POINT\"] = df_st_dist[\"POINT\"].apply(wkt.loads)\n",
    "gdf_st_dist = gpd.GeoDataFrame(df_st_dist, geometry=\"POINT\", crs=4326)\n",
    "gdf_st_dist"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-17T07:39:49.720968Z",
     "start_time": "2023-03-17T07:39:49.700968Z"
    }
   },
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Use Client-side tools to create risk bands</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using GeoPandas, paint each address as follows</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b style = 'color:red'>Red</b> for addresses inside the Flood Zone</li>\n",
    "    <li><b style = 'color:#8B8000'>Yellow</b> for addresses 0 to 5 KM away</li>\n",
    "    <li><b style = 'color:green'>Green</b> for addresses 5 to 10 KM away</li>\n",
    "    </ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note there are about 65,000 addresses inside the 10 KM boundary to Flood Zone 7.  To optimize for demonstration purposes, a random 10% sample of addresses is used.  This analysis can be easily performed on the Vantage database, but concessions need be made for client-side tools.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:46:46.976665Z",
     "start_time": "2023-03-20T16:45:44.147227Z"
    }
   },
   "outputs": [],
   "source": [
    "# Create circle markers for each address (10% sample)\n",
    "# Color them based on the distance calculation above\n",
    "\n",
    "for _, r in df_st_dist[df_st_dist.DIST_KM == 0].iterrows():\n",
    "    lat = r['POINT'].y\n",
    "    lon = r['POINT'].x\n",
    "    folium.CircleMarker(location=[lat, lon], \n",
    "                        popup='ADDR: {} <br> DIST: {}'.format(r['full_add_2'], r['DIST_KM']), \n",
    "                        radius=1,\n",
    "                        color = 'red').add_to(m)\n",
    "\n",
    "for _, r in df_st_dist[df_st_dist['DIST_KM'].between(0.00001, 5)].iterrows():\n",
    "    lat = r['POINT'].y\n",
    "    lon = r['POINT'].x\n",
    "    folium.CircleMarker(location=[lat, lon], \n",
    "                        popup='ADDR: {} <br> DIST: {}'.format(r['full_add_2'], r['DIST_KM']), \n",
    "                        radius=1,\n",
    "                        color = 'yellow').add_to(m)\n",
    "for _, r in df_st_dist[df_st_dist['DIST_KM'].between(5, 10)].iterrows():\n",
    "    lat = r['POINT'].y\n",
    "    lon = r['POINT'].x\n",
    "    folium.CircleMarker(location=[lat, lon], \n",
    "                        popup='ADDR: {} <br> DIST: {}'.format(r['full_add_2'], r['DIST_KM']), \n",
    "                        radius=1,\n",
    "                        color = 'green').add_to(m)\n",
    "\n",
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Optional - Save map as .html file to view later or share</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:55:56.578915Z",
     "start_time": "2023-03-20T16:54:49.695449Z"
    }
   },
   "outputs": [],
   "source": [
    "m.save(\"linz_flood_risk_zone7_points.html\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Heatmap Visualization</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using GeoPandas, get latitude and longitude and create Heat Map interactive map.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:56:52.988069Z",
     "start_time": "2023-03-20T16:56:52.941071Z"
    }
   },
   "outputs": [],
   "source": [
    "gdf_st_dist = gpd.GeoDataFrame(df_st_dist, geometry=\"POINT\", crs=4326)\n",
    "gdf_st_dist['lat'] = gdf_st_dist.geometry.y\n",
    "gdf_st_dist['lon'] = gdf_st_dist.geometry.x"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Population in buffer area within 10 KMs radius</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:57:54.313101Z",
     "start_time": "2023-03-20T16:56:56.048947Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = Figure(width=600, height=400)\n",
    "hm = folium.Map(location=[-39.646172303816414, 176.775454544966948], zoom_start=8)\n",
    "\n",
    "\n",
    "minimap = plugins.MiniMap()\n",
    "hm.add_child(minimap)\n",
    "\n",
    "for _, r in df_flood.iterrows():\n",
    "    sim_geo = gpd.GeoSeries(r['GEOM']).simplify(tolerance=0.001)\n",
    "    geo_j = sim_geo.to_json()\n",
    "    geo_j = folium.GeoJson(data=geo_j,\n",
    "                           style_function=lambda x: {'fillColor': 'red', 'color': 'red', 'weight': 0.5})\n",
    "#     folium.Popup(r['FID']).add_to(geo_j)\n",
    "    geo_j.add_to(hm)\n",
    "\n",
    "locations=list(zip(gdf_st_dist.full_add_2, gdf_st_dist.DIST_KM))\n",
    "popups = ['ADDR: {} <br> DIST: {}'.format(full_add_2, DIST_KM) for (full_add_2, DIST_KM) in locations]\n",
    "\n",
    "location=gdf_st_dist[['lat','lon']]\n",
    "plugins.MarkerCluster(location, popups=popups).add_to(hm)\n",
    "fig.add_child(hm)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Assign risk score in range of 0-100 for a Heat Map gradient value.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T16:59:31.509961Z",
     "start_time": "2023-03-20T16:58:34.440048Z"
    }
   },
   "outputs": [],
   "source": [
    "gdf_st_dist.loc[gdf_st_dist['DIST_KM'] ==0, 'RISK'] = 100\n",
    "gdf_st_dist.loc[gdf_st_dist['DIST_KM'].between(0.00001, 5), 'RISK'] = 50\n",
    "gdf_st_dist.loc[gdf_st_dist['DIST_KM'].between(5, 10), 'RISK'] = 25\n",
    "\n",
    "heat_data=list(zip(gdf_st_dist['lat'], gdf_st_dist['lon'], gdf_st_dist.RISK))\n",
    "folium.plugins.HeatMap(heat_data,\n",
    "                        gradient={0.1: 'blue', 0.3: 'lime', 0.5: 'yellow', 0.7: 'orange', 1: 'red'}, \n",
    "                        min_opacity=0.05, \n",
    "                        max_opacity=0.9, \n",
    "                        radius=25,\n",
    "                        use_local_extrema=False\n",
    "                      ).add_to(hm)\n",
    "\n",
    "folium.LayerControl().add_to(hm)\n",
    "hm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T17:01:08.775855Z",
     "start_time": "2023-03-20T17:00:17.595009Z"
    }
   },
   "outputs": [],
   "source": [
    "hm.save(\"linz_flood_risk_zone7_heatmap.html\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Visualization in Plotly</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;;color:#00233C'>Plotly is another popular visualization tool. Overlay the risk bands on the Plotly street map.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-03-20T17:02:01.639429Z",
     "start_time": "2023-03-20T17:02:00.388342Z"
    }
   },
   "outputs": [],
   "source": [
    "import plotly.graph_objects as go\n",
    "\n",
    "fig = go.Figure(go.Densitymapbox(lat=gdf_st_dist['lat'], \n",
    "                                 lon=gdf_st_dist['lon'],\n",
    "                                 z=gdf_st_dist['RISK'],\n",
    "                                 radius=3,\n",
    "                                 colorscale=[[0.0, 'blue',],[0.3,'lime'],[0.5,'yellow'],[0.7,'orange'],[1.0, 'red']],\n",
    "                                 zmin=0.0,\n",
    "                                 zmax=1.0,\n",
    "                                 opacity=0.5                           \n",
    "                                ))\n",
    "\n",
    "fig.update_layout(mapbox_style=\"open-street-map\",\n",
    "                  mapbox_center_lon=176.775454544966948,\n",
    "                  mapbox_center_lat=-39.646172303816414,\n",
    "                  mapbox_zoom=8)\n",
    "\n",
    "fig.update_layout(margin={\"r\":0,\"t\":0,\"l\":0,\"b\":0})\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Clean up</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove the within table:\n",
    "db_drop_table('addrs_in_buffer', schema_name = 'demo')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "position": {
    "height": "334.85px",
    "left": "1219.8px",
    "right": "20px",
    "top": "120px",
    "width": "279.4px"
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}