--Create schema in gep664 to hold project
CREATE SCHEMA catch
AUTHORIZATION postgres;

--Load shapefiles into catch for blockgroups, blockgroup centers, and hospitals
--Name: bgroups, bgroups_pop, hospitals
--SRID is 2263

--Calculate distances within threshold and carry over relevant attributes
CREATE TABLE catch.dist3 AS
SELECT h.gid, h.name, b.geoid2, ST_Distance(h.geom, b.geom) AS dist,h.capacity AS beds, b.pop2010
FROM catch.hospitals h, catch.bgroups_pop b
WHERE ST_Dwithin(h.geom, b.geom, 15840) AND b.pop2010 > 0;

--Calculate population and services by service point
CREATE TABLE catch.bedval AS
SELECT gid, name, SUM(pop2010) AS popsum, beds, (beds/SUM(pop2010)) AS r
FROM catch.dist3
GROUP BY gid, name, beds;

--Bring these service point summaries back to the main data
CREATE TABLE catch.dist3new AS
SELECT d.gid, d.name, d.geoid2, d.dist, d.beds, d.pop2010,  b.popsum, b.r
FROM catch.dist3 d, catch.bedval b
WHERE d.gid=b.gid;

--Calculate service accessibility by origin points
CREATE TABLE catch.bgroup_acc AS
SELECT geoid2, SUM(r) AS sumr
FROM catch.dist3new
GROUP BY geoid2;

--Relate origin points and access back to origin areas for mapping
CREATE VIEW catch.bgroup_hosp AS
SELECT b.geoid2, a.sumr, b.geom
FROM catch.bgroups b, catch.bgroup_acc a
WHERE b.geoid2=a.geoid2;