MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘PostGIS’ Category

Add PostGIS to PostgreSQL

without comments

The following blog post shows you how to add PostGIS and PgRouting to your existing install of PostgeSQL 14 on the Ubuntu Desktop, Version 22.0.4. This blog post relies on information in this earlier Install and Configure PostgreSQL on Ubuntu post. Generalized documentation on PostGIS exists at this URL.

You install the postgis libraries:

sudo apt install -y postgis

You install the postgresql-14-pgrouting libraries:

sudo apt install -y postgresql-14-pgrouting

You should also install ogr2ogr program, which is a command-line utility for converting data between GIS data formats, including common file formats and common spatial databases. You install the ogr2ogr libraries:

sudo apt install -y gdal-bin

Verify the installation by using the which utility, like

which -a ogr2ogr

It should return:

/usr/bin/ogr2ogr

You can qualify the installed PostGIS packages with the following command:

dpkg -l | grep -i postgis

It should display:

ii  postgis                                    3.2.0+dfsg-1ubuntu1                     amd64        Geographic objects support for PostgreSQL
ii  postgis-doc                                3.2.0+dfsg-1ubuntu1                     all          Geographic objects support for PostgreSQL -- documentation
ii  postgresql-14-pgrouting                    3.3.0-2                                 amd64        Routing functionality support for PostgreSQL/PostGIS
ii  postgresql-14-pgrouting-scripts            3.3.0-2                                 all          Routing functionality support for PostgreSQL/PostGIS - SQL scripts
ii  postgresql-14-postgis-3                    3.2.0+dfsg-1ubuntu1                     amd64        Geographic objects support for PostgreSQL 14
ii  postgresql-14-postgis-3-scripts            3.2.0+dfsg-1ubuntu1                     all          Geographic objects support for PostgreSQL 14 -- SQL scripts

Connect as the postgres user by becoming the root user with this command:

sudo sh

Then, assume the role of the postgres user with this command:

su - postgres

Connect to the PostgreSQL database as the privileged postgres owner/user:

psql postgres

You will see the following prompt after connecting to the PostgreSQL database:

postgres@student-virtual-machine:~$ psql
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
Type "help" for help.
 
postgres=#

As the the privileged postgres owner/user issue the following commands to create the gisdb database and set a new search path for it:

CREATE DATABASE gisdb;
ALTER DATABASE gisdb SET search_path=public,postgis,contrib,tiger;

Connect to the gisdb database:

\connect gisdb

You are now connected to database gisdb as the postgres user. You change to the postgis schema, and create the following extensions in this schema.

CREATE SCHEMA postgis;
CREATE EXTENSION postgis SCHEMA postgis;
CREATE EXTENSION postgis_raster SCHEMA postgis;
CREATE EXTENSION fuzzystrmatch SCHEMA postgis;
CREATE EXTENSION address_standardizer_data_us SCHEMA postgis;

You query the modified catalog with this query:

SELECT n.nspname AS "Name"
,      pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM   pg_catalog.pg_namespace n
WHERE  n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
ORDER BY 1;

It should return:

  Name   |  Owner   
---------+----------
 postgis | postgres
 public  | postgres
(2 rows)

You must assign the postgis_tiger_geocoder and postgis_topology without a schema assignment. The
postgis_tiger_eeocoder must be assigned by default to the tiger schema, and the postgis_topology schema.

CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;

You reuse the same above referenced query to see the modified catalog with this query:

It should return:

    Name    |  Owner   
------------+----------
 postgis    | postgres
 public     | postgres
 tiger      | postgres
 tiger_data | postgres
 topology   | postgres
(5 rows)

Connect as the gisdb database with this command:

\connect gisdb

You can see the active PostGIS extension with this command:

\dx postgis

It shows:

                               List of installed extensions
  Name   | Version | Schema  |                        Description                         
---------+---------+---------+------------------------------------------------------------
 postgis | 3.2.0   | postgis | PostGIS geometry and geography spatial types and functions
(1 row)

Now, you can use this query:

SELECT postgis_full_version();

to discover what PostGIS version is installed:

                                                                       postgis_full_version                                                                        
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.12" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
(1 row)

Connect back to as the privileged postgres owner/user with this command:

\connect postgres

Next, check the available databases with this command:

\l

It should display the following:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 gisdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 videodb   | student  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/student          +
           |          |          |             |             | student=CTc/student  +
           |          |          |             |             | dba=CTc/student
(5 rows)

As the privileged postgres owner/user make the following grants with these commands:

GRANT TEMPORARY, CONNECT ON DATABASE gisdb TO PUBLIC;
GRANT ALL PRIVILEGES ON DATABASE gisdb TO postgres;
GRANT ALL PRIVILEGES ON DATABASE gisdb TO dba;

After making the grants, check the available databases access with this \l command:

\l

It should display the following:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 gisdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | dba=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 videodb   | student  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/student          +
           |          |          |             |             | student=CTc/student  +
           |          |          |             |             | dba=CTc/student
(5 rows)

At this point, you can exit psql, the postgres user’s account, and the root user’s account. This should return you to your sudoer account, which in my case is the student user.

Connect to the gisdb with the following command-line interface command:

psql -U student -W -d gisdb

You can create the following, as per instructions in PostGIS Chapter 9 instructions:

-- Conditionally drop table.
DROP TABLE IF EXISTS geometries;
 
-- Create table with geometry column in table.
CREATE TABLE geometries
( geometries_id  INT
, name           VARCHAR
, geometry_obj   GEOMETRY);
 
 
INSERT INTO geometries
( name
, geometry_obj )
VALUES
 ('Point', 'POINT(0 0)')
,('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)')
,('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')
,('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))')
,('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');
 
SELECT name
,      ST_AsText(geometry_obj)
FROM   geometries;

Unfortunately, it raises the following error when attempting to create the geometries table:

psql:/home/student/Code/postgis/geometry.sql:7: ERROR:  type "geometry" does not exist
LINE 4: , geometry_obj   GEOMETRY);

As always, I hope the solutions presented helps move forward implementations of the technology. You can also find an excellent tutorial to learning PostGIS in the Introduction to PostGIS tutorial.

Written by maclochlainn

February 15th, 2024 at 10:57 pm