jatorre’s gists

jatorre / gist:7210438

Created October 29, 2013 07:35

Geocoder/Reverse geocoder inside SQL

	CREATE TYPE geocode_response AS (match_level text, latitude float, longitude float, address text, the_geom geometry);
	CREATE OR REPLACE FUNCTION cdb_geocode(text) RETURNS SETOF geocode_response
	AS $$ DECLARE
	token text :='xxxx';
	app_id text :='xxxx';
	BEGIN

	RETURN QUERY WITH geocode_response AS (
	SELECT status, content_type,
	content::json->'Response'->'View'->0->'Result'->0 as geocode_result

jatorre / gist:7336704

Last active December 27, 2015 13:59

	WITH summary_groups as(
	WITH summary as (
	select (row_number() over (order by time asc nulls last)+1)/2 as rownum,time
	from sensor_log_2013_10_27_12_01
	order by time asc)

	SELECT
	max(time) OVER(PARTITION BY rownum) -
	min(time) OVER(PARTITION BY rownum) diff
	FROM summary)

jatorre / gist:7401056

Created November 10, 2013 17:19

Removing the requirement for xml support on resolving tables function

	-- Return an array of table names scanned by a given query
	--
	-- Requires PostgreSQL 9.x+
	--
	CREATE OR REPLACE FUNCTION CDB_QueryTables(query text)
	RETURNS name[]
	AS $$
	DECLARE
	exp JSON;
	tables NAME[];

jatorre / gist:9352791

Last active August 29, 2015 13:57

Vizzuality is looking for an awesome Office Manager in Madrid

	Who we are:
	We are building the easiest and most powerful data mapping engine ever. We have more than 40.000
	loving users (growing rapidly) and customers from all around the world. We have received awesome
	reviews from all the key tech and startup media like Mashable today. And we have offices in
	downtown Madrid and New York.

	Our team of engineers and designers are changing the way maps are made. This is a big task, we
	need to scale! Because of that we need to complement our strong technical arm with your
	organizational expertise and results driven attitude.

jatorre / gist:9368356

Last active August 29, 2015 13:57

CartoDB 2 Sales/Account manager position

	CartoDB 2 Sales/Account manager position

	Who we are:
	We are building the easiest and most powerful data mapping engine ever.
	We have more than 40.000 loving users (growing rapidly) and customers
	from all around the world. We have received awesome reviews from all the
	key tech and startup media like Mashable today. And we have offices in
	downtown Madrid and New York.

	Our team of engineers and designers are changing the way maps are made.

jatorre / gist:11371647

Created April 28, 2014 13:13

	SELECT *,
	'http://maps.googleapis.com/maps/api/streetview?size=300x190&location='\|\|
	ST_Y((the_geom))\|\|','\|\|ST_X((the_geom))
	\|\|'&sensor=false&fov=110' as image
	FROM table_2012_entire_tree_down_complaints_sandy

jatorre / gist:f2d5801a7365dc4ab364

Created July 10, 2014 14:16

Exampel SQL

https://stagingfonmaps.cartodb.com/api/v2/sql?q=SELECT%20external_id,%20ST_Y(the_geom)%20AS%20lat,%20ST_X(the_geom)%20AS%20lon,%20network_id,%20category_NAME,%20status,%20hotspots,%20metadata%20FROM%20pois%20WHERE%20the_geom%20%26%26%20ST_MakeEnvelope(0,%200,%20180,%2090,%204326)

jatorre / gist:ff39d8e195fb286992ef

Created August 4, 2014 22:18

http://osmbuildings.cartodb.com/api/v2/sql?format=geojson&dp=6&q=SELECT%20cartodb_id%2CST_Intersection(CASE%20WHEN%20ST_Dimension(ST_Snap(ST_CollectionExtract(ST_SnapToGrid(ST_CollectionExtract(ST_Simplify(%22the_geom%22%2C%200.03055572509765625)%2C%20ST_Dimension(%22the_geom%22)%20%2B%201)%2C%200.0611114501953125)%2C%20ST_Dimension(%22the_geom%22)%20%2B%201)%2C%20ST_SnapToGrid(ST_Expand(ST_MakeEnvelope(-35.947265625%2C19.89072302399691%2C-20.302734375%2C33.797408767572485%2C%204326)%2C%207.3333740234375)%2C0.0611114501953125)%2C%200.0611114501953125))%20%3D%200%20OR%20GeometryType(ST_Snap(ST_CollectionExtract(ST_SnapToGrid(ST_CollectionExtract(ST_Simplify(%22the_geom%22%2C%200.03055572509765625)%2C%20ST_Dimension(%22the_geom%22)%20%2B%201)%2C%200.0611114501953125)%2C%20ST_Dimension(%22the_geom%22)%20%2B%201)%2C%20ST_SnapToGrid(ST_Expand(ST_MakeEnvelope(-35.947265625%2C19.89072302399691%2C-20.302734375%2C33.797408767572485%2C%204326)%2C%207.3333740234375)%2C0.0611114501953125)%2C%200.0611114501953125))%20%3D%

jatorre / index.html

Last active August 29, 2015 14:11 — 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" />

jatorre / gist:1377437b4b80e0ab11f8

Created December 28, 2014 17:19

Embed this interactive visualization

<iframe width='100%' height='520' frameborder='0' src='http://osm2.cartodb.com/viz/9f8764fa-8eaf-11e4-ae69-0e9d821ea90d/embed_map' allowfullscreen webkitallowfullscreen mozallowfullscreen oallowfullscreen msallowfullscreen></iframe>

	SELECT *,
	'http://maps.googleapis.com/maps/api/streetview?size=300x190&location='\|\|
	ST_Y((the_geom))\|\|','\|\|ST_X((the_geom))
	\|\|'&sensor=false&fov=110' as image
	FROM table_2012_entire_tree_down_complaints_sandy

Javier de la Torre jatorre