Create a GET_LINE_HIGH function

Following "Create a GET_POINT_HIGH function" concept, I am going to create a GET_LINE_HIGH function.

Before to get this function done, I need a LINE constructor

create or replace
FUNCTION line(
first_x NUMBER, first_y NUMBER, next_x NUMBER, next_y NUMBER, srid NUMBER DEFAULT 4326)
RETURN SDO_GEOMETRY
DETERMINISTIC
IS
BEGIN
  RETURN SDO_GEOMETRY(2002, srid, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(first_x, first_y, next_x, next_y));
END;


Then

create or replace
FUNCTION GET_LINE_HIGH 
(
  first_x NUMBER
, first_y NUMBER
, next_x NUMBER
, next_y NUMBER  
, SRID IN NUMBER DEFAULT 4326 
) RETURN NUMBER AS 
BUFFER_GEOM SDO_GEOMETRY;
n_High NUMBER;
BEGIN
  BUFFER_GEOM := sdo_geom.sdo_buffer(line(first_x, first_y, next_x, next_y), 10, 1 );
  SELECT AVG(z) INTO n_high FROM dem_part WHERE SDO_INSIDE(location, BUFFER_GEOM) = 'TRUE';
  RETURN n_High;
END GET_LINE_HIGH;


Eliminate top and button 10%

create or replace
FUNCTION GET_LINE_HIGH 
(
  first_x NUMBER
, first_y NUMBER
, next_x NUMBER
, next_y NUMBER  
, SRID IN NUMBER DEFAULT 4326 
) RETURN NUMBER AS 
BUFFER_GEOM SDO_GEOMETRY;
n_High NUMBER;
BEGIN
  BUFFER_GEOM := sdo_geom.sdo_buffer(line(first_x, first_y, next_x, next_y), 10, 1 );
  SELECT AVG(T.Z) INTO n_high
  FROM (
    SELECT Z, CUME_DIST() over (order by Z) P FROM dem_part WHERE SDO_INSIDE(location, BUFFER_GEOM) = 'TRUE') T
  WHERE T.P > 0.1 AND T.P < 0.9;
  RETURN n_High;
END GET_LINE_HIGH;