Last active
December 17, 2015 09:29
-
-
Save andrewxhill/5588148 to your computer and use it in GitHub Desktop.
On the fly size sort of polygons in CartoDB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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