Accessing PostGIS on a cloud.gov-brokered PostgreSQL database

instructions.md

Note - the steps below are adapted from these instructions.

Prerequisites

• Install Cloud Foundry CLI tool.
• Install Cloud Foundry CLI Service Connection Plugin.

Create an RDS service

~$ cf create-service aws-rds medium-psql postgis-test

Once the database spins up (and it can take several minutes for it to do so), connect to the the RDS service. Note - if you don't have a deployed app, you can use one of the cf-hello-worlds apps from here. For the purposes of this example, the app instance is simply used to support an SSH tunnel to connect to the RDS service:

~$ cf connect-to-service {name-of-your-app} postgis-test

This will drop you into a psql prompt that you can use to interact with the service.

Add PostGIS to your database

Get the current user name:

=> select current_user;

Make note of the user name, and use it when invoking the postgis-init.sql script:

=> \set user '{current_user}' \i path/to/postgis-init.sql

Test it out

Check that PostGIS is installed:

=> SELECT PostGIS_full_version();

Test out access to the TIGER tables:

=> SET search_path=public,tiger; 
=> select na.address, na.streetname, na.streettypeabbrev, na.postdirAbbrev, na.location, na.stateAbbrev, na.zip from normalize_address('1800 F St NW, Washington, DC 20006') as na;

 address | streetname | streettypeabbrev | postdirabbrev |  location  | stateabbrev |  zip  
---------+------------+------------------+---------------+------------+-------------+-------
    1800 | F          | St               | NW            | Washington | DC          | 20006
(1 row)

postgis-init.sql

CREATE EXTENSION postgis;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;

ALTER SCHEMA tiger OWNER TO :user;
ALTER SCHEMA tiger_data OWNER TO :user;
ALTER SCHEMA topology OWNER TO :user;

ALTER TABLE tiger.zip_state OWNER TO :user;
ALTER TABLE tiger.countysub_lookup OWNER TO :user;
ALTER TABLE tiger.zip_lookup_all OWNER TO :user;
ALTER TABLE tiger.cousub OWNER TO :user;
ALTER TABLE tiger.addr OWNER TO :user;
ALTER TABLE tiger.zcta5 OWNER TO :user;
ALTER TABLE tiger.pagc_gaz OWNER TO :user;
ALTER TABLE tiger.pagc_lex OWNER TO :user;
ALTER TABLE tiger.pagc_rules OWNER TO :user;