ZhukO
December 28, 2018, 9:47pm
1
Hello!
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: https://www.ohdsi.org/
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
kocio
(kocio)
December 29, 2018, 1:40am
2
I’m not sure if I understand you, but we use in OSM quite easy to parse hierarchy:
https://wiki.openstreetmap.org/wiki/Key:admin_level
escada
(Escada)
December 29, 2018, 7:04am
3
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.
ZhukO
December 29, 2018, 9:09am
4
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.
kocio
(kocio)
December 29, 2018, 5:03pm
5
I’m still mot sure why you have just one level. Here you can browse hierarchical admin borders overview for different countries:
https://wambachers-osm.website/boundaries/
ZhukO
January 2, 2019, 4:01pm
6
Thank you for answering, it’s all my fault.
Hierarchy is hidden inside [rpath] attribute.
ZhukO
January 6, 2019, 8:04pm
7
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?
escada
(Escada)
January 7, 2019, 8:09am
8
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.
wambacher
(Walter Nordmann)
January 7, 2019, 10:45am
9
ZhukO:
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?
Hi, as i told you some days ago (via mail):
some hundreds per day
30.12.18:
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
6.1.19
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.
regards
walter
wambacher
(Walter Nordmann)
January 7, 2019, 10:53am
10
escada:
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.
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.
Regards
walter
ZhukO
January 8, 2019, 3:43pm
11
escada:
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.
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!
escada
(Escada)
January 8, 2019, 8:11pm
12
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.
ZhukO
January 8, 2019, 8:38pm
13
escada:
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
Thanks!
Tordanik
(Tobias Knerr)
January 8, 2019, 9:35pm
14
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. ;))
escada
(Escada)
January 9, 2019, 12:57pm
15
Tordanik:
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.
ZhukO
January 11, 2019, 11:03pm
16
Ok guys, digging into OSM a little bit and getting more and more questions
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?
escada
(Escada)
January 12, 2019, 11:03am
17
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.
ZhukO
January 12, 2019, 11:15am
18
escada:
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?
escada
(Escada)
January 13, 2019, 7:41am
19
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: https://www.openstreetmap.org/relation/2504948 The boundary is shown as a closed line and at the correct position.
ZhukO
March 23, 2019, 9:54am
20
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