Phil Allsopp Former EDB Member May 8, 2019
In this blog, I’ll describe what PostGIS is, how you can set this up quickly and easily using EnterpriseDB’s Advanced Server and scratch the surface of what’s possible using PostGIS.
First of all, what is PostGIS?
PostGIS is a spatial extender for Postgres and adds more geometric types and spatial functionality to Postgres. You can use PostGIS to easily calculate distances between geometric objects, examples of which are points, lines, and polygons; you can easily calculate areas, whether one or more points sit within any geometric shapes (e.g. which town does a house sit within), whether lines cross shape boundaries and so on. One example of current enterprise use of PostGIS is by local administrative organizations (e.g. city councils) who use PostGIS to calculate business rates/land taxes for land ownership/use.
What PostGIS is not...
is something that looks like google maps, although you can set up a tile server application that can present PostGIS data in exactly that way. A future blog will show you how to do that using OpenStreetMap data as your data source and PostGIS as your database.
This blog will show you how easy it is to set up PostGIS on your database server and will finish off by showing you how to use just a few of the spatial functions that are available to you.
So Let’s get started with the installation.
I’ll assume that you have just installed your minimal copy of Centos 7 and your Centos 7 has access to the Internet.
Once we’re all set, let’s log in as the root user.
Let's install a few packages.
Now I’ll set up the time correctly for my location - Sydney, Australia.
You’ll need an EDB account to complete the following installations. If you don’t already have an EDB account, you can create one (which will give you your access credentials) here:
Next, we’ll install the EDB REPO and set up our EDB YUM repo username and password.
Install EDB Advanced server v 11 and PostGIS
We’ll create a file with your environment variables that we will use globally; you can do the same in a bash.rc file if you prefer.
Save the file, then type:
Initialize the Database cluster
And startup Advanced Server
Login to the database using the psql client
At this point you would normally configure your firewall and/or security groups, secure your database, remove default access to the public group and public schema, add a password to the enterprisedb user account, set up pg_hba.conf to tighten up access control and so on.
I’m going to skip all of those steps here as our goal is to just install and run a demo of PostGIS.
Note: if you want advice on setting up, fully securing and tuning EnterpriseDB’s Advanced Server, you can contact EDB using one of the contact methods on the EDB website.
Set up a new database template where we'll add our PostGIS extensions
We now have PostGIS database template setup, PostGIS installed, as well as some relevant supporting extensions. So let’s go ahead and create a database called test, create a single table and then insert the EDB office names, longitudes and latitudes so that we can use this data to calculate the distances in Km between the different EDB offices.
We'll set up a test database using the template database we just created and then create the table we need.
Insert the basic data we need; the data consists of the EDB office names along with their longitude and latitudes.
Now for the interesting part.
Let’s write a query that calculates the distance between the EDB offices using the PostGIS ST_DistanceSpheroid and ST_DistanceSphere functions.
I won’t go into too much detail about how this works here other than to say that the ST_DistanceSphere function calculates distances over the surface of a sphere, whereas ST_DistanceSpheroid calculates the distance over an oblate spheroid which is a little more accurate but takes a little longer to calculate.
Calculate km distances between EDB offices
and the result is:
And that’s it, it’s that easy.
It should be noted that if you just want to run the distance calculations shown above you could just set up the table data, create a small stored procedure which used the haversine formula, Vincenty’s algorithm or another algorithm of your choice to do the calculations although PostGIS has much more functionality than just distance calculations between lng/lat points.
I’ll blog more on the subject of PostGIS over the coming months so that you can get a better idea of the full capabilities it has to offer.