Skip to content

Instantly share code, notes, and snippets.

@andrewbt

andrewbt/.block

Created January 12, 2017 01:53
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 andrewbt/24bf9d8cf7cf241b2472c0c91a976b89 to your computer and use it in GitHub Desktop.
Save andrewbt/24bf9d8cf7cf241b2472c0c91a976b89 to your computer and use it in GitHub Desktop.
Download ZIP
CARTO + Leaflet.draw example
Raw
.block
license: mit
Raw
README.md

CARTO + Leaflet.draw

A more complete, working version of this blog post for creating, editing, and deleting points from or to a CARTO points-geometry table. Some modifications would be needed on the front-end side to have this work for a polygons or lines table. CARTO has a limitation of only one type of geometry per table, so you would either want to limit users to Leaflet.draw'ing one geometry type, or otherwise creating new/separate functions and loading from separate tables for different types.

Built with blockbuilder.org

Raw
index.html
<!DOCTYPE html>
<html>
<head>
<title>Leaflet Draw</title>
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/3.15/themes/css/cartodb.css" />
<script src="http://libs.cartocdn.com/cartodb.js/v3/3.15/cartodb.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/leaflet.draw/0.4.3/leaflet.draw.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/leaflet.draw/0.4.3/leaflet.draw.js"></script>
</head>
<body>
<div id="map" style="position: absolute; top: 0; left: 0; width: 80%; height: 80%;"></div>
<script>
//initialize the map with Leaflet
var map = new L.Map('map', {center: new L.LatLng(40.7, -74), zoom: 11});
//add a carto basemap tile layer
var carto = L.tileLayer('https://cartodb-basemaps-{s}.global.ssl.fastly.net/light_all/{z}/{x}/{y}@2x.png', {
maxZoom: 18,
attribution: '&copy; <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a>, &copy; <a href="https://carto.com/attributions">CARTO</a>'
}).addTo(map);
//prep the draw FeatureGroup and add Leaflet.draw controls to map
var drawnItems = new L.FeatureGroup();
var drawControl = new L.Control.Draw({
position: 'topright',
draw: {
polyline: false,
polygon: false,
circle: false,
rectangle: false,
marker: true
},
edit: {
featureGroup: drawnItems,
remove: true
}
});
map.addControl(drawControl);
//create cartodb.SQL object to grab geojson of table
//see here https://carto.com/blog/the-versatility-of-retreiving-and-rendering-geospatial
var sql = new cartodb.SQL({ user: 'athompson', format:'GeoJSON'});
sql.execute("SELECT * FROM areas_of_interest_newyork_nycsanitation")
.done(function(data) {
//add geojson features to the drawnItems FeatureGroup
//console.log(data);//optional/debugging
geojsonLayer = L.geoJson(data, {
onEachFeature: function (feature, layer) {
layer.cartodb_id=feature.properties.cartodb_id;
drawnItems.addLayer(layer);
}
});
//add the drawnItems FeatureGroup, populated with geojson from carto table, to the map
map.addLayer(drawnItems);
})
.error(function(errors) {
// errors contains a list of errors
console.log("errors:" + errors);
});
function persistOnCartoDB(action, layers) {
/*
this function interacts with the Security Definer
function previously defined in our CARTO account.
Gets an action (update, insert, or delete) and a list
of GeoJSON objects (the geometry objects only, to work
with ST_GeomFromGeojson()) with which to change the table.
see http://gis.stackexchange.com/questions/169219/invalid-geojson-when-inserting-data-to-a-cartodb-postgis-table-from-leaflet
*/
var cartodb_ids = [];
var geojsons = [];
//console.log(action + " persistOnCartoDB");
switch (action) {
case "UPDATE":
//console.log(layers.getLayers().length);
if (layers.getLayers().length < 1) return;
layers.eachLayer(function(layer) {
cartodb_ids.push(layer.cartodb_id);
geojsons.push("'" + JSON.stringify(layer.toGeoJSON().geometry) + "'");
});
break;
case "INSERT":
cartodb_ids.push(-1);
//console.log("here is the geojsons");
//console.log(geojsons);
//console.log("'" + JSON.stringify(layers.toGeoJSON().geometry) + "'");
geojsons.push("'" + JSON.stringify(layers.toGeoJSON().geometry) + "'");
break;
case "DELETE":
layers.eachLayer(function(layer) {
cartodb_ids.push(layer.cartodb_id);
geojsons.push("''");
});
break;
}
//constructs the SQL statement
var sql = "SELECT nyc_sanitation_example_upsert_areas_of_interest_newyork_nycsanitation(ARRAY[";
sql += cartodb_ids.join(",");
sql += "],ARRAY[";
sql += geojsons.join(",");
sql += "]);";
console.log("persisting... " + sql);//optional/debugging
//POST the SQL up to CARTO
$.ajax({
type: 'POST',
url: 'https://athompson.carto.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 " + responseData);
}
});
}
//bind drawing events to fire the persistOnCartoDB() function
map.on('draw:created', function (e) {
//console.log("draw:created fired");
var layers = e.layer;//was e.layers not layer
drawnItems.addLayer(layers);
//console.log(e);
persistOnCartoDB("INSERT", layers);
//console.log("draw:created:insert persistOnCartoDB fired");
});
map.on('draw:edited', function (e) {
//console.log("draw:edited fired");
var layers = e.layers;
persistOnCartoDB("UPDATE", layers);
//console.log("draw:edited:update persistOnCartoDB fired");
});
map.on('draw:deleted', function (e) {
//console.log("draw:deleted fired");
var layers = e.layers;
persistOnCartoDB("DELETE", layers);
//console.log("draw:deleted:delete persistOnCartoDB fired");
});
</script>
</body>
</html>
Raw
security-definer-function.sql
DROP FUNCTION IF EXISTS nyc_sanitation_example_upsert_areas_of_interest_newyork_nycsanitation(int[], text[]);
-- Returns a set of op,cartodb_id values where op means:
--
-- deleted: -1
-- updated: 0
-- inserted: 1
--
CREATE OR REPLACE FUNCTION nyc_sanitation_example_upsert_areas_of_interest_newyork_nycsanitation(
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 areas_of_interest_newyork_nycsanitation 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 areas_of_interest_newyork_nycsanitation 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 areas_of_interest_newyork_nycsanitation (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 nyc_sanitation_example_upsert_areas_of_interest_newyork_nycsanitation(integer[],text[]) TO publicuser;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment