Tutorial 3: Using your own geospatial data

1. The Db2 web console provides a wizard that you can use to load shapefiles into your Db2 database. To access this page, go back to the Db2 welcome page and open the menu there. Click Load, then Load Geospatial Data.
2. This opens the first page of the wizard for loading geospatial data. The source file must be a compressed .zip, .gz, or .tar file. Hover your pointer over the question mark icon to display the other requirements of the source file. For example, each file in the shapefile set must have the same base name and must be in the root directory of the compressed file.
   
3. Click Browse files, select the compressed file that contains the geospatial data to be loaded, and click Open.
   
4. When the name of the file is displayed in the File Name field, click Next.
   
5. Db2 uploads the data and displays a check mark when the process has finished. Then, click Next.
   
6. On this page, specify the name of the table that is to contain the uploaded geospatial data. In this scenario, we'll name the table GEO_WEATHER_WARNINGS.
   
7. Db2 displays the spatial reference systems that your uploaded table can use. If no spatial reference system matches your data, you can create a new one as needed. In this scenario, select the WGS84_SRS_1003 entry and click Next.
   
8. After you click Finish, you are informed that the load was successful and how many rows the table contains. If you scroll down, you can see a preview of the table.
   
9. If you want even more information, you can click View full table structure and details. The Tables page opens.
   
10. Select the schema and then select the new table that you just created for your geospatial data. When you select the table, Db2 displays a list of its columns.
11. To view the data contained in the table, click the Browse Data tab. When you're finished reviewing the data, return to ArcMap.

1. Right-click your database connection and click Refresh.
   
2. 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.
   
3. 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.
   
4. 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.
5. 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 
        

6. 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.
   
7. In the resulting map, the darkness of each warning region indicates the degree to which it has a high risk of insured property loss.
   
8. 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 
        )
        

9. 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.
   
10. 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.