BASH.md

Bash Cheatsheet

The most common commands so I don't forget!

Directories

• list files ls
• list all files (including .dot files) ls -a
• list all files (with long format) ls -l
• make directory mkdir [opt] dir
• make directory (including it's parents) mkdir -p [opt] parent1/parent2/dir
• change directory cd path
• current directory . parent dir .. home dir ~
• print working directory pwd
• create empty file touch file
• copy cp [OPT] FROM TO
• copy directories recursively cp -r FROM TO
• move or rename mv FROM TO
• remove rm [OPT] file
• recursively remove directories rm -r dir
• force remove rm -f file
• remove directory rmdir [OPT] dir
• remove directory parents rmdir -p dir

Files

• concatenate and print files cat [OPT] files
• concatenate and print files (number de output of lines) cat -l files
• view file vim [OPT] file
• display first lines head [OPT] file
• display last lines tail [OPT] file
• print lines matching a pattern grep [OPT] pattern [FILE...]
• display the number of matched lines -c
• ignore case sensitivity -i
• display the filenames -l
• display the line numbers -n
• match whole word -w
• word count wc [OPT] file
• line count -l
• byte count -c
• character count -m
• word count -w

CTRL-KEY Commands

• kill process CTRL + C
• stop process CTRL + Z
• end of file CTRL + D

I/O Redirection

• Redirect stdout to a file (overwrite) command > file (append) command >> file

• Redirect stdin to a file command < file

• Redirect the output from one command as input to the next one command1 | command2 | command3

Wildcards

• * any number of characters
• ? any single character

System and Security

Unix permissions: Allows users to run programs with the security privileges of another user (normally the root).

• sudo [OPT] [USER] command

(here should be an image of the permissions)

• change permissions chmod [OPT] MODE file
• -R recursively
• +rx read and execute for all
• g-w deny write access for group
• 777 read, write and execute for all

Unix Processes

• run process on background command &
• background or suspended processes jobs
• send signal to a process kill signal process
• kill process by name killall process
• display top cpu processes top
• reports the process status ps
• reports the process status (full listing) ps -f
• reports the process status (all processes) ps -e

Others

• last commands used history
• -c clear
• !* last matched command
• time command execution time command
• compare files line by line diff
• walk a file hierarchy find path [OPT] [EXPRESSION]
• -name find by name
• -size find by size
• -iname case insensitive mode

POSTGRES CHEATSHEET.md

PostgreSQL & PostGIS Cheatsheet

The most common commands so I don't forget!

Postgres

• 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

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

Common Commands

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

• to create a databse: CREATE DATABASE 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 create a user without password CREATE USER username;

• to create a user with password CREATE USER username WITH PASSWORD 'password-text';

• to create a user with a password that is valid until the end of 2004. After one second has ticked in 2005, the password is no longer valid. CREATE USER miriam WITH PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';

• to create an account where the user can create databases CREATE USER manuel WITH PASSWORD 'jw8s0F4' CREATEDB;

• 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

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

Common Commands

• 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);

Spatial Indexing

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 USING table_name_gix;
  Do this after making changes to your dataset or importing new data.

Importing data to PostGIS

Using shp2pgsql

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

Using osm2pgsql

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

Using ogr2ogr

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

Exporting data from PostGIS

Using ogr2ogr

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"