vkdb.InsideSelection

gistfile1.txt

CREATE OR REPLACE FUNCTION vkdb.InsideSelection(InputUdvaelgelsesPolygon INT)
  RETURNS TABLE(
    ogc_fid             INT,
    boreholeno          VARCHAR(10),
    xutm                DOUBLE PRECISION,
    yutm                DOUBLE PRECISION,
    elevation           DOUBLE PRECISION,
    drilldepth          DOUBLE PRECISION,
    intakeno            NUMERIC(10),
    mintop              DOUBLE PRECISION,
    maxbottom           DOUBLE PRECISION,
    filtertop_interval  VARCHAR(7),
    filterbja           VARCHAR(2),
    sampleid            NUMERIC(10),
    attribute           VARCHAR(1),
    amount              DOUBLE PRECISION,
    long_text           VARCHAR(60),
    compoundno          NUMERIC(10),
    enhed               VARCHAR(500),
    sampledate          TIMESTAMP WITH TIME ZONE,
    plantid             NUMERIC(10),
    plantname           VARCHAR(50),
    active              NUMERIC(10),
    companytype         VARCHAR(3),
    use                 VARCHAR(2),
    grumoborno          NUMERIC(10),
    loopstation         NUMERIC(10),
    compoundgroup       NUMERIC(5),
    stofgruppe          VARCHAR(100),
    compoundundergroup  NUMERIC(5),
    stofundergruppe     VARCHAR(100),
    waterworks_max      DOUBLE PRECISION,
    proeveaar           NUMERIC(10),
    mi_style            VARCHAR(100),
    mi_prinx            NUMERIC(10),
    bk                  NUMERIC(10),
    aa                  NUMERIC(10),
    lith_top            DOUBLE PRECISION,
    lith_bottom         DOUBLE PRECISION,
    lith_filterbja      VARCHAR(2),
    antalscreen         NUMERIC(10),
    gv_dkmlag           VARCHAR(254),
    abandondat          TIMESTAMP WITH TIME ZONE,
    abandcause          VARCHAR(1),
    bamgruppe           VARCHAR(12),
    pesticidgruppe      VARCHAR(255),
    moderstof_metabolit VARCHAR(255),
    rv                  BYTEA,
    sp_geometry         GEOMETRY(Point, 25832)
  ) AS $$
DECLARE
  start         FLOAT;
  InputGeometri GEOMETRY;
BEGIN
  InputGeometri = (SELECT udvaelgelsespolygoner.sp_geometry
                   FROM vkdb.udvaelgelsespolygoner
                   WHERE udvaelgelsespolygoner.mi_prinx = InputUdvaelgelsesPolygon
                   LIMIT 1);
  RETURN QUERY (
    WITH BoreHolesInside AS
    (
        SELECT b.boreholeno
        FROM vkdb.TResultat_JUP_Pest_OSklasse b
        WHERE st_intersects(InputGeometri, b.sp_geometry)
    )
    SELECT b.*
    FROM BoreholesInside a INNER JOIN vkdb.TResultat_Jup_Pest_e1997_lith_dkm b ON a.boreholeno = b.BOREHOLENO);
END;
$$
LANGUAGE plpgsql;