-- This file provides step by step instructions for reading geographic
-- data into postgis and creating from it, a spatially aware table
-- with and index and a proper entry in the geometry_columns table so
-- that it can be accessed by external tools

-- Much of what follows is adapted from PostGIS In Action
-- http://www.manning.com/obe/ By Regina O. Obe and Leo S. Hsu

-- BEFORE you can do anything with this file, you will need to connect
-- with postgreSQL+PostGIS. Although the world owes you a user
-- friendly intuitive touchy feely pointy clicky user
-- interface... where SQL is concerned, the world has yet to make good
-- on this debt.  I am sorry. It's not fair.  The most macho
-- alternative for editing and executing SQL code is of course emacs.
-- As with R, there exists a mode -- sql-postgres -- which provides
-- several life enhancing key combinations and allows you to connect
-- to the database server in an inferior shell window (just like R).
-- For now, however, since you are probably not planing to live in
-- SQL, the wisest approach is to edit your file in emacs (where you
-- will get the usual colorful clues about syntax) and then to drag
-- and drop commands into a separate window where an application
-- called "psql" is running. psql is a command line interface that
-- that let's you submit SQL commands to the database server:

-- Whatever method you use to connect with the postgres, you will need
-- to provide 4 bits of information in order to connect:

-- Userid:  same as your login id
-- Server:  coale
-- database: demog215
-- password: 

-- to launch psql type:
-- psql -U userid -h coale demog215


-- Once you have a connection to the database  type

\d

-- in th psql window  This should produce a list of database
-- objects. Notice that the first column is called "Schema" and that
-- all of the objects in the demog215 database are in the "public"
-- schema. For your own safety and my convenience, you cannot write to
-- any objects in the "public" schema you may however read/select from
-- them. To do any work, you will need your own private schema which
-- you should now create by typing this in your psql window. 
-- WHERE USERNAME is you username ok?

create schema USERNAME;


-- a schema is like a folder it is a
-- "place" where you can write your output files without ruining other
-- people's work.  by default everything you creaet will wind up in
-- your eponymous schema. 

-- create a table to hold the fastfood location data this is the
-- important table it will hold first lattitude and longitude and
-- eventually a more useful representation of that information.

CREATE TABLE fastfoods
(
franchise char(1) NOT NULL,
lat double precision,
lon double precision
);

-- once the above table is created, we populate it from a flat file.
-- Let's use the \copy command from inside psql to read a csv files
-- with three columns -- into the empty fastfoods table that we just
-- created. It would be nice if the csv file has the structure implied
-- by the create table statement

-- NOTE fastfoods should wind up in your personal "schema" NOT in the
-- public schema

\copy fastfoods from '~carlm/215/GIS/fastfoods.csv' delimiter as ','

-- Create a lookup table to match one letter codes that are in the
-- fastfoods data to fastfood franchises

CREATE TABLE lu_franchises(
franchise_code char(1) PRIMARY KEY,
franchise_name varchar(100));
INSERT INTO lu_franchises(franchise_code, franchise_name)
VALUES ('b', 'Burger King'),
('c', 'Carl''s Jr'),
('h', 'Hardee''s'),
('i', 'In-N-Out'),
('j', 'Jack in the Box'),
('k', 'Kentucky Fried Chicken'),
('m', 'McDonald''s'),
('p', 'Pizza Hut'),
('t', 'Taco Bell'),
('w', 'Wendy''s');


-- let's see what's in it:
-- notice the "group by"
select franchise, count(*) from fastfoods group by franchise;

-- now something a wee bit fancier

select lu.franchise_name, count(*) 
  from fastfoods as ff
  INNER JOIN  lu_franchises as lu
  on lu.franchise_code = ff.franchise
group by lu.franchise_name order by count;

-- this is called an "inner join"  


-- The fastfoods table at present is NOT a spatially aware table. It
-- has lat and lon stored as plain old numbers. Thus far we have used
-- only plain ordinary SQL to make a table full of plain ordinary
-- data.  To make it geographical, we need to convert the lat/lon numbers into
-- "point" data. Doing this will make the table "spatial".

-- create new column to hold our spatialized version of lat and long
-- specify SRID 2163 -- which is one of a zillion "spatial reference" systems.

SELECT AddGeometryColumn(SCHEMA, 'fastfoods', 'geom', 2163, 'POINT',2);
-- 
UPDATE fastfoods
SET geom =
ST_Transform(ST_GeomFromText('POINT(' || lon || ' ' || lat || ')',4326), 2163);

-- create an index.  We will do this many times. It takes only a few
-- seconds to create but it save HOURS when we do queries later on
-- with this data.

CREATE INDEX idx_fastfoods_geom ON fastfoods USING gist(geom);
vacuum analyze fastfoods

-- SRID 2163 is a coordinate system in euclidean 2D space.  It is in
-- other words a set of compromises that allow us to represent
-- sphoerical data (earth) as if it were really a flat plane. 

-- Let's take a look at how lat and lon differ from the data stored in
-- the newly created "geom" column

alter table fastfoods add column xcord double precision;
alter table fastfoods add column ycord double precision;

update  fastfoods set xcord = ST_X(geom);
update  fastfoods set ycord = ST_Y(geom);

select lon, xcord, lat, ycord from fastfoods limit 10;

-- add a column of unique ids for each fastfood resaurant
ALTER TABLE fastfoods ADD COLUMN ff_id SERIAL PRIMARY KEY;

-- to see the names of all the tables that you have created.
-- type 
\d

-- to see the structure of a particular table type
\d fastfoods


