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.