(upbeat music) Hello and welcome. Please enjoy this short readiness video on Azure database for PostgreSQL. Hi, Sai, how are you doing? Good, good Sanjay, how are you? I’m great. And you know this, I know this is going to be a surprise okay, for me because this gonna be a fun, I’m looking forward to this particular demo, which I have– that you will talk about. So, let’s see. What are you gonna talk about? Yeah, so today I’m here to talk about how do we build geospatial applications with Azure database for Postgres. So, yeah. All right, let’s get started. Awesome, so Postgres has very good geospatial feature sets, and this is because of the PostGIS extension. So, the PostGIS extension let’s you create and represent various geospatial constructs such as latitudes, longitudes, geometries, intersections, polygons, et cetera, at the database layer, Okay. And what Azure database Postgres does is, along with supporting all of these cool features that come in the PostGres support, it gives you the ability to compliment your geospatial database with tools such as Power BI to solve mission critical, hard business problems. Oh wow. Awesome, so today, what we’ll be doing, Sanjay, is we’ll go through a business scenario of site selection. So, site selection is a very common real world scenario across multiple industries. Say, you take the retail industry, right? So, you are a retail store, and you want to figure out where do you, a retail company, and you want to figure out where do you want to start your retail stores. Ah. So you want to open a new store, and you’re trying to decide a site where you should open. Exactly. That is one. The second is, you are a scooter company and you want to decide, “Where do I want to place my charging stations?” Right? So there are multiple scenarios like that. And today we will be focusing, just as an example, the second one which is, “I am a scooter company and I want to decide where do I want to put my charging stations.” right? So, here there is a Forbes article, it’s a fantastic article, and you should go through, I see. Which talks about, what is the major expense for a scooter company. It says that the major expense is the nightly charging, which the company needs to do for its scooters. And the major contribution of that is actually, the scooter company just moving those scooters to the charging stations, and just bringing them back. Of course. Because, I know I remember renting one of those in Lisbon this summer. Exactly. So people just leave them where they are. And they don’t, obviously– Right, right. Exactly. So it’s super critical you know, to have these charging stations in the right locations, so that they reduce the cost. And as you said, right, here is the SWOT analysis, which shows why this market is so heavy. I mean, there are of competitors, a lot of companies doing this. And then if you observe right in the objectives, one of the most important objectives is to minimize expenses. Yes. And for that, deciding the sites for your charging stations, is super critical. Right? So, today’s problem, the big question is, where are the best places to put scooter charging centers for a scooter company? Awesome. Right? So let’s learn how Azure Database for Postgres with multiple community tools solves this problem. So, in solving this question, there are many factors you need to keep in mind. One is population. You want to place these charging stations in a location with high population. Of course. Next is, proximity to public transportation. So you don’t want to have these charging stations close to subways. Because typically people prefer to use public transportation than using the scooters. Okay. The third is around income. So if you are in the higher bracket, then you might not prefer to use scooters, whereas if you are in the lower bracket you might not afford to use the scooters. So that is also an important factor. And last but not least, obviously crime rates. You don’t want to have charging stations in areas with large crime rates. Right? So these are all the factors we need to keep in mind in making this decision. Okay.
Right? Let’s see how Postgres, with the community tools, makes all of this simple, right? So, this is a big, complicated task, and for today we’ll just take the New York City, which is a huge city, and it has close to 10,400 kilometers of streets, and it has 43,000 intersections. So it’s a lot. Wow. And then, this is a depiction of how it looks in the New York City. So all the red ones you see there, right, the red dots, are street intersections. Okay. And then, this is being displayed by a tool called QGIS. It’s a tool that is available in the community used very predominantly in the geospatial industry, and under the covers this QGIS is compatible with Postgres, and it’s actually running a Postgres database, under the covers. Okay. Right? So let’s consider the first factor of proximity to public transportation. You don’t want to have your charging stations close to subways, right? Because people prefer public transportation. Let’s take that factor and let’s see how we will use and filter all of these intersections. Right? So what we’ll do is, we’ll run a simple delete command, as you are seeing on the screen, which deletes all the intersections, that are at a radius of 500 meters at the subway stations. Yes. Right, so if you see the delete command, the interesting piece is getting a little technical is, it does a geospatial join. So that is needed to figure out these locations that are close to subway regions, and then you delete it from the entire intersections table. What is a geospatial join, is that on after STW? Exactly. So that’s where you have the geospatial join, you have STD built in, right? That is a construct which performs the join, and if you see, that’s all PostGIS in Azure Database for PostgreSQL. Okay. Right? So, once I do that delete, if you see, the green ones are the subway stations, the red ones, which are closer to the green ones, have now disappeared, and then you see just plain colors, right? Yes. So, we did some of all the filtering with one factor, which is proximity to public transportation. Now we’ll go a step higher, and let’s use Power BI. Power BI, here if you see, is compatible with Postgres again. Again, it supports all the PostGIS constructs, and Power BI has something called GeoJSON. Of course. And that is compatible with the geospatial features as well. For Postgres. Right, and if you see, there is a table there which shows all the factors which we talked about. And then the one in the red, which is the side priority, is a score we are generating which weights all of these factors and generates a normalized code. And this also can be done with Power BI, by the way. Right, and the one in the green you see, is the neighborhood ID. Neighborhood ID, right? And here what we are doing is we are ranking the neighborhoods with the highest priority. So we are taking top ten neighborhoods with highest priority. And then in the next step, what we’ll do is we’ll delete all the intersections that are not close to these neighborhoods, right? Oh yeah. And let’s go to QGIS again, and this is the Delete Like operation, if you see. Again, it does a complex geospatial join, and a simple delete query. And then, here if we see we are taking those neighborhood IDs, and then we are deleting them, we are deleting all the regions that are not close to these neighborhoods, right? Of course. And finally, after running this delete, here is how it would look. At least you have lesser options now. You can breathe, and like– Exactly, so– You can think logically now. Exactly, so a lot a of filtering now. Yeah. And then you have those red locations. And those are the ideal regions where you can have the charging stations. So 95 percent of the filtering is done in a couple of minutes, right? Okay. So– Where is Times Square in this? (laughs) No, I’m just joking. It’s a great question. I think Times Square is right in between, because it’s in Midtown, Okay. So I love that place, by the way, Yeah, awesome. So the summary is, using PostGis and Postgres, Azure Database for PostgreSQL, you can solve hard geospatial problems very easily. So feel free to, if there is a geospatial use case, go ahead and spin up a database using our past offering, and test it out. Fantastic. So basically, We could, you know– our customers and of course internal Microsoft folks as well, they can actually use Power BI with the whole Postgres and really make this magic happen. Exactly, exactly. Wow, many many thanks for this amazing scenario. Awesome, Sanjay. Nice talking to you, sir. Thanks for watching this short Azure Readiness video.

Building geo-spatial applications with Azure Database for PostgreSQL
Tagged on:                                                                                             

Leave a Reply

Your email address will not be published. Required fields are marked *