PostGIS Starter Pack.
I was recently tasked with developing an application that, given a certain coordinate(latitude, longitude) as center and a radius, it calculates (or queries) a database for users within that radius.Much more like Uber gets nearby drivers.
In this first part, I will walk you though setting our environment up and ready for the upcoming parts.I do hope you enjoy the guide and find it useful. 😃
Let’s first get the boring stuff out of the way before we start the fun part.
1.Posgresql is a relational database management system(RDMS).I don’t want to say anything further about this but what this informally means is that data is conceptualized as tables and nothing more than tables.For more about RDMS, please refer here and here(Kudos to C.J Date for such great books).
2.Postgis is a geospatial extension for Postgresql. It makes working with geographic data and locations a breeze.
3.The earth is spherical(Sorry, flat-earthers).
What this means is that you can’t simply find the distance between point A and B by simple subtraction( like we do in a 2D cartesian plane).We have to use a projection and a special coordinate system to do such.
There are multiple such coordinate systems, each having an identifier.This is what we call a spatial reference system ID(SRID).All existing SRIDs can be found here. The most common is SRID 4326.
This wraps up our theory section.Now let’s get to the fun stuff.
Getting ourselves ready.
We are going to be working with an Ubuntu 16.04 Virtual Machine.It might be running locally or you can spin up one on AWS or Google Compute and SSH into it.
The following commands should be run in the VM or on your local machine.
1.sudo apt-get update
2.sudo apt-get -y install postgresql postgresql-client postgresql-contrib
4.sudo -u postgres psql postgres
6.Enter your new password
7.CREATE EXTENSION adminpack;
I hate using the terminal to run SQL queries and prefer pgAdmin 4/3. pgAdmin is an intuitive application that connects to your postgresql databases and allows you to run SQL queries on a nice UI.
We first need to allow PostgreSQL accept remote connections from our local computer.
- Go to ip4.me to get the IPv4 of your local computer.
sudo nano /etc/postgresql/9.6/main/pg_hba.conf
- Scroll down to the bottom and paste this line(Of course replacing with the IPv4 you got).
host all all YOUR_IPV4_ADDRESS/32 md5
4. Save and exit.
sudo nano /etc/postgresql/9.6/main/postgresql.conf
- Look for this line.It’s most likely around line 59.
listen_addresses = 'localhost'
Replace it with
listen_addresses = '*'
3. Save and exit
sudo service postgresql restart
- Crete a test database
1. sudo -i -u postgres2. createdb test1
1. sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable
2. sudo apt-get update
3. sudo apt-get install postgis
3.Enable the PostGIS extension.PostgreSQL extensions are enabled per database.
1. sudo -i -u postgres
2. psql -d test1
3. CREATE EXTENSION postgis;
4. SELECT PostGIS_version();
You should see something like this
We are all set.Quit from the SQL session and the postgres user by typing
Install the GDAL and OGR vector libraries that most PostGIS applications require to translate various data types into a format PostGIS can use.
sudo apt-get install gdal-bin
If you were logged into a VM ,you can now exit the SSH session.The remaining steps will be performed on your local machine.
Configure pgAdmin 4
Note: As of this writing, there is no pgAdmin4 binary for Ubuntu, you will have to install it from the source.However, the instructions here can still be used with pgAdmin 3.
- Head over to pgAdmin , grab it and install it.
- Start pgAdmin4.
- Add your server by right-clicking Servers , then Create->Server
- In the dialog that pops up, fill in as follows, replacing the Name, IP and Password accordingly.
5.Click Save.The server will be listed on your left sidebar.
- Select your server from the left sidebar and expand it by clicking the + button.Expand Schemas -> public -> Tables.
Currently there is only one table listed.
Let’s talk about it.
2. spatial_ref_sys table
Remember all the things we said about SRIDs and how there are various projections?Well, this table is a list of all the coordinate systems currently installed.Right click it then View Data -> View All Rows
You should see something like this
You can get the same output by doing the following.
- Right Click the table then select Query Tool.
- In the editor, enter the following SQL query.
As you can see the SRID column is the primary key used in spatial coordinate systems.
Congratulations 👏 👏, you have set up your environment ready for the next part.
In this first blog, I have walked you through on installing PostgreSQL and PostGIS.I have also shown you how to configure pgAdmin4.
If you found this blog helpful, please leave a comment and don’t be mean with the up-vote button 😄.