 I won't need 20 minutes. And then when there is a question in the audience, we don't have microphones, so just make a somewhat of the question of thank you. Alright, so hello. I am Nick, or my full name is Dominic George. Maybe some of you already attended the talk, Ike, and I did about veripeditus yesterday. Is there anyone here who attended the veripeditus talk yesterday? Okay. So I have to start off with a few excuses. I know you never do this in a talk, but first thing is the preparation for this was a two-hour workshop, and it is now cut down to a 25-minute talk. So I will give you a quick introduction into OSM Ike me. This is what we will do today. The second excuse is, yeah, actually, this will be the most unprofessional talk in this room because I am not a geospatial expert at all. My knowledge about geospatial and geography stuff basically boils down to what coordinates are. So this is a technical talk about how to handle open-street map data, and then you can do all the fancy geostuff with this that you like. Okay. So when we developed veripeditus, which is a framework for developing augmented reality games, you maybe know Pokemon Go or Ingress, and we started developing a framework that helps at creating such games. And we wanted to bind game objects like items and non-player characters and stuff like this in this framework to open-street map objects. So we didn't only want game developers to be able to just let items and characters spawn at given geolocations, but also add objects that are tagged on open-street map. And so we had to find a way how to get hold of the full set of open-street map live data, which is where there are basically two ways to do this. One way is you can directly access the open-street map API, and you can do this once, you can do this twice, but if you do this a few thousand times, they will sooner or later get a bit angry with you, or the servers will just not let you access it anymore. You can't do this in a centralized server application. And the second way we could have gone is we could have just downloaded the whole planet data from open-street map, imported it in a huge database, which is I don't have exit numbers, maybe one of you has, I think it's compressed in a tileball, it's one and a half terabyte or something, I don't know. So, and we didn't have the resources to handle this and we couldn't set up a database instance to work with this data. And then we came up with an idea, or let's say we thought what needed to be done. We wanted to have all of open-street map available, not just a subset, but we wanted to have all of it. So people creating augmented reality games on the server, they shouldn't be limited in what they could do with the data. We wanted the application itself to only load relevant data, not the full set. And the data should have, we wanted the data to be available on the local server at any point in time and not load it from the open-street map API every few seconds. There were a few cool add-ons that we wanted to add. First of all, we were using SQL Alchemy, which is an object relational map for SQL-based databases in Python. Who of you knows SQL Alchemy? This is great. For those who don't, as I said, it is an object relational map which basically allows definition of database tables and views in pure Python objects. And you can access them as pure Python objects, and the mapper will do all the work with creating the tables in the SQL database, maintaining relations with foreign keys and all of this stuff. We wanted to keep this transparent, so developers using SQL Alchemy could just use OpenStreetMap as if it were available in the local SQL database, even if it isn't. So let's say the application is querying for any object, tag, shop equals bakery, somewhere in a bounding box, and then they just make an SQL query and then OS Alchemy kicks in and says, oh, I don't have this data available, I get it from OpenStreetMap, and this is in the database, but this is all completely transparent for the developer. Yeah, okay, we can... This slide is a bit outdated because due to the cutting of the... to our workshop, I now reuse the presentations from... the presentations from another conference. But still, yeah, OS Alchemy is not yet so stable. We are using it in veripeditus, and it works quite well. It gets slow at some points when it gets many concurrent accesses, but it works quite well. Okay. So before we get to an example of how OS Alchemy can be used in a Python application, we implemented support for live OpenStreetMap data, so only the current data, not for historical data, because, well, there might be use for this, but normally in an application that users use, there is no need for historical data for old revisions of OpenStreetMap data, but only for the current data set. And for this live data, we support the basic OpenStreetMap data set, which consists of nodes, ways, and relations. Who of you is not familiar to some extent with the OpenStreetMap data model? How the map data is made up? Okay. So basically, it consists of nodes that are at certain coordinates, and you can put these nodes together to a way or an area, and you can put arbitrary objects together in a relation, and then you just extend it with key-value pairs that make up the whole map rendering and all the properties of these map objects. So it is very easy to model in a relational database. Okay. So we use the Overpass API in the back end, which is a bit, yeah, it works a bit better in this case, and it also allows public access a bit more than the base OpenStreetMap API. The most important component then is an SQL to Overpass SQL compiler. You know, Overpass uses this kind of domain-specific language to query tags and bounding boxes and all the kinds of data, but we, as I said, we wanted the developers to only need to use SQL, or the SQL wrapper in SQL alchemy, and so first thing we did is we built a compiler that gets an SQL query from SQL alchemy, translates it, it compiles it into an Overpass SQL program and sends it to the Overpass API, and then imports the resulting XML data back. And we implemented, of course, some caching so the OS-M alchemy can actually figure out that some data is already available and doesn't need to reload it from the Internet. So the basic flow in OS-M alchemy is, for example, we query for nodes within a bounding box. It kicks, it jumps into, it is triggered by the SQL alchemy module, intercepts the query, compiles it to Overpass SQL, then it gets a normalized hash of this program and looks in the database when this query was last run, if it was run at any time. If it was run recently, then it just returns and does nothing and lets SQL alchemy provide its data from the SQL backend, and if not, it does this whole importing Overpass SQL. All that. So here's a short example. For example, we want to get all bakeries within a bounding box. Then SQL alchemy is used like this in order to get, to query all objects of the node class and filter it by its latitude and longitude to create a bounding box. And, oh, this is a late-ish issue. This would have been a quotation mark in S for key equals shop. And this results in an SQL query object, like so. And then SQL alchemy takes this query and compiles it to this simple, to this simple Overpass SQL program. Actually, this compiler was written by Ike, who, no, there was no one who attended the very Peter's talk, actually, a fortune year old at this point who created this compiler. He did this. This was very cool, I think. Okay. So, let's look at an example. So, using the Flask web framework, this is not anything that has to do with OSM alchemy, basically, but we use it as a basic web application framework because it is easy to use and to understand at this point. So, this is basically everything we need to do in Python now to get the web application available. You can use whatever framework you like or no framework at all. We create the SQL alchemy object to access the database. This can be done with SQL alchemy itself as well. We are using Flask SQL alchemy here, which is a thin wrapper around SQL alchemy to make it fit into this micro web framework. And then all we need to do to add the OpenStreetMap tables to the database is just create an instance of the OSM alchemy library passing the database object, telling it to actually use the Overpass back-end, create the tables and run it. And at this point we have the OpenStreetMap tables available and we can do, for example, we can do queries on OSM.Node or OSM.Way just as we like. So, this is a slightly extended version of the program. It's a few more lines. What we added is this. This is for Flask Restless, which simply creates a restful interface, a JSON interface that you can query using HTTP. And so, with this simple program, we already have a program that gives us all of OpenStreetMap with a JSON interface with local caching and with not using nothing but a plain SQL database that we have and the OSM alchemy and the magic in the background. So, but at this point, we could already just, well, with no authentication and nothing at all. This is very basic, but you could now just go and create a web front-end using AngularJS or jQuery or whatever you like and just access the OpenStreetMap data with whatever HTTP, localhost, slash API, slash node, and then query for anything you like. And, yeah, this is not so exciting because you could actually do this with OpenStreetMap. You could actually query OpenStreetMap from JavaScript itself and wouldn't need this server, but it gets more important when we want to link OpenStreetMap data with our own data in SQL. And this is a very neat thing. We can now have, we can now put a foreign key in our own database table that maps to an OpenStreetMap object and it will show up in SQL alchemy as a relation. So, those of you who know SQL alchemy, which was most of you as I remember, this is a basic database model in SQL alchemy. It will, for those who do not know SQL alchemy, this will just map to an SQL table called review. In this case, which will have an integer column as a primary key called ID. And it will have a foreign key column called OSM node ID which maps to the OSM nodes table, which will be filled by OSM alchemy with live data from OpenStreetMap. And what we can do when we have an instance of this review class in our application, we can now just, let's say we call this instance myReview. We can just do myReview.osmNode.txt and then we have the dictionary of text of this node and we can just see in our application, in our object model what OSM node this is linked to. And then there are two columns, one for stars, which is an integer column and a unicode string column called comment. So what we did now is we can create reviews for arbitrary OpenStreetMap nodes like bakeries or restaurants or hotels or whatever. We can add a specific number of stars to this review just like you know from any review platform. And we can add a comment and this is automatically linked to the live OpenStreetMap data. And should we lose this OpenStreetMap data at some point in time or it needs an update OSM alchemy will do, we will always have a foreign key link to the real OpenStreetMap object here. And we can just, we can see it in the JSON, we can see it in our object model, we can see and use it everywhere, like so. This query gets, it queries for all OpenStreetMap nodes and filters them by ID. So we just get a single OSM node here with this query. This bakery is actually my favorite bakery next to where I live. And then we create an instance of the review class and provide all the columns we need. We provide an argument OSM node which is past the bakery object for the foreign key. We provide the number of stars and the comment and yeah, we add it to the database and commit it and everything is, everything is done. And now we have this, these two linked objects just as if there weren't any OpenStreetMap back in at all. Okay. And then for example, if we query the REST interface for this OpenStreetMap node, we also get a back reference. So we can now just query for OpenStreetMap nodes and we will automatically get all reviews from our local data model linked with this OpenStreetMap object. So we can now, for example, in the web interface just have an OpenStreetMap somewhere in a leaflet map or something and have the user click on any OpenStreetMap object there if we have a data layer. And then we can ask our review service, our review web application to return this OpenStreetMap object and it will be filled with the back references to our local data. And yes, we are recreating yeah, put this. Okay. Do I have time for a live demonstration? No, not really. Okay. What? Five minutes, yeah. Yeah. Okay. Is there anyone here who did not get the basic idea and needs me to show this in practice, how this will look when I access it with a browser or a client? Okay. Okay. Before we get to the question or feedback minutes. So as I already mentioned in the introduction, this is not so much about actually handling geospatial data but it is about getting the OpenStreetMap data into your application and you can use it for everything you like. You could actually add post-GIS to it as an additional layer and then you get all the fancy geospatial querying functionality that it provides. You can of course do your own calculations with the geodata from OpenStreetMap. You can link it with your own geodata in the database just as you like. Okay. So maybe let's skip to this first. You can find OSM Alchemy on GitHub. I am not so sure whether this URL still works. I hope that GitHub still redirects it. If not, you will find it in the very pituitous organization. You can find an example use of OSM Alchemy at the very pituitous server application and if you have any questions or want to play with it or try it out or have any feedback, just report an issue on GitHub or just ping me by email or Java. There are more contact possibilities in the GitHub repository, I think. All right. So what we are, as I said, we are using it in the augmented reality framework, the very pituitous. You can try it here on campus, actually. You can go to nightly.verypituitous.org and to Gist and you can play an augmented reality game that uses OpenStreetMap data from this, from OSM Alchemy to, yeah, provide an augmented reality game here on campus. And the next project will be creating a basic review and check-in service like the, like, Foursquare or Yelp where I can review any location or can check-in at locations using OpenStreetMap data just like I showed in the example. Yeah. We will see what turns out of that. Yeah. Maybe I can get some feedback from you for this idea. Any ideas what you think could be done with OSM Alchemy, anything that you think is a bad idea or a good idea. Actually, when we started this, we weren't so sure whether it would work out in the end, if it could be a good idea to add this much transparency and abstraction to it, but it turned out to work quite well. But maybe there are some critical voices who can warn us that something will fail at some point in time or something good, everything could happen. So, any questions, ideas or feedback? Yeah, hi. The question was how we could expose the GraphQL interface from OSM Alchemy. We haven't tried this, but we could or we would even be more happy if you could actually try. OSM Alchemy is limited to creating the database entries on the fly, and the rest is pure SQL Alchemy. We didn't modify SQL Alchemy. We just add triggers to get the data online. So, if what you imagine to do with GraphQL works with the basic SQL Alchemy, it will also work with the OpenStreamer player that gets into it. So, neither SQL Alchemy... Well, SQL Alchemy doesn't know that there is OSM Alchemy. It just runs the trigger code and then it forgets about it. Yeah, it's you. Actually, well, you could also throw OverpassQL queries into OSM Alchemy. We do not expose this API, but in Python you can use whatever functions from a module that you like. So, you could do this. We do not expose it right now because we really only have this transparency through SQL Alchemy. But you could, yeah. We could probably expose it as well if it is important. Yeah. If the local bakery disappears, then it will at some point disappear from the database. It will be, if you enable caching in OSM Alchemy and it doesn't look at the object within, let's say, a day or so, then it won't vanish for a day. But then it will, of course, vanish, yes. But there, because OpenStreamer objects could vanish, we did not add hard, foreign key constraints to the data model. So, your database won't get inconsistent when the OSM nodes vanish. So, you will still, in this example, keep the reviews and you can probably link them to another OSM element in some maintenance code or something. I'll just drop them here. Okay. I am a bit short-sighted. Sorry. So, any more questions? Yeah, at the back? Yes, it can. The question was whether it's, whether OSM Alchemy can work offline without accessing overpass. And yes, it can. Of course, you will have to make sure to get the data you need.