Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Mapping Postal codes

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
Patric_Nordstrom
Employee
Employee

Mapping Postal codes

Last Update:

Apr 2, 2021 4:34:15 AM

Updated By:

Patric_Nordstrom

Created date:

May 30, 2019 4:28:15 PM

Attachments

Mapping data based on postal code areas or center points is a common task. However postal codes is not a one global standard and there is lot of variation between countries in format and structure that makes the task challenging.

Also the availability of postal code areas varies, many countries are included in the Qlik Location database, but for others postal code areas need to be purchased separately.

Workflow

Here's the high level workflow, the bulk is data preparation before rendering

1. Prepare the data
- Make the postal codes unique
- Pad with zeroes
- Some countries are special
- Check coverage
- Build overview layers when possible

This app will show how to map postal code areas for five European countries: France, Great Britain, Germany and Italy. The indata is not clean and contains errors like in most uses cases.

Prepare the data

Make the postal codes unique

When mapping postal codes for several countries the first step is to make the postal code unique. The easiest way to do this is to add country code prefixed with a comma after the postal code.

=Postal & ',' & CountryCode as zip

Pad with zeroes

In many cases Excel drops leading zeroes in numerical postal codes. I can see that when looking at the max and min lengths. Note that not all postal codes are numeric though and UK is special so we fix that later. Pad with leading zeroes for France, Germany and Italy like this:

=Num(PostalCode,'00000')
 
tablezip.png

 

Great Britain is special

The postcodes are alphanumeric, and are variable in length: ranging from six to eight characters (including a space) long. Each post code is divided into two parts separated by a single space. More info at Wikipedia. Here's the three levels comparable to 2,3 and 4 digit numeric postal codes:

Area: 1 or 2 letters
District: 1 or 2 digits or a digit followed by a letter
Sector: 1 digit
 
Can be parsed like this if the the space is in the right place:
=If(IsNum(Mid(zip,2,1)),Left(zip,1),Left(zip,2)) & ',' & cc // Area
=subfield(zip,' ',1) & ',' & cc // District
=subfield(zip,' ',1) & ' ' & left(subfield(zip,' ',2),1) & ',' & cc // Sector
 
Check coverage
By checking the table at Qlik GeoAnalytics Coverage I can tell that there is built support for postal code areas for most of the countries I am interested. However:
(1) Postal code areas are missing for Italy.
(2) France doesn't support 2,3,4 level postal code areas.
(3) Northern Ireland is not included in the areas for Great Britain.
 
coverage.png

 

 
Truncate for 2 and 3 digit postal code levels
Germany and Switzerland are easy, just truncate the zip code and add the country code. Add the following before the load:
 
load *,
left(zip,2) & ',' & cc as zip2,
left(zip,3) & ',' & cc as zip3;
 
France, IntersectsMost
For France we will use administrative areas level 1 and level 2 as replacement of the non-existing postal code areas of 2 and 3 digit.
 
Using the QGA connector operation IntersectMosts will construct the hierarchy between postal code and admin boundaries:  Adm1>Adm2>Zip. For details see the load script.
 
intersectsmost.png

 

Italy, Within
For Italy we will also use admin 1 and 2, and use a point layer for the postal code level.
 
Using the QGA connector operation Within we can link postal code center points to the administrative boundary level 2: Adm1>Adm2>Zip. For details see the load script.
 
within.png

 

Create map view
After that it's easy to put together a map view.
1. Create a drill down group Country > zip2 > zip3 > zip
2. Add map chart and a area layer, drill down group as dimension, color by Sum(Sales)
3. Add a point layer only visible on level "zip", add a set expression for location: only({<cc={'IT'}>}zip)
 
zip3.png
See the attached app and data for more details.
Tags (1)
Comments
mrmatt
Contributor III
Contributor III

@Patric_Nordstrom thanks , this is helpful however I went through the data prep and still having a significant issue in that many of my US zip codes are showing up in seemingly random countries in Europe.  My Zips are padded to be 5 digits and country code is US. For example 49424,US

Patric_Nordstrom
Employee
Employee

 

 

49424,US works for me:

 

 

zip.png

0 Likes
abg_emea
Contributor II
Contributor II

Hi Patric,

Question regarding Italy. We use QlikSense Business, but the cloud version, not the desktop.
We have postal codes for UK, France, Spain, Germany and Italy. We are able to plot the postal areas to area maps nicely for every country but Italy.
Do you think Qlik is still not supporting the postal code area level for Italy? Alternatively it would be also good if we could use the "commune" level of Italy, that should be administration level 3 I beleive, but as far as I see admin. level 2 is the lowest I can go down in Italy.

Is this the case? Do you know any workaround in cloud?

Many thanks in adavance,

Tamás

Patric_Nordstrom
Employee
Employee

Not in any near future, meanwhile load the commune level of Italy using the techniques described above.

https://www.istat.it/ is good source of Italian boundary data.

Thanks,

Patric

0 Likes
hectorgarcia
Partner - Creator III
Partner - Creator III

I liked the solution posted by  @Patric_Nordstrom  "49424,US works for me" but i Tried the same solution for postal codes in colombia that are good postal codes according google maps
https://www.google.com/maps/d/viewer?mid=19zdhyjMAOw3AeAC5XE-CTpEYPd0a1pHT&ll=6.268636560264406%2C-7... 

 

but for some reason I cannot make it work in my tenant at google cloud as an area layer... any ideas?  

2022-09-19_11-01-38.jpg

 pls find a data sample i have  

INTABLE.Ciudad_geo INTABLE.Geolocation1 INTABLE.Latitude INTABLE.Longitude postal_code_
MEDELLIN [6.1958,-75.583758] 6.1957997 -75.5837577 055411
MEDELLIN [6.2156,-75.582638] 6.2156 -75.5826378 050024
MEDELLIN [6.2281,-75.571809] 6.2281001 -75.5718093 050021
MEDELLIN [6.2281,-75.602588] 6.2281001 -75.6025883 050026
MEDELLIN [6.2326,-75.602707] 6.2325996 -75.6027073 050026
MEDELLIN [6.2353,-75.570487] 6.2353002 -75.5704869 050015
MEDELLIN [6.2362,-75.574794] 6.2362005 -75.5747941 050015
MEDELLIN [6.2378,-75.592741] 6.2377998 -75.5927409 050030
MEDELLIN [6.16268,-75.626596] 6.1626796 -75.6265956 055413
MEDELLIN [6.18535,-75.656993] 6.1853497 -75.6569934 050029
MEDELLIN [6.19713,-75.580071] 6.1971301 -75.5800714 050022
MEDELLIN [6.19754,-75.573441] 6.1975399 -75.5734407 050022
MEDELLIN [6.20217,-75.584456] 6.2021704 -75.5844561 050023
MEDELLIN [6.20271,-75.587207] 6.20271 -75.5872073 050023
MEDELLIN [6.20279,-75.580872] 6.2027897 -75.5808715 050024
MEDELLIN [6.20753,-75.575634] 6.2075303 -75.5756338 050022
MEDELLIN [6.20757,-75.576063] 6.2075696 -75.5760631 050022
MEDELLIN [6.21555,-75.563876] 6.2155501 -75.5638763 050021
MEDELLIN [6.21744,-75.5748] 6.2174397 -75.5748001 050021
MEDELLIN [6.22565,-75.606873] 6.22565 -75.6068733 050026
MEDELLIN [6.22885,-75.582382] 6.2288496 -75.5823825 050001
MEDELLIN [6.23097,-75.581105] 6.23097 -75.5811051 050024
MEDELLIN [6.23319,-75.573945] 6.2331902 -75.5739454 050015
MEDELLIN [6.23501,-75.574457] 6.2350097 -75.5744573 050015
MEDELLIN [6.23716,-75.572602] 6.23716 -75.5726016 050015
MEDELLIN [6.23796,-75.595223] 6.2379604 -75.5952234 050030
MEDELLIN [6.23845,-75.569847] 6.2384503 -75.5698466 050016
MEDELLIN [6.23915,-75.59709] 6.2391501 -75.5970901 050031
MEDELLIN [6.23918,-75.592547] 6.2391799 -75.592547 050031
MEDELLIN [6.24103,-75.571357] 6.2410298 -75.571357 050015
MEDELLIN [6.24554,-75.570219] 6.2455405 -75.5702186 050015
MEDELLIN [6.24727,-75.562467] 6.2472704 -75.5624674 050010
MEDELLIN [6.24877,-75.565606] 6.2487697 -75.5656056 050012
MEDELLIN [6.24896,-75.578818] 6.2489605 -75.5788179 050015
MEDELLIN [6.25007,-75.576471] 6.2500699 -75.5764714 050015
MEDELLIN [6.25211,-75.575899] 6.25211 -75.575899 050010
Ana_Yakushi
Employee
Employee

Hello @hectorgarcia,

We don't have polygons for Colombian postal codes yet, but the good news is that we are adding them soon to our location database and will be available in the coming days.

In the meantime I would suggest to use Point Layer instead, to at least have them represented as a point on the map.

 

Best Regards,

Ana

0 Likes
hectorgarcia
Partner - Creator III
Partner - Creator III

@Ana_Yakushi but i see in the coverage pdf that postal code is covered for colombia

0 Likes
Ana_Yakushi
Employee
Employee

Hello @hectorgarcia,

Do you mean the QGA Coverage Table? If you look at the top of the table, there is a separation between points and areas. For Colombia we have full coverage in point data. For areas the coverage is only admin1 and admin2.

Ana_Yakushi_0-1663687828689.png

 

Best Regards,

Ana

hectorgarcia
Partner - Creator III
Partner - Creator III

@Ana_Yakushi  do you have an estimated date for release the new geolocation database with the polygons?

0 Likes
Ana_Yakushi
Employee
Employee

@hectorgarcia we are currently testing the new version. If everything goes well, it should be released within the next 2 weeks.

Version history
Last update:
‎2021-04-02 04:34 AM
Updated by: