Skip to content

Instantly share code, notes, and snippets.

View jatorre's full-sized avatar

Javier de la Torre jatorre

95 followers · 7 following
View GitHub Profile
@jatorre
jatorre / gist:1854494
Created February 17, 2012 17:30
My first use of rank window functions
SELECT *, rank() OVER (PARTITION BY challenge_id ORDER BY user_num_captures DESC) AS rank,
(SELECT COUNT(*) FROM captures WHERE  challenge_id = subq.challenge_id) as total_captures
FROM (SELECT challenge_id,ch.title, player_id,count(*) AS user_num_captures FROM captures AS ca INNER JOIN challenges AS ch ON ca.challenge_id=ch.cartodb_id GROUP BY challenge_id,ch.title,player_id) AS subq
@jatorre
jatorre / gist:3702691
Created September 11, 2012 22:39
<?php
require("Encoding.php");
//Get IDs
$jsonurl = "http://lamp.sla.ny.gov/ArcGIS/rest/services/sla/MapServer/4/query?text=&geometry=&geometryType=esriGeometryPoint&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&objectIds=&where=1%3D1&time=&returnIdsOnly=true&returnGeometry=true&maxAllowableOffset=&outSR=4326&outFields=OBJECTID%2CShape%2CSerialNo%2CLicenseType%2CLicenseClass%2CZone%2CCountyCode%2CName%2CDBA%2CAddress1%2CAddress2%2CCity%2CState%2CZIP%2CLicenseCat%2CLicenseSym&f=json";
$json = file_get_contents($jsonurl,0,null,null);
$json_output = json_decode($json);
@jatorre
jatorre / gist:3709197
Created September 12, 2012 19:11
-- Moving Hawaii and Alaska closer to mainland
--Hawaii
UPDATE map_guardian_gop SET the_geom = ST_Transform(ST_Translate(the_geom_webmercator,5500000,800000),4326) WHERE state_fips='15'
--Alaska, move and rotate
UPDATE map_guardian_gop SET the_geom = ST_Transform(ST_Scale(ST_Translate(the_geom_webmercator,-11000000,-3500000),0.5,0.5),4326) WHERE state_fips='02'
UPDATE map_guardian_gop SET the_geom = ST_Transform(ST_Rotate(the_geom_webmercator,pi()/8,-16500000,2800000),4326) WHERE state_fips='02'
@jatorre
jatorre / gist:3891312
Created October 15, 2012 08:04
Calculating Stratos distances
//Furthest distance travelled from start point. // http://javi.cartodb.com/api/v2/sql?q=
WITH points AS
(SELECT
(SELECT (the_geom) FROM stratos ORDER BY id ASC LIMIT 1) as p1,
(SELECT (the_geom) FROM stratos
WHERE ST_x(the_geom)<180 AND ST_x(the_geom)>-180 AND ST_x(the_geom)<>0
AND ST_x(the_geom)<>0 ORDER BY ST_Distance(the_geom,(SELECT the_geom
FROM stratos ORDER BY id ASC LIMIT 1)) DESC LIMIT 1) as p2
)
SELECT ST_distance(p1::geography,p2::geography) as furthest_distance FROM points
@jatorre
jatorre / gist:3892131
Created October 15, 2012 12:07
#in myquery.sql you can have a VEERY BIG SQL file that you need to run on CartoDB, like a huge INSERT or UPDATE
curl --data "api_key=XXXXXXXXXXXXXXXXXXXXXXXXX&q=`cat myquery.sql`" https://jatorre.cartodb.com/api/v1/sql
@jatorre
jatorre / index.html
Created November 22, 2012 23:04
<!DOCTYPE html>
<html lang="en">
<head>
<style type="text/css">
html, body {height: 100%; padding: 0; margin: 0;}
#map {position:absolute; width:100%; top:0; right:0; bottom:0; left:0;}
</style>
<link href="http://staging20.cartodb.com/assets/leaflet-510ae051a5d99e48b066cfdc9b22c20b.css" media="screen" rel="stylesheet" type="text/css" />
<link href="http://staging20.cartodb.com/assets/cdb/themes/css/cartodb-3836c012509ff34e44900ab95393c88f.css" media="screen" rel="stylesheet" type="text/css" />
@jatorre
jatorre / README.md
Created November 22, 2012 22:10 — forked from mbostock/.block
d3.geo.tile

A demo of the d3.geo.tile plugin, which determines which 256x256 tiles are visible in the viewport based on a scale and translate. This demo combines the tile plugin with d3.behavior.zoom for panning and zooming, resulting in a a simple slippy map. Based partly on an example by Tom MacWright.

@jatorre
jatorre / README.md
Created November 22, 2012 22:47
Example CartoDB 2.0 map

Testing

@jatorre
jatorre / gist:4154502
Created November 27, 2012 14:28
Combine multiple Census files into single shpefile using PostGIS
mkdir tmpfolder;
cd tmpfolder;
wget "http://www2.census.gov/geo/tiger/TIGER2012/TRACT/tl_2012_01_tract.zip";
unzip tl_2012_01_tract.zip;
shp2pgsql -s4326 -p tl_2012_01_tract.shp census | psql -hlocalhost -Upostgres -dcensus;
rm -rf tl_2012_01_tract.*;
for i in {1..9} :;
do
wget "http://www2.census.gov/geo/tiger/TIGER2012/TRACT/tl_2012_0${i}_tract.zip";
unzip tl_2012_0${i}_tract.zip
@jatorre
jatorre / gist:4162875
Created November 28, 2012 17:56
/** bubble visualization */
#tm{
marker-fill: #FF5C00;
marker-line-color: #FFF;
marker-line-width: 2;
marker-line-opacity: 1;
marker-opacity: 0.8;
marker-placement: point;
marker-type: ellipse;