Call Scalar Function From HANA Procedure

[robert_schmidt01]

I have a function that is defined as:
Where XXXXX is my schema YYYYY is my package.

PROCEDURE "XXXXX"."YYYYY.SPATIAL::SP_GA_PT_PATH_DISTANCE" (IN PID NVarChar(36)) 
LANGUAGE SQLScript
SQL SECURITY INVOKER 
--DEFAULT SCHEMA <default_schema_name>
AS
BEGIN

I want to call a function and assign the result to a variable, I have tried the following two ways:

intIntersect := XXXXX.YYYYY.SPATIAL::GA_INTERSECT (32.925148, -117.020051,   -- xx 
                                                   32.924672, -117.019454,   -- xx
                                                   32.924488, -117.020322,   -- xx
                                                   32.924849, -117.019759);

SELECT XXXXX.YYYYY.SPATIAL::GA_INTERSECT (32.925148, -117.020051,   -- xx 
                                          32.924672, -117.019454,   -- xx
                                          32.924488, -117.020322,   -- xx
                                          32.924849, -117.019759) INTO intIntersect FROM DUMMY;

I have played with different permutations of this, but nothing works.

Any ideas?

Thanks.

[lbreddemann]

As I explained here:

What you describe as a FUNCTION is really a PROCEDURE in your code example.

These differ in the ways you can call either of them. Procedures need to be called via the CALL statement.

Functions can either be used as scalar function in all places where you can use expressions (i.e. the projection list of a SELECT-statement) or, for table-typed functions, like a table in the WHERE condition.

The parameters handed over to the procedure seem to be a list of data items. The general way to pass "lists" of parameters is to use a table-type parameter:

CREATE FUNCTION "XXXXX"."YYYYY.SPATIAL::SP_GA_PT_PATH_DISTANCE" 
              (IN_PIDS TABLE (PID NVARCHAR(36)) )
       RETURNS TABLE (DISTANCES DECIMAL)
AS
BEGIN
   SELECT * FROM :IN_PIDS;
...

That's of course all covered in the many SAP tutorials and trainings...

[robert_schmidt01]

Sorry, the question wasn't clear. I have a procedure called: SP_GA_PT_PATH_DISTANCE
That takes PID as an argument.

I want to call function: GA_INTERSECT (takes two latitude-longitude pairs as arguments)
from within SP_GA_PT_PATH_DISTANCE.

I have tried the following two ways to call GA_INTERSECT from within SP_GA_PT_PATH_DISTANCE.

Called from within SP_GA_PT_PATH_DISTANCE.

intIntersect is a variable that I want to store the results of the call to the GA_INTERSECT function.

Tried way #1:

intIntersect := XXXXX.YYYYY.SPATIAL::GA_INTERSECT (32.925148,-117.020051,-- xx 
                                                   32.924672,-117.019454,-- xx
                                                   32.924488,-117.020322,-- xx
                                                   32.924849,-117.019759);

Tried way #2:

SELECT XXXXX.YYYYY.SPATIAL::GA_INTERSECT (32.925148,-117.020051,-- xx 
                                          32.924672,-117.019454,-- xx
                                          32.924488,-117.020322,-- xx
                                          32.924849,-117.019759)INTO intIntersect FROMDUMMY;

Function Definition:

CREATE FUNCTION "GA_INTERSECT" ( IN LAT_A1 DOUBLE,
                         IN LONG_A1 DOUBLE,
                         IN LAT_A2 DOUBLE,
                         IN LONG_A2 DOUBLE,
                         IN LAT_B1 DOUBLE,
                         IN LONG_B1 DOUBLE,
                         IN LAT_B2 DOUBLE,
                         IN LONG_B2 DOUBLE) 
RETURNS RET_DOESINTERSECT DOUBLE
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
DECLARE MA DOUBLE;
DECLARE MB DOUBLE;
DECLARE BA DOUBLE;
DECLARE BB DOUBLE;
DECLARE XA DOUBLE;
DECLARE MAX_MIN_X DOUBLE;
DECLARE MIN_MAX_X DOUBLE;
DECLARE DOESINTERSECT INTEGER;

SELECT 1 INTO DOESINTERSECT FROM DUMMY;

IF LAT_A2-LAT_A1 != 0 AND LAT_B2-LAT_B1 != 0 THEN
   SELECT (LONG_A2 - LONG_A1)/(LAT_A2 - LAT_A1) INTO MA FROM DUMMY; 
   SELECT (LONG_B2 - LONG_B1)/(LAT_B2 - LAT_B1) INTO MB FROM DUMMY;
   IF MA = MB THEN
      SELECT 0 INTO DOESINTERSECT FROM DUMMY;
   END IF;
END IF;

SELECT LONG_A1-MA*LAT_A1 INTO BA FROM DUMMY;
SELECT LONG_B1-MB*LAT_B1 INTO BB FROM DUMMY;
SELECT (BB - BA) / (MA - MB) INTO XA FROM DUMMY;

-- Max of Mins
IF LAT_A1 < LAT_A2 THEN         -- MIN(LAT_A1, LAT_A2) = LAT_A1
   IF LAT_B1 < LAT_B2 THEN        -- MIN(LAT_B1, LAT_B2) = LAT_B1
      IF LAT_A1 > LAT_B1 THEN       -- MAX(LAT_A1, LAT_B1) = LAT_A1
         SELECT LAT_A1 INTO MAX_MIN_X FROM DUMMY;
      ELSE                          -- MAX(LAT_A1, LAT_B1) = LAT_B1
         SELECT LAT_B1 INTO MAX_MIN_X FROM DUMMY;
   END IF;
   ELSEIF LAT_B2 < LAT_B1 THEN   -- MIN(LAT_B1, LAT_B2) = LAT_B2
      IF LAT_A1 > LAT_B2 THEN       -- MAX(LAT_A1, LAT_B2) = LAT_A1
         SELECT LAT_A1 INTO MAX_MIN_X FROM DUMMY;
      ELSE                          -- MAX(LAT_A1, LAT_B2) = LAT_B2
         SELECT LAT_B2 INTO MAX_MIN_X FROM DUMMY;
      END IF;
   END IF;
ELSEIF LAT_A2 < LAT_A1 THEN     -- MIN(LAT_A1, LAT_A2) = LAT_A2
   IF LAT_B1 < LAT_B2 THEN        -- MIN(LAT_B1, LAT_B2) = LAT_B1
      IF LAT_A2 > LAT_B1 THEN       -- MAX(LAT_A2, LAT_B1) = LAT_A2
         SELECT LAT_A2 INTO MAX_MIN_X FROM DUMMY;
      ELSE                          -- MAX(LAT_A2, LAT_B1) = LAT_B1
         SELECT LAT_B1 INTO MAX_MIN_X FROM DUMMY;
      END IF;
   ELSEIF LAT_B2 < LAT_B1 THEN   -- MIN(LAT_B1, LAT_B2) = LAT_B2
      IF LAT_A2 > LAT_B2 THEN       -- MAX(LAT_A2, LAT_B2) = LAT_A2
         SELECT LAT_A2 INTO MAX_MIN_X FROM DUMMY;
      ELSE                          -- MAX(LAT_A2, LAT_B2) = LAT_B2
         SELECT LAT_B2 INTO MAX_MIN_X FROM DUMMY;
      END IF;
   END IF;
END IF;

-- Min of Max
IF LAT_A1 > LAT_A2 THEN         -- MAX(LAT_A1, LAT_A2) = LAT_A1
   IF LAT_B1 > LAT_B2 THEN        -- MAX(LAT_B1, LAT_B2) = LAT_B1
      IF LAT_A1 < LAT_B1 THEN       -- MIN(LAT_A1, LAT_B1) = LAT_A1
         SELECT LAT_A1 INTO MIN_MAX_X FROM DUMMY;
      ELSE                          -- MIN(LAT_A1, LAT_B1) = LAT_B1
         SELECT LAT_B1 INTO MIN_MAX_X FROM DUMMY;
      END IF;
   ELSEIF LAT_B2 > LAT_B1 THEN   -- MAX(LAT_B1, LAT_B2) = LAT_B2
      IF LAT_A1 < LAT_B2 THEN       -- MIN(LAT_A1, LAT_B2) = LAT_A1
         SELECT LAT_A1 INTO MIN_MAX_X FROM DUMMY;
      ELSE                          -- MIN(LAT_A1, LAT_B2) = LAT_B2
         SELECT LAT_B2 INTO MIN_MAX_X FROM DUMMY;
      END IF;
   END IF;
ELSEIF LAT_A2 > LAT_A1 THEN     -- MAX(LAT_A1, LAT_A2) = LAT_A2
   IF LAT_B1 > LAT_B2 THEN        -- MAX(LAT_B1, LAT_B2) = LAT_B1
      IF LAT_A2 < LAT_B1 THEN       -- MIN(LAT_A2, LAT_B1) = LAT_A2
         SELECT LAT_A2 INTO MIN_MAX_X FROM DUMMY;
      ELSE                          -- MIN(LAT_A2, LAT_B1) = LAT_B1
         SELECT LAT_B1 INTO MIN_MAX_X FROM DUMMY;
      END IF;
   ELSEIF LAT_B2 > LAT_B1 THEN   -- MAX(LAT_B1, LAT_B2) = LAT_B2
      IF LAT_A2 < LAT_B2 THEN       -- MIN(LAT_A2, LAT_B2) = LAT_A2
         SELECT LAT_A2 INTO MIN_MAX_X FROM DUMMY;
      ELSE                          -- MIN(LAT_A2, LAT_B2) = LAT_B2
         SELECT LAT_B2 INTO MIN_MAX_X FROM DUMMY;
      END IF;
   END IF;
END IF;
IF XA < MAX_MIN_X OR
   XA > MIN_MAX_X THEN
   SELECT 0 INTO DOESINTERSECT FROM DUMMY;
END IF;
RET_DOESINTERSECT := :DOESINTERSECT;
END;

Thanks.

[lbreddemann]

I used your function (after replacing all those unnecessary SELECT ... INTO ... FROM DUMMY with simple assignments) without any issues both in SQLScript and in a plain SELECT statement.

Now would be a good, albeit late, time to share what exactly is not working for you.

Another question is: why not simply use the existing HANA spatial function to check for line intersections?

[robert_schmidt01]

Thanks.

I think not using ST_Intersects, because won't let me use round earth (4326).

[robert_schmidt01]

Followed Lars' advice and called function:

DOESINTERSECT := "XXXXX"."YYYYY.SPATIAL::GA_INTERSECT" (r1.TOLAT, r1.TOLONG, r1.FROMLAT, r1.FROMLONG, CURRLAT,  CURRLONG,  CENTERLAT,  CENTERLONG);

Also, rewrote function to use ST_INTERSECT:

FUNCTION "XXXXX"."YYYYYYYYYYYY.SPATIAL::GA_INTERSECT" ( IN LAT_A1 DOUBLE,
                                                        IN LONG_A1 DOUBLE,
                                                        IN LAT_A2 DOUBLE,
                                                        IN LONG_A2 DOUBLE,
                                                        IN LAT_B1 DOUBLE,
                                                        IN LONG_B1 DOUBLE,
                                                        IN LAT_B2 DOUBLE,
                                                        IN LONG_B2 DOUBLE) 
RETURNS RET_DOESINTERSECT DOUBLE 
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
DECLARE DOESINTERSECT INTEGER;

SELECT NEW ST_LineString('LineString(' || LONG_A1 || ' ' || LAT_A1 || ',' || LONG_A2 || ' ' || LAT_A2 || ')').ST_Intersects(NEW ST_LineString('LineString(' || LONG_B1 || ' ' || LAT_B1 || ',' || LONG_B2 || ' ' || LAT_B2 || ')') ) INTO DOESINTERSECT FROM DUMMY;

    RET_DOESINTERSECT := :DOESINTERSECT;
END;

Still have my select into, because complains about the use of the NEW operator with a scalar assignment. HELP tells me to try: ST_GeomFromText, as in:

SELECT ST_GeomFromText( 'LineString( 1 2, 5 7 )', 4326 ).ST_AsText() FROM dummy;

But, haven't taken the time to get this to work.

[robert_schmidt01]

Got STGEOMETRYFROMTEXT to work:

stEndCoord1 := ST_GEOMFROMTEXT('Point(' || decEndPointLong1 || ' ' || decEndPointLat1 || ')', 4326);