Skip to content

Instantly share code, notes, and snippets.

Last active April 9, 2021 12:26
Show Gist options
  • Save mheadd/43b12c9d22f30ba029194dedf0682b95 to your computer and use it in GitHub Desktop.
Save mheadd/43b12c9d22f30ba029194dedf0682b95 to your computer and use it in GitHub Desktop.
Accessing PostGIS on a PostgreSQL database

Note - the steps below are adapted from these instructions.


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, 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)
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment