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;