## To convert State Plane to Lat/Lng (WGS 84)
## could also use: http://gitorious.org/tlevine/noaa_post_project/blobs/master/noaa.py but it hits the web
brew install postgis
brew install gdal
# from http://russbrooks.com/2010/11/25/install-postgresql-9-on-os-x
# NB: Out of the box, you authenticate against the DB using the same user account under which you ran brew install.
#initialize the DB
initdb /usr/local/var/postgres
# add startup items
cp /usr/local/Cellar/postgresql/9.0.4/org.postgresql.postgres.plist ~/Library/LaunchAgents
launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
#start Postgres.
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
    
# confirm that it works
psql postgres -c "select 1;"

# create spatial db template
createdb template_postgis
psql -d template_postgis -f /usr/local/Cellar/postgresql/9.0.4/share/postgresql/contrib/postgis-1.5/postgis.sql
psql -d template_postgis -f /usr/local/Cellar/postgresql/9.0.4/share/postgresql/contrib/postgis-1.5/spatial_ref_sys.sql 


# create DB based on template
createdb -T template_postgis nyclu
psql -d nyclu

# for every column in header, make a text entry
# run that as a command in psql
cat CREATE_TABLE.txt | pbcopy
# inside psql, paste    

# create sql dump
ogr2ogr -f PGDump sqf_2010.sql 2010.csv 

# import data
psql -d nyclu -f sqf_2010.sql

# create a geometry column
SELECT AddGeometryColumn ('public','sqf_2010','the_geom',4326,'POINT',2);
    
# populate geom
UPDATE "sqf_2010" SET the_geom = ST_Transform(ST_GeomFromText('POINT('|| xcoord ||' ' || ycoord || ')', 2263), 4326) WHERE coalesce(trim(xcoord),'')  <> '' AND coalesce(trim(ycoord),'')  <> '';

# create index
CREATE INDEX idx_2010_the_geom ON "2010" USING GIST ( the_geom );

# create new table with the stuff we want in it
create table state_plane_to_latlng as
SELECT DISTINCT xcoord, ycoord
, ST_x(the_geom) as longitude
, ST_y(the_geom) as latitude
from "sqf_2010"
ORDER BY xcoord, ycoord;

# export table to csv
COPY state_plane_to_latlng TO '/state_plane_to_latlng.csv' CSV HEADER;

# for some reason there's an empty line after the header