Tutorial 3: Using your own geospatial data

This is the last of a series of three tutorials that show you how to analyze geospatial data with Db2® and Esri ArcGIS:

If you want to learn how to work with base maps first, view the first tutorial of this series: Tutorial 1: Working with base maps.
Do you want to learn how to create your own maps? Then, view the second tutorial of this series: Tutorial 2: Creating new maps.

This tutorial shows you how to carry out the following tasks:

Load your own geospatial data into Db2.
Use uploaded data.

Time required

10 minutes

Scenario

You are a data scientist working for a large insurance company, and you want to analyze occurrences of tornadoes in the conterminous United States. You want to determine which counties have the most tornadoes. You also want to correlate tornado occurrences with customer data to determine in which counties your company is exposed to the highest level of risk from potential tornado damage settlements.

Difficulty

Beginner

Audience

Data scientists

System requirements

Windows 7 Ultimate, Professional and Enterprise (32 bit and 64 bit [EM64T])
Windows 8 Pro and Enterprise (32 bit and 64 bit [EM64T])
Windows 8.1 Pro and Enterprise (32 bit and 64 bit [EM64T])

Prerequisites

This scenario uses an Esri ArcGIS for Desktop installation that is connected to Db2 to visualize and analyze geospatial data (see Connecting Esri ArcGIS for Desktop). The data for this scenario is already loaded into your Db2 database. This scenario assumes that you already know how to work with base maps and how to create your own maps.

Preparing data for an individual geospatial load

Now, we'll show you how to load new geospatial data into your Db2 database, and how to put that data to use. But first, some background information: Geospatial data is typically made available in the form of shapefile sets. Many organizations such as the US Census Bureau and the US National Weather Service provide shapefile sets that can be downloaded from the internet.

For example, the US National Weather Service provides shapefile sets that contain data about current weather warnings. An insurance company could use this data to determine which customers are in danger and to warn them. This would give the customers time to secure their property and to prevent damage, which would benefit both the customers and the insurance company. You can download the files that contain geospatial data about current weather warnings from the National Weather Service Data Download in GIS Format page of the US National Weather Service website.

Procedure

The weather warning files are divided into four categories: warnings about tornadoes, warnings about thunderstorms, warnings about flash floods, and special marine warnings. Each shapefile set contains one SHP file, one DBF file, one SHX file, and one PRJ file. For this scenario, we downloaded the four files containing the geospatial data for tornado warnings from October 2014, and put them into a compressed file. You can download this file here.

Downloading a shapefile from the National Warning GIS Shapefiles page

Loading geospatial data into Db2 Warehouse SaaS

Procedure

Use the IDA LOADGEOSPATIALDATA command to load the downloaded shapefiles into your Db2 database.
After loading the data, return to ArcMap.

Using uploaded data

Procedure

Right-click your database connection and click Refresh.
Note that the table GEO_WEATHER_WARNINGS is now listed below the database in the catalog pane. Drag the new table to the table of contents and drop it above all the other layers.
The tornado warnings are represented as polygons on the map. Let's zoom in for a closer look. To access more detailed information about a warning, select the identify tool and click on a polygon.
In the window that is displayed, the WFILE row contains a link to the US National Weather Service website. Click the lightning bolt icon to the right of the link to open the website and view information about the warning, such as the name of the affected region.
We will now create a new query layer that will calculate the total value of the insurance policies in an endangered region. In this way, an insurance company could estimate the potential impact of a particular tornado.
Clear the check box for the GEO_WEATHER_WARNINGS layer, then select File > Add Data > Add Query Layer. Specify the name WEATHER_WARNING_RISK. Then, enter the SQL query in the query field:


       SELECT W.*, (
       SELECT SUM(C.INSURANCE_VALUE)
       FROM SAMPLES.GEO_CUSTOMER AS C
  	   WHERE DB2GSE.ST_INTERSECTS(W.GEO_DATA,C.SHAPE) = 1 SELECTIVITY 0.00001
       ) AS COUNTY_INSURANCE_VALUE  
       FROM GEO_WEATHER_WARNINGS AS W

When the map is updated, select the layer properties. On the Graduated colors page, assign the value COUNTY_INSURANCE_VALUE. Choose the blue color gradient and set the number of classes to 4. Then, click OK.
In the resulting map, the darkness of each warning region indicates the degree to which it has a high risk of insured property loss.
Now, let's create a query layer that shows which customers live in regions for which there is an active tornado warning. An insurance company could create a list of such customers and use it to contact them. Create a new query layer with the name POTENTIAL_UPCOMING_CLAIMS and enter the SQL query in the query field:


        SELECT *  FROM SAMPLES.GEO_CUSTOMER WHERE OBJECTID IN (
        SELECT DISTINCT C.OBJECTID
      	FROM SAMPLES.GEO_CUSTOMER AS C,
        GEO_WEATHER_WARNINGS AS W
       	WHERE DB2GSE.ST_INTERSECTS(W.GEO_DATA,C.SHAPE) = 1 SELECTIVITY 0.00001 
        )

Creating a query layer that contains affected customers by using SQL code

When the map is updated, select the layer properties. On the Graduated colors page, select the value with the name INSURANCE_VALUE. Choose a brown color gradient and set the number of classes to 4.
The customers are now color-coded based on the value of their insured property: A darker color corresponds to a higher property value. To display a list of customers, you can use the identify tool. Zoom in on one region. Then, select the identify-tool and use it to draw a frame that contains all the customers in the region. ArcMap displays a list of those customers. When you select a customer in the list, the location of that customer flashes green on the map.

Tutorial summary

You can load geospatial data into Db2 by using the Load Geospatial Data page. When you refresh your database connection, you can use the data that you loaded into Db2.