clouddatastoreblog.sql

-- Create table for PGH 311 requests
create table if not exists pgh_311_requests (
  REQUEST_ID integer,
  CREATED_ON varchar,
  REQUEST_TYPE varchar,
  REQUEST_ORIGIN varchar,
  STATUS integer,
  DEPARTMENT varchar,
  NEIGHBORHOOD varchar,
  COUNCIL_DISTRICT integer,
  WARD integer,
  TRACT bigint,
  PUBLIC_WORKS_DIVISION integer,
  PLI_DIVISION integer,
  POLICE_ZONE integer,
  FIRE_ZONE varchar,
  X float,
  Y float,
  GEO_ACCURACY varchar)

-- Copy data from .csv in Amazon S3
FROM '<s3path>/pgh311requests.csv'
WITH credentials 'aws_access_key_id=<key>;aws_secret_access_key=<key>'
delimiter ',' 
CSV
IGNOREHEADER 1;

-- Count # of records in table
select count (request_id) from pgh_311_requests;

-- View first 50 records
select * from pgh_311_requests
limit 50;

-- Convert to spatial table
-- Convert X Y column attributes to geometry/geography
SELECT REQUEST_ID, CREATED_ON,REQUEST_TYPE, REQUEST_ORIGIN, STATUS, DEPARTMENT, NEIGHBORHOOD, COUNCIL_DISTRICT, WARD, TRACT, PUBLIC_WORKS_DIVISION, PLI_DIVISION, POLICE_ZONE, FIRE_ZONE, X, Y, GEO_ACCURACY, ST_SetSRID(ST_MakePoint(X::float8,Y::float8), 4326)::geometry as geom
INTO pgh_311_requests_spatial
FROM pgh_311_requests
WHERE X != '' AND Y != '';