Exploring Germany's Urban Geography with Census and OpenStreetMap Data

Speaker 1 [00:06]

Okay. I would like to introduce our speaker today, who will be speaking on exploring Germany's urban geography with census and open street map data, Mr. Travis Hathaway. Please clap hands for him as he's starting to speak. Yeah. Welcome, everyone. I'm really excited to be here. So today I'll be talking about, as promised, census and open street map data and moreover the topic of urban geography. And before I start, can I get a quick hands? How many people here would say they come from the field of social sciences? All right, great. And so as the other people coming from more technical sciences, like a computer science background, these kinds of things, maybe show some hands. Okay, yeah, cool. All right, so mostly technical crowd. This is actually really good because hopefully I can be introducing the subject to a lot of you. So let's get started. So who am I? Well, by day I'm a professional software engineer, but by night I pretend like I'm a social scientist. So yeah, I call myself an amateur social scientist. I actually did my bachelor's and then my master's in the field of social science, and most recently I completed a master's degree in urban sustainability at the University of Kiel in Germany. But what I do for my day job is I work at Anaconda, and I specifically focus on condo packaging. So if you have any questions about that kind of stuff, feel free to come check me out later at the Anaconda booth. We'll be happy to talk your ear off about condo packaging. And before we get started here today, I really, really want to give special thanks to our unsung hero, and this is my Lenovo ThinkStation P340. And this guy has been... All the wonderful data in this presentation, this guy has tirelessly cranked away. The fan has been going night and day. It's how I know that my calculations and my data science is happening, so it's really cool. It has 32 gigabytes of RAM, 16 cords, and has on several occasions promised that it will not join the coming robot rebellion against humans. So everyone can, yeah, yeah, a round of applause, please. There we go. All right, good, good job. Cool. So as I alluded to in the intro, today I'm going to help you guys think like an urban geographer. And to help show how to do this, I'm going to show my own personal journey of how I made a medical emergency facility accessibility report in Germany. So, yeah, hospitals. And yeah, so before we get started, and because we have a lot of people here with a more technical background, less social science background, I wanted to give a firm definition of what urban geography exactly is, and this is a field that examines various aspects of urban life, including physical, social, and economic perspectives. And so studies in urban geography often include things like the physical environment, so things like urban sprawl and land use patterns, other things like infrastructure, transportation, utilities, and public spaces, and even the evolutions of city layouts as they change over time. And the other really important area of urban geography studies are social and cultural, and these studies often have to do with the equitable distribution of resources. So who in cities have a lot, who in cities don't have a lot, and whether or not that's right. It usually isn't. Yeah. And then these other studies include measures of vibrancy and livability. And finally, we also have what the nature of public space is. And the last category of studies, and by means not all, but just to give you guys just a couple examples of what these studies look like, are studies of urban processes. And so here we have things like studying urbanization and de-urbanization, so how cities develop and then how they slowly fall away and crumble, and other things like gentrification, so how rent prices rise and maybe hopefully lower someday. And then we also have the effects of natural processes on urban spaces. So these are things like how does climate change interact with cities and how do cities prepare for that kind of thing. Cool. So today, I'm going to go through three separate phases as we kind of go together to create our own study of the urban environment in Germany. First, I'm going to talk about laying the foundation. And in this phase, we're going to compile a Postgres database with all the data that we'll need to then ask questions and do our research. And after that, we'll actually talk about how do we even ask meaningful questions. We'll talk about what it looks like to look at previous research and derive our own questions from that. And then finally, and maybe most important, we'll talk about analyzing and presenting the findings of our research. So let's get started by laying the foundation. So as I said in the intro, we're going to be using census data. And something wonderful happened in the year 2024 on the 15th of June. The country of Germany and their statistics office published the Census 2022 data set. And this is a wonderful data set that includes data available at 10 kilometer, 1 kilometer, and 100 meter resolutions. And one of the unique things about this new data set is that it included this new 100 meter resolution category. This is really great because it allows us to look at things like population and immigration background on basically a per block basis. This data set comes to us available as CSV files, which is very, very great, and I'm super grateful that the statistics office makes this data available. But I had one big problem. I personally prefer this wonderful dancing elephant and using PostgreSQL because when When I'm doing a research project, I like to have all of my data in the same place, and I don't prefer it to be spread across CSV files all over my hard drive. And so in order to bring a little bit of sanity to organizing and importing the census data, because it's, I think it's, yeah, it's about 40 different files, I created this custom tool called census2pgsql, and census2pgsql is, yeah, it's a command line tool written in Python, take a picture of that, and it is, yeah, don't worry, there'll be links later, and it has a couple different commands, so it has a list command, a create command, and a drop, and with the list, you can list all the different types of datasets, with create, you can import this into your Postgres database, and drop was really useful for cleaning this out of your database in case you need to remove all traces of your research, which maybe is necessary for some of you. Just kidding. But yeah, let's look at a table, let's look at one of these tables, and so right now we're looking at the population in five different age groups at 100 meter resolution, and so it'll create these tables in the wonderful Deutsche Sprache, so yeah, I didn't want to go through the trouble of translating, because hey, it's a German data set, but what we have in here is we have the five different age groups, we also have the total population, And then we have the really important geom data point, so that's how we're going to do our spatial analysis later. And if you want to install this yourself, it is available as a conda package, of course, and you can also install it with pip or uv. And that adds census to pg-sql. That was the first sort of block I needed in order to do this data analysis. We have all this wonderful data about Germany's population, but I needed even more. And for that, I turn to OpenStreetMap. And specifically, I turn to a tool called pgosmflex. And pgosmflex is a simple wrapper around osm to pgsql. And these are both tools that... So osm to pgsql, you can think about as kind of... It's a low-level tool that allows for the importing of big data sets of OpenStreetMap data into Postgres. And pgOSMflex is a wrapper around this that offers a more opinionated view of how the OSM data should be represented in the database itself. And it can be used to import an entire country's worth of data, it can even do a whole planet if you'd like. I heard the OSM project is currently working on Mars, but we've got to get people out there first to collect data. No, I'm just kidding. Yeah, but this... And so when you have a database like this, it's really useful for exploratory data analysis. And I'm going to show you an example of the different types of tables that you have in the database once you get it imported. We can see that we have amenity line, amenity point, amenity polygon. So these are just categories of different places. So amenities would be like schools, police stations, hospitals, that kind of thing. And then we also have building and building points and building polygons. And then we have POI for point of interest. and so this is an example of what one of those tables looks like and if anyone's ever done an import like just a raw import of OSM to PG sequel you can tell immediately that this is a lot more nice neatly organized we have some nice fields like OSM type and subtype and then we have house number streets for the address and then the all-important geom yeah because normally with OSM to PG SQL, it will import, yeah, there's several ways that you can import it, but you have to really tell it how to do it, or else you're gonna get a pretty unorganized mess of columns. So, yeah, let's keep going further. Now that we have both the census data and this OpenStreetMap data, I wanna know what can I actually do with this? And so for just a practice run to do some exploratory data analysis, I decided to ask this maybe silly question. How many Reva's does Germany have? And so I ask this because I keep telling people, oh yeah, this is my favorite supermarket brand, so Reva, if you're watching, I would really enjoy some Gutschein, you know, if you can. Yeah, but so, and also, but this gave me a great opportunity to get to know the data more in OpenStreetMap and just sort of answer a simple question and also do queries on the entirety of Germany. And so in order to do that, I created this sort of maybe scary-looking but actually not that crazy-looking query, and first what we do is we do a query to gather everything from the POI table, the polygon, and then we have a couple different items in our where clause. So where the name is roughly equal to Reva, And then, here, this line, we're excluding to go, express, getrenke, and ophold, because I wanted to just have the supermarkets and not all these little gas station mini-marts, right? Because that's not an actual Reva. Those are like mini-Revas. And then, finally, I decided to include shop and supermarket by using the type and the subtype fields. And then, the other thing that I had the union to this is the points. And so this sort of reflects the maybe a little bit inconsistencies in the OpenStreetMap database because sometimes, most of the time, ravels are gonna appear as a polygon because you're gonna actually have the outline of the building in the data set. But sometimes they actually just appear as points. And I would say that they appear as points a minority of the time. But yeah, anyway, you have to account for that when you're doing these queries. And when I got the account for that, It gave me a grand total of 3,750 revas. And I got that number, and I was like, great. How do I actually compare it to make sure it's correct? And what I did is I went to the official reva website, and they stated it's about 3,800. So for me, that's pretty close. And remember, everybody, this is social science research, so it doesn't have to be that exact, right? We're allowed to, you know? Yeah, yeah. But yeah, so now that we know where the Revas are, how do we even go further with this analysis? Well, because we have our census data, we can actually do, we can ask questions like, yeah, like which Bundesland has the most Revas per capita? And we're all sitting right now in the Bundesland with the most Revas per capita. That is Hessen. So congratulations, Hessen. At 0.74 Revas per 10,000 people, you have the most in the entirety of Germany. And the Bundesland with the least is Sachsen-Anhalt, so they have the least, but, you know, obviously, Sachsen is not far behind with 0.27, so, yeah. And then we can even see, but if you look at total Rewe's, right, obviously, Nordhain-Westfalen wins at 828, but they also have the most population, so, yeah. But this is all well and good. And I was really happy that I did my little Reva analysis. And you can actually read even more on my blog if you want to later. But I thought to myself, hey, you know, I'm an urban geography researcher, not a marketer. And I want to ask some meaningful questions. And not just meaningful questions, but meaningful research questions. And so I gathered four different points that aren't, they're just more like Travis's rules. And remember, I'm just an amateur social scientist with a master's degree, so take all this with a little bit of salt. But my four rules for creating meaningful research questions is to first, account for previous research. And this is super important, right? do your literature review, go out and read. And so when you're looking for things to research, the best thing you can do is just spend a good month just diving into academic research articles, figuring out what's currently being asked, what open questions there are. And then as you do that, you're going to sort of develop a natural instinct for fitting this research question in your desired field of study. So the more you go out and you read urban geography journal articles, and there are a ton of them out there, you can sort of get a little bit of an intuition for, hey, would this question actually fit within this field of study? And then when you finally formulate your research question, you want to also say, hey, how is this question going to add to the existing body of knowledge? And so for that, one of the things I really like to do is always go look at the discussion or the conclusion section of a lot of these papers, and you're going to find questions that weren't addressed or questions that arose out of the process of doing research. And so all good research questions, after you get done doing the research, they should generate even more questions because that's what the pursuit of knowledge is all about, right? So, yeah. And then finally, you get bonus points for picking something topical. And so if there's something going on current event-wise or if there's something being debated right now in the public, for example, in Berlin, I now see all these different signs about the auto-free, yeah, the auto-free sort of, how do you say, yeah, whatever, it's basically the zustimmung, the voting thing that they're doing right now. But anyway, so yeah, if you were to pick something related to that, that would be really cool because it's topical, it's something that people care about. And so with all that in mind, I decided to focus my particular research topic on the issue of spatial accessibility, and I just want to give everybody a quick overview of what that topic entails. So this focuses on the distribution and availability of crucial amenities in urban environments. So this means like, hey, these are studies like food deserts, this is studies like are there enough schools near families with children, and all these types of things. And this can be used as a way to promote equitable distribution, and just like the things with food deserts is that you can say, hey, is there any population in the country that is facing a systematic disadvantage when it comes to access to these resources? And so these studies are really great ways for promoting equitable change in our societies, which is really good. And these studies often use a data-driven GIS analysis, which if you haven't been paying attention, we have this wonderful database with all this great GIS data. So yeah, this felt like a perfect fit, at least for this conference talk. So I was trying to pick a topic. I wanted to have Germany as the entire study area. I wanted spatial accessibility as an investigation means, and then I wanted to focus on a single amenity. And as I already said in the beginning, I ended up choosing hospitals for this. And one of the reasons I chose hospitals is because I wanted to stay topical. And for that reason, I'm going to bring in this lovely, lovely German word. But this is the Krankenhaus für Sorgungs- und Besserungsgesetz. And this was a law that was passed by the previous coalition in 2024. And this was a law that was a major reform of how hospitals in Germany are financed. And this is a controversial law because proponents say this will help consolidate funding to a core set of central hospitals, but then opponents say that no, it's going to lead to hospital closures. But then proponents say yes, that's exactly what we want. We'd rather have one very good hospital than three bad hospitals. yeah and so furthermore this is a law that will instead of funding for how many hospital beds a hospital has it'll change the funding to what type of services they offer and yeah so let's and if we look at the data in Germany and we look at the actual health care expenditures of the percentage of GDP we can see why they want a change we can we can see a very slow increase from 10 years ago, about 11%, to now at 12.3%. And perhaps more alarming is that, as a percentage of GDP amongst the top OECD countries, Germany is number two, only second to the United States, which is by far the most. And I come from the United States. You do not want to be like the United States. That's why I live in Germany. but and then the other but the other but the other thing about this um that was interesting is that germany also has the second highest number of hospital beds per 1000 inhabitants and so um all of this is why the supporters of this law say hey we have too many hospital beds they're too expensive to operate um and this and therefore we want we want things to change this law is still being currently debated even as of this year the current coalition government was passing some amendments to this law that made some changes that delayed the implementation of it but this is needless to say this is an ongoing debate and so with all that in mind and with the potential for hospital closures I felt like this would be a really great time to do a spatial accessibility study of hospitals in Germany And moreover, I wanted to see whether any age cohorts, specifically older age cohorts, face any sort of level of systematic disadvantages when it comes to access. And so I was almost ready for the analysis, and I needed to figure out where these hospitals actually were. And I started having trouble. So I was running this query, fairly similar to my previous one, to try to find all the hospitals in the OpenStreetMap data set using the tag emergency, yes, yes please, give me the emergency ones, and then also doing the same thing where I was doing the polygon table for POI and then the point. And I got 823. And I thought, okay, great, got all the hospitals, good. I can move on with my life. But like every good social scientist, I was actually doing some validation and verification to make sure this data set was actually real. The more I looked at this data, the more I realized there were some big holes in it. And specifically, places I knew that were actually hospitals and emergency rooms, like this university clinic in Kiel, where I studied and actually have visited this hospital before, I think I got my COVID shot in the emergency room there, it wasn't in the data set. And so I was kind of freaking out, running around, flipping tables, doing all the things you do when you have bad data. And at that point, I kind of said, okay, I need to find an actual different data set for hospitals in Germany. But luckily, there's this amazing... Yeah, so the German statistics department again came to the rescue and provided this wonderful data set, the Versichernis der Krankenhäuser in Germany. And this is a really cool Excel spreadsheet data set that gives you all the different hospitals and clinics in Germany. And after I got done doing my analysis and figuring out how many emergency rooms there are in this data set, it was 1,113. So this is clearly a difference between the OpenStreetMap data. And the other thing is that this data set also introduced this idea of levels of care. So we have 1, 2, and 3. And this was also something introduced by some previous laws for better hospital reporting in Germany. And so the cool thing was, was that, okay, I didn't, that data wasn't in the OpenStreetMap data set, but it was here, and now I have even more information about the hospitals to do an even richer analysis. So at the end of the day, it was good. Maybe I can come back someday and actually update the OpenStreetMap database. I want to get involved more with that project, so it could be a really cool thing to do, yeah, to get better information about emergency rooms in there. But anyway, I had to go on, and the study must go on, so yeah. I simply added to my Postgres database, I created this new hospitals table with a really, really simple table with just the care level and then the geometry there, and I was able to get going. With the last step, and this is the analyze and present phase, and here I want to take a brief moment to talk about open and reproducible science. And so as I was reading all those papers and coming up with my research questions and all that good stuff, I was frustrated because I saw lots of really great science, but I didn't see very much code out there available. And so what I told myself, and because I work in open source, I said, hey, I want to make sure that everything that I do is written in a way and published in a way that anybody can read it, investigate it, use it, adapt it. And so that's exactly what I did. All of this stuff is available on GitHub, and it's open, and it's licensed under an MIT license, so you can do whatever you want with it. And it's also organized into a CLI program so that you could perhaps use it and adapt it to yourself. And I was going for trying to make it easy to use for people other than myself, and maybe my future self in the future. But yeah, and what does that program look like? It's a simple CLI program called EMSDE, and it has two commands for the analysis and then the report, and we're going to talk about the analyze command right now. But before we do that, I'm going to talk about the last tool we need, which is the open route service, and the open route service is something that you can install locally. It's a Java program that runs and can do routing for an entire country's worth of data. So it's really, really useful. Yeah. And yeah. So let's look at the schematics of how this program actually works. So in the center, we have this nice Python CLI program that is reading from a Postgres database and then querying out to the open route service. And what it is responsible for doing, it's responsible for taking the census and then the hospital data sets, reading them, and then finding the closest. So what we do initially is we run a Postgres or a PostGIS query to do a nearest neighbor search for the census and the hospital points. And then we take those point combinations, we query open route service, and then we write that to a travel cost table that has the cost in seconds. And so from the travel cost table, we use that to generate all of our reports. And the open route service, yeah, where open route service comes into play is that, like I said, initially, we just have from post gist, we have the nearest neighbor points, so that will give us a straight line distance, but then with open route service, we get the network distance, yeah, and we also have the cost in seconds. And then additionally, it's really nice, I didn't choose to use this data, but it could actually be really interesting is that I saved all of the routes as well along with that. So yeah, it could be really some fun stuff to do, yeah, when you aggregate those routes and stuff. All right. And also too, the really cool thing about saving the routes is that when you do this sort of analysis, It's good to have ways to check your work visually. And so a lot of what I was doing and the way I even generated this map was that I would have the Postgres database and then use QGIS in the front as a front end and then being able to go in and investigate individual points. And this was really, really valuable just to make sure that everything was going on well. And so Postgres with PostGIS And then QGIS is a really, really nice pair to have in order to verify the work. Yeah, and it's interesting because my workflow was basically QGIS to have a nice front end to quickly inspect the data points, but then having the CLI program doing most of the work. So yeah, a lot of the travel cost stuff wasn't actually done inside of QGIS. It was done as a CLI program. But yeah, that's my own little special setup. I know some people actually like to do the analysis and QGIS itself, and it's totally possible, and there's lots of great tools there for that. Cool. All right, so now we can actually take a look at the results. So drumroll, please. But yeah, so what we're looking at is the population share within 60-minute, 30-minute, and 15-minute thresholds for any hospital in Germany. And some good news here is that for all age groups, everybody appears to be within at least 60 minutes of a hospital, and that's any type of hospital. And then when we go down to 30 minutes, there is a 98.4% coverage. And then what you're looking at, too, is this is the variance amongst age groups that we'll take a look at in the next slide. And then when we look at 15-minute access, you see it goes down, and there's also a bigger variance amongst age groups. But I think most, and this is a thing that I would like to research more, but yeah, 30 minutes seems to be a pretty good target. And this is also, yeah, I should also mention, this is traveling by car. So, cool. And then if we look here, this is the age split. And so if you remember from the research question, that was something important that I wanted to ask is there any significant differences between these five age groups. And so what we can see is that the... And you're looking also, too, at the 15-minute difference because, yeah, when we're looking at 30 minutes, it's actually so small you can't even see it. But as you lower the threshold, this difference gets more pronounced. And so what we can see is that the 18 to 29 group is 71.3%, and then the 65 and up is 64.8, and then the lowest group is 63.9. So that's interesting, and that's for access to any hospital in Germany, but let's take a look now at the different care levels. So for 1, 2, and 3, and we can see that the 60-minute story is pretty good, nice coverage, but where this sort of starts to look a little bit more interesting is the 30-minute level, and now we can see that there are some differences here between the level one type hospitals and the level three type hospitals. And also, too, the general rule is that as we lower the threshold and as we go up in level, the variances also appear to be getting greater between the age groups. And we can even see now the difference between level one, two, and three and the different age groups and how this looks. And one of the things that struck out to me was that the 18 to 29 age group does appear to be consistently higher. And one of the things that, I mean, this is me just being kind of armchair statistician. I'd love to dive into it, but I fortunately didn't have enough time. But I think that this group of people is more likely to live in highly urban environments. And what we see also with the data set is that highly urban environments have just better access to hospitals because, yeah, that's where these environments are. And also another thing is that a lot of these hospitals, especially the level threes, are university clinics, and so who lives close to universities, right, it's this age group, 18 to 29. So yeah, but this is really cool because we can dig into it a lot deeper. Yeah, so like I said, all good research projects just make more research questions. So to answer the question in the end, was there a difference? I couldn't really find any. when I was looking at the age groups, so yeah. But unfortunately I wasn't able to find a difference, actually, not unfortunately, it's fortunate. It's nice when you can't, sometimes it's nice when you can't prove something because that means that things are, yeah, tending towards being equitably distributed, which is good. But yeah, but one of the final things that I want to introduce today before I end, and maybe the best actually, is the map that I made in order to visualize these, because those numbers are good, and it's nice to see that, but I think sometimes a map is much more capable of actually telling a story. And so with a map, we can actually begin to see where there are regional differences, and so with this slider, I can slide to where there is 30 minutes, yeah, for people who are greater than 30 minutes, and the other thing I can do is I can even select the level of care so I can do two and three together and so when we look at this map right we see I think a lot of people that are from Germany might not be surprised at what they see at this map we can see that what Bundesländer are kind of maybe disadvantaged when it comes to two and three hospitals well it's the Bundesländer in the east right and so and unfortunately Unfortunately, the East German Bundesländer are often sort of more disadvantaged when it comes to these types of things. So this would be a really cool thing to research further. And yeah. And the cool thing is, is with this map, we can really zoom in really, really far, and we can get these 100-meter census-level details, and we can hover over each individual point, And we can see the times here, and we can also see the age split, so the demographic split here, for this little village, so Bad Dilsnack, is anyone from Bad Dilsnack? Okay. Yeah. But I encourage everybody to kind of check out this map afterwards. This would be, it's a really cool way to sort of get a good overview of, yeah, of maybe your community or someone, yeah, anywhere else in Germany, and I would love feedback on it, too, if you guys have the time to leave any feedback. And the other thing, too, is that this map, I'm currently working on this. There's actually a full write-up with some more data visualizations that you can play around with that I didn't talk to today. So yeah, you can please feel free to check it out. Okay. Let's go to now the limitations, right? Be careful. Here's the there be dragons portion of the talk. So what I want to do, I wanted to ask, so I'm talking about these thresholds, 30 minutes, right? But does living closer to a hospital actually improve health outcomes? I think from some of the previous features I found, yes, it does. But even this assumption that we're making about this type of map, right, deserves more attention because perhaps, yeah, maybe it doesn't matter if there's a level one because people can be easily transported to a higher level facility via helicopter, which is the case in Germany. And Germany has a really extensive system of helicopter transport for patients in really life or death emergencies. So that deserves more attention. And for these reasons, it's really important. You don't want to draw any sort of concrete conclusions of this analysis because it's just an abstraction of reality, right? So, this was a measurement that was taken in 2022 with these types of hospitals. So, the data has to be really, really, really accurate for you to have, like, a super realistic view of it. And so, for that reason, these tools should always be taken with a little bit of, like, a pinch of salt because they maybe would hint where you want to look further, but yeah. But they don't, I would never look at such a tool or these statistics and take them as sort of some absolute truth. I would always, always try to figure out what data is hiding underneath this, how is this data collected, how could the data be improved, and always looking at the quality of it. Because yeah, We need to make sure that as much as we want to research phenomena, especially in urban geography and especially when you do data-driven research, you also want to make sure that you're not trying to simplify reality, because reality is really ugly and messy. So that's my big, big point. And then, like I said, these tools are only as good as the data that you feed into them. And then some of you might be wondering, hey, this sounds great. I want to be an urban geographer and I want to go out and do some projects. That's awesome. That's what I hopefully inspired some of you to do today. I would really encourage some of you to check out your local OpenStreetMap group. This is something I want to get more involved with. I'm actually going to try to, I live in Berlin, so I'm going to try to reach out to the groups there and start doing some mapping because, yeah, I was recently at a FOSSGIS conference and this conference was really inspiring. There's a lot of people doing really cool things with mapping, and a lot of people doing cool things with open source mapping tools, so. And the other thing I would encourage people to do is to find a data science for good organization. There's a lot of these floating around. I would, yeah, I would always, always encourage people to try to work with other people and bounce ideas off back and forth. Because I think one of the biggest mistakes that I made with this presentation and this research is that I didn't work with anybody else. This was just a solo project. So that's something that I personally want to change in the future, is to more collaboration for these types of projects. And then, yeah, the other thing is focus on local issues and share your research at community meetups. This is a really good way to reach out to people, make sure that your research and your hard work doesn't go unnoticed. And I found local issues to be the most important because you all have intimate knowledge of what's going on in your own cities. And yeah, you should all feel empowered to develop tools that can make a difference at the policy level and also get involved with local politics. So this is something I would really, really encourage people doing going forward because we have all these wonderful public data sets out there and it would be a shame if they just sit there and aren't being used to actually fight for policies that make our cities better, right, and are actually based on a solid foundation of facts, right? So, yeah. And one of the last things is all of this would not be available without all of these amazing open-source projects, Python, of course, and, yeah, Postgres, and just, yeah. So thank you to all these wonderful, wonderful projects. Please, if any of these are ever in need of help, feel free to contribute or reach out to the maintainers. That was it. Here's where you can find links to all these wonderful things if you want to check it out. If you want to see me after the talk, feel free to tap me on my shoulder, say hi. I'm a friendly guy. I definitely don't bite. Thank you, everybody. Thank you, everybody. Thank you. Questions? How do you match the addresses in the public hospital data set to the OSM and census data? Getting coordinates from addresses sounds fragile. Yeah, so the hospital data sets, that's a really good question. I went through actually a couple of iterations on this because I didn't actually start with the Kronkenhaus was like this. I started with this, it was another data set that was the hospital rating system. And so, yeah, and so I did do geocoding for all the addresses, right? And yeah, that's fragile-ish. I really wish that the data set included geolocated points, but unfortunately that was the reality I was living in. Yeah, if anyone had any idea for how we could verify these points, I would, because a really cool thing about that data set specifically is that it's released under an open data license, and so what I actually thought about doing in the same way I created census to PG SQL, I wanted to create a like this to PG SQL. So I wanted to create everything to PG SQL, right? And the cool thing is that if anybody's interested, we could create a GitHub project that could take that Excel spreadsheet and load it into Postgres and then do the geocoding, and we can even save the geocoding on it and then help and distribute it, and maybe even approach the statistics office so that they can even link to and support such things. So, yeah, I hope that answered the question. Yeah. Cool, thanks. Another question. Any weird adverts you spotted in the census data? flipped coordinates and things of that nature yeah flipped okay so the yeah i don't believe that i found any um sort of anomalies like that because i would hope that if there were flipped coordinates so what what you can do basically is you can load all the points in the cugas and then you can just zoom to extent right and then you have all these points and so every time you know i was doing that right it zoomed to the extent of the data and yeah i didn't because if there was a flip coordinate somewhere right it would you'd have some coordinate way off somewhere else so yeah i mean that's why i was stressing before like when you have these data sets it's it's a lot of times it's really important to just do some sanity checks by visualizing it and looking at things on a map because then you get a nice overview of all the data points and you can things that in and the cool thing is that you can actually make visualizations where anomalies sort of immediately pop out at you. And I can even show you one thing here. One of the... So there's an anomaly right here. This big red spot. And let's zoom in on it. And this is the one mistake in the data set that I didn't have enough time to quick fix. But, look, it's red, it's red, it's red, it's red, it's red. Oh, wait, where did it go? Okay. That's because when I was doing the routing, that, it was, it must have snapped to somewhere on the routing network at a higher level, right, because this is the one kilometer level, it must have snapped somewhere that totally didn't make sense. But then at a lower level, it goes away, right, because then you have finer grain resolution. But anyway, so that's, I mean, that's just one example of how you can create a visualization to find anomalies for you. And then you can And also obviously you can do added test and write test to find these anomalies as well. But for me, and just being that this was an amateur project that I was doing as a hobby in my free time, yeah, that's kind of the level of, yeah, you know. Okay. And what did you use to build the map? Oh, the map? So the map was built using the wonderful OpenLibre project. And excuse me, not OpenLibre, MapLibre. And this was really cool, the vector data was also using vector tiles, and for that I was using Tippecanoe to generate the vector tiles, and that was coming straight from a Postgres database? Yeah. Anyway, but it's all, all that code is available on the project website, so you can check out all the details there. And then the readme is a little bit skinny right now, but I was just working really hard on getting the project up, ready for the slides. So yeah, I'm going to be adding to the readme and I'm also going to be making it so that it's more reproducible and easier to just kind of like copy and run. And I'll be using the, I'll be using conda packaging to do that. So yeah, if anyone's interested in how that's going to go.