How to install and configure PostGIS on Ubuntu 14.04

Introduction

PostGIS is a spatial extension of the PostgreSQL relational database. PostGIS allows you to store spatial data using geometric and geographic data types, use spatial functions to perform spatial queries to determine area, distance, length and perimeter, and create spatial indexes on the data to speed up spatial queries.

In this tutorial, you will install PostGIS, configure PostgreSQL for spatial data, load some spatial objects into the database, and perform basic queries.

Preparation

Before starting this tutorial, you need the following:

Step 1-Install PostGIS

PostGIS is not included in Ubuntu's default repository, but we can get it through UbuntuGIS, UbuntuGIS is an external repository that maintains many open source GIS packages. Although the PostGIS package in this repository may not always be the latest version, it is well maintained and there is no need to compile PostGIS from source code. Therefore, to install PostGIS, we will add this repository to our sources and then install it using our package manager.

Log in to the server as a non-root user:

ssh sammy@your_ip_address

Since we are using Ubuntu 14.04, we need the unstable branch of the repository. Execute the following command to add the repository to the source:

sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable

You will see the following output:

Unstable 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

Press ENTER to accept the warning and the source will be added:

gpg: 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 installing PostGIS, please update the list of available packages so that the packages in the new repository can be added to the list.

sudo apt-get update

After updating the source, install PostGIS.

sudo apt-get install postgis

Enter Y when prompted to install PostGIS together with the necessary dependencies.

We can now connect to PostgreSQL and integrate PostGIS.

Step 2-Use PostGIS to enable Spacial function

The function of PostGIS must be activated based on each database before spatial data can be stored. Before starting this tutorial, we will use the test1 database and postgres user you set up earlier.

Use the sudo command to switch to the postgres user:

sudo -i -u postgres

Then connect to the test1 database:

psql -d test1

Next, enable the PostGIS extension on the database:

CREATE EXTENSION postgis;

Let us verify that everything is ok. Execute the following commands:

SELECT PostGIS_version();

You will see this output:

   postgis_version
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1(1 row)

We are all ready. Types of

\ q

Exit the SQL session and return to the terminal prompt.

Then switch back to your main user account:

su sammy

We now have a database with PostGIS installed, but let's adjust some PostgreSQL settings to smoothly proceed to the next step.

Step 3-Optimize PostgreSQL for GIS database objects

PostgreSQL is designed to run anything from integrated systems to large enterprise databases, but its configuration out of the box is very conservative. Compared with text data, GIS database objects are large, so let us configure PostgreSQL to better handle these objects.

We configure PostgreSQL by editing the postgresql.conf file. Open this file:

sudo nano /etc/postgresql/9.3/main/postgresql.conf

We need to make some changes to this file to support spatial data.

First, shared_buffers should be changed to about 75% of the server RAM. 200MB is a good value for a server with 512MB RAM. Find the shared_buffers line and modify it as follows:

shared_buffers = 200MB                  # min 128kB

Next, find the line that starts with #work_mem. By default, this line is commented out, so uncomment this line and increase its value to 16MB:

work_mem = 16MB                         # min 64kB

Then find #maintenance_work_mem, uncomment, and increase its value to 128MB:

maintenance_work_mem = 128MB            # min 1MB

Find checkpoint_segments, then uncomment and change its value to 6:

checkpoint_segments =6         # in logfile segments, min 1, 16MB each

Finally, look for #random_page_cost. After finding it, uncomment and set its value to 2.0:

random_page_cost =2.0                 # same scale as above

Press CTRL+X to exit, then press Y and ENTER to save the changes to this file.

Restart PostgreSQL to make the following changes:

sudo service postgresql restart

We have now installed PostGIS and configured PostgreSQL. Let's enter some data into the database so we can test it out.

Step 4-Load spatial data

Let's load some spatial data into our database so that we can be familiar with the tools and process of importing this data into PostgreSQL, so we can perform some spatial queries later.

Natural Earth provides an important source of basic data for the entire world at different scales. Most importantly, these data are in the public domain.

Navigate to your home folder and create a new folder called nedata. We will use this folder to save the Natural Earth data we will download.

cd ~
mkdir nedata

Then navigate to this new folder:

cd nedata

We will download the 1:110m national dataset from Natural Earth. Use wget to pull down the file 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 need the command unzip which can be installed via the package manager. Install it using the following command:

sudo apt-get install unzip

Then unzip the file:

unzip ne_110m_admin_0_countries.zip

You will now have six additional files in this folder:

. DBF, .PRJ, .SHP and .SHP files form a ShapeFile, a popular geospatial vector data format used by GIS software. We can load it into our test1 database.

For this, we will install the Geospatial Data Abstraction Library GDAL. When we install GDAL, we will also get OGR (OpenGIS Simple Function Reference Implementation) and the command ogr2ogr. This is a vector data conversion library, and we will use it to convert Shapefile into data that can be used by PostGIS.

Install GDAL using the package manager:

sudo apt-get install gdal-bin

Now switch to user postgres again:

sudo -i -u postgres

Now we will use ogr2ogr to convert Natural Earth's Shapefile into a PostGIS table, as shown below:

ogr2ogr -f PostgreSQL PG:dbname=test1 -progress -nlt PROMOTE_TO_MULTI /home/sammy/nedata/ne_110m_admin_0_countries.shp

Let's break this command and look at each option in detail. First, we specify this option:

- f PostgreSQL

This switch declares that the output file type is a PostgreSQL table.

Next, we have this option:

PG:dbname=test1

This will set the connection string to our database. We just specify the database name here, but if you want to use other users, hosts, and ports, you can specify these options:

PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'"

The next in our list of options is:

- progress

This option displays a progress bar so that we can see the process.

Next, we pass this argument:

- nlt PROMOTE_TO_MULTI

PostgreSQL is strict about object types. The ogr2ogr command will make assumptions about the geometry type based on the first few features in the file. The data we imported contains a mixture of polygons and multi-part polygons or MultiPolygons. These cannot be inserted into the same field, so we promote all features to multi-part polygons and create the geometry field as MultiPolygon.

Finally, we specify the path of the input file:

/home/sammy/nedata/ne_110m_admin_0_countries.shp

Visit the ogr2ogr website for complete options.

When you run the full command, you will see the following output:

0...10...20...30...40...50...60...70...80...90...100- done.

We can use the ogrinfo command to check whether the data has been imported. Execute the following commands:

ogrinfo -so PG:dbname=test1 ne_110m_admin_0_countries

This will display the following output:

INFO: 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 the database, so let's see how to use it to solve the problem.

Step 5-Query spatial data

Suppose we are asked to find the ten northernmost countries in the world. It is easy to use PostGIS and the data we imported.

Log in to the test1 database again.

psql -d test1

List the tables in the database:

\ dt 

This will return two tables:

     List of relations
 Schema |           Name            | Type  |  Owner
- - - - - - - - +- - - - - - - - - - - - - - - - - - - - - - - - - - - +- - - - - - - +- - - - - - - - - - public| ne_110m_admin_0_countries | table | postgres
 public| spatial_ref_sys           | table |postgres(2 rows)

We will use the ne_110m_admin_0_countries table, which contains data that can help us answer our questions. This table contains a column admin containing country/region names, and a column wkb_gemoetry containing geometry data. If you want to view all the columns in the table ne_110m_admin_0_countries, you can issue the following command:

\ d ne_110m_admin_0_countries

You will see the column and its data type. The data types of the wbk_geometry column are as follows:

 wkb_geometry |geometry(MultiPolygon,4326)|

The wbk_geometry column contains polygons. We are dealing with countries and their irregular borders, so there is no single latitude value for each country in our database. Therefore, in order to obtain the latitude of each country, we first use the ST_Centroid function of PostGIS to find the centroid of each country. Then we use the ST_Y function to extract the Y value of the centroid. We can use this value as the latitude.

This is the query we will run:

SELECT admin,ST_Y(ST_Centroid(wkb_geometry))as latitude 
FROM ne_110m_admin_0_countries 
ORDER BY latitude DESC 
LIMIT 10;

We sort the results in descending order, because the most northerly country will have the highest latitude.

Executing this query, you will see the top ten northernmost countries:

 admin   |    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 the answer, you can exit the database

\ q

in conclusion

Now you have configured a spatially enabled database for spatial query, and you can use some data in this database for further exploration.

To learn more about the installation and configuration of PostGIS related tutorials, please go to [Tencent Cloud + Community] (https://cloud.tencent.com/developer?from=10680) to learn more.


Reference: "How to Install and Configure PostGIS on Ubuntu 14.04"

Recommended Posts

How to install and configure PostGIS on Ubuntu 14.04
How to install and configure PostGIS on Ubuntu 14.04
How to install and configure NATS on Ubuntu 16.04
How to install and configure Gogs on Ubuntu 18.04
How to install and configure Cyberpanel on Ubuntu 18.04
How to install and configure ownCloud on Ubuntu 16.04
How to install and configure GitLab on Ubuntu 18.04
How to install and configure Ansible on Ubuntu 18.04
How to install and configure VNC on Ubuntu 18.04
How to install and configure Sphinx on Ubuntu 16.04
How to install and configure OrientDB on Ubuntu 14.04
How to install and configure AppScale on Ubuntu 12.04
How to install Pycharm and Ipython on Ubuntu 16.04/18.04
How to install and configure Elasticsearch on CentOS 7
How to install and secure phpMyAdmin on Ubuntu 16.04
How to install and use Docker on Ubuntu 20.04
How to install and configure VNC on CentOS 8
How to install and use Curl on Ubuntu 18.04
How to install and use Wine on Ubuntu 18.04
How to install and secure phpMyAdmin on Ubuntu 16.04
How to install and use Composer on Ubuntu 20.04
How to install and use BaasBox on Ubuntu 14.04
How to install and configure phpMyAdmin on CentOS 6
How to install and use Docker on Ubuntu 16.04
How to install and configure Redmine on CentOS 8
How to install Ruby on Ubuntu 20.04
How to install Memcached on Ubuntu 20.04
How to install Java on Ubuntu 20.04
How to install MySQL on Ubuntu 20.04
How to install VirtualBox on Ubuntu 20.04
How to install Elasticsearch on Ubuntu 20.04
How to install Protobuf 3 on Ubuntu
How to install Nginx on Ubuntu 20.04
How to install Apache on Ubuntu 20.04
How to install Git on Ubuntu 20.04
How to install Node.js on Ubuntu 16.04
How to install MySQL on Ubuntu 20.04
Install and configure MySQL on Ubuntu
How to install Vagrant on Ubuntu 20.04
How to install Bacula-Web on Ubuntu 14.04
How to install PostgreSQL on Ubuntu 16.04
How to install Git on Ubuntu 20.04
How to install Anaconda3 on Ubuntu 18.04
How to install Memcached on Ubuntu 18.04
How to install Jenkins on Ubuntu 16.04
How to install MemSQL on Ubuntu 14.04
How to install Go on Ubuntu 20.04
How to install MongoDB on Ubuntu 16.04
How to install Mailpile on Ubuntu 14.04
How to install PrestaShop on Ubuntu 16.04
How to install Skype on Ubuntu 20.04
How to install Jenkins on Ubuntu 20.04
How to install Python 3.8 on Ubuntu 18.04
How to install KVM on Ubuntu 18.04
How to install opencv3.0.0 on ubuntu14.04
How to install Anaconda on Ubuntu 20.04
How to install Prometheus on Ubuntu 16.04
How to install Jenkins on Ubuntu 18.04
How to install Apache on Ubuntu 20.04
How to install Solr 5.2.1 on Ubuntu 14.04
How to install MariaDB on Ubuntu 20.04