postgres/create_funcs.sql

   1 -- create_funcs.sql
   2 -- File ID: 1a0fae52-fafb-11dd-b392-000475e441b9
   3
   4 -- clname(): Return the name of the closest waypoint in 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(): Return the distance (in degrees) to the closest waypoint in 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(): Calculate coordinates for a specific point in time that’s between two track points.
  35 CREATE OR REPLACE FUNCTION findpos(currtime timestamp) RETURNS point AS $$ -- {{{
  36 DECLARE
  37     firstdate timestamp;
  38     lastdate timestamp;
  39     firsttime timestamp;
  40     firstcoor point;
  41     lasttime timestamp;
  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(): Return a string with date, position, closest name and distance to the nearest point.
 118 CREATE OR REPLACE FUNCTION wherepos(currtime timestamp) 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 through all the entries in wayp_new and add them to wayp.
 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             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 '% already exists in wayp', currpoint;
 151                 INSERT INTO wayp_rej SELECT * FROM wayp_new WHERE id = curr_id;
 152             ELSE
 153                 INSERT INTO wayp SELECT * FROM wayp_new WHERE id = curr_id;
 154             END IF;
 155             DELETE FROM wayp_new WHERE id = curr_id;
 156         ELSE
 157             EXIT;
 158         END IF;
 159     END LOOP;
 160 END;
 161 $$ LANGUAGE plpgsql; -- }}}
 162
 163 CREATE OR REPLACE FUNCTION numpoints(place varchar) RETURNS integer AS $$ -- {{{
 164 BEGIN
 165     RETURN((SELECT count(*) from logg where name = place));
 166 END;
 167 $$ LANGUAGE plpgsql; -- }}}
 168
 169 -- update_trackpoint(): Update all fields in a fixed radius from the specified coordinate.
 170 CREATE OR REPLACE FUNCTION update_trackpoint(currpoint point) RETURNS void AS $$ -- {{{
 171 BEGIN
 172     RAISE NOTICE 'starting update_trackpoint(%), %', currpoint, clname(currpoint);
 173     UPDATE logg SET name = clname(coor), dist = cldist(coor)
 174         WHERE ($1 <-> coor) < 0.05;
 175     RAISE NOTICE 'update_trackpoint(%) is finished', currpoint;
 176 END;
 177 $$ LANGUAGE plpgsql;
 178 -- }}}
 179
 180 -- secmidnight(): Return number of seconds since midnight for a specified date.
 181 CREATE OR REPLACE FUNCTION secmidnight(timestamp) RETURNS double precision -- {{{
 182 AS $$
 183     SELECT extract(HOUR FROM $1) * 3600 + extract(MINUTE FROM $1) * 60 + extract(SECOND FROM $1);
 184 $$ LANGUAGE SQL; -- }}}
 185
 186 -- nullable(): Make it possible to use SELECT with NULL string.
 187 CREATE OR REPLACE FUNCTION nullable(t text) RETURNS text -- {{{
 188 AS $$
 189 BEGIN
 190     IF t IS NULL THEN
 191         RETURN '';
 192     ELSE
 193         RETURN t;
 194     END IF;
 195 END;
 196 $$ LANGUAGE plpgsql; -- }}}