jatorre

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

-- 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

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

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

--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 

jatorre

#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;

jatorre

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

# 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"

jatorre

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

# 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"