Pharmacies in the Czech Republic with opening hours and GPS

-- lékárny, které mají otevřeno v neděli ve 20:00, s adresou a GPS
SELECT l.nazev, l.ruian_adresa, l.lat, l.lon, o.otevreno_od, o.otevreno_do
FROM lekarny.lekarny l JOIN lekarny.oteviraci_doba o USING (kod_pracoviste)
WHERE o.den_cislo = 7 AND NOT o.zavreno
  AND (o.nepretrzite OR (TIME '20:00' >= o.otevreno_od AND TIME '20:00' < o.otevreno_do));

-- deset nejbližších lékáren k bodu (Brno, náměstí Svobody), vzdálenost v km
SELECT nazev, ruian_adresa,
       round((6371 * acos(least(1, cos(radians(49.1951)) * cos(radians(lat)) * cos(radians(lon) - radians(16.6068))
                                   + sin(radians(49.1951)) * sin(radians(lat)))))::numeric, 2) AS km
FROM lekarny.lekarny WHERE lat IS NOT NULL ORDER BY km LIMIT 10;

-- počet lékáren a lékáren s pohotovostí podle krajů
SELECT kraj_nazev, count(*) AS lekaren, count(*) FILTER (WHERE pohotovost) AS s_pohotovosti,
       round(avg(hodin_tydne), 1) AS prumer_hodin_tydne
FROM 'parquet/lekarny.parquet' GROUP BY 1 ORDER BY 2 DESC;