Click on the space between any of the pairs of polygons to update the map.
Last active
August 29, 2015 14:21
-
-
Save jsanz/60050dbfe104da69f15e to your computer and use it in GitHub Desktop.
Fill empty space between two polygons
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 DATA AS( | |
SELECT * FROM jsanz.table_150512_fill_void_test | |
ORDER BY ST_Distance( | |
ST_Transform(THE_GEOM,3857), | |
ST_Transform(ST_GeomFromText('POINT(17.9 -2.3)',4326),3857) | |
) | |
LIMIT 5 ), -- This grabs the 5 closest polygons on a table to an input point | |
PAIRS AS ( | |
SELECT | |
d1.cartodb_id as d1id, | |
ST_GeometryN(d1.the_geom,1) as the_geom1, -- Ensure single polygons | |
d2.cartodb_id as d2id, | |
ST_GeometryN(d2.the_geom,1) as the_geom2 | |
FROM data d1 JOIN data d2 | |
ON d1.cartodb_id < d2.cartodb_id | |
ORDER BY d1id ), -- This creates unique pairs of polygons | |
CANDIDATES AS ( | |
SELECT | |
d1id, d2id, the_geom1, the_geom2 | |
FROM PAIRS | |
WHERE ST_Intersects(ST_Buffer(the_geom1,0.05),ST_Buffer(the_geom2,0.05)) | |
), -- This filters to only those that are close each other (at least 0.05 degrees) | |
RINGS as ( | |
SELECT | |
d1id, d2id, | |
ST_DumpRings(ST_Union( | |
ST_Buffer(the_geom1,.05), | |
ST_Buffer(the_geom2,.05) | |
)) as gdump | |
FROM CANDIDATES | |
) -- This for every pair buffers each polygon, union them and get the interior rings | |
SELECT | |
1 as cartodb_id, | |
d1id, d2id, | |
ST_Transform(ST_Buffer((RINGS.gdump).geom,.05),3857) as the_geom_webmercator | |
FROM RINGS | |
WHERE | |
(RINGS.gdump).path[1] > 0 and -- Get only interior rings (shell is = 0) | |
(RINGS.gdump).geom is not null and | |
ST_Intersects((RINGS.gdump).geom,ST_GeomFromText('POINT(17.9 -2.3)',4326)) | |
-- Finally only it's returned the polygon that intersects the initial point | |
-- applying a buffer and a schema suitable for 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
<!DOCTYPE html> | |
<html> | |
<head> | |
<title>Fill space between polygons</title> | |
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" /> | |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/> | |
<link rel="shortcut icon" href="http://cartodb.com/assets/favicon.ico" /> | |
<style> | |
/* basic style for full screen map */ | |
html, body, #map { | |
height: 100%; | |
padding: 0; | |
margin: 0; | |
} | |
/* style for the leaflet messagebox */ | |
.leaflet-control-messagebox { | |
display: none; /* Initially hidden */ | |
border: 2px solid black; | |
background-color: white; | |
padding: 3px 10px; | |
} | |
</style> | |
<!-- Include the cartodb CSS --> | |
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/3.14/themes/css/cartodb.css" /> | |
</head> | |
<body> | |
<div id="map"></div> | |
<!-- Include the cartodb.js Library --> | |
<script src="http://libs.cartocdn.com/cartodb.js/v3/3.14/cartodb.js"></script> | |
<!-- Include the leaflet messagebox Library --> | |
<script src="https://www.grendelman.net/leaflet/leaflet-messagebox/leaflet-messagebox.js"></script> | |
<script> | |
function main() { | |
var allLayers; | |
var box; | |
// Instantiate the CartoDBJS visualization | |
cartodb.createVis('map', | |
'https://team.cartodb.com/u/jsanz/api/v2/viz/3f8b04a4-f8b7-11e4-a5ef-0e853d047bba/viz.json', | |
{ | |
shareable: false, | |
title: false, | |
description: false, | |
search: false, | |
tiles_loader: true, | |
center_lat: -1.9, | |
center_lon: 18.2, | |
zoom: 6, | |
cartodb_logo: false | |
}) | |
.done(function(vis, layers) { | |
var map = vis.getNativeMap(); | |
allLayers = layers[1]; | |
box = L.control.messagebox({timeout:10000, position: 'bottomleft'}).addTo(map); | |
map.on('click',function(e){ | |
// Show on the messagebox the coordinates clicked | |
box.show( 'click:' + e.latlng.lat.toFixed(2) + ',' + e.latlng.lng.toFixed(2) ); | |
// Create some SQL from the clicked point | |
var WKT4326 = 'ST_GeomFromText(\'POINT(' + e.latlng.lng + ' ' + e.latlng.lat + ')\',4326)'; | |
var WKT = 'ST_Transform(' + WKT4326 + ',3857)'; | |
// Update Layer of the point | |
var SQL = 'select 1 as cartodb_id, ' + WKT + ' as the_geom_webmercator'; | |
cartodb.log.log('Updating point layer with this new SQL:',SQL); | |
var layerToModify = allLayers.getSubLayer(2); | |
layerToModify.setSQL(SQL); | |
// Update the layer that fills the empty space | |
SQL = 'WITH DATA AS(SELECT * FROM jsanz.table_150512_fill_void_test ORDER BY ST_Distance(ST_Transform(THE_GEOM,3857),' + WKT + ') LIMIT 5 ), PAIRS AS (SELECT d1.cartodb_id as d1id, ST_GeometryN(d1.the_geom,1) as the_geom1, d2.cartodb_id as d2id, ST_GeometryN(d2.the_geom,1) as the_geom2 FROM data d1 JOIN data d2 ON d1.cartodb_id < d2.cartodb_id ORDER BY d1id ), CANDIDATES AS (SELECT d1id, d2id, the_geom1, the_geom2 FROM PAIRS WHERE ST_Intersects(ST_Buffer(the_geom1,0.05),ST_Buffer(the_geom2,0.05)) ), RINGS as (SELECT d1id, d2id, ST_DumpRings(ST_Union(ST_Buffer(the_geom1,.05), ST_Buffer(the_geom2,.05) )) as gdump FROM CANDIDATES ) SELECT 1 as cartodb_id, d1id, d2id, ST_Transform(ST_Buffer((RINGS.gdump).geom,.05),3857) as the_geom_webmercator FROM RINGS WHERE (RINGS.gdump).path[1] > 0 and (RINGS.gdump).geom is not null and ST_Intersects((RINGS.gdump).geom,' + WKT4326 + ')' | |
cartodb.log.log('Updating void layer with this new SQL:',SQL); | |
var layerToModify = allLayers.getSubLayer(0); | |
layerToModify.setSQL(SQL); | |
}); | |
}) | |
.error(function(err) { | |
console.log(err); | |
}); | |
} | |
window.onload = main; | |
</script> | |
</body> | |
</html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment