Adding GIS Support to Postgres

create role gis_admin login password 'gis_password';

GRANT rds_superuser TO gis_admin;
create database gis;
grant all privileges on database gis to gis_admin;

psql -h <hostname> –username=gis_admin –password –dbname=<dbname>

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION address_standardizer_data_us;

Verify

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;

Transfer ownership

ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA tiger_data OWNER TO gis_admin; 
ALTER SCHEMA topology OWNER TO gis_admin;

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;

-- This 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;

Testing out the installation

SET search_path=public,tiger;

-- Test it out again
-- Should return: 
--  address | streetname | streettypeabbrev |  zip  
-- ---------+------------+------------------+-------
--        1 | Devonshire | Pl               | 02109


SELECT address, streetname, streettypeabbrev, zip
 FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;

-- Should return 1
SELECT topology.createtopology('my_new_topo',26986,0.5);