Skip to content

Instantly share code, notes, and snippets.

@andrewxhill

andrewxhill/index.html

Last active December 17, 2015 09:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save andrewxhill/5588148 to your computer and use it in GitHub Desktop.
Save andrewxhill/5588148 to your computer and use it in GitHub Desktop.
Download ZIP
On the fly size sort of polygons in CartoDB
Raw
index.html
<html>
<head>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
<style>
html, body, #map {
height: 100%;
padding: 0;
margin: 0;
}
</style>
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v2/themes/css/cartodb.css" />
<!--[if lte IE 8]>
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v2/themes/css/cartodb.ie.css" />
<![endif]-->
</head>
<body>
<div id="map"></div>
</body>
<!-- include cartodb.js library -->
<script src="http://libs.cartocdn.com/cartodb.js/v2/cartodb.js"></script>
<style type='text/css'>
html, body {
margin: 0;
padding: 0;
height: 100%;
width: 100%;
background: white;
}
#map{
height: 100%;
width: 100%;
background: white;
}
#tooltip {
position:absolute;
right: 100px;
width:140px;
height:auto;
padding:10px;
z-index:1000;
display:none;
background:white;
border-color:#DDDDDD!important;
font:normal 12px Arial!important;
color:#666666!important;
}
#tooltip label {font-weight:bold; width: 100%;}
</style>
<script>
// starting latitude and longitude for our map
var position = new L.LatLng(45.525302, -122.6701);
// starting zoom
var zoom = 16;
// is our Leaflet map object
var map = new L.Map('map').setView(position, zoom)
, mapboxUrl = 'http://{s}.tiles.mapbox.com/v3/cartodb.map-1nh578vv/{z}/{x}/{y}.png'
//, mapboxUrl = 'http://tile.stamen.com/toner/{z}/{x}/{y}.jpg'
, basemap = new L.TileLayer(mapboxUrl, {
maxZoom: 20,
attribution: "CartoDB Tutorials"
});
cartodb.createLayer(map, 'http://osm2.cartodb.com/api/v1/viz/osm_export_polygon/viz.json', {
query: "WITH RECURSIVE dims AS (SELECT 2*sqrt(sum(ST_Area(the_geom))) as d, sqrt(sum(ST_Area(the_geom)))/20 as w, count(*) as rows FROM osm_export_polygon WHERE the_geom IS NOT NULL), geoms AS (SELECT the_geom, cartodb_id, ST_YMax(the_geom)-ST_YMin(the_geom) as height FROM osm_export_polygon WHERE the_geom IS NOT NULL ORDER BY ST_YMax(the_geom)-ST_YMin(the_geom) DESC), geomval AS (SELECT the_geom, cartodb_id, row_number() OVER (ORDER BY height DESC) as id from geoms), positions(cartodb_id, the_geom,x_offset,y_offset,new_row,row_offset) AS ( (SELECT cartodb_id, the_geom, 0.0::float, 0.0::float, FALSE, 2 from geomval limit 1) UNION ALL ( SELECT (SELECT cartodb_id FROM geomval WHERE id = p.row_offset), (SELECT the_geom FROM geomval WHERE id = p.row_offset), CASE WHEN p.x_offset < s.d THEN (SELECT (s.w+(ST_XMax(the_geom) - ST_XMin(the_geom)))+p.x_offset FROM geomval WHERE id = p.row_offset) ELSE 0 END as x_offset , CASE WHEN p.x_offset < s.d THEN p.y_offset ELSE (SELECT (s.w+(ST_YMax(the_geom) - ST_YMin(the_geom)))+p.y_offset FROM geomval WHERE id = p.row_offset) END as y_offset , FALSE, p.row_offset+1 FROM positions p, dims s WHERE p.row_offset < s.rows ) ), sfact AS ( SELECT ST_XMin(the_geom) as x, ST_YMax(the_geom) as y FROM geomval LIMIT 1 ) SELECT ST_Transform(ST_Translate( the_geom, (x - ST_XMin(the_geom) - x_offset), (y - ST_YMin(the_geom) - y_offset)),3857) as the_geom_webmercator, cartodb_id FROM positions,sfact order by row_offset asc ",
tile_style: "#{{table_name}}{polygon-fill: red; line-color: #FFFFFF;}"})
.on('done', function(layer) {
map.addLayer(layer);
layer.on('featureOver', function(e, pos, latlng, data) {
cartodb.log.log(e, pos, latlng, data);
});
layer.on('error', function(err) {
cartodb.log.log('error: ' + err);
});
}).on('error', function() {
cartodb.log.log("some error occurred");
});
</script>
</body>
</html>
Raw
osm_export_polygon.sql
WITH RECURSIVE
dims AS (
SELECT 2*sqrt(sum(ST_Area(the_geom))) as d, sqrt(sum(ST_Area(the_geom)))/20 as w, count(*) as rows FROM osm_export_polygon WHERE the_geom IS NOT NULL),
geoms AS (
SELECT the_geom, cartodb_id, ST_YMax(the_geom)-ST_YMin(the_geom) as height FROM osm_export_polygon WHERE the_geom IS NOT NULL ORDER BY ST_YMax(the_geom)-ST_YMin(the_geom) DESC),
geomval AS (
SELECT the_geom, cartodb_id, row_number() OVER (ORDER BY height DESC) as id from geoms),
positions(cartodb_id, the_geom,x_offset,y_offset,new_row,row_offset) AS (
(SELECT cartodb_id, the_geom, 0.0::float, 0.0::float, FALSE, 2 from geomval limit 1)
UNION ALL
(SELECT
(SELECT cartodb_id FROM geomval WHERE id = p.row_offset),
(SELECT the_geom FROM geomval WHERE id = p.row_offset),
CASE WHEN p.x_offset < s.d THEN (SELECT (s.w+(ST_XMax(the_geom) - ST_XMin(the_geom)))+p.x_offset FROM geomval WHERE id = p.row_offset) ELSE 0 END as x_offset,
CASE WHEN p.x_offset < s.d THEN p.y_offset ELSE (SELECT (s.w+(ST_YMax(the_geom) - ST_YMin(the_geom)))+p.y_offset FROM geomval WHERE id = p.row_offset) END as y_offset , FALSE, p.row_offset+1
FROM positions p, dims s
WHERE p.row_offset < s.rows ) ),
sfact AS (
SELECT ST_XMin(the_geom) as x, ST_YMax(the_geom) as y FROM geomval LIMIT 1 )
SELECT
ST_Transform(ST_Translate( the_geom, (x - ST_XMin(the_geom) - x_offset), (y - ST_YMin(the_geom) - y_offset)),3857) as the_geom_webmercator, cartodb_id
FROM positions,sfact
order by row_offset asc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment