IBM Db2 Warehouse is a high performing, analytic engine that combines in-memory processing with integrated massively parallel in-database analytics. Db2 Warehouse has row-based and columnar storage options for table data and can also store and manage spatial data. In-database analytics capabilities in Db2 Warehouse include open source analytics with R and Apache Spark, out-of-box analytic stored procedures, and geospatial analytic functions.

Spatial data (stored as spatial features) complements traditional data warehouse data and helps conduct location-aware analytics. A geographic feature is a representation of real-world objects with an identifiable location. Some examples of geographic features that can be managed and analyzed with Db2 Warehouse include:

  • An object, such as a building, river, forest, or mountains.
  • A space, such as a safety zone around a hazardous site.
  • An event at a specific location, such as an auto accident or a sales transaction at a store.

Db2 Warehouse supports multiple spatial geometries including Points, Linestrings, Polygons, Multipoints, Multilinestrings, Multipolygons, and a variety of spatial analytic functions specific to the different geometries.

Creating tables and managing spatial data in Db2 Warehouse

Spatial objects are stored as geometries in Db2 Warehouse. ST_Point is one type of spatial geometry representing a location (Longitude, Latitude) of a person or object (in this case, it represents a customers location at a given point in time). The following code listing creates a spatial column that contains only ST_Point values, inserts some points, and then selects the data stored in the table.

CREATE TABLE customer_location (customer_id INT, geom db2gse.ST_Point) organize by row;

insert into customer_location values(1, db2gse.ST_Point(37.475,-122.332,4326));
insert into customer_location values(2, db2gse.ST_Point(37.362,-122.305,4326));
insert into customer_location values(3, db2gse.ST_Point(37.601,-122.415,4326));
insert into customer_location values(4, db2gse.ST_Point(37.879,-122.546,4326));
insert into customer_location values(5, db2gse.ST_Point(37.226,-122.228,4326));
insert into customer_location values(6, db2gse.ST_Point(37.125,-121.975,4326));
insert into customer_location values(7, db2gse.ST_Point(38.217,-122.772,4326));
insert into customer_location values(8, db2gse.ST_Point(37.231,-121.558,4326));
insert into customer_location values(9, db2gse.ST_Point(37.038,-122.003,4326));

Note: For use with your data, make sure to exclude null values.

You can manage and query spatial data with SQL. With Mapbox, you can visually inspect and interact with spatial data. Mapbox is a cross-industry developer platform that you can use to create custom applications that solve problems with maps, data, and spatial analytics. This tutorial shows you how to use a combination of Mapbox API’s and Db2 Warehouse’s open interfaces to read and display Db2 Warehouse spatial data using a Mapbox-based web application.

High-level solution architecture

Mapbox can use geoJSON from an HTTP/HTTPS data source as the spatial data format to display geometries on a map. In this tutorial, you use a combination of the Db2 Warehouse REST interface and open source R integration to read spatial data from Db2 Warehouse tables, generate geoJSON for spatial geometries, and display the points on a Mapbox map.

Note: We leverage in-database R to generate geoJSON, so we need to have R installed on Db2 Warehouse using these instructions.

flow

R script to read spatial data from Db2 Warehouse and generate geoJSON

Save the following code snippet on your local file system, name the file getDb2SpatialData.R, and adjust connection and schema information. This R script is then uploaded to Db2 Warehouse and executed using the Db2 Warehouse REST interface.

# Load Db2 Warehouse R library/package
suppressMessages(library(ibmdbR))
# Connect to Db2 database
con <- idaConnect('BLUDB','','')
idaInit(con)

# Execute SELECT to retrieve spatial geometries from Db2 Warehouse table
spatialPointsData <- idaQuery('SELECT db2gse.st_Y( GEOM ) as long, db2gse.st_X( GEOM )
as lat FROM <YOUR_Db2_Warehouse_SCHEMA>.CUSTOMER_LOCATION')

# Loop through all available rows of data and generate geoJSON string
geoJSONStr<-""
for(i in 1:nrow(spatialPointsData)) {
geoJSONStr <-paste(geoJSONStr, '{ "type": "Feature", "geometry": {"type":"Point","coordinates":[',spatialPointsData[i,1], ',',spatialPointsData[i,2],']}, "properties": [] },')
}
coords<-substr(geoJSONStr[1],1,nchar(geoJSONStr[1]) - 1)
startStr<-'{"type": "FeatureCollection", "features": ['
endStr<-"}"
spatialPointsData<-paste(startStr, coords, endStr)

# Output geoJSON string for Mapbox and close database connection
print(spatialPointsData)
idaClose(con)

CURL command to upload R script to Db2 Warehouse

You can upload R scripts to Db2 Warehouse using the REST interface POST with either the CURL command (shown below) or programmatically with an HTTP POST.

curl --user "Db2User:Db2Password" -H "Content-type: multipart/form-data" -F 
"data=@getDb2SpatialData.R" -X POST 
"https://Db2WarehouseURL_Or_IP:8443/dashdb-api/home" --insecure

Note: This CURL command uses the --insecure option to disable certificate validation. Remove this option if you want to use the secure option with a valid SSL certificate.

Mapbox has an extensive API to display spatial data and conduct spatial analytic functions. The high-level steps to display spatial data stored in Db2 Warehouse using Mapbox include:

  • Creating a simple web page (in HTML format) with a placeholder (DIV tag) for the Mapbox map.
  • Writing a simple Javascript function getDb2Points() to read the spatial data from Db2 Warehouse in geoJSON format.
  • Get a Mapbox access token to use Mapbox in your web page (you need to replace the in the getDb2Points() function).
  • Open the web page using a web browser.

Web page to display a Mapbox map with spatial data from Db2 Warehouse

The following listing is an HTML template for web page displaying Mapbox maps.

<html>
<head>
    <meta charset='utf-8' />
    <meta name='viewport' content='initial-scale=1,maximum-scale=1,user-scalable=no' />
    <script src='https://api.tiles.mapbox.com/mapbox-gl-js/v0.42.2/mapbox-gl.js'></script>
    <link href='https://api.tiles.mapbox.com/mapbox-gl-js/v0.42.2/mapbox-gl.css' rel='stylesheet' />
    <style>
    body {
        margin: 0;
        padding: 0;
    }

    #map {
        position: absolute;
        top: 0;
        bottom: 0;
        width: 100%;
    }
    </style>
</head>

<body onload="JavaScript:getDb2Points();">
    <div id='map'></div>
</body>

</html>

JavaScript function to call the Db2 Warehouse R script

The following JavaScript function makes a REST POST call to execute the R script. This R script reads the spatial data from Db2 Warehouse, converts the spatial data to geoJSON, and displays it on a Mapbox map.

var jsonResult='';
    var jsonResultObj='';
    var geoJSON='';
    var mapIcons = [
        "marker-15",
        "rocket-15",
        "bar-15"
    ];

    function escapeRegExp(str) {
        return str.replace(/([.*+?^=!:${}()|\[\]\/\\])/g, "\\$1");
    }

    function replaceAll(str, find, replace) {
        return str.replace(new RegExp(escapeRegExp(find), 'g'), replace);
    }

    function getDb2Points()
    {
    var req = new XMLHttpRequest();
    req.open("POST", "https://<Db2WarehouseURL_Or_IP>:8443/dashdb-api/rscript/getDb2SpatialData.R", true);
        req.setRequestHeader("Authorization", "Basic " +
btoa("<Db2User>:<Db2Password>"));
        req.send();
        req.onreadystatechange= function () {
          if(req.readyState === XMLHttpRequest.DONE && req.status === 200) {
        jsonResult=req.responseText;
        jsonResultObj=JSON.parse(jsonResult);
        geoJSON=jsonResultObj.result.rScriptOutput;
        geoJSON=replaceAll(geoJSON,'\\','');
        geoJSON=geoJSON.replace('\}"',']}');
        geoJSON=geoJSON.trim().substr(5,geoJSON.length-4);
        geoJSON=JSON.parse(geoJSON);
        showMap();
                                      }
        };
    }

    function showMap()
    {
        var randomIcon = mapIcons[Math.floor(Math.random()*mapIcons.length)];

        mapboxgl.accessToken = ‘<YOUR_MAPBOX_ACCESS_TOKEN>’;
        var map = new mapboxgl.Map({
            container: 'map',
            style: 'mapbox://styles/mapbox/streets-v10?optimize=true',
            center: [-122.546,37.879],
            zoom: 8
        });
        map.on('style.load', function() {
            // Add a symbol layer.

            map.addSource('data', {
                "type": "geojson",
                "data": geoJSON
            });

            map.addLayer({
                "id": "symbols",
                "type": "symbol",
                "source": "data",
                "layout": {
                    "icon-image": randomIcon,
                    "icon-size": 2
                }
            });

    // Center the map on the coordinates of any clicked symbol from the 'symbols' layer.
            map.on('click', 'symbols', function(e) {
                map.easeTo({ center: e.features[0].geometry.coordinates });
            });

    // Change the cursor to a pointer when it enters a feature in the 'symbols' layer.
            map.on('mouseenter', 'symbols', function() {
                map.getCanvas().style.cursor = 'pointer';
            });

    // Change it back to a pointer when it leaves.
            map.on('mouseleave', 'symbols', function() {
                map.getCanvas().style.cursor = '';
            });

        });
    }
</script>

Web page with Mapbox displaying customer locations managed in Db2 Warehouse

The resulting web page reads customer locations stored in Db2 Warehouse and then displays their locations using Mapbox.

Map with pinpoints where customers are

This tutorial showed you how to visualize spatial data stored in Db2 Warehouse using Mapbox. You can use these techniques to incorporate spatial information as a part of reports, corporate dashboards, and more. This tutorial is a simple example of what you can do with Db2 Warehouse and Mapbox integration. Stay tuned for additional tutorials that demonstrate the value and use of spatial analytics with Db2 Warehouse.

Experience the power of Db2 Warehouse, complete with in-database Apache Spark and Spatial Analytics from the IBM Docker Store. https://store.docker.com/images/ibm-db2-warehouse-dev