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 Aron MacDonald‘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:


blog_polygon.PNG


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.

/wp-content/uploads/2014/02/blog2_comp_393895.jpg



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:


blog_path.PNG



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:

blog_pin.PNG



Finished mapping Manhattan.

/wp-content/uploads/2014/02/blog1_393893.jpg

You can organise your places rather nicely in Google Earth too, and toggle visibility.

blog_big_apple_folder.PNG

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 typeST_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 🙂



select shape.st_distance(ST_GeomFromEWKT(‘SRID=4326;POINT(40.758977 -73.984746)’))

      from spatiallocations

      where id = 6;

blog5.PNG



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

blog4.PNG


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;

blog7.PNG

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


blog20.JPG

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 Gerrit Simon Kazmaier, 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();

To report this post you need to login first.

19 Comments

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

  1. Jon-Paul Boyd Post author

    Thanks for the positive comments.  Lots of opportunity for having fun exploring the spatial engine yourself, look forward to reading your exploits!

    (0) 
    1. Jon-Paul Boyd Post author

      Thanks Vivek!  Within Hana we now have a spatial engine, so what I wanted to do is explore and document capability beyond using Hana as a spatial data repository and test consumption of the engine’s powerful functions within Hana.  Indeed this was the point a few of us in the community reached last week.  Give it a go and let us know.

      (0) 
  2. Satya Chintakindhi

    All

    seen blogs showing inserts of points/polygons in static way (passing values directly but not passing lats/longs from table columns)

    like…

    insert into spatial_table values (new ST_POLYGON(‘POLYGON((0.0 0.0, 4.0 0.0, 2.0 2.0, 0.0 0.0)

    Here is the scenario..

    table1 – has list of all polygon lat/longs for counties/ cities

    table2 – few specific area polygons that I need to check from table1 to see if it falls in that county or city

    –doesn’t work (query in stored proc)

    select NEW ST_Point(:var_Lat, :var_Long).ST_Within(NEW ST_Polygon(“coln_county_poly”)) FROM “Test_Spatial”.”County_Poly”;

    var_Lat & var_Long is specific area polygon from table2

    coln_county_poly has value ‘POLYGON(0 0, 2 0, 1 2, 0 0)’ –list of counties

    –works

    select NEW ST_Point(1, 1ST_Within(NEW ST_Polygon( ‘Polygon((0 0, 2 0. 1 2, 0 0))’ ) ) from dummy

    pls share if anyone tried/ implemented this…or since I am new to this pls advice where should I post this for solution…Thanks !!

    (0) 
    1. Jon-Paul Boyd Post author

      Hi,

      Your question may have been better posed in Hana SPS07 and spatial data, however as you asked, in “Final Thoughts” I mentioned the inability to create variables of type ST_GEOMETRY in procedures, and that is where your problem lies.  You should be able to achieve what you want in XSJS, indeed the solution to your problem is within the discussion thread I posted.  Have a go and let us know how you got on.

      (0) 
        1. Aron MacDonald

          Hi Satya/Jon-Paul

          I’m not sure if this helps, but  on a related note I’ve used a procedure to insert ST_POINTS into a table, based on latitude and longitude fields from another table.

          The simple procedure contained the following statement:

          insert into XXXX

                 (select Q.id, new ST_Point( Q.longitude , Q.latitude )

                  from YYYYY as Q

                  left outer join XXXX as QG

                  ON Q.id = QG.id

                  where QG.id is null );

          I used the left outer join to only find those records that had NOT yet been filled, and fill those with ST_POINTS.

          Full details are included in the attachment at the end of http://scn.sap.com/community/developer-center/hana/blog/2014/03/10/feeling-the-earth-move-with-hadoop-hana

          Cheers

          Aron

          (0) 
          1. Jon-Paul Boyd Post author

            Hey Aron, that could work for Satya if there is no requirement for local variable declaration and manipulation, within procedure, thanks for the hint.

            (0) 
  3. Kevin Small

    Hi Jon-Paul, great article.

    Regretfully you won’t get any beautifully rendered maps nor tablet-enabled apps here.

    There is no need, when the content is as good as this 🙂 .  I like the XSJS service, a good example of SP7 spatial.

    (0) 
    1. Jon-Paul Boyd Post author

      Thanks Kevin.  I look forward to forthcoming fixes for ST_CONTAINS to exploit the engine further.  I would imagine spatial is regarded as “niche” and bypassed for initial hana implementations,  there’s a lot of innovation potential here.

      (0) 
  4. Vladislav Bezrukov

    Great stuff, JP.

    I am trying to insert an ST_POINT as you described

    insert into SpatialLocations values(6, ‘Chrysler Building’, new ST_POINT(‘POINT((

        40.75157529253383 -73.97548823598672

    ))’));

    but getting an error:

    Could not execute ‘insert into “<mySpatialTable>” values(6, ‘Chrysler Building’, new ST_POINT(‘POINT(( …’ in 13 ms 922 µs .

    SAP DBTech JDBC: [266]: inconsistent datatype: Well-known Text deserialization failed. Reason: Invalid Number: Unexpected value.!Position: 7. Of input: POINT((

        40.75157529253383 -73.97548823598672

    )) at function __st_point__()

    Can you check, if it still works for you? I checked rev.74 and higher.

    Best regards, Vlad

    (0) 
    1. Kai-Christoph Mueller

      Hi Vladislav,

      I think this is due to the line breaks in your WKT string. Guess you formatted the SQL statement?

      This point insert works on my system (with according tables):

      INSERT INTO “DCODE”.”geoTable” VALUES( “DCODE”.”geoSequence”.nextval, NEW ST_POINT(‘POINT(56.953125 45.583289756006316)’,4326));

      You are welcome to check my Post on this topic:

      http://scn.sap.com/community/developer-center/hana/blog/2014/10/13/modeling-the-world-in-geojson-and-how-to-get-it-into-hana-to-use-the-geo-spatial-engine

      Cheers

      kc

      (0) 
  5. M. van Foeken

    Hi Jon,

    Great article! In your Final Thoughts paragraph you mentioned you were unable to calculate the size of the polygon using the ST_Area function. Have you been able to follow up on this? I’m struggling with the fact that I want to calculate the size of a polygon based on latitude and longitude coordinates as well. But I have not been able to find a clear answer how to resolve this.

    Any help would be much appriciated!

    With kind regards,

    Martijn van Foeken | Just-BI

    (0) 

Leave a Reply