SAP HANA spatial function

I want to create function in SAP HANA DB for calculation distance between two points.

create function "kostya"."calc_distance" (in a ST_Point, in b ST_Point)
returns dist DOUBLE
AS
BEGIN
    dist = a.ST_Distance(b);
END

when I have tried to execute this code I have got error:

feature not supported: Unsupported parameter type:ST_POINT: line 2 col 48 (at pos 48)

but when I have executed similar SQL query:

select NEW ST_Point(10, 1).ST_Distance( NEW ST_Point(1, 1)) FROM dummy;

I have got write result.

Any tips ?

Solution

From the docs we learn:

The input and output parameters of a procedure can have any of the primitive SQL types or a table type.

Geospatial data types are, unfortunately, not listed as such.

So you will have to pass scalare values like a.ST_X() to the function and combine them back to an ST_Point within the procedure using the ST_Point(DOUBLE, DOUBLE) constructor like so:

CREATE FUNCTION "calc_distance" (
    IN a_long DOUBLE, 
    IN a_lat DOUBLE, 
    IN b_long DOUBLE, 
    IN b_lat DOUBLE
) RETURNS dist DOUBLE
AS
BEGIN
    SELECT NEW ST_Point(:a_long, :a_lat).ST_Distance(
        NEW ST_Point(:b_long, :b_lat)) INTO dist FROM dummy;
END;

-- sample usage
SELECT "calc_distance"(a.ST_X(), a.ST_Y(), b.ST_X(), b.ST_Y())
FROM (
    SELECT NEW ST_Point(10.0, 50.0) AS a, NEW ST_Point(11.0, 51.0) AS b FROM dummy
);

This works without complaint from the Web Based Development Workbench of HANA 102.01. From a Eclipse Luna with SAP Development Tools for Eclipse installed, we get

java.sql.SQLWarning: Not recommended feature: Using SELECT INTO in Scalar UDF
Statement 'CREATE FUNCTION "calc_distance" ( IN a_long DOUBLE, IN a_lat DOUBLE, IN b_long DOUBLE, IN b_lat ...' 
successfully executed in 16 ms 272 µs  (server processing time: 7 ms 460 µs) - Rows Affected: 0

Maybe in SAP HANA Studio the warning becomes an error. Seems that things are in process over there :)

As of SAP HANA SPS11, there is extended support for spatial datatypes. See this video from the SAP HANA Academy.