Skip to content

Instantly share code, notes, and snippets.

View strk's full-sized avatar

Sandro Santilli strk

193 followers · 1 following
View GitHub Profile
@strk
strk / ver_unknown_revisions.sql
Created May 3, 2018 10:23
DROP FUNCTION IF EXISTS table_version.ver_unknown_revisions();
CREATE OR REPLACE FUNCTION table_version.ver_unknown_revisions()
RETURNS TABLE (tab TEXT, rev INT) AS
$$
DECLARE
v_rec RECORD;
BEGIN
DROP TABLE IF EXISTS tmp_ids;
CREATE TEMP TABLE tmp_ids(t text, r int);
@strk
strk / gist:9b6c7cbee02e8d3fae3f317b052c49f0
Created February 15, 2018 16:03
create table_version revision disorder
CREATE EXTENSION table_version;
CREATE TABLE IF NOT EXISTS t (k int primary key, v text);
SELECT table_version.ver_enable_versioning('public','t');
SELECT table_version.ver_create_revision('r1'); -- 1001 (empty)
SELECT table_version.ver_complete_revision();
SELECT table_version.ver_create_revision('r2'); -- 1002
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');
INSERT INTO t VALUES (3, 'c');
@strk
strk / gist:aac9149e43bc4a4ec9667efe23b35679
Last active February 15, 2018 17:31
Fix table_version revision disorder
--
-- Change ID of any revision having revision_time at a later time than
-- any other revision with higher ID
--
-- New IDs for revisions to be moved will be assigned in
-- revision_time order and start after the highest existing
-- revision ID.
--
-- The number of moved revisions is returned.
--
@strk
strk / index.html
Last active August 29, 2015 13:56 — forked from andrewxhill/index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<!--Edit the title of the page-->
<title>CartoDB Point Clustering</title>
<meta name="description" content="">
<meta name="author" content="">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/themes/css/cartodb.css" />
@strk
strk / gist:8704942
Last active August 29, 2015 13:55
WITH trip AS (
SELECT ST_MakeLine(the_geom_webmercator ORDER BY cartodb_id ASC) as g
FROM jackerouak
),
params AS (
SELECT
greatest(st_xmax(g)-st_xmin(g), st_ymax(g)-st_ymin(g))/20 as maxlen_old,
CDB_XYZ_Resolution(4)*10 as maxlen
FROM trip
),
@strk
strk / gist:8693074
Last active August 29, 2015 13:55
WITH clusters AS (
SELECT
ST_Collect(the_geom_webmercator) as g,
count(*) as count
FROM the_tornados WHERE the_geom_webmercator && !bbox!
GROUP BY ST_SnapToGrid(the_geom_webmercator, greatest(!pixel_width!,!pixel_height!)*48)
)
SELECT row_number() over () as cartodb_id,
count,
least(100, greatest(count,10)) as size,
@strk
strk / index.html
Created January 29, 2014 17:16 — forked from javierarce/index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>CartoDB Point Clustering</title>
<meta name="description" content="">
<meta name="author" content="">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/themes/css/cartodb.css" />
<!--[if lte IE 8]>
@strk
strk / gist:6608224
Created September 18, 2013 12:05
https://strk.cartodb.com/tiles/layergroup/662381ebb78b01d284a9bf18bcb74324:0/{z}/{x}/{y}.png?cache_policy=persist
@strk
strk / gist:4995973
Created February 20, 2013 14:40
Attempt to profile http request times
var start_time = Date.now();
http.get(url, function(res) {
res.body = '';
if ( res.statusCode != 200 ) {
res.on('data', function(chunk) {
// Save only first chunk, to reduce cost of the operation
if ( res.body.length == 0 ) res.body += chunk;
});
}
res.on('end', function() {
@strk
strk / kmeans.sql
Created December 4, 2012 08:31 — forked from andrewxhill/kmeans.sql
kmeans function and examples
--DROP FUNCTION axh_kmeans_geom_sort(geometry[], integer, integer);
CREATE OR REPLACE FUNCTION axh_kmeans_geom_sort(the_geom geometry[], clusters integer DEFAULT NULL, iterations integer DEFAULT NULL)
RETURNS SETOF GEOMETRY
AS $$
DECLARE
h GEOMETRY; -- rectangle cell
c INTEGER; --