When SQLite Has to Be Enough: Practical Geospatial Analytics Without Big Data

Intro

Opening

0:04 Okay. Um, yeah. Um, thank you for the opportunity to talk to you again. I’m sad that I cannot be uh here with you u today in person, but I think that it’s uh more uh responsible if I don’t uh infect you all with my uh infection. So, let’s start. Um, I’m talking to you about um a very

Two data worlds

0:30 interesting uh work that we did with a client. But first, I think I need to um bridge the gap between what you’re probably uh used to and what is still a reality for large part of the German um economy. So if you’re part of the or if you um often go to these tech meetups, you probably hear or you even work in these companies, you hear stories about like companies like Zando, Sum Up and all these big scaleups and startups and then you hear all these interesting things about event-driven architecture, scaling, optimize, automate, optimize and all these very fancy things. The funny this is also one part of Germany but one the other part of Germany is where we still use um fox for example or we not really um up to uh the technological development. So there it’s not so much about scaling it or optimizing it but just get it to run with what you’ve got. And this is one tale that I want to share with you. For example, um this is the same story again. No, like in in the scaleup world or in the tech industry world, how do we scale this? How can we make it faster? How can we make it make it better? And um in the other world, it’s more like how do we make it work with the stuff that we have? And for this um I want to tell you about um a municipal um heat

Municipal heat planning case

2:01 provider and what they have what uh there for example um they have to do what they have to do is they have to decide um how do they um build up the remote heating networks and this is this is not a joke. This is really what what’s going on there right now. They have um a shareepoint and the shareepoint they share shape files. I’m not sure how many of you have know about giz or stuff like this but shape files are basically a special data type file where you can use uh geospatial data. They manually copy it to their local um CUGIS uh develop um installment and then they do their analysis and they work with a forecast from 2016. They’re not using a model from 2016 and update it with new data. They use a forecast from 2016 about uh what they should do next. And the analyst is so to say the orchestration layer. And this is very different maybe for for most of you what you encounter in your everyday life but this still is happening in Germany. And of course if you’re a tech person you want to do okay let’s build a new um data infrastructure let’s do all the lakehouse whatever um build maybe use data bricks or something like this and fix the data and the data infrastructure. But we really need to understand that first of all uh they probably don’t have they neither have the budget nor they do they have the the people infrastructure to deal with it

Decision before infrastructure

3:41 and even then it would take a very long time to get it running and right now they’re still working with the prediction from 2016. um this is not really fundamentally helping them with their problem. So what we have to do first is like really understand what is driving um the value here and the value here is getting better predictions not necessarily get a better data infrastructure um because that’s also a very interesting aspect of it. Oh I’m am I still here? Okay. Um sorry uh I think you’re sharing the wrong screen because we don’t see the slides moving. Okay. Uh let me try to is it better now or should I share Now you are on not every data set needs a pipeline. Is that correct? I think so. Yes. I I think we lost the previous four, Uh can you see it moving now? Can you go in the slide show mo mode in presentation mode or something?

5:02 Uh okay. does it let me let me share again. Let’s see if this helps. Is it Can you see now? It doesn’t go full screen for us. eating 101. Um, you should just share what he sees the whole screen and not only Oh, are you sharing one window or the full screen? I understand. I try. I try. yeah, you have to share the full screen this is why it was not moving.

Slow- and fast-moving data

6:07 Yeah, it’s a bit more messy but okay. Now, is it working? Appreciation from the room. Okay, let me see where we’ve been. Okay, so one very interesting part also about the problem is that we have very different time scales on which the data is changing because most of the data is actually not changing at all over very large um time frames. For example, the parcels are not going to change. Um, the building geometry, the network location, the streets are not going to change and many other properties of the of the property are not very ch much changing. But some aspects that can be very interested if you if you um try to predict which or try to plan the the heating network can move quite fast. So for example um if you followed the German politic discussion about um heat pumps for uh this can change very rapidly. One day it’s it’s uh you get lots of subsidies and everyone is very um excited about it. The next time there’s a big campaign in the in the newspaper and excitement significantly um diminishes. And this can or energy prices are also very fast moving for example in the political landscape that we have today. It can even be rel regulatory changes. And uh this is um very important for the question whether you should um build new remote heating networks strongly depends on the uh on the attractiveness of the other um options. for example, heat palms I’ve also mentioned or other forms of heating maybe even um wood pellets and stuff like this. So we have these two two um very different or very on different time scales changing properties and that we have to deal with and um then we need to decide what we actually want to predict. So, for example, you’re you’re a city planner or you’re working for the for the municipal um company and you’re um trying to predict where you should build or um where you should build the next uh pipeline. One question is so example one part one one one thing is that is interesting is how likely are the new uh parcels that could be connected to the heating system are actually willing to be connected uh to the heating to heating network because if they are not connected so to the heating they are not your customers and you don’t make money in it. But this is only one part of the equation because uh you can imagine that if you connect a single a single um living house with this network, this is a very different um situation than if you would connect a very big industrial complex to your heating network. So um the connection

Connection probability is not value

9:22 probability is very important but it’s only part of the story. So if we want to really understand how we should extend the network, we need to understand the probability that they’re actually going to uh that they’re willing to connect um and also like the expected um the expected money that we can make. And for this we will use um what we actually built is we built two different models. We take the we take one model that predicts the probability that this parcel is going to connect to the network if we build a connection and the other one is a model that predicts the actual um so we had the probability and the other one predicts the amount of money that we can make with it in in a 5year period for example. And together these uh two models can predict the expected value of the um of the parcel and then we can make more informed discussions or more informed uh decisions about um how we should extend the network. We don’t use one model for both because um the the probability and the expected or the the the money that we can make with um have very different behave very differently. They take different inputs.

10:48 So it’s it’s actually working better if we use two models for it. So we have more flexibility in the in the modeling. And yeah, for example, we could use a simple logistic regression

Two models, one decision metric

11:01 um for one thing and for the other one we could use um a treebased or XG boost regression for example and combine it to get an expected five-year value. Um as I said a separate drivers that go into the probability and into the value. So it makes sense um to split it into into two Um so what we’re going to to build or what what we’re building is we have the existing building data the G data that they have they’re still in the existing files we include structured APIs. Uh for example there’s the kit institute uh fvida of that gives um market value about energy prices for example that you can include via structured APIs and we will use an unstructured data um uh from for example official publications or or news feeds that we will feed through um a local lia model if you probably heard about Jeff for example that’s a decision model lia is a open source variant that can run locally that’s very important because we don’t have a cloud and together we get um a system that extends what they’re currently doing they have a local running app that we’ve built in Rust a desktop application they can in in uh include their existing data we can feed in API data and we can even include like public publicly uh publications for example to get an in get an idea about how the public perception about for example um remote heating or ver um heat pumps is changing and this can feed in to the prediction either into the probability or in the value for example and this is basically what we have to do because we um yeah we we have to first

A local decision tool

13:07 of all work with the existing data, the very slowmoving data. We want to include uh faster moving data from from the outside but we cannot build in an ideal world we would build a big um data warehouse infrastructure where we can consolidate and uh combine all the data and feed it into the model. But um here we have to do with anything that can run locally basically and this is the solution that we came came up with. So it’s a try to be to get the best of both worlds or get that we can the the the users can use it in the same way that they used to work with the tool. we don’t need new uh big infrastructure and at the same time we get access to more um more up-to-date data. Um exactly this is what I’ve already told you about like um important information that we need to feed into the model is for example new heat pump subsidies or the reduction of the new heat of heat pump subsidies um changing energy prices and this will make uh feed into the um this will feed into the liar model and this liar model um might affect the probability that people are um that people want to connect to the heating uh to the heating infrastructure and thus um affect the the expected value of the properties that you might connect uh to your

Keeping the forecast current

14:48 heating uh network and yeah that’s what we had to do. So we don’t want to build another 10 year old. So um we don’t want to build a new forecast that will be just obsolete or will be 10 years old in in 10 years. Um we have to live with the situation that they probably don’t touch this whole thing for the next couple couple of years maybe even 10 years. So we have to build it um that it’s a little bit self- adapting with uh fresh fresh inputs. The geo data can be if they get new geo data they can feed it into the model. We have some APIs that can be fed in. We get the local layer reads of relevant publications and then we update not necessarily the model but the prediction that we get from the model and the predictions um are not static but they can be rerun whenever you want to feel uh whenever you want to do it. So even the application stays use um stays useful when the assumption changes. So um yeah that’s the that’s the

Q&A

16:00 takeaway. Um build the decision first or be build what’s um creating the value first and infrastructure only if it pays or if you’re able u if you’re able to support it. Um, yeah, that’s my talk and now I’m happy to answer if some arise. Okay, thank you. Thank you, Alex. not working. Not uh sorry, the microphone is not working in the room. it’s on. Oh, one is low. looks like we still need electricity Okay. So, let’s see if there are some questions in the room. There’s one In the spirit of your talk, it’s less of an infrastructure question and more of a domain expertise question. Uh one, what’s a parcel? Uh and two, is this about building infrastructure or connecting? And from the client’s point of view in this case uh is it either acquire a retail or consumer client or uh an enterprise client or like if they acquire an enterprise client is it can be people going without heat on or how does this work?

18:16 Um I haven’t heard anything about it but I I I no I haven’t completely um heard everything. I think your question was what the the client was exact and um I think okay I can repeat this uh one question is what is a parcel first of all? Okay. A so maybe I can start with the first one and if you if you look at Google maps for example um then you can see all the then you all the streets and stuff like this and a parcel would be it’s not nec sometimes it’s it’s a little bit different but there are like um certain uh parts of the city that are how do should I say it? Um, for city planning, the city is divided into lots of lots of parcels. Usually they look like one one block for example, but they could be two blocks, but in general it’s it’s like a block for example. This is just like how the city planning is dividing the city city um for planning purposes. And this in general it’s it’s it’s blocks for example one one housing block or if it’s a big um a bigger um factory or something like this then this could be a block or or or a parcel.

19:45 Uh second question uh second question is from the client’s point of view what’s the short point why can’t they get both the enterprise and the retail client and uh if they decide for example to go with a factory does this mean 10 families will go without heating for the winter or is this more distribution not infrastructure? No. So the idea is you have an existing network and you can only extend the network wherever um maybe you want to you have a certain budget for for building new networks and you need to decide which side of the network you’re going to extend it and then you want from a from a um from a financial point of view you want to build it where you get the most return on investment and then you look okay if I build it here for let’s say 1 kilometer in this direction. There are all these parcels that I can connect and I want to predict how much money I will make with it. And then you for each parcel you could um calculate the probability that they want to connect because this is only um the way it works is you cannot force them to connect. You can build the the line and then the owner of the house needs to agree to be connected. They can say no I don’t want to be connected. I get heating from my own. Uh maybe I have a vehma pump or maybe I have a gas. Um many houses in Germany still have um heating in in the in the house. There’s just a big boiler or a gas tank or for example heated like this. And this is just the calculation.

21:22 How do I extend the network uh with the most return on on investment for the local heat provider? Okay. Okay. No other questions. Okay. I think we can thank Alexander. Was a bit complicated today, but being around and presenting. Thank you. Thank you. Bye-bye.