The Bridge Between Business Intelligence and GIS: Why It's a Game-Changer for Your Business
The volume of data companies possess and generate has expanded exponentially. Institutions endeavor to harness this data for their benefit. However, converting this data into actionable wisdom and knowledge requires proficiency in the appropriate methods. This is where your data analyst team typically proves invaluable. Yet, many fail to recognize that 80% of all data possesses some form of spatial definition. While not immediately apparent, sooner or later, someone will pose a simple question to the data analyst team – Where?
The vital role of Spatial Data in Business Data Analysis
For GIS (Geographic Information System) specialists, addressing the question of Where, is a daily occurrence and a fundamental aspect of their role. The significant contribution that a GIS analyst brings to the analytics team often lies in their approach to answering this question. Unlike traditional data analysts, GIS specialists frequently communicate their findings through maps or data visualizations. This sets GIS apart from conventional BI solutions, which typically present data solely in tables or basic charts. Maps offer a more intuitive representation of spatial patterns within the data, often revealing insights that might otherwise remain hidden within a flat table format.
Stop investing time in SQL queries for a single answer
For a GIS specialist, a common task might be framed as follows: "Can you create a map visualization pinpointing areas with the highest frequency of traffic accidents?" Ideally, the task includes specifications on the preferred visualization type and additional details about the desired outcome. However, as reality often proves, we don't inhabit an ideal world. These decisions fall on the shoulders of a specialist who must then process the data and devise a suitable visualization.
Naturally, displaying ten thousand points on a map isn't practical. Hence, the specialist opts to aggregate the data into a specific administrative unit, such as zip codes.
The SQL query to get the desired data could look like this:
SELECT zipcodes.id, zipcodes.name, COUNT(accidents.id) AS accidents
FROM accidents
LEFT JOIN zipcodes
ON ST_Intersects(zipcodes.geometry, accidents.geometry)
GROUP BY zipcodes.id, zipcodes.name;
The visualization of the data can look like this:
This task alone can be time-consuming. However, the challenge for GIS specialists often intensifies once the initial visualization is presented, as it frequently triggers a cascade of thoughts and subsequent inquiries. Questions arise: What are the primary causes of these accidents? How do accidents aggregate along specific roads? Are certain roads more problematic than others? Do particular locations experience higher accident frequencies? Furthermore, what if we focus solely on accidents involving trucks or those occurring at night? The possibilities for inquiry, given available spatial data, seem nearly endless. For GIS specialists, each question necessitates a tailored SQL query, exemplified by:
SELECT roads.id, roads.class, COUNT(accidents.id) AS accidents
FROM accidents
LEFT JOIN roads
ON ST_Closest(roads.geometry, accidents.geometry)
GROUP BY roads.id, roads.class;
SELECT zipcodes.id, zipcodes.name, COUNT(accidents.id) AS accidents
FROM accidents
LEFT JOIN zipcodes
ON ST_Intersects(zipcodes.geometry, accidents.geometry)
WHERE accidents.daytime = ‘night’
GROUP BY zipcodes.id, zipcodes.name;
SELECT zipcodes.id, zipcodes.name, COUNT(accidents.id) AS accidents
FROM accidents
LEFT JOIN zipcodes
ON ST_Intersects(zipcodes.geometry, accidents.geometry)
WHERE accidents.car_involved IN (‘truck’, ‘bus’)
GROUP BY zipcodes.id, zipcodes.name;
And then, of course, creating a specific visualization, like this:
Upgrade Your Business Intelligence Approach
Noticeably, each resulting query consistently includes the accident count; only the spatial phenomenon to which the accidents are aggregated (such as roads or administrative units) and the accident circumstances vary. The primary issue arises when decision-makers require new insights, prompting a return to GIS specialists for spatial analysis. Consequently, many business analysts opt for BI tools like Tableau, which allow data slicing without the need for GIS expertise. However, this approach comes with a drawback: the loss of crucial spatial insights originally sought. Thus, the ideal solution lies in integrating the capabilities of GIS and BI tools. Fortunately, this integration is precisely what Location Intelligence tools like CleverMaps offer.
Functions from a regular BI tool combined with a web map
CleverMaps adopts a traditional data model approach, linking tables through foreign and primary keys. This stands in contrast to GIS, where data must be aggregated for specific queries, requiring a new query for each question posed. Whether your data resides in tables within a database or files in a folder structure, SQL queries are essential for joining tables to obtain answers.
In CleverMaps, the process diverges. As a data analyst, you prepare a table containing traffic accident data and specify the foreign keys for later connections with relevant data sources, such as administrative units, road numbers, or dates. Once your table is prepared, it can be uploaded to CleverMaps and linked with other tables using their primary and foreign keys, establishing a logical data model visually represented as follows.
The primary advantage of this approach is that once the data model is established, there's no need for frequent table joins to obtain new information or filter specific types of roads or accidents. Instead, you create what's known as a "metric." For instance, if the desired result is a count of accidents, the metrics would resemble the following:
{
"name": "accidents_metric",
"type": "metric",
"content": {
"type": "function_count",
"content": [
{
"type": "property",
"value": "accidents.id"
}
]
}
}
How to visualize data without a GIS Specialist or Data Analyst
From this point onward, the role of a GIS specialist or data analyst is fulfilled. Why? Because all the desired answers are just a few clicks away. Should anyone wish to view accidents aggregated according to a different spatial phenomenon instead of administrative units, they can simply switch the visualization type at the top of the screen.
You can choose from various visualization options such as heatmap, line representation, polygon representation, or original points of the accidents. Additionally, you have the flexibility to aggregate your spatial data into a hexagonal grid and adjust the grid size according to your preferences.
Endless possibilities for metric creation
Another major benefit of merging BI with GIS is the ability to accompany maps with multiple metrics and indicators, not just one. For instance, the total sum of damage resulting from accidents may be the most crucial indicator for decision-making. Creating similar metrics and utilizing a SUM function to display total damage is easily achievable. Given that the accidents table is already linked to administrative units and road segments, visualizations are readily available. The potential for metric creation is virtually limitless, as you can combine columns from any table or even different tables to analyze various relationships, such as between the number of accidents and the number of inhabitants in a given area.
Visualize Data without GIS or BI Expertise. Explore CleverMaps Studio, and book a demo with us.
