postgres/create_views.sql

   1 -- create_views.sql
   2 -- File ID: 24babc2a-fafb-11dd-96fe-000475e441b9
   3
   4 -- siste_aar: List all places from last year, DISTINCT ON name and every week
   5 CREATE OR REPLACE VIEW siste_aar -- {{{
   6     AS SELECT * FROM (
   7         SELECT DISTINCT ON (
   8             name, date_trunc('week', date)
   9         ) *
  10         FROM logg
  11         WHERE date > now() + interval '1 year ago'
  12     ) AS s
  13     ORDER BY date; -- }}}
  14
  15 -- siste_halvaar: List all places from last six months, DISTINCT ON name and every week
  16 CREATE OR REPLACE VIEW siste_halvaar -- {{{
  17     AS SELECT * FROM (
  18         SELECT DISTINCT ON (
  19             name, date_trunc('week', date)
  20         ) *
  21         FROM logg
  22         WHERE date > now() + interval '0.5 year ago'
  23     ) AS s
  24     ORDER BY date; -- }}}
  25
  26 -- siste_maaned: List all places from last month, DISTINCT ON name and every hour
  27 CREATE OR REPLACE VIEW siste_maaned -- {{{
  28     AS SELECT * FROM (
  29         SELECT DISTINCT ON (
  30             name, date_trunc('hour', date)
  31         ) *
  32         FROM logg
  33         WHERE date > now() + interval '1 month ago'
  34     ) AS s
  35     ORDER BY date; -- }}}
  36
  37 -- siste_uke: List all places from last week, DISTINCT ON name and every hour
  38 CREATE OR REPLACE VIEW siste_uke -- {{{
  39     AS SELECT * FROM (
  40         SELECT DISTINCT ON (
  41             name, date_trunc('hour', date)
  42         ) *
  43         FROM logg
  44         WHERE date > now()+interval '1 week ago'
  45     ) AS s
  46     ORDER BY date; -- }}}
  47
  48 -- siste_dogn: List all places from the last 24 hours, DISTINCT ON name and every minute
  49 CREATE OR REPLACE VIEW siste_dogn -- {{{
  50     AS SELECT * FROM (
  51         SELECT DISTINCT ON (
  52             name, date_trunc('minute', date)
  53         ) *
  54         FROM logg
  55         WHERE date > now()+interval '1 day ago'
  56     ) AS s
  57     ORDER BY date; -- }}}
  58
  59 /*** Intervaller ***/
  60
  61 CREATE OR REPLACE VIEW minutt -- {{{
  62     AS SELECT * FROM (
  63         SELECT DISTINCT ON (
  64             date_trunc('minute', date)
  65         ) *
  66         FROM logg
  67     ) AS s
  68     ORDER BY date; -- }}}
  69 CREATE OR REPLACE VIEW minuttname -- {{{
  70     AS SELECT * FROM (
  71         SELECT DISTINCT ON (
  72             date_trunc('minute', date),
  73             name
  74         ) *
  75         FROM logg
  76     ) AS s
  77     ORDER BY date; -- }}}
  78 CREATE OR REPLACE VIEW hourname -- {{{
  79     AS SELECT * FROM (
  80         SELECT DISTINCT ON (
  81             date_trunc('hour', date),
  82             name
  83         ) *
  84         FROM logg
  85     ) AS s
  86     ORDER BY date; -- }}}
  87
  88 /*** Formater ***/
  89
  90 CREATE OR REPLACE VIEW closest AS -- {{{
  91     SELECT * FROM (
  92         SELECT DISTINCT ON (name) * FROM (
  93             SELECT * FROM LOGG
  94                 ORDER BY dist
  95         ) AS b
  96         WHERE name IS NOT NULL
  97     ) AS a
  98         ORDER BY date; -- }}}
  99
 100 CREATE OR REPLACE VIEW gpx AS -- {{{
 101     SELECT '<trkpt lat="' || coor[0] || '" lon="' || coor[1] || '"> ' ||
 102         '<ele>' || ele || '</ele> ' ||
 103         '<time>' || date || '</time> ' ||
 104     '</trkpt>'
 105     AS gpx,
 106     date, coor, ele, name, dist, description
 107     FROM logg; -- }}}
 108
 109 CREATE OR REPLACE VIEW gpst AS -- {{{
 110     SELECT date, coor, ele, name, dist,
 111     '<tp> <time>' || date || 'Z' || '</time> <lat>' || coor[0] || '</lat> <lon>' || coor[1] || '</lon> </tp>'
 112     AS gpst
 113     FROM logg; -- }}}
 114
 115 -- ev: List events and the log
 116 CREATE OR REPLACE VIEW ev AS -- {{{
 117     SELECT * FROM (
 118         SELECT     'gps' AS flag, date, coor, name || ' (' || dist || ')' AS name, ele::numeric(8,1), NULL AS descr
 119             FROM logg
 120         UNION ALL
 121         SELECT   'event' AS flag, date, coor, NULL, NULL, descr AS descr
 122             FROM events
 123         UNION ALL
 124         SELECT     'pic' AS flag, date, coor, filename, NULL, NULL
 125             FROM pictures
 126         UNION ALL
 127         SELECT    'film' AS flag, date, coor, filename, NULL, descr
 128             FROM film
 129         UNION ALL
 130         SELECT    'lyd' AS flag, date, coor, filename, NULL, descr
 131             FROM lyd
 132     ) AS u
 133     ORDER BY date; -- }}}
 134
 135 CREATE OR REPLACE VIEW media AS -- {{{
 136     SELECT 'film' AS what,
 137         date,
 138         filename,
 139         clname(coor) AS name,
 140         cldist(coor) AS dist,
 141         coor
 142         FROM film
 143     UNION ALL
 144     SELECT 'pic', date, filename, clname(coor), cldist(coor), coor
 145         FROM pictures
 146     UNION ALL
 147     SELECT 'lyd', date, filename, clname(coor), cldist(coor), coor
 148         FROM lyd
 149     ORDER BY date; -- }}}
 150
 151 -- wp: List all waypoints, sorted north → south, west → east
 152 CREATE OR REPLACE VIEW wp AS -- {{{
 153     SELECT
 154         coor AS coor,
 155         substr(name, 1, 20) AS name,
 156         type AS type,
 157         numpoints as nump,
 158         substr(cmt, 1, 20) AS cmt,
 159         ele AS ele,
 160         time AS time
 161         FROM wayp
 162         ORDER BY coor[0] DESC, coor[1]; -- }}}