Serving up Apples and Pears: Spatial Data and D3

D3 is a third party Data visualization tool included as standard in HANA XS.

SAPUI5 is great for standard stuff, but for going off-piste D3 is fantastic.  It just boggles my mind with what Mike Bostock has created and shared with the world. Where does he find the time?  Check out many of the great examples at http://d3js.org/

Mike: If you every read this.  Thank you, D3 is brilliant 🙂

Back in the pure realm of HANA SPS07 we now have more advance GIS features to use, check out http://help.sap.com/hana/SAP_HANA_Spatial_Reference_en.pdf

Using these feature it’s now possible to store geographic shapes, such as countries, regions, boundaries, buildings  and perhaps even fruit trees.

Rather than just serving up some fast data facts from HANA I thought why not serve up some fruit.

In the beginning I used HANA XS & D3 to create the world (based on http://bl.ocks.org/mbostock/3757119)

I then defined a spatial data table and inserted some fruit (spatial data shapes – Polygons).

Finally HANA spat the fruit out onto the earth, reformatted into GeoJson. Charming!!!

NOTE: No bitmap images are used in the creation of this. It is built exclusively with HANA XS and D3.

To recreate this on your HANA SPS7 box:

A) Create the world map with D3 in HTML. It’s pretty straight forward, you can follow any of Mikes many examples.  (My completed HTML is included at the end)

B) Create a Spatial table in HANA for storing the fruit

CREATE COLUMN TABLE SpatialShapes(

id integer, shape ST_GEOMETRY, name nvarchar(20), color nvarchar(20));

C) Use a GIS tool to create your polygon (e.g. http://www.openjump.org/) then save in a suitable format (see SAP_HANA_Spatial_Reference_en.pdf section 2.3.2)

D) Import or Insert the shape into the new HANA SpatialShapes table.

In my case I inserted 2 fruit:

APPLE

INSERT INTO SpatialShapes VALUES(1, new ST_POLYGON(‘POLYGON ((-115.80622866283737 50.396875473355024, -125.85683522021442 49.748449243846835, -132.34109751529638 50.721088588109126, -140.44642538414882 50.07266235860092, -150.82124505627996 47.15474432581405, -159.57499915464064 41.96733448974847, -166.38347456447673 30.29566235860094, -169.62560571201772 17.97556399794519, -168.97717948250948 -3.42250157582529, -165.41083522021444 -11.852042559431862, -159.25078603988652 -22.875288461071204, -151.4696712857882 -32.277468788940055, -144.98540899070622 -37.464878625005625, -137.20429423660784 -42.00386223156299, -130.07160571201769 -43.9491409200876, -122.29049095791935 -43.3007146905794, -115.15780243332917 -42.32807534631711, -108.3493270234931 -42.652288461071194, -97.97450735136196 -43.9491409200876, -88.8965401382472 -39.410157313530206, -80.46699915464065 -30.980616329923645, -72.68588440054229 -18.01209173975972, -68.1469007939849 -4.395140920087595, -66.20162210546032 9.870236129092735, -68.1469007939849 21.866121374994382, -73.98273685955867 36.455711538928796, -81.43963849890294 44.88525252253535, -90.5176057120177 49.748449243846835, -98.94714669562426 50.721088588109126, -105.75562210546032 51.36951481761733, -110.94303194152589 50.396875473355024, -112.2398844005423 50.721088588109126, -108.3493270234931 62.06854760450257, -108.02511390873902 65.95910498155176, -109.64617948250951 67.90438367007634, -111.59145817103409 68.55280989958455, -112.88831063005047 66.93174432581405, -112.56409751529638 64.33803940778127, -113.21252374480459 59.474842686469785, -114.83358931857508 53.963219735650114, -115.80622866283737 50.396875473355024))’), ‘Apple’, ‘red’);

PEAR

INSERT INTO SpatialShapes VALUES(2, new ST_POLYGON(‘POLYGON ((43.10892629676332 9.24977905375759, 36.948877116435455 2.441303643921527, 31.761467280369878 -6.312450454439132, 28.84354924758299 -14.417778323291587, 27.5466967885666 -24.792597995422724, 29.81618859184529 -37.43690947083256, 34.679385313156764 -48.78436848722602, 43.10892629676332 -57.53812258558666, 53.48374596889447 -62.401319306898145, 65.4796312147961 -64.34659799542273, 69.69440170659938 -61.75289307738994, 76.50287711643544 -60.456040618373535, 82.33871318200922 -62.72553242165223, 91.41668039512396 -64.02238488066864, 106.0062705590584 -59.15918815935716, 113.46317219840265 -52.02649963476698, 120.92007383774693 -37.11269635607847, 121.5685000672551 -20.577827503619446, 115.08423777217314 -2.746106192144042, 107.30312301807479 12.81612331605268, 100.81886072299282 25.460434791462518, 90.44404105086167 46.2100741357248, 86.5534836738125 47.5069265947412, 83.63556564102561 48.1553528242494, 81.04186072299282 50.42484462752808, 82.9871394115174 64.04179544720022, 82.01450006725511 78.63138561113463, 74.55759842791085 78.63138561113463, 74.23338531315676 69.87763151277399, 76.17866400168134 63.39336921769201, 75.20602465741905 58.530172496380544, 74.88181154266495 55.288041348839556, 74.55759842791085 53.666975775069076, 70.34282793610758 52.69433643080677, 65.15541810004201 51.39748397179038, 61.913286952501025 50.42484462752808, 59.31958203446824 47.8311397094953, 56.725877116435456 42.96794298818382, 43.10892629676332 9.24977905375759))’), ‘Pear’, ‘gold’);

Check the output with:

select *, shape.ST_AsGeoJSON() as “GeoJSON” from  SpatialShapes

E) Create a HANA XS Serverside Javascript GeoShapes.xsjs to serve up the shapes in GeoJson format

function getGeoShapes() {

  

       function createShapeEntry(rs) {

         

              var geometry = JSON.parse(rs.getNString(3)); // GeoJson is Object

         

              //D3 currently appears to need anti clockwise winding of Shapes

              //https://github.com/mbostock/d3/issues/1232

              geometry.coordinates[0] = geometry.coordinates[0].reverse();

         

              return {

                     “type”:“Feature”,“properties”: { “name” : rs.getNString(1), “color” : rs.getNString(2) },

                     “geometry”: geometry,

                     “id” : rs.getInteger(4)

              };

       }

       var body = ”;

       var list = [];

  

       try {

              var query = ‘select name, color, shape.ST_AsGeoJSON() as “GeoJSON”, id  from  <INSERT YOUR SCHEMA>.SpatialShapes where not name is null’; // and id = 23′;

                      

              var conn = $.db.getConnection();

              var pstmt = conn.prepareStatement(query);

              var rs = pstmt.executeQuery();

              while (rs.next()) {

                     list.push(createShapeEntry(rs));

              }

              rs.close();

              pstmt.close();

         

       } catch (e) {

              $.response.status = $.net.http.INTERNAL_SERVER_ERROR;

              $.response.setBody(e.message);

              return;

       }

       body = JSON.stringify({

              “type”:“FeatureCollection”,

              “features”: list

       });

       $.response.contentType = ‘application/json; charset=UTF-8’;

       $.response.setBody(body);

       $.response.status = $.net.http.OK;

}

var aCmd = $.request.parameters.get(‘cmd’);

switch (aCmd) {

default:

       getGeoShapes();

}

F) Finally, add the HANA shapes onto the world map html (marked in Blue)

<html><head> 

    <meta http–equiv=’X-UA-Compatible’ content=’IE=edge’ /> 

    <title>Hello World</title> 

 

    <script id=’sap-ui-bootstrap’

        src=’../../../sap/ui5/1/resources/sap-ui-core.js’ 

        data-sap-ui-theme=’sap_goldreflection’ 

        data-sap-ui–libs=’sap.ui.commons’></script>  

 

    <script src=”../../../sap/ui5/1/resources/sap/ui/thirdparty/d3.js”>

    <style type=”text/css“>

              svg {

                     width: 1280px;  //1980px;

                     height: 800px;  //800px

                    pointer-events: all;

              }

             

             

              path {

                     fill: #aaa;

                    stroke: #fff;

              }

    </style>        

 

<script> 

       var html1 = new sap.ui.core.HTML(“html1”, {

        // the static content as a long string literal

        content:

                           “<div class=’Chart’>” +

                     “</div>“

                ,

        preferDOM : false,                     

        // use the afterRendering event for 2 purposes

        afterRendering : function(e) {

       

                              

      

                     // WORLD CHART

                     var feature;

          

                     var vScale = 200;

                     var width = 1280,

                     height = 800;

           

                     var projection = d3.geo.equirectangular()

                      .scale(vScale)

                      .translate([640, 400]);

                    

                    

                     var path = d3.geo.path()

                         .projection(projection);

                    

                     // Custom Mode

                     var svg = d3.select(“.Chart”).append(“svg:svg”)

                         .attr(“width”, width)

                         .attr(“height”, height)

                         ;

                    

                    

                     var graticule = d3.geo.graticule();

                    

                    

                     //WORLD

                     d3.json(“world-countries.json”, function(collection) {

                       feature = svg.selectAll(“path.world”)

                           .data(collection.features)

                           .enter().append(“svg:path”)

                           .attr(“d”, path)

                           .attr(“class”, “world”)

                           .style(“stroke-width”, 2)

                                 .style(“stroke”, “white”)

                                 .style(“fill”, “LightSlateGray “)

                           ;

                      

                      

                       svg.append(“path”)

                         .datum(graticule)

                           .style(“fill”, “none”)

                           .style(“stroke”, “#777”)

                           .style(“stroke-width”, “.5px”)

                           .style(“stroke-opacity”, “.5”)

                           .attr(“d”, path);

                          

                       addHanaShapes();

                     });

           

                     function addHanaShapes() {

                           // Serving up HANA shape objects               

                           d3.json(“../services/GeoShapes.xsjs”, function(collection) {

      

      

                             feature = svg.selectAll(“path.hana”)

                               .data(collection.features)

                               .enter().append(“svg:path”)

                                 .attr(“d”, path)

                                  .attr(“class”, “hana“)

                                 .style(“stroke-width”, 0.5)

                                       .style(“stroke”, “black”)

                                       .style(“fill-opacity”, 0.25)

                                       .style(“fill”, function(d, i) {

                                           return d.properties.color;

                                         })

                                 ;

                    

      

                     });

              }

                    

                          

        }

    });

    html1.placeAt(‘content’); 

</script>

</head>

<body class=’sapUiBody’>

    <div id=’content’></div>

</body>

</html>

If you give it a go then please do let me know how you get on. 😎