{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Getting started with ibmdbpy - Part 2: Geospatial extension\n", "\n", "This notebook showcases the key abstractions and features of ibmdbpy geospatial extension. It provides you with step-by-step examples to get started with the package and its gespatial functionalities.\n", "\n", "____\n", "\n", "### Accelerate Python analytics with in-database processing by using ibmdbpy and IBM Db2 Warehouse\n", " \n", "The ibmdbpy project provides access to in-database algorithms in IBM Db2 Warehouse through a Python interface for data manipulation. It accelerates Python analytics by seamlessly pushing operations written in Python into the underlying database for execution, thereby benefitting from in-database performance-enhancing features, such as columnar storage and parallel processing. For more details about ibmdbpy, please refer to the [documentation](https://pythonhosted.org/ibmdbpy/index.html) and to the dedicated [Git repository](https://github.com/ibmdbanalytics/ibmdbpy/tree/master/ibmdbpy). This notebook provides you with an overview of ibmdbpy geospatial extension.\n", "\n", "__About ibmdbpy's geospatial extension__\n", "\n", "Ibmdbpy supports a wrapper for spatial functions which enables you to generate and analyze spatial information about geographic features, and to store and manage the data on which this information is based. The spatial data is identified by ibmdbpy as a special class called IdaGeoDataFrame that extends all the properties of an IdaDataFrame and has additional methods supported for geospatial types like ST_Point, ST_LineString, ST_Polygon etc. Like Python package GeoPandas, which is an extension of Pandas and provides the GeoDataFrame abstraction, ibmdbpy spatial extension lets you play with IdaGeoDataFrames and IdaGeoSeries as an extension of IdaDataFrames and IdaSeries. The Python wrappers for spatial functions which Db2 currently supports make the querying process much simpler for users. For more details about ibmdbpy geospatial extension, please refer to the dedicated [documentation](https://pythonhosted.org/ibmdbpy/geospatial.html). More details about Db2 spatial extender can be found on the [IBM Knowledge Center](https://www.ibm.com/support/knowledgecenter/SSCJDQ/com.ibm.swg.im.dashdb.spatial.doc/doc/csbp1001.html).\n", "\n", "__Prerequisites__\n", "* Db2 account: see [IBM Cloud](https://cloud.ibm.com/login) or [Db2 Warehouse](https://www.ibm.com/support/knowledgecenter/en/SSCJDQ/com.ibm.swg.im.dashdb.kc.doc/welcome.html)\n", "* Db2 driver: learn more on [IBM Knowledge Center](https://www.ibm.com/support/knowledgecenter/en/SSFMBX/com.ibm.swg.im.dashdb.doc/connecting/connect_applications_by_type.html) and see [IBM Support](https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads)\n", "* Having installed the [ibmdbpy](https://pypi.org/project/ibmdbpy/) python library with pip: \n", "> pip install ibmdbpy \n", "* Optional dependency for JDBC is the [jaydebeapi](https://pypi.org/project/JayDeBeApi/) library. Run the following command to install ibmdbpy, as well as the dependencies for the JDBC feature:\n", "> pip install ibmdbpy[jdbc]\n", "\n", "__Contents__\n", "\n", "1. Establish connection to Db2 database\n", "2. Open and define IdaGeoDataFrames\n", "3. Learn how to handle IdaGeoDataFrames " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "__Imports__" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "from ibmdbpy import IdaDataBase, IdaDataFrame, IdaGeoDataFrame" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 1. Establish connection to Db2 database\n", "\n", "In ibmdbpy, users can choose to use JDBC to connect to a remote Db2 instance. The JDBC Connection is based on a Java Virtual Machine, so it is available on every machine that can run Java. You could also use an ODBC connection, however this is not the option we use in this notebook.\n", "\n", "__JDBC__\n", "\n", "First, you need to dowload a valid driver (more info on [IBM Support](https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads)). Then you need to put the `db2jcc.jar` or`db2jcc4.jar` file in the ibmdbpy site-package folder. When ibmdbpy runs, it checks whether one of those files exists in its installation folder and uses it to establish the connection. \n", "\n", "More details on IBM Knowledge Center:\n", "* JDBC for [IBM Db2 Warehouse](https://www.ibm.com/support/knowledgecenter/en/SSCJDQ/com.ibm.swg.im.dashdb.doc/connecting/connect_connecting_jdbc_applications.html)\n", "* JDBC for [IBM Db2 on Cloud](https://www.ibm.com/support/knowledgecenter/en/SSFMBX/com.ibm.swg.im.dashdb.doc/connecting/connect_connecting_jdbc_applications.html)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "#Enter the values for you database connection\n", "dsn_database = \"___\" # e.g. \"BLUDB\"\n", "dsn_hostname = \"___\" # e.g.: \"abc.url.example\"\n", "dsn_port = \"___\" # e.g. \"50000\"\n", "dsn_uid = \"___\" # e.g. \"db2_1234\"\n", "dsn_pwd = \"___\" # e.g. \"zorglub\"" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "connection_string='jdbc:db2://'+dsn_hostname+':'+dsn_port+'/'+dsn_database+':user='+dsn_uid+';password='+dsn_pwd+\";\" \n", "# the IdaDataBase object holds the connection to database.\n", "idadb=IdaDataBase(dsn=connection_string)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Congratulations! You successfully connected to Db2 with ibmdbpy! When you are done, use `idadb.close()` to close the connection. To reconnect, or if the connection was broken, just use `idadb.reconnect()`. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "__Verbosity and autocommit__\n", "\n", "The verbose mode automatically prints all SQL-communication between ibmdbpy and Db2, which can be very useful for debugging or understanding how ibmdbpy works. Choose the mode with `set_verbose()` or by setting the `verbosity` option when defining the IdaDataBase object. We encourage you to take a look at the prints in the first place, then feel free to silence the verbose. Note that printing adds delay when running cells. " ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "# Verbosity\n", "from ibmdbpy.utils import set_verbose\n", "set_verbose(False) # set it to True if you want to see the detail of ibmdbpy operations" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "By default the environment variable `AUTOCOMMIT` is then set to True, which means that every SQL statement which is submitted through the connection is executed within its own transaction and then committed implicitly. When you close the connection to Db2, if the environment variable `AUTOCOMMIT` is set to False, then all changes after the last explicit commit are discarded. \n", "\n", "Let's get to it!" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 2. Open and define IdaGeoDataFrames\n", "\n", "Let's explore the functionalities of ibmdbpy's geospatial extension! In this notebook, we will get familiar with IdaGeoDataFrames. An IdaGeoDataFrame is a reference to a spatial table in a remote instance of Db2. It has inherited the properties of an IdaDataFrame and benefits from additional functionalities derived from Db2 spatial extension. \n", "\n", "In the examples below, we simply use sample data available out of the box in Db2 Warehouse. The`SAMPLES.GEO_COUNTY` dataset contains geographical and administrative data about US counties." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "__Load the data__" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Method 1: open the data directly as IdaGeoDataFrame\n", "\n", "Simply create an object of the IdaGeoDataFrame class. The first argument is the name of the IdaDataBase object which holds the connection to the database, the second argument designates the Db2 table you want to open. Optionally you may set an `indexer` column and a `geometry` column.\n", "\n", "Note that in the following cell we have set the `OBJECTID` column as indexer when defining the IdaDataFrame. Otherwise, since the data is partitioned, it cannot be guaranteed that rows are always displayed in the same order. (Although, in paractice, an implicit sorting is operated by ibmdbpy). To ensure a behavior closer to Pandas' we therefore explicitly set an eligible column as index." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "# prepopulated table in Db2\n", "idageodf = IdaGeoDataFrame(idadb, 'SAMPLES.GEO_COUNTY', indexer='OBJECTID')" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/Users/eva.feillet/.local/lib/python3.7/site-packages/jaydebeapi/__init__.py:250: UserWarning: No type mapping for JDBC type 'STRUCT' (constant value 2002). Using None as a default type_code.\n", " \"Using None as a default type_code.\" % (type_name, jdbc_type_const))\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
OBJECTIDSHAPESTATEFPCOUNTYFPCOUNTYNSNAMEGEOIDNAMELSADLSADCLASSFPMTFCCCSAFPCBSAFPMETDIVFPFUNCSTATALANDAWATERINTPTLATINTPTLON
01MULTIPOLYGON (((-99.4756582604 33.8340108094, ...4848701384029Wilbarger48487Wilbarger County06H1G402046900A2.514474e+0918257915.0+34.0849199-099.2424397
12MULTIPOLYGON (((-96.6219873342 30.0442882117, ...4801501383793Austin48015Austin County06H1G402028826420A1.674401e+0925610780.0+29.8919013-096.2701696
23MULTIPOLYGON (((-99.4497297204 46.6316377481, ...3804701035620Logan38047Logan County06H1G4020A2.571371e+0947715597.0+46.4692780-099.5045846
34MULTIPOLYGON (((-107.4817473750 37.0000108736,...0806700198148La Plata08067La Plata County06H1G402020420A4.382664e+0919545452.0+37.2873673-107.8397178
45MULTIPOLYGON (((-91.2589262966 36.2578866492, ...0512100069178Randolph05121Randolph County06H1G4020A1.689166e+099968439.0+36.3412985-091.0284409
\n", "
" ], "text/plain": [ " OBJECTID SHAPE STATEFP \\\n", "0 1 MULTIPOLYGON (((-99.4756582604 33.8340108094, ... 48 \n", "1 2 MULTIPOLYGON (((-96.6219873342 30.0442882117, ... 48 \n", "2 3 MULTIPOLYGON (((-99.4497297204 46.6316377481, ... 38 \n", "3 4 MULTIPOLYGON (((-107.4817473750 37.0000108736,... 08 \n", "4 5 MULTIPOLYGON (((-91.2589262966 36.2578866492, ... 05 \n", "\n", " COUNTYFP COUNTYNS NAME GEOID NAMELSAD LSAD CLASSFP MTFCC \\\n", "0 487 01384029 Wilbarger 48487 Wilbarger County 06 H1 G4020 \n", "1 015 01383793 Austin 48015 Austin County 06 H1 G4020 \n", "2 047 01035620 Logan 38047 Logan County 06 H1 G4020 \n", "3 067 00198148 La Plata 08067 La Plata County 06 H1 G4020 \n", "4 121 00069178 Randolph 05121 Randolph County 06 H1 G4020 \n", "\n", " CSAFP CBSAFP METDIVFP FUNCSTAT ALAND AWATER INTPTLAT \\\n", "0 46900 A 2.514474e+09 18257915.0 +34.0849199 \n", "1 288 26420 A 1.674401e+09 25610780.0 +29.8919013 \n", "2 A 2.571371e+09 47715597.0 +46.4692780 \n", "3 20420 A 4.382664e+09 19545452.0 +37.2873673 \n", "4 A 1.689166e+09 9968439.0 +36.3412985 \n", "\n", " INTPTLON \n", "0 -099.2424397 \n", "1 -096.2701696 \n", "2 -099.5045846 \n", "3 -107.8397178 \n", "4 -091.0284409 " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "idageodf.head()\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Method 2: convert an IdaDataFrame with eligible geometry column into an IdaGeoDataFrame\n", "\n", "You can create an IdaGeoDataFrame by converting an IdaDataFrame which you gave an eligible geometry column. Note that an IdaGeoDataFrame inherits all the properties of an IdaDataFrame, so you could also simply open it as an IdaDataFrame if you do not need to manipulate its geospatial information, and convert it back any time into an IdaGeoDataFrame. For conversion, use the `IdaGeoDataFrame.from_IdaDataFrame` function. Note that in any case, an IdaGeoDataFrame is characterized by the presence of a column with geometry type: its data type belongs to the `ST_Geometry` family (like in Db2 : `db2gse.ST_Geometry`). " ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "# here we open the Db2 table SAMPLES.GEO_COUNTY as IdaDataFrame\n", "idadf = IdaDataFrame(idadb, 'SAMPLES.GEO_COUNTY', indexer='OBJECTID')" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "# now we convert this IdaDataFrame into an IdaGeoDataFrame\n", "# we set the 'SHAPE' column as geometry\n", "geo_idadf = IdaGeoDataFrame.from_IdaDataFrame(idadf, geometry = \"SHAPE\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "__Understand the geometry column__\n", "\n", "Have a look at the data types : you see that the `SHAPE` column is of type `ST_MULTIPOLYGON`. What does that mean? It is a geospatial data type inherited from Db2gse, Db2 Spatial Extender." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TYPENAME
OBJECTIDINTEGER
SHAPEST_MULTIPOLYGON
STATEFPVARCHAR
COUNTYFPVARCHAR
COUNTYNSVARCHAR
NAMEVARCHAR
GEOIDVARCHAR
NAMELSADVARCHAR
LSADVARCHAR
CLASSFPVARCHAR
MTFCCVARCHAR
CSAFPVARCHAR
CBSAFPVARCHAR
METDIVFPVARCHAR
FUNCSTATVARCHAR
ALANDDECIMAL
AWATERDECIMAL
INTPTLATVARCHAR
INTPTLONVARCHAR
\n", "
" ], "text/plain": [ " TYPENAME\n", "OBJECTID INTEGER\n", "SHAPE ST_MULTIPOLYGON\n", "STATEFP VARCHAR\n", "COUNTYFP VARCHAR\n", "COUNTYNS VARCHAR\n", "NAME VARCHAR\n", "GEOID VARCHAR\n", "NAMELSAD VARCHAR\n", "LSAD VARCHAR\n", "CLASSFP VARCHAR\n", "MTFCC VARCHAR\n", "CSAFP VARCHAR\n", "CBSAFP VARCHAR\n", "METDIVFP VARCHAR\n", "FUNCSTAT VARCHAR\n", "ALAND DECIMAL\n", "AWATER DECIMAL\n", "INTPTLAT VARCHAR\n", "INTPTLON VARCHAR" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Take a look at the datatypes of our dataset\n", "idageodf.dtypes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note on geospatial data types\n", "\n", "* The simplest spatial data item consists of two coordinates which define the position of a single geographic feature. It is denoted with the type `ST_Point`. You may also define 3D points with an angle measure, it will still be of type `ST_POINT`. \n", "\n", "* More extensive spatial data items may consist of several coordinates that define a linear path (such as a road or river), they are denoted as `ST_LineString`, or may consist of coordinates that define the boundary of an area (for example, the boundary of a land parcel or flood plain), they are denoted as `ST_Polygon`. Each spatial data item is an instance of a spatial data type. \n", "\n", "* A collection of such instances can also be defined: `ST_MultiLineString` for the union of several `ST_LineString` objects, `ST_MultiPolygon` for the union of several `ST_Polygon`objects... Spatial data types are structured types that belong to a single hierarchy `ST_Geometry`. \n", "\n", "* If a column has mixed data types (for example, `ST_LineString`and `ST_MultiLineString`, or `ST_Polygon`and `ST_Point`), then the type `ST_Geometry` is attributed to the column.\n", "\n", "A dataset may have several columns with a geospatial datatype. However, only one column can be defined as *the* geometry column of the IdaGeoDataFrame. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Defining the geometry column\n", "\n", "An IdaGeoDataFrame has by definition at least one column which can be defined as geometry. However the geometry is not attributed by default. It needs to be set explicitly. Here are two ways to define a geometry column.\n", "\n", "1. When defining the IdaGeoDataFrame, use the `geometry` option:\n", "> idageodf = IdaGeoDataFrame(idadb, \"TABLENAME\", geometry = \"GEOM_COL\")\n", "\n", "2. Or afterwards, use `set_geometry`:\n", "> idageodf.set_geometry('GEOM_COL')\n", "\n", "If you want to switch the geometry column:\n", "> idageodf.set_geometry('OTHER_GEOM_COL')" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Geometry property has not been set yet.\n" ] } ], "source": [ "# Has a geometry column already been set?\n", "try:\n", " idageodf.geometry.column\n", "except:\n", " print(\"Geometry property has not been set yet.\")" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "# Explicitly designate a column as geometry.\n", "idageodf.set_geometry('SHAPE')" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'SHAPE'" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Check the change.\n", "idageodf.geometry.column" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Congratulations! You have made your first steps with IdaGeoDataFrames, ibmdbpy's abstraction dedicated to geospatial data. Let's see how to manipulate them and what interesting features ibmdbpy offers!" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3. Learn how to handle IdaGeoDataFrames \n", "\n", "There are two types of functions you can apply to IdaGeoDataFrames. \n", "* Single-input functions work on a single IdaGeoDataFrame with one spatial column.\n", "* Double-input functions can either work on a single IdaGeoDataFrame with two spatial columns or two different IdaGeoDataFrames with one spatial column each. \n", "\n", "When calling methods to perform operations on IdaGeoDataFrames, these operations will automatically be performed on the chosen geometry column(s). \n", "\n", "For example, if you want to compute the area of a county, provided that the geometry column contains the coordinates of each county as a collection of polygons, calling `idageodf.area()` will compute the area of each county. \n", "> idageodf[\"AREA\"] = idageodf.area()\n", "\n", "A new column called `AREA` is created in the Db2 table. The area is computed on the basis of the collection of polygons contained in the `SHAPE` column of the IdaGeoDataFrame, which we have defined as the geometry column.\n", "\n", "But if you want to perform an operation on a geospatial column which is not defined as *the* geometry column, then you may either set this column as the new geometry column, or explicitly specify this column as the targeted IdaGeoSeries of the method you call. \n", "> idageodf[\"AREA\"] = idageodf[\"SHAPE\"].area()\n", "\n", "Under the hood, an SQL statement using `db2gse.ST_AREA` function is executed. You can print this statement by enabling the verbose option.\n", "\n", "Let's get familiar with IdaGeoDataFrame manipulation!" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "__Operations on a single IdaGeoDataFrame__\n", "\n", "Here we create new columns by applying methods directly to our IdaGeoDataFrame, as explained above." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Compute the area of each county and store the information in a new column\n", "\n", "We apply the `area` method as explained in this section's introduction." ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "# optional : define a unit, here the area will be given in squared kilmometers.\n", "\n", "idageodf['AREA_KM2'] = idageodf.area(unit='KILOMETER')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The `DB2GSE.ST_AREA()`function has been applied to the `SHAPE` column in order to compute the area associated to each polygon. The output of this function is an IdaGeoSeries. We have obtained a new column that is added to the original IdaGeoDataFrame, meaning the underlying Db2 table has been physically modified. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Compute the coordinates of the line defining each county's boundary \n", "\n", "We apply the `boundary` method to our `SHAPE` column." ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "idageodf['BOUNDARIES'] = idageodf.boundary()" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
OBJECTIDSHAPESTATEFPCOUNTYFPCOUNTYNSNAMEGEOIDNAMELSADLSADCLASSFP...CSAFPCBSAFPMETDIVFPFUNCSTATALANDAWATERINTPTLATINTPTLONAREA_KM2BOUNDARIES
01MULTIPOLYGON (((-99.4756582604 33.8340108094, ...4848701384029Wilbarger48487Wilbarger County06H1...46900A2.514474e+0918257915.0+34.0849199-099.24243972531.368865LINESTRING (-99.4756582604 33.8340108094, -99....
12MULTIPOLYGON (((-96.6219873342 30.0442882117, ...4801501383793Austin48015Austin County06H1...28826420A1.674401e+0925610780.0+29.8919013-096.27016961741.293545LINESTRING (-96.6219873342 30.0442882117, -96....
23MULTIPOLYGON (((-99.4497297204 46.6316377481, ...3804701035620Logan38047Logan County06H1...A2.571371e+0947715597.0+46.4692780-099.50458462618.142954LINESTRING (-99.4497297204 46.6316377481, -99....
34MULTIPOLYGON (((-107.4817473750 37.0000108736,...0806700198148La Plata08067La Plata County06H1...20420A4.382664e+0919545452.0+37.2873673-107.83971784404.975320LINESTRING (-107.4817473750 37.0000108736, -10...
45MULTIPOLYGON (((-91.2589262966 36.2578866492, ...0512100069178Randolph05121Randolph County06H1...A1.689166e+099968439.0+36.3412985-091.02844091701.895596LINESTRING (-91.2589262966 36.2578866492, -91....
\n", "

5 rows × 21 columns

\n", "
" ], "text/plain": [ " OBJECTID SHAPE STATEFP \\\n", "0 1 MULTIPOLYGON (((-99.4756582604 33.8340108094, ... 48 \n", "1 2 MULTIPOLYGON (((-96.6219873342 30.0442882117, ... 48 \n", "2 3 MULTIPOLYGON (((-99.4497297204 46.6316377481, ... 38 \n", "3 4 MULTIPOLYGON (((-107.4817473750 37.0000108736,... 08 \n", "4 5 MULTIPOLYGON (((-91.2589262966 36.2578866492, ... 05 \n", "\n", " COUNTYFP COUNTYNS NAME GEOID NAMELSAD LSAD CLASSFP ... \\\n", "0 487 01384029 Wilbarger 48487 Wilbarger County 06 H1 ... \n", "1 015 01383793 Austin 48015 Austin County 06 H1 ... \n", "2 047 01035620 Logan 38047 Logan County 06 H1 ... \n", "3 067 00198148 La Plata 08067 La Plata County 06 H1 ... \n", "4 121 00069178 Randolph 05121 Randolph County 06 H1 ... \n", "\n", " CSAFP CBSAFP METDIVFP FUNCSTAT ALAND AWATER INTPTLAT \\\n", "0 46900 A 2.514474e+09 18257915.0 +34.0849199 \n", "1 288 26420 A 1.674401e+09 25610780.0 +29.8919013 \n", "2 A 2.571371e+09 47715597.0 +46.4692780 \n", "3 20420 A 4.382664e+09 19545452.0 +37.2873673 \n", "4 A 1.689166e+09 9968439.0 +36.3412985 \n", "\n", " INTPTLON AREA_KM2 \\\n", "0 -099.2424397 2531.368865 \n", "1 -096.2701696 1741.293545 \n", "2 -099.5045846 2618.142954 \n", "3 -107.8397178 4404.975320 \n", "4 -091.0284409 1701.895596 \n", "\n", " BOUNDARIES \n", "0 LINESTRING (-99.4756582604 33.8340108094, -99.... \n", "1 LINESTRING (-96.6219873342 30.0442882117, -96.... \n", "2 LINESTRING (-99.4497297204 46.6316377481, -99.... \n", "3 LINESTRING (-107.4817473750 37.0000108736, -10... \n", "4 LINESTRING (-91.2589262966 36.2578866492, -91.... \n", "\n", "[5 rows x 21 columns]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Take a look at the new columns!\n", "idageodf.head()" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TYPENAME
AREA_KM2DOUBLE
BOUNDARIESST_GEOMETRY
\n", "
" ], "text/plain": [ " TYPENAME\n", "AREA_KM2 DOUBLE\n", "BOUNDARIES ST_GEOMETRY" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# What is the data type of our new columns?\n", "idageodf[['AREA_KM2','BOUNDARIES']].dtypes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "As you see, the area is a number with type DOUBLE and the boundaries are displayed as `ST_LineString` objects in the view in the cell above. So you may wonder why our `BOUNDARIES` column has the `ST_GEOMETRY` datatype. The reason is `db2gse.ST_BOUNDARY` function, which is used under the hood by ibmdbpy. This function returns by definition a column of data type `ST_GEOMETRY`, whatever the input data type.\n", "\n", "Reminder: Our dataset now has two eligible geometry columns, but there is no ambiguity about which is used because we have explicitly defined the `SHAPE` column as geometry. We can change this setting any time if needed with `set_geometry`." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "__SQL queries on a single IdaGeoDataFrame__\n", "\n", "Calling geospatial methods directly on the IdaGeoDataFrame works because we have explicitly set the geometry column, so ibmdbpy knows which feature to pick to perform the requested action. Alternatively, you can write custom SQL queries using db2gse to manipulate the tables. Here is an example. " ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
OBJECTIDBOUNDARIES
01LINESTRING (-99.4756582604 33.8340108094, -99....
12LINESTRING (-96.6219873342 30.0442882117, -96....
23LINESTRING (-99.4497297204 46.6316377481, -99....
34LINESTRING (-107.4817473750 37.0000108736, -10...
45LINESTRING (-91.2589262966 36.2578866492, -91....
56LINESTRING (-123.3609766418 45.7796744083, -12...
67LINESTRING (-81.6941497935 39.8426437809, -81....
78LINESTRING (-87.7623450952 44.6770233072, -87....
89LINESTRING (-72.1021669126 42.0288105765, -72....
\n", "
" ], "text/plain": [ " OBJECTID BOUNDARIES\n", "0 1 LINESTRING (-99.4756582604 33.8340108094, -99....\n", "1 2 LINESTRING (-96.6219873342 30.0442882117, -96....\n", "2 3 LINESTRING (-99.4497297204 46.6316377481, -99....\n", "3 4 LINESTRING (-107.4817473750 37.0000108736, -10...\n", "4 5 LINESTRING (-91.2589262966 36.2578866492, -91....\n", "5 6 LINESTRING (-123.3609766418 45.7796744083, -12...\n", "6 7 LINESTRING (-81.6941497935 39.8426437809, -81....\n", "7 8 LINESTRING (-87.7623450952 44.6770233072, -87....\n", "8 9 LINESTRING (-72.1021669126 42.0288105765, -72...." ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# here we only select the first 9 rows\n", "idadb.ida_query('SELECT OBJECTID, DB2GSE.ST_BOUNDARY(SHAPE) AS BOUNDARIES FROM (SELECT \"OBJECTID\",\"SHAPE\" FROM SAMPLES.GEO_COUNTY WHERE (\"OBJECTID\" < 10))')\n", " " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "__Operations on two IdaGeoDataFrames__\n", "\n", "* Preparation step\n", "\n", "Here we create two IdaGeoDataFrames that we will use to showcase a new type of functionality.\n", "Using a filtering statement, we obtain ida1 (county name is Austin) and ida2 (county name is Kent)." ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Shape of ida1:\n", "(1, 21)\n", "Shape of ida2:\n", "(5, 21)\n" ] } ], "source": [ "# Filtering on NAME\n", "ida1 = idageodf[idageodf['NAME'] == 'Austin']\n", "ida2 = idageodf[idageodf['NAME'] == 'Kent']\n", "\n", "# How many rows in each dataframe?\n", "print(\"Shape of ida1:\")\n", "print(ida1.shape)\n", "print(\"Shape of ida2:\")\n", "print(ida2.shape)" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
OBJECTIDSHAPESTATEFPCOUNTYFPCOUNTYNSNAMEGEOIDNAMELSADLSADCLASSFP...CSAFPCBSAFPMETDIVFPFUNCSTATALANDAWATERINTPTLATINTPTLONAREA_KM2BOUNDARIES
0109MULTIPOLYGON (((-85.7825032878 42.7682081077, ...2608101622983Kent26081Kent County06H1...26624340A2.195539e+0962785346.0+43.0324971-085.54744602258.324412LINESTRING (-85.7825032878 42.7682081077, -85....
1163MULTIPOLYGON (((-71.3805426551 41.6503345267, ...4400301219778Kent44003Kent County06H4...14839300N4.365120e+0850723547.0+41.6689489-071.5776343484.777990LINESTRING (-71.3805426551 41.6503345267, -71....
21840MULTIPOLYGON (((-100.5174574836 33.3978716832,...4826301383917Kent48263Kent County06H1...A2.337522e+091065234.0+33.1847797-100.76971992338.550487LINESTRING (-100.5174574836 33.3978716832, -10...
32231MULTIPOLYGON (((-75.1384627071 39.0027048751, ...1000100217271Kent10001Kent County06H1...42820100A1.518598e+09549069137.0+39.0970884-075.50298192080.430658LINESTRING (-75.1384627071 39.0027048751, -75....
42939MULTIPOLYGON (((-75.7560059150 39.2460739025, ...2402900593907Kent24029Kent County06H1...A7.175112e+08353307672.0+39.2412793-076.12598671076.193887LINESTRING (-75.7560059150 39.2460739025, -75....
\n", "

5 rows × 21 columns

\n", "
" ], "text/plain": [ " OBJECTID SHAPE STATEFP \\\n", "0 109 MULTIPOLYGON (((-85.7825032878 42.7682081077, ... 26 \n", "1 163 MULTIPOLYGON (((-71.3805426551 41.6503345267, ... 44 \n", "2 1840 MULTIPOLYGON (((-100.5174574836 33.3978716832,... 48 \n", "3 2231 MULTIPOLYGON (((-75.1384627071 39.0027048751, ... 10 \n", "4 2939 MULTIPOLYGON (((-75.7560059150 39.2460739025, ... 24 \n", "\n", " COUNTYFP COUNTYNS NAME GEOID NAMELSAD LSAD CLASSFP ... CSAFP CBSAFP \\\n", "0 081 01622983 Kent 26081 Kent County 06 H1 ... 266 24340 \n", "1 003 01219778 Kent 44003 Kent County 06 H4 ... 148 39300 \n", "2 263 01383917 Kent 48263 Kent County 06 H1 ... \n", "3 001 00217271 Kent 10001 Kent County 06 H1 ... 428 20100 \n", "4 029 00593907 Kent 24029 Kent County 06 H1 ... \n", "\n", " METDIVFP FUNCSTAT ALAND AWATER INTPTLAT INTPTLON \\\n", "0 A 2.195539e+09 62785346.0 +43.0324971 -085.5474460 \n", "1 N 4.365120e+08 50723547.0 +41.6689489 -071.5776343 \n", "2 A 2.337522e+09 1065234.0 +33.1847797 -100.7697199 \n", "3 A 1.518598e+09 549069137.0 +39.0970884 -075.5029819 \n", "4 A 7.175112e+08 353307672.0 +39.2412793 -076.1259867 \n", "\n", " AREA_KM2 BOUNDARIES \n", "0 2258.324412 LINESTRING (-85.7825032878 42.7682081077, -85.... \n", "1 484.777990 LINESTRING (-71.3805426551 41.6503345267, -71.... \n", "2 2338.550487 LINESTRING (-100.5174574836 33.3978716832, -10... \n", "3 2080.430658 LINESTRING (-75.1384627071 39.0027048751, -75.... \n", "4 1076.193887 LINESTRING (-75.7560059150 39.2460739025, -75.... \n", "\n", "[5 rows x 21 columns]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ida2.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Comment: there are 5 counties called Kent in the US. If you look at the `STATEFP` column containing the FIPS codes, you can tell that they are located in Michigan, Rhode Island, Texas, Delaware and Maryland respectively." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Mutual distance computation on two IdaGeoDataFrames\n", "\n", "So we have two IdaGeoDataFrames, the first one with 1 row only, the second one with 5 rows in total. Let's use the `distance` method to compute the distance between any county of the first dataset and any county of the second dataset. We apply it on ida1, with ida2 as argument, and obtain a new table. The output is an IdaGeoDataFrame containing the row IDs of each element from the input datasets, and the computed result.\n", "\n", "Okay, but what exactly does this distance actually stands for? Under the hood, ibmdbpy executes an SQL query using db2gse.ST_DISTANCE. This geospatial function from Db2 returns the shortest distance between any point in the first geometry to any point in the second geometry, measured in the default or given units. Here we have chosen to output the result in kilometers." ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "column1_for_db2gse: SHAPE\n", "column2_for_db2gse: SHAPE\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
INDEXERIDA1INDEXERIDA2RESULT
021091047.000738
121631572.738833
2222311308.108878
3229391282.652624
421840305.497341
\n", "
" ], "text/plain": [ " INDEXERIDA1 INDEXERIDA2 RESULT\n", "0 2 109 1047.000738\n", "1 2 163 1572.738833\n", "2 2 2231 1308.108878\n", "3 2 2939 1282.652624\n", "4 2 1840 305.497341" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result = ida1.distance(ida2,unit = 'MILE')\n", "result.head()" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n" ] } ], "source": [ "print(type(result))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Many more functionalities are available: intersections, overlaps etc. Take a look at [ibmdbpy documentation](https://pythonhosted.org/ibmdbpy/geoFrame.html) to learn more! An **extensive guide** is also provided in the next notebook of this series." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "____\n", "\n", "__Final step: Close the connection__\n", "\n", "Closing the IdaDataBase object is equivalent to closing the connection: once the connection is closed, it is not possible anymore to use the IdaDataBase instance and any IdaDataFrame instances that were opened in this notebook. Only the changes which have physically impacted the database remain.\n", "\n", "So far, all the modifications you made are visible in the notebook as query outputs or as temporary views that have automatically been dropped after use. For example, no table corresponding to ida1, ida2 or their distances have been saved, so once the connection to the database is closed, none of the changes will remain in Db2. If you want to save a particular table to Db2, use :\n", "> idadf.save_as(\"TABLE_NAME\", clear_existing=True|False)" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Connection closed.\n" ] } ], "source": [ "idadb.close()\n", "# idadb.reconnect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Where to go from here?\n", "\n", "Congratulations! You are now familiar with the basic functionalities of ibmdbpy's geospatial extension! You are ready to get hands-on experience by playing with other notebooks of this series:\n", "\n", "* Getting started with ibmdbpy: \n", " \n", " [Basics](./ibmdbpy_GettingStarted_1-basics.ipynb)\n", " \n", " \n", "* More on ibmdbpy's geospatial functions:\n", "\n", " [Extensive guide](./ibmdbpy_GettingStarted_3-geo_guide.ipynb)\n", " \n", "\n", "* Ibmdbpy in practice : analyze the Museums dataset, understand how to create IdaDataFrames and IdaGeoDataFrame:\n", " \n", " [Preprocessing](../MuseumsUseCase/ibmdbpy_Museums_DataAnalysis_1-preprocessing.ipynb)\n", "\n", " [Geospatial recommendation](../MuseumsUseCase/ibmdbpy_Museums_DataAnalysis_2-geospatial.ipynb)\n", "\n", "\n", "* Machine learning with ibmdbpy: \n", " \n", " [Naïve Bayes](../MachineLearning/ibmdbpy_NaiveBayes.ipynb)\n", "\n", " [Association Rules Mining](../MachineLearning/ibmdbpy_AssociationRulesMining.ipynb)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "____\n", "\n", "__Authors__\n", "\n", "Eva Feillet - ML intern, IBM Cloud and Cognitive Software @ IBM Lab in Böblingen, Germany" ] } ], "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.3" } }, "nbformat": 4, "nbformat_minor": 2 }