Module 3 - Vector Operations

This module moved from building the database to actually writing spatial SQL to answer a real analytical question: does proximity to the beach correlate with higher home prices in Pinellas County?

Before writing any queries, the module asked whether the existing ERD could actually support this kind of analysis without structural changes. It could: the proximity relationship between parcel_points and beaches supports distance-based queries directly through spatial geometry, the one-to-many "has" relationship between parcel points and sale records supports aggregating multiple sales per parcel, and the "affects" relationship linking inflation_index to parcel_sales via date fields supports adjusting historical prices for inflation before comparing them.

The first query calculates the distance from every parcel point to the nearest beach segment using PostGIS's ST_Distance():

SELECT pp.parcelid,
    ST_Distance(pp.geom, b.geom) AS distance
FROM parcels.beaches AS b, parcels.parcel_points AS pp
ORDER BY ST_Distance(pp.geom, b.geom) DESC;

To verify this wasn't just numerically correct but actually right, I cross-checked it against ArcGIS Pro's Near tool. I ran it with parcel_points as the input and beaches as the near feature, then spot-checked a handful of parcel IDs against the SQL output. The distances matched, which confirmed the query was doing what I intended rather than just running without error.

The second query needed to compare home sale prices across different years fairly, which meant adjusting nominal prices using the Case-Shiller inflation index rather than comparing raw sale prices directly:

SELECT pin, AVG(real_price) AS avg_real_price
FROM (
    SELECT ps.pin, ps.price, ps.sale_date, ii.tpxrsa,
        ((ps.price * 100.7) / ii.tpxrsa) AS real_price
    FROM parcels.parcel_sales AS ps, parcels.inflation_index AS ii
    WHERE date_part('year', ps.sale_date) = date_part('year', ii.date)
    AND date_part('month', ps.sale_date) = date_part('month', ii.date)
) AS subquery
ORDER BY pin, avg_real_price DESC;

The final step joined the distance results with the price results by parcel ID, sorted by distance, to see whether closer parcels actually showed higher average prices. I initially got zero results back from the join and had to track down why. The issue turned out to be a formatting mismatch: the pin field in the sales table was formatted with dashes (01-30-14-42030-001-0030), while parcelid in the spatial table was the same identifier with no dashes (013014420300010030). Since they were stored as different strings, the join was silently matching nothing. I fixed it using REPLACE() to strip the dashes from pin before comparing:

WITH parcel_distances AS (
    SELECT pp.parcelid, MIN(ST_Distance(pp.geom, b.geom)) AS distance_to_beach
    FROM parcels.parcel_points AS pp, parcels.beaches AS b
    GROUP BY pp.parcelid
),
average_prices AS (
    SELECT pin AS parcelid, AVG(price) AS avg_price
    FROM parcels.parcel_sales
    GROUP BY pin
)
SELECT pd.parcelid, pd.distance_to_beach, ap.avg_price
FROM parcel_distances pd
JOIN average_prices ap ON pd.parcelid = REPLACE(ap.parcelid, '-', '')
ORDER BY distance_to_beach;

The output showed a general trend of higher average home prices for parcels closer to the beach, but with meaningful variability, which is an honest signal that proximity is likely one factor among several driving price, not the whole story.

 

Module 2 - Development of Spatial Databases

Where Module 1 focused on the logical design of a spatial database (entities, relationships, cardinality), this module moved to the physical implementation and taking that conceptual model and actually building it in PostgreSQL/PostGIS, with real data types, schemas, and imported data.

The scenario involved a Pinellas County home-value study: analyzing the relationship between parcel sale prices and proximity to the beach, using four entities: parcel_points and beaches (spatial), and parcel_sales and inflation_index (non-spatial, tabular). The first step was translating the conceptual model into a physical ERD with PostgreSQL-specific data types and clearer relationship semantics:

 

I created a dedicated schema (parcels) and defined the two non-spatial tables directly with the appropriate PGSQL data types: varchar with explicit lengths, date, integer, and numeric(5,2) for the inflation index's price ratio field.

CREATE SCHEMA parcels;

CREATE TABLE parcels."parcel_sales" (
    "pin" varchar(25),
    "sale_date" date,
    "frontage" varchar(20),
    "price" integer,
    "quality" varchar(20),
    "heated_area" integer,
    "effective_age" integer,
    "evac_zone" varchar(10)
);

CREATE TABLE parcels."inflation_index" (
    "date" date,
    "tpxrsa" numeric(5,2)
);

With the tables created, I imported parcel_sales.csv and inflation_index.csv using pgAdmin's Import/Export tool instead of the SQL COPY command, since COPY requires superuser privileges that weren't available on this instance.

SELECT * FROM parcels.parcel_sales LIMIT 10;
SELECT * FROM parcels.inflation_index LIMIT 10;

The parcel_points and beaches shapefiles came in through the PostGIS Shapefile GUI Loader, which handles the geometry-specific import (and SRID assignment) that a plain CSV import doesn't need to worry about:

SELECT * FROM parcels.parcel_points ORDER BY gid ASC LIMIT 10;
SELECT * FROM parcels.beaches ORDER BY gid ASC LIMIT 10;

 

Module 1 - Spatial Database Design Using ERDs

This module's project was to design a conceptual database for tracking historical water quality data across Pensacola (Escambia and Santa Rosa counties) which is the kind of database that would support mapping water quality trends to inform land-use decisions, similar in spirit to how FDEP's STORET system aggregates water sampling data statewide. Given my work with water quality and regulatory data at DEP, this exercise mapped closely onto real problems I already think about.

The task was to build an entity-relationship diagram (ERD) covering entities like county boundaries, hydrological units, water bodies, water sample stations, and water sample measurements. We were also tasked to make deliberate choices about cardinality (how entities relate: one-to-many, many-to-many) and how to visually distinguish spatial from non-spatial entities, following the modeling framework from Calkins (1996). 

 

I modeled overlapping spatial features like land use polygons and county boundaries, or water bodies and hydrologic units as many-to-many relationships, since real-world spatial overlap is rarely clean or exclusive. Hierarchical or event-based relationships, by contrast, I modeled as one-to-many: a single water sample station can produce many measurements over time, but each measurement belongs to exactly one station; a water body can contain many sample stations, but each station sits in only one water body at a time.

Following Calkins' framework, I distinguished spatial entities (marked with geometry type and G/T indicators) from non-spatial entities like the Water Quality Organization table, which tracks administrative assignment rather than anything with coordinates. I also used different connector shapes (hexagons for spatial/topological relationships, diamonds for non-spatial ones) so the diagram is able to visually separate "this is geometry" from "this is supporting attribute data" at a glance.

The module also asked whether Calkins' and Akinyemi's spatial-relationship frameworks are actually necessary for GIS design. I believe they matter most when a system needs to support real spatial analysis such as routing, tracing, or topology-aware queries. Road networks and stream flowlines often need built-in topology, and land-use overlays rely more on coordinate-based intersection queries. Without deliberately designing for that distinction upfront, it's easy to end up with a database that displays data fine, but can't actually support the analysis that it was built for.

 

Module 6 - Proportional Symbols and Bivariate Choropleth

     This week's module explored visualizing data using proportional symbols. We were tasked to create a map depicting gains and losses in the job market using proportional symbology. The dataset was split between positive and negative values, so I used two separate layers as ArcGIS cannot represent negative values properly using proportional symbols. The first layer represented job market gains, represented in blue, while the second layer showed losses, represented in red. To ensure proportional symbology worked correctly for the losses, I used the Field Calculator in the Attribute Table to convert all negative values to positive. This allowed the symbol sizes to reflect the magnitude of the losses accurately.

 

Module 5 - Analytical Data

     The goal of this week's module was to create an infographic based on two potentially causally related health variables obtained from the County Health Rankings & Roadmaps program. The variables I chose to visualize were reported insufficient sleep and average mentally unhealthy days per month. 

     

    I placed a column along each side of the infographic and placed my charts in these. In the middle are the choropleth maps of my chosen variables. I ensured every element was consistent in color, using either a shade of purple from the insufficient sleep choropleth map or a shade of blue from the mental health choropleth map. I made the text white or black depending on the lightness of the background to ensure readability and ensured the background colors consisted of muted hues as to not distract from the main elements of the infographic. 

    For my bar graph, I chose to plot the top 3 and bottom 3 counties, as well as the US average for comparison. Because of my variable unit mismatch I calculated an “Insufficient sleep-weighted mental health burden” by using the formula [ (Insufficient Sleep% / 100) x Average bad mental health days ] for each county. I kept the purple color scheme the same and gave the number 1 county the same color as the darkest shade of the choropleth color ramp.

Module 4 - Color Concepts & Choropleth Mapping

     This week's module involved examining RGB and HSV color ramps in ArcGIS Pro as well as exploring effective choropleth mapping. Three methods of creating color ramps were examined: linear progression, adjusted progression, and the website ColorBrewer.

 

    Left to right: Linear progression, adjusted progression and ColorBrewer

    The linear and adjusted progression color ramps are more mathematically consistent, following a uniform or modified stepwise increase in RGB values. In contrast, the ColorBrewer color ramp has non-uniform stepwise intervals, suggesting that ColorBrewer does not follow a mathematical pattern, but is instead designed in a way where the hues are easily distinguishable from one another. The ColorBrewer color ramp is visually distinct from the calculated progression ramps as its hues have greater contrast, which makes class distinctions clearer. The linear and adjusted color ramps appear visually similar, especially in the darker hues. The adjusted color ramp compensates for this with its increased intervals for darker hues, but does not have the same intentional hue contrast found in the ColorBrewer color ramp.

    The last section of the module tasked us with creating a choropleth map of population change in a state of our choosing. I chose to map population change in Colorado, using the Natural Breaks method.

  

     I chose Natural Breaks because the population-based data had natural variation with an uneven distribution of observations in the histogram. The Natural Breaks method considers how the data is clustered, which is useful in mapping data with natural variations. I chose a blue to red diverging color scheme and reversed the values to depict a gain (blue) to loss (scale). A blue to red color ramp allows for clear differentiation between increases and decreases and the colors blue and red suit the natural associations between gain and loss. I added Colorado's average population change for context, and changed the legend’s labels from “x% - x%” to “x% to x%” for readability.