Working with PostGIS
PostGIS is an extension to PostgreSQL for storing and managing spatial information.
If you
are not familiar with PostGIS, see PostGIS.net
You need to perform a bit of setup before you can use the PostGIS extension. The following list shows what you need to do; each step is described in greater detail later in this section.
-
Connect to the DB instance using the master user name used to create the DB instance.
-
Load the PostGIS extensions.
-
Transfer ownership of the extensions to the
rds_superuser
role. -
Transfer ownership of the objects to the
rds_superuser
role. -
Test the extensions.
Step 1: Connect to the DB instance using the master user name used to create the DB instance
First, you connect to the DB instance using the master user name that was used to
create
the DB instance. That name is automatically assigned the rds_superuser
role.
You need the rds_superuser
role that is needed to do the remaining
steps.
The following example uses SELECT
to show you the current user. In this case, the current user
should be the master user name you chose when creating the DB instance.
SELECT CURRENT_USER;
current_user ------------- myawsuser (1 row)
Step 2: Load the PostGIS extensions
Use CREATE EXTENSION
statements to load the PostGIS extensions. You must also load the
extension. You can then use the
\dn
command to list the owners of the PostGIS schemas.
CREATE EXTENSION postgis; CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION postgis_topology; \dn
List of schemas Name | Owner --------------+----------- public | myawsuser tiger | rdsadmin tiger_data | rdsadmin topology | rdsadmin (4 rows)
Step 3: Transfer ownership of the extensions to the rds_superuser role
Use the ALTER SCHEMA statements to transfer ownership of the schemas to the
rds_superuser
role.
ALTER SCHEMA tiger OWNER TO rds_superuser; ALTER SCHEMA tiger_data OWNER TO rds_superuser; ALTER SCHEMA topology OWNER TO rds_superuser; \dn
List of schemas Name | Owner --------------+--------------- public | myawsuser tiger | rds_superuser tiger_data | rds_superuser topology | rds_superuser (4 rows)
Step 4: Transfer ownership of the objects to the rds_superuser role
Use the following function to transfer ownership of the PostGIS objects to the
rds_superuser
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$;
Next, run this 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 rds_superuser;') 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;
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)