Visualizing Geographic SQL Data on Google Maps

Analytics dashboards display different data visualizations to represent and convey data in ways that allow users to quickly digest and analyze information. Most multivariate datasets consumed by dashboards include a spatial field/s, such as an observation's set of coordinates (latitude and longitude). Plotting this data on a map visualization contextualizes the data within a real-world setting and sheds light on spatial patterns that would otherwise be hidden in the data. Particularly, seeing the distribution of your data across an area connects it to geographical features and area-specific data (i.e., neighborhood/community demographics) available from open data portals. The earliest example of this is the 1854 cholera visualization by John Snow , who marked cholera cases on a map of London's Soho and uncovered the source of the cholera outbreak by noticing a cluster of cases around a water pump. This discovery helped to correctly identify cholera as a waterborne disease and not as an airbourne disease. Ultimately, it changed how we think about disease transmission and the impact our surroundings and environment have on our health. If your data consists of spatial field/s, then you too can apply the simple technique of plotting markers on a map to extrapolate valuable insight from your own data. Map visualizations are eye-catching and take on many forms: heatmaps, choropleth maps, flow maps, spider maps, etc. Although colorful and aesthetically pleasing, these visualizations provide intuitive controls for users to navigate through their data with little effort. To create a map visualization, many popular libraries (e.g., Google Maps API and deck.gl ) support drawing shapes, adding markers and overlaying geospatial visualization layers on top of a set of base map tiles. Each layer generates a pre-defined visualization based on a collection of data. It associates each data point with certain attributes (color, size, etc.) and renders them on to a map. By pairing a map visualization library with React.js, developers can build dynamic map visualizations and embed them into an analytics dashboard. If the visualizations' data comes from a PostgreSQL database, then we can make use of PostGIS geospatial functions to help answer interesting questions related to spatial relationships, such as which data points lie within a 1 km. radius of a specific set of coordinates. Below, I'm going to show you how to visualize geographic data queried from a PostgreSQL database on Google Maps. This tutorial will involve React.js and the @react-google-maps/api library, which contains React.js bindings and hooks to the Google Maps API, to create a map visualization that shows the location of data points. To get started, clone the following two repositories: The first repository contains a Create React App with TypeScript client-side application that displays a query builder for composing and sending queries and a table for presenting the fetched data. The second repository contains a multi-container Docker application that consists of an Express.js API, a PostgreSQL database and pgAdmin. The Express.js API connects to the PostgreSQL database, which contains a single table named cp_squirrels seeded with 2018 Central Park Squirrel Census data from the NYC Open Data portal. Each record in this dataset represents a sighting of an eastern gray squirrel in New York City's Central Park in the year 2018. When a request is sent to the API endpoint POST /api/records , the API processes the query attached as the body of the request and constructs a SQL statement from it. The pg client executes the SQL statement against the PostgreSQL database, and the API sends back the result in the response. Once it receives this response, the client renders the data to the table. To run the client-side application, execute the following commands within the root of the project's directory: Inside of your browser, visit this application at http://localhost:3000/ . Before running the server-side application, add a .env.development file with the following environment variables within the root of the project's directory: ( .env.development ) To run the server-side application, execute the following commands within the root of the project's directory: Currently, the client-side application only displays the data within a table. For it to display the data within a map visualization, we will need to install several NPM packages: The Google Maps API requires an API key, which tracks your map usage. It provides a free quota of Google Map queries, but once you exceed the quota, you will be billed for the excessive usage. Without a valid API key, Google Maps fails to load: The process of generating an API key involves a good number of steps, but it should be straight-forward. First, navigate to your Google Cloud dashboard and create a new project. Let's name the project "react-google-maps-sql-viz." Once the project is created, select this project as the current project in the notifications pop-up. This reloads the dashboard with this project now selected as the current project. Now click on the "+ Enable APIs and Services" button. Within the API library page, click on the "Maps JavaScript API" option. Enable the Maps JavaScript API. Once enabled, the dashboard redirects you to the metrics page of the Maps JavaScript API. Click the "Credentials" option in the left sidebar. Within the "Credentials" page, click the "Credentials in APIs & Services" link. Because this is a new project, there should be zero credentials listed. Click the "+ Create Credentials" button, and within the pop-up dropdown, click the "API key" option. This will generate an API key with default settings. Copy the API key to your clipboard and close the modal. Click on the pencil icon to rename the API key and restrict it to our client-side application. Rename API key to "Google Maps API Key - Development." This key will be reserved for local development and usage metrics recorded during local development will be tied to this single key. Under the "Application Restrictions" section, select the "HTTP referrers (web sites)" option. Below, the "Website restrictions" section appears. Click the "Add an Item" button and enter the referrer " http://localhost:3000/* " as a new item. This ensures our API key can only be used by applications running on http://localhost:3000/ . This key will be invalid for other applications. Finally, under the "API Restrictions" -> "Restrict Key" section, select the "Maps JavaScript API" option in the <select /> element for this key to only allow access to the Google Maps API. All other APIs are off limits. After you finish making these changes, press the "Save" button. Note: Press the "Regenerate Key" button if the API key is compromised or accidentally leaked in a public repository, etc. The dashboard redirects you back to the "API & Services" page, which now displays the updated API key information. Also, don't forget to enable billing! Otherwise, the map tiles fail to load: When you create a billing account and link the project to the billing account, you must provide a valid credit/debit card. When running the client-side application in different environments, each environment supplies a different set of environment variables to the application. For example, if you decide to deploy this client-side application live to production, then you would provide a different API key than the one used for local development. The API key used for local development comes with its own set of restrictions, such as only being valid for applications running on http://localhost:3000/ , and collects metrics specific to local development. For local development, let's create a .env file at the root of the client-side application's project directory. For environment variables to be accessible by Create React App, they must be prefixed with REACT_APP . Therefore, let's name the API key's environment variable REACT_APP_GOOGLE_MAPS_API_KEY , and set it to the API key copied to the clipboard. Let's start off by adding a map to our client-side application. First, import the following components and hooks from the @react-google-maps/api library: ( src/App.tsx ) Let's destructure out the API key's environment variable from process.env : ( src/App.tsx ) Establish where the map will center. Because our dataset focuses on squirrels within New York City's Central Park, let's center the map at Central Park. We will be adding a marker labeled "Central Park" at this location. ( src/App.tsx ) Within the <App /> functional component, let's declare a state variable that will hold an instance of our map in-memory. For now, it will be unused. ( src/App.tsx ) Call the useJsApiLoader hook with the API key and an ID that's set as an attribute of the Google Maps API <script /> tag. Once the API has loaded, isLoaded will be set to true , and we can then render the <GoogleMap /> component. ( src/App.tsx ) Currently, TypeScript doesn't know what the type of our environment variable is. TypeScript expects the googleMapsApiKey option to be set to a string, but it has no idea if the REACT_APP_GOOGLE_MAPS_API_KEY environment variable is a string or not. Under the NodeJS namespace, define the type of this environment variable as a string within the ProcessEnv interface. ( src/react-app-env.d.ts ) Beneath the table, render the map. Only render the map once the Google Maps API has finished loading. Pass the following props to the <GoogleMap /> component: Here, we set the center of the map to Central Park and set the zoom level to 14. Within the map, add a marker at Central Park, which will physically mark the center of the map. ( src/App.tsx ) The onLoad function will set the map instance in state while the onUnmount function will wipe the map instance from state. ( src/App.tsx ) Altogether, here's how your src/App.tsx should look after making the above modifications. ( src/App.tsx ) Within your browser, visit the application at http://localhost:3000/ . When the application loads, a map is rendered below the empty table. At the center of this map is marker, and when you hover over this marker, the mouseover text shown will be "Central Park." Suppose we send a query requesting for all squirrel observations that involved a squirrel with gray colored fur. When we display these observations as rows within a table, answering questions like "Which section of Central Park had the most observations of squirrels with gray colored fur?" becomes difficult. However, if we populate the map with markers of these observations, then answering this question becomes easy because we will be able to see where the markers are located and identify clusters of markers. First, let's import the <InfoWindow /> component from the @react-google-maps/api library. Each <Marker /> component will have an InfoWindow, which displays content in a pop-up window (in this case, it acts as a marker's tooltip), and it will only be shown only when the user clicks on a marker. ( src/App.tsx ) Since each observation ("record") will be rendered as a marker within the map, let's add a Record interface that defines the shape of the data representing these observations mapped to <Marker /> components. ( src/App.tsx ) We only want one InfoWindow to be opened at any given time. Therefore, we will need a state variable to store an ID of the currently opened InfoWindow. ( src/App.tsx ) Map each observation to a <Marker /> component. Each <Marker /> component has a corresponding <InfoWindow /> component. When a marker is clicked on by the user, the marker's corresponding InfoWindow appears with information about the color of the squirrel's fur for that single observation. Since every observation has a unique ID, only one InfoWindow will be shown at any given time. ( src/App.tsx ) Altogether, here's how your src/App.tsx should look after making the above modifications. ( src/App.tsx ) Within the query builder, add a new rule by clicking the "+Rule" button. Set this rule's field to "Primary Fur Color" and enter "Gray" into the value editor. Keep the operator as the default "=" sign. When this query is sent to the Express.js API's POST /api/records endpoint, it produces the condition primary_fur_color = 'Gray' for the SQL statement's WHERE clause and will fetch all of the observations involving squirrels with gray-colored fur. Press the "Send Query" button. Due to the high number of records returned by the API in the response, the browser may freeze temporarily to render all the rows in the table and markers in the map. Once the browser finishes rendering these items, notice how there are many markers on the map and no discernable spatial patterns in the observations. Yike! For large datasets, rendering a marker for each individual observation causes massive performance issues. To avoid these issues, let's make several adjustments: Define a limit on the number of rows that can be added to the table. ( src/App.tsx ) Add a state variable to track the number of rows displayed in the table. Initialize it to five rows. ( src/App.tsx ) Anytime new data is fetched from the API as a result of a new query, reset the number of rows displayed in the table back to five rows. ( src/App.tsx ) Using the slice method, we can limit the number of rows displayed in the table. It is increased by five each time the user clicks the "Load 5 More Records" button. This button disappears once all of the rows are displayed. ( src/App.tsx ) To render a heatmap layer, import the <HeatmapLayer /> component and tell the Google Maps API to load the visualization library . For the libraries option to be set to LIBRARIES , TypeScript must be reassured that LIBRARIES will only contain specific library names. Therefore, import the Libraries type from @react-google-maps/api/dist/utils/make-load-script-url and annotate LIBRARIES with this type. ( src/App.tsx ) ( src/App.tsx ) ( src/App.tsx ) ( src/App.tsx ) Pass a list of the observations' coordinate points to the <HeatmapLayer /> component's data prop. ( src/App.tsx ) Altogether, here's how your src/App.tsx should look after making the above modifications. ( src/App.tsx ) Save the changes and re-enter the same query into the query builder. Now the table displays information only the first five observations of the fetched data, and the heatmap visualization clearly distinguishes the areas with no observations and the areas with many observations. Click here for the final version of this project. Click here for the final version of this project styled with Tailwind CSS . Try visualizing the data with other Google Maps layers.

Thumbnail Image of Tutorial Visualizing Geographic SQL Data on Google Maps