DEV Community: Jérôme Urbain

Comparison of Popular Enterprise Master Data Management Tools (MDMs)

Jérôme Urbain — Mon, 27 Feb 2023 14:55:52 +0000

You can think of Master Data Management (MDM) as a set of processes and technologies for managing and maintaining a single, consistent version of the data used across an entire organization. These tools ensure that all the data used by various units within the organization is accurate, consistent, and up to date.

MDM tools make it easy to access and analyze data from different sources efficiently while keeping things organized. They also allow for easy reporting to facilitate accurate decision-making and data-driven insights. This central source of master data enables organizations to easily integrate new data sources without losing data consistency and accuracy, thus enforcing data privacy, security, and compliance with data regulations.

That said, choosing an MDM tool is not an easy task. Let’s compare the most popular enterprise MDM tools on the market so you can make an informed decision based on each tool's features, pros, and cons.

The factors we’ll consider to compare the tools are:

Data governance compliance
Cloud support (public, private, hybrid)
Ability to process big data
AI and machine Learning for data enrichment
Data discovery and profiling
Data quality management
Integration
Licensing and pricing

Let's start with the best enterprise MDM tools available today.

IBM InfoSphere Master Data Management

IBM InfoSphere Master Data Management is a comprehensive MDM software solution that helps organizations manage their critical data assets. It provides powerful tools for data governance, cloud support, big data processing, AI and machine learning, data discovery and profiling, data quality management, and integration.

Some of the features and strengths of IBM InfoSphere Master Data Management include:

Data governance compliance: IBM InfoSphere Master Data Management has a comprehensive suite of data governance and security tools to ensure compliance with industry regulations. These include data access control, data masking and encryption, data audit and activity tracking, and data lineage and impact analysis.
Cloud support: InfoSphere MDM has native support for deployment on IBM SoftLayer global cloud infrastructure, on-premise servers, Docker containers, Red Hat OpenShift Container Platform, and vanilla Kubernetes. The platform also supports multi-cloud data integration.
Ability to process big data: Data management capabilities are integrated into the platform, allowing businesses to efficiently process and analyze large datasets.
AI and machine learning for data enrichment: IBM InfoSphere MDM in particular Cloud Pak for Data Edition offers a range of machine learning-based tools for data enrichment, including natural language processing, sentiment analysis, and predictive analytics.
Data discovery and profiling: With its data discovery and profiling capabilities, users can quickly uncover insights from their data and better understand patterns and trends.
Data quality management: The platform provides tools for detecting and correcting errors and inconsistencies in data, as well as monitoring data quality over time.

Some drawbacks of IBM InfoSphere Master Data Management include:

Integration: Unsurprisingly, InfoSphere tends to integrate better with other products of the IBM ecosystem, such as WebSphere Application Server, Message Queue, IBM Cloud Pak for Data, and Db2 PureScale.
Licensing and pricing: According to their website, “IBM InfoSphere Master Data Management is available on IBM Cloud Pak for Data, on-premises and as a cloud-managed offering in different sizes.” This licensing model can make it challenging to choose the ideal alternative. Furthermore, the deployment on IBM Cloud Pak for Data and on-premises has two editions, standard and advanced. As for Master Data Management on Cloud, Managed, there are three price tiers available: managed small, managed medium, and managed large. All in all, both the pricing and licensing models are clearly aimed at corporations and large businesses rather than small and medium-sized businesses.

Overall, InfoSphere is an excellent choice for large enterprises and corporations looking for an MDM solution with tight integration with the rest of the IBM ecosystem that provides robust data governance, AI and machine learning, discovery and creation of data profiling, and data quality management. But IBM InfoSphere Master Data Management can be expensive for small and medium-sized businesses. It may not be the ideal solution for organizations that avoid vendor lock-in at all costs.

Informatica Master Data Management

Informatica Master Data Management is a powerful MDM solution that allows organizations to manage, integrate, and govern their data centrally. It enables users to accurately identify, cleanse, and link related data across multiple systems, making operations more efficient and enabling better decision-making.

Some of the features and strengths of Informatica Multidomain MDM include:

Data governance compliance: Informatica Multidomain MDM is designed to meet regulatory requirements around data governance and provides strong data quality and integration capabilities that ensure compliance with data governance standards.
Ability to process big data: With Informatica, businesses can build automated data pipelines for AI and advanced analytics thanks to the CLAIRE engine.
AI and machine learning for data enrichment: Informatica Multidomain MDM can use AI and machine learning for data enrichment and to help identify areas of potential data quality issues.
Data discovery and profiling: Informatica Multidomain MDM has powerful data discovery and profiling tools that can help identify potential data issues and provide insight into data quality.
Data quality management: The platform offers robust data quality management capabilities that can help identify and address potential data quality issues, as well as improve the overall quality of data.
Integration: Thanks to Informatica’s extensive Cloud Connectivity library, your business can get real-time insights from cloud apps like Salesforce, Marketo, snowflake, and more.
Licensing and pricing: Informatica uses convenient consumption-based pricing called Informatica Processing Unit (IPU) pricing. This pricing model allows organizations to dynamically scale up/down Informatica's Intelligent Cloud Services depending on their needs and thus optimize their IT budget.

Some drawbacks of Informatica Multidomain MDM include:

Cloud support: While Informatica MDM supports data from public, private, and hybrid clouds, the platform itself is a cloud-native SaaS solution. That’s a drawback for organizations looking to deploy the MDM on-premises or in their own cloud.

On the other hand, its flexible pricing model and its comprehensive data governance, data processing, and integration capabilities make Informatica an excellent MDM solution for small to large businesses looking for an all-in-one SaaS solution.

SAP Master Data Governance

SAP Master Data Governance is an excellent solution for managing corporate master data securely. It allows organizations to easily monitor and manage their data structures in the cloud and on-premises. The platform provides robust data governance features, such as compliance with GDPR and other global data regulations, data discovery and profiling, data quality management, and AI and machine learning for data enrichment. SAP MDG supports on-premises deployments as well as public, private, and hybrid clouds for data integration and sharing.

Some of the features and strengths of SAP Master Data Governance include:

Data governance c\ompliance: SAP Master Data Governance excels at data governance compliance by providing a framework to ensure data is appropriately managed and monitored to meet regulatory requirements. It also offers automated workflows and tasks to ensure that data needed for regulatory compliance is accurately entered within the system.
Cloud support: SAP Master Data Governance supports data from public, private, and hybrid cloud sources. It also offers a variety of deployment options for data sharing and data integration, including on-premise and cloud. This allows customers to choose the best deployment option for their specific needs.
AI and machine learning for data enrichment: SAP Master Data Governance provides advanced AI and machine learning capabilities for enriching data. It can use natural language processing and machine learning algorithms to identify patterns and relationships in data, enabling users to gain deeper insights into their data.
Data quality management: SAP Master Data Governance offers comprehensive data quality management capabilities. It provides automated checks and validation to ensure that data is accurate, complete, and up to date.

Some drawbacks of SAP Master Data Governance include:

Ability to process big data: Even though SAP MDG can perform changes in large volumes of data in real time, it’s not optimized to process big data.
Data discovery and profiling: SAP Master Data Governance’s data discovery and profiling capabilities are not as robust as those of some of its enterprise-grade competitors (such as IBM InfoSphere Master Data Management).
Integration: SAP Master Data Governance does not offer comprehensive third-party integration capabilities, leaving customers to build their own custom integration solutions. That said, the platform integrates seamlessly with other SAP applications, such as SAP ECC, S/4HANA, and Business Warehouse. This lets customers quickly and easily synchronize and integrate their master data across other SAP enterprise apps.
Licensing and pricing: The platform has two editions: SAP Master Data Governance on SAP S/4HANA (Private Cloud Edition or on-premise) aimed at large companies and corporations looking for a centralized enterprise-wide MDM solution; and SAP Master Data Governance, cloud edition which offers a somewhat lower entry barrier thanks to a modular MDM approach. However, only the first edition offers all the data domains, data models, and features, and it’s not an option for companies with a low budget.

It’s pretty clear that SAP Master Data Governance is a product aimed at large companies that prefer a centralized platform focused on data governance and based on the robust SAP ecosystem.

Pimcore Open Source Master Data Management

Pimcore Master Data Management is an impressive open-source platform that offers a comprehensive suite of data management capabilities. It’s a great tool for organizations that need to manage large volumes of data, as it enables them to ensure data governance compliance, integrate with multiple systems, and manage data quality.

As far as compliance goes, Pimcore allows users to create custom data governance policies and audit trails, ensuring that all data is properly managed and secured.

Some of the features and strengths of Pimcore Master Data Management include:

Cloud support: Pimcore supports public, private, and hybrid cloud deployments, allowing for greater flexibility and scalability. It also has APIs for integration with cloud storage solutions. Furthermore, thanks to a partnership with Bitnami, your team can install Pimcore using public cloud images for AWS, Google Cloud, Microsoft Azure, or Oracle Cloud Platform.
AI and machine learning for data enrichment: Pimcore comes with a flexible and powerful AI and machine learning platform, allowing users to create sophisticated models and algorithms for data enrichment.
Data quality management: Pimcore provides a comprehensive set of tools for data quality management, including data profiling, data cleansing, data versioning, and data auditing.
Integration: Pimcore supports integration with a wide range of data sources, including databases, messaging systems, and file systems. It also has an open API for integration with third-party applications.
Licensing and pricing: Pimcore offers three editions, the free and open-source Community Edition, the subscription-based software-as-a-service Cloud Edition, and the on-premises Enterprise Edition, making it the winner of this showdown in terms of licensing and pricing flexibility.

Some drawbacks of Pimcore Master Data Management include:

Data governance compliance: While Pimcore supports almost any kind of data, be it unstructured or structured, one of its major drawbacks is its limited out-of-the-box support for data privacy regulations and data governance compliance.
Ability to process big data: While Pimcore supports data ingestion from a wide range of sources, it doesn't currently support big data processing natively.

In short, Pimcore is an ideal choice for small and medium-sized businesses looking for an affordable open-source MDM solution that doesn't lock them into a specific vendor and offers the convenience of using it as Product Information Management (PIM), Digital Asset Management (DAM), or Digital Commerce Platform.

Ataccama ONE

Ataccama ONE is a powerful data analytics platform that offers an impressive range of features and capabilities. It provides an excellent level of data governance compliance, with robust security measures to ensure that data is protected and organizations are meeting all their data governance requirements. Ataccama ONE offers support for public, private, and hybrid cloud storage, making it an ideal solution for organizations of all sizes.

Some of the features and strengths of Ataccama ONE include:

Data governance compliance: The platforms provide robust tools for data governance and privacy, including a data catalog, data lineage, data profiling, metadata management, and data stewardship. These capabilities make it easy to comply with data governance regulations and best practices.
Cloud support: Ataccama ONE supports two deployment options: Platform as a Service (PaaS) and on-premise and hybrid. The PaaS deployment runs on Amazon AWS and Microsoft Azure, while the on-premise and hybrid deployment runs on Azure, AWS, Google Cloud, and Linux. Also, organizations can manage data from multiple cloud sources, be it public, private, or hybrid.
Big data processing: ONE offers built-in capabilities for harnessing big data. It features an integrated data lake, a scalable data processing engine, and a data integration framework.
AI and machine learning: Ataccama ONE provides comprehensive machine learning and AI capabilities, including a self-service data enrichment platform, a machine learning library, and an AI-driven data discovery engine.
Data discovery and profiling: Ataccama ONE features a strong data discovery and profiling engine that can quickly find, profile, and analyze data from multiple sources.
Data quality management: Data quality management capabilities include AI-driven anomaly detection, data cleansing, data validation, data standardization, and data enrichment tools.

Some drawbacks of Ataccama ONE include:

Integration: Ataccama ONE offers a comprehensive data integration framework that supports data ingestion and replication from multiple sources. However, you should keep in mind that ONE is a fully integrated platform, so if your organization avoids vendor lock-in, it may not be the ideal solution.
Licensing and pricing: ONE licensing model can be complex and difficult to understand due to its modular nature. This, in turn, makes it difficult to compare its price with that of the competition since aspects such as the type of deployment and the contracted modules come into play.

All in all, Ataccama ONE is an excellent choice for organizations with a strong focus on advanced data analytics and big data processing. In fact, the platform markets itself as being specifically tailored for data analysts, data engineers, data scientists, and data stewards.

Conclusion

The most popular Master Data Management tools all have their own methods of handling data governance compliance, cloud support, big data, AI and machine learning, and so on.

MDM Tool	Strengths	Drawbacks
IBM InfoSphere	Comprehensive data compliance	Works best with other IBM products
Informatica	Extensive library for third-party integrations	SaaS, so not possible to deploy on-premises
SAP Master Data Governance	Excels at data compliance	Not an option for a low budget
Pimcore Open Source	The clear winner in pricing flexibility	Limited out-of-the-box compliance support
Ataccama ONE	A fully integrated platform designed especially for data scientists and data engineers	Not for those avoiding vendor lock-in

But obviously, each is tailored to certain needs; IBM InfoSphere Master Data Management and SAP Master Data Governance have proven more suitable for large companies, while Informatica Multidomain MDM, Pimcore Open Source Master Data Management, and Ataccama ONE are excellent alternatives for small to medium-sized companies looking for more affordable solutions.

GeoPostcodes is an example of one of the data sources you’ll probably use to improve your master data quality. A common data type that most companies use to inform their logistics, sales, and marketing endeavors is postal data—think zip codes, international postcodes, streets and boundaries. GeoPostcodes offers the most comprehensive enterprise postal code database in the world. Implement it easily with your systems of choice thanks to its ERP connector.

The Quickest Way to Build an Address Autocomplete API

Jérôme Urbain — Mon, 27 Feb 2023 14:21:30 +0000

An address autocomplete is an address form feature that’s kind of just what it says on the tin: it automatically suggests addresses to users as they type into an online form. It’s an elegant way to ensure accuracy as a user inputs all the components of a complete address, like country, city, postal code, and street.

But building a form to capture an address with auto-complete and validation can be quite a challenge. What should the form ask for first: the postal code or the region? Which data input should use auto-complete? Which reliable data source should be used? How to optimize UI responsiveness?

In this article you will learn all that’s required to build a backend API for address completion.

The technicalities of building an API backend and frontend are beyond the scope of this article. They’re too dependent on your chosen programming language. As a personal recommendation, I favor React with Material UI for the frontend and Node.js with Express for the backend. But you will need some solid technical knowledge about building an API backend and frontend before you read on.

What Does an Address Autocomplete Feature Actually Look Like?

To expound on my earlier definition, an address autocomplete feature generates suggestions based on previously entered information as the user enters data into the form. The user can easily choose from a list of options, ensuring the accuracy of the input address. This is especially useful when someone needs to enter a long or complex address or when the user isn’t familiar with a specific address format.

Why Is Address Autocomplete Important?

An efficient address autocompletion form on your website prevents you from ingesting inaccurate data, like typos or nonexistent addresses. Unless you allow free entry, you could also lose sign-up or orders. But allowing that will even worsen the data quality.

Ensuring data quality right from the start is critical for proper data processing. Without it, you risk having incorrect shipping addresses, which can cause delays, extra costs, warnings in your billing system, or incorrect recommendations from a store locator feature—all bad for your business and bad for user experience.

But the feature isn’t just about preventing negative events. Address autocompletion can have quite a positive impact simply by making things easier on your users, like when someone has a time constraint and needs to complete a purchase quickly. It will also increase the overall quality of all the addresses you collect in your database and therefore decrease your operational costs.

Building and Implementing an Address Autocomplete API

For the purposes of this article, I’m going to assume that you’re using a datasource with a structure similar to this:

Countries: Lists all countries and their corresponding ISO code. This table should also include metadata cataloging which data is present or not, like streets that might be missing or whether no postal codes exist for a certain country.
Places: Linked to a country, lists the cities and towns.
Postal codes: Linked to a place, lists the related postal codes. Note that one postal code can be included in more than one place.
Streets: Linked to a place and a postal code, lists all related streets. Note that the same street can cross multiple places or postal codes.

Keep a Few Tips in Mind

For building and apartment numbers, I work on the assumption that the datasource doesn’t provide accurate information. It’s truly difficult to have a complete and accurate source of house numbers, so I advise you to leave that as a free input. Otherwise your captured data quality will probably be impaired.

Note that some countries don’t have postal codes or your datasource might not have accurate data for that country. In that case, just show a free input for the fields that are missing data.

The form built in this tutorial calls a backend API that’s linked to a referential database. The API sends back and validates the data from the source, and provides autocomplete when necessary. It’s important to remember that the quality of the result depends a lot on the quality of the reference datasource. You can read the paragraph Ensuring the Quality of Your Datasource to learn more about this.

Choose Your Field Order

There are two possible ways to approach building this form. You can enforce the fields to fill in this order:

1. Country -> Postal code -> Locality -> Street. This approach is easier technically, but some users might prefer to see a filtered list of postal codes.
2. Country -> Locality -> Postal code -> Street. This is a more traditional approach.

I actually recommend combining the two approaches in one form and letting the user fill whichever field they want first. The country should still be enforced as the first field to fill because it's information all the users can give quickly and correctly. It also narrows down the possible choices and allows you to hide or show some fields. As mentioned earlier, some countries don’t even have postal codes, so you wouldn’t need to show a field for them.

The final result for Approach 1 should look like this:

The final result for Approach 2 should look like this:

Display the Data in the Form

For some endpoints, the data is returned based on what the user is currently typing. To avoid having too many hits on your API endpoint, I recommend a delay of 20 ms. In that time, the user might continue typing their query; by introducing a delay, you avoid sending useless queries to the backend.

A convenient way to implement that functionality is to use the RxJS debounceTime feature.

Build the API

Here I’ll describe all the endpoints you need to serve to have a complete API. But first, let me share a few quick insights that I’ve found helpful:

If a path contains a colon (:), that indicates a parameter that will be reused in the query.
For POST requests, the content’s structure is described as the payload in a JSON format.
For all the API endpoints doing an autocomplete, don’t return anything if the user hasn’t started typing. It would make little sense in terms of user experience.
When filtering on a user’s input, always do a case-insensitive search with wildcards before and after.
The queries that might return a lot of data should be limited to 100 results to increase both the query performance and the response time.
Consider that important entities like postal code and place will have a unique identifier. Depending on your datasource, the structure might slightly differ.
Depending on your datasource’s license, you might want to implement a per user throttling and quota mechanism to avoid having all the data scrapped. #### Approaches 1 and 2 — Country

Endpoint: GET /countries

This returns the list of countries without any filtering. The list is short, so it can be loaded entirely on the client side.

Again, remember that some countries don’t have postal codes and your datasource might not have street data for all the countries. This is a good place to return that information and simply show a free text field for the missing data.

Approach 1 — Postal codes from Country

Endpoint: GET /countries/:iso/postcodes/:postcode

This returns the autocompletion of a postal code for a given country. The data must be filtered on the postal code.

Approach 1 — Places from Postcode

Endpoint: GET /postcodes/:postcodeId/places

Given a postal code, the API will return all the associated places.

Approach 2 — Places from Country

Endpoint: POST /countries/:iso/places
Payload: { place }

This query should return all the places in a country. The data is filtered on the place entered by the user.

Approach 2 — Postal codes from Place

Endpoint: GET /places/:placeId/postcodes/:postcode

If the user chose a place from a country, this endpoint must be used to autocomplete the postal code.

Approach 1 and 2 — Street from Place

Endpoint: POST /places/:placeId/streets
Payload: { postcodeId, street }

Note that both the place and the postal code should be necessary to correctly filter the street. This of course depends on your datasource, but combining both usually offers the most accurate discriminator.

Improving Your Address Autocomplete API

The autocomplete mechanism presented in this article only works on perfect matches. That’s rarely good enough, so you’ll most likely want to improve on what we’ve already got here.

Implement Full Text Search

Users are going to make spelling mistakes. To alleviate that in simple projects, you can use trigrams or the full text search capability of your DBMS.

For more complex solutions you can use a dedicated search engine such as Elasticsearch, OpenSearch, or Typesense.

Use Additional Datasets

Additional data can significantly enhance the user experience.

For example, consider how weighted data can improve the autocompletion. The most important places have the heavier weight, which would sort the results in a useful order that’s comprehensible for the user while aiding in disambiguation. With correctly weighted data, Boston (Massachusetts) would appear first in a list of the many cities that are named Boston in the United States.

Another interesting improvement would be grouping places by the name people expect to find. For example, London doesn’t actually exist as a place within the fine-grained English administrative divisions, but is rather a group of boroughs. Your users will probably still be looking for London, though.

Ensuring the Quality of Your Datasource

The quality of the datasource used to build your API is critical to the success of your autocomplete feature. Otherwise you’ll gather incomplete information at best and inaccurate information at worst. If a user enters data not present in your dataset, and you’ve made the use of your data mandatory, you could even lose the sign-up or the order, decreasing your overall conversion rates. This can be mitigated by allowing free text to be entered but will worsen the captured data quality.

The datasource you work with needs to include an up-to-date, exhaustive list of valid postal codes and places for all your target countries. Streets can be left optional but having a complete list of streets definitely helps. As it’s difficult to always have an up to date list of valid addresses, it’s best to allow free text. You can try to combine both approaches by trying auto-complete first and then allowing free text if nothing is found.

You’ve got 2 main options for your sources.

If you’d like to use free or open source data providers, we would recommend to use a combination of GeoNames and OpenStreetMap. GeoNames links places to postal codes for 96 countries while OpenStreetMap offers street coverage for most countries.

If you’re willing to invest in a paid solution, I’d recommend our GeoPostcodes database. It contains places for all countries in the world, postal codes for all of the countries using them, and streets for a third of the countries. It’s updated on a weekly basis, relying on more than 1,500 sources. You would benefit from the fact that postal codes and streets are already linked in a unified structure. Based on our experience, it would increase the quality of your API and its speed of implementation. Browse our datasets yourself and access our map explorer for free.

The API described in this tutorial would benefit from GeoPostcodes products like:

Our postal normalized street database. It includes alternative place names in foreign languages.
Town weights. This improves results sorting.
City tags. You can natively incorporate large cities and metropoles in the search.

Conclusion

Clearly, having an autocomplete address form is crucial to a lot of business processes, like logistics or online sales. It vastly improves data quality and ensures correct data processing. With all the information covered here—what it looks like to start with postal code or place for your form, and why you’ve got to have a reliable and updated datasource —you should be well on your way to creating the perfect form to capture accurate addresses.

How to Build a Zip Code to Time Zone Database in Five Steps

Jérôme Urbain — Fri, 17 Feb 2023 09:43:37 +0000

Timing matters. Whether you want to remind customers about reservations, run a survey, or send marketing messages, contacting people at the optimal time is a key to business success. In the case of call centers, timing can even be critical, when you really need live interaction with the recipient.

But knowing the proper time to call can be tricky when you’re working with a large user base. Your recipients may be spread over a range of time zones, and it’s not always straightforward to gather that data from standard user information. Some large countries (e.g., USA, Canada, Mexico, Brazil, Russia) span numerous time zones; you’ll need more than just a country of residence to determine the time zone of their residents.

In this article, we’ll see how you can infer time zones from international postcodes. Zip codes (also known as postal codes or postcodes) are an element of standard address formatting. They’re also frequently included in user information, making zip codes a good candidate for determining time zones.

What Is Zip Code to Time Zone Mapping?

Zip code to time zone mapping is the process of associating a time zone to its correct postal code. The end goal of this is to facilitate reaching out to any contact at a suitable time.

This requires access to:

Zip codes with additional information (administrative divisions, coordinates, etc.)
Time zone rules

When mapping zip codes to time zones across a single country, it’s usually feasible to work with a set of time zone rules (e.g., California is in time zone America/Los_Angeles, while New York is in time zone America/New_York). However, this can already require some multi-level processing in order to build accurate mapping. For instance, states and even counties in the USA can include more than one time zone. As a result, you would not only need to find a source that maps zip codes to states/counties, then assign time zones to states/counties and then encode exceptions.

Of course, rules get more complicated when you target international data, which is the purpose of this article. So, we will not work with time zone rules, but use a more convenient method for international data, focusing on geographical matching between postcodes and time zones.

Assigning Time Zones to Zip Codes

Let’s dive into the practicalities of assigning time zones to zip codes. In this section, we’ll talk about where to find the base data (the postcodes and time zones we’ll work with), how to upload the data to a common framework, and finally how to use both datasets to assign a time zone to every postcode.

Sourcing Postcode Data

You first need to identify a good source of international postcodes, with geographical information. Free candidates include GeoNames and OpenStreetMap.

From those sources, you can access postal codes for a number of countries (and even more if you combine them) along with their latitude and longitude. This tutorial will use GeoNames.

Sourcing Time Zone Data

The most widely used time zone data is published by the Internet Assigned Numbers Authority. It includes a set of rules and code that anyone can process, not only to capture the different time zones, but also to access the past and future time switches from Standard Time (sometimes referred to as “winter time”) to Daylight Saving Time (“summer time”) in relevant time zones.

For the sake of our geographical mapping, we’ll use a map of the time zones as maintained on Time Zone Boundary Builder. This project releases high quality maps of the official IANA time zones. You can download the geojson file in the Releases folder.

Take the file with oceans if you want to cover the whole earth. Take the one without if you don’t want to assign a time zone to points that would fall into oceans (indicating coordinates are likely wrong).

The world time zones map, according to the IANA 2022g release, looks like this:

However, the map only includes the zone name. In order to use the zones, you’ll need more information (such as the Standard and Daylight Saving Times of each time zone, and so on). To obtain additional details like that, you can process the official IANA time zone files. If you don’t want to run that, or extract data from Wikipedia (scraping or extracting this table), you can get a consolidated CSV file from GeoPostcodes.

Uploading the Data to a Common Database

To combine the data sources, you’ll benefit from uploading them to a common framework. While it’s perfectly possible to handle the processes with widespread programming languages and libraries (e.g. Python and GeoPandas), we’ll use a database to store and query our data in this tutorial.

Our preference goes to PostgreSQL with the PostGIS extension, a leading open-source database engine with state-of-the-art geographical data processing features. It offers all the functionalities you need in this case:

Uploading the different data sources
Matching them using geographical attributes or other keys
Capable of storing, querying, and exporting the results

It also natively handles time zones, including the IANA zone names, making it a great choice if you want to manage your time data in the same framework as your postcodes data.

First, set up a PostgreSQL/PostGIS database. There are numerous tutorials available for that, but we suggest this one.

Once you have the database set up, you can upload your different sources to it. We’re assuming in this tutorial that you’ll upload everything to a schema named timezones.

All the commands we show here can be run from any SQL client (like pgAdmin or DBeaver), but also using the psql interactive terminal.

The general psql command syntax looks like this:

psql -tA -U #username -d #database -h #host -c "#sql_command"

It’s useful to set up a password file to avoid being prompted for your password every time.

Now, it’s time to upload your files. You’ll need to unzip all the files to a common directory (/home/timezones, in this tutorial). Then, you can upload their data to your PostgreSQL database with the following commands.

Geonames.csv

First, create a table to host the data:

CREATE TABLE timezones.geonames_postcodes(
iso char(2), 
postcode varchar(20), 
place varchar(180), 
adm1_name varchar(80), 
adm1_code varchar(20), 
adm2_name varchar(80), 
adm2_code varchar(20), 
adm3_name varchar(80), 
adm3_code varchar(20), 
lat float, 
lng float, 
geo_accuracy int);

Now we can upload the data, here using the \copy command from psql:

psql -tA -U #username -d #database -h #host -c "\copy timezones.geonames_postcodes FROM /home/timezones/allCountries.txt CSV HEADER DELIMITER AS E'\t' ;"

Alternatively, you can also use the COPY from a SQL client, and most clients offer functionalities to upload a CSV directly from the graphical interface.

IANA geojson

Leverage the ogr2ogr GDAL command to directly upload the geojson file with time zone polygons to our database:

    ogr2ogr -f "PostgreSQL" -clo SCHEMA=timezones PG:"dbname=postgres user=#user" combined.json -nln timezone_polygons

Time Zone Information

You can upload the file available from GeoPostcodes’s website, GPC-TIMEZONES.csv, with the following commands. First create a table, then upload the CSV:

    CREATE TABLE timezones.timezones_info(
iso char(2), 
country text, 
timezone text, 
std_offset char(6), 
dst_offset char(6), 
zone_abbrevation char(10), 
zone_alt_name text);
psql -tA -U #username -d #database -h #host -c "\copy timezones.timezones_info FROM /home/timezones/GPC-TIMEZONES.csv CSV HEADER DELIMITER AS ';' ;"

Preparing Geographical Data

Once you’ve uploaded the necessary data, it’s a good idea to add a spatial index on the polygon database to speed up the spatial joins:

CREATE INDEX timezones_geom_idx on timezones.timezone_polygons USING SPGIST(wkb_geometry);

You’ll also benefit from creating a geometry column in the Geonames table, which you can then invoke for the spatial joins:

ALTER TABLE timezones.geonames_postcodes ADD COLUMN geom geometry(point,4326);

UPDATE timezones.geonames_postcodes SET geom=ST_Makepoint(lng,lat);

Linking Postcodes and Time Zones with Geographical Matching

Now that all your data is stored in the PostgreSQL database, leverage the geographical attributes in the geonames_postcodes and timezone_polygons tables to join them. Use the coordinates of every postcode and check which time zone polygon they fall in.

You can perform this with the following query, creating a new table to store your results:

CREATE TABLE timezones.postcodes_timezones AS SELECT g.iso, g.postcode, t.tzid FROM timezones.geonames_postcodes g JOIN timezones.timezone_polygons t on ST_Intersects(g.geom,t.wkb_geometry);

After this process, some postcodes might be mapped to several time zones. This can happen either because time zone polygons overlap (it’s infrequent but it happens) or because the postcode is associated with a locality that falls in more than one time zone.

To properly match the time zone most associated to each postal code, run:

CREATE TABLE timezones.postcodes_tz AS SELECT iso, postcode, mode() WITHIN GROUP(ORDER BY tzid) as timezone FROM timezones.postcodes_timezones GROUP BY iso, postcode;

Note that in case of a tie, mode will arbitrarily select one candidate.

Once the time zone has been identified, consider gathering extra information about it, like its Standard and Daylight Saving Times. If you want to pull in the information contained in the timezones_info table, extend the query to:

SELECT pt.iso, pt.postcode, mode() WITHIN GROUP(ORDER BY pt.tzid) AS timezone, ti.std_offset, ti.dst_offset, ti.zone_abbrevation, ti.zone_alt_name FROM timezones.postcodes_timezones pt LEFT JOIN timezones.timezones_info ti on ti.timezone=pt.tzid GROUP BY 1, 2, 4, 5, 6, 7;

The following screenshot shows the result, where you can see every postcode mapped to a single time zone, with high level information about the time zone:

Known Issues with a Postcode to Time Zone Database

The IANA time zone polygons are released shortly after any updates to the IANA time zone rules, so the main issues you might encounter will relate to postal data.

Aside from the issue with retrieving a single time zone per postcode in the case of a tie, there are three other areas for potential hiccups to consider:

Country coverage
Coordinates accuracy
Out-of-date postcode data

Country Coverage

GeoNames and OpenStreetMap produce postcodes for the vast majority of countries spanning multiple time zones (the main exception being Indonesia for GeoNames). The other countries, which are covered by a single time zone, can be mapped to time zones with country-wide rules, taken from the timezones.timezones_info table.

Coordinates Accuracy

OpenStreetMap and GeoNames deliver generally reliable coordinates. However, a known issue of GeoNames is that coordinates are rounded and consequently mapped to a grid in some areas.

These approximate coordinates will generally take you to the correct time zone or at least a neighboring time zone, so you’ll end up within two hours of the actual local time (taking variations about Daylight Saving Times into account).

Out-of-date Postcode Data

Both OpenStreetMap and GeoNames do not always have the most complete and up-to-date postcode data. As a result, you could be missing postcodes. Some OpenStreetMap postcodes should just be discarded altogether, as they don’t respect the country’s postcode format.

If you can accommodate small errors (in other words, assign a time zone that’s close to correct), you can look for the closest postcode in your available data. Postcodes usually follow a hierarchical structure, so look for a postcode sharing the same leading characters. For instance, postcode 6999 should be considered closer to 6990 than 7000, and T2H 0K7 is closer to T2H 0Y0 than T2H 1K7.

Note of course that approximations can add up in cases like that. If you link a postcode to another alphabetically close postcode, that second postcode can itself have approximative coordinates.

Conclusion

While you can derive time zone information from postcodes using freely available data and software, the quality of the result is highly dependent on the data sources you use.

We’ve highlighted some common issues and explained some workarounds, but you might want to consider a commercial option if you need:

Higher accuracy than the ±2 hours solved for in this article
Extra information, such as the results of switching to/from Daylight Saving Time
Data that’s always up to date

GeoPostcodes maintains a worldwide database of postcodes, including up-to-date time zone information for every postcode-locality combination. When a postcode is linked to several time zones, you can access all of them and use locality to filter.

You can have a look at the Timezone product sheet or even browse the data for yourself. But don’t hesitate to reach out to us if you want to know more!