postgres/create_funcs.sql

   1 -- create_funcs.sql
   2 -- File ID: 1a0fae52-fafb-11dd-b392-000475e441b9
   3
   4 -- clname(): Returnerer navnet på det nærmeste veipunktet i wayp.
   5 CREATE OR REPLACE FUNCTION clname(point) RETURNS text -- {{{
   6 AS $$
   7 SELECT name FROM (
   8         SELECT
   9             name,
  10             ($1 <-> coor)
  11             AS avs
  12             FROM wayp
  13             WHERE ($1 <-> coor) < 0.05
  14             ORDER BY avs
  15             LIMIT 1
  16     ) AS s;
  17 $$ LANGUAGE SQL;
  18 -- }}}
  19
  20 -- cldist(): Returnerer avstanden (i grader) til det nærmeste veipunktet i wayp.
  21 CREATE OR REPLACE FUNCTION cldist(point) RETURNS numeric -- {{{
  22 AS $$
  23 SELECT round(avs::numeric, 5) FROM (
  24         SELECT
  25             ($1 <-> coor)
  26             AS avs
  27             FROM wayp
  28             WHERE ($1 <-> coor) < 0.05
  29             ORDER BY avs
  30             LIMIT 1
  31     ) AS s;
  32 $$ LANGUAGE SQL; -- }}}
  33
  34 -- findpos(): Beregn koordinater for et tidspunkt som ligger mellom to trackpunkter.
  35 CREATE OR REPLACE FUNCTION findpos(currtime timestamptz) RETURNS point AS $$ -- {{{
  36 DECLARE
  37     firstdate timestamptz;
  38     lastdate timestamptz;
  39     firsttime timestamptz;
  40     firstcoor point;
  41     lasttime timestamptz;
  42     lastcoor point;
  43     currlat numeric;
  44     currlon numeric;
  45 BEGIN
  46     SELECT INTO firstdate date
  47         FROM logg
  48         ORDER BY date
  49         LIMIT 1;
  50     SELECT INTO lastdate date
  51         FROM logg
  52         ORDER BY date DESC
  53         LIMIT 1;
  54     IF currtime < firstdate OR currtime > lastdate THEN
  55         RETURN(NULL);
  56     END IF;
  57
  58     SELECT INTO firsttime date
  59         FROM logg
  60         WHERE date <= currtime
  61         ORDER BY date DESC
  62         LIMIT 1;
  63     SELECT INTO firstcoor coor
  64         FROM logg
  65         WHERE date <= currtime
  66         ORDER BY date DESC
  67         LIMIT 1;
  68     SELECT INTO lasttime date
  69         FROM logg
  70         WHERE date >= currtime
  71         ORDER BY date
  72         LIMIT 1;
  73     SELECT INTO lastcoor coor
  74         FROM logg
  75         WHERE date >= currtime
  76         ORDER BY date
  77         LIMIT 1;
  78
  79     IF firsttime = lasttime THEN
  80         RETURN(firstcoor);
  81     END IF;
  82
  83     currlat = firstcoor[0] +
  84     (
  85         (
  86             lastcoor[0] - firstcoor[0]
  87         ) *
  88         (
  89             (
  90                 extract(EPOCH FROM currtime) - extract(EPOCH FROM firsttime)
  91             )
  92             /
  93             (
  94                 extract(EPOCH FROM lasttime) - extract(EPOCH FROM firsttime)
  95             )
  96         )
  97     );
  98     currlon = firstcoor[1] +
  99     (
 100         (
 101             lastcoor[1] - firstcoor[1]
 102         ) *
 103         (
 104             (
 105                 extract(EPOCH FROM currtime) - extract(EPOCH FROM firsttime)
 106             )
 107             /
 108             (
 109                 extract(EPOCH FROM lasttime) - extract(EPOCH FROM firsttime)
 110             )
 111         )
 112     );
 113     RETURN(currlat, currlon);
 114 END;
 115 $$ LANGUAGE plpgsql; -- }}}
 116
 117 -- wherepos(): Returnerer en streng med dato, posisjon, nærmeste navn og avstand til nærmeste punkt.
 118 CREATE OR REPLACE FUNCTION wherepos(currtime timestamptz) RETURNS text AS $$ -- {{{
 119 DECLARE
 120     currpos point;
 121     currname text;
 122     currdist numeric;
 123     currlat numeric(9, 6);
 124     currlon numeric(9, 6);
 125 BEGIN
 126     currpos = findpos(currtime);
 127     currlat = currpos[0];
 128     currlon = currpos[1];
 129     currname = clname(currpos);
 130     currdist = cldist(currpos);
 131     RETURN(currtime || ' - ' || currlat::text || ' ' || currlon::text || ' - ' || currname || ' - ' || currdist);
 132 END;
 133 $$ LANGUAGE plpgsql; -- }}}
 134
 135 -- loop_wayp_new(): Loop gjennom alle entryene i wayp_new og legg dem inn i systemet.
 136 CREATE OR REPLACE FUNCTION loop_wayp_new() RETURNS void AS $$ -- {{{
 137 DECLARE
 138     curr_id integer;
 139     currpoint point;
 140 BEGIN
 141     UPDATE wayp_new SET coor = point(
 142         round(coor[0]::numeric, 6),
 143         round(coor[1]::numeric, 6)
 144     );
 145     LOOP
 146         curr_id = (SELECT id FROM wayp_new ORDER BY id LIMIT 1);
 147         IF curr_id IS NOT NULL THEN
 148             RAISE NOTICE 'curr_id er ikke null: %', curr_id;
 149             currpoint = (SELECT coor FROM wayp_new WHERE id = curr_id);
 150             IF (SELECT coor FROM wayp WHERE coor[0] = currpoint[0] AND coor[1] = currpoint[1] LIMIT 1) IS NOT NULL THEN
 151                 RAISE NOTICE '% finnes allerede i wayp', currpoint;
 152                 INSERT INTO wayp_rej SELECT * FROM wayp_new WHERE id = curr_id;
 153             ELSE
 154                 RAISE NOTICE '% er ikke i wayp', currpoint;
 155                 INSERT INTO wayp SELECT * FROM wayp_new WHERE id = curr_id;
 156             END IF;
 157             DELETE FROM wayp_new WHERE id = curr_id;
 158         ELSE
 159             RAISE NOTICE 'curr_id er null';
 160             EXIT;
 161         END IF;
 162     END LOOP;
 163 END;
 164 $$ LANGUAGE plpgsql; -- }}}
 165
 166 CREATE OR REPLACE FUNCTION numpoints(place varchar) RETURNS integer AS $$ -- {{{
 167 BEGIN
 168     RETURN((SELECT count(*) from logg where name = place));
 169 END;
 170 $$ LANGUAGE plpgsql; -- }}}
 171
 172 -- update_trackpoint(): Oppdater alle feltene i en viss omkrets av punktet som spesifiseres.
 173 CREATE OR REPLACE FUNCTION update_trackpoint(currpoint point) RETURNS void AS $$ -- {{{
 174 BEGIN
 175     RAISE NOTICE 'starter update_trackpoint(%), %', currpoint, clname(currpoint);
 176     UPDATE logg SET name = clname(coor), dist = cldist(coor)
 177         WHERE ($1 <-> coor) < 0.05;
 178     RAISE NOTICE 'update_trackpoint(%) er ferdig', currpoint;
 179 END;
 180 $$ LANGUAGE plpgsql;
 181 -- }}}
 182
 183 -- secmidnight(): Returnerer antall sekunder sia midnatt for en dato.
 184 CREATE OR REPLACE FUNCTION secmidnight(timestamptz) RETURNS double precision -- {{{
 185 AS $$
 186     SELECT extract(HOUR FROM $1) * 3600 + extract(MINUTE FROM $1) * 60 + extract(SECOND FROM $1);
 187 $$ LANGUAGE SQL; -- }}}