branches/gpst.postgis/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     -- RAISE NOTICE '-----------------------------------';
  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     -- RAISE NOTICE 'currtime = %', currtime;
  79     -- RAISE NOTICE 'firsttime = %, firstcoor = %', firsttime, firstcoor;
  80     -- RAISE NOTICE 'lasttime = %, lastcoor = %', lasttime, lastcoor;
  81
  82     IF firsttime = lasttime THEN
  83         RETURN(firstcoor);
  84     END IF;
  85
  86     currlat = firstcoor[0] +
  87     (
  88         (
  89             lastcoor[0]-firstcoor[0]
  90         ) *
  91         (
  92             (
  93                 EXTRACT(EPOCH FROM currtime)-EXTRACT(EPOCH FROM firsttime)
  94             )
  95             /
  96             (
  97                 EXTRACT(EPOCH FROM lasttime)-EXTRACT(EPOCH FROM firsttime)
  98             )
  99         )
 100     );
 101     currlon = firstcoor[1] +
 102     (
 103         (
 104             lastcoor[1]-firstcoor[1]
 105         ) *
 106         (
 107             (
 108                 EXTRACT(EPOCH FROM currtime)-EXTRACT(EPOCH FROM firsttime)
 109             )
 110             /
 111             (
 112                 EXTRACT(EPOCH FROM lasttime)-EXTRACT(EPOCH FROM firsttime)
 113             )
 114         )
 115     );
 116     -- RAISE NOTICE 'currcoor = (%,%)', currlat, currlon;
 117     RETURN (currlat,currlon);
 118 END;
 119 $$ LANGUAGE plpgsql; -- }}}
 120
 121 -- wherepos(): Returnerer en streng med dato, posisjon, nærmeste navn og avstand til nærmeste punkt.
 122 CREATE OR REPLACE FUNCTION wherepos(currtime timestamptz) RETURNS text AS $$ -- {{{
 123 DECLARE
 124     currpos point;
 125     currsted text;
 126     currdist numeric;
 127     currlat numeric(9, 6);
 128     currlon numeric(9, 6);
 129 BEGIN
 130     currpos = findpos(currtime);
 131     currlat = currpos[0];
 132     currlon = currpos[1];
 133     currsted = clname(currpos);
 134     currdist = cldist(currpos);
 135     RETURN(currtime || ' - ' || currlat::text || ' ' || currlon::text || ' - ' || currsted || ' - ' || currdist);
 136 END;
 137 $$ LANGUAGE plpgsql; -- }}}
 138
 139 -- loop_wayp_new(): Loop gjennom alle entryene i wayp_new og legg dem inn i systemet.
 140 CREATE OR REPLACE FUNCTION loop_wayp_new() RETURNS void AS $$ -- {{{
 141 DECLARE
 142     curr_id integer;
 143     currpoint point;
 144 BEGIN
 145     -- FOR curr IN SELECT * FROM wayp_new LOOP
 146     --     NULL;
 147     -- END LOOP;
 148     LOOP
 149         curr_id = first_wayp_new();
 150         IF curr_id IS NOT NULL THEN
 151             RAISE NOTICE 'curr_id er ikke null: %', curr_id;
 152             currpoint = (SELECT coor FROM wayp_new WHERE id = curr_id);
 153             IF (SELECT coor FROM wayp WHERE coor[0] = currpoint[0] AND coor[1] = currpoint[1]) IS NOT NULL THEN
 154                 RAISE NOTICE '% finnes allerede i wayp', currpoint;
 155                 INSERT INTO wayp_rej SELECT * FROM wayp_new WHERE id = curr_id;
 156             ELSE
 157                 RAISE NOTICE '% er ikke i wayp', currpoint;
 158                 INSERT INTO wayp SELECT * FROM wayp_new WHERE id = curr_id;
 159                 PERFORM update_trackpoint(currpoint);
 160             END IF;
 161             DELETE FROM wayp_new WHERE id = curr_id;
 162             -- COPY (SELECT name FROM wayp WHERE coor::varchar = currpoint::varchar)
 163             --     TO STDOUT;
 164         ELSE
 165             RAISE NOTICE 'currpoint er null';
 166             EXIT;
 167         END IF;
 168     END LOOP;
 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     -- Avstanden hjemmefra.
 177     UPDATE logg SET avst = '(60.42543,5.29959)'::point <-> $1
 178         WHERE ($1 <-> coor) < 0.05;
 179     RAISE NOTICE 'avst ferdig';
 180     -- Sted og dist
 181     UPDATE logg SET sted = clname(coor), dist = cldist(coor)
 182         WHERE ($1 <-> coor) < 0.05;
 183     RAISE NOTICE 'update_trackpoint(%) er ferdig', currpoint;
 184 END;
 185 $$ LANGUAGE plpgsql;
 186 -- }}}
 187
 188 -- first_wayp_new(): Returnerer id for den eldste i wayp_new.
 189 CREATE OR REPLACE FUNCTION first_wayp_new() RETURNS integer AS $$ -- {{{
 190 BEGIN
 191     RETURN (SELECT id FROM wayp_new ORDER BY id LIMIT 1);
 192 END
 193 $$ LANGUAGE plpgsql;
 194 -- }}}
 195
 196 -- secmidnight(): Returnerer antall sekunder sia midnatt for en dato.
 197 CREATE OR REPLACE FUNCTION secmidnight(timestamptz) RETURNS double precision -- {{{
 198 AS $$
 199 SELECT extract(hour from $1) * 3600 + extract(minute from $1) * 60 + extract(second from $1);
 200 $$ LANGUAGE SQL; -- }}}