Skip to content

Instantly share code, notes, and snippets.

@vpicavet
Last active August 29, 2015 14:06
Show Gist options
  • Save vpicavet/073d2faecfe7df57417d to your computer and use it in GitHub Desktop.
Save vpicavet/073d2faecfe7df57417d to your computer and use it in GitHub Desktop.
-- Open These views with QGIS
-- You can label the ticks view with this expression : "CASE WHEN m % 5 = 0 THEN h END" to get hour numbers
-- Set categorized styles on typ and unit to set different styles for dials, and for the hands
-- Please tweet your best clock designs with #PostGISClock hashtag
create or replace view ticks as
select
m as id
, case when m % 5 = 0 then 'h' else 'm' end as typ
, m % 5 as h
, m as m
, st_makepoint(
cos(m::double precision * -pi() / 30.0 + pi() / 2)
, sin(m::double precision * -pi() / 30.0 + pi() / 2)
)
from
generate_series(1, 60) as m;
create or replace view clock as
with hms (id, unit, length, nb, val) as (
values (1, 'h', 0.3, 12, extract('hour' from current_time))
, (2, 'm', 0.6, 60, extract('minute' from current_time))
, (3, 's', 0.9, 60, extract('second' from current_time))
)
select
hms.id
, st_makeline(
st_makepoint(0, 0)
, st_makepoint(
hms.length * cos(val::double precision * -pi() / 6.0 + pi() / 2)
, hms.length * sin(val::double precision * -pi() / 6.0 + pi() / 2)
)
) as geom
from
hms;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment