branches/gpst.postgis/postgres/create_views.sql

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