GIS Databases: PostGIS / PostgreSQL

Geographic Information Systems (GIS) Database Description:

Regular SQL relational databases hold data in fields with data types such as integers, floating point numbers and text data. Some even support images and "BLOBs" (Binary Large Objects).

GIS databases have the ability to store geometry and geographic data in the database fields and possess query capabilities to find the distance between two locations, length of a route or find the entities of interest within a specified boundary or boarder.

GIS databases are typically extensions added to a standard SQL database which allow one to define GIS entities and perform the GIS relational queries based on these geometry. This tutorial will cover the PostGIS extension to the PostgreSQL database, probably the most popular and certainly very capable GIS database solutions. Other GIS solutions exist for Oracle (9i+), DB2 (9+), Apache Solr (4+) and MySQL (4.1+) but they have not received the same level of attention and popularity.

PostGIS and PostgreSQL Installation:

Installation:

Ubuntu: sudo apt-get install postgis
CentOS/RedHat: sudo yum install postgis

The dependencies are such that PostgreSQL will be installed as a dependency of PostGIS.

For more on installing and using the PostgreSQL database see the YoLinux PostgreSQL tutorial.

Test:

Start the database server: sudo /etc/init.d/postgresql restart
Become user "postgres": sudo - postgres

Start the PostgreSQL command line client: psql

postgres=# \c
 You are now connected to database "postgres" as user "postgres".
postgres=#\q

PostGIS Examples:

GIS database tables can be configured for 2D or 3D support. We have examples of both.

PostGIS 2D Examples:

Creating a GIS database:

postgres=# CREATE DATABASE mountains;
CREATE DATABASE
postgres=# \c mountains
You are now connected to database "mountains" as user "postgres".
postgres=# CREATE EXTENSION postgis;
CREATE EXTENSION
postgres=# CREATE EXTENSION postgis_topology;
CREATE EXTENSION
mountains=# CREATE TABLE ski_resorts (id INTEGER NOT NULL, name VARCHAR(30) NOT NULL, state_code CHARACTER(2), country_code CHARACTER(2), location GEOGRAPHY(POINT,4326), CONSTRAINT pk_id PRIMARY KEY(id));
CREATE TABLE
mountains=# INSERT INTO ski_resorts (id, name, state_code, country_code, location) VALUES (1,'Squaw','CA','US',ST_GeogFromText('SRID=4326;POINT(-120.234811 39.196195)'));
INSERT 0 1
mountains=# INSERT INTO ski_resorts (id, name, state_code, country_code, location) VALUES (2,'Breckenridge','CO','US',ST_GeogFromText('SRID=4326;POINT(-106.067927 39.480965)'));
INSERT 0 1
mountains=# INSERT INTO ski_resorts (id, name, state_code, country_code, location) VALUES (3,'Steamboat','CO','US',ST_GeogFromText('SRID=4326;POINT(-106.80616 40.45695)'));
INSERT 0 1
mountains=# INSERT INTO ski_resorts (id, name, state_code, country_code, location) VALUES (4,'Vail','CO','US',ST_GeogFromText('SRID=4326;POINT(-106.373445 39.640031)'));
INSERT 0 1
mountains=# SELECT * FROM ski_resorts;
 id |     name     | state_code | country_code |                      location                      
----+--------------+------------+--------------+----------------------------------------------------
  1 | Squaw        | CA         | US           | 0101000020E61000006D6FB724070F5EC0C251F2EA1C994340
  2 | Breckenridge | CO         | US           | 0101000020E6100000FCE07CEA58845AC0A4C2D84290BD4340
  3 | Steamboat    | CO         | US           | 0101000020E6100000FAD51C2098B35AC01FF46C567D3A4440
  4 | Vail         | CO         | US           | 0101000020E6100000B476DB85E6975AC08DB62A89ECD14340
(4 rows)
mountains=# CREATE USER dude WITH PASSWORD 'supersecret';
CREATE ROLE
mountains=# GRANT ALL PRIVILEGES ON ski_resorts to dude;
GRANT

Note the "id" integer field identified as a PRIMARY KEY. Tools like QGIS require the inclusion of an integer field with unique values. This is enforced by the PRIMARY KEY constraint. This is important when designing a PostGIS/PostgreSQL database schema.

[Potential Pitfall]: the command "CREATE EXTENSION postgis;" gives the following error:
ERROR: could not open extension control file "/usr/share/postgresql/9.3/extension/postgis.control": No such file or directory
Install missing package: apt-get install postgresql-9.3-postgis-scripts

[Potential Pitfall]: the command "CREATE TABLE ski_resorts ...;" gives the following error:

ERROR:  type "geography" does not exist
LINE 1: ...CHARACTER(2), country_code CHARACTER(2), location GEOGRAPHY(...
                                                             ^

Create and connect to the database first and then "CREATE EXTENSION postgis;"

[Potential Pitfall]: the command "INSERT INTO ski_resorts ...;" gives the following error:
ERROR: parse error - invalid geometry
You may have the order mixed up. Note that it is "longitude" space "latitude"! Latitudes outside the range of -90 to 90 degrees will cause an error.

Querying a GIS database:

GIS query for location as well as for the individual longitude/latitude values in degrees:

mountains=# SELECT ST_AsText(location) AS Dist_deg FROM ski_resorts WHERE name='Vail';
           dist_deg           
------------------------------
 POINT(-106.373445 39.640031)
(1 row)
mountains=# SELECT ST_X(ST_AsText(location)), ST_Y(ST_AsText(location)) AS Dist_deg FROM ski_resorts WHERE name='Vail';
    st_x     | dist_deg  
-------------+-----------
 -106.373445 | 39.640031
(1 row)

GIS queries do more than just retrieve data but perform GIS analysis. For example, find the distance (in km) from Breckenridge to Vail (as the crow fies):

mountains=# SELECT ST_Distance(a.location, b.location)/1000 as Dist_deg from ski_resorts a, ski_resorts b where a.name='Breckenridge' AND b.name='Vail';
    dist_deg    
----------------
 31.64270439224
(1 row)

PostGIS 3D Example:

Create and query a 3D Georaphic database:

mountains=# CREATE TABLE ski_resorts_3d (id INTEGER NOT NULL, name VARCHAR(30) NOT NULL, state_code CHARACTER(2), country_code CHARACTER(2), location GEOGRAPHY(POINTZ,4326), CONSTRAINT pk_id PRIMARY KEY(id));
CREATE TABLE
mountains=# INSERT INTO ski_resorts_3d (id, name, state_code, country_code, location) VALUES (2,'Breckenridge','CO','US',ST_GeographyFromText('SRID=4326;POINTZ(-106.067927 39.480965 2926)'));
INSERT 0 1
mountains=# INSERT INTO ski_resorts_3d (id, name, state_code, country_code, location) VALUES (4,'Vail','CO','US',ST_GeographyFromText('SRID=4326;POINTZ(-106.373445 39.640031 2475)'));
INSERT 0 1
mountains=# INSERT INTO ski_resorts_3d (id, name, state_code, country_code, location) VALUES (5,'Heavenly','CA','US',ST_GeographyFromText('SRID=4326;POINTZ(-119.939488 38.935541 1993)'));
INSERT 0 1
mountains=# INSERT INTO ski_resorts_3d (id, name, state_code, country_code, location) VALUES (6,'Mammoth','CA','US',ST_GeographyFromText('SRID=4326;POINTZ(-119.03792 37.65127 2424)'));
INSERT 0 1
mountains=# SELECT ST_AsText(location) AS Dist_deg FROM ski_resorts_3d WHERE name='Heavenly';
               dist_deg               
--------------------------------------
 POINT Z (-119.939488 38.935541 1993)
(1 row)
mountains=# SELECT ST_X(ST_AsText(location)), ST_Y(ST_AsText(location)), ST_Z(ST_AsText(location)) FROM ski_resorts_3d WHERE name='Heavenly';
    st_x     |   st_y    | st_z 
-------------+-----------+------
 -119.939488 | 38.935541 | 1993
(1 row)
mountains=# SELECT ST_Distance(a.location, b.location)/1000 AS spheroid_dist FROM ski_resorts_3d a, ski_resorts_3d b WHERE a.name='Heavenly' AND b.name='Mammoth';
 spheroid_dist
-----------------
 162.91764071573
(1 row)

Note: The table is generated to hold a "POINTZ" which is a 3D point where the third coordinate is the altitude in meters. Attempts to insert a 2D point will result in an error.
POINTZ: longitude latitude altitude

PostGIS PostgreSQL GIS Types:

Geometry entities are the definition of spatial shapes in a X,Y coordinate system in meters on a reference plane.
Geography entities are geometry entities defined in a "geographic" coordinates such as "lat/lon".

Geometry:

Geometry without spacial reference is a simple X,Y coordinate sytem in meters.

Geometry	Geometry Return Type	Description
Point	ST_Point	Singular X,Y point POINT(30 10)
LineString	ST_LineString	Ordered set of line or curve connected points
Polygon	ST_Polygon	Closed LINESTRING
MultiPoint	ST_MultiPoint	Collection of X,Y points POINT(30 10)
MultiLineString	ST_MultiLineString	Collection of line or curve connected points
MultiPolygon	ST_MultiPolygon	a closed LINESTRING
GeometryCollection	ST_GeometryCollection	Heterogeneous collection of geometry
CircularString		Circle defined by three points on the circle - start, end and any other point on the circle
CurvePolygon
CompoundCurve		outer ring and zero or more inner rings
Triangle		four points where the start and end points are the same
Circle
Curve
MultiCurve		collection of curves, which can include linear strings, circular strings or compound strings
Surface
MultiSurface		collection of surfaces, which can be (linear) polygons or curve polygons
PolyhedralSurface

Enter lat/lon into a GEOMETRY column: (equivalent)

ST_GeomFromEWKT('SRID=4326;POINT(39.640031,-106.373445)
ST_GeomFromText('POINT(39.640031 -106.373445)', 4326)

Note that if you try to insert PostGIS entities using two different SRS into the same database column, you will get an error. Consistency is a requirement.

Entering geometry data into a column of a geography defined type: ST_POINT(39.640031,-106.373445)

Geography:

Geographic entities undergo a spatial transformation for use on a 3D spherical or ellipsoidal globe. To provide a spatial reference system (SRS) such as WSG 84 (srid = 4326) used by GPS systems, it must be specified. Geography routines do not support curves, TINS, or POLYHEDRALSURFACEs.

PostGIS PostgreSQL GIS Functions:

Relationships and Measurements:

Function	Description
ST_3DClosestPoint()	Find the closest point in the table
ST_Covers()	one geometric entity covers another
ST_Contains()	Polygon contains another geometric entity
ST_Crosses()	line crosses another line
ST_Distance()	find the distance between two entities
ST_Dwithin()	one geometric entity lies within another
...	...

Transformations:

Function	Description
ST_AddPoint()	Add a point
ST_LineMerge()	merge multiple lines together
ST_RotateX()	Rotate entity about the X-axis
ST_Transform()	perform transformation
ST_Translate()	translate geometric object
ST_Scale()	scale a geometric object
ST_SnapToGrid()	position entity to grid position alignment
...	...

PostGIS cheat sheet of functions

QGIS GIS Viewer:

QGIS is a GIS GUI tool which interfaces with PostGIS and PostgreSQL and allows for the visualization of database geometry and the results of GIS queries. QGIS operates equally on 2D and 3D geography schemas.

Install: sudo apt-get install qgis

QGIS Display PostGIS Data:

Right click on "PostGIS" and enter the PostgreSQL connection information.

Select the "Connect" button to get list. Select the "ski_resorts" table and the points will be displayed on the map.

Select the "ski_resorts" layer and select the symbol you wish to use (in this case "star").

Right click on "WMS" + "New connection..." and enter the "GetCapabilities" URL.

View showing the "BlueMarble June 2004" world map and the ski resort locations stored in the PostGIS/PostreSQL database table "ski_resorts". Note that the "ski_resorts" layer is above the "BlueMarble June 2004" layer. This is important for viewing so that the points are not masked. If the order is incorrect, drag the layers into the correct order as shown.

Select the "i" (Identify Features) icon and then select the point to query.

A label can also be assigned to the point, in this case the SQL column "name".

Point labels

QGIS Display PostGIS Query Results:

QGIS can display the geometry results of a spatial query in two ways:

Use a database VIEW
Directly display the results of a database query

1) Database VIEW:

First generate a database "VIEW" to store the results of the query, then add the "VIEW" as a QGIS layer.

mountains=# CREATE VIEW vw_select_co AS 
mountains=#   SELECT id, name, state_code, country_code, location
mountains=#     FROM ski_resorts WHERE state_code='CO';
CREATE VIEW
mountains=# GRANT ALL PRIVILEGES ON vw_select_co to dude;
GRANT

In QGIS open the DB Manager: From the toolbar select "Database" + "DB Manager" + "DB Manager"

PostgreSQL VIEW showing the resorts in Colorado (state code 'CO')

2) Query Results:

From the DM Manager, select the wrench icon to get this window. Note the items selected such as "Load as new layer" and the selection of columns. This requires the use of a unique integer as a column in the table. Remember this when developing database schemas for use with QGIS.