Managing spatial data with the PostGIS extension
PostGIS is an extension to PostgreSQL for storing and managing spatial information. To learn
more about PostGIS, see PostGIS.net
Starting with version 10.5, PostgreSQL supports the libprotobuf 1.3.0 library used by PostGIS for working with map box vector tile data.
Setting up the PostGIS extension requires rds_superuser
privileges. We recommend that you create a user (role)
to manage the PostGIS extension and your spatial data. The PostGIS extension and its related components add thousands of functions
to PostgreSQL. Consider creating the PostGIS extension in its own schema if that makes sense for your use case. The following example
shows how to install the extension in its own separate database, but this isn't required.
Topics
Step 1: Create a user (role) to manage the PostGIS extension
First, you connect to your RDS for PostgreSQL DB instance as a user that has rds_superuser
privileges. If you kept
the default name when you set up your instance, you connect as postgres
:
psql=>
psql --host=111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=postgres --password
Create a separate role (user) to administer the PostGIS extension:
postgres=>
CREATE ROLE
gis_admin
LOGIN PASSWORD 'change_me
';CREATE ROLE
Grant this role rds_superuser
privileges, to allow the role to install the extension:
postgres=>
GRANT rds_superuser TO
gis_admin
;GRANT
Create a database to use for your PostGIS artifacts:
postgres=>
CREATE DATABASE
lab_gis
;CREATE DATABASE
Give the gis_admin
all privileges on the lab_gis
database.
postgres=>
GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin;
GRANT
Exit the session and re-connect to your RDS for PostgreSQL DB instance as gis_admin
:
postgres=>
--host=
111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=gis_admin
--password --dbname=lab_gis
Password for user gis_admin:...
lab_gis=>
Continue setting up the extension as detailed in the next steps.
Step 2: Load the PostGIS extensions
Use CREATE EXTENSION
statements to load the PostGIS extensions.
CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION
You can verify the results by running the SQL query shown in the following example, which lists the extensions and their owners.
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;
List of schemas Name | Owner --------------+----------- public | postgres tiger | rdsadmin tiger_data | rdsadmin topology | rdsadmin (4 rows)
Some use cases don't require all the extensions created in this step.
Step 3: Transfer ownership of the extensions
Use the ALTER SCHEMA statements to transfer ownership of the schemas to the
gis_admin
role.
ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA
You can confirm the ownership change by running the following SQL query. Or
you can use the \dn
metacommand from the psql command line.
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;
List of schemas Name | Owner --------------+--------------- public | postgres tiger | gis_admin tiger_data | gis_admin topology | gis_admin (4 rows)
Step 4: Transfer ownership of the PostGIS objects
Use the following function to transfer ownership of the PostGIS objects to the
gis_admin
role. Run the following statement from the psql prompt to
create the function.
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
Next, run the following query to run the exec
function that in turn runs
the statements and alters the permissions.
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;') FROM ( SELECT nspname, relname FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) WHERE nspname in ('tiger','topology') AND relkind IN ('r','S','v') ORDER BY relkind = 'S') s;
Step 5: Test the extensions
Add tiger
to your search path using the following command.
SET search_path=public,tiger;
SET
Test tiger
by using the following SELECT statement.
SELECT na.address, na.streetname, na.streettypeabbrev, na.zip FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev | zip ---------+------------+------------------+------- 1 | Devonshire | Pl | 02109 (1 row)
Test topology
by using the following SELECT statement.
SELECT topology.createtopology('my_new_topo',26986,0.5);
createtopology ---------------- 1 (1 row)
Step 6: Update the PostGIS extension
Different versions of PostgreSQL support different versions of the PostGIS extension. You can check to see if an upgrade is available by running the following command. This function is available with PostGIS 2.5.0 and higher versions.
SELECT PostGIS_Extensions_Upgrade();
Depending on the version that you're upgrading from, this function might need to run a second time. The result of the first run of the function determines if an additional upgrade function is needed.
If your application doesn't support the latest PostGIS version, you can still create an older version of PostGIS that is available in your major version as follows.
CREATE EXTENSION postgis VERSION "2.5.5"
If you want to upgrade to a specific PostGIS VERSION from an older version, you can also use the following command.
ALTER EXTENSION postgis UPDATE TO "2.5.5"
PostGIS extension versions
You can list the versions that are available in your release by using the following command.
SELECT * FROM pg_available_extension_versions WHERE name='postgis';
You can also find version information in the following sections in the Amazon RDS for PostgreSQL Release Notes: