Skip to content

Instantly share code, notes, and snippets.

@crstn

crstn/definer.sql

Last active August 31, 2016 04:17
Show Gist options
  • Save crstn/5ca52b2c577724db0e51 to your computer and use it in GitHub Desktop.
Save crstn/5ca52b2c577724db0e51 to your computer and use it in GitHub Desktop.
Download ZIP
Editing data in a CartoDB table via Leaflet with leaflet.draw plugin. Heavily inspired by http://blog.cartodb.com/read-and-write-to-cartodb-with-the-leaflet-draw-plugin/.
Raw
definer.sql
DROP FUNCTION IF EXISTS carsten_upsert_pois(int[], text[]);
-- Returns a set of op,cartodb_id values where op means:
--
-- deleted: -1
-- updated: 0
-- inserted: 1
--
CREATE OR REPLACE FUNCTION carsten_upsert_pois(
cartodb_ids integer[],
geojsons text[])
RETURNS TABLE(op int, cartodb_id int)
LANGUAGE plpgsql SECURITY DEFINER
RETURNS NULL ON NULL INPUT
AS $$
DECLARE
sql text;
BEGIN
sql := 'WITH n(cartodb_id,the_geom) AS (VALUES ';
--Iterate over the values
FOR i in 1 .. array_upper(geojsons, 1)
LOOP
IF i > 1 THEN sql := sql || ','; END IF;
sql :=sql || '('||cartodb_ids[i]||','
|| 'ST_SetSRID(ST_GeomFromGeoJSON(NULLIF('''|| geojsons[i] ||''','''')),4326))';
END LOOP;
sql := sql || '), do_update AS ('
|| 'UPDATE pois p '
|| 'SET the_geom=n.the_geom FROM n WHERE p.cartodb_id = n.cartodb_id '
|| 'AND n.the_geom IS NOT NULL '
|| 'RETURNING p.cartodb_id ), do_delete AS ('
|| 'DELETE FROM pois p WHERE p.cartodb_id IN ('
|| 'SELECT n.cartodb_id FROM n WHERE cartodb_id >= 0 AND '
|| ' n.the_geom IS NULL ) RETURNING p.cartodb_id ), do_insert AS ('
|| 'INSERT INTO pois (the_geom)'
|| 'SELECT n.the_geom FROM n WHERE n.cartodb_id < 0 AND '
|| ' n.the_geom IS NOT NULL RETURNING cartodb_id ) '
|| 'SELECT 0,cartodb_id FROM do_update UNION ALL '
|| 'SELECT 1,cartodb_id FROM do_insert UNION ALL '
|| 'SELECT -1,cartodb_id FROM do_delete';
RAISE DEBUG '%', sql;
RETURN QUERY EXECUTE sql;
END;
$$;
--Grant access to the public user
GRANT EXECUTE ON FUNCTION carsten_upsert_pois(integer[],text[]) TO publicuser;
Raw
index.html
<!DOCTYPE html>
<html>
<head>
<title>CartoDB Editing via Leaflet</title>
<link rel="stylesheet" href="libs/leaflet.css" />
<link rel="stylesheet" href="libs/leaflet.draw.css" />
<script src="libs/leaflet-src.js"></script>
<script src="libs/leaflet.draw.js"></script>
<script src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
<style>
body {
padding: 0;
margin: 0;
}
html,
body,
#map {
height: 100%;
}
</style>
</head>
<body>
<div id="map"></div>
<script>
// create a map in the "map" div, set the view to a given place and zoom
var map = L.map('map').setView([19.4378, -99.0706], 11);
// add an OpenStreetMap tile layer
L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {
attribution: '&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors'
}).addTo(map);
// Initialise the FeatureGroup to store editable layers
var drawnItems = new L.FeatureGroup();
//Add previous data
var url = "https://carsten.cartodb.com/api/v2/sql?format=geojson&q=SELECT cartodb_id,the_geom FROM pois";
$.getJSON(url, function(data) {
geojsonLayer = L.geoJson(data, {
onEachFeature: function(feature, layer) {
layer.cartodb_id = feature.properties.cartodb_id;
drawnItems.addLayer(layer);
}
});
map.addLayer(drawnItems);
// Initialise the draw control and pass it the FeatureGroup of editable layers
var drawControl = new L.Control.Draw({
edit: {
featureGroup: drawnItems
}
});
map.addControl(drawControl);
map.on('draw:created', function(e) {
drawnItems.addLayer(e.layer);
persistOnCartoDB("INSERT", e.layer);
});
map.on('draw:edited', function(e) {
persistOnCartoDB("UPDATE", e.layers);
});
map.on('draw:deleted', function(e) {
persistOnCartoDB("DELETE", e.layers);
});
function persistOnCartoDB(action, layers) {
var cartodb_ids = [];
var geojsons = [];
switch (action) {
case "UPDATE":
if (layers.getLayers().length < 1) return;
layers.eachLayer(function(layer) {
cartodb_ids.push(layer.cartodb_id);
geojsons.push("'" + JSON.stringify(layer.toGeoJSON()) + "'");
});
break;
case "INSERT":
cartodb_ids.push(-1);
geojsons.push("'" + JSON.stringify(layers.toGeoJSON()) + "'");
break;
case "DELETE":
layers.eachLayer(function(layer) {
cartodb_ids.push(layer.cartodb_id);
geojsons.push("''");
});
break;
}
var sql = "SELECT carsten_upsert_pois(ARRAY[";
sql += cartodb_ids.join(",");
sql += "],ARRAY[";
sql += geojsons.join(",");
sql += "]);";
console.log("persisting... https://carsten.cartodb.com/api/v2/sql?q=" + sql);
$.ajax({
type: 'POST',
url: 'https://carsten.cartodb.com/api/v2/sql',
crossDomain: true,
data: {
"q": sql
},
dataType: 'json',
success: function(responseData, textStatus, jqXHR) {
console.log("Data saved");
if (action == "INSERT")
layers.cartodb_id = responseData.rows[0].cartodb_id;
},
error: function(responseData, textStatus, errorThrown) {
console.log("Problem saving the data");
console.log(responseData);
console.log(textStatus);
console.log(errorThrown);
}
});
}
});
</script>
</body>
Raw
step1.html
<!DOCTYPE html>
<html>
<head>
<title>CartoDB Editing via Leaflet</title>
<link rel="stylesheet" href="libs/leaflet.css" />
<script src="libs/leaflet-src.js"></script>
<style>
body {
padding: 0;
margin: 0;
}
html,
body,
#map {
height: 100%;
}
</style>
</head>
<body>
<div id="map"></div>
<script>
// create a map in the "map" div, set the view to a given place and zoom
var map = L.map('map').setView([19.4378, -99.0706], 11);
// add an OpenStreetMap tile layer
L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {
attribution: '&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors'
}).addTo(map);
</script>
</body>
Raw
step2.html
<!DOCTYPE html>
<html>
<head>
<title>CartoDB Editing via Leaflet</title>
<link rel="stylesheet" href="libs/leaflet.css" />
<script src="libs/leaflet-src.js"></script>
<script src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
<style>
body {
padding: 0;
margin: 0;
}
html,
body,
#map {
height: 100%;
}
</style>
</head>
<body>
<div id="map"></div>
<script>
// create a map in the "map" div, set the view to a given place and zoom
var map = L.map('map').setView([19.4378, -99.0706], 11);
// add an OpenStreetMap tile layer
L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {
attribution: '&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors'
}).addTo(map);
// Initialise the FeatureGroup to store editable layers
var drawnItems = new L.FeatureGroup();
//Add previous data
var url = "https://carsten.cartodb.com/api/v2/sql?format=geojson&q=SELECT cartodb_id,the_geom FROM pois";
$.getJSON(url, function(data) {
geojsonLayer = L.geoJson(data, {
onEachFeature: function(feature, layer) {
layer.cartodb_id = feature.properties.cartodb_id;
drawnItems.addLayer(layer);
}
});
map.addLayer(drawnItems);
});
</script>
</body>
Raw
step3.html
<!DOCTYPE html>
<html>
<head>
<title>CartoDB Editing via Leaflet</title>
<link rel="stylesheet" href="libs/leaflet.css" />
<link rel="stylesheet" href="libs/leaflet.draw.css" />
<script src="libs/leaflet-src.js"></script>
<script src="libs/leaflet.draw.js"></script>
<script src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
<style>
body {
padding: 0;
margin: 0;
}
html,
body,
#map {
height: 100%;
}
</style>
</head>
<body>
<div id="map"></div>
<script>
// create a map in the "map" div, set the view to a given place and zoom
var map = L.map('map').setView([19.4378, -99.0706], 11);
// add an OpenStreetMap tile layer
L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {
attribution: '&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors'
}).addTo(map);
// Initialise the FeatureGroup to store editable layers
var drawnItems = new L.FeatureGroup();
//Add previous data
var url = "https://carsten.cartodb.com/api/v2/sql?format=geojson&q=SELECT cartodb_id,the_geom FROM pois";
$.getJSON(url, function(data) {
geojsonLayer = L.geoJson(data, {
onEachFeature: function(feature, layer) {
layer.cartodb_id = feature.properties.cartodb_id;
drawnItems.addLayer(layer);
}
});
map.addLayer(drawnItems);
// Initialise the draw control and pass it the FeatureGroup of editable layers
var drawControl = new L.Control.Draw({
edit: {
featureGroup: drawnItems
}
});
map.addControl(drawControl);
});
</script>
</body>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment