The most common commands so I don't forget!

• to start the Postgres server do: postgres -D /usr/local/var/postgres

• or do pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start to start and pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop to stop

• to have Postgres start everytime you boot your Mac do: ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents then to check that it's working after booting do: ps ax | grep sql

psql is the unix command line tool for interacting with postgres.

• for doing admin type things log in as postgres: psql postgres;

• to create a databse: CREATE database-name;

• to delete a database DROP DATABASE database-name;

• to connect to a database: \c database-name;

• to connect when starting psql use the -d flag like: psql -d nyc_noise

• to list all databases: \l

• to quit psql: \q

• to grant privileges to a user: GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

• to enable the hstore extension ( for key : value pairs) do: CREATE EXTENSION hstore

• to view columns of a table: \d table_name

• to rename a column: alter table noise.hoods rename column noise_sqkm to complaints_sqkm;

• to change a column's data type: alter table noise.hoods alter column noise_area type float;

• to compute values from two columns and assign them to another column: update noise.hoods set noise_area = noise/(area/1000);

• create a new table for data from a CSV that has lat and lon columns:

  create table noise.locations
  (                                     
    name varchar(100),
    complaint varchar(100), descript varchar(100),
    boro varchar(50),
    lat float8,
    lon float8,
    geom geometry(POINT, 4326)
  );
  

• importing data from a CSV file:

   COPY noise.locations (name, complaint, descript, boro, lat, lon)
   FROM '/Users/chrislhenrick/tutorials/postgresql/data/noise.csv' WITH CSV HEADER;
  

• list all columns from a table in a db in alphabetical order:

  select column_name
  from information_schema.columns
  where table_schema = 'public'
  and table_name = 'bk_pluto'
  order by column_name;
  

PostGIS is the extension for Postgres that allows for working with geometry data types and doing GIS operations in Postgres.

• to enable PostGIS on a regular Postgres table do: CREATE EXTENSION postgis;

• to enable PostGIS topology do: CREATE EXTENSION postgis_topology;

• to update a column from table A with the number of points from table B that intersect table A's polygons:

update noise.hoods set num_complaints = (
	select count(*)
	from noise.locations
	where
	ST_Intersects(
		noise.locations.geom,
		noise.hoods.geom
	)
);

• calculating area in EPSG 4326:
  alter table noise.hoods set area = (select ST_Area(geom::geography));

• inputing values for the geometry type after loading data from a CSV:
  update noise.locations set the_geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);

Makes queries hella fast. OSGeo has a good tutorial.

• Basically the steps are:
  CREATE INDEX table_name_gix ON table_name USING GIST (geom);
VACUUM ANALYZE table_name
CLUSTER table_name
  Do this after making changes to your dataset or importing new data.

1. Do:
   shp2pgsql -I -s 4326 nyc-pediacities-hoods-v3-edit.shp noise.hoods > noise.sql
   Or for using the geography data type do:
   shp2pgsql -G -I nyc-pediacities-hoods-v3-edit.shp noise.nyc-pediacities-hoods-v3-edit_geographic > nyc_pediacities-hoods-v3-edit.sql

2. Do:
   psql -d nyc_noise -f noise.sql
   Or for the geography type above:
   psql -d nyc_noise -f nyc_pediacities-hoods-v3-edit.sql

To import an OpenStreetMap extract in PBF format do:
osm2pgsql -H localhost --hstore-all -d nyc_from_osm ~/Downloads/newyorkcity.osm.pbf

Example importing a GeoJSON file into a database called nyc_pluto:
ogr2ogr -f PostgreSQL PG:"host='localhost' user='chrislhenrick' port='5432' dbname='nyc_pluto' password=''" bk_map_pluto_4326.json -nln bk_pluto

Note: You may need to set the GDAL_DATA path if you git this error:

ERROR 4: Unable to open EPSG support file gcs.csv.
Try setting the GDAL_DATA environment variable to point to the
directory containing EPSG csv files.

If on Linux / Mac OS do this: export GDAL_DATA=/usr/local/share/gdal
If on Windows do this: C:\> set GDAL_DATA=C:\GDAL\data

To Export Data
Use ogr2ogr as follows to export a table (in this case a table called noise.hoods) to a GeoJSON file (in this case a file called nyc_hoods_noise.geojson):

ogr2ogr -f GeoJSON -t_srs EPSG:4326
nyc_hoods_noise.geojson PG:"dbname='name_of_database' user='user_name' password='your_password'" 
-sql "SELECT neighborho, borough, no_complaints, area_sqm, complaints_sqkm, geom FROM noise.hoods"