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.
Before starting this tutorial, you need the following:
test1 database and user you set up for this tutorial. If you use it in a production environment, I still recommend that you directly use Cloud relational database. The cloud relational database allows you to easily deploy, manage and expand a relational database in the cloud, providing a safe, reliable, scalable and flexible On-demand cloud database service. Tencent Cloud relational database provides MySQL, SQL Server, MariaDB, PostgreSQL database engine, and optimizes the performance of the database engine.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.
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.
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.
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:
ne_110m_admin_0_countries.README.html ne_110m_admin_0_countries.VERSION.txt ne_110m_admin_0_countries.dbf ne_110m_admin_0_countries.prj ne_110m_admin_0_countries.shp ne_110m_admin_0_countries.shx. 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.
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
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