Skip to Content
Actions

Serving up Apples & Pears: Spatial Data and D3

February 17, 2014 | 85 Views |
 Databasegeojsongeospatialhana
Follow

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!!!

Capture.JPG

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. 😎

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  2. Aron MacDonald Post author

    If anyone wants to check if your city is in a shape then can test with similar syntax The ST_POINT represents the Longitude & Latitude of your location of choice):

    — London is NOT inside the Apple  RETURNS 0

    select shape.ST_Contains(NEW ST_Point( -0.22083, 51.85333)) from  SpatialShapes where name = ‘Apple’;  

    — LA IS inside the Apple   RETURNS 1

    select shape.ST_Contains(NEW ST_Point(  -118.41667, 34.08333 )) from  SpatialShapes where name = ‘Apple’;  

    (0) 
  3. Jon-Paul Boyd

    Hi Aron,

    I’ve found that performing a ST_AsGeoJSON returns a truncated coordinate. 

    In your first example INSERT you have 115.80622866283737, you are probably getting back something like 115.80622?

    Either that or the shape is being truncated upon INSERT according to some stipulation of the SRID.

    (0) 
    1. Aron MacDonald Post author

      Hi Jon-Paul,

      That’s a very interesting observation.  I hadn’t noticed that.

      It probably doesn’t cause much of an issue zoomed out to show the world, but zoomed in at street level the accuracy lost may be important.

      I think the INSERT is ok because when using ST_AsWKT()  it returns it the same level of detail on may shapes..

      You can also compare with the following SQL:

      select NEW ST_Point(  -115.80622866283737, 50.396875473355024 ).ST_AsGeoJSON()  from dummy;

      Returns: {“type”: “Point”, “coordinates”: [-115.806,50.3969]}

      select NEW ST_Point(  -115.80622866283737, 50.396875473355024 ).ST_AsWKT()  from dummy;

      Returns: POINT (-115.806228662837370 50.396875473355024)

      I was then curious about the accuracy lost by ST_AsGeoJSON() . Unfortunately there isn’t a ST_DISTANCE_SPHERE function, but fortunately there is still ST_DISTANCE (which I think is  returns distance in degrees).  I think then with a rough and ready  formula to converting degrees into miles (1 degree = 60 minutes,  1 minute = 1 Mile, 1 Mile = 1852 Metres)  then I might be able to calculate the accuracy lost. [where would we be without Google]

      So perhaps the SQL to check the inaccuracy of ST_AsGeoJSON()  is:

      select NEW ST_Point(  -115.80622866283737, 50.396875473355024 ).ST_Distance(NEW ST_Point(  -115.8062, 50.3969)) * 60 * 1852 from dummy;

      Returns: 4.2388916015625  metres

      Interestingly the ST_AsGeoJSON() rounding  is also dependant on distance from 0.

      e.g.

      select NEW ST_Point(  1.00005, 1.00005 ).ST_AsGeoJSON()  from dummy;

      Returns: {“type”: “Point”, “coordinates”: [1.00005,1.00005]}

      select NEW ST_Point(  100.00005, 100.000005 ).ST_AsGeoJSON()  from dummy;

      Returns: {“type”: “Point”, “coordinates”: [100,100]}

      It looks like HANA ST_AsGeoJSON()  function handles a max 6 digits length. hmmmmm

      I wonder if there is some undocumented HANA feature to increase this???

      ——————————————————————————————————

      So IF my calculation is correct then the maximum default inaccuracy of ST_AsGeoJSON()  might be:

      select NEW ST_Point(  100, 100 ).ST_Distance(NEW ST_Point(  100.0005, 100.0005)) * 60 * 1852 from dummy;


      Result: 78.52546691894531  metres

      So looks like I can’t rely on ST_AsGeoJSON()  to plot fruit trees after all 😛  

      If I need to improve accuracy I may need to use ST_AsWKT()  and write some custom java script to reformat into GeoJson manually.  😡

      If you think I’ve made some glaringly mistake in my observation or distance formulation then I really welcome the feedback.

      Thanks

      Aron

      (0) 
      1. Jon-Paul Boyd

        Hi Aron, Im going to look at your calculations right now and get back to you.  Would you mind if we continue conversation absolutely specific to spatial data in the “SPS07 spatial data” thread, to centrally collate all the info and findings we get – it will form a bucket of info we can all use and help others find it?

        (0) 

Leave a Reply