Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- 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 != '';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment