Mike's Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Adding GIS Support to Postgres ======

<code>
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;
</code>

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

<code>
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;
</code>

=== Verify ===

<code>
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;
  </code>

=== Transfer ownership ===

<code>
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;
</code>

=== Testing out the installation ===
<code>
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);

</code>