Deploy spatial data with PostgreSQL¶

The following document provides guidelines how to install PostGIS and how to run the basic queries.

Considerations¶

We assume that you have the basic knowledge of spatial data, GIS (Geographical Information System) and of shapefiles.
For uploading the spatial data and querying the database, we use the same data set as is used in PostGIS tutorial

Install PostGIS¶

On Debian and UbuntuOn RHEL and derivatives

Enable Percona repository

As other components of Percona Distribution for PostgreSQL, PostGIS is available from Percona repositories. Use the percona-release repository management tool to enable the repository.
```
$ sudo percona-release setup ppg11
```
Install PostGIS packages
```
$ sudo apt install percona-postgis
```
The command in the previous step installs the set of PostGIS extensions. To check what extensions are available, run the following query from the psql terminal:
```
SELECT name, default_version,installed_version
FROM pg_available_extensions WHERE name LIKE 'postgis%' or name LIKE address%';
```
Note

To enable the postgis_sfcgal-3 extension on Ubuntu 18.04, you need to manually install the required dependency:
```
$ sudo apt-get install libsfcgal1
```

For Red Hat Enterprise Linux 8 and derivatives, replace the operating system version in the following commands accordingly.

RHEL 9CentOS 9Oracle Linux 9Rocky Linux 9RHEL UBI 9

Enable Percona repository

As other components of Percona Distribution for PostgreSQL, PostGIS is available from Percona repositories. Use the percona-release repository management tool to enable the repository.
```
$ sudo percona-release setup ppg11
```
Install epel repository
```
$ sudo yum install epel-release
```
Enable the llvm-toolset dnf module
```
$ sudo dnf module enable llvm-toolset
```

Enable the codeready builder repository to resolve dependencies conflict.

$ sudo dnf config-manager --set-enabled codeready-builder-for-rhel-9-x86_64-rpms

Install the extension

$ sudo yum install percona-postgis33 percona-postgis33-client

Enable Percona repository

As other components of Percona Distribution for PostgreSQL, PostGIS is available from Percona repositories. Use the percona-release repository management tool to enable the repository.
```
$ sudo percona-release setup ppg11
```
Install epel repository
```
$ sudo yum install epel-release
```
Enable the llvm-toolset dnf module
```
$ sudo dnf module enable llvm-toolset
```
Enable the codeready builder repository to resolve dependencies conflict.
```
$ sudo dnf config-manager --set-enabled crb
```

Install the extension

$ sudo yum install percona-postgis33 percona-postgis33-client

Enable Percona repository

As other components of Percona Distribution for PostgreSQL, PostGIS is available from Percona repositories. Use the percona-release repository management tool to enable the repository.
```
$ sudo percona-release setup ppg11
```
Install epel repository
```
$ sudo yum install epel-release
```
Enable the llvm-toolset dnf module
```
$ sudo dnf module enable llvm-toolset
```
Enable the codeready builder repository to resolve dependencies conflict.
```
$ sudo dnf config-manager --set-enabled ol9_codeready_builder
```

Install the extension

$ sudo yum install percona-postgis33 percona-postgis33-client

Enable Percona repository

As other components of Percona Distribution for PostgreSQL, PostGIS is available from Percona repositories. Use the percona-release repository management tool to enable the repository.
```
$ sudo percona-release setup ppg11
```
Install epel repository
```
$ sudo yum install epel-release
```
Enable the llvm-toolset dnf module
```
$ sudo dnf module enable llvm-toolset
```

Enable the codeready builder repository to resolve dependencies conflict.

$ sudo dnf install dnf-plugins-core
$ sudo dnf config-manager --set-enabled powertools

Install the extension

$ sudo yum install percona-postgis33 percona-postgis33-client

Configure the Oracle-Linux repository. Create the /etc/yum.repos.d/oracle-linux-ol9.repo file to install the required dependencies:

/etc/yum.repos.d/oracle-linux-ol9.repo

[ol9_baseos_latest]
name=Oracle Linux 9 BaseOS Latest ($basearch)
baseurl=https://yum.oracle.com/repo/OracleLinux/OL9/baseos/latest/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1     

[ol9_appstream]
name=Oracle Linux 9 Application Stream ($basearch)
baseurl=https://yum.oracle.com/repo/OracleLinux/OL9/appstream/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1     

[ol9_codeready_builder]
name=Oracle Linux 9 CodeReady Builder ($basearch) - Unsupported
baseurl=https://yum.oracle.com/repo/OracleLinux/OL9/codeready/builder/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

Download the right GPG key for the Oracle Yum Repository:

$ wget https://yum.oracle.com/RPM-GPG-KEY-oracle-ol9 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

Enable Percona repository

As other components of Percona Distribution for PostgreSQL, PostGIS is available from Percona repositories. Use the percona-release repository management tool to enable the repository.
```
$ sudo percona-release setup ppg11
```
Install epel repository
```
$ sudo yum install epel-release
```
Disable the upstream postgresql package:
```
$ sudo dnf module disable postgresql
```

Install the extension

$ sudo yum install percona-postgis33 percona-postgis33-client

This installs the set of PostGIS extensions. To check what extensions are available, run the following query from the psql terminal:

SELECT name, default_version,installed_version
FROM pg_available_extensions WHERE name LIKE 'postgis%' or name LIKE 'address%';

Enable PostGIS extension¶

Create a database and a schema for this database to store your data. A schema is a container that logically segments objects (tables, functions, views, and so on) for better management. Run the following commands from the psql terminal:
```
CREATE DATABASE nyc;
\c nyc;
CREATE SCHEMA gis;
```
To make PostGIS functions and operations work, you need to enable the postgis extension. Make sure you are connected to the database you created earlier and run the following command:
```
\c nyc;
CREATE EXTENSION postgis;
```

Check that the extension is enabled:

SELECT postgis_full_version();

The output should be similar to the following:

postgis_full_version
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.3.3" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

Upload spatial data to PostgreSQL¶

PostGIS provides the shp2pgsql command line utility that converts the binary data from shapefiles into the series of SQL commands and loads them into the database.

For testing purposes, download the sample data set:

$ curl -LO https://s3.amazonaws.com/s3.cleverelephant.ca/postgis-workshop-2020.zip

Unzip the archive. From the folder where the .shp files are located, execute the following command and replace the dbname value with the name of your database:
```
shp2pgsql \
  -D \
  -I \
  -s 26918 \
  nyc_streets.shp \
  nyc_streets \
  | psql -U postgres dbname=nyc
```
The command does the following:
- -D flag instructs the command to generate the dump format
- -I flag instructs to create the spatial index on the table upon the data load
- -s indicates the spatial reference identifier of the data. The data we load is in the Projected coordinate system for North America and has the value 26918.
- nyc_streets.shp is the source shapefile
- nyc_streets is the table name to create in the database
- dbname=nyc is the database name
Check the uploaded data

\d nyc_streets;
                                         Table "public.nyc_streets"
 Column |              Type               | Collation | Nullable |                 Default
--------+---------------------------------+-----------+----------+------------------------------------------
 gid    | integer                         |           | not null | nextval('nyc_streets_gid_seq'::regclass)
 id     | double precision                |           |          |
 name   | character varying(200)          |           |          |
 oneway | character varying(10)           |           |          |
 type   | character varying(50)           |           |          |
 geom   | geometry(MultiLineString,26918) |           |          |
Indexes:
    "nyc_streets_pkey" PRIMARY KEY, btree (gid)
    "nyc_streets_geom_idx" gist (geom)

Repeat the command to upload other shapefiles in the data set: nyc_census_blocks, nyc_neighborhoods, nyc_subway_stations

Contact Us

For free technical help, visit the Percona Community Forum.

To report bugs or submit feature requests, open a JIRA ticket.

For paid support and managed or consulting services , contact Percona Sales.

Last update: October 13, 2023
Created: June 30, 2023