Chapter 9 APIs and Databases

Last updated: 2023-01-05 18:18:20

9.1 Introduction

In Chapters 7–8, the foreground layers we displayed on Leaflet web maps came from GeoJSON files stored on the server. This is a viable approach when our data are relatively small and not constantly updated. When this is not the case, however, using GeoJSON files can become limiting.

For example, loading layers from GeoJSON files becomes prohibitive when files get too large, because the entire file needs to be transferred through the network, even if we only want to display some of the content, for example by subsetting the layer in the JavaScript code after it has been received. It is obviously unreasonable to have the user wait until tens or hundreds of megabytes are being received, in the meanwhile seeing an empty map. Processing very large amounts of data can also make the browser unresponsive. A natural solution is to use a database. Unlike a file, a database can be queried to request just the minimal required portion of information each time, thus making sure that we are transferring and processing manageable amounts of data.

Another limitation of using GeoJSON files becomes apparent when the data are constantly updated and/or used for different purposes rather than just being displayed on a particular web map. For example, we may wish to build a web map displaying real-time municipal events, which means the data are constantly updated or edited (e.g., by the municipality staff) and/or used in various contexts (e.g., examined in GIS software by other professionals). Again, a natural solution is to use a database, shared between numerous concurrent connections for viewing and editing the data, through many types of different interfaces. Our web map, making use of one such concurrent connection, will therefore be synchronized with the database so that the displayed information is always up-to-date.

This chapter (Chapter 9) and the next two (Chapters 10–11) introduce the idea of loading data from a spatial database to display them on an interactive map, while dynamically filtering the data to transfer just the portion that we need. That way, we are freed from the limitation regarding the amount of data “behind” the web map. In other words, the database that stands behind our web map can be very large in size, yet the web map will stay responsive, thanks to the fact that we load subsets of the data each time, based on what the user chooses to see. Moreover, the database can be shared across other users and interfaces, keeping the data up-to-date and synchronized. In this chapter (Chapter 9), we introduce the concepts and technologies that enable a Leaflet map to load data from a spatial database. In the next two chapters, we go through examples of using non-spatial (Chapter 10) and spatial (Chapter 11) database queries for loading subsets of data from a database.

It should be mentioned that Web Map Services (WMS) (Section 6.6.5) comprise an alternative solution for displaying large, up-to-date amounts of data on a web map, however this solution is beyond the scope of this book. In short, with a WMS we are using a GIS database to build on-demand raster tiles. The server generates custom tiles based on the parameters it is given, so that the user has control of the displayed content, such as choosing which layers to display. This is unlike pre-compiled tile datasets, such as those introduced in Section 6.5.10 and used as base layers in the examples in Chapters 6–8, since pre-compiled tiles are fixed and cannot be dynamically modified based on user input.

There are valid use cases for both the database and WMS approaches. Basically, the database approach works better when loading vector layers that the user interacts with, which is made possible by the fact that the server can send raw data (such as GeoJSON), and we can control the way that data are displayed on the client, using JavaScript code. The WMS approach works better when our data are very complex and have elaborate symbology. In such cases, it makes sense to have a dedicated map server with specialized software to build raster images with the displayed content, and send them to the client to be displayed as-is83.

9.2 Web map connection to database

9.2.1 The problem

A problem that immediately arises regarding retrieval of spatial data from a database onto a web map is that client-side scripts cannot directly connect to a database. A dynamic server, which we mentioned in Section 5.4.3, is the solution to this problem. On the dynamic server, server-side scripts, which indeed can connect to the database, are used to query the database and send the data back to the client. In fact, the need to send information from a database to the browser is one of the main motives for setting up a dynamic server.

In this book, we focus on client-side solutions, so we will not be dealing with setting up our own dynamic server coupled with a database. However, we are going to learn about the variuous components of the setup (the database, SQL, and the API) as well as how to use them for our purposes in web mapping.

When setting up a server-side solution, the developer has a wide range of choices, anywhere between two extremes:

  1. Using a dedicated service—simpler to set up and maintain, but more expensive
  2. Using a general-purpose cloud service—more work to set up and maintain, but cheaper

We will now briefly discuss the two approaches. In this book we are going to use the second approach.

9.2.2 The CARTO platform

The simpler yet more expensive solution is to use an existing dedicated service by a commercial company. A notable provider of the solution we cover in this chapter is a commercial company called CARTO. The CARTO platform provides several cloud computing GIS and web-mapping services. One of the services, which is relevant to our discussion, is the SQL API (Section 9.7). CARTO allows you to upload your own data into a managed spatial database, while CARTO’s SQL API allows you to interact with that database. In other words, CARTO takes care of setting up and maintaining a spatial database, as well as setting up server-side components to make that database reachable through HTTP.

CARTO is a commercial service that comes at a price, currently at 199$/month. At the time of writing, there is a free trial of 14 days, and a free plan for 2 years for students. It is worth mentioning that the CARTO platform is open-source. In principle, it can be installed on any computer to replicate almost the entire functionality of CARTO for free. The installation and maintenance are quite complicated though.

9.2.3 Open-source minimal setup

Alternatively, when using a general-purpose cloud service (such a those provided by Amazon, Google, or DigitalOcean), the developer has to “manually” install and configure the software and scripts on the server. To reproduce the essential capabilities of the CARTO SQL API, a minimal setup comprises a cloud server, a database, and a server-side script for the SQL API. The online version of the book (Section 0.8) includes an additional supplement with instructions for one way to do that, using the DigitalOcean cloud infrastructure provider, and using the PostgreSQL/PostGIS and Node.js software. The installation and setup of the server are beyond the scope of the main text of the book, but it is important to be aware that it can be done with some additional time investment. The only paid component in this setup is DigitalOcean, for “renting” a cloud server, which costs as little as 6$/month for minimal hardware. In this book, we use the minimal server setup, pre-installed at the address https://geobgu.xyz/sql/.

Through the rest of the chapter, we will introduce the server platform structure and the technologies it is based on: databases (Section 9.3) and spatial databases (Section 9.4), PostGIS (Section 9.5), SQL (Section 9.6), and the SQL API (Section 9.7). Towards the end of the chapter, we will see how a server can be used for querying and displaying data from a database on a Leaflet map (Section 9.8). In the next two chapters, we will dig a little deeper into different types of queries and their utilization in web mapping. In Chapter 10, we will see an example of non-spatial, attribute-based filtering of data, based on user input from a dropdown menu. In Chapter 11, we will see an example of using spatial queries to retrieve data based on proximity to a clicked location.

9.3 Databases

The term database describes an organized collection of data. A database stores data, but also facilitates indexing, searching, and querying the data, as well as modifying and adding new data. The most established and commonly used databases follow the relational model, where the records are organized in tables, and the tables are usually associated with one another via common columns.

For example, Figure 9.1 shows a small hypothetical database with two tables named airports and flights. The airports table gives the name and location (lon, lat) of seven different airports. The flights table lists the departure time (year, month, day, dep_time), the origin (origin), and the destination (dest) of five different flights that took place between the airports listed in the airports table on a particular day. Importantly, the airports and flights tables are related through the airport code column faa in the airports table matching the origin and dest columns in the flights table.

An example of a relational database with two tables

FIGURE 9.1: An example of a relational database with two tables

Relational database queries, including both “ordinary” (Section 9.6.2) and spatial (Sections 9.6.3–9.6.4) queries, are expressed in a language called SQL (Section 9.6).

9.4 Spatial databases

A spatial database is a database that is optimized to store and query data that represents objects defined in a geometric space. Regarding the storage part, plainly speaking, the tables in a spatial database have a special type of geometry column, which holds the geometric component of that specific record, i.e., the geometry type and the coordinates. This may sound familiar—recall that geometry GeoJSON types (Section 7.3.2), which represent just the geometric part of a feature, consist of two properties: "type" and "coordinates". The similarity between the geometry column and the GeoJSON geometry types in not incidental, but due to the fact that both are based on the Simple Features standard, which we mentioned in Section 7.3.1. The difference is that in a spatial database, the geometries are usually encoded in a format called Well-Known Binary (WKB), a binary version of the Well-Known Text (WKT) format (which we mentioned in Section 7.3.1), rather than in the GeoJSON format.

In addition to geometry storage, spatial databases define special functions that allow for queries based on geometry. This means we can use the database to make spatial numeric calculations (e.g., geographical distance; Sections 11.3.2–11.3.3), retrieve data based on location (e.g., K-nearest neighbors; Section 11.3.2), or create new geometries (e.g., calculating the centroid of a geometry). These are called spatial queries (Section 9.6.4), since they involve the spatial component of the database, i.e., the geometry column of at least one table. The concept is very similar to spatial operators and functions used in GIS software, such as the Select by Location tool in ArcGIS.

Commonly used open-source spatial databases include PostgreSQL/PostGIS (see Section 9.5 below), SQLite/SpatiaLite, and MySQL. There are also proprietary databases that support spatial data, such as Oracle Spatial and Microsoft SQL server.

9.5 What is PostGIS?

PostGIS is a popular extension for the PostgreSQL database, making the PostgreSQL/PostGIS combination a spatial database (Obe and Hsu 2020). In other words, a PostgreSQL database with the PostGIS extension enabled allows for storage of spatial data and execution of spatial SQL queries (Section 9.6.4). At the moment, the PostgreSQL/PostGIS combination84 makes the most powerful open-source spatial database available.

Since both PostgreSQL and PostGIS are free and open-source, you can install PostGIS on your computer and set up your own database. However, running a database requires some advanced setup and maintenance, which is outside the scope of this book85.

9.6 What is SQL?

9.6.1 Overview

SQL is a language for writing statements to query or to modify tables stored in a relational database, whether spatial or non-spatial. Using SQL, you can perform many types of tasks: filtering, joining, inserting new data, updating existing data, etc. SQL statements can be executed in many types of database interfaces, from command lines interfaces, through database administrator consoles in GIS software, and to APIs that connect to the database through HTTP—such as the SQL API, which we are going to use (Section 9.7). You may already be familiar with SQL syntax from GIS software, such as ArcGIS and QGIS, where SQL can be used to select features from a spatial layer.

Using our demonstration server, we will experiment with writing SQL queries to extract data from a cloud-based database (Section 9.7) and to display these data on Leaflet map (Section 9.8). That way, we will become familiar with the whole idea of querying spatial databases, from the web-mapping perspective. Hopefully, this introduction will be of use if, later on, you decide to go deeper into the subject and set up a spatial database on your own (Section 9.2.3).

SQL, as you can imagine, is a very large topic (Nield 2016; DeBarros 2018). The syntax of SQL is not the focus of this book, so we will not go deeply into details nor will we cover the whole range of query types that can be used for various tasks. In the following Chapters 10–11, we will only encounter about ~5-6 relatively simple types of SQL queries, most of which are briefly introduced below (Sections 9.6.2–9.6.4). This set of SQL queries will be enough for our purposes, and you will be able to modify the code to apply the same type of queries to different data, even if you have never used SQL before.

In the following examples (Sections 9.6.2–9.6.4), we will demonstrate several types of SQL queries on a database that contains just one table, a table named plants. The plants table contains rare plant observations in Israel86. Each row in the plants table represents an individual observation of a rare plant species. The table has different columns describing each observation, such as:

  • id—Observation ID
  • name_lat—Latin species name
  • obsr_date—Observation date
  • geom—The location; this column is the geometry column (Section 9.4)

The query examples are just for illustration and are not meant to be replicated in a console or command line, since we are not setting up our own database. However, shortly you will be able to execute them through the SQL API (Section 9.7).

9.6.2 Non-spatial queries

The most basic SQL statement is the SELECT statement. A SELECT statement pulls data from a table, possibly filtered on various criteria and supplemented with new columns resulting from table joins or transformations. For example, we can use the following SELECT query to get a subset of the plants table, with just three of its columns: id, name_lat and obsr_date. The table is also filtered, to include only those rows where the Latin species name is equal to the specific value 'Anticharis glandulosa': 

SELECT id, name_lat, obsr_date 
  FROM plants 
  WHERE name_lat = 'Anticharis glandulosa';

By convention, SQL keywords are written in uppercase, while specific values—such as column names—are written in lowercase. This is not strictly required, as SQL is not case-sensitive, unlike JavaScript, for instance, which is case-sensitive. Spaces and line breaks are ignored in SQL, like in JavaScript. The query ends with the ; symbol.

Note the way that the query is structured. The queried column names are listed after the SELECT keyword, the table name is specified after FROM, and the condition for filtering returned records is constructed after the WHERE keyword. In this example, the condition name_lat='Anticharis glandulosa' means “return all records (rows) where the value of name_lat is equal to 'Anticharis glandulosa'”.

If we had access to a PostGIS database with the plants table and could type the above SQL query through its command line interface (called psql), the following textual printout with the query result would appear in the command line87: 

   id   |       name_lat        | obsr_date
--------+-----------------------+------------
 339632 | Anticharis glandulosa | 1988-03-18
 359135 | Anticharis glandulosa | 2012-12-15
 367327 | Anticharis glandulosa | 2012-12-15
(3 rows)

According to the result, we can tell that there are only three observations of 'Anticharis glandulosa' in the plants table. Note that the last line is not part of the result, but only specifies the number of returned rows.

9.6.3 The geometry column

As mentioned in Section 9.4, the distinctive feature of a spatial database is that its tables may contain a geometry column. The values in the geometry column specify the spatial locations of the respective database records (i.e., the table rows). The geometry column usually contains binary code, which is an encoded version of the Well-Known Text (WKT) format, known as Well-Known Binary (WKB). The binary compression is conventionally used to reduce the required storage space for the database.

For example, the geometry column in our plants table is named geom. The following query returns the contents of three columns from the plants table, the “ordinary” id and name_lat columns, as well as the geometry column named geom. The query is also limited to the first five records, with the LIMIT 5 part: 

SELECT id, name_lat, geom
  FROM plants 
  LIMIT 5;

Here is the printout we would see on the command line in this case: 

   id   |    name_lat    |                        geom                        
--------+----------------+----------------------------------------------------
 321432 | Iris haynei    | 0101000020E6100000520C906802D741400249D8B793624040
 321433 | Iris haynei    | 0101000020E6100000D235936FB6D34140C6151747E55E4040
 321456 | Iris atrofusca | 0101000020E61000001590F63FC0984140EDB60BCD75723F40
 321457 | Iris atrofusca | 0101000020E6100000672618CE35984140357C0BEBC6833F40
 321459 | Iris vartanii  | 0101000020E6100000E6B0FB8EE19141405D6E30D461793F40
(5 rows)

It is evident the WKB strings in the geom column make no sense to the human eye. However, WKB can always be converted to its textual counterpart WKT, using the ST_AsText operator, as demonstrated in the following, slightly modified, version of the above SQL query: 

SELECT id, name_lat, ST_AsText(geom) AS geom 
  FROM plants 
  LIMIT 5;

In the modified query, we replaced the geom part with ST_AsText(geom), thus transforming the column from WKB to WKT. The AS geom part sets the new column name to geom, whereas otherwise it would get a default name such as st_astext. Here is the resulting table, with the geometry column transformed to its WKT representation and renamed to geom:

   id   |    name_lat    |            geom            
--------+----------------+----------------------------
 321432 | Iris haynei    | POINT(35.679761 32.770133)
 321433 | Iris haynei    | POINT(35.654005 32.741372)
 321456 | Iris atrofusca | POINT(35.193367 31.44711)
 321457 | Iris atrofusca | POINT(35.189142 31.514754)
 321459 | Iris vartanii  | POINT(35.139696 31.474149)
(5 rows)

Similarly, we can convert the WKB geometry column to GeoJSON, which we are familiar with from Chapter 7. To do that, we use the ST_AsGeoJSON function instead of the ST_AsText function, as follows:

SELECT id, name_lat, ST_AsGeoJSON(geom) AS geom 
  FROM plants 
  LIMIT 5;

Here is the result, with the geometry column now given in the GeoJSON format:

   id   |    name_lat    |                         geom
--------+----------------+------------------------------------------------------
 321432 | Iris haynei    | {"type":"Point","coordinates":[35.679761,32.770133]}
 321433 | Iris haynei    | {"type":"Point","coordinates":[35.654005,32.741372]}
 321456 | Iris atrofusca | {"type":"Point","coordinates":[35.193367,31.44711]}
 321457 | Iris atrofusca | {"type":"Point","coordinates":[35.189142,31.514754]}
 321459 | Iris vartanii  | {"type":"Point","coordinates":[35.139696,31.474149]}
(5 rows)

Examining either one of the last two query results, we can tell that the the plants table—or at least its first five records—contains geometries of type "Point" (Table 7.2).

9.6.4 Spatial queries

The geometry column can be used to apply spatial operators on our table, just like in GIS software. Much like general SQL (shown previously), the syntax of spatial SQL queries is a very large topic (Obe and Hsu 2020), and mostly beyond the scope of this book. In Chapter 11 we will experiment with just one type of a spatial query, which returns the nearest records from a given point.

For example, the following spatial query returns the nearest five observations from the plants table based on distance to the specific point [34.810696,31.895923] (as in [lon,lat]). Plainly speaking, this SQL query sorts the entire plants table by decreasing proximity of all geometries to [34.810696,31.895923], then the top five records are returned:

SELECT id, name_lat, ST_AsText(geom) AS geom 
  FROM plants 
  ORDER BY 
    geom::geography <-> 
    ST_SetSRID(
      ST_MakePoint(34.810696, 31.895923), 4326
    )::geography
  LIMIT 5;

The selection of top five records is done using the LIMIT 5 part. The spatial operators part comes after the ORDER BY keyword, where we calculate all distances from plants points to a specific point [34.810696,31.895923], and use those distances to sort the table. We will elaborate on this part in Chapter 11. Here is the result, with the five nearest observations to [34.810696,31.895923]: 

   id   |       name_lat       |            geom
--------+----------------------+----------------------------
 341210 | Lavandula stoechas   | POINT(34.808564 31.897377)
 368026 | Bunium ferulaceum    | POINT(34.808504 31.897328)
 332743 | Bunium ferulaceum    | POINT(34.808504 31.897328)
 328390 | Silene modesta       | POINT(34.822295 31.900125)
 360546 | Corrigiola litoralis | POINT(34.825931 31.900792)
(5 rows)

For more information, Chapter 7 in the Introduction to Data Technologies book (Murrell 2009) gives a good introduction to (non-spatial) SQL. The W3Schools SQL Tutorial can also be useful for quick reference of commonly used SQL commands. An introduction to spatial operators and PostGIS can be found in the official Introduction to PostGIS tutorial and in the PostGIS in Action book (Obe and Hsu 2020).

9.7 The SQL API

9.7.1 Overview

An SQL API is an API for communication between a program that understands HTTP, such as the browser, and a PostGIS database hosted on a server. The SQL API allows for users to send SQL queries to the PostGIS database. The queries are sent via HTTP (Section 5.3), typically by making a GET request (Section 5.3.2.2) using a URL which includes the server address and the SQL query. The server processes the request and prepares the returned data, according to the SQL query applied on the database. The result is then sent back, as JSON or GeoJSON. Importantly, the fact that the requests are made through HTTP means that we can send requests to the database, and get the responses, from client-side JavaScript code using Ajax (Section 7.7).

The SQL API which we are going to use was set up at https://geobgu.xyz/sql/. It accepts an SQL query, and the query result from the PostGIS database on the server. The server-side script “understands” whether the result is spatial or not, by looking for the conventionally named geom column:

  • If the result contains a geom column → then the response is GeoJSON
  • If the result does not contain a geom column → then the response is JSON

Through explanation of the server-side script (written) and server setup are are beyond the scope of this book and given in Appendix H. Nevertheless, here is a part of the server-side script, where the type of response is determined:

// ...
db.any(sql)
    .then(function(data) {
        if(data.length > 0 && "geom" in data[0]) {
            return parse_to_geojson(data);
        } else {
            return data;
        }
    })
.then(function(data) {
// ...

Without understanding all the details, you can see that the server-side script (Section H.6) handles the above-mentioned two scenarios:

  • If the response is not empty (i.e., has more than 0 rows) and the 1st row (data[0] object) has the "geom" property, i.e., a column named geom → the server returns GeoJSON (transformed from JSON with the parse_to_geojson function)
  • Otherwise, i.e., if the response is empty or does not have a "geom" property → the server returns the response as is (i.e., JSON)

9.7.2 API usage

The basic URL structure for sending a GET request to the SQL API looks like this:

https://geobgu.xyz/sql?q=SQL_STATEMENT

where SQL_STATEMENT should be replaced with the SQL query.

Note that this is a special URL structure, which contains a query string. A query string is used to send parameters to a dynamic server as part of the URL. The query string comes at the end of the URL, after the ? symbol, with the parameters separated by & symbols. In this case, the query string contains one parameter, named q.

For example, here is a specific query: 

https://geobgu.xyz/sql?q=SELECT id, name_lat, geom FROM plants LIMIT 2

where SQL_STATEMENT was replaced with the specific query SELECT id, name_lat, geom FROM plants LIMIT 2.

9.7.3 Query examples

The plants table used in the above SQL examples (Section 9.6) was already uploaded to the server on https://geobgu.xyz/sql/. We will query the database on this sever to experiment with the SQL API.

Let’s try to send a query to the SQL API to get some data, in the GeoJSON format, from the plants table. Paste the following query into the browser’s address bar:

https://geobgu.xyz/sql?q=SELECT id, name_lat, geom FROM plants LIMIT 2

A GeoJSON file will be returned (Section 9.7.2). The query q was SELECT id, name_lat, geom FROM plants LIMIT 2, which means that we request the id, name_lat and geom columns from the plants table, limited to the first 2 records (Section 9.6.3). As a result, the server takes the relevant information from the plants table and returns the following GeoJSON content88: 

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Point",
        "coordinates": [
          35.679761,
          32.770133
        ]
      },
      "properties": {
        "id": 321432,
        "name_lat": "Iris haynei"
      }
    },
    {
      "type": "Feature",
      "geometry": {
        "type": "Point",
        "coordinates": [
          35.654005,
          32.741372
        ]
      },
      "properties": {
        "id": 321433,
        "name_lat": "Iris haynei"
      }
    }
  ]
}

This is a GeoJSON string of type "FeatureCollection" (Section 7.3.4). It contains two features with "Point" geometries, each having two non-spatial attributes: id and name_lat.

Remember that the geometry column needs to be named geom to be processed into the GeoJSON "geometry" property (in our particular server setup). All columns named other than geom are treated as attributes. For example, if we rename the geom column into something else, it will not be interpreted as the geometry column: 

https://geobgu.xyz/sql?q=SELECT id, name_lat, geom AS geometry FROM plants LIMIT 2

Then we get a JSON, where the geometry column contains the raw WKB contents as stored in the database:

[
  {
    "id": 321432,
    "name_lat": "Iris haynei",
    "geometry": "0101000020E6100000520C906802D741400249D8B793624040"
  },
  {
    "id": 321433,
    "name_lat": "Iris haynei",
    "geometry": "0101000020E6100000D235936FB6D34140C6151747E55E4040"
  }
]

Nevertheless, this type of response is going to be useful when generating non-spatial queries, as we will see later on (see Sections 9.9 and 10.4.3). For example, if we are interested in the ID, species names, and observation date of the first two observations, we execute the following query:

https://geobgu.xyz/sql?q=SELECT id, name_lat, obsr_date FROM plants LIMIT 2

Resulting in the following JSON response:

[
  {
    "id": 321432,
    "name_lat": "Iris haynei",
    "obsr_date": "1900-01-01T00:00:00.000Z"
  },
  {
    "id": 321433,
    "name_lat": "Iris haynei",
    "obsr_date": "1900-01-01T00:00:00.000Z"
  }
]

As you can see, the JSON response is an array of rows, whereas each row is an object of "column_name": value pairs.

By the way, while pasting these URL examples into the browser, you may have noticed how the browser automatically encodes the URL into a format that can be transmitted over the Internet. This is something that happens automatically, and we do not need to worry about. For example, as part of URL encoding, spaces are converted to %20, so that the URL we typed above:

https://geobgu.xyz/sql?q=SELECT id, name_lat, geom FROM plants LIMIT 2

becomes: 

https://geobgu.xyz/sql?q=SELECT%20id,%20name_lat,%20geom%20FROM%20plants%20LIMIT%202

Since the returned file is in the GeoJSON format, we can immediately import it into various spatial applications. For example, the file can be displayed and inspected in GIS software such as QGIS (Figure 9.2). If you are not using GIS software, you can still examine the GeoJSON file by importing it into the geojson.io web interface (Section 7.4.1). More importantly for our cause, the GeoJSON content can be instantly loaded in a Leaflet web map, as will be demonstrated next in Section 9.8.

A GeoJSON file, obtained from the SQL API, displayed in QGIS

FIGURE 9.2: A GeoJSON file, obtained from the SQL API, displayed in QGIS

9.8 Loading API data in Leaflet

We have just learned how to use the SQL API to send SQL queries to a database (Section 9.7). Importantly, since we are working with a spatial database, we get the results in the GeoJSON format (as long as they contain the geom column) (Section 9.7.3). In this section, we will load an API query result in a web page and display it on a Leaflet map. The method we are going to use for loading the query result is the fetch function, which we introduced in Section 7.8 and used in many of the examples in Chapters 7–8 when loading GeoJSON layers from files.

Our starting point is the basic map example-06-02.html from Section 6.5.11, with a modified initial map extent, as follows, so that the plants observations will be visible on page load:

let map = L.map("map").setView([32, 35], 8);

Now, in order to load data from the SQL API on the Leaflet map, we need to go through the following steps:

  • Construct the URL to query the SQL API
  • Get the data and add them on the map

As the first step, we will construct the query URL. For convenience, the URL will be split in two parts: the fixed base URL prefix and the varying SQL query suffix. Combining both parts gives the complete URL, which we will use to retrieve data from the database. The fixed base URL, specific to our particular server, can be kept in a separate variable hereby named url. That way, we do not need to repeat it in each and every query we make in our script:

let url = "https://geobgu.xyz/sql?q=";

Then, we define the varying SQL query part, used to retrieve data from the database according to a specific query. For example, we can use the following query, which returns the name_lat and geom columns for the first 25 records from the plants table:

let sql_query = "SELECT name_lat, geom FROM plants LIMIT 25";

Remember that you need to include the geometry column, and make sure it is named geom, in your query, whenever the requested format is GeoJSON. Otherwise, the layer cannot be generated and we get an error (Section 9.7.3). When the base URL and the SQL query are combined, using url+sql_query, we get the complete URL:

https://geobgu.xyz/sql?q=SELECT name_lat, geom FROM plants LIMIT 25

The complete URL can be passed to fetch to load the resulting GeoJSON from CARTO on the Leaflet map:

fetch(url + sql_query)
    .then(function(response) {
        return response.json();
    })
    .then(function(data) {
        L.geoJSON(data, {
            onEachFeature: function(feature, layer) {
                layer.bindPopup("<i>" + feature.properties.name_lat + "</i>");
            }
        }).addTo(map);
    });

This code should be familiar from Chapters 7–8. The primary function is fetch, which we use to make an Ajax GET request from another location on the internet (CARTO). Since the returned data are in the GeoJSON format, the callback function of .then extracts its contents, as an object, using the .json method. Finally, in the second .then, we can use the L.geoJSON function to convert the GeoJSON object to a Leaflet GeoJSON layer. Using the onEachFeature option we are also binding specific popups (Section 8.5) for each feature to display the Latin name of the observed plant species. Finally, the layer is added on the map with the .addTo method.

The resulting map example-09-01.html is shown in Figure 9.3. Our data from CARTO, i.e., the first 25 plant observations, are loaded on the map!

FIGURE 9.3: example-09-01.html (Click to view this example on its own)

  • Paste the above code section into the console of example-09-01.html.
  • Modify the SQL query (sqlQuery) to experiment with adding different observations on the map, according to the SQL examples shown in Section 9.6.
  • For example, you can replace the LIMIT 25 part with a condition of the form WHERE name_lat = '...' to load all observations of a particular species (Section 9.6.2).

We have now covered the general principles of using the CARTO SQL API to display layers coming from a database on a Leaflet map. So far, however, what we did was not very different from loading a GeoJSON file on a map, like we did in Chapters 7–8. The only difference is that the path to the GeoJSON file was a URL addressing the CARTO SQL API, rather than a local (Section 7.8.1) or remote (Section 7.8.2) GeoJSON file. Still, the query was fixed, in the sense that exactly the same layer with 25 observations (Figure 9.3) will be displayed each time the page is loaded (unless the database itself is modified).

In the beginning of this chapter, we mentioned that one of the main reasons of using a database in web mapping is that we can display subsets of the data, filtered according to user input (Section 9.1). That way, we can have large amounts of data “behind” the web map, while maintaining responsiveness thanks to the fact that small portions of the data are transferred to the client each time. To fully exploit the advantages of connecting a database to a web map, in the next two Chapters 10–11 we will see examples where the SQL query is generated dynamically, in response to user input:

  • In Chapter 10, we will load data according to an attribute value the user selects in a dropdown menu.
  • In Chapter 11, we will load data according to spatial proximity to a clicked location on the map.

9.9 Exercise

  • The following non-spatial SQL query returns the (sorted) species list from the plants table: SELECT DISTINCT name_lat FROM plants ORDER BY name_lat.
  • Load the result of the query inside a web page, to dynamically generate an unordered list (<ul>) of all unique plant species names in the plants table (Figure 9.4).
  • Hint: use example-04-06.html from Section 4.11 (Figure 4.7), where we generated an unordered list based on an array, as a starting point for this exercise.

FIGURE 9.4: solution-09.html (Click to view this example on its own)

References

DeBarros, Anthony. 2018. Practical SQL: A Beginner’s Guide to Storytelling with Data. San Francisco, CA, USA: No Starch Press.
Murrell, Paul. 2009. Introduction to Data Technologies. Boca Raton, FL, USA: Chapman; Hall/CRC.
Nield, Thomas. 2016. Getting Started with SQL: A Hands-on Approach for Beginners. Sebastopol, CA, USA: O’Reilly Media, Inc.
Obe, R, and L Hsu. 2020. PostGIS in Action. 3rd ed. Shelter Island, NY, USA.: Manning Publications Co.

  1. There is an official tutorial on using WMS with Leaflet (https://leafletjs.com/examples/wms/wms.html), where you can see a practical example.↩︎

  2. PostgreSQL with the PostGIS extension will be referred to as PostGIS from now on, for simplicity.↩︎

  3. The online supplement includes instructions for installing PostGIS, as part of the custom SQL API setup (Section 9.2.3).↩︎

  4. The data source of this table is the Endangered Plants of Israel (https://redlist.parks.org.il/) website by the Israel Nature and Parks Authority.↩︎

  5. Instructions to set up a PostGIS database and import the plants table are given in the online supplement, as part of setting up an alternative SQL API (Section 9.2.3).↩︎

  6. Note that the GeoJSON response was formatted for convenience. The actual response is returned as an unformatted string, without any spaces and line breaks.↩︎