Lab 4 - Performing Common GIS Operations in PostgreSQL/PostGIS

In this week's lab assignment scenario, we worked with a spatial database to support analysis of town hall meetings that were held from January 2013 to January 2017 for House members from the 113th and 114th Congress in the State of Florida.

The lab's tasks related to performing common GIS operations in PostgreSQL/PostGIS included buffers, overlays, and unions using the following functions, respectively: ST_Buffer(), ST_DWithin(), and ST_Union(). Our class textbook came in quite handy for this part, and is the reason for the screenshot on the left: PostGIS in Action by Regina O. Obe and Leo S. Hsu.

On an ending note, we were asked to write a SQL statement that would return the unions of census blocks that fell within 805 meters of Representative Buchanan's meeting on 4/18/15.

Because I cannot provide a screenshot of that script here in my blog because future students might cheat, instead see below a spatial question I formulated on my own about town hall meetings:

My Spatial Question: What is the total population representation for each Congressional District?

Proposition of SQL query methods to use:

To figure out the total population representation, it would be ideal to aggregate all the attributes from the Census Block Groups dataset that are within each congressional district by doing a table join. The ACS_Block_Groups data provides us with a TotalPop column. We could sum up this field for all blocks within a congressional district, and then see if the total population is evenly dispersed amongst the congressional districts. But since neither of the datasets obtain a unified column ID to join the tables, instead we could use the ST_Union function in PostGIS to maintain all the data fields from both datasets (congressional districts and ACS block groups), while ensuring that block groups that fall within a congressional district are spatially joined, too. In order to properly dissolve the data, we would need to make sure to use the GROUP BY clause within the ST_Union SQL statement as well. The GROUP BY clause is similar to the Summarize Tool in ArcGIS, where you could summarize the TotalPop column by Congressional District, as shown in my screenshots below:

 

But going back to PostgreSQL/PostGIS: now that all data has been combined, we could write a SQL statement that returns the total population for each congressional district.

As far as visuals, I provided a vertical bar chart. I was going to provide a choropleth map, but the data wouldn’t have been normalized, and that would have been sacrilegious to us cartographers. Also, please note that I chose this spatial question with the intent to re-use function(s) we’ve learned thus far. I was not trying to justify nor attack the delineation of congressional districts. It’s just an observation; also note there is an “unknown” data that returned when dissolving the information.