Geographical hierarchy

First of all, I want to say thank you for your work, open street maps are fantastic.

Dear OSM community, I am writing to you being member of another open-source community:
We provide analytical solutions for medical data worldwide and now we need your help.

At OHDSI, we are trying to unify the medical data. We created special data model (called CDM: Common Data Model) for that purposes. One serious problem with CDM is that we don’t have geographic data included in it. Next major update will include geographic data into CDM.
We are planning to store all administration level boundaries of all countries hierarchically with coordinates and ZIP-codes, but original OSM data does not contatin hierarchy of administrative levels. Can you help us with export of this data without reinventing the wheel?

P.S. Currently we are able to export all boundaries of all levels but the problem is to create hierarchy without excessive computational effort

Thank you

I’m not sure if I understand you, but we use in OSM quite easy to parse hierarchy:

As for the ZIP-codes, different countries work with different systems. In Germany and Belgium we have postal code areas, and they are mapped as areas, in a similar way as administrative boundaries. ZIP-codes in the UK or the USA are not areas and are typically only mapped on the addresses themselves.

In exported data all we have is admin_level = 5, for example. so we have to query database to find out hierarchy. It is possible, but not convenient for the whole planet. So if there are any easy ways?

Unfortunately, all we got for some datasets is just a zip-code, sometimes even shortcuted to biggest areas. That’s why we have to store zip-codes.

Thank you for the quick response.

I’m still mot sure why you have just one level. Here you can browse hierarchical admin borders overview for different countries:

Thank you for answering, it’s all my fault.
Hierarchy is hidden inside [rpath] attribute.

Guys, I have one more question: as far as we know that OSM IDs may not be constant due to some changes, and boundaries are changed and improved regulary, how often do this changes occur?

The ID of a boundary relation would only change when someone removed the complete boundary (relation) and creates a new one (with a different ID). Normally this should never happen, but as you can see here, on Wambacher’s site, people sometimes remove boundaries.

Hi, as i told you some days ago (via mail):

some hundreds per day


psql:/osm/db/misc/admin/countries/update_trees_6b.sql:45: HINWEIS:  DEU: 69 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:59: HINWEIS:  AUS: 66 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:60: HINWEIS:  AUT: 10 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:63: HINWEIS:  BDI: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:64: HINWEIS:  BEL: 10 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:71: HINWEIS:  BIH: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:72: HINWEIS:  BLR: 7 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:75: HINWEIS:  BOL: 21 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:78: HINWEIS:  BRA: 48 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:79: HINWEIS:  BRB: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:87: HINWEIS:  CHN: 12 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:101: HINWEIS:  CZE: 6 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:105: HINWEIS:  DNK: 4 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:107: HINWEIS:  DZA: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:125: HINWEIS:  ESP: 33 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:144: HINWEIS:  GRC: 16 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:148: HINWEIS:  GTM: 5 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:152: HINWEIS:  HRV: 23 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:156: HINWEIS:  HUN: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:158: HINWEIS:  IDN: 6 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:160: HINWEIS:  IND: 6 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:163: HINWEIS:  IRN: 20 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:170: HINWEIS:  ITA: 25 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:175: HINWEIS:  JPN: 10 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:186: HINWEIS:  KOR: 5 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:191: HINWEIS:  LBR: 6 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:197: HINWEIS:  LTU: 6 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:204: HINWEIS:  MDG: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:211: HINWEIS:  MMR: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:233: HINWEIS:  NLD: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:234: HINWEIS:  NOR: 9 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:237: HINWEIS:  NZL: 1 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:242: HINWEIS:  PAN: 5 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:248: HINWEIS:  POL: 45 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:250: HINWEIS:  PRT: 20 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:251: HINWEIS:  PRY: 9 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:261: HINWEIS:  SEN: 6 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:286: HINWEIS:  SWE: 6 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:295: HINWEIS:  THA: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:302: HINWEIS:  TUN: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:303: HINWEIS:  TUR: 29 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:305: HINWEIS:  TWN: 60 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:312: HINWEIS:  URY: 5 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:313: HINWEIS:  UZB: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:319: HINWEIS:  VNM: 59 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:350: HINWEIS:  FRA: 191 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:351: HINWEIS:  GBR: 13 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:357: HINWEIS:  FIN: 9 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:363: HINWEIS:  IRL: 65 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:365: HINWEIS:  RUS: 44 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:366: HINWEIS:  UKR: 24 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_6b.sql:367: HINWEIS:  USA: 204 boundaries processed


psql:/osm/db/misc/admin/countries/update_trees_8b.sql:45: HINWEIS:  DEU: 95 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:47: HINWEIS:  AFG: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:54: HINWEIS:  ARG: 73 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:57: HINWEIS:  AUS: 5 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:58: HINWEIS:  AUT: 12 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:62: HINWEIS:  BEL: 31 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:65: HINWEIS:  BGD: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:69: HINWEIS:  BIH: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:70: HINWEIS:  BLR: 15 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:71: HINWEIS:  BLZ: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:73: HINWEIS:  BOL: 19 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:74: HINWEIS:  BRA: 98 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:81: HINWEIS:  CHE: 4 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:83: HINWEIS:  CHN: 62 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:86: HINWEIS:  COD: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:89: HINWEIS:  COL: 5 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:92: HINWEIS:  CRI: 4 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:97: HINWEIS:  CZE: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:101: HINWEIS:  DNK: 4 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:105: HINWEIS:  ECU: 13 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:109: HINWEIS:  ESP: 35 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:110: HINWEIS:  EST: 16 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:122: HINWEIS:  GIB: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:127: HINWEIS:  GRC: 1 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:130: HINWEIS:  GTM: 8 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:134: HINWEIS:  HRV: 27 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:135: HINWEIS:  HTI: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:136: HINWEIS:  HUN: 44 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:138: HINWEIS:  IDN: 17 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:140: HINWEIS:  IND: 18 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:143: HINWEIS:  IRN: 14 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:148: HINWEIS:  ITA: 15 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:153: HINWEIS:  JPN: 16 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:159: HINWEIS:  KEN: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:177: HINWEIS:  LVA: 1 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:179: HINWEIS:  MAR: 8 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:184: HINWEIS:  MEX: 4 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:186: HINWEIS:  MKD: 1 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:188: HINWEIS:  MLT: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:192: HINWEIS:  MOZ: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:193: HINWEIS:  MRT: 1 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:196: HINWEIS:  MWI: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:207: HINWEIS:  NLD: 4 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:211: HINWEIS:  NZL: 4 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:216: HINWEIS:  PAN: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:218: HINWEIS:  PER: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:219: HINWEIS:  PHL: 15 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:221: HINWEIS:  PNG: 5 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:222: HINWEIS:  POL: 48 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:225: HINWEIS:  PRY: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:230: HINWEIS:  ROU: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:231: HINWEIS:  RWA: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:250: HINWEIS:  SWE: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:269: HINWEIS:  TWN: 93 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:270: HINWEIS:  TZA: 6 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:276: HINWEIS:  URY: 6 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:283: HINWEIS:  VNM: 48 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:288: HINWEIS:  XXK: 1 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:293: HINWEIS:  ZAF: 2 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:295: HINWEIS:  ZWE: 3 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:313: HINWEIS:  FRA: 217 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:314: HINWEIS:  GBR: 42 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:315: HINWEIS:  FIN: 9 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:316: HINWEIS:  IRL: 114 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:318: HINWEIS:  UKR: 21 boundaries processed
psql:/osm/db/misc/admin/countries/update_trees_8b.sql:319: HINWEIS:  USA: 144 boundaries processed

7.1.19 still running

Changes of osm-id are rare - but it does happen. Even names are sometimes changing, therefor this is not easy.


This boundaries usually have not been removed by deleting them - this boundaries are “missing” because their geometry has been damaged. Usually by editing errors.

Some boundaries have been deleted but most of them are damaged and can be repaired.


So if IDs are usually new, is it possible that some new relations can take IDs of the old ones?

Sorry, but I didn’t receive this mail with number of boundaries processed daily. Thank you!

No, the IDs keep on increasing, IDs can never be re-used for new object. The deleted objects are also kept in the database, so the IDs do not really disappear when an object is deleted.

Maybe that was not very bright question, but just to be sure


However, you can un-delete a previously deleted object, change all of its attributes, and move it to the other side of the globe to represent an entirely different real-world feature.

This isn’t something that people should be doing, or even do all that often, but the data model doesn’t have any fundamental barriers against this built in.

(Sorry if this is too pedantic. ;))

Not for me, I knew about this case, but see it as an ‘edit’, not a new object.

Ok guys, digging into OSM a little bit and getting more and more questions :slight_smile:

Can you please tell me how is rpath calculated?

I found out that in some cases ID of the area is not the last one in the rpath attribute. Obviously, these cases are mistakes
For example, processing Charlottesville produced this:

But looking for Charlottesville at Nominatim got me this:

The same thing with the other concepts. Can you comment on this please?

Maybe some stupid questions, but in which software are you investigating this rpath ? and from where did you download the data to display in that tool ?

OTOH, Nominatim has some Tiger data that is not in the OSM database.

I use Datagrip, Server is running PostgreSQL, I’ve got data from Mr. Wambacher’s website.
Boundaries were exported as shp and loaded into datagrip with shp2pgsql (part of POSTGIS extension)

There are 174 of 29885 rows with that type of error for US borders from 09.01.2019

What kind of additional data does Nominative store?

Nominatim imports some postal code information from Tiger I believe.

Perhaps there is some error in the shape file generation or reading, as you can see at this moment the data in OSM itself is correct: The boundary is shown as a closed line and at the correct position.

Thank you all, guys, for help.

I think it might be important for you to know that about a week ago we released our OSM vocabulary. This would not have been possible without your help