Skip to content

Instantly share code, notes, and snippets.

@jatorre
jatorre / v_get_tile.plsql
Created December 8, 2010 22:17
a plsql function to get a PostGIS geom out of a tile defined by X,Y,Z. Returns on SRS 900913
CREATE OR REPLACE FUNCTION v_get_tile(x integer,y integer,z integer)
RETURNS geometry AS
$BODY$
DECLARE
origin_shift CONSTANT FLOAT := 20037508.342789244;
initial_resolution CONSTANT FLOAT := 156543.03392804062;
res float;
minx float;
miny float;
maxx float;
@jatorre
jatorre / v_get_tile.plsql
Created December 15, 2010 15:06
A function to create a PostGIS geometry out of a Google Tile X/Y/Z definition
-- Function: v_get_tile(integer, integer, integer)
-- DROP FUNCTION v_get_tile(integer, integer, integer);
CREATE OR REPLACE FUNCTION v_get_tile(x integer, y integer, z integer)
RETURNS geometry AS
$BODY$
DECLARE
origin_shift CONSTANT FLOAT := 20037508.342789244;
initial_resolution CONSTANT FLOAT := 156543.03392804062;
@jatorre
jatorre / vizz_where20_proposal
Created January 20, 2011 23:36
Vizzuality proposal for Where2.0
Proposal: "Mapping to save Biodiversity"
Proposer
Javier de la Torre (jatorre@vizzuality.com)
Description
Biodiversity is the term used to describe the incredible variety of life that has evolved on our
planet over billions of years. From crowdsourcing, mobile app, and web apps, there is a new trend on
how citizens and map developers can help to save the world. This talk covers the latest on Citizen
science and maping, data visualization and challenges where we need your help.
@jatorre
jatorre / readshp.py
Created April 24, 2011 19:53
A python script to detect encoding and SRID of a shapefile GUESSING its best
from chardet.universaldetector import UniversalDetector
import os.path
import sys
import dbfUtils
import sys
from osgeo import osr
from urllib import urlencode
from urllib2 import urlopen
import json
@jatorre
jatorre / gist:1314768
Created October 25, 2011 23:16
Closest bathrooms to a location
--https://jatorre.cartodb.com/api/v1/sql?q=
select
ST_X(ST_Centroid(the_geom)) as longitude,
ST_Y(ST_Centroid(the_geom)) as latitude,
bathroom,class,comfortsta,comments,descriptio,name,organizati,status,
ST_Distance(the_geom::geography,
ST_PointFromText('POINT(-73.999548 40.71954)', 4326)::geography) as distance
from bathrooms
ORDER BY distance ASC
#run this on your map of points
SELECT ST_Transform(ST_ConvexHull(ST_Collect(the_geom)),3857) AS the_geom_webmercator FROM my_spottings
(adjust)
#Change the style to Custom CartoCSS because now you need to apply a polygon style as you are no longer visualizing points
#my_spottings{
polygon-fill:#FFCC00;
http://demo.cartodb.com/api/v1/sql?q=select ST_X(ST_Centroid(the_geom)) as longitude,ST_Y(ST_Centroid(the_geom)) as latitude,bathroom,class,comfortsta,comments,descriptio,name,organizati,status,ST_Distance(the_geom::geography,ST_PointFromText('POINT(-73.999548 40.71954)', 4326)::geography) as distance from bathrooms ORDER BY distance ASC LIMIT 10
@jatorre
jatorre / gist:1607894
Created January 13, 2012 18:22
Ruby script to export a SimpleGeo Layer to CSV
# The following script allows to export a SimpleGeo Storage layer to a CSV
# You have to change the credentials and the layer that you want to export
# It will generate a CSV file with the name of the layer
#
# Author: Javier de la Torre (jatorre@vizzuality.com @jatorre)
#Adapt the following to your SimpleGeo credentials. Get it from the UI.
oauth_token="token"
oauth_secret="secret"
http://vizzuality.cartodb.com/api/v1/sql?q=
SELECT * FROM (SELECT lat,lon,name, ST_Distance(ST_SetSRID(ST_MakePoint(-73.9967,40.724805),4326)::geography,the_geom::geography) as distance_meters FROM com_vizzuality_nycbathrooms) as q WHERE distance_meters < 1500
@jatorre
jatorre / gist:1610866
Created January 14, 2012 09:56
Script to download all layers from SimpleGeo storage to CSV files
# The following script allows to export a SimpleGeo Storage layer to a CSV
# You have to change the credentials and the layer that you want to export
# It will generate a CSV file with the name of the layer
#
# Author: Javier de la Torre (jatorre@vizzuality.com @jatorre)
#Adapt the following to your SimpleGeo credentials. Get it from the UI.
oauth_token="token"
oauth_secret="secret"