JavaScript, PHP, Leaflet Plugins, and the CARTO SQL API

Prerequisite: Make a Basic Web Map, JavaScript: A Mapper's Introduction, and An Intro to Databases and PostGIS: Leaflet and CARTO

Download the Workshop Materials. This is Session 7.

In this advanced tutorial, we will take a look at how we can use a spatial database, such as PostGIS, and collect and write new data to the database using the map at the interface. To illustrate how to do this, we will walk the steps used to code a Leaflet map, populate it with data from CARTO (A popular web mapping application that utilizes PostGIS) based on an easy query, and then use a popular Leaflet Plugin called Leaflet Draw that allows users to draw and add data to your map. The tool we will create will be a map that will have the ability to get data from users and could serve as an easy way to crowdsource data collection. Here is the tool, try adding a point!

Data Collection Application (Click to view this example on its own.)

To use the CARTO SQL API to get data, it requires your username and a public table. For security reasons, THE TABLES SHOULD NOT BE USED TO HOLD SENSITIVE DATA.

CARTO does not allow writing to your table without the API Key. With the API key, you can write, delete, or modify data in our tables, which is exciting, but we don't want this visible to everyone. Using PHP provides a basic level of security to hide this key from the client. The goal of this tutorial is to introduce writing to a database using SQL in a manner in which you can crowdsource data and begin to explore the importance of security and one of the methods you can use to begin implementing it in your maps.

Our Toolbelt (aka Our Technology Stack)

For starters, I should point out that this tutorial is more advanced, and utilizes a handful of tools and coding methods. Some of these tools are new, some of which we have covered in previous tutorials. In our toolbelt today are the following items. I will expand more on them as we go.

  • Atom: Free and open-source text editor designed by Github for coding, markup, and prose.
  • LeafletJS: Free-and-Open-Source JavaScript-based web mapping library.
  • Leaflet.draw: A popular plugin for Leaflet that allows for map users to add data to a map.
  • CARTO: A cloud-based mapping and analysis engine that allows for data storage and runs on PostGIS.
  • jQuery: JavaScript library that aims to make things like event handling and AJAX simpler.
  • jQueryUI: A collection of User Interface widgets built on top of jQuery.
  • PHP: A web scripting language that is interpreted and executed on the server and can be used to increase security.
  • XAMPP Localhost Server: A software that allows you to run a localhost that supports PHP on your machine.

Sounds like a lot? Don't worry, we will step through this!

To start, Download the Workshop Materials . The organization of this workshop will be as follows.

  • PHP: What is it and why do we use it?
  • Create a Dataset in CARTO
  • Set up our HTML document, JavaScript libraries, and map
  • Adding data from CARTO using the SQL API
  • Implement the Leaflet.draw plugin to collect data
  • Send collected data to CARTO using a PHP Proxy
  • View newly collected data on the map

Let's get going!

1. PHP: What is it and why will we use it?

PHP is server-side scripting that is interpreted and executed on the server, not your client machine. The PHP code never actually goes to your browser, but the output from it, which is often times HTML, can. It can be used on the server to communicate with databases and other back-end technology as well as create HTML that is then sent to the client and interpreted by your browser. For example, the way we are going to use it is to send data to CARTO to be stored in a PostGIS database.

In order to receive data from a client, CARTO requires proper credentials, such as an API key, username, or password. This is logical, you don't want just anyone coming in and messing with your data. That means these are items you do not want to send to the client. Sites like Wordpress and Drupal are built and secured using PHP along with HTML, CSS, and JavaScript to help make them dynamic and secure. Our setup today is going to be quite a bit more simple than Wordpress and Drupal, but PHP fundamentals are still there.

The idea, distilled to graphical form.

A simple workflow and infrastructure
Simple Workflow and Infrastructure

Set up your Development Environment

Check if you have PHP

As mentioned, many Content Management Systems use PHP in conjunction with HTML, CSS, and JavaScript, so if you are using Wordpress or Drupal, PHP is included in the installation and will be on your server. To see if you have PHP installed on your hosting space, navigate to your server directory in command line (Terminal or Command Prompt), and type the following. This will try to check the version of PHP you have installed. If installed, it will return the PHP version on your machine, if not, it will return an error. If it returns an error, you need to install PHP in your hosting space.

php -v

Some additional comments: If WordPress is installed and running on your server space, you also have PHP installed and working in that location. If you are on WebFaction or a personal hosting site, such as GoDaddy or Bluehost, it is usually easy to install PHP (or it is already done). Search the site documentation of your hosting provider to install PHP. On WebFaction, create a new website and application configured as Static/CGI/PHP-5.5. But what about our local development environment, localhost?

Install XAMPP: Localhost Software

Our local development environment now needs a localhost server that supports PHP applications. The Python SimpleHTTPServer only supports PHP after some adjustments and is not optimal for this situation, but there are other localhost development environments we can use. Perhaps the best alternative is to download and install a server environment software that creates a localhost server on your machine that mimics a live web server. The options for this include XAMPP (Windows, Mac, Linux), WAMP (Windows), and MAMP (Mac). Of these, I will recommend XAMPP.

Download XAMPP

XAMPP has versions that run on both Windows and Mac, install the proper version for your machine.

On Windows: Once XAMPP is installed, open it up and view the Control Panel. It will look like the following. Start up an Apache Service on your machine by clicking on the Start option of the Apache service. This will create a local web server that runs Apache server software. Then click Explorer to see the localhost file server structure.

In the XAMPP Explorer, navigate to htdocs, then upload the unzipped downloaded materials to a new folder named data-collection in htdocs. Your file path to the downloaded index.html should be C:\xampp\htdocs\index.html. Note that C:\xammp might be different on your machine if you installed XAMPP in a different location.

A simple workflow and infrastructure
XAMPP Control Panel

On Mac: 1. Open XAMPP, click on the "Manage Servers" tab. 2. Start (or verify that it is already started) an Apache Web Server 3. In finder, go to the XAMPP folder in Applications and in that, open the htdocs folder 4. Upload the unzipped project materials into a new folder named data-collection in htdocs 5. Open index.html in a web browser as described for the windows version.

We've reached our starting point...

In a browser, with XAMPP running in the background, navigate to http://localhost/data-collection/index.html. You should see the following blank map!

2. Create a Dataset in CARTO to hold collected data

We have a development environment that will work with PHP running. Now it is time to start creating our data collection app. Navigate to CARTO and log in to your account. From the CARTO dashboard, create a New Dataset and upload the data_collector.csv file found in the data folder in the downloaded materials.

In practice, what we are doing is creating a new PostGIS database table, which we can then reference using the CARTO SQL API to load into our Leaflet map. The schema (the columns and their associated data types) are the important component to remember and preserve, as we refer to them in our SQL statements. We won't touch this table much from here on, but you will see it populate with data as we add it to our Leaflet map.

Your dataset in CARTO should look like the following. Note that I have added some dummy data.

CARTO "data_collector" Table

3. Set up our HTML document, JavaScript libraries, and map

Using your Text Editor (Atom), open up index.html. Time to start coding. I have included the framework and JavaScript libraries in the file already, lets take a look.


Included in the head section of the document you'll find the following stylesheets. They are the stylesheets for the JavaScript libraries we are using. Do not enter these, they are already in your HTML document.

    <link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet-0.7.3/leaflet.css" /> <!-- Leaflet CSS -->
    <link rel="stylesheet" href="http://leaflet.github.io/Leaflet.draw/leaflet.draw.css" /> <!-- Leaflet Draw CSS -->
    <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css"> <!-- jQuery UI CSS -->
    <link rel="stylesheet" type="text/css" href="css/style.css"> <!-- Our own custom CSS document -->

JavaScript Link Relations

Included in the body section of the document you'll find the following links to JavaScript libraries. Do not enter these, they are already in your HTML document.

    <script src="http://cdn.leafletjs.com/leaflet-0.7.3/leaflet.js"></script> <!-- Leaflet v0.7.3 JS -->
    <script src="http://leaflet.github.io/Leaflet.draw/leaflet.draw.js"></script> <!-- Leaflet Draw JS -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script> <!-- jQuery v1.11.2 JS -->
    <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script> <!-- jQueryUI v1.11.4 JS -->

Our custom code will follow these lines between the script tags.

The body

I have populated the document with a simple structure of div elements and inputs, and you will notice a large commented out block of code. This holds a dialog box that will popup to request input from map visitors.

Add Data from CARTO to the Map

Let's add the data_collection table from our CARTO account to the map using the SQL API. This is the same method we used in the An Intro to Databases and PostGIS: Leaflet and CARTO session. Enter the following block of code at the bottom of your script, where you find the // Add data from CARTO using the SQL API comment.

// Add Data from CARTO using the SQL API
// Declare Variables
// Create Global Variable to hold CARTO points
var cartoDBPoints = null;

// Set your CARTO Username
var cartoDBusername = {your cartoDB username};

// Write SQL Selection Query to be Used on CARTO Table
// Name of table is 'data_collector'
var sqlQuery = "SELECT * FROM data_collector";

// Get CARTO selection as GeoJSON and Add to Map
function getGeoJSON(){
  $.getJSON("https://"+cartoDBusername+".cartodb.com/api/v2/sql?format=GeoJSON&q="+sqlQuery, function(data) {
    cartoDBPoints = L.geoJson(data,{
      pointToLayer: function(feature,latlng){
        var marker = L.marker(latlng);

' + feature.properties.description + '
Submitted by ' + feature.properties.name + '

'); return marker; } }).addTo(map); }); }; // Run showAll function automatically when document loads $( document ).ready(function() { getGeoJSON(); });

Walking through this code, it first creates an empty global variable for the data points (cartoDBPoints), sets a SQL query (sqlQuery), creates a function that when called runs jQuery's getJSON function to request a GeoJSON from an external URL (the CARTO SQL API), seting the global variable for our data points to be the found dataset, creating a popup for each marker, then adding to the map, then finally executes the getJSON when the document is loaded ( $(document).ready() ).

Save and refresh your document and browser. Your map will have the dataset from CARTO loaded to your map!

4. Implement the Leaflet.draw plugin to collect data

Next, we want to let the map visitors draw on the map! We can do this using the Leaflet.draw plugin. Check out the Leaflet.draw plugin demo to see what it can do!

Leaflet Plugins

One of the major draws to Leaflet is the number of plugins available to users. Because it is an open-source project, any community member can write up and create additional functionality. Check out the full list of plugins here.

Some highly recommended plug-ins in addition to Leaflet.draw include:

Install Leaflet plugins by one of two ways, downloaded the files to your server or by linking to their location online.

i. Add a Draw Control to our Map

The Leaflet.draw plugin is already added to our document, as we saw above. With the draw plugin added, we can access all of the functionality. Our goal: to click on the start editing button, have a draw control appear that allows us to add points. The first thing we need to do is create a L.Control object called draw control.

Create three variables

To add the draw control toolbar to the map, we must do it manually. Create a new draw control, set the options for which drawing tools are available, and create a variable that we can use to control the visibility. Finally, create a new empty Feature Group that will hold drawn items after they are created on the map. Enter the following into your script.

// Create Leaflet Draw Control for the draw tools and toolbox
var drawControl = new L.Control.Draw({
  draw : {
    polygon : false,
    polyline : false,
    rectangle : false,
    circle : false
  edit : false,
  remove: false

// Boolean global variable used to control visiblity
var controlOnMap = false;

// Create variable for Leaflet.draw features
var drawnItems = new L.FeatureGroup();

ii. Activate our Start Editing and Stop Editing Buttons

We have two inactive buttons on our map, one that will start editing (show the draw control) and one that we can use to stop editing (hide the draw control). In our document, the buttons are composed of the following code. Do not enter these, they are already in your HTML document.

<input type="button" onclick="startEdits()" value="Click to Start Editing">
<input type="button" onclick="stopEdits()" value="Stop Your Editing Session">

These buttons use the HTML/JavaScript onclick event, which on a click of the button, will execute a designated JavaScript function. The "Click to Start Editing" button should run a function that adds the draw control to the map (we will call it startEdits()) and the "Stop your Editing Session" should run a function that removes the draw control, (we will call it stopEdits()). Define those two functions. Enter the following two functions into your script after the creation of the Leaflet draw control.

// Function to add the draw control to the map to start editing
function startEdits(){
  if(controlOnMap == true){
    controlOnMap = false;
  controlOnMap = true;

// Function to remove the draw control from the map
function stopEdits(){
  controlOnMap = false;

The startEdits() function checks to see if the value of controlOnMap is true, if it is, it removes the control from the map, and set the value of controlOnMap to false. If the initial value is not true, it will add the control to the map and set the value of controlOnMap to true.

The stopEdits() function removes the control from the map and sets controlOnMap to false.

iii. Define what happens when a feature is created

Next, make the draw tool add a point to the map by using the draw:created event. This event will execute a function after a feature is creatd on your map. Enter the following.

// Function to run when feature is drawn on map
map.on('draw:created', function (e) {
  var layer = e.layer;

When a feature is created on the map, a layer on which it sits is also created. This function listens for when a feature is created, then takes that event, sets the layer of the event to a variable called layer, then it adds that layer to the drawnItems feature group, then adds drawnItems to the map.

The last line of code opens a page element with an id of dialog. The goal here is to create a dialog that will allow the user to enter information about a feature after he/she adds it to the map. Let's work on this piece next and create a dialog that can open. Before starting this next part though, save and refresh your map. You should be able to add points!

iv. Create a dialog box to collect attributes

We want a dialog box to popup when a feature has been added that will collect information from the user, this could be attributes such as name and description. To do this, it is a two step process. We have to write HTML elements that will compose our dialog box, and then we have to write JavaScript to define the content of these elements of the dialog box. To implement this dialog box, we are going to use jQuery UI. jQuery UI is a popular collection of widgets and user interface components created on top of jQuery. Read more about jQuery UI here.

jQuery has a nice dialog object that is described here that will work for our method of collecting information from the user.

To create the dialog, we use the Dialog object and set the options. Enter the following code, and we will walk through it.

// Use the jQuery UI dialog to create a dialog and set options
var dialog = $("#dialog").dialog({
  autoOpen: false,
  height: 300,
  width: 350,
  modal: true,
  position: {
    my: "center center",
    at: "center center",
    of: "#map"
  buttons: {
    "Add to Database": setData,
    Cancel: function() {
  close: function() {
    form[ 0 ].reset();
    console.log("Dialog closed");

// Stops default form submission and ensures that setData or the cancel function run
var form = dialog.find("form").on("submit", function(event) {

This code describes the dialog object we want to use to collect input from the user. Many of the options are set here, including size, height, and position. Specifically, note the buttons. They are defined in the options and followed by calling functions that will run they are clicked. There are two buttons, "Add to Database" and "Close". When "Add to Database" runs, it will invoke a function named setData(). This doesn't exist yet, we need to write it and tell the browser what to do when this button is clicked. We will do this soon. The final piece creates the form object that is used in the jQuery UI dialog, but prevents default submission.

Next, look into the HTML elements and locate the div with the id="dialog". It is currently commented out. Remove the comments. This is a block of HTML code and tags that define our input. Note the input fields, the first input sets a value for our name and the second sets a value for for description. Here is block of code. This is already in your file, beneath the code for our map. Uncomment it! (Remove the comment tags)

<div id="dialog" title="Point Information">
      <label for="username">Your Name</label>
      <input type="text" name="username" id="username" placeholder="Enter your Name" class="text ui-widget-content ui-corner-all">
      <label for="description">About this Point</label>
      <input type="text" name="description" id="description" placeholder="Description for this point" class="text ui-widget-content ui-corner-all">
      <input type="submit" tabindex="-1" style="position:absolute; top:-1000px">

iv. Write the setData() Function

Here is the heart of our data submission, a function that sets our entered input information to JavaScript variables, organizes them into a SQL query readable by PostGIS, and calls a function to submit it to our database via a PHP proxy. A proxy, in the manner we are using it, is a term that defines another file performing an action in place of our current file. We don't want the JavaScript going straight to the server, so we send it to a middleman, our proxy, which has information needed to complete the delivery to the server.

Here comes SQL!

In SetData(), we take the properties of the form we just submitted, set them to variables, write up a SQL query that will insert a row into our database, defining what we want each field to populated with, and send it to the proxy to be forwarded on into our cloud-based CARTO database. Enter the following code into your script.

function setData() {
    var enteredUsername = username.value;
    var enteredDescription = description.value;
    drawnItems.eachLayer(function (layer) {
        var sql = "INSERT INTO data_collector (the_geom, description, name, latitude, longitude) VALUES (ST_SetSRID(ST_GeomFromGeoJSON('";
        var a = layer.getLatLng();
        var sql2 ='{"type":"Point","coordinates":[' + a.lng + "," + a.lat + "]}'),4326),'" + enteredDescription + "','" + enteredUsername + "','" + a.lat + "','" + a.lng +"')";
        var pURL = sql+sql2;
        console.log("Feature has been submitted to the Proxy");
    drawnItems = new L.FeatureGroup();
    console.log("drawnItems has been cleared");

What on earth did we do here? First we created the setData function. Then we create variables (enteredUsername and enteredDescription) that hold the entered values, which are now properties, from the data form the user just completed. Next, we look at the individual layers in drawnItems (there should really never be more than one), and on each layer (feature), write a SQL INSERT statement that will insert a row into our data_collection database.

The query should look like the following:

INSERT INTO data_collector (the_geom, description, name, latitude, longitude) VALUES
(ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[ {longitude value},{latitude value} ]}'),4326),
'{description value}','{name value}','{longitude value}','{latitude value}')";

Note the usage of variables to place the proper numbers and proper values in the write location in the query. Next, we set the query equal to a variable (pURL) that we will pass to our PHP proxy.

Once the variable is set, we call a function named submitToProxy(pURL). We have not yet written this function and we need to set it up. This is a function that will pass our query to the server, where it will be entered into our PHP script. We will work on this next. The last lines clean up and clear the objects, and close the dialog.

5. Send collected data to CARTO through a PHP Proxy

In the following step, we need to set up how to send the INSERT query to the database. We are using a PHP file on our server to hold our credentials, so we need to set up where this is on the server and then write the code that will send the INSERT query to the server, then to the CARTO database. In the above diagram, here is what we are working on. I would like to provide special credit to Nick Martinelli for inspiring the method.

A simple workflow and infrastructure
Simple Workflow and Infrastructure

i. Create PHP files

The first thing we need to do is create our PHP files. We are going to use two files that contain scripts that will receive a HTTP Post from our index.html and add the API key so we can add data to the CARTO database. The first file (callProxy.php) we put in our server directory, the second file (cartoDBProxy.php), can be put somewhere else on our server. For this exercise, we will put it on the root of our server directory in a folder named php. This is an beginner security setup that is expandable, but for now will hardcode the API key into the PHP.

The files are proxy files that work as a middleman between our index.html and the database. The PHP files do not get sent to the client, meaning we can put information such as usernames, passwords, and keys in them and they won't be downloaded to every browser that accesses the map. We use two files, one to call the proxy that shows the path to the client, and the other the actual proxy, whose location is called within the call script and is hidden on our server.


PHP, as mentioned above, is a server-side scripting language the provides and receives files and data from the client and server and responds with output information that can either push information on to another location (ie a database) or send it back to your browser (as HTML). Learning PHP on the whole is outside of the scope of this tutorial, but I recommend some of the following tutorials to help you get your feet wet and into what the scripting language is and can do.

These PHP files today are in your downloaded documents in the php folder. The content has been populated for you. Here is what those files look like.

    include 'path\to\cartoDBProxy.php';
    //          ^CHANGE THIS TO THE PATH TO YOUR cartodbProxy.php
    $queryURL = $_POST['qurl'];
    $return = goProxy($queryURL);
    echo $return;

This file calls the proxy, sets the query we pass to it equal to a variable, then runs a function named goProxy using the query as an argument.

    $cache_limiter = session_cache_limiter();
    function goProxy($dataURL)
        $baseURL = 'http://{your CARTO username}.cartodb.com/api/v2/sql?';
        //                  ^ CHANGE THE 'CARTODB-USER-NAME' to your cartoDB url!
        $api = '&api_key={your CARTO API key}';
        //             ^ENTER YOUR API KEY HERE!
        $url = $baseURL.'q='.urlencode($dataURL).$api;
        $result = file_get_contents ($url);
        return $result;

This file sets the goProxy function and holds our credentials, hidden two steps away from our HTML document.

We need to modify the contents of these files to contain our CARTO username, CARTO API Key, and the path to the location of cartoDBProxy.php on our server. Change these now. If you don't know the location of your cartoDBProxy.php yet, come back and change this later.

Find your CARTO API keys in your CARTO Profile online on the CARTO website. Click on your account profile and on Your API Keys.

Simple Workflow and Infrastructure

ii. Place the PHP files on your web server

Next, move the cartoDBProxy.php file to another location in your server directory. This can be anywhere above the web root. This adds a secondary layer of security. Use the following diagram to help locate the PHP scripts on your server.

Simple Workflow and Infrastructure

Once your files are located, change the path in callProxy.php so that it will locate cartoDBProxy.php when it runs!

This concludes our PHP work and will hide our API key from the client, allowing it to be used to send data to our database.

iv. Write Function that posts Input data to the Proxy files

Going back to our map and the index.html. We need to create a function that will now send the data we collected and entered to the two PHP proxy files we just created. The setData() function, written above, has two key lines. One that sets our written INSERT query to a variable named pURL, and the the next that sends to a function called submitToProxy(pURL).

We need to write the submit to proxy function and use it to 'post' our data to the server, where it can be input into the PHP and ultimately the database. POST is an HTTP AJAX call that is works to send data and information to a server, much like GET gets data from a server. jQuery has a nice post method we will use called $.post(), read more about it here. The request will be formatted like the following: $.post( url [, data ] [, success ] [, dataType ] ). Data represents the query, and success is a function that will run if the POST request is successful.

Enter the following two functions into your script.

    // Submit data to the PHP using a jQuery Post method
    var submitToProxy = function(q){
      $.post("php/callProxy.php", { // <--- Enter the path to your callProxy.php file here
        cache: false,
        timeStamp: new Date().getTime()
      }, function(data) {

    // refresh the layers to show the updated dataset
    function refreshLayer() {
      if (map.hasLayer(cartoDBPoints)) {

So what does this do. SubmitToProxy hold a function that sends the query to the PHP files, refreshLayer() runs only if the POST is successful. If the post is successful, it refreshes our map by getting a new copy of the data from the server as a GeoJSON.

v. Test your Map

Save and refresh. Open up your map in the browser, and give it a shot! Try adding a point to your map.

Data Collection Application (Click to view this example on its own.)

To see how it is working on the database end, go to CARTO and look over your data_collector table. You should see your added points now sitting in your dataset!

Data Collection Table with New Data

6. Next Steps and Expandability

This exercise is intended to demonstrate the power of using a mapping library, like Leaflet, a database, such as PostgreSQL (manifested through CARTO), and JavaScript, HTML, and PHP as a method for not only storing and displaying data, but also as a method for collecting and cataloging data in an efficient manner using technology available in an open source and approachable stack.

Some suggested next steps

The exercise is intended to be expandable, and illustrate the components in creating an interactive mapping experience. More is obviously needed to make a map ready for primetime.

Add user registration and login

The method shown above displays a way you can set up an open app that can receive data from anyone who attempts to put a point on the map. This is great for crowdsourcing data collection and public and open datasets, but what if you want something a bit more private? With PHP, you can set up user registrations, user logins, and password protected applications and sites. I suggest starting with some of the basic tutorials, then once completed, seeing how you can implement user registration on your map. Some PHP login and security tutorials:

Collect lines and polygons in addition to points

Consider how you can use this method to collect other features in addition to points, such as lines and polygons. Leaflet.draw supports multiple data storage types, and you should be able to modify and expand the code above to include these data types as well. Hint: you will need to create some new datasets!

Edit Points and Update your Database

Leaflet.draw also supports editing points, which you could then push back to your database using a SQL UPDATE statement. Consider how you could implement this.

Expand to mobile

The Leaflet.draw tool has limited mobile capabilities. Consider methods you could use to collect data, perhaps outside the map, then push them to your CARTO dataset and update your map.


Nick Martinelli and his Portland neighborhood mapping project and repo.

Andrew Hill from CARTO and his always excellent work.


This is the last of the regularly scheduled #webmapworkshop sessions. Supplemental sessions will follow, and the next session will be Postgres database input and collection on your own server, not through CARTO. Look for more in the future!

Happy Mapping!

Return to DUSPVIZ tutorials page