Skip to content

Instantly share code, notes, and snippets.

Avatar

Alberto Romeu alrocar

View GitHub Profile
@alrocar
alrocar / clickhouse_to_markdown.md
Last active Aug 17, 2021
Export from ClickHouse to Markdown table
View clickhouse_to_markdown.md

SQL

SELECT
  number a,
  number + 1 b,
  number + 2 c
FROM numbers(100) INTO OUTFILE '/tmp/tt.md'
FORMAT Template
SETTINGS
@alrocar
alrocar / anomaly_detection.pipe
Created Jun 22, 2021
Z-score based anomaly detection
View anomaly_detection.pipe
NODE context
DESCRIPTION >
Based on https://blog.tinybird.co/2021/06/24/anomaly-detection/
SQL >
%
SELECT
sum(qty) qty,
toStartOfInterval(utc_date, INTERVAL {{Int32(interval_duration, 10)}} second) date
FROM
@alrocar
alrocar / ingest_from_array.py
Created Jul 20, 2020
Ingest to Tinybird from a Python array
View ingest_from_array.py
import csv
import requests
from io import StringIO
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry
rows = [[1, 2, 3], [4, 5, 6]]
endpoint = 'https://api.tinybird.co'
View kuviz.html
<iframe src="https://public.carto.com/kuviz/f8dcf0ef-45e5-4578-a245-af443c870ffa" height="600" width="600" />
<iframe src="https://www.youtube.com/embed/enMumwvLAug" frameborder="0" allowfullscreen="true"> </iframe>
View collect-terrain-tiles.py
#!/usr/bin/env python
from __future__ import print_function
from math import log, tan, pi
from itertools import product
from argparse import ArgumentParser
from os.path import join, splitext
import tempfile, shutil, urllib, io, sys, subprocess
tile_url = 'https://elevation-tiles-prod.s3.amazonaws.com/geotiff/{z}/{x}/{y}.tif'
View create_buildings_named_map.py
import os
import sys
import argparse
from carto.auth import APIKeyAuthClient
from carto.maps import NamedMapManager
parser = argparse.ArgumentParser(description=(
'Example of CopySQLClient usage to stream data from NEXRAD Level 2'
View import_us_buildings.py
import os
import sys
import argparse
try:
from urllib2 import urlopen
except ImportError:
from urllib.request import urlopen
try:
from StringIO import BytesIO
@alrocar
alrocar / animate lines script
Last active Jul 5, 2018
animate lines script
View animate lines script
with lines as
(SELECT cartodb_id, color, ord, ST_Segmentize(ST_MakeLine(st_centroid(the_geom), lead(st_centroid(the_geom)) OVER (ORDER BY ord))::geography, 100000)::geometry as the_geom
FROM world_borders_four_colors_1_adjacency_list),
tosplit AS (
SELECT * FROM lines
WHERE ST_XMax(the_geom) - ST_XMin(the_geom) > 180
),
nosplit AS (
SELECT * FROM lines
WHERE ST_XMax(the_geom) - ST_XMin(the_geom) <= 180
@alrocar
alrocar / high quality gif
Last active May 26, 2018
high-quality-gif
View high quality gif
# first create a palette of the portion of the video with more colors
ffmpeg -y -ss 20 -t 3 -i input.mp4 -vf fps=50,scale=640:-1:flags=lanczos,palettegen palette.png
# then use the palette to obtain high quality colors
ffmpeg -i input.mp4 -i palette.png -filter_complex "fps=50,scale=640:-1:flags=lanczos[x];[x][1:v]paletteuse" output.gif
# -ss -> start from second
# -t -> duration
View PostGIS functions for map coloring
CREATE OR REPLACE FUNCTION adjacency_list(table_name regclass, user_name text) RETURNS void AS $$
BEGIN
EXECUTE format('DROP TABLE IF EXISTS %s_adjacency_list;
CREATE TABLE %s_adjacency_list AS
SELECT DISTINCT a.cartodb_id,
array_agg(b.cartodb_id) over (PARTITION BY a.cartodb_id) AS adjacent,
count(b.*) over (PARTITION BY a.cartodb_id) AS valence,
0 AS color
FROM %s a,
%s b