Data integration in GIS and ETL processes

Prepare and Integrate Data Spatially

How do you display your address, contact, location and other data on a digital map?

The data is extracted (E) from its original source, such as an Excel spreadsheet, by a so-called ETL process, then it is transformed (T) into a new format that can be displayed on a map and finally it is loaded (L) into a GIS, for example WebGIS or QGIS.

The data in the lists or files, which are available in different formats, are prepared spatially so that they can be displayed visually on an interactive map in the GIS. WIGeoGIS takes care of this data integration for you.

Image: Data integration processes make the data, which mostly comes from different sources and systems, “GIS-compatible”.

Depending on the specific starting point and the goal of the data analysis in GIS, this process requires other intermediate steps. The address data often has to first be 

• geocoded,
• aggregated into larger units,
• or conversely, disaggregated into smaller units,
• generalized and
• enriched with additional information.

At the end of this process, you get much more value out of your data: You can use our GIS software to display the visualized and linked data on a digital map so it can be

• evaluated,
• analyzed and
• used to make operational decisions.

In this way, you will be able to make future decisions on a solid, data-based foundation.

What is the point of aggregating data into larger units?

A common way of spatially preparing data is aggregation. This combines several data points to form a larger unit.

A typical example is aggregating sales from ZIP Code to state or district level or from state to country level. In this way, the GIS shows you at a glance how many sales were made in a specific district, even though the original lists only contained sales per ZIP Code.

“Aggregation is the process of combining multiple small data points in order to draw information from them. For example, I have customer points and I know the sales that each customer has generated. When aggregating, we summarize the customers per municipality so that statements can be made about the sales in the municipalities. The sales in the municipalities can then also be compared with one another. This is just one example of data aggregation. Of course, municipalities can also be aggregated into states in order to make a statement about the sales in the states.”
Andreas Marth, Head of Solutions, WIGeoGIS

Aggregation is often about sales figures. But also the potential in different regions can be combined into larger regions such as states, so that the interactive map shows which states have the highest potential.

Image: A common method of data integration is spatial aggregation. Points such as customers, inhabitants, etc. are aggregated into areas and the aggregated values are made visible with colors.

When is data disaggregation needed?

Disaggregation is the reverse of aggregation. For example, you only have a certain value at a state level, but all the other values are at a community level, and you want to make statements about communities.

To make this possible, we use the method of disaggregation.

“A disaggregation is usually done when data in finer granularity is either not available across the board or is very expensive.”
Josef Mayer, Head of Solutions, WIGeoGIS

Example: Although I know how many car registrations there are per district, in the GIS I need this number one level down at the ZIP Code level. If 100,000 cars were registered in a county with ten ZIP codes, I cannot assume that 10,000 cars were registered in each ZIP Code area. That would be too simplistic and would not reflect reality. We therefore use various keys and auxiliary variables, for example the number of inhabitants, the built-up area or the number of residential buildings in the individual ZIP Code areas. We then distribute the car registrations to the ZIP Codes according to these keys.

Important: A disaggregation is always just an approximation of reality. However, it is often better to work with probable values than to omit certain data altogether.

Because disaggregation often involves combining data from spatial units with different resolutions, a reliable calculation is only possible at the spatial level. If you only have the corresponding numbers and data as a table or list, you can only use the method to a very limited extent, or you first have to geocode these numbers and put them on a digital map.

Many WIGeoGIS customers, including Claas and Stihl, already benefit from the disaggregation method. WIGeoGIS also disaggregates data for Fressnapf:

“We perform a disaggregation of the potential data in different countries with different data accuracies and calculate customer-specific potentials on a 500 x 500 meter grid. This gives us a uniform database across countries and we can perform our analyses using the same scheme, with the same data quality and in a way that is comparable across countries.”
Giuseppe Mustillo, Head of International Expansion Analytics and Controlling, Fressnapf Immobilien- und Vermögensverwaltungs GmbH

Automating ETL Processes

ETL processes, in other words, extracting, transforming and loading the data to the GIS, are usually automated, so that a change in your database automatically appears on the interactive map. At regular intervals, for example, once a week, once a day or every minute, the GIS retrieves the data from your database, for example from various Excel lists and other files. This way you do not have to re-upload the list yourself every time you make a change to it and your WebGIS or QGIS is always up to date.

“By automating the ETL process, the customer does not have to worry about whether or when they need to update the data in the GIS.”
Andreas Marth, Head of Solutions, WIGeoGIS

Image: Data from different sources and systems can be combined using ETL processes and made “GIS-compatible”.

Especially in corporations and larger companies, it is usually not easy to collect the data from the different data pools and to ensure that they are regularly updated in order to use them for geographical analyses. Automated data integration helps here.

A WIGeoGIS customer who already benefits from the advantages of the automated ETL process is the Austrian Post:

“With the help of WIGeoGIS, we have created a location planning tool that brings together the relevant information from various internal and external data sources on one platform. Automated processes for data preparation and analysis are carried out in the background to ensure that decisions are always based on the latest data and up-to-date knowledge.”
Simone Ortner, Österreichische Post AG

Many users benefit from automated ETL processes. This automates “Excel to Map and Report” workflows. Contents from the Excel spreadsheets are always up-to-date in map reports. This saves a lot of time compared to a manual and non-automated analysis.

Why is enriched data more valuable?

Everything that is enriched gains in value. Data too is more valuable when it is enriched. As a rule, data is enriched with the following information:

• Internal company information
• Geographic information
• Demographic information or
• A combination of the above characteristics

“As soon as I put an address on the map, I can enrich it with all the information that I have in the GIS from other sources.”
Josef Mayer, Head of Solutions, WIGeoGIS

Image: For location analyses, companies enrich locations with relevant information about the location environment. Competitors, inhabitants, purchasing power and more are determined for each catchment area. This is important in order to standardize and objectify corporate decisions.

Some examples of data enrichment

• You add coordinates to the customer data record and see on the digital map with one click which customers are 20 minutes away from a location.
• You enrich customer data records with the information about which sales representatives they are assigned to and see which employees look after how many customers, where they are and how much they ordered in the last quarter on the interactive map.
• You enrich geographical data with spatial characteristics, for example with the information that they are located in a tourist or industrial area or on a main road.
• Geo-data can also be enriched with information on purchasing power, age structure and employment rate, the percentage of university graduates or children in a region. For example, the percentage of babies allows conclusions to be drawn about the need for diapers.

Many WIGeoGIS customers benefit from data enrichment.