(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.