webMapWorkshop-logo-01

An Introduction to Databases: PostGIS and CARTO

Prerequisite: Make a Basic Web Map and JavaScript Introduction

Web applications and interactive online maps are all about the data. Data being displayed, data being collected, and data being stored - on the front end and behind the scenes in the back end. Visualizing this data can affect policy, influence decision makers, and inform the general public. Mapping applications must be able to quickly distill and process large datasets, and provide the ability to load and function without hindering the user experience.

During this series of workshops, we have introduced web-based mapping with Leaflet, focusing on data stored in GeoJSON format on our server. GeoJSON is important when using JavaScript based mapping libraries because it encodes data directly into a JavaScript object, but using only GeoJSON has limitations. Perhaps most impeding is the size limit of a GeoJSON object. Storing large datasets becomes quickly prohibitive, as all points and feature vertices are loaded into your browser when your page loads. This necessitates some alternatives and displays the need for easy ways to filter data that make it easier to provide data to the map. Some of these alternatives include, but are not limited to: web map services, tile services, and filtering our GeoJSON datasets through usage of an online database.

The session today introduces PostGIS, a spatial extension for the popular open-source PostgreSQL database management system. To introduce PostGIS, we will use CARTO, which provides the experience of a geospatial database in the cloud, to store a dataset and run queries on that dataset. CARTO provides a powerful user interface to a PostGIS/PostgreSQL back end, and has a useful SQL API we can use to run queries and retrieve data we have stored on the CARTO cloud. The session today will load data from CARTO into a Leaflet map and run through some sample PostGIS SQL queries to introduce you to the technology.

Workshop Structure

To get started, set up an account on CARTO!

https://carto.com/signup/

The exercise today is in three main parts:

  • A Brief Intro to Spatial Databases
  • Adding Data from CARTO using the SQL API
  • Running Geospatial Queries to filter data

Download the Workshop Materials. This is Session 6.


Start your Localhost Server
Fire up a web page for your map on your local machine to prepare to make some changes. To do this, serve your page on a localhost server on your machine. Open a command line (Command Prompt (Windows) or Terminal (Mac)), use cd to change the directory to the one where you placed the materials package you downloaded.

Once in that location start a basic Python SimpleHTTPServer by entering python -m SimpleHTTPServer and pressing return. This will work for today.

Then open a brower tab to http://localhost:8000/example-1.html to view the first example.

For more instructions the Python SimpleHTTPServer, read the tutorial on localhost servers here.

What we'll make today!

Click Map to Find Coffee Shops Closest to Me (Click to view this example on its own.)

1. A Brief Intro to Spatial Databases

The term database describes an organized collection of data. Organized collections of data allow for easy indexing, searching, and querying and enable retrieving, modifying, and adding data to a database. Many popular modern databases follow a relational model, where each record in the database has a unique ID that can be used to relate that record to others in the database. Online databases sit on a server, just like your webpage. You install a database on your server, then use an administrative console that comes with the database to access and add data to the database.

A sample table from a relational database.
An example table found in a relational database.

A spatial database is a database that is optimized to store and query data that represents objects defined in a geometric space (Wikipedia). Spatial databases allow for queries that are based on geometry, which means you can retrieve data based on location and geography. These are called 'spatial queries'. For example, say you have a point dataset of all fire hydrants in Cambridge, and you have a polygon dataset of Cambridge neighborhoods. If both of these are in your spatial database, you could request to see only fire hydrants that are in a specific neighborhood, and all records in the database that satisfy this request will be returned to you. These database requests are written in a language called Structured Query Language, or SQL.

Some Common Spatial Databases

The lesson today uses a PostGIS and PostgreSQL database that is on the CARTO cloud. Any data you upload to CARTO sits in a PostGIS database!


i. What is SQL?

SQL is a standard and specific programming language for requesting and modifying data from a database. It can perform selections, joins, insertions, and updates to database records and tables. You can use SQL to request data from database administrative consoles, or through an API that connects to a database. The most basic SQL statement is a SELECT statement. A SELECT statement requests data from a database based on that data meeting certain criteria. An example SELECT statement follows:

SELECT *
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;

Here, the statement requests all columns (SELECT * (the asterisk means all)) from a table named Book (FROM Book) that have a price greater than 100.00 (WHERE price > 100.00) and then orders them alphabetically by title (ORDER BY title). price and title are columns in the dataset. The syntax of a query will vary somewhat from platform to platform, but the concepts are largely the same across the board.

SQL, as you can imagine, is a large topic to cover. To learn more on your own initiative, check out some of the following online tutorials.


ii. What is PostGIS?

PostGIS is a popular extension for PostgreSQL databases that allow for spatial queries to be performed on relational data within a PostgreSQL database. PostGIS has a special characters and commands that can be used in a SQL statement to relate the query to geometry. It was created in 2005 by Paul Ramsey, an influential figure in the open source geospatial world. Mr. Ramsey is now employed by CARTO.

Read the PostGIS documentation for more information and questions on capabilities and syntax. Google searching what you want to query is often a great first step in finding the right syntax!


iv. What is CARTO?

You can install PostGIS on any PostgreSQL database, including those you might have running on your own servers. Running a database server requires some advanced setup and maintenance however, that is outside the scope of this exercise. CARTO uses PostGIS as a backend and has an excellent SQL API that allows users to utilize SQL queries to extract data from datasets they have uploaded. Essentially, it is a PostGIS database in the cloud that you can upload data to and then run queries to return it to your applications. This makes learning some of the basics easier.

To begin running SQL on data in CARTO, all you have to do is upload a dataset, then you can access it using the SQL API. Let's add some data and run some queries!


2. Adding Data from CARTO Using the SQL API

Use example-1.html in the materials for this section of the exercise

Adding data to a Leaflet map from CARTO is easy, and there are a couple methods available to do it. The method we will look at today is using the CARTO SQL API. The CARTO SQL API is a programming interface that allows you to embed database queries written in SQL into a URL that can be used in an HTTP call (AJAX!). Start by setting up a free account on CARTO and upload some data!

1. Upload data to CARTO.

Provided in the download materials is the coffee shop dataset (coffee_cafes.geojson) that we have used in previous weeks. In your CARTO account create a new table. Upload coffee_cafes.geojson to your CARTO account, naming the table coffee_cafes. Once the data is uploaded, that is all we need to do. We can map this using CARTO tools, or load it into another map. On the back end, this uploaded dataset is a table in a PostGIS database that you can access using your CARTO username.

2. Our Leaflet Map

Provided to you is a simple example map with the Leaflet and jQuery libraries loaded. Open up example-1.html from the materials. You will see a familiar boilerplate, with our loaded libraries, some HTML elements, styling from a stylesheet named style.css, a map object instantiated and a base layer loaded.

The JavaScript in our document at this point is very simple. Looks like the following.

<script>
    // Create Leaflet map object
    var map = L.map('map',{ center: [42.381899, -71.122499], zoom: 13});

    // Add Tile Layer basemap
    L.tileLayer('http://tiles.mapc.org/basemap/{z}/{x}/{y}.png', {
      attribution: 'Tiles by MAPC, Data by MassGIS',
      maxZoom: 17,
      minZoom: 9
    }).addTo(map);

    // Global Variables
    // Will go here


    // Database Queries
    // Will go here

</script>

This script creates a map object and loads a tile layer. There are some comments in place to help us structure where we will write the rest of our script.

a. Set up a Global Variable for Coffee Shops

In the section of the script with the comment stating Global Variables, lets create an empty null variable called coffeeShopLocations that we can use to hold data when we add it. Enter the following line into your script under Global Variables will go here.

// Global Variables
// Will go here
var coffeeShopLocations = null;

b. Create Variables that hold SQL Database Queries

In our file, you will notice two radio buttons, one says Show All Coffee Shops and the other says Starbucks Only. Create a couple of variables that will hold the queries that will retrieve the data according to the criteria, one for each. The queries look like the following.

Select all coffee shops from dataset:
SELECT * FROM coffee_cafes

Select all coffee shops that are named Starbucks:
SELECT * FROM coffee_cafes WHERE name='Starbucks'

Note that they are simple SELECT statements, retrieving data from the table named coffee_cafes.

Enter the following into your script.

// Database Queries
// Will go here
// Get all coffee cafes from dataset
var sqlQuery = "SELECT * FROM coffee_cafes";
// Get all coffee cafes that have name 'Starbucks'
var sqlQueryStarbucks = "SELECT * FROM coffee_cafes WHERE name='Starbucks'";

These two variables will be used to retrieve data from our database in our HTTP request to load a GeoJSON.

c. Add the GeoJSON data to the map

Next, we can add the data to the map. Use the jQuery getJSON method to make a call to an outside URL, and then enter a URL to request data from CARTO. In this URL, something interesting happens.


The CARTO SQL API

The CARTO SQL API allows for users to write SQL statements to perform PostGIS operations on your uploaded CARTO data. When you create a CARTO account, all tables you upload are in one large database. This is accessed through your account name. Interaction with the SQL API is allowed through URLs. To format an API request and return the query results, use the following URL format.

Important Note: To use the CARTO SQL API to get data, it requires need 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. To access private tables or a personal PostGIS database, you need additional security. We will address this in the next session, where we will illustrate a method that hides credentials from the client, for now, we want to illustrate the concept.

https://{Your CARTO Username}.carto.com/api/v2/sql?q={SQL statement}

To get your result returned as a GeoJSON, add format=GeoJSON to the URL.

https://{Your CARTO Username}.carto.com/api/v2/sql?format=GeoJSON&q={SQL statement}

This will return a GeoJSON text element to your browser that contains the result of your query. Because it is GeoJSON, it will be ready to ingest into Leaflet. For more on making calls to the SQL API, read here.


Set your Username and Request the Data from CARTO

To make things a bit easier, set your CARTO username as a variable, this way you only have to write it once.

// Set CARTO Username
var cartoDBUserName = "{Your CARTO Username}";

Next, write a function that will show all coffee shops. Use $.getJSON from jQuery to make the HTTP request that returns a GeoJSON request from CARTO. Enter the following block of code and we will walk through what it did.

// Function to add all coffee shops
function showAll(){
    if(map.hasLayer(coffeeShopLocations)){
        map.removeLayer(coffeeShopLocations);
    };
    // Get CARTO selection as GeoJSON and Add to Map
    $.getJSON("https://"+cartoDBUserName+".carto.com/api/v2/sql?format=GeoJSON&q="+sqlQuery, function(data) {
        coffeeShopLocations = L.geoJson(data,{
            onEachFeature: function (feature, layer) {
                layer.bindPopup('<p><b>' + feature.properties.name + '</b><br /><em>' + feature.properties.address + '</em></p>');
                layer.cartodb_id=feature.properties.cartodb_id;
            }
        }).addTo(map);
    });
};

Whoa, that was a lot. Let's break it down. First, it removes the layer if it exists, we don't want duplicates. Next, it uses $.getJSON to quest the data from another location on the internet (being CARTO). This other location has to return a JSON formatted document, which the CARTO SQL API provides. So we enter the URL that goes to the SQL API, including our username and the SQL query. Note how we used the cartoDBUserName variable to hold our name, and the sqlQuery variable to hold our SQL Query. Without these, the whole URL would look like the following.

https://{Your CARTO Username}.carto.com/api/v2/sql?format=GeoJSON&q=SELECT * FROM coffee_cafes

In the following lines, we write a function that will run on the successful return of a JSON object. This function will set coffeeShopLocations to be equal to a Leaflet GeoJSON object, then bind a popup to each feature that displays the name and address of each coffee shop. Finally, it sets an id for the feature that equals the cartodb_id from the loaded GeoJSON.

Run the showAll() function when the document is loaded

To run the function when the document loads, use the $( document ).ready method from jQuery. Anything within the brackets following this method will run as soon as the document is loaded. This loads the points to the map right after the page is loaded.

// Run showAll function automatically when document loads
$( document ).ready(function() {
  showAll();
});

Save your document and view your map. It should look something like the following. Our data from CARTO is loaded into our map!

CARTO Data is on the Map! (Click to view this example on its own.)

d. Write Function to Filter Starbucks

If we want to filter to show a specific coffee shop, we do that in a very similar manner. The following block of code uses the same strategy as above, but uses the second query we wrote in place of the first. It is saved as a variable named sqlQueryStarbucks. Enter the following block of code following the previous lines.

// Function to Filter Starbucks
function showStarbucks(){
  if(map.hasLayer(coffeeShopLocations)){
    map.removeLayer(coffeeShopLocations);
  };
  $.getJSON("https://"+cartoDBUserName+".carto.com/api/v2/sql?format=GeoJSON&q="+sqlQueryStarbucks, function(data) {
    coffeeShopLocations = L.geoJson(data,{
      onEachFeature: function (feature, layer) {
        layer.bindPopup('<p><b>' + feature.properties.name + '</b><br /><em>' + feature.properties.address + '</em></p>');
        layer.cartodb_id=feature.properties.cartodb_id;
      }
    }).addTo(map);
  });
};

Activate Buttons

To execute the filter, we need to call the function. The two radio buttons right now in our map do not do anything. We can use jQuery to run a function that executes when the radio button is clicked. The buttons in HTML look like the following. The value is the important property.

<input type="radio" name="filter" value="all" checked>Show All Coffee Shops</input>
<input type="radio" name="filter" value="starbucks">Starbucks Only</input>

When we sign into the map, we see all coffee shops, so using the checked option of the input, we can have the button be selected when we load the page. To make something happen when there is a click on one of the radio buttons, we need to use an event listener. JQuery has a number of event listener functions you can use, we are listen for a click on the input that has value='starbucks' and run the showStarbucks() filter when it is heard. Same goes with the radio toggle that has value='all', which will run showAll() on a click. Enter the following event listeners in to your script, beneath the showStarbucks function. These listen for a clicks that change the value property of the input.

// Event Listeners
$('input[value=starbucks]').click(function(){
  showStarbucks();
});

$('input[value=all]').click(function(){
  showAll();
});

Save and refresh your map. Check out the toggles!

e. Add data to CARTO, see it on your map

Because our map queries the CARTO database to display contents, if we want to add data to our map or change our dataset, we can simply add points to our CARTO dataset. Try this out. Go to CARTO, add a few lines of data (create a few new coffee shops!), and then go back and refresh your leaflet map. You should see them populate on your map. Using CARTO (and PostGIS databases) can be an excellent way to store your data and view it on your map in small bits by using SQL queries. We have scratched the surface of SQL queries. To learn more on your own initiative, check out some of the following online tutorials.

SQL

PostGIS SQL Reference

Save your document. You can close example-1.html. Use it as an example for your projects!

3. PostGIS Queries: Find all Coffee Shops Near Me

We are going to look further uses of PostGIS. Use example-2.html in the materials for this section of the exercise.

In the previous section we ran through adding data to Leaflet from a CARTO database. We used a SQL query to get this data from PostGIS. The query we ran however did not require any spatial knowledge, we simply selected all of the data from the dataset using a simple SELECT statement, then put together another query that filtered the data according to a property using another SELECT statement. The power of PostGIS is that you can use spatial queries, and request items according to their location in space. Let's demonstrate this by using the Leaflet functionality to find our location, then run a query that finds the five closest coffee shops.

Open example-2.html in your working materials. You will see a map that looks like the following, showing all of the coffee shops grabbed out of the CARTO database.

a. Open up File and Observe the Contents

With example-2.html open, view your file in your localhost server. You will see the following, with two inactive buttons, one that will find our location and the five nearest coffee shops, and the other that resets the map.

Note the script, it is very similar to what we just created in the first exercise. Using a SELECT query, we added a dataset stored in CARTO to our map, then set a popup on those data points. The buttons are not active though, and there is no PostGIS query in the script to find the five nearest coffee shops. In the script, the only difference from example-1.html is found in the showAll function, which follows.

// Get CARTO selection as GeoJSON and Add to Map
function showAll(){
    if(map.hasLayer(coffeeShopLocations)||map.hasLayer(locationMarker)){
        map.removeLayer(coffeeShopLocations);
        map.removeLayer(locationMarker);
    };
    map.setView(new L.LatLng(42.381899, -71.122499), 13);
    $.getJSON("https://"+cartoDBUserName+".carto.com/api/v2/sql?format=GeoJSON&q="+sqlQuery, function(data) {
        coffeeShopLocations = L.geoJson(data,{
            onEachFeature: function (feature, layer) {
            layer.bindPopup('

' + feature.properties.name + '
' + feature.properties.address + '

'); layer.cartodb_id=feature.properties.cartodb_id; } }).addTo(map); }); };

To get us started, I've included a small snip in the if statement that checks to see if there is a location marker on our map. If so, it removes it. We will create this location marker in the following steps, placing it at our physical location.

b. Set up Global Variables

To start, locate the line in our script that says Find five closest coffee shops. This is at the bottom of our script. All of our code will go in the script below this line. The first thing we want to do is declare a couple of global variables that will hold our data objects. We also want to add a marker that is added to the map at our location when it is found. Do this by setting up an icon object that will show a red icon at our location instead of the default blue. The red icon is included in your images folder in the downloaded materials. For more on setting up icons, check out the Web Map Workshop styling session. The code looks like the following, enter this starting at Find five closest coffee shops.

// Find five closest coffee shops

// Set Global Variable that will hold your location
var myLocation = null;

// Set Global Variable that will hold the marker that goes at our location when found
var locationMarker = null;

// Set 'Your Location' icon
var redIcon = L.icon({
    iconUrl: 'images/redIcon.png',
    shadowUrl: 'images/marker-shadow.png',
    iconAnchor: [13, 41]
});

Save your document. Nothing has visibly changed in our map yet, but we have set up a couple of variables to help us with finding our location.

c. Set up Location Finding Functions

Next, we need to set up a number of functions that will find our location, tell Leaflet what to do when our location is found or if it is not found, then run a query that uses our location and brings back the five nearest coffee shops from the CARTO database.

i. Locate the Location of the User

The first function we write finds the location of the user. It is a simple function that uses the locate method of the map object. The locate method fires a locationfound event when location is found, and a locationerror event if location is not found. Enter the following code.

// Function that will locate the user when called
function locateUser(){
  map.locate({setView: true, maxZoom: 15});
};

// Map Event Listener listening for when the user location is found
map.on('locationfound', locationFound);

// Map Event Listener listening for when the user location is not found
map.on('locationerror', locationNotFound);

These lines of code do a few notable things, one, on locate, it sets the view to the location found by setting the setView method to true. It then sets the zoom to 15 using the maxZoom method. Next, we set up two Leaflet event listeners that listen for an event on the map object. They listen for locationfound and locationerror on the map object, and accept a function that will run when either event is fired. Click here to read more about map events.

ii. Create functions that execute when location is found (or not)

Next we need to create two functions, one called locationFound() that will run when location is found, and the other called locationNotFound(), that will run in case location is not found when the computer or device attempts to find it. Those functions will look like the following.

// Function that will run when the location of the user is found
function locationFound(e){
    myLocation = e.latlng;
    closestCoffee(); 
    locationMarker = L.marker(e.latlng, {icon: redIcon});
    map.addLayer(locationMarker);    
};

// Function that will run if the location of the user is not found
function locationNotFound(e){
    alert(e.message);
};

These functions are fairly straightforward. Location found reads an event, signified by the e in the brackets, then uses arguments from that event in the function code. Here it takes the location found event, sets the latitude and longitude to equal to the myLocation variable, creates a marker at the latitude and longitude of the event, gives it the red icon, and sets the marker equal to the locationMarker variable. It then adds the location marker variable to the map as a layer, so we can see the red marker at our location, and tells the browser to run a function called closestCoffee().

We haven't written closestCoffee() yet! But this function will run a PostGIS query that calls back the five closest points to latitude and longitude of the event.

The second function, called locationNotFound(e), reads the same information from the event, but tells the browser what to do if the location is not found. In this case, we tell it to popup an alert message that will display the default error message from the event.

d. Use PostGIS to find the Closest Coffee Shops

Finally, we can write up a function that incorporates a PostGIS query to find the closest coffee shops to our found latitude and longitude. Call that function closestCoffee(), in order to match above.

i. Working with PostGIS

The first thing we want to do is determine how we write the PostGIS query we want to use to extract data from the database. As we just learned above, we want to use a SELECT statement, because we are 'selecting' data out of the database.

PostGIS Queries and Geometry

In CARTO, PostGIS geometry is held in a field called the_geom. This field contains an encoded geometric point, and is important in using spatial queries. The most straightforward way, as we saw above, to extract data from PostGIS is write a SQL query to do so. In the following example, taken from the PostGIS Documentation, it describes how you can write a SQL query and store the_geom as a text using a theoretical table named roads, and returning the line strings of each road. Do not enter this in your document, it is for illustration.

db=# SELECT road_id, ST_AsText(the_geom) AS geom, road_name FROM roads;

road_id | geom                                    | road_name
--------+-----------------------------------------+-----------
      1 | LINESTRING(191232 243118,191108 243242) | Jeff Rd
      2 | LINESTRING(189141 244158,189265 244817) | Geordie Rd
      3 | LINESTRING(192783 228138,192612 229814) | Paul St
      4 | LINESTRING(189412 252431,189631 259122) | Graeme Ave
      5 | LINESTRING(190131 224148,190871 228134) | Phil Tce
      6 | LINESTRING(198231 263418,198213 268322) | Dave Cres
      7 | LINESTRING(218421 284121,224123 241231) | Chris Way
(6 rows)

This is a series of lines, from one point to another, with the name. This encodes a number of SQL practices. The first, in the select line, we are selecting three columns (road_id, ST_AsText(the_geom) AS geom, road_name) from roads. The middle one is where we use the PostGIS geometry output AsText to return a text format of the_geom, and set the output to be called geom. Finally, it returns the name of the road.

The PostGIS reference contains the documentation for all of the functionality of the functions, constructors, editors, and operators of PostGIS. When you load geographic data into PostGIS (on your own or using CARTO), it will create the_geom field that you can use to run queries. Read more of the reference documentation and try some manipulations using CARTO.

Using PostGIS to find the points closest to another point

To accomplish our task of finding the five coffee shops closest to us, we will use an operator built into PostGIS that returns the 2D distance between points. Operators are part of the bread and butter of PostGIS, and can test geometric comparisons between features in the database. We have a latitude and longitude from the event location returned when our location was found, we can use this in PostGIS, but we have to make a point from it that PostGIS can read. To do this, use the Make Point geometry constructor to 'make a point' at the location.

PostGIS supports projections and coordinate systems (Spatial Reference Systems), in order to ensure we are working the proper projection, we have to set the SRID (Spatial Reference ID), of the point we just made. This is accomplished using ST_SetSRID

The spatial query we need to use to access our database and get the five nearest points is the following. Do not enter this in your document, it is for illustration.

SELECT * FROM coffee_cafes ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint({lng},{lat}), 4326) LIMIT 5

In this circumstance, we could hard code a latitude and longitude into the query, or we can use the latitude and longitude returned from our location found event. The spatial reference code is 4326, which is WGS84. This is based on the EPSG number, which can be here. EPSG (which actually stands for European Petroleum Survey Group) is a coordinate system parameter set commonly used in web mapping. A reference can be found here.


ii. Write up the closestCoffee() function

The function to find the closest coffee shops will incorporate this spatial query, remove any existin layers we don't want to duplicate, and use the CARTO SQL API to access our CARTO tables and provide us an object we can display on the map. Enter the following function in our script tags after the other functions.

// Function will find and load the five nearest coffee shops to a user location
function closestCoffee(){
  // Set SQL Query that will return five closest coffee shops
  var sqlQueryClosest = "SELECT * FROM coffee_cafes ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint("+myLocation.lng+","+myLocation.lat+"), 4326) LIMIT 5";

  // remove CoffeeShopLocations if on map
  if(map.hasLayer(coffeeShopLocations)){
    map.removeLayer(coffeeShopLocations);
  };

  // remove locationMarker if on map
  if(map.hasLayer(locationMarker)){
    map.removeLayer(locationMarker);
  };

  // Get GeoJSON of five closest points to the user
  $.getJSON("https://"+cartoDBUserName+".carto.com/api/v2/sql?format=GeoJSON&q="+sqlQueryClosest, function(data) {
    coffeeShopLocations = L.geoJson(data,{
      onEachFeature: function (feature, layer) {
        layer.bindPopup('

' + feature.properties.name + '
' + feature.properties.address + '

'); layer.cartodb_id=feature.properties.cartodb_id; } }).addTo(map); }); };

Whoa, that was another big block of code. What did we do here? First, we created a new local variable called sqlQueryClosest. This variable will hold the query we are going to run on the database. For the latitude and longitude values, we used the properties of myLocation to get the latitude and longitude of the location found event. This will convert our found location to a point that can be used in PostGIS. The we find the five closest points using the <-> Operator and set the SRID to be 4326, or WGS84 (meaning PostGIS will expect a latitude and longitude in WGS84).

Next we remove coffeeShopLocations and locationMarker if they exist, this will prevent multiple copies of the layer from appearing on the map. And finally, we write our get JSON statement to return a JSON from CARTO and create a GeoJSON map object from it. Not too bad!

Save and refresh your map. You should see the following!

Find Coffee Shops Closest to Me (Click to view this example on its own.)

See if you can write up a couple other queries and extract data using CARTO SQL API. Next week, we will expand on this and get something in PostGIS database not in CARTO.


Using CARTO.js to Display a CARTO visualization

In addition to the SQL API, which returns objects we can use in Leaflet, CARTO also has a JavaScript library built on top of Leaflet. It is called CARTO.js. If you want to use CARTO for data management, the SQL API will accomplish much of what you are looking for, but if you have created a visualization in CARTO you would like to push to your webmap, you can use the CARTO.js library. The goal of today was to learn databases and PostGIS, and a lesson in CARTO.js is out of the scope of this tutorial, but you should know it exists. As CARTO.js is built on top of Leaflet, it will support all of the functionality of Leaflet and contains a couple add-ons that can help when working with CARTO datasets.

Read more on CARTO.js View some Examples

4. PostGIS Queries: Find all Coffee Shops Near a Click

Adjusting this code to make it so that you find the five nearest coffee shops based on a click is easy. We've already written all the heavy lifting code! Let's give it a shot.

1. First, remove the button that is clicked to find the five nearest coffee shops. The code to remove looks like the following. Then, add a small instruction to click the map to get the five closest coffee shops.

<div id="form">
  <form action="">
    <input type="button" onclick="locateUser()" value="Find Coffee Closest to Me">
    <input type="button" onclick="showAll()" value="Reset Map">
  </form>
</div>

And replace it with...

<div id="form">
  <p>Click on the map to find the five closest coffee shops to that location</p>
  <form action="">
    <input type="button" onclick="showAll()" value="Reset Map">
  </form>
</div>

2. Next, find the following lines. Essentially, we want to get coordinates that we will pass to the query from a click, rather than using the browser location.

// Function that will locate the user when called
function locateUser(){
  map.locate({setView: true, maxZoom: 15});
};

// Map Event Listener listening for when the user location is found
map.on('locationfound', locationFound);

// Map Event Listener listening for when the user location is not found
map.on('locationerror', locationNotFound);

And replace those lines with...

// Listen for a click event on the Map element
map.on('click', locationFound);

3. Save and refresh your map. By passing the coordinates from your click to the PostGIS query, the five closest coffee shops are located by the location you provide on click now! The distances are determined as the crow flies. Check it out!

Click Map to Find Coffee Shops Closest to Me (Click to view this example on its own.)

5. Find Coffee Shops that Intersect North Cambridge

To finish our exercise for the day, let's load a layer of Cambridge neighborhoods into CARTO and find coffee shops in a specific neighborhood. Our map will look like the following.

Coffee Shops in North Cambridge (Click to view this example on its own.)

Open up example-3.html to get started.

1. To achieve this, we need to first upload the cambridge_neighborhoods.geojson file to CARTO as a new dataset. We can then run queries on it.

2. Determine the proper SQL query. The SQL query we need to run to get the intersecting points looks like the following.

SELECT dunkindonuts.name, dunkindonuts.contact FROM dunkindonuts, (SELECT * FROM cambridge_neighborhoods WHERE name = 'North Cambridge') AS temp WHERE ST_Intersects(dunkindonuts.the_geom, temp.the_geom)

Knowing that this works, we can mimic exactly what we did above. Input the following code in the script of example 3.

// Find coffee shops in North Cambridge

// Set up PostGIS SQL Query for North Cambridge
var north_cambridge_query = "SELECT dunkindonuts.name, dunkindonuts.the_geom FROM dunkindonuts, (SELECT * FROM cambridge_neighborhoods WHERE name = 'North Cambridge') AS dd_nc WHERE ST_Intersects(dunkindonuts.the_geom, dd_nc.the_geom)"

// Create function that will run query on click
// Get CartoDB selection as GeoJSON and Add to Map
function showNorthCambridge(){
  if(map.hasLayer(coffeeShopLocations)){
    map.removeLayer(coffeeShopLocations);
  };
  map.setView(new L.LatLng(42.381899, -71.122499), 13);
  $.getJSON("https://"+cartoDBUserName+".cartodb.com/api/v2/sql?format=GeoJSON&q="+north_cambridge_query, function(data) {
    coffeeShopLocations = L.geoJson(data,{
      onEachFeature: function (feature, layer) {
        layer.bindPopup('

' + feature.properties.name + '
' + feature.properties.address + '

'); layer.cartodb_id=feature.properties.cartodb_id; } }).addTo(map); }); };

Our map, on click, will run the query, and load just those points that fall within the North Cambridge neighborhood.

Coffee Shops in North Cambridge (Click to view this example on its own.)

Explore some more on your own, this is a powerful mapping method!


6. Create a Data Collection App Using PostGIS and CARTO (A Teaser)

Now. The fun topic for next week! PostGIS supports adding and modifying data in addition to simply extracting data when working with your spatial databases. The CARTO allows for UPDATE and INSERT SQL statements to be run on your database to add data to your CARTO PostGIS database. UPDATE statements will update existing rows in your database, and INSERT statements will add data to your database.

More on writing data to CARTO

Writing data to CARTO from Leaflet using the SQL API comes with a big caveat however, and this will require some work on our part to get around it. To write data to a CARTO database, you use the following:

https://{account}.carto.com/api/v2/sql?q=INSERT INTO test_table (column_name, column_name_2, the_geom)
VALUES ('this is a string', 11, ST_SetSRID(ST_Point(-110, 43),4326))&api_key={Your API key}

We could use this, but what is the caveat? To write to a database, the CARTO SQL API requires our unique API key, and because JavaScript sits on the client, if we put our API key in to the script, it will be exposed and everyone will be able to see it. This obviously isn't very secure, and it allows anyone to access, modify, add data to, or delete our tables. We don't want this, obviously, so we want to try to put our credentials on the server side, hidden somewhere, so not just anyone can access our data. To do this, we have a couple of options, the one I will show you next week during our workshop time is how to use a server-side language called Hypertext Preprocessor, or PHP to hide credentials on your server.

What is PHP?

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, does does not actually go to the client. When it goes to the client, it is translated to HTML. On the whole, it can be used to create and read HTML, and can access elements from the DOM if they are pushed back to the server. Sites like Wordpress and Drupal are built and secured using PHP along with HTML, CSS, and JavaScript to help make them dynamic and secure. For an expanded lesson in why we use this, view the next #webmapworkshop session (link coming soon) on creating a data collection app that uses PHP and AJAX to push data back to the server and into a CARTO PostGIS table.

Note: Our Python SimpleHTTPServer will not for this application. To run PHP and create a server proxy, first install a software like XAMPP (Window/Mac/Linux), to create a development server environment on your local machine.

Sample Data Collection Web Map Application

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

6. In Conclusion

Spatial databases, specifically PostgreSQL and PostGIS are often excellent solutions for storing and querying data that can be displayed on your web map. The reasons for this include the ability to store large datasets and filter to display what you want, the ability to run spatial queries that allow you to select features according to their relationship with other features, and the ability to save features you might collect or write to your map.

Using CARTO can be a fantastic gateway to expanding your database usage. CARTO data size limits might be lower, but if you can learn principles, it will make it easier if you ever have to create your own database or instance of PostGIS. Read more and practice up, or you can also check out the tutorial I made for installing PostGIS on your WebFaction hosting account.



Return to DUSPVIZ tutorials page