# Chapter 10 Non-spatial queries

Last updated: 2018-11-11 20:44:59

## 10.1 Introduction

In Chapter 9, we introduced the CARTO SQL API, and used it to display the result of a database query on a Leaflet map (Figure 9.7). The query we used was constant: the same observations, namely the first 25 records in the plants table, were deterministically loaded. In this Chapter we are going to extend this example with a user interface where one can choose which subset of the data gets downloaded and displayed. Specifically, we will use an input element where the user selects a species name, which we will use to dynamically generate an SQL query for filtering the plants table and getting the observations for that particular species only. That way, the user is able to load all observations for one plant species at a time.

We will accomplish the above in several steps -

• Experiment with queries for loading a subset of records from a database onto a Leaflet map (Section 10.2.2), using SQL filtering based on attribute values (Section 10.2.1)
• Add a <select> input for choosing a species (Section 10.3)
• Collect all unique species names, also using SQL (Section 10.4.3)
• Automatically add all unique species names as <option> elements inside <select> (Section 10.4.4)
• Bind the list with the map, so that changing our species choice will load new observations on the map (Section 10.5.3)

The final result we aim at in this Chapter is shown on Figure 10.7.

## 10.2 Subsetting with SQL

### 10.2.1 Filtering based on attributes

In Chapter 9, we used the following SQL query to load the first 25 records from the plants table on a Leaflet map (Figure 9.7).

SELECT *
FROM plants
LIMIT 25;

As mentioned in Section 9.6.2, using SQL we can also filter the returned data, using the WHERE keyword combined with one or more criteria. For example, the name_lat column specifies the Latin name of the observed species in the plants table. In case we want to select the observation of just one species, we can add the WHERE name_lat = '...' expression to our SQL query, with ... being the species name of interest. For example, the following expression gives all observations where the name_lat is equal to 'Iris mariae' (Figure 10.1).

SELECT *
FROM plants
WHERE name_lat = 'Iris mariae';

For simplicity, let’s add LIMIT 1 to examine just the first observation for 'Iris mariae' -

SELECT *
FROM plants
WHERE name_lat = 'Iris mariae'
LIMIT 1;

When working with CARTO we can execute this query with the corresponding SQL API call -

https://michaeldorman.carto.com/api/v2/sql?format=GeoJSON&q=
SELECT * FROM plants WHERE name_lat = 'Iris mariae' LIMIT 1;

Here is the returned GeoJSON string -

{
"type": "FeatureCollection",
"features": [
{
"type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [
34.412502,
31.019879
]
},
"properties": {
"cartodb_id": 26,
"id": 358449,
"sp_id": 30,
"observer": "ערן היימס",
"presence": 1,
"obsr_date": "2010-03-13T00:00:00Z",
"population_type": 1,
"model": "obs.obsr",
"name_heb": "אירוס הנגב",
"name_lat": "Iris mariae"
}
}
]
}

Indeed, this is a "FeatureCollection" with just one feature and all available attributes, representing a single record for 'Iris mariae' as requested from the server.

### 10.2.2 Displaying on a map

For adding the observations of a single species on a Leaflet map, we can take example-09-01.html from Section 9.8 and modify just the SQL query part. Instead of the query we used in example-09-01.html we can use the new one which we have just constructed, where an individual species is being selected -

SELECT *
FROM plants
WHERE name_lat = 'Iris mariae';

Or better yet, rather than requesting all table columns with SELECT * we can limits the returned columns to just the ones we need, for minimizing the size of transferred data. In this case we need the name_lat column (for popup content, see below), and the_geom column for being able to export a GeoJSON object. Thus SELECT * becomes SELECT name_lat, the_geom -

SELECT name_lat, the_geom
FROM plants
WHERE name_lat = 'Iris mariae';

After creating the map object and loading a tile layer, the following code is inserted to load the observations for 'Iris mariae' on the map, along with popups -

var urlGeoJSON = "https://michaeldorman.carto.com/api/v2/sql?" +
"format=GeoJSON&q=";
var sqlQuery1 = "SELECT name_lat, the_geom FROM plants " +
"WHERE name_lat = 'Iris mariae'";

$.getJSON(urlGeoJSON + sqlQuery1, function(data) { L.geoJSON(data, { onEachFeature: function(feature, layer) { layer.bindPopup( "<i>" + feature.properties.name_lat + "<i>" ); } }).addTo(map); }); This code section is very similar to the ones we used to load GeoJSON of the real-time earthquake locations or towns boundaries in Chapters 7 and 8, respectively. It incorporates the $.getJSON function for loading GeoJSON with Ajax (Section 7.6.3), as well as the onEachFeature option of L.geoJSON to bind specific popups per feature (Section 8.5). In this case the popup displays the name_lat attribute value, in italic font. The novel part, introduced in Chapter 9, is that instead of loading a static GeoJSON from a file or from a URL, we are requesting the GeoJSON content from the CARTO SQL API, using a URL for a specific SQL query as given in sqlQuery1.

The resulting map (example-10-01.html) with marked 'Iris mariae' observations is shown on Figure 10.2.

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

## 10.3 Creating a dropdown menu

Our final goal is for the user to be able to choose a species he/she wants to display, rather than setting it to 'Iris mariae'. Therefore, our next step is to add an input element for selecting a species to load on the map. Since there are several hundreds of species (417 to be exact; see below how to find that out), a list or a set of radio buttons will be too long to fit on screen and inconvenient to browse. A dropdown menu is a good choice in case we give a lot of options to choose from. A dropdown menu can be opened to browse the species list and automatically closed again when selection is made, thus obstructing the background (the Leaflet map, in our case) only temporarily.

As we have seen in Section 1.5.12, a dropdown menu can be constructed using the <select> element, with an internal set of <option> elements for the different choices. For example, suppose that we had just three plant species in the database. In such case, the dropdown menu could be defined with HTML code such as follows -

<select id="species_sel">
<option value="Abutilon indicum">
Abutilon indicum
</option>
<option value="Acinos rotundifolius">
Acinos rotundifolius
</option>
</option>
</select>

Each <option> element has a value attribute, as well as text content. Recall from Section 1.5.12 that the value attribute specifies the ID sent to the server, or queried with the .val method of jQuery (Section 4.6.6), while the text contents between the opening and closing tags (<option>...</option>) specifies the texts displayed on screen. In this particular example the value attribute and the text content are identical, but this does not necessarily have to be so (see Section 1.5.12 for an example).

To incorporate a dropdown menu such as the one coded above into our Leaflet map, we use the L.control function, which we already know how to use from Sections 6.8 and 8.6. The following code can be added to our <script> to build the dropdown menu -

var dropdown = L.control({position: "topright"});
var div = L.DomUtil.create("div", "dropdown");
div.innerHTML =
'<select id="species_sel">' +
'<option value="Abutilon indicum">' +
'Abutilon indicum' +
'</option>' +
'<option value="Acinos rotundifolius">' +
'Acinos rotundifolius' +
'</option>' +
'</option>' +
'</select>';
return div;
};
dropdown.addTo(map);

The above code is actually composed of three expressions -

• Defining a map control object, named dropdown, using L.control
• Setting the contents of the control, using the .innterHTML property and custom HTML code. Note that the control itself is set as a <div> element with class "dropdown", and the <select> element inside the <div> is set with an ID attribute of "species_sel"
• Adding the dropdown control to our map

Next, we need to improve the default display by adding some CSS rules applied to #species_sel, which is the <select> input element. For example, we can make the font slightly larger, add shadow effects around the input box, etc. To do that, we add the following entry in the <style> element within the <head> -

#species_sel {
font: 14px/16px Arial, Helvetica, sans-serif;
background-color: rgba(255,255,255,0.8);
}

Here is a short explanation of what each of the above styling rules does. You can go back to Chapter 2 for more information on each of these properties.

• padding: 6px 8px; - The padding clears an area around the content (and inside the border) of an element. When two values are given, the first one (6px) refers to padding-top and padding-bottom and the second one (8px) refers to padding-right and padding-left (Section 2.7.3)
• font: 14px/16px Arial, Helvetica, sans-serif; - Specifies the font properties. When two sizes are given, the first one (14px) refers to font-size and the second one (16px) refers to line-height (the total height of text plus the distance to the next line). The font specification first gives specific fonts (Arial, Helvetica), then a general font family (sans-serif) to fall back on if those fonts are not installed on the system (Section 2.7.2)
• background-color - The box background color is given in the RGBA format: intensity of red, green, blue and transparency (alpha). In this case we have white at 80% opacity (rgba(255,255,255,0.8)) (Section 2.7.1)
• box-shadow - Adds a shadow around the element’s box. The sizes refer to horizontal offset (0), vertical offset (0), where zero means the shadow is symmetrical around all sides of the box. Blur distance (15 px) determines how far the shadow extends. Color specification comes next, in this case the shadow color is black at 20% opacity (rgba(0,0,0,0.2))
• border-radius - Adds rounded corners to an element, the size (5px) sets the radius (Section 2.7.3.2)

The reason we don’t need any styling rules for the entire control <div> (e.g. using the class selector .dropdown), just for the internal <select> element (using #species_sel), is that the control contains nothing else but the dropdown menu. The exercise (Figure 10.8) for this Chapter will require creating a more complex control, with two dropdown menus. In that case, styling the entire control is necessary too - for styling its external borders, the text labels of its the separate dropdown menus, etc.

The resulting map (example-10-02.html) is shown on Figure 10.3.

FIGURE 10.3: example-10-02.html (Click to view this example on its own)

The dropdown menu with the three individual species, which can be seen in the top-right corner on 10.3 is not yet functional. We can make a selection of a different species, but the map will still show 'Iris mariae', since it is hard-coded into our SQL query sqlQuery1. However, before making the necessary adjustments for linking our dropdown menu with the marker layer on the map we would like to improve the way that we create the dropdown menu in the first place. Instead of using pre-defined HTML code with just three species, we would like to build the options dynamically, with JavaScript code. Remember the $.each iteration example from Section 4.11, where a list of species names was dynamically constructed based on an array? This is exactly the technique we will use to dynamically populating our species dropdown menu with all unique species names. ## 10.4 Populating dropdown options ### 10.4.1 How many species do we have? Before looking into the dropdown menu construction, let’s check how many different plant species we actually have in the database. The following SQL query can be used to do that - SELECT COUNT(DISTINCT name_lat) AS n FROM plants; We are using the COUNT function along with the DISTINCT keyword to get the number of unique values in the name_lat column, putting the result into a new column named n. The result is a small table with one column (n) and one row, as follows -  n ----- 417 (1 row) We can get this result with the CARTO SQL API too, as follows - https://michaeldorman.carto.com/api/v2/sql?q= SELECT COUNT(DISTINCT name_lat) AS n FROM plants; Here is the response - { "rows": [ { "n": 417 } ], "time": 0.04, "fields": { "n": { "type": "number" } }, "total_rows": 1 } Since we omitted the format parameter, the result is a JSON object. Remember that, as mentioned in Section 9.7.2, applying a non-spatial query such as the one above with the format=GeoJSON option will result in an error, because in order to produce a GeoJSON object the query must include the geometry column. When output format is set to JSON, the "rows" property of the resulting object is the actual returned content. The other properties in the JSON object are metadata coming from the API, with information regarding processing time ("time"), the column data types ("fields") and the total number of rows ("total_rows"). The "rows" property is structured as an array, with array elements representing table rows. Each element is an object, representing all of the column values for that table row. In this case, there is just one row and one column, which is why the array has just one element, and that element is an object with just one property - [{"n": 417}] The response tells us that there are 417 species in the plants table. Manually writing the <option> elements for each one of them is obviously not a good idea. Automating the construction of HTML code with 417 <option> element will be more convenient. What is more important, however, is the fact that our dynamically generated dropdown menu will be based on the exact species present in the database, in real-time. In other words, with the dynamic approach we no longer need to worry about updating the HTML code in case the database changes, since the HTML code is built based on content from the database itself and is always up-to-date. Remember that unlike a local file, the CARTO database, just like any other database, may be accessed and modified by different users on different platforms. For example, a database with a list of rare and protected species may be periodically updated by the GIS department of the Nature and Parks authority, adding new observations, removing species that are no longer considered rare, and so on. When our web page connects to the database to dynamically generate its contents each time we browse, we do not need to worry about changing the code to reflect database changes; the changes will be automatically reflected each time the database is queried. • Removing the DISTINCT keyword from the above query gives the overall number of values in the name_lat column, that is, the total number of records in the table • Execute the query through the CARTO SQL API to find out how many plant observations are there in the plants table ### 10.4.3 Finding unique values The following SQL query can be used to get the list of unique values in the name_lat column of the plants table - SELECT DISTINCT name_lat FROM plants ORDER BY name_lat; The SELECT DISTINCT keyword combination before the name_lat column name ensures that we only get the unique values in that column. We are also using the ORDER BY keyword to have the resulting table of species names sorted by alphabetical order of their names. It makes sense that the options in the dropdown menu are alphabetically ordered: that way the user can easily locate the species he/she is interested in, by scrolling towards that species and/or by typing its first letter with the keyboard. Also, all species of the same genus will be listed together since they start with the same word. You can test the above query with the CARTO API - https://michaeldorman.carto.com/api/v2/sql?q= SELECT DISTINCT name_lat FROM plants ORDER BY name_lat The JSON result should look as follows - { "rows": [ {"name_lat":"Abutilon indicum"}, {"name_lat":"Acinos rotundifolius"}, {"name_lat":"Adonis aestivalis"}, ... ] "time": 0.032, "fields": { "name_lat": { "type": "string" } }, "total_rows": 417 } The "rows" property, which contains the returned table, is deprecated here to save space, with the ... symbol representing further objects for the other 414 species not shown. We will use this response to fill up the dropdown menu where the user selects a species to display on the map. ### 10.4.4 Adding the options To populate the dropdown menu, we need to incorporate the above SQL query into our script. First, we set a variable sqlQuery2 for the SQL query listing the distinct species. We now have two different SQL queries: the one for loading observations for one species sqlQuery1, and the one for finding unique species names sqlQuery2. For now, in sqlQuery1 we manually set the first displayed species to 'Abutilon indicum', since it is the first species in terms of alphabetic ordering (see above SQL API response) and therefore comprising the first option in the dropdown menu. Later on (Section 10.6) we will replace this with an automatic way to determine the first species to load, without hard-coding its name into the script. Also note that we have two different URL prefixes, urlJSON for getting sqlQuery2 in JSON format (the unique species names) and urlGeoJSON, with the format=GeoJSON option, for getting sqlQuery1 in GeoJSON format (the plant observations). var urlJSON = "https://michaeldorman.carto.com/api/v2/sql?q="; var urlGeoJSON = "https://michaeldorman.carto.com/api/v2/sql?" + "format=GeoJSON&q="; var sqlQuery1 = "SELECT name_lat, the_geom FROM plants " + "WHERE name_lat = 'Abutilon indicum'"; var sqlQuery2 = "SELECT DISTINCT name_lat FROM plants " + "ORDER BY name_lat"; Second, we insert the following expression for constructing the dropdown menu based all unique species names in the database - $.getJSON(urlJSON + sqlQuery2, function(data) {
$.each(data.rows, function(index, value) {$("#species_sel").append(
'<option value="' + value.name_lat + '">' +
value.name_lat +
'</option>'
);
});
});

This is an Ajax request using $.getJSON. What does it do? Let’s go over the code step by step. • The $.getJSON function is used to load JSON content with unique species names from the database, using urlJSON+sqlQuery2, as an object named data. The contents of data is the same as shown in the JSON printout from Section 10.4.3
• Then, the $.each function is used to iterate over each element in the rows property of data (i.e. the returned table rows), applying a function which - • Uses a DOM selector with a reference to the #species_sel element, i.e. our dropdown menu • Appends an internal <option> element before the closing tag of the referred <select> element, with the content being the name_lat property of the current row value Note that in the above example, we are actually searching for the dropdown menu reference "#species_sel" in the DOM for 417 times, since the expression $("#species_sel") is executed inside the iteration. This is not necessary. We can locate the "#species_sel" element just once, keeping its reference in a variable (named menu in the example below). That way, the browser searches for the #species_sel reference only once, and uses the same reference inside the iteration. The performance difference is negligible in this case, but this is an example of an optimization: getting rid of actions that are unnecessarily repeated in our code.

$.getJSON(urlJSON + sqlQuery2, function(data) { var menu =$("#species_sel");
$.each(data.rows, function(index, value) { menu.append( '<option value="' + value.name_lat + '">' + value.name_lat + '</option>' ); }); }); As a result of adding either of the last two code sections, we now have a map (example-10-03.html) with a dropdown menu listing all available species. The observations of the first species on the list are shown on the map on page load (Figure 10.4). FIGURE 10.4: example-10-03.html (Click to view this example on its own) • Insert a console.log expression inside the <script> of example-10-03.html so that the name of each species is printed in the console as it is being added to the list of options • You should see a printout such as shown on (Figure 10.5) ## 10.5 Updating the map ### 10.5.1 Overview We now have a web page showing observations of the first species from the plants table, as well as a dropdown menu with all species that are present in the plants table. Try clicking on the dropdown menu and selecting a species. Nothing happens! What’s still missing is an event listener, triggering an update of the plants layer on selection change. In the present case, the event listener function should do the following things whenever the species selection changes - • Clear the map of all previously loaded observations • Define a new SQL query for getting the observations corresponding to the species currently selected in the dropdown menu • Send the SQL query to the database and get the observations, as GeoJSON • Display the new observations on the map ### 10.5.2 Manual example Before setting up the event listener it is sometimes convenient to start with a manually defined, specific case. Suppose the user chose a different species instead of 'Abutilon indicum', say 'Iris mariae', in the dropdown menu. The code for adding display a new species on the map is exactly the same as the one we used for 'Abutilon indicum'. The only thing that’s changed is the species name in the SQL query. Try entering the following code section into the console in the above example. This load new observations, for 'Iris mariae', on top of the existing ones for 'Abutilon indicum' - var valueSelected = "Iris mariae"; var sqlQuery3 = "SELECT name_lat, the_geom FROM plants " + "WHERE name_lat = '" + valueSelected + "'";$.getJSON(urlGeoJSON + sqlQuery3, function(data) {
L.geoJSON(data, {
onEachFeature: function(feature, layer) {
layer.bindPopup(
"<i>" + feature.properties.name_lat + "<i>"
);
}
});
• Change the value of valueSelected in the above code section to a different species name from the list and execute the code section in the console
• You should see observations of the species you chose added on the map

### 10.5.3 Automatic updating

In the last code section we manually added new observations of another species from the database. What we now need is to associate this code section with dropdown menu changes. In other words, we want the selection of a new species in the menu to trigger the automatic removal of the old observations and loading the new ones. This is where the event listener comes into play.

The first thing we do is define a layerGroup named plantsLayer and add it on the map. The named layer group can be referenced later on, to clear it or add layers into it. This will make it easier for us to clear any previously loaded species and add markers for the currently selected one. Recall the GeoJSON viewer in example-07-02.html from Section 7.5, where we used a layer group for clearing old GeoJSON layers whenever a new one is submitted from the text area input.

var plantsLayer = L.layerGroup().addTo(map);

Accordingly, we change the .addTo(map) part with .addTo(plantsLayer) when loading the initial species on the map. So, the following piece of code from example-10-03.html -

$.getJSON(urlGeoJSON + sqlQuery1, function(data) { L.geoJSON(...).addTo(map); }); Is changed to this one in example-10-04.html - $.getJSON(urlGeoJSON + sqlQuery1, function(data) {
});

The second thing we do is “wrap” the manual example from the previous section (which loaded "Iris mariae"), so that -

• It is contained inside an event listener function, responding to changes in the dropdown menu ("#species_sel"). The appropriate event type (see Section 4.2.3) in this case is "change", which means that any change in the input element triggers the even listener
• The old observations are cleared before adding new ones, using .clearLayers()
• The species name to load from the database is determined from the currently selected value in the dropdown menu, using $("#species_sel").val() (rather than hard coded as "Iris mariae") The following code section does all of that - $("#species_sel").on("change", function() {
plantsLayer.clearLayers();
var valueSelected = $("#species_sel").val(); var sqlQuery3 = "SELECT name_lat, the_geom FROM plants " + "WHERE name_lat = '" + valueSelected + "'";$.getJSON(urlGeoJSON + sqlQuery3, function(data) {
L.geoJSON(data, {
onEachFeature: function(feature, layer) {
layer.bindPopup(
"<i>" + feature.properties.name_lat + "<i>"
);
}
});
});

Let’s review the code, step by step. In the external part of the expression, we see that an event listener is being binded to a DOM selection -

$("#species_sel").on("change", function() { ... }); More specifically, an anonymous function is being set to execute every time the input element "#species_sel" changes. Let’s now review the body of that function. First thing, the function clears the contents of the plantsLayer. This means that any new selection on the dropdown menu clears the map of any “old” markers - plantsLayer.clearLayers(); Next, the function defines a new variable named valueSelected - var valueSelected =$("#species_sel").val();

The value of valueSelected is the name of the currently selected species on the dropdown list. The current value is obtained with the .val method, which we learned about in Section 4.6.6. Recall the calculator example example-04-09.html where we used .val to get the currently entered numbers for displaying their multiplication product (Section 4.13).

Second, valueSelected is being used to construct a new SQL query string sqlQuery3. This query will be used to request the observations of the newly selected species from the database -

var sqlQuery3 =
"SELECT name_lat, the_geom FROM plants " +
"WHERE name_lat = '" + valueSelected + "'";

Finally, the new GeoJSON content is being requested with urlGeoJSON+sqlQuery3 and the $.getJSON function. The returned GeoJSON object is then added on the map using L.geoJSON and .addTo(plantsLayer) - $.getJSON(urlGeoJSON + sqlQuery3, function(data) {
L.geoJSON(data, {
onEachFeature: function(feature, layer) {
layer.bindPopup(
"<i>" + feature.properties.name_lat + "<i>"
);
}
});

The resulting map (example-10-04.html) is shown on Figure 10.6. Thanks to the event listener, the dropdown menu is now functional and the map is responsive to the current species selection.

FIGURE 10.6: example-10-04.html (Click to view this example on its own)

## 10.6 Rearranging the code

Going over the complete code of example-10-04.html (above), you may notice one important drawback: there are two code section doing practically the same thing. Namely, we have the following code section for loading the initial query sqlQuery1, displaying the initial species from the database on page load -

$.getJSON(urlGeoJSON + sqlQuery1, function(data) { L.geoJSON(...).addTo(plantsLayer); }); And we also have the event listener, which does the same thing for each subsequently selected species, using a dynamically generated query sqlQuery3 - $("#species_sel").on("change", function() {
plantsLayer.clearLayers();
var valueSelected = $("#species_sel").val(); var sqlQuery3 = "SELECT name_lat, the_geom FROM plants " + "WHERE name_lat = '" + valueSelected + "'";$.getJSON(urlGeoJSON + sqlQuery3, function(data) {
});
});

This duplication contradicts the DRY (Don’t Repeat Yourself) principle, an essential guideline in computer programming. Basically, unnecessarily duplication makes our code more difficult to manage, since we need keep track of all duplicated parts and make sure they are synchronized every time we make changes in our code. For example, suppose we decide to change the popup content for the plant observation markers, say, adding the observation date below the species name. In the current code structure, we need to change the respective code inside L.geoJSON in two places: the L.geoJSON function call that loads the initial species and the L.geoJSON function call that loads any subsequently selected species. If we forget to change one of those places, then popup content will look different depending whether we used the dropdown menu or not, which is clearly undesired.

One more inconvenience in the present code configuration is that the initial species, the one displayed on map load, is hard-coded in sqlQuery1 (it is 'Abutilon indicum'). Like we said earlier, the database may be changed in the future, so that 'Abutilon indicum' may be removed or a new species with alphabetical precedence will be added. In such case the initial view will be incorrect: the species initially loaded on the map will no longer match the first species inside the dropdown menu. Again, it is beneficial to determine the first species to load according to the database, rather than based on a constant string.

The solution to the duplication issue is to use a function, which also makes our code slightly shorter and more elegant. Instead of repeating the above code section in two places, we wrap the second code section - the one for loading the currently selected species - in a new function called displaySpecies -

function displaySpecies() {
plantsLayer.clearLayers();
var valueSelected = $("#species_sel").val(); var sqlQuery3 = "SELECT name_lat, the_geom FROM plants " + "WHERE name_lat = '" + valueSelected + "'";$.getJSON(urlGeoJSON + sqlQuery3, function(data) {
L.geoJSON(data, {
onEachFeature: function(feature, layer) {
layer.bindPopup(
"<i>" + feature.properties.name_lat + "<i>"
);
}
});
}

The displaySpecies function can be used in an .on("change", ...) event listener, just like the anonymous function was in the previous version, so the event listener definition from example-10-04.html -

$("#species_sel").on("change", function() {...}); Becomes this in example-10-05.html - $("#species_sel").on("change", displaySpecies);

This change takes care of clearing the map and loading a new species whenever the dropdown menu selection changes. However, how will the initial species be added on map load? To do that, we simply need to call the function one more time in our script, outside of the event listener. The appropriate place to call the function is inside the Ajax request constructing the dropdown menu. That way, the first species is displayed right after the dropdown menu is filled with all of the unique species names.

$.getJSON(urlJSON + sqlQuery2, function(data) { var menu =$("#species_sel");
$.each(data.rows, function(index, value) { menu.append( '<option value="' + value.name_lat + '">' + value.name_lat + '</option>' ); }); displaySpecies(); }); Since displaySpecies is being called after the dropdown menu is already built, we can be sure that the initially loaded species corresponds to the first selected <option>. The resulting map (example-10-05.html) is shown on Figure 10.7. Visually it is the same as example-10-05. However, the underlying code is now improved. FIGURE 10.7: example-10-05.html (Click to view this example on its own) ## 10.7 Exercise • Build a web map with two dropdown menus - for genus and species of rare plants (Figure 10.8) • When the user selects a genus, the second dropdown should automatically update to list the species in that genus • The user then selects a species to show it on the map • If the user makes no selection, the first species in each genus should be automatically shown on the map • This is a challenging exercise, so here is a hint on how to start. Run the following code section in the console of example-10-05.html. This piece of code constructs an object named species, whose properties are genus names and values are arrays with all species in that genus. Expand the code and use the species object to populate the dropdown menus. var species = {};$.getJSON(urlJSON + sqlQuery2, function(data) {
\$.each(data.rows, function(index, value) {
var tmp = value.name_lat;
var tmp_split = tmp.split(" ");
if(species[tmp_split[0]] === undefined) {
species[tmp_split[0]] = [tmp];
} else {
species[tmp_split[0]].push(tmp);
}
});
});