Setting up postgres.app with PostGIS

I should know better, but I'm a sucker for an upgrade. I upgraded to OS X Mountain Lion and a number of apps I use wouldn't start. A brew installed version of PostgreSQL couldn't start because the postgres user was changed to nobody. For some reason, I couldn't su to nobody or create a new postgres user using dscl.

The data on the database wasn't particularly large and I also had the scripts to reload the data so I decided to try Heroku's postgres.app install of postgres.
Installation is simple. Download the app and unzip into the Applications directory. Double click to start, and the postgres icon will appear on the task bar.

In order to use the commandline tools, you will need to set the path to the postgres.app binaries. Add this to your .bash_profile or wherever your paths are set in you .dot files. (note: @mweissman suggested this is unnecessary on OSX Lion 10.7 and higher because PostgreSQL and the client tools are already part of the operating system.)

PATH="/Applications/Postgres.app/Contents/MacOS/bin:$PATH"

view raw setpath.sh hosted with ❤ by GitHub

In the terminal, connect to the postgres using psql and create a database. Connect to the database and add the PostGIS extension.

	# connect to postgres
	psql -h localhost
	# create database
	CREATE DATABASE mydatabase;
	# switch to your database
	\connect mydatabase
	# add postgis extension
	CREATE EXTENSION postgis;

view raw createdb.sql hosted with ❤ by GitHub

Now that the PostGIS extension has been added to your database, creating table is straight forward. In this example, I'm creating a table from a CSV file with point geometry and creating a spatial index on the geometry.

	-- create the table
	CREATE TABLE brady (
	location varchar,
	date date,
	description text,
	latitude real,
	longitude real);
	-- read CSV file into the table (actually tab delimited)
	COPY brady (location, date, description, latitude, longitude)
	FROM '/Users/sparafina/projects/gun_violence/major-shootings_geocoded.csv'
	WITH DELIMITER AS "\t"
	CSV HEADER;
	-- add geometry column
	SELECT AddGeometryColumn('public', 'brady', 'geom', 4326, 'POINT', 2);
	-- create points from latitude and longitude fields
	UPDATE brady SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);
	-- create a spatial index on the geometry
	CREATE INDEX brady_idx ON brady USING GIST ( geom );

view raw create_point.sql hosted with ❤ by GitHub

And that's it, quick and simple.