Spatial Database on Microsoft Azure

Posted May 27, 2021

By Hillson Ghimire 𝕏 2 min read

Almost every GIS operation is shifting from Conventional GIS application to the Cloud Resources. This provides broad spectrum of services to users across the globe. Any organization can “plug-in” to virtual environments and use the computing resources available as on required basis.

Why is cloud hosted database required?

There are several advantages of hosting database on cloud. Some points to note are:

Support for Large Data Volumes
Scaling
Performance
High Availability
Maintenance
Concurrent Connections

Server Creation

After logging into the azure dashboard, we can easily navigate to ‘Azure Database for PostgreSQL servers’ and follow along as:

Add
Single Server

After the process is complete, Azure Database for PostgreSQL servers is created.

We can fill up all the requirements according to our needs and then create the server. After creation the dashboard is loaded as:

From the setting Heading, we can select the connection strings and then use these connection strings according to our application needs. The connection string page is as:

We might also want to modify the connection security parameters. In my case, I modified the firewall setting, to make the server accessible from the clients local machine IP address. This will prevent any unauthorized access to the server from unknown IPs not specified in the firewall rule.

Now, Azure provides you with Cloud Shell, you can either select ‘bash’ or ‘powershell’. I am familiar with bash so that goes to my bucket. We can communicate to our database server using this cloud shell.

Now, We can log in to the remote PostgreSQL Database Server on the cloud from our device terminal or from the pgAdmin4 interface. But in my case, I am working on spatial datasets and trying to establish database connection to the Database Engine in QGIS Desktop for analysis purposes.

QGIS and Azure Database for PostgreSQL/PostGIS

Used Dataset:

Nepal Road Network (main roads) from WFP GeoNode
Nepal Admin Shapefile

I have data locally on my machine. These dataset can be loaded as postgis object using different techniques pointed below:

Using shp2pgsql CLI utility
Using shp2pgsql GUI utility (already discussed)
Using QGIS DBmanager (easier approach)
Using ogr2ogr

Among these techniques, I am going to import using “ogr2ogr” command line utility. “ogr2ogr” is the swiss-army knife when it comes to conversion of GIS data. It is available as part of FW Tools.

The general syntax for conversion is:

>> ogr2ogr -f "file_format" destination_data source_data

Now, to convert ESRI Shapefile to PostGIS object:

>> ogr2ogr.exe -f "PostgreSQL" PG:"dbname=my_database user=postgres" "source_data.shp" -skip-failures

Importing spatial data to PostGIS by @tjukanov is an amazing resource if you are trying it.

This is what I did to get my file on the remote postgreSQL server.

>> ogr2ogr -f "PostgreSQL" PG:"host=gis-resource-server.postgres.database.azure.com dbname=hillson user=hillson@gis-resource-server password=********” shapeFileName.shp 

We’re hosted by here!

Now, we can easily import these dataset into our QGIS Application through DBManager in QGIS. This part is already explained in my previous blog