&& Using The Postgresql Database on Linux 
  -----------------------------------------,
 
  This book purports to provide concise and practical recipes for 
  using the open-source relational (sql) database systems Postgresql on linux

  @@ www.postgresql.org
     the official site
  @@ http://www.cyberciti.biz/faq/psql-fatal-ident-authentication-failed-for-user/
     a good article about authentication problems (i.e. you cant logon)

 * Watch postgresql calls from your application on localhost
 >> sudo tcpdump -nnvvXSs 1514 -i lo0 dst port 5432

INSTALLING POSTGRESQL 

  * install the postgresql database on a debian-style distribution
  >> sudo apt-get install postgresql

  * install the postgresql on debian with postgis support
  >> sudo apt-get install postgresql-postgis

  * create a user with same name as current user and database
  >> sudo -u postgres createuser -s $USER
  >> createdb

  * now log into the shell
  >> psql

AUTHENTICATION 

  The process of logging onto something is generally referred to 
  as 'authentication'. Postgresql uses reasonably complicated authentication
  which may cause some head-scratching.

  * switch user to the 'postgres' user, in order to administer the server
  >> sudo su postgres  ##(only necessary on some linux distributions, at first)
  >> psql -U postgres  ##(should achieve the same thing)

  * find out where the authentication configuration file is
  >> find / -name 'pg_hba.conf'
  >> locate pg_hba.conf          ##(maybe the same)

STOPPING AND STARTING THE SERVER ....

  * check that the postgresql database server is running
  >> ps ax | grep postgres
  >> ps ax | grep postmaster    ##(an older form)
  >> pg_ctl status              ##(PGDATA needs to be set)
  >> sudo /etc/init.d/postgresql-8.4 status  ##(maybe best on debian-style linux)

  * start the postgresql database server
  >> sudo /etc/init.d/postgresql-8.4 start ##(best on debian style linux?) 
  >> pg_ctl start
  >> pg_ctl -w start     ##(refuses connections until the server is going)

  * the default 8.4 data folder on ubuntu
  >> /var/lib/postgresql/8.4/main

  * start a postgresql server listening on tcp port 5433
  >> pg_ctl -o "-p 5433" start

  * shutdown the postgre database server
  >> pg_ctl stop
  >> sudo /etc/init.d/postgresql-8.4 stop     ##(debian-style) 

  * restart the database server
  >> pg_ctl restart
  >> sudo /etc/init.d/postgresql-8.4 restart  ##(debian-style) 

BATCH SQL STATEMENTS

 * Run gunzipped sql file in PostGres, adding to the library.
 >> gzip -dc /tmp/pavanlimo.gz | psql -U user db

ANALYSING DATA

  * find the number of rows in a table
  >> select count(*) from theTable

  * a fast but not quite so accurate way
  >> SELECT reltuples AS estimate FROM pg_class where relname = 'mytable';

VIEWING DATA

 * view all data from the table 'trees'
 >> SELECT * FROM trees;
  
 * display table data with extra ascii borders
 >> \pset border 2
 >> SELECT * FROM trees;

 * display table data with no ascii borders
 >> \pset border 0

TABLES
 
 * view the definition (columns) of the table 'offers' 
 >> \d offers   

CREATING TABLES ....

  * create a new table 'newtable' with 2 fields (columns)
  -------------------------------------------------------

   testdb=> CREATE TABLE newtable (
       testdb(>  first integer not null default 0,
       testdb(>  second text)
       testdb-> ;
  ,,,

 * postgresql SQL to show count of ALL tables (relations)
 --------
    SELECT relname, reltuples, pg_relation_size(relname) 
    FROM pg_class r 
    JOIN pg_namespace n ON (relnamespace = n.oid) 
      WHERE relkind = 'r' AND n.nspname = 'public' ORDER BY relname;
  ,,,,

 * for x in `psql -e\l | awk '{print $1}'| egrep -v
 >> for x in `psql -e\l | awk '{print $1}'| egrep -v "(^List|^Name|\-\-\-\-\-|^\()"`; do pg_dump -C $x | gzip > /var/lib/pgsql/backups/$x-nightly.dmp.gz; done

DATABASES ....

  * list databases on the database server
  >> psql -l 

  * List all PostgreSQL databases. Useful when doing backups
  >> psql -U postgres -lAt | gawk -F\| '$1 !~ /^template/ && $1 !~ /^postgres/ && NF > 1 {print $1}'

  * create a postgresql database called 'company'
  >> createdb company    ##(this is typed at the bash shell)

  * delete the database called 'company'
  >> dropdb company

THE PSQL UTILITY ....

  * connect to the 'test' database on the database server 
  >> psql test   ##(the name of a database is required, or defaults to the user)
  >> psql        ##(may produce an error, if there is no database with username)

  * list all databases on the database server
  >> psql -l 

  * connect to the 'test' database on the server using the role (user/group) 'bob'
  >> psql -U bob -d test

  == psql commands
  .. \q   - quit the psql interpreter
  .. \du  - show database roles
  .. \l   - list all databases
  ..

USER ACCOUNTS ....

  * create a new postgre sql 'role' called 'bob' 
  >> createuser bob            ##(a role is like a user and group combined)
  pg:>> CREATE ROLE bob;     ##(within the psql interpreter)

  * eliminate the postgresql role 'bob'
  >> dropuser bob
  pg:>> DROP ROLE bob;
  pg:>> drop role bob;   ##(the same, its not case sensitive)

  * show current roles within the database server
  pg:>> \du
  pg:>> SELECT rolname FROM pg_roles;  ##(only displays the names)

  * create a new database role 'bob' which can create databases, users and login
  >> CREATE ROLE bob LOGIN CREATEDB CREATEROLE;

BACKUPS 

 * make pgsql backup and gzip it
 >> pg_dump otrs2 | gzip > dump.gz

 * dump database from postgresql to a file
 >> pg_dump -Ft -b -Uusername -hdb.host.com db_name > db.tar

 * Export/Backup a PostgreSQL database
 >> pg_dump -U postgres [nomeDB] > db.dump

CSV

  But the input file format has to be exactly right

  * import csv data from a text file on server into a postgres table
  >> \copy trees FROM '/home/rowantree/Desktop/tree/ala.eucalyptus.csv' DELIMITER ',' CSV

  The below requires the right permissions for the file on the server
  because the psql prog is running as a different user.

  * import csv data from a text file on server into a postgres table
  >> COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' WITH (FORMAT csv);

  * copy from a csv using column names to map csv fields
  --------
    \copy landmarks
      (name,address,date_built,architect,landmark,latitude,longitude) 
    FROM '/local/path/to/Individual_Landmarks.csv' DELIMITERS ',' CSV HEADER;
  ,,,,

  To import geographical point info from a csv, first import to a 
  latitude and longitude column and then update the geography column.
  The latitude and longitude should probably be varchar type.

  * update a point column from a latitude and longitude column
  ------
    UPDATE landmarks SET geo = ST_GeomFromText
      ('POINT(' || longitude || ' ' || latitude || ')',4326);
  ,,,,


POSTGIS
  
  Postgis is an extension to postgresql which makes it easier to 
  handle geometric and geographical data. It extends postgres with
  datatypes and functions.

  * install the postgresql on debian with postgis support
  >> sudo apt-get install postgresql-postgis

  * enable postgis in the current database
  ----
  CREATE EXTENSION IF NOT EXISTS plpgsql; # only needed once
  create extension postgis
  ,,,,


  4326 is a common mapping srid system (usually the default)
  spatial reference system = SRID 
  
  * create a table for geographic (lat/long) points
  ----------
    CREATE TABLE global_points (
      id SERIAL PRIMARY KEY,
      name VARCHAR(64),
      location geography(POINT,4326)
    );
  ,,,,

  * create a table with 3D (lat,long,altitude) geography
  >> CREATE TABLE trees 
  >>   (gid serial PRIMARY KEY, geog geography(POINTZ,4326) );

  * 2D polygon geography, for regions etc
  ----
    CREATE TABLE regions 
      (gid serial PRIMARY KEY, geog geography(POLYGON,4267) );
  ,,,,

  * create a geography index
  >> CREATE INDEX gindex ON trees USING GIST ( location );

  * insert data
  >> INSERT INTO global_points (name, location) VALUES ('Town', 'SRID=4326;POINT(-110 30)');

  * an example table with point data with constraints
  -------
  CREATE TABLE landmarks (
  gid serial NOT NULL,
  name character varying(50),
  latitude double precision,
  longitude double precision,
  the_geom geometry,
  CONSTRAINT landmarks_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
);

CREATE INDEX landmarks_the_geom_gist
  ON landmarks
  USING gist
  (the_geom );
  ,,,,

DISTANCE ....

  You may want to know how far away something is. This is how.
  
  * the postgis distance functions
  -----
    float ST_Distance(geometry g1, geometry g2);
    float ST_Distance(geography gg1, geography gg2);
    float ST_Distance(geography gg1, geography gg2, boolean use_spheroid);
  ,,,,

  ST_DistanceSphere maybe faster than ST_Distance or ST_DistanceSpheroid ??

  * a query to find trees not too far from a point  
  ---------
    SELECT * FROM trees
    WHERE ST_DistanceSphere
      (position, ST_MakePoint(your_lon, your_lat)) <= radiusInMiles * 1609.34
  ,,,,

  * find shops less than 20kms from a point
  ---------
    SELECT * FROM shops 
    WHERE ST_DistanceSphere
      (geo, ST_MakePoint(longitude, latitude)) <= 20
  ,,,,

LONGITUDE AND LATITUDE ....

  In postgis, longitude usually comes first, unlike in osm and 
  google maps. But the coords can be flipped.

  * swap the latitude and longitude
  -------------
    ALTER TABLE sometable
    ALTER COLUMN geog
      TYPE geography(LineString,4326)
      USING geography(ST_FlipCoordinates(geometry(geom)));
  ,,,

COLUMNS

  * add a postgis column to the trees table
  >> alter table trees add position geography(POINT,4326);

  * alter a column datatype to varchar
  >> alter table egtrees alter column egname type varchar ;

SELECTING AND QUERYING RECORDS

  The sql 'select' command starts the whole schmozzle

PATTERN MATCHING ....

  * get records matching a simple string pattern
  >> select * from trees where sciname like '%ovata%';