branches/gpst.spread/postgres/create_views.sql

   1  -- $Id$
   2
   3 CREATE OR REPLACE VIEW siste_aar
   4     AS SELECT * FROM (
   5         SELECT DISTINCT ON (
   6             sted, date_trunc('week', date)
   7         ) *
   8         FROM logg
   9         WHERE date > now() + interval '1 year ago'
  10     ) AS s
  11     ORDER BY date;
  12
  13 CREATE OR REPLACE VIEW siste_halvaar
  14     AS SELECT * FROM (
  15         SELECT DISTINCT ON (
  16             sted, date_trunc('week', date)
  17         ) *
  18         FROM logg
  19         WHERE date > now() + interval '0.5 year ago'
  20     ) AS s
  21     ORDER BY date;
  22
  23 CREATE OR REPLACE VIEW siste_maaned
  24     AS SELECT * FROM (
  25         SELECT DISTINCT ON (
  26             sted, date_trunc('hour', date)
  27         ) *
  28         FROM logg
  29         WHERE date > now() + interval '1 month ago'
  30     ) AS s
  31     ORDER BY date;
  32
  33 CREATE OR REPLACE VIEW siste_uke
  34     AS SELECT * FROM (
  35         SELECT DISTINCT ON (
  36             sted, date_trunc('hour', date)
  37         ) *
  38         FROM logg
  39         WHERE date > now()+interval '1 week ago'
  40     ) AS s
  41     ORDER BY date;
  42
  43 CREATE OR REPLACE VIEW siste_dogn
  44     AS SELECT * FROM (
  45         SELECT DISTINCT ON (
  46             sted, date_trunc('minute', date)
  47         ) *
  48         FROM logg
  49         WHERE date > now()+interval '1 day ago'
  50     ) AS s
  51     ORDER BY date;
  52
  53 /*** De 50.000 punktene med høyest fjernesthjemmefrahet. ***/
  54
  55 CREATE OR REPLACE VIEW fjernest
  56     AS SELECT * FROM logg
  57         ORDER BY avst DESC LIMIT 50000;
  58
  59 CREATE OR REPLACE VIEW fjernest_siste_aar
  60     AS SELECT * FROM logg
  61         WHERE date > now()+interval '1 year ago'
  62         ORDER BY avst DESC LIMIT 50000;
  63
  64 CREATE OR REPLACE VIEW fjernest_siste_halvaar
  65     AS SELECT * FROM logg
  66         WHERE date > now()+interval '0.5 year ago'
  67         ORDER BY avst DESC LIMIT 50000;
  68
  69 CREATE OR REPLACE VIEW fjernest_siste_maaned
  70     AS SELECT * FROM logg
  71         WHERE date > now() + interval '1 month ago'
  72         ORDER BY avst DESC LIMIT 50000;
  73
  74 CREATE OR REPLACE VIEW fjernest_siste_uke
  75     AS SELECT * FROM logg
  76         WHERE date > now() + interval '1 week ago'
  77         ORDER BY avst DESC LIMIT 50000;
  78
  79 CREATE OR REPLACE VIEW fjernest_siste_dogn
  80     AS SELECT * FROM logg
  81         WHERE date > now() + interval '1 day ago'
  82         ORDER BY avst DESC LIMIT 50000;
  83
  84 /*** Intervaller ***/
  85
  86 CREATE OR REPLACE VIEW minutt
  87     AS SELECT * FROM (
  88         SELECT DISTINCT ON (
  89             date_trunc('minute', date)
  90         ) *
  91         FROM logg
  92     ) AS s
  93     ORDER BY date DESC;
  94
  95 /*** Formater ***/
  96
  97 CREATE OR REPLACE VIEW closest AS
  98     SELECT * FROM (
  99         SELECT DISTINCT ON (sted) * FROM (
 100             SELECT * FROM LOGG
 101                 ORDER BY dist
 102         ) AS b
 103         WHERE sted IS NOT NULL
 104     ) AS a
 105         ORDER BY date;
 106
 107 CREATE OR REPLACE VIEW gpx AS
 108     SELECT '<trkpt lat="' || coor[0] || '" lon="' || coor[1] || '"> ' ||
 109         '<ele>' || ele || '</ele> ' ||
 110         '<time>' || date || '</time> ' ||
 111     '</trkpt>'
 112     AS gpx,
 113     date, coor, ele, sted, dist, description
 114     FROM logg;
 115
 116 CREATE OR REPLACE VIEW gpst AS
 117     SELECT date, coor, ele, sted, dist, avst,
 118     '<tp> <time>' || date at time zone 'UTC' || 'Z' || '</time> <lat>' || coor[0] || '</lat> <lon>' || coor[1] || '</lon> </tp>'
 119     AS gpst
 120     FROM logg;
 121
 122 -- Lister ut events sammen med loggen.
 123 CREATE OR REPLACE VIEW ev AS
 124     SELECT * FROM (
 125         SELECT     'gps' AS flag, date, coor, sted || ' (' || dist || ')' AS sted, ele::numeric(8,1), NULL AS descr, avst
 126             FROM logg
 127         UNION ALL
 128         SELECT   'event' AS flag, date, coor, NULL, NULL, descr AS descr, NULL
 129             FROM events
 130         UNION ALL
 131         SELECT     'pic' AS flag, date, coor, filename, NULL, NULL, NULL
 132             FROM pictures
 133     ) AS u
 134     ORDER BY date;
 135
 136 CREATE OR REPLACE VIEW wp AS
 137     SELECT
 138         coor AS coor,
 139         substr(name, 1, 20) AS name,
 140         type AS type,
 141         substr(cmt, 1, 20) AS cmt,
 142         ele AS ele,
 143         time AS time
 144         FROM wayp
 145         ORDER BY coor[0] desc, coor[1];