Skip to content

Instantly share code, notes, and snippets.

@jsanz
Last active August 29, 2015 14:21
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 jsanz/60050dbfe104da69f15e to your computer and use it in GitHub Desktop.
Save jsanz/60050dbfe104da69f15e to your computer and use it in GitHub Desktop.
Fill empty space between two polygons

Click on the space between any of the pairs of polygons to update the map.

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