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. 😎
Very fruity example of Hana spatial data consumption from a third party, well done!
Thanks. I thought serving up a whole salad was a bit OTT. 😛
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’;
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.
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
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?
no problem.