postgres/create_funcs.sql

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