jatorre

--First, this is quick hack ported from https://github.com/fruminator/openstreetblock/

--Replace the latitude and longitude accordly on the different places and encode the whole SQL or it will not work


http://vizzuality.cartodb.com/api/v1/sql?q=

select l.name, l.osm_id   from osm_line l   join way_nodes wn   on l.osm_id = wn.way_id   where wn.node_id = (    select n.id   from nodes n   where ARRAY[n.id::integer] && (select w.nodes from osm_line l join osm_ways w on l.osm_id = w.id    where         st_intersects(way         , ST_buffer(ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326), .002)) order by way <-> ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326)       limit 1) order by n.geom <-> ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326)   limit 1) and wn.way_id != (   select osm_id from osm_line l   join osm_ways w on l.osm_id = w.id   where st_intersects(way, ST_buffer(ST_SetSRID(ST_MakePoint(-73.999548,40.71954),4326), .002)   ) LIMIT 1)     and l.name is not null and l.name != ''

jatorre

#wifi {
     [type='Free']  {marker-fill:black}
     [type!='Free'] {marker-fill:red}
     marker-width:5;
     marker-line-color:white;
}


--------------------------------------------------

jatorre

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

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

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

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

#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

UPDATE counties_results o SET pres_dem_pct = n.pres_dem_pct, pres_gop_pct = n.pres_gop_pct,
  pres_oth_pct = n.pres_oth_pct, pres_pctrpt = n.pres_pctrpt
FROM ( VALUES
(48.51, 39.69, 11.8, 100, 'AK', '02000'),
(51.65, 41.67, 6.68, 100 , 'AL' , '01001' ),
(52.14, 42.4, 5.46, 100 , 'AL' , '01003' ),
(51.94, 42.24, 5.82, 100 , 'AL' , '01005' ),
(60.28, 34.95, 4.76, 100 , 'AL' , '01007' ),
(53.19, 42.04, 4.77, 100 , 'AL' , '01009' ),
(45.07, 50.85, 4.08, 100 , 'AL' , '01011' ),

jatorre

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

Testing