Creating a Census Tracts Table

In the workshop \data\ directory, is a file that includes attribute data, but no geometry, nyc_census_sociodata.sql. The table includes interesting socioeconomic data about New York: commute times, incomes, and education attainment. There is just one problem. The data are summarized by "census tract" and we have no census tract spatial data!

In this section we will

• Load the nyc_census_sociodata.sql table
• Create a spatial table for census tracts
• Join the attribute data to the spatial data
• Carry out some analysis using our new data

-- Make the tracts table
CREATE TABLE nyc_census_tract_geoms AS
SELECT
  ST_Union(the_geom) AS the_geom,
  SubStr(blkid,1,11) AS tractid
FROM nyc_census_blocks
GROUP BY tractid;
-- Index the tractid
CREATE INDEX nyc_census_tract_geoms_tractid_idx ON nyc_census_tract_geoms (tractid);
-- Update the geometry_columns table
SELECT Populate_Geometry_Columns();

-- Make the tracts table
CREATE TABLE nyc_census_tracts AS
SELECT
  g.the_geom,
  a.*
FROM nyc_census_tract_geoms g
JOIN nyc_census_sociodata a
ON g.tractid = a.tractid;
-- Index the geometries
CREATE INDEX nyc_census_tract_gidx ON nyc_census_tracts USING GIST (the_geom);
-- Update the geometry_columns table
SELECT Populate_Geometry_Columns();

SELECT
  Round(100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total), 1) AS graduate_pct,
  n.name, n.boroname
FROM nyc_neighborhoods n
JOIN nyc_census_tracts t
ON ST_Intersects(n.the_geom, t.the_geom)
WHERE t.edu_total > 0
GROUP BY n.name, n.boroname
ORDER BY graduate_pct DESC
LIMIT 10;

 graduate_pct |       name        | boroname
--------------+-------------------+-----------
         40.4 | Carnegie Hill     | Manhattan
         40.2 | Flatbush          | Brooklyn
         34.8 | Battery Park      | Manhattan
         33.9 | North Sutton Area | Manhattan
         33.4 | Upper West Side   | Manhattan
         33.3 | Upper East Side   | Manhattan
         32.0 | Tribeca           | Manhattan
         31.8 | Greenwich Village | Manhattan
         29.8 | West Village      | Manhattan
         29.7 | Central Park      | Manhattan

Polygon/Polygon Joins

In our interesting query (in :ref:`interestingquestion`) we used the :command:`ST_Intersects(geometry_a, geometry_b)` function to determine which census tract polygons to include in each neighborhood summary. Which leads to the question: what if a tract falls on the border between two neighborhoods? It will intersect both, and so will be included in the summary statistics for both.

To avoid this kind of double counting there are two methods:

• The simple method is to ensure that each tract only falls in one summary area (using :command:`ST_Centroid(geometry)`)

  System Message: ERROR/3 (<string>, line 134); backlink

  Unknown interpreted text role "command".

• The complex method is to divide crossing tracts at the borders (using :command:`ST_Intersection(geometry,geometry)`)

  System Message: ERROR/3 (<string>, line 135); backlink

  Unknown interpreted text role "command".

Here is an example of using the simple method to avoid double counting in our graduate education query:

SELECT
  Round(100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total), 1) AS graduate_pct,
  n.name, n.boroname
FROM nyc_neighborhoods n
JOIN nyc_census_tracts t
ON ST_Contains(n.the_geom, ST_Centroid(t.the_geom))
WHERE t.edu_total > 0
GROUP BY n.name, n.boroname
ORDER BY graduate_pct DESC
LIMIT 10;

Note that the query takes longer to run now, because the :command:`ST_Centroid` function has to be run on every census tract.

 graduate_pct |       name        | boroname
--------------+-------------------+-----------
         49.2 | Carnegie Hill     | Manhattan
         39.5 | Battery Park      | Manhattan
         34.3 | Upper East Side   | Manhattan
         33.6 | Upper West Side   | Manhattan
         32.5 | Greenwich Village | Manhattan
         32.2 | Tribeca           | Manhattan
         31.3 | North Sutton Area | Manhattan
         30.8 | West Village      | Manhattan
         30.1 | Downtown          | Brooklyn
         28.4 | Cobble Hill       | Brooklyn

Avoiding double counting changes the results!

Large Radius Distance Joins

A query that is fun to ask is "How do the commute times of people near (within 500 meters) subway stations differ from those of people far away from subway stations?"

However, the question runs into some problems of double counting: many people will be within 500 meters of multiple subway stations. Compare the population of New York:

SELECT Sum(popn_total)
FROM nyc_census_blocks;

8008278

With the population of the people in New York within 500 meters of a subway station:

SELECT Sum(popn_total)
FROM nyc_census_blocks census
JOIN nyc_subway_stations subway
ON ST_DWithin(census.the_geom, subway.the_geom, 500);

10556898

There's more people close to the subway than there are people! Clearly, our simple SQL is making a big double-counting error. You can see the problem looking at the picture of the buffered subways.

The solution is to ensure that we have only distinct census blocks before passing them into the summarization portion of the query. We can do that by breaking our query up into a subquery that finds the distinct blocks, wrapped in a summarization query that returns our answer:

SELECT Sum(popn_total)
FROM (
  SELECT DISTINCT ON (blkid) popn_total
  FROM nyc_census_blocks census
  JOIN nyc_subway_stations subway
  ON ST_DWithin(census.the_geom, subway.the_geom, 500)
) AS distinct_blocks;

4953599

That's better! So a bit over half the population of New York is within 500m (about a 5-7 minute walk) of the subway.