PostGIS is the spatial extension to the PostgreSQL relational database. PostGIS lets you store spatial data using geometry and geography data types, perform spatial queries with spacial functions to determine area, distance, length, and perimeter, and create spatial indexes on your data to speed up spatial queries.
In this guide, you’ll install PostGIS, configure PostgreSQL for spatial data, load some spatial objects into your database, and perform a basic query.
Before you begin this guide you’ll need the following:
test1database and user that you’ll set up in that guide for this tutorial.
PostGIS is not included in the default repositories for Ubuntu, but we can get it through [UbuntuGIS] (https://launchpad.net/~ubuntugis/), an external repository that maintains a number of open source GIS packages. While the PostGIS package in this repository might not always be the cutting edge release, it is well maintained, and it removes the need to compile PostGIS from source. So to install PostGIS, we’ll add this repository to our sources and then install it with our package manager.
Log into your server with your non-root user:
- ssh sammy@your_ip_address
Since we’re using Ubuntu 14.04 we’ll need the unstable branch of the repository. Execute the following command to add the repository to your sources:
- sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable
You’ll see the following output:
OutputUnstable releases of Ubuntu GIS packages. These releases are more bleeding edge and while generally they should work well, they dont receive the same amount of quality assurance as our stable releases do. More info: https://launchpad.net/~ubuntugis/+archive/ubuntu/ubuntugis-unstable Press [ENTER] to continue or ctrl-c to cancel adding it
ENTER to accept the warning, and the source will be added:
Outputgpg: keyring `/tmp/tmpintg192h/secring.gpg' created gpg: keyring `/tmp/tmpintg192h/pubring.gpg' created gpg: requesting key 314DF160 from hkp server keyserver.ubuntu.com gpg: /tmp/tmpintg192h/trustdb.gpg: trustdb created gpg: key 314DF160: public key "Launchpad ubuntugis-stable" imported gpg: Total number processed: 1 gpg: imported: 1 (RSA: 1) OK
Before you can install PostGIS, update your list of available packages so the packages from the new repository are added to the list.
- sudo apt-get update
Once your sources update, install PostGIS.
- sudo apt-get install postgis
Y when prompted to install PostGIS along with its necessary dependencies.
We can now connect to PostgreSQL and integrate PostGIS.
PostGIS’s features must be activated on a per-database basis before you can store spacial data. We’ll work with the
test1 database and the
postgres user from the How To Install and Use PostgreSQL on Ubuntu 14.04 tutorial you followed before starting this tutorial.
sudo command, switch to the
- sudo -i -u postgres
Then connect to the
- psql -d test1
Next, enable the PostGIS extension on the database:
- CREATE EXTENSION postgis;
Let’s verify that everything worked correctly. Execute the following command:
- SELECT PostGIS_version();
You’ll see this output:
Outputpostgis_version --------------------------------------- 2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 (1 row)
We’re all set. Type
to exit the SQL session and return to your terminal prompt.
Then switch back to your main user account:
- su sammy
We now have a database with PostGIS installed, but let’s tweak some PostgreSQL settings to make things run smoothly.
PostgreSQL is designed to run on anything from integrated systems to large corporate databases, but out of the box it is configured very conservatively. GIS database objects are large in comparison to text data, so let’s configure PostgreSQL to work better with those objects.
We configure PostgreSQL by editing the
postgresql.conf file. Open this file:
- sudo nano /etc/postgresql/9.3/main/postgresql.conf
There are a few changes we need to make to this file to support spatial data.
shared_buffers should be changed to around 75% of your server’s RAM. So
200MB is a good value for a server with 512MB of RAM. Locate the
shared_buffers line and modify it like this:
shared_buffers = 200MB # min 128kB
Next, locate the line starting with
#work_mem. This line is commented out by default, so uncomment this line and increase its value to
work_mem = 16MB # min 64kB
#maintenance_work_mem, uncomment it, and increase its value to
maintenance_work_mem = 128MB # min 1MB
checkpoint_segments, then uncomment it and change its value to
checkpoint_segments = 6 # in logfile segments, min 1, 16MB each
Finally, look for
#random_page_cost. When you find it, uncomment it and set its value to
random_page_cost = 2.0 # same scale as above
CTRL+X to exit, followed by
ENTER to save the changes to this file.
You can check out the tutorial Tuning PostgreSQL for Spatial for more information on these settings.
Restart PostgreSQL for these changes to take place:
- sudo service postgresql restart
We now have PostGIS installed and PostgreSQL configured. Let’s get some data into the database so we can test things out.
Let’s load some spatial data into our database so we can get familiar with the tools and the process for getting this data into PostgreSQL, and so we can do some spatial queries later.
[Natural Earth] (http://www.naturalearthdata.com/) provides a great source of basic data for the whole world at various scales. Best of all, this data is in the public domain.
Navigate to your home folder and create a new folder called
nedata. We’ll use this folder to hold the Natural Earth data we’ll download.
- cd ~
- mkdir nedata
Then navigate into this new folder:
- cd nedata
We will download the 1:110m Countries data set from Natural Earth. Use
wget to pull that file down to your server:
- wget http://www.naturalearthdata.com/http//www.naturalearthdata.com/download/110m/cultural/ne_110m_admin_0_countries.zip
The file you just downloaded is compressed, so you’ll need the
unzip command which you can install through the package manager. Install it with the following command:
- sudo apt-get install unzip
Then unzip the file:
- unzip ne_110m_admin_0_countries.zip
You’ll have six additional files in the folder now:
The .dbf, .prj, .shp, and .shp files make up a ShapeFile, a popular geospatial vector data format used by GIS software. We can load this into our
To do this, we’ll install GDAL, the Geospatial Data Abstraction Library. When we install GDAL, we’ll also get OGR (OpenGIS Simple Features Reference Implementation) and the command
ogr2ogr. This is a vector data translation library which we’ll use to translate the Shapefile into data that PostGIS can use.
Install GDAL using the package manager:
- sudo apt-get install gdal-bin
Now switch to the
postgres user again:
- sudo -i -u postgres
Now convert the Shapefile that you got from Natural Earth into a PostGIS table using
ogr2ogr, like this:
- ogr2ogr -f PostgreSQL PG:dbname=test1 -progress -nlt PROMOTE_TO_MULTI /home/sammy/nedata/ne_110m_admin_0_countries.shp
Let’s break that command down and look at each option in detail. First, we specify this option:
This switch states that the output file type is a PostgreSQL table.
Next, we have this option:
This sets the connection string to our database. We’re just specifying the database name here, but if you wanted to use a different user, host, and port, you can specify those options like this:
PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'"
Next in our list of options is this:
This option displays a progress bar so we can visualize the process.
Next, we pass this argument:
PostgreSQL is strict on object types. The
ogr2ogr command will make an assumption on the geometry type based on the first few features in a file. The data we’re importing contains a mix of Polygon types and multi-part polygons, or MultiPolygons. These cannot be inserted into the same field, so we promote all the features to multi-part polygons, and the geometry field will be created as a MultiPolygon.
Finally, we specify the path to the input file:
Visit the ogr2ogr website to see the full set of options.
When you run the full command, you’ll see the following output:
Output0...10...20...30...40...50...60...70...80...90...100 - done.
We can check that the data was imported by using the
ogrinfo command. Execute the following command:
- ogrinfo -so PG:dbname=test1 ne_110m_admin_0_countries
This will display the following output:
OutputINFO: Open of `PG:dbname=test1' using driver `PostgreSQL' successful. Layer name: ne_110m_admin_0_countries Geometry: Multi Polygon Feature Count: 177 Extent: (-180.000000, -90.000000) - (180.000000, 83.645130) Layer SRS WKT: GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]], UNIT["degree",0.0174532925199433, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4326"]] FID Column = ogc_fid Geometry Column = wkb_geometry scalerank: Integer (4.0) featurecla: String (30.0) ... region_wb: String (254.0) name_len: Real (16.6) long_len: Real (16.6) abbrev_len: Real (16.6) tiny: Real (16.6) homepart: Real (16.6)
We now have spatial data in our database, so let’s look at how we can use it to solve problems.
Suppose we’ve been asked to find the ten most northerly countries in the world. That’s easy using PostGIS and the data we’ve imported.
Log back in to the
- psql -d test1
List the tables in the database:
This will return two tables:
OutputList of relations Schema | Name | Type | Owner --------+---------------------------+-------+---------- public | ne_110m_admin_0_countries | table | postgres public | spatial_ref_sys | table | postgres (2 rows)
We’ll use the
ne_110m_admin_0_countries table, which contains the data that’ll help us answer our question. This table has an
admin column that contains the name of the country, and a
wkb_gemoetry column that contains geometric data. If you want to see all of the columns in the
ne_110m_admin_0_countries table, you can issue the command:
- \d ne_110m_admin_0_countries
You’ll see the columns and their data types. The
wbk_geometry column’s data type looks like this:
wkb_geometry | geometry(MultiPolygon,4326) |
wbk_geometry column contains polygons. We’re dealing with countries and their irregular borders, and thus each country in our database does not have a single value for latitude. So to get the latitude for each country we first find out the centroid of each country using PostGIS’s
ST_Centroid function. We then extract the centroid’s Y value using the
ST_Y function. We can use that value as the latitude.
Here’s the query we’ll run:
- SELECT admin, ST_Y(ST_Centroid(wkb_geometry)) as latitude
- FROM ne_110m_admin_0_countries
- ORDER BY latitude DESC
- LIMIT 10;
We order the results in descending order because the most northerly country will have the highest latitude.
Execute that query and you’ll see the top ten most northerly countries:
Outputadmin | latitude -----------+------------------ Greenland | 74.7704876939899 Norway | 69.1568563971328 Iceland | 65.074276335291 Finland | 64.5040939185674 Sweden | 62.8114849680803 Russia | 61.9808407507127 Canada | 61.4690761453491 Estonia | 58.643695240707 Latvia | 56.8071751342793 Denmark | 56.0639344617945 (10 rows)
Now that you have your answer, you can exit the database with
You can find more information on the various PostGIS functions in the PostGIS Reference section of the PostGIS documentation.
You now have a spatially enabled database configured for spatial queries, and you have some data in that database you can use for further exploration.
For a more in-depth guide to creating spatial queries, see the Boundless PostGIS Tutorial
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Click below to sign up and get $200 of credit to try our products over 60 days!
The last step, when you query the database, you misspelled “latitude” n Select admin, ST_Y(ST_Centroid(wkb_geometry)) as latitude. When you run the query, you will get an error that “latitude” does not exist. Correct the spelling and it works.
An alternative to getting postgis from ububtu-gis unstable is to use, the PostgreSQL Apt Repository. From my experience they keep it current. Right now postgres 9.5 & 9.6 are available along with postgis 2.3 [https://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS22UbuntuPGSQL95Apt]