branches/gpst.gpxfix/postgres/create_views.sql

   1 -- $Id$
   2 -- File ID: 24babc2a-fafb-11dd-96fe-000475e441b9
   3
   4 -- siste_aar: List ut alle plasser siste år, DISTINCT ON name og hver uke
   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 ut alle plasser siste halvår, DISTINCT ON name og hver uke
  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 ut alle plasser siste måned, DISTINCT ON name og hver time
  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 ut alle plasser siste uka, DISTINCT ON name og hver time
  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 ut alle plasser siste døgn, DISTINCT ON name og hvert minutt
  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 AT TIME ZONE 'UTC' || 'Z' || '</time> <lat>' || coor[0] || '</lat> <lon>' || coor[1] || '</lon> </tp>'
 112     AS gpst
 113     FROM logg; -- }}}
 114
 115 -- ev: Lister ut events sammen med loggen.
 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     ) AS u
 127     ORDER BY date; -- }}}
 128
 129 -- wp: Lister ut veipunktene, sortert nord → sør, vest → øst
 130 CREATE OR REPLACE VIEW wp AS -- {{{
 131     SELECT
 132         coor AS coor,
 133         substr(name, 1, 20) AS name,
 134         type AS type,
 135         numpoints as nump,
 136         substr(cmt, 1, 20) AS cmt,
 137         ele AS ele,
 138         time AS time
 139         FROM wayp
 140         ORDER BY coor[0] DESC, coor[1]; -- }}}