You can translate the document:

Overview

In this document, we will see how we can leverage Denodo's GIS functionality to build a simple app to display geospatial data on a map.

The Denodo Geo Service serves as a gateway to query the views hosted within a Virtual DataPort Server. By leveraging this service, applications can retrieve GeoJSON objects. This flexibility enables seamless integration with a wide range of applications, whether they require GeoJSON objects. The Denodo Geo Service ensures that the output format is standardized as JSON. When querying a view that contains a specific field representing geometric data, this information is automatically incorporated into the resulting JSON object, providing a comprehensive representation of the data's geometry type.

API Data Sources

A web API data source is an external service or endpoint that provides data and functionalities through API calls. It can offer various types of data and allows developers to access and retrieve data from the source by making HTTP requests to specific URLs.

We will be utilizing Government Data APIs, which are open data APIs published by many governments. These APIs provide information on various topics, such as demographics, U.S. Energy Information Administration, and health data. Additionally, we will use a web open data source API that contains county boundaries for U.S. states.

For this integration, we will be utilizing open source APIs and implementing them using theDenodo Geo Service. This web service provides structured geospatial data, which can be accessed through querying views within a Virtual DataPort Server. The data is returned in the form of GeoJSON objects or ArcGIS Features in JSON format, with geometry type incorporated if the view contains a designated field of geometric type.

Websites and APIs

Open data soft Website: containing the county boundaries for U.S states along with its latitude and longitude coordinates. US County Boundaries.

Open data soft API: US County Boundaries API 

Covid Tracking Website: the most recent COVID data for the US. Data API | The COVID Tracking Project

Covid Data API:: Covid Tracking API

U.S Energy Information Administration,

IMPORTANT: In order to access the U.S Energy Information Administration API you will need to

register for an API key through this URL: https://www.eia.gov/opendata/register.php.

After filling out the indicated information on the site. The API key will be sent to your email address along with any required API notifications.

Sample API URL: EIA API URL

Denodo Geo Service Installation

Here is a detailed outline of the steps to deploy the Denodo Geo Service:

  1. Go to the Denodo Support Site
  2. Click on the "Downloads" drop-down menu and select "DenodoConnects".
  3. Download the "Denodo Geo Service" and extract the files to a location of your choice.
  4. Navigate to the extracted files and go to the Denodo Geo Service\denodo-geo-service-8.0\bin\ folder and run the "denodo_geo_service.bat" script.
  5. Wait for the Denodo Geo Service to deploy. This may take a few minutes.
  6. Once the deployment is complete, you can start using the Denodo Geo Service for your data integration needs.

Connecting the APIs to Denodo

US Counties API

  1. Launch Denodo Web Design Studio.
  2. Go to the  "New" menu and select "Data Source" from the dropdown menu.
  3. In the "Data Source" menu, choose "JSON" as the connector to use.
  4. Enter a name for the new data source.
  5. In order to get the latitude and longitude information for each US state, enter the base URL for the API:

https://public.opendatasoft.com/api/records/1.0/search/?dataset=us-county-boundaries&q=&rows=400&facet=statefp&facet=countyfp&facet=name&facet=namelsad&facet=stusab&fac

  1. Once you have entered all the necessary information, click "Save" to create the new data source.
  2. In the data source tab, click ‘Create Base View’ and save it as "bv_states".

Flatten Views

Denodo Virtual DataPort offers support for a wide range of complex data types through the utilization of "register" and "array" types. With this advanced feature, you can effortlessly transform even the most complex hierarchical data structures into easily readable rows and columns, which can then be queried with precision and efficiency. This is particularly useful when working with large datasets that contain numerous JSON files, as the ability to extract specific data elements allows you to seamlessly query and join data from across multiple sources, enabling you to gain valuable insights that would otherwise be difficult to obtain.

Flatten the base view ‘bv_states’ and save it as ‘f_states’.

Before joining this data with the COVID-19 data, it's important to clean up the information as needed. This may involve removing any unnecessary or redundant data, or reformatting certain fields to ensure consistency across datasets. Take the time to ensure that all the data is accurate and relevant before proceeding with the data join.

COVID-19 API

Next, we will integrate the COVID-19 API:

  1. Go to the Denodo Web Design Studio.
  2. Create a new JSON data source.
  3. Enter the base URL for the API: https://api.covidtracking.com/v1/states/current.json
  4. Save the new data source as "covid_tracking".
  5. Create a base view and save it as "covid_tracking".
  6. Flatten the base view and save it as "f_covid_tracking".

Joining the Views

We are going to combine the information coming from the states view with the Covid tracking view to obtain the point_wkt format for each state location along with its corresponding Covid information. This allows us to display the data in GeoJSON format, which is a widely used format for encoding a variety of geographic data structures. With this approach, we are able to take advantage of the strengths of both the states view and the Covid tracking view, enabling us to gain a more comprehensive understanding of the Covid situation in each state. Furthermore, the GeoJSON format allows for more flexible and dynamic visualizations of the data, enabling us to identify patterns and trends that may not be immediately clear in other formats. This approach represents a significant advancement in our ability to analyze and visualize Covid data, and has the potential to provide valuable insights into the factors that are driving the spread of the virus across different regions.

  1. Create a new join view.
  2. Drag and drop the two views you want to join. In our case, we are joining the views ‘f_covid_tracking’ and ‘f_states’.
  3. Select join condition as f_bv_states.stusab = f_covid_tracking.state
  4. Save the view as ‘covid_tracking_states’.

Once the new join view is created and with the Denodo Geo Service running the information coming from the view will be available from the Geo Service that will return the data from the view in a predefined JSON format. In our example the URL will be:

http://localhost:8999/geojson/admin/views/covid_tracking_states

Where admin is the virtual database where we have created the view and the hostname and port can be changed depending on the specific configuration.

To access this information authentication will be needed and the data returned can be used from an Apache Zeppelin Notebook.

Installing Apache Zeppelin

Apache Zeppelin is an open-source web-based notebook for interactive data analytics and visualization. It provides a collaborative and interactive environment for data exploration, analysis and visualization.

We will be using the official docker image for this example. Docker needs to be installed and configured in your environment to run an Apache Zeppelin container.

Use this command to launch Apache Zeppelin in a container.

docker run -p 8080:8080 --rm --name zeppelin apache/zeppelin:0.10.0

Once it is running you will be able to access your Apache Zeppelin on localhost:8080 from a browser.

Creating a Zeppelin Notebook

From Zeppelin we are going to create a new Notebook, click the Notebook drop down menu and create a new notebook that we will name it as “COVID-19 Geo Service”. Select Angular as the interpreter and proceed to add the following code:

%angular

<!DOCTYPE html>

<html>

<head>

<title>Map with GeoJSON Popups</title>

            <link rel="stylesheet"

href="https://unpkg.com/leaflet@1.0.3/dist/leaflet.css" />

<style>

    #map {

                 height: 800px;

  width: 100%;

}

/* Styling for the popup menu */

.popup-menu {

background-color: lightblue;

border: 3px solid red;

padding: 10px;

}

h1 {

text-align: center;

}

</style>

</head>

<body>

<h1>COVID-19 Data by State</h1>

<div id="map"></div>

<!-- The popup menu -->

<div id="popupMenu" class="popup-menu">

<h3 id="stateName"></h3>

<p>Last Update: <span id="lastupdateet"></span></p>

<p>Positive Cases: <span id="positive"></span></p>

<p>Deaths: <span id="death"></span></p>

<p>Hospitalized Increase: <span id="hospitalizedIncrease"></span></p>

<p>Hospitalized Currently: <span id="hospitalizedCurrently"></span></p>

<p>Total Test Results: <span id="totalTestResults"></span></p>

</div>

<script type="text/javascript">

function initMap() {

L.Icon.Default.imagePath = 'https://unpkg.com/leaflet/dist/images/';

var map = L.map('map').setView([41.826540, -87.748143], 8);

L.tileLayer('http://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {

attribution: 'Map data &copy; <a href="http://openstreetmap.org">OpenStreetMap</a> contributors',

maxZoom: 15,

minZoom: 3

}).addTo(map)        ;

let username = 'admin';

let password = 'admin';

let headers = new Headers();

headers.set('Authorization', 'Basic ' + btoa(username + ":" + password));

fetch("http://localhost:8999/geojson/admin/views/covid_tracking_states", {

method: 'GET',

headers: headers

})

.then(response => response.json())

.then(data => {

const namelsadByState = {}; //filter county per state storage

//get only one county per state and filter out all other counites in the state

data.features.forEach(feature => {

const state = feature.properties.state_name;

const namelsad = feature.properties.namelsad; // county name

if (!namelsadByState[state]) { // filter counties for each state

namelsadByState[state] = feature;

} else {

// keep the first county for each state

      }

           });

const uniqueNamelsad = Object.values(namelsadByState); //newly filtered out data containing one county per state

L.geoJSON(uniqueNamelsad, {

onEachFeature: function (feature, layer) {

var popupContent = "<b>Properties:</b><br>";

// properties to display in popup

popupContent += "State: " + feature.properties.state_name + "<br>";

popupContent += "Last Update: " + feature.properties.lastupdateet + "<br>";

popupContent += "Positive: " + numberWithCommas(feature.properties.positive) + "<br>";

popupContent += "Death: " + numberWithCommas(feature.properties.death) + "<br>";

popupContent += "Hospitalized Increase: " + numberWithCommas(feature.properties.hospitalizedincrease) + "<br>";

popupContent += "Hospitalized Currently: " + numberWithCommas(feature.properties.hospitalizedcurrently) + "<br>";

popupContent += "Total Test Results: " + numberWithCommas(feature.properties.totaltestresults) + "<br>";

layer.bindPopup(popupContent);

// Add click event to show the popup menu with additional data

layer.on('click', function () {

document.getElementById("stateName").innerText = feature.properties.state_name;

document.getElementById("lastupdateet").innerText = feature.properties.lastupdateet;

document.getElementById("positive").innerText = numberWithCommas(feature.properties.positive);

document.getElementById("death").innerText = numberWithCommas(feature.properties.death);

document.getElementById("hospitalizedIncrease").innerText = numberWithCommas(feature.properties.hospitalizedi

ncrease);

document.getElementById("hospitalizedCurrently").innerText = numberWithCommas(feature.properties.hospitalized

currently);

document.getElementById("totalTestResults").innerText = numberWithCommas(feature.properties.totaltestresult

s);

// Show popup menu

document.getElementById("popupMenu").style.display = "block";

});

     }

            }).addTo(map);

})

.catch(error => {

console.error("Error fetching GeoJSON data: ", error);

      });

}

// Function to add commas to numeric values

function numberWithCommas(x) {

return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");

}

if (window.L) {

initMap();

} else {

console.log('Loading Leaflet library');

var script = document.createElement('script');

script.type = 'text/javascript';

script.src = 'https://unpkg.com/leaflet/dist/leaflet.js';

script.onload = initMap;

script.onerror = function (err) { alert(err); }

document.getElementsByTagName('head')[0].appendChild(script);

}

    </script>

</body>

</html>

The code sets up a map using the Leaflet library, fetches COVID-19 data from a local server, processes and displays the data on the map as markers. When a marker (state) is clicked, a popup menu shows up with detailed COVID-19 statistics for that state. It is important to note that for this code to work correctly, the local server must serve the correct GeoJSON data at the given endpoint ( http://localhost:8999/geojson/admin/views/covid_tracking_states ). Additionally, Leaflet and Leaflet CSS should be correctly linked in the HTML file.

It provides a detailed breakdown of the code for a Leaflet map that displays COVID-19 statistics by state. The guide covers the HTML and CSS structure, JavaScript functionality, data processing and formatting, and dynamic library loading. In terms of the HTML and CSS structure, the document starts by importing the Leaflet CSS for map styling, and some custom styles are added for the map and popup menu. The main content of the body includes an h1 title and a div for the map. There is also another div for the popup menu that will display additional data when a state on the map is clicked.

The JavaScript code is the core of the functionality and is wrapped in the initMap function. The function starts by initializing the map using Leaflet and setting its view over coordinates (41.826540, -87.748143) with a zoom level of 8. It then sends a GET request to a local server endpoint to fetch GeoJSON data that contains COVID-19 statistics by state. To access the data, a Basic Authentication header is added to the request.

Once the data is fetched, it is processed to ensure only one county per state is displayed. For each unique state, a layer is added to the map. When a state (or county representing the state) is clicked on the map, a popup is displayed with relevant COVID-19 data. Additionally, the popup menu at the bottom of the page is populated with more detailed data. To format the COVID-19 numbers in the popup for better readability, the code also defines a utility function numberWithCommas(x) that takes a numeric value and formats it with commas.

At the end of the script, the code checks if the Leaflet library ( L ) is loaded. If it is not loaded, it dynamically loads the library and initializes the map after the library is loaded.

Additionally, Leaflet and Leaflet CSS should be correctly linked in the HTML file. Once you add the code into the paragraph, you need to hit the run button and let it load for a while. The data takes time to load onto the map.

Results

The image below shows the information that will be presented on the map, which indicates various properties of COVID-19 data. This map is part of an interactive mapping project that provides a comprehensive view of state-by-state COVID-19 data in the United States.

Troubleshooting

GeoJSON is not displaying all the rows in the view

By default, the Denodo Geo Service has a default row limit of 10. In order to change that limit, navigate to the Geo Service configuration file (<GEO_SERVICE_HOME>/conf/application.properties) and edit the file to modify the property default.rowlimit. Enter a number that is enough to retrieve all the rows returned by the queried view. For instance:

default.rowlimit=400

Alternative Method: You can also specify the maximum number of results by using the $count parameter. This option selects the first n entries of the collection, where n is a non-negative integer:

.../geojson/<DBNAME>/views/<VIEWNAME>?$count=<POS_INT_VALUE>

Example:

.../geojson/admin/views/covid_tracking_states?$count=400

Admin access

By default access to the Geo Service with the admin user is disabled. To enable admin access edit the Geo Service configuration file (<GEO_SERVICE_HOME>/conf/application.properties) and edit the file to modify the property enable.adminUser from false to true.

enable.adminUser=true

Also make sure that the properties spring.datasource.username and spring.datasource.password are properly set in this same file for the connection to the Denodo VDP server with a user with proper access to query the view used from the Denodo Geo Service.

References

Denodo Geo Service - User Manual

JSON Sources

Flatten views in Denodo

Docker Image for Apache Zeppelin

Disclaimer
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here