Introduction
The Hana Spatial Engine became GA (general availability) as of SPS07. Very exciting, no more ATAN2, SIN, COS and SQRT for me.
Let me give you a straightforward example of what is traditionally required to calculate the distance between two pairs of GPS latitude/longitude coordinates. This is my implementation in JavaScript:
myHplApp.controller.Distance = function(fromLatitude, fromLongitude, toLatitude, toLongitude) {
var earthRadiusMetres = 6371000; // in metres
var distanceLatitude = myHplApp.controller.toRad(fromLatitude - toLatitude);
var distanceLongitude = myHplApp.controller.toRad(fromLongitude - toLongitude);
var lat1 = myHplApp.controller.toRad(fromLatitude);
var lat2 = myHplApp.controller.toRad(toLatitude);
var a = Math.sin(distanceLatitude / 2) * Math.sin(distanceLatitude / 2) +
Math.sin(distanceLongitude / 2) * Math.sin(distanceLongitude / 2) *
Math.cos(lat1) * Math.cos(lat2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
var distance = (earthRadiusMetres * c).toFixed(2);
return distance;
};
Compared with the requirement being delivered in Hana Spatial:
SELECT NEW ST_Point('POINT(58.641759 -3.068672)',4326).ST_Distance(NEW ST_Point('POINT(50.05935 -5.708054)',4326)) FROM dummy;
This simple example involves only two coordinate pairs, not 20, 50 or 100 describing a more complex geometric or geographic shape.. Now we have Spatial, let's get Hana to do all the heavy lifting for our GIS (Geographic Information System) needs!
Frequent visitors to the Hana Developer Center and Hana In-Memory forums may have seen fantastic blogs relevant to geo data processing, like kevin.small's reverse geocoding and s0000716522's spatial with D3. It's clear this topic has community interest and I'm rather intimidated to be writing my first SCN blog in such esteemed company.
Regretfully you won't get any beautifully rendered maps nor tablet-enabled apps here. What I do want to share with you is the consolidated learning of a team effort this week in the forum, thanks to all, it's been fun. I'm going to use Manhattan as an example, let's begin.
Shopping for data on 5th Avenue
We need to describe real world objects like streets, areas, roads and other points of interest. We do this with spatial artifact types like points (points of interest), lines (streets, rivers) and polygons (lakes, counties, countries).
To describe parts of Manhattan I'm interested in I need geographic data, and I'm going to use a GIS tool to get that. I use Google Earth, a personal choice, whatever you find works for you. First I'm interested in the whole of Manhattan, Central Park and Liberty Island. I want to mark those areas out. In Google Earth I can use the polygon tool:
This is cool because you can give an area a border and fill colour and set the opacity. You can see from the screenshots below I've set Manhattan as red.
Now I want to plot 5th Ave, 59th St and Manhattan Bridge. I've set these as yellow to stand out. I have done this with the path tool in Google Earth, which looks like:
Finally some places of interest - my favourite, the Chrysler Building, followed by the Empire State and Statue of Liberty. You do this with the placemark tool in Google Earth:
Finished mapping Manhattan.
You can organise your places rather nicely in Google Earth too, and toggle visibility.
OK, so we have plotted our places of interest, how to we get all these coordinates? Right-click each place of interest to get the context menu then choose Save Place As. Set the type as Kml.
Open the file in a text editor (I use Notepad++). For Central Park you will see something like the following - you are interested in the contents of the <coordinates> tag.
| <coordinates> | ||||||
| -73.95804761112954,40.80030564443565,0 -73.98220944876016,40.76822446495113,0 -73.97245661014151,40.76435147208066,0 -73.94896121467113,40.79633309813332,0 -73.95804761112954,40.80030564443565,0 | ||||||
| </coordinates> |
Filling the Hana bag with spatial data goodness
Let's create a table to store our spatial data.
create column table SpatialLocations(
id integer,
name nvarchar(40),
shape ST_GEOMETRY(4326)
);
Q. What is type ST_GEOMETRY?
A. ST_GEOMETRY is the spatial data supertype which we are going to use to define our column shape to contain all our coordinates. More on that in the Spatial Reference guide page 9.
Q. What does 4326 represent?
A. 4326 is the identifier for WSG84 (World Geodetic System, 1984) for a specific SRS (Spatial Reference System). For now think of it as a set of specifications that enables us to accurately transform groups of spatial coordinates into real world points and measures.
Now we have our Spatial Locations table created, let's fill it with our Manhattan data.
IMPORTANT - Longitude is expected first, which is the opposite to that provided by Google Earth.
Central Park going into the "bag"
insert into SpatialLocations values(2, 'Central Park', new ST_POLYGON('POLYGON((
40.80030564443565 -73.95804761112954,
40.76822446495113 -73.98220944876016,
40.76435147208066 -73.97245661014151,
40.79633309813332 -73.94896121467113,
40.80030564443565 -73.95804761112954
))'));
5th Avenue
insert into SpatialLocations values(3, '5th Avenue', new ST_LINESTRING('LINESTRING((
40.80326767213774 -73.94457982661453,
40.7315072365715 -73.99689578972514
))'));
And my favourite, the Chrysler
insert into SpatialLocations values(6, 'Chrysler Building', new ST_POINT('POINT((
40.75157529253383 -73.97548823598672
))'));
Note how I'm describing each real world "shape" with a spatial shape type - ST_POLYGON, ST_LINESTRING, ST_POINT. Also note with polygons you "close the loop", my first coordinate pair is also my last. I have included all 9 Manhattan spatial shape table inserts as an attachment.
Taking a bite with SQL
Here's an example SQL statement determining the distance (in metres) between a point describing Times Square and spatial shape id 6 in our table, the Chrysler building. Result 1058m.
Note here we specify SRID 4326 for Times Square, as the SRID (spatial reference identifier) needs to be common. Soon an enhancement to deduce an implied SRID from the column value SRID will be with us :smile:
select shape.st_distance(ST_GeomFromEWKT('SRID=4326;POINT(40.758977 -73.984746)'))
from spatiallocations
where id = 6;
Q. How can I be sure the result is in metres?
A. Linear unit of measure associated with SRID, in view ST_SPATIAL_REFERENCE_SYSTEMS
Another example of distance between the Chrysler (id 6) and Empire State (id 7) in our spatial locations table:
select A.name as "From", B.name as "To", A.shape.ST_DISTANCE(B.shape) as "Distance(m)"
from SpatialLocations A , SpatialLocations B
where A.id = 6 and B.id = 7;
Munching consumption with XSJS
We have seen interrogation of the spatial shapes directly with SQL in the console. I've also included consumption with an XSJS service (code below and attached - no warranty provided for lack of robustness!).
Q. Tell me about Central Park?
A. bigApple.xsjs?cmd=poi&poi=2
Q. Give me the distance between the Chrysler and Empire State?
A. bigApple.xsjs?cmd=dst&poiFrom=6&poiTo=7
{
- shape:
[
]- {}
- From: "Chrysler Building",
- To: "Empire State Building",
- Distance(m): 1148
- {}
}
Q. Is the Chrysler within 1100m of the Empire State?
A. No. bigApple.xsjs?cmd=wdst&dstM=1100&poiFrom=6&poiTo=7
{
- shape:
[
]- {}
- From: "Chrysler Building",
- To: "Empire State Building",
- Distance(m): "1100",
- Within distance: "No"
- {}
}
Q. Is the Chrysler within 1200m of the Empire State?
A. Yes. bigApple.xsjs?cmd=wdst&dstM=1200&poiFrom=6&poiTo=7
{
- shape:
[
]- {}
- From: "Chrysler Building",
- To: "Empire State Building",
- Distance(m): "1200",
- Within distance: "Yes"
- {}
}
Q. Does 5th Avenue intersect Manhattan Bridge?
A. No. bigApple.xsjs?cmd=int&poiFrom=3&poiTo=5
{
- shape:
[
]- {}
- From: "5th Avenue",
- To: "Manhattan Bridge",
- Intersects: "No"
- {}
}
Q. Does 5th Avenue intersect 59th St.?
A. Yes. bigApple.xsjs?cmd=int&poiFrom=3&poiTo=4
{
- shape:
[
]- {}
- From: "5th Avenue",
- To: "59th St",
- Intersects: "Yes"
- {}
}
Q. Does 5th Avenue equal 59th St.?
A. No. bigApple.xsjs?cmd=eq&poiFrom=3&poiTo=4
{
- shape:
[
]- {}
- POI A: "5th Avenue",
- POI B: "59th St",
- Spatially Equal: "No"
- {}
}
Final Thoughts
Currently there is a lack of documentation, example code, cookbooks and developer orientated videos, especially considering the GA status. Over the last year the Hana Developer community has indulged in a plethora of quality educational and technical resources for core topics. The bar is set high. However once I had a few examples on the go it was easy to work with, very code light.
Hana itself has evolved remarkably over the last year I have been developing with it as a solution - it's an effort to keep up. With the inclusion of the spatial engine Hana continues to mature as a well-rounded and capable offering. There are however a few gaps in the current spatial engine solution. One is being able to work with ST_GEOMETRY spatial types in SQLScript procedures. I understand the Spatial team are making progress towards this for 7.2. Another gap is inability in declaring entities with spatial-based columns in your CDS (Core Data Services) .hdbdd or hdbtable definition artifacts. I also understand this is coming in SPS8/9 (TBD). So improvements and added functionality to Spatial will come with each release, as they have for Hana core as a whole, with the learning materials to support.
In the meantime a HUGE thank-you to gerritkazmaier, chief technical architect for Spatial who has been providing great support in the community forum. Perhaps whilst I have his attention - for some reason I was unable to successfully work with the ST_CONTAINS and ST_WITHIN methods for SRID 4326, to determine if for example Central Park or Statue of Liberty lies within Manhattan. I could not get ST_AREA to give me the size of Liberty Island. It is not clear if some methods are restricted by SRID "type". Any pointers here welcomed!
The Hana Spatial Engine opens up a whole host of exciting opportunities for delivering application capability with spatial awareness. The consumer group is large and diverse, including:
- Healthcare, for tracking of infection.
- Enabling local authorities with decision support systems for urban planning and geographic analytics on crime.
- Consumer goods companies delivering targeted customer marketing and effective sales delivery.
The future is exciting, it's spatial!
(Footnote: - no more fruit-orientated blogs from me)
XSJS service bigApple.xsjs
(You may wish to get the code from the attachment due to formatting concerns)
function bigApple() {
var pstmt = null,
rs = null,
conn = $.db.getConnection(),
bodyContent = '',
myCmd = $.request.parameters.get('cmd'),
myPoi = $.request.parameters.get('poi'),
myPoiFrom = $.request.parameters.get('poiFrom'),
myPoiTo = $.request.parameters.get('poiTo'),
myDstM = $.request.parameters.get('dstM'),
myQuery = null,
geoShape = [];
var poiOut = function(val){
var geometry = JSON.parse(val.getNString(3));
return {
"id": val.getInteger(1),
"name": val.getString(2),
"geometry": geometry
};
};
var distanceOut = function(val){
return {
"From": val.getString(1),
"To": val.getString(2),
"Distance(m)": val.getInteger(3)
};
};
var withinDistanceOut = function(val){
return {
"From": val.getString(1),
"To": val.getString(2),
"Distance(m)": myDstM,
"Within distance": val.getInteger(3) ? "Yes" : "No"
};
};
var intersectsOut = function(val){
return {
"From": val.getString(1),
"To": val.getString(2),
"Intersects": val.getInteger(3) ? "Yes" : "No"
};
};
var equalOut = function(val){
return {
"POI A": val.getString(1),
"POI B": val.getString(2),
"Spatially Equal": val.getInteger(3) ? "Yes" : "No"
};
};
function querySpatial(myQuery) {
try {
pstmt = conn.prepareStatement(myQuery.query);
if (myQuery.id) {
pstmt.setInt(1,myQuery.id);
} else if (myQuery.dstM) {
pstmt.setInt(1,myQuery.dstM);
pstmt.setInt(2,myQuery.idFrom);
pstmt.setInt(3,myQuery.idTo);
} else {
pstmt.setInt(1,myQuery.idFrom);
pstmt.setInt(2,myQuery.idTo);
}
rs = pstmt.executeQuery();
while (rs.next()) {
geoShape.push(myQuery.fnOut(rs));
}
bodyContent = JSON.stringify({
"shape": geoShape
});
$.response.setBody(bodyContent);
} catch (e) {
$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
$.response.setBody(e.message);
return;
}
}
try {
switch (myCmd) {
case "poi": //Point of Interest
myQuery = { query: 'select id, name, shape.ST_AsGeoJSON()
from SpatialLocations where id = ?',
id: parseInt(myPoi,10),
fnOut: poiOut };
querySpatial(myQuery);
break;
case "dst": //Distance between two points
myQuery = { query: 'select A.name as "From", B.name as "To",
A.shape.ST_DISTANCE(B.shape) as "Distance(m)" from
SpatialLocations A ,
SpatialLocations B where A.id = ? and B.id = ?',
idFrom: parseInt(myPoiFrom,10),
idTo: parseInt(myPoiTo,10),
fnOut: distanceOut };
querySpatial(myQuery);
break;
case "wdst": //Within Distance of two points
myQuery = { query: 'select A.name as "From", B.name as "To",
A.shape.ST_WithinDISTANCE(B.shape,?) as
"Distance(m)" from SpatialLocationsA,
SpatialLocations B where A.id = ? and B.id = ?',
dstM: parseInt(myDstM,10),
idFrom: parseInt(myPoiFrom,10),
idTo: parseInt(myPoiTo,10),
fnOut: withinDistanceOut };
querySpatial(myQuery);
break;
case "int": //Two points intersect
myQuery = { query: 'select A.name as "From", B.name as "To",
A.shape.ST_Intersects(B.shape) as "Intersects"
from SpatialLocations A ,
SpatialLocations B where A.id = ? and B.id = ?',
idFrom: parseInt(myPoiFrom,10),
idTo: parseInt(myPoiTo,10),
fnOut: intersectsOut };
querySpatial(myQuery);
break;
case "eq": //Two spatial geometries equal
myQuery = { query: 'select A.name as "POI A", B.name as "POI B",
A.shape.ST_Equals(B.shape) as "Spatially Equal"
from SpatialLocations A ,
SpatialLocations B where A.id = ? and B.id = ?',
idFrom: parseInt(myPoiFrom,10),
idTo: parseInt(myPoiTo,10),
fnOut: equalOut };
querySpatial(myQuery);
break;
default:
$.response.status = $.net.http.INTERNAL.SERVER.ERROR;
$.response.setBody('Invalid cmd '+ myCmd);
}; //End myCmd
} catch(e) {
$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
$.response.setBody(e.message);
} finally {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
}
}
bigApple();
- SAP Managed Tags:
19 Comments
