Set up a spatial database with PostGIS on EC2 using an EBS.

PostGIS install

Steps:
Set up EBS
21Created 1 TB EBS
	Created 64 Bit Amazon Linux AMI 2012.09 M1 Medium 3.7 gb ram, 2 ECUs

	Connect to ec2 instance via ssh
	chmod 400 IFS-KeyPair.pem
	ssh -v -i IFS-KeyPair.pem [email protected]
	Note: Do not replace ec2-user with your user id. This is a AWS Amazon AMI requirement.

	Attached the EBS volume to the EC2 Instance
	http://www.webmastersessions.com/how-to-attach-ebs-volume-to-amazon-ec2-instance
	Logged in to the EC2 instance, 
	The volume should show up as /dev/sdf or something.
	
	#Format the volume
	sudo mkfs.ext3 /dev/sdf  

	#create the directory to link the volume to.
	sudo mkdir /spatial-db

	#link the directory to the EDS Volume by modifying the fstab file
	echo “/dev/sdf /spatial-db ext3 noatime 0 0″ >> /etc/fstab

	#mount the directory
	mount /spatial-db

	#check the disk size
	df -h /spatial-db

	# to just do a one time mount you can use the cmd
	mount /dev/sdf /spatial-db

	#fstab on AWS sucks. Look at using autofs
	#http://www.centos.org/docs/2/rhl-rg-en-7.2/s1-nfs-client-config.html
	Right now will just need to manually do the mount after reboots

Install PostgreSQL
	#utility docs at http://www.postgresql.org/docs/9.0/static/reference-client.html

	#install Postgress...
	sudo yum install postgresql postgresql-server postgresql-devel postgresql-contrib

	#Open port 5432 for postgress
	In the EC2 Security Group, add a TCP rule to allow 5432 for IP 0.0.0.0/0

	#change ownership of the /spatial-db/db directory to the postgres user and group, and change to the postgres user. As the postgres user, we 
	# can configure and launch the server.
	sudo chown -R postgres:postgres /spatial-db/db
	sudo su -
	su postgres -
	initdb -D /spatial-db/db

	#update the config files. Need to be the postgres user when doing this.
	vim /spatial-db/db/postgresql.conf

	localhost='*'
	port=5432
	######################################################################
	vim /spatial-db/db/pg_hba.conf

	#change the bottom of the file to look like:
	# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

	# "local" is for Unix domain socket connections only
	local   all             postgres                                trust
	# IPv4 local connections:
	host    all             pg_power_user      0.0.0.0/0               md5  
	host    all             pg_query_user      0.0.0.0/0               md5 
	# IPv6 local connections:
	host    all             all             ::1/128                 md5

	######################################################################

	#should now be able to start the db with (I think I still need to be user postgres)
	pg_ctl -w start -D /spatial-db/db

	#to stop, do 
	pg_ctl -w stop -D /spatial-db/db

	#to restart
	pg_ctl -w restart -D /spatial-db/db

	#health check
	pg_ctl status -D /spatial-db/db	

	######################################################################
	#create the Postgress User Accounts. pg_power_user is a superuser, pg_query_user is not
	createuser --interactive pg_power_user
	createuser --interactive pg_query_user

	#Connect to the database as postgres, and set the new user passwords 
	psql -p 5432
	postgres=# ALTER USER pg_power_user WITH PASSWORD 'Q98356Gdv';
	postgres=# ALTER USER pg_query_user WITH PASSWORD 'asjfienSwer8!';

	#create a database for user pg_query_user
	postgres=# CREATE DATABASE test_pg_db WITH OWNER pg_query_user;

	######################################################################
	#Install pgAdmin from http://pgadmin.org/download/macosx.php
	#can connect with pg_power_user or pg_query_user
	#Test that you can remotely connect to the DB and create some tables 
	######################################################################

Install PostGIS
	#based on http://imperialwicket.com/aws-configuring-a-geo-spatial-stack-in-amazon-linux
	#Set up PostGIS 2.0 - Have to compile it. Not in Amazon's yum repo and I don't want to hack RPM
	# First install gcc and friends
	sudo yum install gcc make gcc-c++ libtool libxml2-devel

	# make a directory for building
	cd /home/ec2-user/
	mkdir postgis 
	cd postgis

	# download, configure, make, install geos (GEOS 3.3.2+ is recommended.)
	wget http://download.osgeo.org/geos/geos-3.3.6.tar.bz2
	tar xjf geos-3.3.6.tar.bz2
	cd geos-3.3.6
	./configure
	make
	sudo make install

	# download, configure, make, install proj (version 4.6.0 or greater)
	cd /home/ec2-user/postgis/
	wget http://download.osgeo.org/proj/proj-4.8.0.tar.gz
	wget http://download.osgeo.org/proj/proj-datumgrid-1.5.zip
	tar xzf proj-4.8.0.tar.gz
	cd proj-4.8.0/nad
	unzip ../../proj-datumgrid-1.5.zip
	cd ..
	./configure  
	make
	sudo make install

	# download, configure, make, install postgis 2.x
	cd /home/ec2-user/postgis/
	wget http://download.osgeo.org/postgis/source/postgis-2.0.2.tar.gz
	tar xzf postgis-2.0.2.tar.gz 
	cd postgis-2.0.2
	./configure --with-geosconfig=/usr/local/bin/geos-config --without-raster
	make
	sudo make install

	# update your libraries
	sudo su
	echo /usr/local/lib >> /etc/ld.so.conf
	exit
	sudo ldconfig

Create an actual spatial database
	#switch to the postgres user
	sudo su -
	su postgres -

	#create the database
	createdb --owner pg_query_user my-spatial-db
	createlang plpgsql my-spatial-db
	psql -d my-spatial-db -f /usr/share/pgsql/contrib/postgis-2.0/postgis.sql
	psql -d my-spatial-db -f /usr/share/pgsql/contrib/postgis-2.0/spatial_ref_sys.sql

Shove some spatial data into it
	#http://postgis.net/docs/manual-2.0/PostGIS_FAQ.html#id367489
	#remember that the postgres user is the only user with local connection rights...
	psql -U postgres -d my-spatial-db

	#in psql
	#create a table
	#always use srid = 4326 for WGS 84                                     WKT TYpe, Projection srid
	CREATE TABLE some_lon_lats (id SERIAL, name VARCHAR(24),geom geometry('POINT',4326));

	#Insert the longs/lats
	INSERT INTO some_lon_lats (name,geom) VALUES 
	('point a',ST_GeomFromText('POINT(-73.9567 40.7879)',4326)),
	('point b',ST_GeomFromText('POINT(-73.7689 41.1234)',4326)),
	('point c',ST_GeomFromText('POINT(-73.1145 40.5645)',4326)),
	('point d',ST_GeomFromText('POINT(-73.1212 40.9844)',4326)),
	('point e',ST_GeomFromText('POINT(-72.9554 39.9889)',4326)),
	('point f',ST_GeomFromText('POINT(-73.6555 41.2543)',4326));


	#to view the table:
	select id, name, ST_AsText(geom) from some_lon_lats;