andrewxhill

#points_na{
	marker-fill:#FF6600;
	marker-opacity:0;
	marker-width:35;
	marker-line-color:white;
	marker-line-width:3;
	marker-line-opacity:0;
	marker-placement:point;
	marker-type:ellipse;
	marker-allow-overlap:true;

andrewxhill

#! /usr/bin/env python
#
# $Id: raster2pgsql.py 7038 2011-04-15 17:56:46Z jorgearevalo $
#
# This is a simple utility used to dump GDAL dataset into HEX WKB stream.
# It's considered as a prototype of raster2pgsql tool planned to develop
# in future.
# For more details about raster2pgsql tool, see Specification page:
# http://trac.osgeo.org/postgis/wiki/WKTRaster
#

andrewxhill

WITH f as (SELECT
ST_LongestLine(ST_SetSRID(ST_MakePoint(-3,55),4326),ST_SetSRID(ST_Union(ST_MakePoint(-1,51),ST_MakePoint(-1,49)),4326)) as line) 
SELECT ST_Distance_Sphere(ST_StartPoint(f.line),ST_EndPoint(f.line)) FROM f

andrewxhill

CREATE AGGREGATE vector_sum (
    sfunc = AXH_Vector_Sum,
    basetype = int[],
    stype = int[],
    initcond = '{0}'
);

DROP FUNCTION IF EXISTS AXH_Vector_Sum(int[], int[]);
CREATE OR REPLACE FUNCTION AXH_Vector_Sum(int[], int[]) 
    RETURNS int[] AS $$

andrewxhill

http://wri-01.cartodb.com/tiles/rivers/7/43/66.png?sql=select 1 as lid, the_geom_webmercator from rivers UNION select 2 as lid, the_geom_webmercator from country_attributes_live where iso = 'BRA' &map_key=&style=
@adm1_line_color:#FFF;
@adm1_line_glowColor:#000;
#rivers {
[lid=1] {
  [zoom<=6]{
    line-width:0;
  }

andrewxhill

WITH foo AS (SELECT ST_AsGeoJSON(ST_Centroid(ST_Envelope(admin1_attributes_live.the_geom))) as center, ST_AsGeoJSON(ST_Envelope(admin1_attributes_live.the_geom)) as bbox, name_1 as name, id1 FROM admin1_attributes_live WHERE admin1_attributes_live.iso = 'IDN') SELECT center, bbox, foo.name, foo.id1, sum(global_4x_grid.total_incr) as total FROM foo,global_4x_grid WHERE global_4x_grid.iso='IDN' AND global_4x_grid.id1 = foo.id1 group by foo.id1, foo.name, center, bbox ORDER BY total; 

--HUMAN READABLE


WITH foo AS
    (SELECT 
        ST_AsGeoJSON(ST_Centroid(ST_Envelope(admin1_attributes_live.the_geom))) as center, 
        ST_AsGeoJSON(ST_Envelope(admin1_attributes_live.the_geom)) as bbox,
        name_1 as name,

andrewxhill

function drawVisualization() {
  // Create and populate the data table.
  var data = new google.visualization.DataTable();
  data.addColumn('string', 'country');
  data.addColumn('number', '2005');
  data.addColumn('number', '2010');
  data.addRow(["Andorra", 34.04255319, 34.04255319]);
  data.addRow(["Belarus",  41.59148055,  42.55004437]);
  data.addRow(["Comoros",  2.688172043,  1.612903226]);
  data.addRow(["Gabon",  85.38052548,  85.38052548]);

andrewxhill

WITH inp as ( SELECT ST_Centroid(ST_Collect(ST_envelope(the_geom_webmercator))) as centroid,
                    ST_ConvexHull(ST_Collect(ST_Envelope(the_geom_webmercator))) as hull
 FROM table_name GROUP BY some_column)
SELECT ST_Buffer(centroid,
                ST_Length( ST_LongestLine(centroid, hull))) as the_geom_webmercator
FROM inp;

andrewxhill

CREATE TYPE CDB_DensityGridPair AS (
    geom GEOMETRY,
    val FLOAT
);
CREATE OR REPLACE FUNCTION CDB_DensityGrid(table_name TEXT, geom_name TEXT, dimension FLOAT)
RETURNS SETOF CDB_DensityGridPair AS $$
DECLARE
BEGIN
	RETURN QUERY
    	EXECUTE 'SELECT   ST_Envelope(GEOMETRYFROMTEXT(''LINESTRING(''||(st_xmax(geom)+(seed/2))||'' ''||(st_ymax(geom)+(seed/2))||'', ''||(st_xmin(geom)-(seed/2))||'' ''||(st_ymin(geom)-(seed/2))||'')'',3857)) as geom, val FROM (SELECT '||dimension||' as seed, count(*)::float as val,

andrewxhill

 GET_PROJECTS_QUERY_TEMPLATE: "WITH qu AS ( " +
   "        SELECT  " +
   "            P.nexso_code, COUNT(S.cartodb_id) AS solution_count, P.cartodb_id AS project_id, P.title, P.approval_date, P.fixed_approval_date, P.external_project_url,  " +
   "            P.location_verbatim, T.name AS topic_name, P.solution_id AS solution_id, P.budget, S.name AS solution_name, S.nexso_url AS solution_url,  " +
   "            A.external_url AS agency_url, A.name AS agency_name, ST_AsGeoJSON(A.the_geom) AS agency_position, " +
   "            PWA.the_geom " +
   "        FROM  " +
   "            v3_projects P LEFT JOIN v1_solutions S ON (P.solution_id = S.cartodb_id) " +
   "            LEFT JOIN v1_agencies A ON (P.agency_id = A.cartodb_id) "+
   "            LEFT JOIN v1_topics AS T ON (P.topic_id = T.cartodb_id),  " +