Skip to Content

Hana SPS07 and spatial data

Hi,

Interested in the intriguing possibilities of spatial data manipulation now that you have your SPS07 instance?

Wish to learn more or contribute along with other explorers in this brave new world of business data meets geospatial?

There appears to be very little in way of example content. The official reference guide, which albeit useful, does not provide use cases within the context of SQLScript where the functionality could really be exploited over large geospatial datasets.

http://help.sap.com/hana/SAP_HANA_Spatial_Reference_en.pdf

There are a few other blogs out there which present wonderful slides or flashy videos yet add little to illuminate what's going on under the hood, how the engine was put together component by component with example code which we as developers love and learn from best.

I thought I'd throw out this discussion into the wild to hopefully attract like-minded souls who may be starting out on this geo adventure, where we can share thoughts, solutions, workarounds and information to everyone else.

So without further ado.....

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

14 Answers

  • Feb 19, 2014 at 01:57 PM

    Aron,

    Specifying the SRID in the point constructor, using the "John O'Groats to Lands End" example.

    Test 1 - SRID 4326 - WSG84


    SELECT NEW ST_Point('POINT(58.641759 -3.068672)',4326).ST_Distance(NEW ST_Point('POINT(50.05935 -5.708054)',4326)) FROM dummy

    Returns 996229m or 996Km. Still wondering why it's 30Km over what I'd expect.

    Test 2 - SRID 1000004326 - WSG84 (Planar)

    If I change to the WSG84 (Planar) SRID with the following I get the result in decimal degrees with 8.979091356508434

    SELECT NEW ST_Point('POINT(58.641759 -3.068672)',1000004326).ST_Distance(NEW ST_Point('POINT(50.05935 -5.708054)',1000004326)) FROM dummy

    Which surprises me as looking at ST_SPATIAL_REFERENCE_SYSTEMS the linear unit of measure for both 4326 and 1000004326 is meter.

    Guess I need to understand the relationship between the below and how ST_SPATIAL_REFERENCE_SYSTEMS is defined (reference, page 38):

    "The ST_Distance method computes the shortest distance between two geometries. For planar spatial reference systems, the distance is calculated as the Cartesian distance within the plane, computed in the linear units of measure for the associated spatial reference system. For round-Earth spatial reference systems, the distance is computed taking the curvature of the Earth's surface into account using the ellipsoid parameters in the spatial reference system definition."

    Test 3 - SRID 0 - Default

    And for completeness, default SRID 0, also in decimal degrees with 8.979090690612793 (small difference comparing with WSG84 Planar)

    SELECT NEW ST_Point('POINT(58.641759 -3.068672)',0).ST_Distance(NEW ST_Point('POINT(50.05935 -5.708054)',0)) FROM dummy

    This at least confirms that when no SRID is specified the system does indeed use SRID 0 as indicated in the reference, as the computed decimal degrees is the same as given in an earlier post above..

    Now to understand why I get an additional 30Km than expected for test 1.


    (By the way, it could be a good idea to use a common "from-to" set of coordinates when we are fleshing the geographic side out, to compare results etc - could go with this example or anything else you suggest?)


    sqlsrid.PNG (8.6 kB)
    srid1.PNG (10.1 kB)
    srid3.PNG (8.1 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Jon-Paul Boyd Gerrit Simon Kazmaier

      Worked like a charm. Now have SRID 1000004326 with an updated linear unit of measure as planar degree, and was able to successfully create the table with location referencing 1000004326, insert the points and select from, as per your script. Thanks!

      srid13.PNG (23.5 kB)
  • Feb 13, 2014 at 04:21 PM

    I wanted to process a geospatial dataset containing latitude and longitude references, comparing each of those to a given lat/lng reference via inbound parameters for instance, and planned to do this with SQLscript. Seemed reasonable.

    • Unable to define inbound parameters with a spatial type.

    • Following fails, so I dont see a way of working with a simple local variable.

    declare lvGeo ST_POLYGON;




    • Following also fails within SQLScript ("Could not create catalog object: invalid datatype; ST_GEOMETRY")


    create local temporary table #test_table(pointFrom ST_GEOMETRY, pointTo ST_GEOMETRY );


    or


    create global temporary table test_table(pointFrom ST_GEOMETRY, pointTo ST_GEOMETRY );




    • I can however create a global table type successfully from the SQL console

    CREATE TYPE "ABC"."def.data::GEO.GeoFromTo" AS TABLE ( "GEOFROM" ST_GEOMETRY, "GEOTO" ST_GEOMETRY );

    and can do the following to instance my geospatial type into say an "out" table variable:

    SELECT NEW ST_POLYGON() FROM DUMMY;

    I could also create a dummy table with one row of several geospatial containers to work with, however to me that would appear to necessitate working with 2 cursors, one for the large gepspatial dataset and another for the reference/comparison dataset, as I can only work with these special types with table columns?

    Currently there are workarounds to define the model outside the preferred CDS yet limitations on how to consume the data easily.

    Developers would welcome any examples of consumption of datasets containing geospatial data types within SQLScript or indeed analytic or calculation views that utilise geospatial methods like ST_DISTANCE.

    Any other ideas or correction most most welcome, thanks.

    Add comment
    10|10000 characters needed characters exceeded

    • Jon-Paul Boyd Gerrit Simon Kazmaier

      Hi Gerrit, and welcome to the thread! It's really appreciated to have one of the SAP Hana Spatial team contributing.

      I would happily take the above solution, I'm sure this would be a welcome addition 😊

  • Feb 19, 2014 at 11:05 AM

    Hi Aron,

    Regarding your test of ST_DISTANCE - taken from the spatial reference guide:

    "The ST_Distance method computes the shortest distance between two geometries. For planar spatial reference systems, the distance is calculated as the Cartesian distance within the plane, computed in the linear units of measure for the associated spatial reference system. For round-Earth spatial reference systems, the distance is computed taking the curvature of the Earth's surface into account using the ellipsoid parameters in the spatial reference system definition."

    • I inserted my geometric data specifying SRID (Spatial Refeference Identifier) 4326 - This equates to WSG84.

    • From table ST_SPATIAL_REFERENCE_SYSTEMS I see that it is a "round earth" reference system, with the linear unit of measure as meter,

    This to me signifies that when I use ST_DISTANCE (linear measure) I already get back my result in metres, and performing a few tests this would seem correct.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Gerrit Simon Kazmaier

      Thanks for the information. I look forward to improved optimization.

      The immediate solution you proposed is behaving as if ST_Buffer wasn't working. The test point happens to match exactly one record in the database and this is the only record returned. If I move the test point .0001 degrees, no results are returned.

      My reading of the doc is that ST_Buffer(0.5) creates a half-meter buffer, so I tried some variants such as ST_Buffer(50000, 'meter'). I also tried putting the buffer on the location column. None of this made any difference.

      Here is one example (the intent is to return all points within 50km of a reference point):

      WHERE location.ST_Intersects(ST_GeomFromEWKT('SRID=4326;POINT(-94.1167 29.7000)').ST_Buffer(50000, 'meter')) = 1;

  • Feb 13, 2014 at 03:55 PM

    There are 3 spatial reference systems defined, these help us to identify and describe our spatial world, like modelling our Earth to describe the ellipsoid (it's no perfect circle!) when working with geographic data,such as the semi major axis and inverse flattening:

    select * from ST_SPATIAL_REFERENCE_SYSTEMS;



    srid.PNG (21.0 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • With spatial data we can describe real world objects like addresses, buildings, rivers, roads and countries with artefact types like points, lines and polygons. Take a look at page 9 onwards of the Reference guide which details the spatial data types.

  • Feb 13, 2014 at 04:05 PM

    As of SPS07 we are unable to add columns of a spatial type either via CDS (.hdbdd) or .hdbtable. If supported by analytic or calculation views I would dearly love to see use-case examples.


    (Note - Thomas Jung kindly commented on another thread "They are planned to be added to HDBDD (CDS) in the future. I can't say if that will be SP8 or SP9 yet.")

    Nevertheless, to add a spatial type column to an existing table via the SQLConsole here's an example - note this specifically indicates the column contains spatial data referencing the 4326 WSG84 system.

    ALTER TABLE SPATIALSHAPES ADD (geolocation ST_POINT(4326));

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 13, 2014 at 04:08 PM

    Want to add some geospatial data - an example here (key ID, ST_GEOMETRY). Note I can also specify the SRID here if I want the data to specifically use the WSG84 system (and not the system default).

    Note this polygon is created from longitude and latitude values, in WKT format, with the longitude first.



    INSERT INTO SpatialShapes VALUES(1, new ST_POLYGON('POLYGON((7.392836 45.475097, 7.893152 45.475018, 7.893134 45.474962, 7.892817 45.475057, 7.392836 45.475097))',4326));

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 17, 2014 at 07:57 PM

    Latest update - Unable to natively instance and consume spatial data within hana SQLScripts or information views.

    However consumption alone within XSJS is relatively straight-forward via the much-loved prepared statement 😉


    Where I have constant test longitude and latitude for test purposes can of course always be replaced by elements within an array for example. As you will see it's not too sophisticated, simply offering possibility to inspect returned result set. Look forward to enhancements that allow creation of variable instances of type ST_GEOMETRY.



    I very much welcome correction or additions as it adds to the knowledge base.

    function testSpatial(){

    var query,

    pstmt,

    conn = $.db.getConnection("abc.def.services::conn");

    var geo = {

    longitude: 1.23456,

    latitude: 76.54321,

    };

    var messageText = '';

    try {

    query = 'select ID from "SCHEMA"."SPATIALSHAPES" where SHAPE.ST_Contains( new ST_Point(' + geo.longitude + ',' + geo.latitude + ') ) = 1';

    pstmt = conn.prepareStatement(query);

    var rs = pstmt.executeQuery();

    while (rs.next()) {

    messageText = rs.getString(1);

    }

    var bodyContent = JSON.stringify({

    "data": messageText

    });

    $.response.contentType = "application/json";

    $.response.setBody(bodyContent);

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

    } catch(e) {

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

    $.response.setBody(e.message);

    } finally {

    conn.close();

    }

    }

    testSpatial();

    Note I have concatenated the longitude and latitude, for the life of me I could not use placeholder ? and pstmt.setDecimal(1,geo.longitude) and pstmt.setDecimal(2,geo.latitude) for both longitude and latitude - worked for one or the other, but not both - where both used I kept getting "invalid number" error on prepared statement - explanation welcomed 😊

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi, I've been using this approach with my application.

      But after the update to SP8, it now throws an internal server error.

      So I got the code and tested in the SQL console in Hana Studio, and it shows the following error:

      SAP DBTech JDBC: [264]: invalid datatype: __eqwithnull__ Cannot compare Locator and Locator

      Debugging even further it was caused by the statement in the join:

      S."StateShape".ST_Contains( NEW ST_POINT (I."xCoordinate", I."yCoordinate")) = 1

      Do you have any workarounds on this? Thanks!

  • Feb 19, 2014 at 10:22 AM

    (Note - extracted from original post at Serving up Apples & Pears: Spatial Data and D3 )

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

    For example you have an INSERT into ST_GEOMETRY of 115.80622866283737, with ST_AsGeoJSON you get back something like 115.80622.

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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 19, 2014 at 10:23 AM

    Aron MacDonald responded to above with below (collating here as info bucket for SPS07 spatial specific) - thanks Aron.

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 19, 2014 at 10:27 AM

    Hi Aron,

    Many thanks for taking a look. I can confirm that ST_AsGeoJSON does lose geometric precision.

    Can also confirm there is no storage precision loss - ST_AsText, in addition to ST_AsWKT returns full precision - what goes into the database comes out:

    select SHAPE.ST_AsText() as "WKT" from "_SYS_BIC"."hpl.missioncontrol.models/AT_SCENARIO_TERRAIN"

    Returns the like of:

    POLYGON ((6.892805365019621 46.475136880333316,6.892739484137811 46.475160973073280,6.892705440891889 46.475123420221337,6.892792591997461 46.475100608648830,6.892805365019621 46.475136880333316))




    Interestingly I found some threads talking specifically about the precision of ST_AsGeoJSOn #4396 (OGR 1.8 and earlier truncates geometry precision) – GDAL

    "It seems clear that the precision is being truncated in the geojson. I would presume that is leading to some sort of geometric degeneracy. I see that GDAL/OGR trunk (but likely not 1.8.x) has COORDINATE_PRECISION layer creation option and defaults to 15 decimal places. So you might want to upgrade to trunk and try with that."

    It's only an assumption to which I'd welcome official comment, however I wonder if SAP has implemented the spatial capability with a forked version of an open source spatial features library that has not had this precision fix? Wild speculation at this point.

    At this moment I dont see any hardship with precision loss in GeoJSON as we can form our own JSON with values from the "text based" methods.

    Required precision would need to be determined on a case by case basis - a trade off between precision accuracy and data manipulation, storage and transfer.

    Add comment
    10|10000 characters needed characters exceeded