NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

Newbie...I'll be working with OSM person (not yet found by my developer so can't ask yet) and then sending extracted data to our web developer to add to Mysql database. I would like it in MsAccess so I can look it over first. We need to extract data from a variety of searches, including, for ex, schools. They often have multiple key tags "Amenity=school" AND "Building=school" so on extraction they will end up in our table, I assume, in a multi-value cell in the "Key" column/field? (Or perhaps a child table for each record? I'm no DB guy). Perhaps comma or semicolon delimited like Building;Amenity? But then what happens to the values for these two keys since only one value column...perhaps same thing, School;School (guess I picked a bad example because the value is the same word in this case.) And the order would perhaps reflect which value belongs to which key in the adjacent key cell/column? Or should a new row be created somehow for each of the keys so only one in each cell (and we could eliminate new duplicate rows for these nodes later by OSM ID column filtering.)

Can any of the OSM filters do this easily, or perhaps it is the conversion tools that would get it into Access table for us that could do this "one row for each key" conversion, or perhaps we can just filter the data even though there are two values right along with those with one value, some kind of "or" statement? Hope I'm explaining this ok. Thanks for any advice.

EDIT by the way, how would our shape files be stored in Access since multiple nodes for a given record...again is is some sort of multi value cell the nodes that comprise the way should end up in, or a child table, if that is the word?

asked 25 May '19, 22:44

philip's gravatar image

philip
26346
accept rate: 0%

edited 25 May '19, 22:55

The amenity column and building column (and basically a column for the 7 different Keys in our searches, which would usually just be null for 6 of the 7 for a given node) is a great idea, vs key column that would result in multi value cells...I believe you are generally only allowed to use one value per key on a given node so this would solve our problem. I assume it is easy to do with the various OSM data-extraction utilities? Thanks much.

(26 May '19, 11:35) philip

Sometimes an example helps. To take the keys you mentioned, "amenity=school" and "building=school", they actually mean different things within OSM (but are both in some sense "schools").

An "amenity=school" describes a currently active school, and is normally drawn around the edge of the grounds.

A "building=school" describes a building that was built as a school. It might not be part of a school currently (maybe the building is used for offices, or something else).

I'd suggest using taginfo and overpass turbo to explore the data near you so that you can see how things are tagged. That'll give you and understanding of the data and help you explain what tags you want to look for.

permanent link

answered 26 May '19, 12:29

SomeoneElse's gravatar image

SomeoneElse ♦
36.9k71370866
accept rate: 16%

Yes thanks. We are trying to err on the side of too many vs. missing any, so while building=school may not actually be a currently functioning school, that is better, for our use, than missing some that are functioning schools, but are not also tagged with amenity=school. I also notice in the link you provided that 10% of the amenity=school nodes also have "building=school" as an additional tag. Thought it would be higher overlap. Even more bizarre is that only approx. 60,000 of the 500,000 "building=school"have "amenity=school" as an additional tag. So either a lot of schools are defunct, or people are not tagging them consistently. Thanks again.

(26 May '19, 13:07) philip

Most schools are mapped as areas including playgrounds, staff & parent parking, sports fields etc. This is useful because access rights to school grounds are often controlled for pupil safety. Therefore only a few schools, typically in inner cities are likely to have both tags.

(26 May '19, 13:28) SK53 ♦

I'd expect relatively little overlap between those two tags. Most "amenity=school" will be drawn around the boundary of the grounds. There will be several buildings within those grounds, and these won't be tagged "amenity=school" as well because they are not, in themselves, schools - just buildings within a school.

(26 May '19, 13:29) SomeoneElse ♦

Thanks both. So on one hand makes sense that most nodes/ways dont have multiple tags, but on other hand means we may end up with a lot of the same schools that are separate nodes/ways as both buildings and amenities? Or perhaps the multiple buildings in school are each separate nodes or ways and in a relationship of some sort with others of same school? I'm new at this so have to contemplate. Or am I misundertanding and there is no "standard" in that both amenities and buildings can be drawn as shapes? If you wanted the most accurate results for actual operating schools, and only had one choice of key/value, which would you choose?

(26 May '19, 13:51) philip

As maxerickson said above, you need to decide what you want to count - institutions or buildings. If you want to count institutions count "amenity=school"; if you want to count buildings that were built as part of schools, count "building=school". If you count "building=school" your result will include things that were built for education but no longer used for it, and things not built for education that are now used for it.

(26 May '19, 14:04) SomeoneElse ♦

Thanks again. Yeah, I see what you mean. I searched overpass turbo for buildings=school and when you zoom in it seems most are simply separate buildings in a single school. So we will stick to amenity=school, which hopefully the majority of those "clusters of buildings" are also tagged as separately. Having 5 or 10 buildings as separate records in our database as "schools" that are really part of same school is a bigger issue than missing that % that weren't tagged as amenity=school, but only 500,000 building=school and if...guessing...5 buildings per school, that is 100,000 schools tagged that way, while a million are tagged amenity=school, so seems obvious we should stick with that (plus many of those 100,000 multi-building schools will be in the million, so probably losing way less.) Appreciate the help!

(26 May '19, 21:18) philip

Just FYI, the building/amenity ratio can be the other way around too. In some places multiple schools are located in a single building, and the result might be a single building=school containing multiple amenity=school nodes.

(27 May '19, 17:31) jmapb
showing 5 of 7 show 2 more comments

On the OSM side, tagging is completely freeform, there are no columns and there are essentially no rules about what tags can be applied to which objects.

The questions you are asking don't really have answers, in the sense that they are choices you get to make when you do the data extraction. Do you want an amenity column, do you want a building column, etc. Some tags will be contradictory and some tags will not make sense. You probably need to deal with those scenarios.

You'll likely end up with a list of tags you care about and a list of tags that you discard. You can look at openstreetmap-carto.lua and openstreetmap-carto.style in https://github.com/gravitystorm/openstreetmap-carto for examples of how the data is interpreted for a relatively complicated use case (the default tiles served on openstreetmap.org).

permanent link

answered 25 May '19, 23:11

maxerickson's gravatar image

maxerickson
12.7k1083176
accept rate: 32%

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×53
×6
×4

question asked: 25 May '19, 22:44

question was seen: 2,314 times

last updated: 27 May '19, 17:31

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum